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

时间：2023-03-08 15:53:54

笔记记录自林晓斌（丁奇）老师的《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;

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

相关文章