mysql执行计划成本分析和慢查解决方案

在用explain看某一条sql的执行计划的时候，有些索引的执行结果跟我们期望不一致，那不一致的原因是什么呢？mysql底层判断要走哪个索引的依据是什么？是基于什么成本考虑来走哪个索引？以及在了解这些原理之后，我们是否有一套针对mysql慢查治理的一般思路和解决方案？这就是本文存在的原因，也是自己在项目中总结的一些经验和沉淀。在讲执行计划成本和慢查解决方案之前，先对一些关系技术——mysql的索引基础知识做个简单概述。 

  索引是加快查询速度的核心技术，也是治理慢查的关键思路。但是有一些疑难点，很多人都是一知半解，特别是索引树上存储的数据内容比较模棱两可，现在针对这些疑难点做一个总结。

一 索引分类和索引树数据组成

索引，是基于B+数的数据结构存储数据的，主要有聚集索引，辅助索引和覆盖索引。

1-聚集索引

这里就是主键索引，每个所以都是按照主键ID进行索引的数据，在索引的叶子节点数据组成=主键ID+该行的所有数据。

比如订单表order_info的前两行记录为：

CREATE TABLE `order_info` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT NOT NULL COMMENT '主键',
  `order_no` varchar(32) UNSIGNED NOT NULL DEFAULT '' COMMENT '订单号',
  `user_id` BIGINT UNSIGNED NOT NULL DEFAULT '0' COMMENT '用户ID',
  `money` BIGINT UNSIGNED NOT NULL DEFAULT '0' COMMENT '金额',
  PRIMARY KEY (`id`),
  UNIQUE KEY `order_no` (`order_no`),
  KEY `order_uid` (`order_no`,`user_id`)
) ENGINE=InnoDB CHARSET=utf8mb4 COMMENT='订单表';

id（主键ID）	order_no(订单号)	user_id（用户ID）	money(金额)
1	E202111	11	100
2	E202112	22	200

此时主键ID包含的索引数据=id+（order_no+user_id+money），数据组成如下图所示

2-辅助索引

该索引的特点是，索引数据处理包含索引字段，还包含主键ID。比如上面订单表的辅助索引key =order_uid，除了包含其自身字段order_no和user_id外，还包含主键id，如下图所示：

3-覆盖索引

覆盖索引，是辅助索引中的一种，理解好“覆盖”二字是关键：SELECT时需要的数据，如果在索引数据里都存在，那么就不需要回表再查询其他字段信息，这样的索引叫做覆盖索引。

比如拿上面的订单表为例：

select order_no,user_id from order_info where order_no='E202111'

这时只需要查 order_no和user_id 的值，而 这两个值已经在 key=order_uid索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 order_uid 已经“覆盖了”我们的查询需求，我们称为覆盖索引。

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

二-走上索引原则-最左匹配原则

B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。为了直观地说明这个概念，我们用（order_no，user_id）这个联合索引来分析。

可以看到，索引项是按照索引定义里面出现的字段顺序排序的。

 这样，比如下面的语句，因为没有走上最左的order_no的索引，最终没有走上key=order_uid的索引。原因也很简单，在联合索引的情况下，数据是按照索引第一列排序，第一列数据相同时才会按照第二列排序。也就是说，如果我们想使用联合索引中尽可能多的列，查询条件中的各个列必须是联合索引中从最左边开始连续的列。如果我们仅仅按照第二列搜索，肯定无法走索引

select * from order_info where user_id=22 

三-不是所有针对索引列的查询都能用上索引

在上一个案例中，我创建了一个 order_no+user_id 的联合索引，仅搜索 order_no 时就能够用上这个联合索引。这就引出两个问题：

1)是不是建了索引一定可以用上？

2)怎么选择创建联合索引还是多个独立索引？
首先，建立一个新表student，如下：

