MySQL 优化&索引原理面试

1,493 阅读13分钟

查询计划 explain

面试官:我看你简历上写熟悉 SQL 优化,你们工作中是怎么优化的?

我们会开启慢查询日志,捕捉到慢的 SQL,先用 explain 看下查询计划,主要看 type、possible_keys、key、rows、extra 这几列,然后进行相应优化。

面试官:那你具体说说这几列吧

type 是这条 SQL 性能的重要参考指标,一般来说我们至少要求是 index 级别,其次看 possible_keys、key。看下可能用到的索引,实际有没有用到,如果没有用到再具体分析原因。

如果本身就没有可用的索引,那就很简单了,正常建索引就行了。

如果该用的索引用了,执行计划也没问题,那么就看扫描行数。如果扫描行数特别多,这个就要慢慢改 SQL 去尝试优化扫描行数(这个需要很强的功底),或者考虑数据分表分库了。最后看 extra 有没有额外耗时操作,比如文件内排序等。

其实归根结底就是看查询有没有用到索引,如果用到了索引还慢就优化扫描行数

面试官:那你说下 type 有哪几种值,代表什么意思

下面这张表是最常见的几种,从上到下,性能依次变差:

代表含义
const表示通过主键索引一次就查到了记录,由于只匹配一行记录,所以性能非常好
eq_ref一般 join 出现,表示左边表的每一行,右边表都有唯一一条记录与之对应,例如用户账号表和用户信息表 用 userId 关联
ref虽然使用了索引,但该索引列的值并不唯一,有重复。这样即使使用索引快速查找到了第一条数据,仍然不能停止,要进行目标值附近的小范围扫描
range根据索引列进行范围扫描,比如 where 中出现 "<" 、">"、"between and"
index根据索引进行有顺序的全表扫描,与 all 相比,它按照索引的顺序进行全表扫描
all全表扫描,需要扫描所有行记录

那 index 和 all 的区别是什么呢?网上很多教程说 index 是只扫描索引树,不访问具体行记录,其实这是错误的。 index 和 all 一样,也会进行全表扫描,只是 MySQL 会按索引次序进行全表扫描,而不是直接扫描行数据。也就是说它是有顺序的磁盘 IO。只有当 extra 列显示了 using index 时,这才表明它只扫描了索引树,因为 using index 的含义是 MySQL 正在使用覆盖索引,所需要的的结果直接从索引树就能获取,不需要扫描具体行记录。

索引的优势劣势

面试官:你刚刚说优化归根结底就是看索引,为什么有索引就查询快?

因为索引就是一种用于提升查询速度的数据结构,我们用的是 InnoDB 存储引擎,它把所有记录的索引字段以一种 B+TREE 的数据结构存储,它拥有二叉树的一些特性,查询很快。如果没有索引,它需要在磁盘中每个页都要扫描,有了索引之后就可以选择性扫描页。将随机磁盘 IO 变成了有选择性的磁盘 IO。进行的磁盘 IO 次数少了很多。

面试官:照你这么说那我每个字段都建索引是不是很好呢?

不是的,索引是一颗逻辑上的 B+TREE,需要维护,当对表中的数据进行增删改的时候,索引树也要动态维护(变化)。这样会降低数据增删改的速度,所以索引不能无脑建。

面试官:那你说说哪些字段适合建立索引,哪些字段不适合建立索引

如果这一列未来重复的数据很少,就适合建立索引,如果一列数据重复率很高就不适合。比如用户手机号、订单表订单编号等就适合建索引。而用户性别、订单状态就不适合。

面试官:为什么呢?

这个就要从索引的数据结构 B+TREE 来说了。如果索引列没有重复的值,那在索引树搜索的时候查询到目标之后不会再进行搜索。如果有大量相同值,那么很多叶子节点磁盘块里面都是相同值,那么在搜索到目标之后还需要继续搜索(继续查询其他磁盘块,进行 IO 操作)(下面会详细说 B+TREE)

索引类型&回表

面试官:你说说 MySQL 有哪几种索引

MySQL 中的索引可分类:

image.png

除了主键索引,其他索引都是非聚集索引。聚集索引它这颗索引树不仅存储索引字段值,还在叶子节点存储了完整的行记录。而非聚集索引,它的索引树叶子节点存储的是主键值,而不是具体行记录。所以使用非聚集索引查询时,原理是先查到主键值,然后根据主键值去聚集索引的那颗树查询完整行记录,这个过程也叫作回表

