sql server(MSSql)数据表数量大时建索引的方法,sql server(MSSql)索引知识归纳

@[TOC](sql server(MSSql)数据表数量大时建索引的方法,sql server(MSSql)索引知识归纳)

sql server(MSSql)数据库不但吃内存，而且有些表数据量达到几万的数量，一但和其它表联合查询就会慢，再按没有索引的字段排序就会更慢。

先看结论，再看实践过程

数据量达到几万时，有索引的查询比没索引可以快100倍。对有索引的字段进行排序比没索引的也快百倍。联合索引时，字段单独作为条件或者作为排序时索引无效。

准备样例数据

--书本表
CREATE TABLE t_book (
	id	int PRIMARY KEY IDENTITY(1,1),
	[name]	varchar(100),
	author	varchar(100)
);
--用户表
CREATE TABLE t_user (
	id	int PRIMARY KEY IDENTITY(1,1),
	[name]	varchar(100)
);
--阅读记录表
CREATE TABLE t_read (
	id	int PRIMARY KEY IDENTITY(1,1),
	[user_id]	int,
	book_id	int,
	[page]	int,
	addTime	int default(datediff(second,'1970-01-01',getutcdate()))
);
INSERT INTO t_book([name],author) VALUES('书本001','作者001'),	('书本002','作者002'),	('书本003','作者003'),	('书本004','作者004'),	('书本005','作者005'),	('书本006','作者006'),	('书本007','作者007'),	('书本008','作者008'),	('书本009','作者009'),	('书本010','作者010'),	('书本011','作者011'),	('书本012','作者012'),	('书本013','作者013'),	('书本014','作者014'),	('书本015','作者015'),	('书本016','作者016'),	('书本017','作者017'),	('书本018','作者018'),	('书本019','作者019'),	('书本020','作者020'),	('书本021','作者021'),	('书本022','作者022'),	('书本023','作者023'),	('书本024','作者024'),	('书本025','作者025'),	('书本026','作者026'),	('书本027','作者027'),	('书本028','作者028'),	('书本029','作者029'),	('书本030','作者030'),	('书本031','作者031'),	('书本032','作者032'),	('书本033','作者033'),	('书本034','作者034'),	('书本035','作者035'),	('书本036','作者036'),	('书本037','作者037'),	('书本038','作者038'),	('书本039','作者039'),	('书本040','作者040'),	('书本041','作者041'),	('书本042','作者042'),	('书本043','作者043'),	('书本044','作者044'),	('书本045','作者045'),	('书本046','作者046'),	('书本047','作者047'),	('书本048','作者048'),	('书本049','作者049'),	('书本050','作者050'),	('书本051','作者051'),	('书本052','作者052'),	('书本053','作者053'),	('书本054','作者054'),	('书本055','作者055'),	('书本056','作者056'),	('书本057','作者057'),	('书本058','作者058'),	('书本059','作者059'),	('书本060','作者060'),	('书本061','作者061'),	('书本062','作者062'),	('书本063','作者063'),	('书本064','作者064'),	('书本065','作者065'),	('书本066','作者066'),	('书本067','作者067'),	('书本068','作者068'),	('书本069','作者069'),	('书本070','作者070'),	('书本071','作者071'),	('书本072','作者072'),	('书本073','作者073'),	('书本074','作者074'),	('书本075','作者075'),	('书本076','作者076'),	('书本077','作者077'),	('书本078','作者078'),	('书本079','作者079'),	('书本080','作者080'),	('书本081','作者081'),	('书本082','作者082'),	('书本083','作者083'),	('书本084','作者084'),	('书本085','作者085'),	('书本086','作者086'),	('书本087','作者087'),	('书本088','作者088'),	('书本089','作者089'),	('书本090','作者090'),	('书本091','作者091'),	('书本092','作者092'),	('书本093','作者093'),	('书本094','作者094'),	('书本095','作者095'),	('书本096','作者096'),	('书本097','作者097'),	('书本098','作者098'),	('书本099','作者099'),	('书本100','作者100');
INSERT INTO t_book([name],author) SELECT [name]+'a',author+'a' FROM t_book;
INSERT INTO t_book([name],author) SELECT [name]+'b',author+'b' FROM t_book;
INSERT INTO t_book([name],author) SELECT [name]+'c',author+'c' FROM t_book;
INSERT INTO t_book([name],author) SELECT [name]+'d',author+'d' FROM t_book;
INSERT INTO t_user([name]) VALUES('用户001'),	('用户002'),	('用户003'),	('用户004'),	('用户005'),	('用户006'),	('用户007'),	('用户008'),	('用户009'),	('用户010'),	('用户011'),	('用户012'),	('用户013'),	('用户014'),	('用户015'),	('用户016'),	('用户017'),	('用户018'),	('用户019'),	('用户020'),	('用户021'),	('用户022'),	('用户023'),	('用户024'),	('用户025'),	('用户026'),	('用户027'),	('用户028'),	('用户029'),	('用户030'),	('用户031'),	('用户032'),	('用户033'),	('用户034'),	('用户035'),	('用户036'),	('用户037'),	('用户038'),	('用户039'),	('用户040'),	('用户041'),	('用户042'),	('用户043'),	('用户044'),	('用户045'),	('用户046'),	('用户047'),	('用户048'),	('用户049'),	('用户050'),	('用户051'),	('用户052'),	('用户053'),	('用户054'),	('用户055'),	('用户056'),	('用户057'),	('用户058'),	('用户059'),	('用户060'),	('用户061'),	('用户062'),	('用户063'),	('用户064'),	('用户065'),	('用户066'),	('用户067'),	('用户068'),	('用户069'),	('用户070'),	('用户071'),	('用户072'),	('用户073'),	('用户074'),	('用户075'),	('用户076'),	('用户077'),	('用户078'),	('用户079'),	('用户080'),	('用户081'),	('用户082'),	('用户083'),	('用户084'),	('用户085'),	('用户086'),	('用户087'),	('用户088'),	('用户089'),	('用户090'),	('用户091'),	('用户092'),	('用户093'),	('用户094'),	('用户095'),	('用户096'),	('用户097'),	('用户098'),	('用户099'),	('用户100');
INSERT INTO t_read([user_id], book_id, [page]) SELECT u.id, b.id, (u.id*b.id)%100+1 FROM t_user AS u, t_book AS b;