CREATE TABLE `person` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `score` int(11) NOT NULL,
  `create_time` timestamp NOT NULL,
  PRIMARY KEY (`id`),
  KEY `create_time` (`create_time`),
  KEY `name_score` (`name`,`score`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

通过下面的存储过程循环创建 10 万条测试数据，我的机器的耗时是 140 秒（本文的例子均在 MySQL 5.7.26 中执行）：

CREATE DEFINER=`root`@`%` PROCEDURE `insert_person`()
begin
    declare c_id integer default 1;
    while c_id<=100000 do
    insert into person values(c_id, concat('name',c_id), c_id+100, date_sub(NOW(), interval c_id second));
    set c_id=c_id+1;
    end while;
end

现在，我们通过几个案例来分析一下索引失效的情况。

第一，索引只能匹配列前缀。比如下面的 LIKE 语句，搜索 name 后缀为 name123 的用户无法走索引，执行计划的 type=ALL 代表了全表扫描：

EXPLAIN SELECT * FROM person WHERE NAME LIKE '%name123' LIMIT 100

把百分号放到后面走前缀匹配，type=range 表示走索引扫描，key=name_score 看到实际走了 name_score 索引： 

原因很简单，索引 B+ 树中行数据按照索引值排序，只能根据前缀进行比较。如果要按照后缀搜索也希望走索引的话，并且永远只是按照后缀搜索的话，可以把数据反过来存，用的时候再倒过来。

 第二，条件涉及函数操作无法走索引。比如搜索条件用到了 LENGTH 函数，肯定无法走索引：

EXPLAIN SELECT * FROM person WHERE LENGTH(NAME)=7

同样的原因，索引保存的是索引列的原始值，而不是经过函数计算后的值。如果需要针对函数调用走数据库索引的话，只能保存一份函数变换后的值，然后重新针对这个计算列做索引。

第三，联合索引只能匹配左边的列。也就是说，虽然对 name 和 score 建了联合索引，但是仅按照 score 列搜索无法走索引：

EXPLAIN SELECT * FROM person WHERE SCORE>45678

 原因也很简单，在联合索引的情况下，数据是按照索引第一列排序，第一列数据相同时才会按照第二列排序。也就是说，如果我们想使用联合索引中尽可能多的列，查询条件中的各个列必须是联合索引中从最左边开始连续的列.

三 执行计划选择成本分析

通过前面的案例，我们可以看到，查询数据可以直接在聚簇索引上进行全表扫描，也可以走二级索引扫描后到聚簇索引回表。看到这里，你不禁要问了，MySQL 到底是怎么确定走哪种方案的呢。其实，MySQL 在查询数据之前，会先对可能的方案做执行计划，然后依据成本决定走哪个执行计划。

这里说的成本，包括IO成本和CPU成本：

• O 成本，是从磁盘把数据加载到内存的成本。默认情况下，读取数据页的 IO 成本常数是 1（也就是读取 1 个页成本是 1）

• CPU 成本，是检测数据是否满足条件和排序等 CPU 操作的成本。默认情况下，检测记录的成本是 0.2。

基于此，我们分析下全表扫描的成本。

全表扫描，就是把聚簇索引中的记录依次和给定的搜索条件做比较，把符合搜索条件的记录加入结果集的过程。那么，要计算全表扫描的代价需要两个信息：

• 聚簇索引占用的页面数，用来计算读取数据的 IO 成本；
• 表中的记录数，用来计算搜索的 CPU 成本。

那么，MySQL 是实时统计这些信息的吗？其实并不是，MySQL 维护了表的统计信息，可以使用下面的命令查看：

SHOW TABLE STATUS LIKE 'person'

 可以看到：

• 数据长度=4734976字节，每页大小为16K，则大概有289页，所以IO成本=289*1=289
• 总行数=100086行，所以CPU成本是100086*0.2=20017左右。

所以，全表扫描的总成本=IO成本+CPU成本=289+20017= 20306 左右。

接下来，我还是用 person 表这个例子，和你分析下 MySQL 如何基于成本来制定执行计划。现在，我要用下面的 SQL 查询 name>‘name84059’ AND create_time>‘2020-01-24 05:00:00’

EXPLAIN SELECT * FROM person WHERE NAME >'name84059' AND create_time>'2020-01-24 05:00:00'

执行计划的结果是全表扫描：

只要把 create_time 条件中的 5 点改为 6 点就变为走索引了，并且走的是 create_time 索引而不是 name_score 联合索引，主要原因还是改为6点后扫描的行数少了，成本变低了。

我们可以得到两个结论：

• MySQL 选择索引，并不是按照 WHERE 条件中列的顺序进行的
• 即便列有索引，甚至有多个可能的索引方案，MySQL 也可能不走索引

其原因就是，MySQL 并不是猜拳决定是否走索引的，而是根据成本来判断的。虽然表的统计信息不完全准确，但足够用于策略的判断了。不过，有时会因为统计信息的不准确或成本估算的问题，实际开销会和 MySQL 统计出来的差距较大，导致 MySQL 选择错误的索引或是直接选择走全表扫描，这个时候就需要人工干预，使用强制索引了。

四-慢查解决方案

出现慢查的主要原因，一般都是建立索引不当或者sql的条件不满足索引匹配规则的问题。所以针对这些问题，总结出慢查问题的一般解决方案：

1）检查建立索引是否合理，索引的顺序是否合理

• 索引的顺序很重要，辅助索引的字段区分度应该是从左到右，呈递增趋势。最理想的情况是一个sql只需检索出一条数据，这样即使回表也只会一次。最差的情况，就是索引有跟没有一个样，就是通过这个索引根本区分不出想要的数据，比如索引：country_sex，用性别做左右字段，只能查出一半的数据，命中率很低。
• 索引的字段大小不要太大，不然会占用过多的内存。

2）尽量使用覆盖索引，避免回表成本。

• 如果要查询的数据都在索引树里，就可以避免根据辅助索引上的主键ID来回表查其他数据。

3）涉及到order by 的，尽量走上索引，还有查询的字段越少越好。

 order by 这个动作，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数 sort_buffer_size。

sort_buffer_size，就是 MySQL 为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。

所以，如果你查询的字段比较多，占用的内存>sort_buffer_size，这样就会导致sort_buffer_size空间不够，最后需要额外的磁盘临时文件来辅助排序。如果查询的字段的内存<sort_buffer_size，则只需要在内存排序即可，速度比较快。

另外，如果能够走上索引，索引树的数据天然有序，不需要再排序，固然速度是最快的。

以上就是本文的分享，也是自己的一些总结和沉淀，希望通过记录的方式能够让自己学过的更深刻，更扎实；同时，也希望看过此文的博友也能有所收获，谢谢。