索引(二)
《MysSQL 实战45讲》笔记
创建表T
CREATE TABLE T(
id INT(16) PRIMARY KEY,
k INT(16) NOT NULL,
s VARCHAR(16) NOT NULL DEFAULT '',
KEY `idx_k` (`k`))
ENGINE=INNODB;
插入测试数据
insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');
非主键索引执行顺序
如果执行 select * from T where k between 3 and 5
,需要执行几次树的搜索操作,会扫描多少行?
这条SQL查询语句的执行流程:
- 在k索引树上找到k=3的记录,取得 ID = 300;
- 再到ID索引树查到ID=300对应的R3;
- 在k索引树取下一个值k=5,取得ID=500;
- 再回到ID索引树查到ID=500对应的R4;
- 在k索引树取下一个值k=6,不满足条件,循环结束。
步骤2、4回到主键索引树搜索的过程,就是回表。这个查询过程读了k索引树的3条记录(步骤1、3和5),回表了两次(步骤2和4) 。
覆盖索引
如果执行的语句是select ID from T where k between 3 and 5
,这时只需要查ID的值,而ID的值已经在k索引树上了,因此可以直接提供查询结果,不需要回表(再到主键索引树上搜索)。也就是说,在这个查询里面,索引 k 已经“覆盖了”我们的查询需求,我们称为覆盖索引。
由于覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段。
例子
有一市民表 user 如下:
如果现在有一个高频请求,要根据市民的身份证号查询他的姓名,可以将身份证号和名字建立联合索引 (id_card_name)。它可以在这个高频请求上用到覆盖索引,不再需要回表查整行记录,减少语句的执行时间。
最左前缀原则
现在有一个出现频率不高的查询,按照市民的身份证号去查他的家庭地址,单独为一个不频繁的请求创建一个(身份证号,地址)的索引感觉有点浪费 ,但是总不能让它走全表扫描吧。
先说结论,按照市民的身份证号去查他的家庭地址 会直接走 联合索引 id_card_name。
例子
给市民表 user 建立 联合索引 name_age,索引树如下
索引项是按照索引定义里面出现的字段顺序排序的。即先按名字排序后再按照年龄排序。
假如SQL语句的条件是where name like ‘张%’
,那么可以使用上 联合索引 name_age找到 ID3,然后向后遍历,直到不满足条件为止。
最左前缀指的是联合索引最左N个字段,也可以是字符串索引的最左M个字符。
安排索引内的字段顺序
-
当已经有了 name_age 这个联合索引后,一般就不需要单独在 name 上建立索引了
-
联合查询,又有基于name、age各自的查询时,考虑的原则就是空间,name字段通常是比age字段大的 ,可以创建一个 name_age 的联合索引和一个 age 的单字段索引。