读mysql45讲-索引续

有一个表的创建语句如下：

 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')

在执行select * from t where k between 3 and 5 的大概过程：

1. 先根据k索引树找到k=3的索引项
2. 然后根据对应的ID值=300回表查询整行数据
3. 接着在k索引树继续寻找下一个值，得到下一个k=5的索引项
4. 得到k=5对应的ID=500，再次回表查询整行数据
5. 继续在k索引树上寻找下一个值，发现下一个k=6 不符合条件了，所以停止查询，也不会进行回表的操作。

之前提到过：表中的数据都是按照主键的数据来存放的，所以需要查询表中的数据时候必须回表，那么有没有什么情况是不需要回表的了？

覆盖索引

如果上面的查询语句是 select ID from t where k between 3 and 5 , 也就是只需要查询ID的值，不需要整行的数据，而ID的值其实已经存在于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

因为身份证号码是可以标识出一个人的，所以经常有用身份证号码去查询个人信息的需求。

假设现在有个高频请求，就是用身份证号码去查询姓名；
那sql差不多就是select name from tuser where id_card = xxxx;因为name不是主键，不会存在id_card索引树上，就需要回表的步骤；
那如果为了减少回表，我们将name和id作为联合主键就有点太牵强了，因为不可能每个高频请求需要的字段都给作为联合主键。

所以这个时候就可以将id_card和name建立一个联合索引；

索引下推

在查询姓名的要求上再进一步，查询名称中包含表的姓名,身份证号码是32开头；
select name from tuser where substring(id_card,1,2) = 32 and name like ‘%表%’

在mysql5.6之前，在根据 substring(id_card,1,2) = 32找到符合条件的ID之后，会再次回表，查询name字段然后再判断是否包含表这个字；

在mysql5.6之后，在根据 substring(id_card,1,2) = 32找到符合条件的ID之后，则会直接判断name字段是否包含表这个字；

最左前缀原则

基于上面建立联合索引的结果，那如果之后还有根据身份证号码查询疫苗接种情况，根据身份证号码查询贷款次数等等背景，那为了减少回表的操作，难道都建立一个联合索引吗，那索引就太多了；
可以看到上面已经加上了一个(name,age)的索引：

可以看到索引项是按照索引定义的字段顺序来排序的。
也就(name,age)这个联合索引是name在前，age在后的；所以索引项中也都是name在前，age在后；索引项也就是先根据name排序，然后再根据age排序，因为name=张三有多个，所以再内部按照age排序；

所以当我们执行select 贷款次数 from table where name like '%海绵宝宝%'的时候，也是会走(name,age)这个索引的；
但是如果是执行select 贷款次数 from table where age >40 就不会走(name，age)这个索引的；
这就是最左前缀原则，只要满足最左前缀原则，就可以利用索引来加速查询。

联合索引是(a,b)；但是查询条件中只有b的限制条件，是不会走联合索引的，这个时候要是想走索引，就得单独维护一个(b)的索引；
但是如果建立的联合索引是(b,a)，查询条件中只有b的限制条件，那就是会走查询的，就可以少维护一个索引，节省了空间。

如果通过调整顺序，可 第 以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。