由上一篇可以了解到，索引分为主键索引（聚簇索引）和 非主键索引（二级索引），那么如何有效地利用这些索引呢？
先考虑这么一个表结构：
mysql> create table T (
ID int primary key,
k int NOT NULL DEFAULT 0,
s varchar(16) NOT NULL DEFAULT '',
index 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');
可以看到，表 T 中，ID为主键索引，k为二级索引。
然后考虑这么一个查询语句：
select * from T where k=5;
那么这条SQL语句的查询流程就是：
1、在 k 索引树上找到 k=5 对应的 ID
2、然后再到 ID 索引树上找到整行记录
这个回到主键索引树上搜索的过程，就叫做回表。
但是要注意，并不是所有根据二级索引查询的语句，都有这个回表过程。此处回表，是因为 k 索引树对应的记录没有存需要查询的记录（select *）
覆盖索引
如果查询的语句变成：
select ID from T where k=5;
那么就避免了这个回表的过程，因为 ID 的值已经在 k 索引树上了。 也就是说，在这个查询中，索引k 已经覆盖了查询的需求，称之为覆盖索引。
由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段
接下来考虑这么一个场景，有一张市民表，定义如下：
CREATE TABLE `tuser` (
`id` int(11) NOT NULL,
`id_card` varchar(32) DEFAULT NULL,
`name` varchar(32) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`ismale` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `id_card` (`id_card`),
KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB
然后讨论这么一个问题：已经在身份证（id_card）上建立了索引的情况下，是否有必要建立一个 （身份证号，姓名）的联合索引？
如果，现在有这么个高频请求，要根据市民的身份证号查询姓名，那么这个查询就是有意义的。 因为这个高频请求用到了覆盖索引。
但是，这种覆盖索引也是有代价的，只根据 身份证号 建立的索引，肯定比 （身份证号，姓名）建立的索引，占用的空间要小。
最左前缀原则
B+树的索引结构，满足最左前缀原则。
这里用 (name, age) 这个联合索引做分析：
image.png
可以看到，索引项是按照索引定义里面出现的字段顺序排序的。
当搜索语句的 where 条件，能从左到右的满足联合索引的顺序时，就可以利用到这个联合索引。
比如，(name, age) 这个联合索引，就能满足：
name="abc" and age=10 的查询
name="abc" 的查询
但是，单独的 age=10 的查询是不满足这个联合索引的搜索条件的。
另外，最左前缀原则也满足字符串匹配。字符串的大小也在 B+ 树的比较大小范围内。
了解了什么是最左前缀原则，现在来探讨一个问题：
在建立联合索引的时候，如何安排索引内的字段顺序？
这里的评估标准是，索引的复用能力。 因为支持最左前缀原则，当已经有了 (a,b) 这个联合索引，就不需要单独为 a 建立索引了。 所以，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要有限考虑的
那么，如果对于字段 a 和字段 b ，既有联合查询，又有基于a、b各自的查询呢？
那么需要考虑的原则就是空间了。 如果 a 是字符串，b是个int 型。那么在 a 上建立的索引树肯定会大于 b 的。 那么就考虑建立 (a,b) 和 b 两棵索引树，这样就避免了单独为 a 建立索引树而占用更大空间的情况。
索引下推
在 MySQL5.6 后，引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
网友评论