MySQL 执行计划 - LD's BLOG | 个人技术学习

本文将介绍 MySQL 中的执行计划。

一、基础知识

具体请看：

表示可以通过索引唯一地确定某条记录（无论是否回表）。

将通过 唯一二级索引 / 主键 获取 唯一二级索引值 + 主键值 / 完整记录 所消耗的时间定为常量级。

因此，

通过索引唯一地获取某一行记录 = 常量

通过索引唯一地获取主键，通过主键获取完整记录 = 常量 + 常量 = 常量

该访问方法的出现条件为：

表示通过二级索引定位到索引值相同的多条记录。

该访问方法的出现条件为：

搜索条件为 “非唯一” 二级索引与常数的等值比较
搜索条件为唯一二级索引 is null

唯一二级索引并不限制 null 的数量，可能会定位到多条记录
搜索条件为联合二级索引的符合 “最左前缀原则” （且非 “全值匹配”）的等值比较

联合二级索引依据多个列排序，当查询符合 “最左前缀原则”（且非 “全值匹配”）时，可以定位到多条记录

表示通过索引定位到索引值处于某个范围内的多条记录。

该访问方法的出现条件为：

表示通过遍历二级索引查询记录。

该访问方法的出现条件为：

联合二级索引，查询的列 “覆盖索引”，查询条件不符合 “最左前缀原则”

1
2
3

KEY idx_key_part(key_part1, key_part2, key_part3)

SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'abc';

对聚簇索引进行全表扫描。

MySQL 会在执行查询语句之前会分析各种执行计划的成本，在其中选择最优的执行计划。

成本由两个部分组成：

在 InnoDB 中，规定读取一个页花费 1.0 的（I/O）成本，读取记录、比较记录花费 0.2 的（CPU）成本。

进行全表扫描时，需要遍历并比较聚簇索引中的所有记录。因此，成本由页面个数和记录数量共同贡献，成本 = 页面个数 * 1 + 记录数 * 0.2
页面个数和记录数通过表的统计信息获取
1
SHOW TABLE STATUS LIKE '表名';
使用索引时，需要通过索引定位到记录，还可能进行回表，并且需要进行记录的比较。

因此，成本由 “定位”、”回表” 和 “比较” 三部分组成：
- 定位：在定位时，需要访问 “索引定位到的记录” 所在的页面，读取所有记录，因此成本 页数 * 1.0 + 记录数 * 0.2
  - 页数计算方法：InnoDB 粗暴地认为单个索引值、一个索引值范围对应一个页面
  - 记录数有两种计算方法：
    - 通过 B+ 树中定位到所有记录的 “最左记录” 和 “最右记录”，计算得出预估记录数
    - MySQL 中为每一个索引维护了一份统计数据，可用使用其中的基数（Cardinality）属性作为预估记录数
      
      1
      SHOW INDEX FROM 表名;
- 回表：InnoDB 将每一次回表操作简单看作访问一次页面，因此每有一条数据便需要进行一次页面的读取，成本为 记录数 * 1.0
- 比较：成本为 记录数 * 0.2

连接查询总成本 = 访问驱动表的成本 + 驱动表查询得到的记录数 * 访问被驱动表的成本

开发数据库 MySQL MySQL高级