索引（二）

索引（二）

《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查询语句的执行流程：

1. 在k索引树上找到k=3的记录，取得 ID = 300；
2. 再到ID索引树查到ID=300对应的R3；
3. 在k索引树取下一个值k=5，取得ID=500；
4. 再回到ID索引树查到ID=500对应的R4；
5. 在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 的单字段索引。