5. MySQL基于索引的优化

建表语句如下：

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;

id	k	s
100	1	‘aa’
200	2	‘bb’
300	3	‘cc’
500	5	‘ee’
200	6	‘ff’
200	7	‘gg’

一. 索引覆盖

生成的索引树入下图

如果执行：select * from T where k between 3 and 需要执行几次树的搜索操作，会扫描多少行？
该语句执行流程如下：

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，不满足条件，循环结束。

如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 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

以姓名(name)和年龄(age)简历联合索引如下图：

(name,age索引图)

1. 如果只根据name查询那么，可以复用该聚合索引。不需要再在name上单独建索引了。
   第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。
2. 那么，如果既有联合查询，又有基于 name、age各自的查询呢？查询条件里面只有 age 的语句，是无法使用 (name,age) 这个联合索引的，这时候你不得不维护另外一个索引，也就是说你需要同时维护 (name,age)、(age) 这两个索引。
   这个时候就得考虑空间了。假设name 字段是比 age 字段大的 ，那我就建议你创建一个（name,age) 的联合索引和一个 (age) 的单字段索引。仅多了age索引树

三. 索引下推

比如执行以下语句：

mysql> select * from tuser where name like '张 %' and age=10 and ismale=1;

mysql5.6以后会在聚簇索引树中过滤掉age不满足条件的而不用回表查询，称为索引下推。如图：