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;

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