索引创建与删除

创建

CREATE INDEX idx_book_book_id ON t_read (book_id );
CREATE INDEX idx_book_user_book_id ON t_read ([user_id], book_id);

删除

DROP INDEX idx_book_user_book_id;
DROP INDEX idx_book_book_id;

注意：

• 最好使用U盘、机械硬盘来测试，如果使用固态硬盘，数据量要*10倍才能看出效果。我用的是机械硬盘。

汗，本机性能差到我测不下去了，有没有索引都一个样

当联合索引列A、B任意单独一个字段作为查询条件时索引无效

--没有索引
select * from t_read where book_id=187;  --100 rows returned in 204ms
select * from t_read where [user_id]=79;  --1600 rows returned in 378ms

--t_read表[user_id],book_id是联合索引
CREATE INDEX idx_book_user_book_id ON t_read ([user_id], book_id);
select * from t_read where book_id=187;  --100 rows returned in 204ms
select * from t_read where [user_id]=79;  --1600 rows returned in 378ms

DROP INDEX idx_book_user_book_id;
CREATE INDEX idx_book_user_book_id ON t_read (book_id, [user_id]);
select * from t_read where book_id=187;  --100 rows returned in 204ms
select * from t_read where [user_id]=79;  --1600 rows returned in 378ms

联合索引用于排序

