mysql索引解析(联合索引、最左前缀、索引覆盖、索引下推)

首先问问这些知识点自己是否清楚。

  • 索引的常见数据结构有哪些？（哈希表，有序数组，B+树），它们分别有怎样的特点？分别适合哪些应用场景？
  • 主键索引（也称聚簇索引）和非主键索引（也称辅助索引/二级索引）的概念是什么？回表的概念又是什么？
  • 什么是联合索引，什么是最左前缀原则？
  • 什么是覆盖索引？能否清楚的说出Index Condition Pushdown优化的原理？

1. 索引基础

索引对查询的速度有着至关重要的影响，理解索引也是进行数据库性能调优的起点，索引就是为了提高数据查询的效率。索引可以包含一个或多个列的值，如果索引包含多个列的值，则列的顺序也十分重要，因为MySQL只能高效地使用索引的最左前缀列。

2. 索引类型

用于提高读写效率的数据结构有很多，这里先介绍常见的3种，分别是：

  1. 哈希表
  2. 有序数组
  3. 搜索树（重点）

2.1 哈希索引

哈希表是一种以键-值（key-value）的方式存储数据的结构，我们只要输入待查找的值（即key），就可以找到其对应的值（即Value）。哈希的思路很简单，把值放在数组里，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置，即idx = Hash(key)。如果出现哈希冲突，就采用拉链法解决。

因为哈希表中存放的数据不是有序的，因此不适合做区间查询，适用于只有等值查询的场景。

2.2 有序数组

有序数组在等值查询和范围查询场景中的性能都非常优秀。用二分法就可以快速找到（时间复杂度为O(logN)）。但是如果要往中间插入一条数据，则必须挪动后面的所有记录，成本较高。因此，有序数组只适用于静态存储引擎，即数据表一旦建立后不再会修改。

2.3 B+树索引（InnoDB）

首先，得先好好理解什么是B+树！看单独介绍B树、B+树的文章，基于篇幅不在此赘述。简单的说，是因为使用B+树存储数据可以让一个查询尽量少的读磁盘，从而减少查询时磁盘I/O的时间。

在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。又因为前面我们提到的，InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。每一个索引在 InnoDB 里面对应一棵 B+ 树。

假设，有这样一张表：该表主键为ID，且还有一个字段为k，并在k上有索引。

CREATE TABLE T(
    id int primary key,
    k int not null,
    index (k)
)engine=InnoDB;

表中有5条记录，分别为R1~R5，(100,1)、(200,2)、(300,3)、(500,5)和(600,6)。则在InnoDB中的索引组织结构是这样的：

InnoDB

根据叶子结点的内容，索引类型分为主键索引和非主键索引。

  • 主键索引的叶子结点存的是整条记录，主键索引也被称为聚簇索引（clustered index）。
  • 非主键索引的叶子结点存的是主键的值，非主键索引也被称为二级索引（secondary index）/普通索引/辅助索引。

那么，基于主键索引和非主键索引的查询有什么区别？

  • 如果语句是 select * from T where ID=500，即主键查询，则只需要搜索ID这棵树。
  • 如果语句是 select * from T where k=5，即非主键索引查询，则需要先搜索k索引树，得到ID的值为500，再到ID索引树搜索一次。从非主键索引回到主键索引的过程称为回表。

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。而从存储空间的角度讲，因为非主键索引树的叶结点存放的是主键的值，那么，应该考虑让主键的字段尽量短，这样非主键索引的叶子结点就越小，非主键索引占用的空间也就越小。一般情况下，建议创建一个自增主键，这样非主键索引占用的空间最小。

3. 联合索引

联合索引是指对表上的多个列进行索引。下面以一个例子进行说明。假设有下面这样一张表，有这样一个需求，我们需要查询某个用户的购物情况，并按照时间进行排序，取出某用户近几次的购物情况。（注：例子来源于《MySQL技术内幕》）

// 表
CREATE TABLE buylog(
    userid int not null,
    buy_date DATE
)ENGINE=InnoDB;

// 插入数据
insert into buylog values(1, '2019-08-13');
insert into buylog values(2, '2019-08-14');
insert into buylog values(3, '2019-08-15');
insert into buylog values(1, '2019-08-11');
insert into buylog values(3, '2019-08-10');
insert into buylog values(1, '2019-08-12');

// 添加索引
alter table buylog add index(userid);
alter table buylog add index(userid, buy_date);
// （或用key关键字也一样的）
alter table buylog add key(userid);
alter table buylog add key(userid, buy_date);

上面的代码建立了两个索引，两个索引都包含了userid字段。

如果只对于userid进行查询，如：

select * from buylog where userid=2;

通过explain查看该语句的执行情况，如下，（explain的用法，简单了解）

image

可以看到，possible_keys在这里有两个索引可供使用，分别是userid索引和(userid,buy_date)联合索引。优化器最终选择的索引（即key）是userid，因为该索引的叶子节点只包含单个键值，所以理论上一页能存放的记录会更多（意味着可以减少查询的次数）。

接着假定要查询userid为1的最近两次的购买记录，如：

select * from buylog where userid=1 order by buy_date desc limit 2;

同样的，我们看一下它的执行过程是怎样的，如下：


image

可以看到，这一次查询优化器选择的索引是userid_2（也就是(userid, buy_date)联合索引）。为什么呢？因为在这个联合索引中，记录已经分别根据userid和buy_date排好序了，利用这个索引则可以直接取出相应的数据而无需再对buy_date额外做一次排序操作了。如果强制使用userid索引，则它的执行计划如下：

image

从Extra字段可以看出，该语句的执行需要使用fliesort，也就是需要一次额外的排序操作才能完成查询。显然，这个排序就是对buy_date字段的排序，因为这里仅使用了userid索引，该索引未对buy_date进行排序。

