likes
comments
collection
share

终于把MySQL如何选择索引研究明白了？

作者站长头像
站长
· 阅读数 6

相信大家都会对这个问题产生好奇，因为我们在日常的工作和学习中或多或少的会遇到明明自己的表里已经加了索引，但是查询就偏偏不走索引，下面我们就来探究一下MySQL是如何选择索引的。

1. MySQL体系结构

想要知道MySQL如何选择索引，首先需要了解MySQL的体系结构，知道是MySQL的大致运行逻辑，知道是在什么时候抉择用不用索引，用哪个索引，MySQL的体系结构如下图所示。

一条查询语句的执行过程如下：

  1. 应用程序通过JDBC连接MySQL（这个过程会进行通信协议，账号密码等的校验，会分配处理线程）
  2. 连接建立起来后，发送SQL到MySQL服务器，MySQL服务器通过SQL接口接收SQL
  3. 如果开启缓存可以优先获取缓存结果（不建议开启，5.6之前默认开启，之后默认关闭）
  4. 如果没有就会将SQL交由解析器处理，解析器会通过词法分析，语法分析判定SQL是否符合规范，如果有错误直接返回，解析器会将应用程序传入的SQL翻译成优化器可以识别的数据结构
  5. 然后就进行优化器的处理，优化器需要进行条件合并简化，决定是否用索引，用哪个索引，多表关联时，决定表的连接顺序，最终生成执行计划，下文中我们会着重介绍MySQL优化器。
  6. 执行器根据执行计划，找对应的数据库引擎，执行SQL
  7. 数据库引擎会根据自己的特性，操作文件服务（与磁盘，内存交互）。

终于把MySQL如何选择索引研究明白了？

2. MySQL优化器

MySQL的优化器是MySQL整个体系结构中最核心的一环，也是最让我们头疼一环，经常在这里会遇到各种坑，索引用不上或用错索引，SQL执行效率低，我们通过去了解MySQL的优化器也可以反推出一些SQL的优化策略。

MySQL优化器的处理过程，预处理负责完成子查询的冗余子句消除、IN类型子查询优化、将ALL/ANY等类型的子查询转换为MIN/MAX等操作，对简单子查询进行的优化；逻辑优化和物理优化主要包括：子查询上拉，把外连接化为内连接，把嵌套连接消除，WHRER子句、JOIN/ON子句、HAVING子句条件表达式的化简（尤其是对含有常量的表达式的化简、等式合并），优化没有GROUP BY子句情况下的COUNT(*)、MIN()、MAX()，裁剪分区partition（如果查询的表是分区表），确定多表的连接路径（单表是多表的特例，统计join的代价，两种多表连接算法选其一搜索最优的join顺序、生成执行计划）、优化等式谓词、优化DISTINCT、创建临时表存储临时结果优化分组排序等操作。MySQL并没有把优化过程明显地分为逻辑查询优化阶段和物理查询优化阶段，而是互为混杂，在物理查询优化之后，继续进行了部分逻辑查询优化。

逻辑优化和物理优化大概的划分如下：

  • 逻辑优化主要对SQL语句进行等价变换。MySQL尽可能地利用了关系代数中可推定的各项规则，对投影、选择等操作进行句式的优化；对条件表达式进行了谓词的优化、条件化简；对连接语义进行了外连接、嵌套连接的优化；对集合、GROUPBY等尽量利用索引、排序算法进行优化。另外还利用子查询优化、视图重写、语义优化等技术对查询语句进行了优化。

    例： select * from t1,t2 where t1.a = t2.a and t2.a = 9 and (not(t1.a>10 or t2.b > 2) or (t1.b = t2.b + 6 and t2.b = 5))

    否定消除 select * from t1,t2 where t1.a = t2.a and t2.a = 9 and (t1.a<=10 and t2.b <= 2 or (t1.b = t2.b + 6 and t2.b = 5))

    等值传递 select * from t1,t2 where t1.a = 9 and t2.a = 9 and (9<=10 and t2.b <= 2 or (t1.b = 5 + 6 and t2.b = 5))

    常量表达式计算 select * from t1,t2 where t1.a = 9 and t2.a = 9 and ( t2.b <= 2 or (t1.b = 11 and t2.b = 5))

    简化 select * from t1,t2 where t1.a = 9 and t2.a = 9 and ( t2.b <= 2 or (t1.b = 11 and t2.b = 5))

  • 物理优化，通过贪心算法，依据代价估算模型，确定对于每个表，根据条件是否应用索引，应用哪个索引和确定多表连接的顺序等问题，在求解多表连接顺序的过程中，对多个连接的表进行排序并探索连接方式，找出花费最小的路径，据此生成最优执行计划。

    代价主要指CPU消耗和IO消耗，对于InnoDB存储引擎来说，页是磁盘和内存之间交互的基本单位，MySQL 8.0规定读取一个页面花费的成本默认是0.25（MySQL 5.7默认1.0），读取以及检测一条记录是否符合搜索条件的CPU成本默认是0.1（MySQL 5.7默认0.2）。0.25和0.1这些数字称之为成本常数，不同版本的成本常数可能不同。