面试官:如果表没有主键,还有没有聚集索引?

(啊这……一般不会没有主键吧)如果没有主键,MySQL 会选择一个唯一且非空键作为聚集索引,如果没有唯一键,MySQL 会隐式定义一个主键来作为聚集索引。所以一张表一定会有聚集索引

索引底层原理 B+TREE

面试官:我看你对索引挺熟悉的,你能详细说说 B+TREE 吗?

我们可以去这个网站来感受一下 B+TREE 这个数据结构,这里我选择 7 阶(实际上 MySQL 里面实现的应该是上百阶),插入 42 个主键值。可以观察这个树的变化,就是我们程序中向数据表不断新增数据之后,索引树的变化,也叫做页的分裂

image.png

这里每一个矩形都是一个磁盘块,也叫作页。图中带箭头的页是叶子节点所在页。值得注意的是,主键 B+TREE 只有叶子节点是存储完整数据的。非叶子节点存储的是主键值和指向其他节点的指针。注意,官网介绍在 MySQL 的实现中,叶子节点所在的页之间的指针是双向的,其实这从倒序排序就很容易看出来。

刚刚我们说了,叶子节点才会存储完整的行记录。以上图右边部分,可以通过下图来理解:

image.png

面试官:你说下主键索引查询数据的过程

当执行查询时,并不会直接找到对应的记录,而是通过主键找到对应记录所在的页,把这一页从磁盘读入内存,在内存中查找。比如我们要查询 id = 42 的记录,根据树的特性从根节点一直找,最终找到了 42 存在于最右边这一页。然后把这一页的数据读入内存,在内存中遍历找到 42 。

这里的 data 就是表中一行完整数据。这里可以看出页与页之间是双向链表的数据结构,其实在页内部,具体的行记录也是链表的数据结构,不过行记录 data 之间是单链表。

面试官:在 MySQL 中实现的是几百阶的 B+TREE ,那么一个页就是几百个元素组成的单链表,因为单链表的查询,我们必须从头开始遍历,这样检索效率不会很低吗?

其实这里并没有画完整页包含的全部内容,在 MySQL 的页中有一个页目录来维护页中全部的主键值和对应的行记录,这个页目录中一个 slot 对应一个分组,一个分组包含几条 data,在内存中查找的时候会根据页目录做二分查找,然后确定对应的分组,在分组中遍历单链表。一个分组里面就几条数据,又因为在内存中,这样花费的时间是可以忽略的。

具体图示:

面试官:对于 MySQL 存储来说,B+TREE 和 BTREE 相比好在哪里?

B+TREE 和 BTREE 的区别是 B+TREE 只有叶子节点存储行记录,而 BTREE 每个节点(页)都存储数据,我们知道 MySQL 一页的磁盘大小都是 16KB,存储具体行数据会占用磁盘空间,所以 B+TREE 一个非叶子节点的页能够存储更多的主键,这样一来树的高度就相对比较低,查询的时候进行的磁盘 IO 操作就少,性能就高。其次由于B+树的叶子节点都是连接在一起的,所以范围查找、排序更加方便

再看不适合建立索引的字段

前面已经说了性别(0 代表女,1 代表男)字段不适合建索引,假设我们建立了索引就会像下面这样:

image.png

你看我现在要用这个索引查 sex = 0 的数据,要扫描多少磁盘块。这个索引就失去了意义

再看回表

前面已经说过了回表的概念,这里再通过具体图来理解一下

image.png

非主键索引的 B+TREE 和主键索引的 B+TREE 几乎是一样的,唯一不同的是非主键索引的 B+TREE 叶子节点存储的是主键值。这里偷个懒,就不画其他图了(你可以把下面这棵树的非主键索引想象成手机号)。假如使用手机号查询完整记录,它先在非主键索引的 B+TREE 查到了目标手机号对应的主键是 42,然后再根据 42 去主键索引的 B+TREE 按上面说的过程查询到完整的目标记录。你可能已经发现了,首先要进行非主键索引的 B+TREE 磁盘 IO,然后还要回表进行主键索引的 B+TREE 磁盘 IO,所以回表能避免就避免。

联合索引&最左前缀匹配

面试官:你们建立联合索引的时候是怎样决定顺序的?查询的时候又是怎样用联合索引字段的?

