MySQL 是怎样运行的-第六部分
  • Jul 3, 2019

这部分主要跟着大佬的脚步“MySQL 是怎样运行的：从根儿上理解 MySQL”，学习MySQL选择各种执行方案的原因,以及如何优化的知识

基于成本的选择

查询成本=I/O成本+CPU成本

MySQL规定读取一个页面花费的成本默认是1.0，读取以及检测一条记录是否符合搜索条件的成本默认是0.2。1.0、0.2这些数字称之为成本常数，这两个成本常数常用到。

SELECT * FROM mysql.server_cost;  //一条语句在server层中执行的成本常数就存储在了server_cost表
SELECT * FROM mysql.engine_cost;  //依赖于存储引擎的一些操作对应的成本常数就存储在了engine_cost表中
FLUSH OPTIMIZER_COSTS;            //重新加载表值

需要注意的是，不管读取记录时需不需要检测是否满足搜索条件，其成本都算是0.2。

Mysql 查询优化器的优化流程：

单表成本分析

  • 根据搜索条件，找出所有可能使用的索引
  • 计算全表扫描的代价
    SHOW TABLE STATUS LIKE 'tablename' 查看表的统计信息
    查询成本 = I/O成本 + CPU成本
    • I/O成本（聚簇索引页面数量（Data_length/16/1024）* 1.0（加载一个页面的成本常数） + 1.1（微调值不用在意））
    • CPU成本 （ 行数（Rows）* 0.2（访问一条记录所需的成本常数）+1.0（微调值不用在意））
  • 计算使用不同索引执行查询的代价
    • 计算根据不同索引查找的成本
    • 是否有可能使用索引合并
  • 对比各种执行方案的代价，找出成本最低的那一个

表统计信息

  • InnoDB 以表为单位来收集统计数据，这些统计数据可以是基于磁盘的永久性统计数据，也可以是基于内存的非永久性统计数据。

  • innodb_stats_persistent 控制着使用永久性统计数据还是非永久性统计数据；
    innodb_stats_persistent_sample_pages 控制着永久性统计数据的采样页面数量；
    innodb_stats_transient_sample_pages 控制着非永久性统计数据的采样页面数量；
    innodb_stats_auto_recalc 控制着是否自动重新计算统计数据。

  • 我们可以针对某个具体的表，在创建和修改表时通过指定 STATS_PERSISTENT 、STATS_AUTO_RECALC 、STATS_SAMPLE_PAGES 的值来控制相关统计数据属性。

  • innodb_stats_method 决定着在统计某个索引列不重复值的数量时如何对待NULL值。

两表连接的成本分析

连接查询的成本计算公式是这样的：

连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出数 x 单次访问被驱动表的成本

对于左（外）连接和右（外）连接查询来说，它们的驱动表是固定的，所以想要得到最优的查询方案只需要：

  • 分别为驱动表和被驱动表选择成本最低的访问方法。

可是对于内连接来说，驱动表和被驱动表的位置是可以互换的，所以需要考虑两个方面的问题：

  • 不同的表作为驱动表最终的查询成本可能是不同的，也就是需要考虑最优的表连接顺序。
  • 然后分别为驱动表和被驱动表选择成本最低的访问方法。

多表连接的成本分析

多表查询主要设计到多表之间有多少种连接顺序

n 表查询时， 一共有 n! 种连接方式， MySQL查询优化器，会计算所有的连接顺序成本，但是当连接顺序的可能过于多的时候，同时也有一些其他的方法，去优化在查询优化器处需要的时间

  • 提前结束某种顺序的成本评估 维护一个全部变量，这个变量代表当前允许的连接成本，当计算某种连接顺序时，该成本超过这个变量，就不会继续分析了
  • 系统变量 optimizer_search_depth
    (如果连接表的个数小于该值，那么就继续穷举分析每一种连接顺序的成本，否则只对与optimizer_search_depth值相同数量的表进行穷举分析)
  • 根据某些规则压根不考虑某些连接顺序 定义 启发式规则（就是根据以往经验指定的一些规则）, 不满足这些规则的连接顺序不分析，而系统变量 optimizer_prune_level 就是控制是否使用这些启发式规则

基于规则的优化

条件化简

  • 移除不必要的括号
  • 常量的传递
  • 等值传递
  • 移除没用的条件
  • 表达式计算
  • HAVING子句和WHERE子句的合并
  • 常量表检测
    • 查询表中一条记录没有，或者只有一条数据
    • 使用主键等值匹配或者唯一二级索引等值匹配作为搜索条件来查询某个表
      这2种方式查询表称为常量表，优化器会线执行常量表查询， 然后把查询总涉及表的条件全部替换成常数，然后在继续分析表的查询成本

外连接消除

内连接 的驱动表和被驱动表的位置可以互相交换， 但是 左外连接 和 右外连接 的驱动表和被驱动表是固定。所以 内连接 可以通过优化表的连接顺序来降低整体的查询成本