3. MySQL如何选择索引

前面说了这么多，相信大家已经对MySQL的体系结构和MySQL的优化器有了较为清晰的认知，那MySQL具体是如何选择索引呢？由上文可知MySQL选择索引是发生在优化器的物理优化部分，也称为基于代价或成本的优化，那么我们再往深入探究一下。

大体的优化逻辑（Innodb）

  1. 根据搜索条件，找出所有可能使用的索引

    对于B+树索引来说，只要索引列和常数使用=、<=>、IN、NOT IN、IS NULL、IS NOT NULL、>、<、>=、<=、BETWEEN AND、!=（<>）或者LIKE（前缀匹配）操作符连接起来，就会产生一个范围区间，也就是说这些搜索条件都可能使用到索引，MySQL把一个查询中可能使用到的索引称之为possible keys。

  2. 计算全表扫描的代价

    全表扫描的就是把聚簇索引中的记录都依次按照给定的搜索条件做一下比较，把符合搜索条件的记录加入到结果集，所以需要将聚簇索引对应的页面加载到内存中，然后再检测记录是否符合搜索条件。由于查询成本=I/O成本+CPU成本，所以计算全表扫描的代价需要两个信息：聚簇索引占用的页面数、记录数（这些信息MySQL可以直接从表统计信息中获取，这个值是个估值）这样就能计算出全表扫描的代价，如下图就是一个案例

    终于把MySQL如何选择索引研究明白了？

    案例的算法就是：

    页数 = Date_lenght / 每页容量（默认16k） = 87719936 / 16 / 1024 = 5354

    IO 成本 = 页数 * IO成本常数（0.25） = 5354*0.25 = 1338.5

    CPU 成本 = row * CPU成本常数（0.1）= 71598 * 0.1 = 7159.8

    总成本 = IO成本 + CPU成本 = 8498.3

    可以通过 explain format=json select * from charge ;验证

  3. 计算使用不同索引执行查询的代价（这里只以单一使用索引为例，不涉及索引合并）

    第一步MySQL以及获取到了possible keys，然后就可以分析单独使用这些索引的成本（优先分析唯一索引，再分析普通索引），是否可以合并使用索引，是否可以实现索引覆盖和索引下推，最后考虑回表操作

    对于二级索引+回表方式的查询，MySQL计算这种查询的成本依赖两个方面的数据：

    范围区间的数量：不论某个范围区间的二级索引到底占用了多少页面，查询优化器粗暴的认为读取索引的一个范围区间的I/O成本和读取一个页面是相同的。

    需要回表的记录数： 优化器需要计算二级索引的某个范围区间到底包含多少条记录，计算过程如下：

    • 步骤1： 先根据条件一下找到对应的B+树索引，找到满足条件一的第一条记录，我们把这条记录称之为区间最左记录。在B+数树中定位一条记录的过程是很快的，是常数级别的，所以这个过程的性能消耗是可以忽略不计的

    • 步骤2： 然后再根据条件二继续从这个B+树索引中找出第一条满足这个条件的记录，我们把这条记录称之为区间最右记录，这个过程的性能消耗同样可以忽略不计的。

    • 步骤3： 如果区间最左记录和区间最右记录相隔不太远（在MySQL 5.7.21这个版本中，只要相隔不小于10个页面即可），那就可以精确统计出满足条件一和条件二的二级索引记录条数。否则只沿着区间最左记录向右读10个页面，计算平均每个页面中包含多少记录，然后用这个平均值乘以区间最左记录和区间最右记录之间的页面数量就可以了。

      那么问如何估计区间最左记录和区间最右记录之间有多少个页面呢？解决这个问题还得回到B+树索引的结构中来：

      终于把MySQL如何选择索引研究明白了？

      从B+树的结构来看想要找到最左记录和最右记录之间有多少个页面，就是有多少个父节点，一般MySQL的B+树不会超过4层，所以统计起来也很快，这样我们就可以统计查找索引的消耗，然后我们需要再去统计索引回表的消耗（这里MySQL认为二级索引范围区间有多少记录就需要回表多少次），就能得到总成本。

      假设我们最左到最右中间是1600个页

      I/O成本 = 范围区间的数量 + 预估的二级索引记录条数 = 1.0x 0.25 + 1600 x 0.25 = 400.25

      CPU成本 = 读取二级索引记录的成本 + 读取并检测回表后聚簇索引记录的成本 = 1600 x 0.1 + 0.01 + 1600 x 0.1 = 320.01

      （0.01是一个微调值，不同场景可能会有差异）

      总成本 = I/O成本 + CPU成本 = 400.25 + 320.00 = 720.26

  4. 对比各种执行方案的代价，找出成本最低的那一个

  5. 验证 通过 explain format=json + 查询 SQL 语句 结果中会返回query_cost 就是成本预算值

觉得有帮助的可以点赞、收藏加关注哦！！！