创建联合索引时按识别度从高到低排列,就是说几乎不重复的放在最左边,稍微有重复数据的放中间,重复数据比较多的放最右边,原理就是上面所说的 sex 性别字段不适合建立索引。查询的时候必须要用到最左边的索引字段,这个也叫做最左前缀匹配规则。

面试官:为什么不遵守最左前缀匹配就不能使用到联合索引?

前面我们说过联合索引属于非聚集索引,它也有自己的 B+TREE。以这张表为例,

idabcd
1111data
2222data
3322data
4322data
5235data
6644data
7455data
8888data

我们建立联合索引(a,b,c),在这颗 B+TREE 中,它是类似这样的:

image.png

首先它是按照第一个字段 a 排序的,a 相同的情况下,按照 b 排序,a,b 都相同的情况下,按照 c 排序。也就是说撇开 a 之后,b,c 都是没有顺序的,我们想要使用这个索引的某个字段,必须得给定它左边的那个字段。所以很明显最左边的字段是必须要有的,否则无法根据索引顺序去查询。这就是最左前缀匹配。

面试官:你这 a,b,c 都是整数类型的,如果是字符串类型的怎么排序呢?

打开 Navicat 在设计表的界面,选中字符串类型字段,有个排序规则 Collation ,它会根据我们指定的排序规则区分大小。

索引覆盖

面试官:你之前说使用非主键索引需要回表查询多次,非主键索引一定会回表查询吗?

不一定的,有一种情况是索引覆盖,这种情况不会回表查询。以上面联合索引那张表为例,假如我们查询的 SQL 是这样的:

SELECT a,b,c FROM test

由于我们查询的三个字段正好是联合索引的几个字段,所以它直接从联合索引那颗 B+TREE 就能直接查到了,然后返回给客户端。不需要再回表查询。所以开发中如果可以的话尽量把查询字段覆盖到索引,这样能显著提升 SQL 性能。

索引失效场景

面试官:你知道哪些场景会导致索引失效吗?

可以归为以下三种:

  • 没有遵循最左前缀匹配规则
  • 索引列上使用函数
  • 索引的效率没有全表扫描高

具体的话可以随便列几条

  • where 条件出现对索引字段 NULL 值判断
  • where 条件出现索引字段 "!="
  • where 条件出现索引字段 "or"
  • where 条件出现索引字段 like "%xx%"
  • where 条件出现对索引字段进行函数操作和表达式连接,例如 to_days( t1.update_time ) > 'xxx'
  • 假如一张表总共只有 10 条数据,那么这 10 条数据无论建不建索引最终都会被一起从磁盘块读到内存,所以这种情况下可能会发生优化器不会使用索引

以上都可能会造成索引失效,变为全表扫描。如果熟悉了索引的原理,上述失效原因都很简单,这里就不细说了

ICP&MRR

面试官:你之前说你们线上用的 MySQL 是 5.7 的版本,你知道 5.6 版本做了哪些优化吗?

比较重要的有两个,我们可以使用下面这条 SQL 查询相关优化的开启状态,默认这两个是开启的

SELECT @@optimizer_switch
  • MRR(Multi-Range Read) 这个东西官网说了一堆我其实也没怎么看明白……只知道最终效果是减少磁盘的随机访问,将随机 IO 变为 顺序 IO。不过官网说了只有需要查询行记录的时候才会使用到 MRR,如果使用了 MRR,explain 的 extra 列会出现 using MRR。
  • ICP(Index Condition Pushdown) 翻译过来叫做索引条件下推,官网的例子已经说得很清楚了,假设有 zipcode,lastname,address 联合索引的一张表 people。执行下面这句 SQL
SELECT * FROM people WHERE zipcode='95054'AND lastname LIKE '%etrunia%' AND address LIKE '%Main Street%';

如果没有索引下推优化,它会先扫描所有符合 zipcode = '95054' 的索引,然后回表查询数据,最后再进行后面两个条件的筛选。这样会从磁盘中扫描出很多不符合全部条件的数据,然后去过滤。有了索引下推优化之后,它会在扫描联合索引的时候就判断后面两个字段是否符合条件,如果不符合就剔除掉,这样一来最后需要回表查询的数据就都是全部符合条件的数据了。相当于我们把 where 所有条件都放在了存储引擎层面。

注意索引下推要生效, where 条件字段必须要覆盖到索引。

结语

如果这篇文章对你有帮助,记得点赞加关注。你的支持就是我继续创作的动力!