SQLite数据表数量大时建索引的方法,SQLite索引知识归纳

数据库相关 / SQLite
字数统计: 2k   |   阅读时长≈ 9 分钟

@TOC

SQLite虽然是本地数据库，但总有些表数据量达到几万的数量，这样一但和其它表联合查询再按没有索引的字段排序就会慢。

先看结论，再看实践过程

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

准备样例数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
--书本表
CREATE TABLE `t_book` (
	`id`	INTEGER PRIMARY KEY AUTOINCREMENT,
	`name`	TEXT,
	`author`	TEXT
);
--用户表
CREATE TABLE `t_user` (
	`id`	INTEGER PRIMARY KEY AUTOINCREMENT,
	`name`	TEXT
);
--阅读记录表
CREATE TABLE `t_read` (
	`id`	INTEGER PRIMARY KEY AUTOINCREMENT,
	`user_id`	INTEGER,
	`book_id`	INTEGER,
	`page`	INTEGER,
	`addTime`	INTEGER DEFAULT (strftime('%s','now'))
);
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, ABS(RANDOM())%100 FROM `t_user` AS u, `t_book` AS b;

索引创建与删除

创建

1
2
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`);

删除

1
2
DROP INDEX `idx_book_user_book_id`;
DROP INDEX `idx_book_book_id`;

注意：

  • 没有使用外键，是因为没搞懂SQLite没有对数据进行外键约束，并且与单独建索引一样占用储存空间，速度也没什么区别。
  • 最好使用U盘、机械硬盘来测试，如果使用固态硬盘，数据量要*10倍才能看出效果。我用的是机械硬盘。

    当联合索引列A、B时第一个字段作为单独的查询条件依然有索引

1
2
3
4
5
6
7
8
9
10
11
12
13
--没有索引
select * from t_read where book_id=189;  --100 rows returned in 58ms
select * from t_read where user_id=79;  --1600 rows returned in 92ms

--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;  --100 rows returned in 59ms
select * from t_read where user_id=79;  --1600 rows returned in 92ms --> 5ms

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;  --100 rows returned in 58ms --> 2ms
select * from t_read where user_id=79;  --1600 rows returned in 97ms

联合索引用于排序

1
2
3
4
5
6
7
8
9
10
11
12
--没有索引（id有索引，所以不按id排序）
select * from t_read where book_id=189 order by page;  --100 rows returned in 114ms
select * from t_read where user_id=75 order by page;  --1600 rows returned in 116ms
--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

速度对比

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
--没有索引
select * from t_read where book_id=189;  --100 rows returned in 58ms
select * from t_read where user_id=79;  --1600 rows returned in 92ms
select * from t_read where user_id=18 and book_id=756; --1 rows returned in 101ms
select * from t_read where book_id=456 and page=92; --1 rows returned in 95ms
select * from t_read where user_id=89 and page=75; --18 rows returned in 101ms
--没有索引的排序
select * from t_read where book_id=189 order by user_id desc;  --100 rows returned in 113ms
select * from t_read where book_id=189 order by page desc;  --100 rows returned in 112ms
select * from t_read where user_id=79 order by book_id;  --1600 rows returned in 117ms
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字段有索引
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-29-sqliteFast.html

打赏
  • 版权声明： 本博客所有文章除特别声明外，著作权归作者所有。转载请注明出处！

撸代码不易，给点物质上的支持吧～

支付宝
微信