总结：

联合索引(a, b)是根据a, b进行排序（先根据a排序，如果a相同则根据b排序）。因此，下列语句可以直接使用联合索引得到结果（事实上，也就是用到了最左前缀原则）：

select ... from xxx where a=xxx; 
select ... from xxx where a=xxx order by b;

而下列语句则不能使用联合查询：

select ... from xxx where b=xxx;

对于联合索引(a, b, c)，下列语句同样可以直接通过联合索引得到结果：

select ... from xxx where a=xxx order by b;
select ... from xxx where a=xxx and b=xxx order by c;

而下列语句则不行，需要执行一次filesort排序操作。

select ... from xxx where a=xxx order by c;

4. 最左前缀原则

对于有很多字段的一张表，查询的方式是多样的，难道要为了每一种可能的查询都定义索引吗？这样岂不是很浪费空间，毕竟建索引也是需要一些空间的。事实上，B+ 树这种索引结构，可以利用索引的“最左前缀”原则来定位记录，避免重复定义索引。

以下面的例子进行说明什么是“最左前缀原则”。


最左前缀原则

假设建立了一个联合索引(name,age)，可以看到，索引项是按照索引定义里面出现的字段顺序排序的，先根据名字排序，名字相同的就根据年龄排序。

当你的逻辑需求是查到所有名字是“张三”的人时，可以快速定位到 ID4，然后向后遍历得到所有需要的结果。

如果你要查的是所有名字第一个字是“张”的人，你的 SQL 语句的条件是"where name like ‘张%’"。这时，你也能够用上这个索引，查找到第一个符合条件的记录是 ID3，然后向后遍历，直到不满足条件为止。

可以看到，不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

因此，基于最左前缀原则，我们在定义联合索引的时候，考虑如何安排索引内的字段顺序就至关重要了！评估的标准就是索引的复用能力，比如，当已经有了(a,b)字段的索引，一般就不需要再单独在a上建立索引了。

5. 覆盖索引

还是利用“2.3 B+树索引”提到过的表，如果执行的语句是：

select * from T where k between 3 and 5;

则这条SQL语句的执行流程如下：

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

在这个过程中，回到主键索引树的过程，称为回表。在这个例子中，由于查询的结果是所有字段，所需要的数据只有主键上才有，所以不得不回表。但如果执行的语句是下面这样的，注意！这里查询的结果只是“ID”（恰好是主键），而不是所有字段了。

select ID from T where k between 3 and 5;

由于查询的值是ID，而ID的值已经在k索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里，索引k已经“覆盖了”我们的查询需求，故称为覆盖索引。

除了上面这种情况，针对某些统计问题时，覆盖索引也能发挥用处。还是以上面的例子，执行如下语句来统计表的记录总数（在此我们假设这张表数据量特别特别大，需要多次磁盘IO）：

select count(*) from T;

如果没有对字段k设置索引，那么只能是通过聚簇索引来计算；如果对字段k设置了索引，那么，由于聚簇索引的叶结点存放的是整行记录的所有信息，而辅助索引的叶结点只存放主键，两者相比，对于一页内存，显然辅助索引能够存放的节点更多，意味着辅助索引可以减少IO次数，从而更快的计算出count(*)的值。

验证如下：

没有对字段k设置索引时，优化器会选择聚簇索引进行操作（即key为PRIMARY）。


image

对字段k设置了索引时，优化器会选择辅助索引进行操作（即key为k）


image

可见，如果建立了辅助索引，在有些场景下，优化器会自动使用辅助索引从而提升查询效率。

总结：覆盖索引就是从辅助索引中就能直接得到查询结果，而不需要回表到聚簇索引中进行再次查询，所以可以减少搜索次数（不需要从辅助索引树回表到聚簇索引树），或者说减少IO操作（通过辅助索引树可以一次性从磁盘载入更多节点），从而提升性能。

6. 索引下推

什么是索引下推（Index Condition Pushdown，ICP）呢？假设有这么个需求，查询表中“名字第一个字是张，性别男，年龄为10岁的所有记录”。那么，查询语句是这么写的：

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

根据前面说的“最左前缀原则”，该语句在搜索索引树的时候，只能匹配到名字第一个字是‘张’的记录（即记录ID3），接下来是怎么处理的呢？当然就是从ID3开始，逐个回表，到主键索引上找出相应的记录，再比对age和ismale这两个字段的值是否符合。

但是！MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表字数。

下面图1、图2分别展示这两种情况。


image

（图1）

image

（图2）

图 1 中，在 (name,age) 索引里面我特意去掉了 age 的值，这个过程 InnoDB 并不会去看 age 的值，只是按顺序把“name 第一个字是’张’”的记录一条条取出来回表。因此，需要回表 4 次。

图 2 跟图 1 的区别是，InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。在我们的这个例子中，只需要对 ID4、ID5 这两条记录回表取数据判断，就只需要回表 2 次。

总结：如果没有索引下推优化（或称ICP优化），当进行索引查询时，首先根据索引来查找记录，然后再根据where条件来过滤记录；在支持ICP优化后，MySQL会在取出索引的同时，判断是否可以进行where条件过滤，也就是说提前执行where的部分过滤操作，在某些场景下，可以大大减少回表次数，从而提升整体性能。

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 203,362评论 5赞 477
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 85,330评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 150,247评论 0赞 337
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,560评论 1赞 273
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,580评论 5赞 365
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,569评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,929评论 3赞 395
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,587评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,840评论 1赞 297
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,596评论 2赞 321
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,678评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,366评论 4赞 318
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,945评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,929评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,165评论 1赞 259
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 43,271评论 2赞 349
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,403评论 2赞 342