对于 外连接，在被驱动表的 WHERE 子句中添加 列不为 NULL 值的条件，外连接和内连接是否可以互相转换， 这样查询优化器可以通过评估表的不同链接顺序成本，选出成本最低的那种连接顺序来执行查询

子查询优化

子查询语法

  • SELECT 子句
  • FROM 子句
  • WHERE 或 ON 子句
  • ORDER BY 子句
  • GROUP BY 子句

按返回的结果集区分子查询

  • 标量子查询 (只返回一个单一值的子查询)
  • 行子查询 (返回一条记录的子查询，这条记录需要包含多个列，如果只包含一个列就是标量子查询)
  • 列子查询 (查询出一个列的数据，这个列需要包含多条数据)
  • 表子查询 (查询结果包含多条记录， 又包含多个列)

按与外层查询关系来区分子查询

  • 不相关子查询 (子查询可以单独运行，不依赖于外层查询值)
  • 相关子查询 (子查询的执行需要依赖于外层查询的值)

子查询语法注意事项

  • 子查询必须用小括号扩起来
  • 在 SELECT 子句中的子查询必须是标量子查询
  • 在想要得到标量子查询或者行子查询，但又不能保证子查询的结果集只有一条记录时，应该使用 LIMIT 1 语句来限制记录数量。
  • 对于 [NOT] IN/ANY/SOME/ALL 子查询来说，子查询中不允许有 LIMIT 语句。
  • 子查询结果其实相当于一个集合， 所以 ORDER BY(排序) DISTINCT(去重) 等都是冗余的
  • 不允许在一条语句中增删改某个表的记录时同时还对该表进行子查询。

子查询的实际逻辑

标量子查询、行子查询

  • 不相关子查询
    • 先单独执行子查询
    • 根据子查询得到的结果当作外层查询的参数在执行外层查询
  • 相关子查询
    • 先从外层查询中获取一条记录
    • 从外层查询的记录中找出子查询中涉及的值，执行子查询
    • 根据子查询的查询结果来检测外层查询 WHERE 子句条件是否成立，如果成立，就把外层查询的那条记录加入到结果集，否则丢弃
    • 在执行第一步，直到外层查询结束

IN子查询优化

select * from s1 where key in (select col2 from s2 where col1 = '2' )

不直接将不相关子查询的结果集当作外层查询的参数，而是将该结果集写入一个临时表里， 这个过程叫 物化(Materialize)，临时表称为 物化表

  • 该临时表的列就是子查询结果集中的列
  • 写入临时表的记录会被去重，让临时表变得更小
  • 一般情况下子查询结果集不会大的离谱，所以会为它建立基于内存的使用 Memory 存储引擎的临时表，而且会为该表建立哈希索引。如果子查询的结果集非常大，临时表会转而使用基于磁盘的存储引擎来保存结果集中的记录，索引类型也对应转变为B+树索引。

之后将临时表和s1可以类似于内连接的形式，通过查询优化器计算成本，并选取查询方式。但是它和实际的内连接是有区别的， 这里引入 半连接(semi-join)
对于s1表的某条记录来说，我们只关心在s2表中是否存在与之匹配的记录是否存在，而不关心具体有多少条记录与之匹配，最终的结果集中只保留s1表的记录。

大佬的总结，具体可以细节可以自己购买查看

  • 如果IN子查询符合转换为semi-join的条件，查询优化器会优先把该子查询转换为semi-join，然后再考虑下边5种执行半连接的策略中哪个成本最低：

    • Table pullout（子查询中的表上拉）
    • DuplicateWeedout（重复值消除）
    • LooseScan（松散索引扫描）
    • Materialization (物化)
    • FirstMatch （首次匹配）

选择成本最低的那种执行策略来执行子查询。

  • 如果IN子查询不符合转换为semi-join的条件，那么查询优化器会从下边两种策略中找出一种成本更低的方式执行子查询：

    • 先将子查询物化之后再执行查询
    • 执行IN to EXISTS转换。

ANY/ALL子查询优化

原始表达式 转换为
< ANY (SELECT inner_expr …) < (SELECT MAX(inner_expr) …)
> ANY (SELECT inner_expr …) > (SELECT MIN(inner_expr) …)
< ALL (SELECT inner_expr …) < (SELECT MIN(inner_expr) …)
> ALL (SELECT inner_expr …) > (SELECT MAX(inner_expr) …)

[NOT] EXISTS子查询的执行

  • 不相关子查询
    先执行子查询，得到 TRUE 或者 FALSE，然后重写查询语句
  • 相关子查询
    标量子查询、行子查询中的相关查询

对于派生表的优化

MySQL在执行带有派生表的时候，优先尝试把派生表和外层查询合并掉，如果不行的话，再把派生表物化掉执行查询。

查询优化的指南

  • MySQL
    • MySQL 是怎样运行的-第五部分
    MySQL 是怎样运行的-第七部分