--没有索引（id有索引，所以不按id排序）
select * from t_read where book_id=189 order by [page];  --100 rows returned in 248ms
select * from t_read where [user_id]=75 order by [page];  --1600 rows returned in 241ms
--t_read表[user_id],book_id是联合索引
CREATE INDEX idx_book_user_book_id ON t_read ([user_id], book_id);
select * from t_read where book_id=189 order by [page];  --100 rows returned in 112ms
select * from t_read where [user_id]=75 order by [page];  --1600 rows returned in 116ms --> 12ms

DROP INDEX idx_book_user_book_id;
CREATE INDEX idx_book_user_book_id ON t_read (book_id, [user_id]);
select * from t_read where book_id=189 order by [page];  --100 rows returned in 114ms --> 3ms
select * from t_read where [user_id]=75 order by [page];  --1600 rows returned in 117ms

速度对比

--没有索引
select * from t_read where book_id=189;  --100 rows returned in 214ms
select * from t_read where [user_id]=79;  --1600 rows returned in 241ms
select * from t_read where [user_id]=18 and book_id=756; --1 rows returned in 220ms
select * from t_read where book_id=131 and [page]=72; --1 rows returned in 181ms
select * from t_read where [user_id]=89 and [page]=89; --18 rows returned in 238ms
--没有索引的排序
select * from t_read where book_id=189 order by [user_id] desc;  --100 rows returned in 274ms
select * from t_read where book_id=189 order by [page] desc;  --100 rows returned in 268ms
select * from t_read where [user_id]=79 order by book_id;  --1600 rows returned in 238ms
select * from t_read where [user_id]=79 order by [page] desc;  --1600 rows returned in 271ms
select * from t_read where [user_id]=89 and [page]=89 order by book_id; --1600 rows returned in 262ms

--t_read表仅book_id字段有索引
select * from t_read where book_id=189;  --100 rows returned in 2ms  明显变快
select * from t_read where [user_id]=79;  --1600 rows returned in 97ms
select * from t_read where [user_id]=18 and book_id=756; --1 rows returned in 1ms
select * from t_read where book_id=456 and [page]=92; --1 rows returned in 1ms  明显变快
select * from t_read where [user_id]=89 and [page]=75; --18 rows returned in 99ms
select * from t_read where book_id=189 order by [user_id] desc;  --100 rows returned in 2ms  明显变快
select * from t_read where book_id=189 order by [page] desc;  --100 rows returned in 1ms 明显变快
select * from t_read where [user_id]=79 order by book_id;  --1600 rows returned in 252ms 反而变慢了
select * from t_read where [user_id]=79 order by [page] desc;  --1600 rows returned in 117ms
select * from t_read where [user_id]=89 and [page]=75 order by book_id; --18 rows returned in 110ms 反而变慢了

--t_read表book_id字段有索引, [user_id],book_id是联合索引
select * from t_read where book_id=189;  --100 rows returned in 58ms --> 1ms
select * from t_read where [user_id]=79;  --1600 rows returned in 92ms --> 5ms
select * from t_read where [user_id]=18 and book_id=756; --1 rows returned in 101ms --> 1ms
select * from t_read where book_id=456 and [page]=92; --1 rows returned in 95ms --> 1ms
select * from t_read where [user_id]=89 and [page]=75; --18 rows returned in 101ms --> 2ms
select * from t_read where book_id=189 order by [user_id] desc;  --100 rows returned in 113ms --> 1ms
select * from t_read where book_id=189 order by [page] desc;  --100 rows returned in 112ms --> 1ms
select * from t_read where [user_id]=79 order by book_id;  --1600 rows returned in 117ms --> 6ms
select * from t_read where [user_id]=79 order by [page] desc;  --1600 rows returned in 117ms  --> 13ms
select * from t_read where [user_id]=89 and [page]=75 order by book_id; --18 rows returned in 110ms --> 1ms

原文链接：/2020-10-31-mssqlFast.html

打赏