MySQL 笔记整理（5） --深入浅出索引（下）

笔记记录自林晓斌（丁奇）老师的《mysql实战45讲》

5） --深入浅出索引（下）

　　这次的笔记从一个简单的查询开始：

　　建表语句是这样的

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

　　如果要执行 select * from t where k between 3 and 5这条语句，需要执行几次搜索操作呢，会扫描多少行呢？由上面的建表及初始化语句我们很容易可以看出，表t上有id字段的主键索引，也有k字段上的非主键索引。数据中满足条件k在3和5之间的记录有两条，分别是 (300,3,'cc'),(500,5,'ee')。我们在k字段上创建了索引，所以在执行这条语句时，mysql就会使用这个索引。如果你看了我的话应该知道，k上的索引是非主键索引，而非主键索引存储的其实是主键的值。所以这条语句的执行流程大致是下面这个样子的。

1. 在k索引树上找到k=3的记录，取得id=300.
2. 再到id索引树查到id=300对应的记录。（第一次回表）
3. 在k索引树上找到k=5的记录，取得id=500.
4. 再到id索引树查到id=500对应的记录。（第二次回表）
5. 在k索引树取下一个值k=6，不符合条件，查询结束。

所以上面这条语句查询了3条记录k=3，5，6.回表了两次。

　　我们注意到一个细节，select * from t where k between 3 and 5这条语句查询的结果是*，也就是所有的字段的内容都会返回。而如果只使用k上的索引，则只能查询到id的值与k的值，并不能返回符合要求的所有字段的值。那么如果k上的索引查询的结果可以满足要求，是不是就不需要回表了呢?答案是肯定的。 如语句 select id from t where k between 3 and 5; id的值已经在k索引树上了，不需要回表就能返回结果。即索引“k”已经覆盖了我们的查询需求，我们称为覆盖索引。

　　由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

　　上面的例子中还有一个小细节需要注意一下：在引擎内部使用索引k其实读了三个记录，但对于mysql的server层来说，它就是找引擎拿到了两条记录。

最左前缀原则：

　　innodb使用b+树这种索引模型，由于b+树的索引结构，可以利用索引的"最左前缀"来定位记录。如你在‘name’字段上建立了索引，当你的语句是 where name like ‘张%’时是可以使用索引的。mysql会利用这个索引向后遍历，直到不满足条件为止。不止是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左n个字段，也可以是字符串索引的最左n个字符。索引建立联合索引的时候，如何安排索引内字段的顺序就很重要了。 有一个原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序就往往是应该优先采用的。

　　如果既有联合查询，又有基于a，b各自的查询呢？如果你建立的联合索引是（a,b），那么在这种情况下，只使用b来查询是没办法使用索引的。这种时候我们需要优先考虑的就是空间了。如（name，age）,name字段一般来说会比age字段占用更多的空间，那么我们建立一个（name，age）的索引再加上一个（age）的索引就好了。

索引下推：

　　还是上面的（name，age）索引为例，如果你查询的条件是 姓张，且年龄小于30岁的所有男生，那么你的查询语句应该这么写。 select * from t where name like '张%'  and age = 10 and ismale = true; 由于索引前缀的规则，只能使用‘张’来找到满足条件的记录，然后再判断查询的其他条件是否满足。在mysql5.6之前，每条满足条件 like '张%'的记录都会回表进行判断。mysql5.6之后引入了索引下推（index condition pushdown）,可以在索引遍历的过程中对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表的次数。举个例子来说明 对于记录 a（张一，40，男）,b（张二，25，男），c（张三，26，女）。没有索引下推时，a,b,c都需要进行回表判断。有索引下推以后，a记录中的age值为40，age字段在查询条件中，也在索引（name，age）中，触发了索引下推，不进行回表。b,c仍需要回表。

上篇问题答案：

　　对于普通索引k，重建时可以这么写：

　　alter table t drop index k; alter table t add index(k);

　　对于主键索引，可以这么写：

　　alter table t drop primary key; alter table t add primary key(id);

　　对于上面的重建索引的作法，说出你的理解。如果有不合时的，为什么?更好的作法是什么？

　　首先来回答一下为什么要重建索引？ 索引可能因为记录的删除，或者页分裂等原因，导致数据页有空洞，重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑，更省空间。

　　重建索引k的做法是合理的。但重建主键过程不合理。不论是删除主键还是创建主键，都会将整个表进行重建，所以连续执行这两条语句，相当于第一个语句白做了。这两条语句可以使用 alter table t engine =innodb来代替。

问题：

　　表结构如下所示：

create table `geek` (
  `a` int(11) not null,
  `b` int(11) not null,
  `c` int(11) not null,
  `d` int(11) not null,
  primary key (`a`,`b`),
  key `c` (`c`),
  key `ca` (`c`,`a`),
  key `cb` (`c`,`b`)
) engine=innodb;

　　由于历史原因， a和b需要做联合主键。那么既然主键包括了a,b这两个字段，又单独在c上创建了一个索引，索引就已经包含了三个字段了，为什么还要创建ca，cb索引呢？有人给出的理由是业务里有这样两个查询：

select * from geek where c=n order by a limit 1;
select * from geek where c=n order by b limit 1;

　　这个理由对吗？为了这两个查询，这两个索引是否都必须呢？为什么呢？