做网站编辑是不是也要做推广,工厂管理软件,fr后缀网站,软件搭建5.3. MySQL 的查询成本
5.3. MySQL 的查询成本
MySQL 执行一个查询可以有不同的执行方案#xff0c;它会选择其中成本最低#xff0c;或者 说代价最低的那种方案去真正的执行查询。不过我们之前对成本的描述是非常模 糊的#xff0c;其实在 MySQL 中一条查询语句的执行成本…5.3. MySQL 的查询成本
5.3. MySQL 的查询成本
MySQL 执行一个查询可以有不同的执行方案它会选择其中成本最低或者 说代价最低的那种方案去真正的执行查询。不过我们之前对成本的描述是非常模 糊的其实在 MySQL 中一条查询语句的执行成本是由下边这两个方面组成的 I/O 成本 我们的表经常使用的 MyISAM、InnoDB 存储引擎都是将数据和索引都存储到 磁盘上的当我们想查询表中的记录时需要先把数据或者索引加载到内存中然 后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为 I/O 成本。 CPU 成本 读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作 损耗的时间称之为 CPU 成本。
对于 InnoDB 存储引擎来说页是磁盘和内存之间交互的基本单位MySQL 规定读取一个页面花费的成本默认是 1.0读取以及检测一条记录是否符合搜索 条件的成本默认是 0.2。1.0、0.2 这些数字称之为成本常数这两个成本常数我 们最常用到当然还有其他的成本常数。 注意不管读取记录时需不需要检测是否满足搜索条件其成本都算是 0.2。
5.3.2. 单表查询的成本
5.3.2.1. 基于成本的优化步骤实战
在一条单表查询语句真正执行之前MySQL 的查询优化器会找出执行该语句 所有可能使用的方案对比之后找出成本最低的方案这个成本最低的方案就是 所谓的执行计划之后才会调用存储引擎提供的接口真正的执行查询这个过程 总结一下就是这样 1、根据搜索条件找出所有可能使用的索引 2、计算全表扫描的代价 3、计算使用不同索引执行查询的代价 4、对比各种执行方案的代价找出成本最低的那一个 下边我们就以一个实例来分析一下这些步骤单表查询语句如下
SELECT * FROM order_exp WHERE order_no IN (DD00_6S, DD00_9S,
DD00_10S) AND expire_time 2021-03-22 18:28:28 AND expire_time
2021-03-22 18:35:09 AND insert_time expire_time AND order_note LIKE %7 排
1% AND order_status 0;乍看上去有点儿复杂我们一步一步分析一下。
根据搜索条件找出所有可能使用的索引 我们前边说过对于 B树索引来说只要索引列和常数使用、、IN、 NOT IN、IS NULL、IS NOT NULL、、、、、BETWEEN、!不等于也可以写 成或者 LIKE 操作符连接起来就可以产生一个所谓的范围区间LIKE 匹配字 符串前缀也行MySQL 把一个查询中可能使用到的索引称之为 possible keys。 我们分析一下上边查询中涉及到的几个搜索条件
order_no IN (‘DD00_6S’, ‘DD00_9S’, ‘DD00_10S’) 这个搜索条件可以使用二 级索引 idx_order_no。expire_time ‘2021-03-22 18:28:28’ AND expire_time ‘2021-03-22 18:35:09’ 这个搜索条件可以使用二级索引 idx_expire_time。insert_time expire_time这个搜索条件的索引列由于没有和常数比较所 以并不能使用到索引。order_note LIKE ‘%hello%’order_note 即使有索引但是通过 LIKE 操作符和 以通配符开头的字符串做比较不可以适用索引。order_status 0由于该列上只有联合索引而且不符合最左前缀原则所 以不会用到索引。 综上所述上边的查询语句可能用到的索引也就是 possible keys 只有 idx_order_no,idx_expire_time。 2. 计算全表扫描的代价 对于 InnoDB 存储引擎来说全表扫描的意思就是把聚簇索引中的记录都依 次和给定的搜索条件做一下比较把符合搜索条件的记录加入到结果集所以需 要将聚簇索引对应的页面加载到内存中然后再检测记录是否符合搜索条件。由 于查询成本I/O 成本CPU 成本所以计算全表扫描的代价需要两个信息 聚簇索引占用的页面数 该表中的记录数 这两个信息从哪来呢MySQL 为每个表维护了一系列的统计信息关于这些 统计信息是如何收集起来的我们放在后边再说现在看看怎么查看这些统计信息。 MySQL 给我们提供了 SHOW TABLE STATUS 语句来查看表的统计信息如果 要看指定的某个表的统计信息在该语句后加对应的 LIKE 语句就好了比方说 我们要查看 order_exp 这个表的统计信息可以这么写
SHOW TABLE STATUS LIKE order_exp\G出现了很多统计选项但我们目前只需要两个 Rows 本选项表示表中的记录条数。对于使用 MyISAM 存储引擎的表来说该值是 准确的对于使用 InnoDB 存储引擎的表来说该值是一个估计值。从查询结果 我们也可以看出来由于我们的 order_exp 表是使用 InnoDB 存储引擎的所以 虽然实际上表中有 10567 条记录但是 SHOW TABLE STATUS 显示的 Rows 值只有 10350 条记录。 Data_length 本选项表示表占用的存储空间字节数。使用 MyISAM 存储引擎的表来说该 值就是数据文件的大小对于使用 InnoDB 存储引擎的表来说该值就相当于聚 簇索引占用的存储空间大小也就是说可以这样计算该值的大小
Data_length 聚簇索引的页面数量 x 每个页面的大小 我们的 order_exp 使用默认 16KB 的页面大小而上边查询结果显示Data_length 的值是 1589248所以我们可以反向来推导出聚簇索引的页面数量 聚簇索引的页面数量 1589248 ÷ 16 ÷ 1024 97 我们现在已经得到了聚簇索引占用的页面数量以及该表记录数的估计值所 以就可以计算全表扫描成本了。 现在可以看一下全表扫描成本的计算过程 I/O 成本 97 x 1.0 1.1 98.1 97 指的是聚簇索引占用的页面数1.0 指的是加载一个页面的成本常数后 边的 1.1 是一个微调值。 TIPSMySQL 在真实计算成本时会进行一些微调这些微调的值是直接硬编 码到代码里的没有注释而且这些微调的值十分的小并不影响我们分析。 CPU 成本 10350x 0.2 1.0 2071 10350 指的是统计数据中表的记录数对于 InnoDB 存储引擎来说是一个估 计值0.2 指的是访问一条记录所需的成本常数后边的 1.0 是一个微调值。 总成本 98.1 2071 2169.1 综上所述对于 order_exp 的全表扫描所需的总成本就是 2169.1。 TIPS我们前边说过表中的记录其实都存储在聚簇索引对应 B树的叶子节点 中所以只要我们通过根节点获得了最左边的叶子节点就可以沿着叶子节点组 成的双向链表把所有记录都查看一遍。 也就是说全表扫描这个过程其实有的 B树非叶子节点是不需要访问的但 是 MySQL 在计算全表扫描成本时直接使用聚簇索引占用的页面数作为计算 I/O 成本的依据是不区分非叶子节点和叶子节点的。 3. 计算使用不同索引执行查询的代价 从第 1 步分析我们得到上述查询可能使用到 idx_order_noidx_expire_time 这两个索引我们需要分别分析单独使用这些索引执行查询的成本最后还要分 析是否可能使用到索引合并。这里需要提一点的是MySQL 查询优化器先分析使 用唯一二级索引的成本再分析使用普通索引的成本我们这里两个索引都是普 通索引先算哪个都可以。我们也先分析 idx_expire_time 的成本然后再看使用 idx_order_no 的成本。 使用 idx_expire_time 执行查询的成本分析 idx_expire_time 对应的搜索条件是expire_time ‘2021-03-22 18:28:28’ AND expire_time ‘2021-03-22 18:35:09’ 也就是说对应的范围区间就是 (‘2021-03-22 18:28:28’ , ‘2021-03-22 18:35:09’ )。 思考题扫描区间怎么样从我们复杂的 SQL 语句里提取出来前面已经讲过 了不记得的同学回看一下章节《3.2.3.深入思考索引在查询中的使用》。 使用 idx_expire_time 搜索会使用用二级索引 回表方式的查询MySQL 计 算这种查询的成本依赖两个方面的数据 1、范围区间数量 不论某个范围区间的二级索引到底占用了多少页面查询优化器认为读取索 引的一个范围区间的 I/O 成本和读取一个页面是相同的。本例中使用 idx_expire_time 的范围区间只有一个所以相当于访问这个范围区间的二级索引 付出的 I/O 成本就是1 x 1.0 1.0 2、需要回表的记录数 优化器需要计算二级索引的某个范围区间到底包含多少条记录对于本例来 说就是要计算 idx_expire_time 在(‘2021-03-22 18:28:28’ ‘2021-03-22 18:35:09’) 这个范围区间中包含多少二级索引记录计算过程是这样的 步骤 1先根据 expire_time ‘2021-03-22 18:28:28’这个条件访问一下 idx_expire_time 对应的 B树索引找到满足 expire_time ‘2021-03-22 18:28:28’ 这个条件的第一条记录我们把这条记录称之为区间最左记录。我们前头说过在 B数树中定位一条记录的过程是很快的是常数级别的所以这个过程的性能消 耗是可以忽略不计的。 步骤 2然后再根据 expire_time ‘2021-03-22 18:35:09’这个条件继续从 idx_expire_time 对应的 B树索引中找出最后一条满足这个条件的记录我们把 这条记录称之为区间最右记录这个过程的性能消耗也可以忽略不计的。 步骤 3如果区间最左记录和区间最右记录相隔不太远在 MySQL 5.7 这个 版本里只要相隔不大于 10 个页面即可那就可以精确统计出满足 expire_time ‘2021-03-22 18:28:28’ AND expire_time ‘2021-03-22 18:35:09’条件的二级索引记 录条数。否则只沿着区间最左记录向右读 10 个页面计算平均每个页面中包含 多少记录然后用这个平均值乘以区间最左记录和区间最右记录之间的页面数量 就可以了。那么问题又来了怎么估计区间最左记录和区间最右记录之间有多少 个页面呢解决这个问题还得回到 B树索引的结构中来。
我们假设区间最左记录在页 b 中区间最右记录在页 c 中那么我们想计算 区间最左记录和区间最右记录之间的页面数量就相当于计算页b和页 c 之间有多 少页面而它们父节点中记录的每一条目录项记录都对应一个数据页所以计算 页 b 和页 c 之间有多少页面就相当于计算它们父节点也就是页 a中对应的目 录项记录之间隔着几条记录。在一个页面中统计两条记录之间有几条记录的成本 就很小了。
不过还有问题如果页 b 和页 c 之间的页面实在太多以至于页 b 和页 c 对 应的目录项记录都不在一个父页面中怎么办既然是树那就继续递归之前我 们说过一个 B树有 4 层高已经很了不得了所以这个统计过程也不是很耗费性 能。知道了如何统计二级索引某个范围区间的记录数之后就需要回到现实问题 中来MySQL 根据上述算法测得 idx_expire_time 在区间(‘2021-03-22 18:28:28’ ‘2021-03-22 18:35:09’)之间大约有 39 条记录。
explain SELECT * FROM order_exp WHERE expire_time 2021-03-22 18:28:28 AND expire_time 2021-03-22 18:35:09;读取这 39 条二级索引记录需要付出的 CPU 成本就是 39 x 0.2 0.01 7.81 其中 39 是需要读取的二级索引记录条数0.2 是读取一条记录成本常数0.01 是微调。 在通过二级索引获取到记录之后还需要干两件事儿 1、根据这些记录里的主键值到聚簇索引中做回表操作 MySQL 评估回表操作的 I/O 成本依旧很简单粗暴他们认为每次回表操作都 相当于访问一个页面也就是说二级索引范围区间有多少记录就需要进行多少 次回表操作也就是需要进行多少次页面 I/O。我们上边统计了使用 idx_expire_time 二级索引执行查询时预计有 39 条二级索引记录需要进行回表 操作所以回表操作带来的 I/O 成本就是 39 x 1.0 39 .0 其中 39 是预计的二级索引记录数1.0 是一个页面的 I/O 成本常数。 2、回表操作后得到的完整用户记录然后再检测其他搜索条件是否成立 回表操作的本质就是通过二级索引记录的主键值到聚簇索引中找到完整的 用户记录然后再检测除 expire_time ‘2021-03-22 18:28:28’ AND expire_time 2021-03-22 18:35:09’这个搜索条件以外的搜索条件是否成立。
因为我们通过范围区间获取到二级索引记录共 39 条也就对应着聚簇索引 中 39 条完整的用户记录读取并检测这些完整的用户记录是否符合其余的搜索 条件的 CPU 成本如下
39 x 0.2 7.8 其中 39 是待检测记录的条数0.2 是检测一条记录是否符合给定的搜索条 件的成本常数。 所以本例中使用 idx_expire_time 执行查询的成本就如下所示 I/O 成本 1.0 39 x 1.0 40 .0 (范围区间的数量 预估的二级索引记录条数) CPU 成本 39 x 0.2 0.01 39 x 0.2 15.61 读取二级索引记录的成本 读取并检测 回表后聚簇索引记录的成本 综上所述使用 idx_expire_time 执行查询的总成本就是 40 .0 15.61 55.61
使用 idx_order_no 执行查询的成本分析 idx_order_no 对应的搜索条件是order_no IN (‘DD00_6S’, ‘DD00_9S’, ‘DD00_10S’)也就是说相当于 3 个单点区间。 与使用 idx_expire_time 的情况类似我们也需要计算使用 idx_order_no 时需 要访问的范围区间数量以及需要回表的记录数计算过程与上面类似我们不详 列所有计算步骤和说明了。 范围区间数量 使用 idx_order_no 执行查询时很显然有 3 个单点区间所以访问这 3 个范围 区间的二级索引付出的 I/O 成本就是 3 x 1.0 3.0 需要回表的记录数 由于使用 idx_expire_time 时有 3 个单点区间所以每个单点区间都需要查找 一遍对应的二级索引记录数三个单点区间总共需要回表的记录数是 58。
explain SELECT * FROM order_exp WHERE order_no IN (DD00_6S, DD00_9S,
DD00_10S);读取这些二级索引记录的 CPU 成本就是58 x 0.2 0.01 11.61 得到总共需要回表的记录数之后就要考虑 根据这些记录里的主键值到聚簇索引中做回表操作所需的 I/O 成本就是 58 x 1.0 58.0 回表操作后得到的完整用户记录然后再比较其他搜索条件是否成立 此步骤对应的 CPU 成本就是 58 x 0.2 11.6 所以本例中使用 idx_order_no 执行查询的成本就如下所示
I/O 成本 3.0 58 x 1.0 61.0 (范围区间的数量 预估的二级索引记录条数)CPU 成本 58 x 0.2 58 x 0.2 0.01 23.21 读取二级索引记录的成本 读取并检测 回表后聚簇索引记录的成本综上所述使用 idx_order_no 执行查询的总成本就是 61.0 23.21 84.21 是否有可能使用索引合并Index Merge 本例中有关 order_no 和 expire_time 的搜索条件是使用 AND 连接起来的而 对于 idx_order_no 和 idx_expire_time 都是范围查询也就是说查找到的二级索引 记录并不是按照主键值进行排序的并不满足使用 Intersection 索引合并的条件 所以并不会使用索引合并。而且 MySQL 查询优化器计算索引合并成本的算法也 比较麻烦所以我们也不会细说。 4. 对比各种方案找出成本最低的那一个 下边把执行本例中的查询的各种可执行方案以及它们对应的成本列出来 全表扫描的成本2148.7 使用 idx_expire_time 的成本55.61 使用 idx_order_no 的成本84.21 很显然使用 idx_expire_time 的成本最低所以当然选择 idx_expire_time 来执行查询。 请注意1、MySQL 的源码中对成本的计算实际要更复杂但是基本思想和 算法是没错的。 2、在 MySQL 的实际计算中在和全文扫描比较成本时使用索引的成本会 去除读取并检测回表后聚簇索引记录的成本也就是说我们通过 MySQL 看到 的成本将会是idx_expire_time 为 47.81(55.61-7.8)idx_order_no 为 72.61(84.21-11.6)。但是 MySQL 比较完成本后会再计算一次使用索引的成本 此时就会加上去除读取并检测回表后聚簇索引记录的成本也就是我们计算出来 的值。 5.3.2.2. 基于索引统计数据的成本计算
index dive 有时候使用索引执行查询时会有许多单点区间比如使用 IN 语句就很容易 产生非常多的单点区间比如下边这个查询下边查询语句中的…表示还有很多 参数
SELECT * FROM order_exp WHERE order_no IN (aa1, aa2, aa3, ... , zzz);很显然这个查询可能使用到的索引就是 idx_order_no由于这个索引并不 是唯一二级索引所以并不能确定一个单点区间对应的二级索引记录的条数有多 少需要我们去计算。就是先获取索引对应的 B树的区间最左记录和区间最右 记录然后再计算这两条记录之间有多少记录记录条数少的时候可以做到精确 计算多的时候只能估算。MySQL 把这种通过直接访问索引对应的 B树来计 算某个范围区间对应的索引记录条数的方式称之为 index dive。
有零星几个单点区间的话使用 index dive 的方式去计算这些单点区间对应 的记录数也不是什么问题如果 IN 语句里 20000 个参数怎么办
这就意味着 MySQL 的查询优化器为了计算这些单点区间对应的索引记录条 数要进行 20000 次 index dive 操作这性能损耗就很大搞不好计算这些单点 区间对应的索引记录条数的成本比直接全表扫描的成本都大了。MySQL 考虑到了 这种情况所以提供了一个系统变量 eq_range_index_dive_limit我们看一下在 MySQL 5.7.21 中这个系统变量的默认值
show variables like %dive%;也就是说如果我们的 IN 语句中的参数个数小于 200 个的话将使用 index dive 的方式计算各个单点区间对应的记录条数如果大于或等于 200 个的话可 就不能使用 index dive 了要使用所谓的索引统计数据来进行估算。怎么个估算 法 像会为每个表维护一份统计数据一样MySQL 也会为表中的每一个索引维护 一份统计数据查看某个表中索引的统计数据可以使用 SHOW INDEX FROM 表名 的语法比如我们查看一下 order_exp 的各个索引的统计数据可以这么写
show index from order_exp;属性名 描述
Table 索引所属表的名称。Non_unique 索引列的值是否是唯一的聚簇索引和唯一二级索引的该列 值为 0普通二级索引该列值为 1。Key_name 索引的名称。Seq_in_index 索引列在索引中的位置从 1 开始计数。比如对于联合索引 u_idx_day_status来说insert_time, order_status, expire_time对应的位置分 别是 1、2、3。Column_name 索引列的名称。 Collation 索引列中的值是按照何种排序方式存放的值为 A 时代表升序存 放为 NULL 时代表降序存放。Cardinality 索引列中不重复值的数量。后边我们会重点看这个属性的。Sub_part 对于存储字符串或者字节串的列来说有时候我们只想对这些串 的前 n 个字符或字节建立索引这个属性表示的就是那个 n 值。如果对完整的列 建立索引的话该属性的值就是 NULL。Packed 索引列如何被压缩NULL 值表示未被压缩。这个属性我们暂时不了 解可以先忽略掉。Null 该索引列是否允许存储 NULL 值。 Index_type 使用索引的类型我们最常见的就是 BTREE其实也就是 B树索 引。Comment 索引列注释信息。Index_comment索引注释信息。Cardinality 属性Cardinality 直译过来就是基数的意思表示索引列中不重 复值的个数。比如对于一个一万行记录的表来说某个索引列的 Cardinality 属性 是 10000那意味着该列中没有重复的值如果 Cardinality 属性是 1 的话就意 味着该列的值全部是重复的。不过需要注意的是对于 InnoDB 存储引擎来说 使用 SHOW INDEX 语句展示出来的某个索引列的 Cardinality 属性是一个估计值 并不是精确的。 前边说道当 IN 语句中的参数个数大于或等于系统变量 eq_range_index_dive_limit 的值的话就不会使用 index dive 的方式计算各个单点 区间对应的索引记录条数而是使用索引统计数据这里所指的索引统计数据指 的是这两个值
使用 SHOW TABLE STATUS 展示出的 Rows 值也就是一个表中有多少条记录。 使用 SHOW INDEX 语句展示出的 Cardinality 属性。 结合上一个 Rows 统计数据我们可以针对索引列计算出平均一个值重复 多少次。 一个值的重复次数 ≈ Rows ÷ Cardinality 以 order_exp 表的 idx_order_no 索引为例它的 Rows 值是 10350它对应 的 Cardinality 值是 10220所以我们可以计算 order_no 列平均单个值的重复次数 就是10350÷ 10220≈ 1.012条 此时再看上边那条查询语句
SELECT * FROM order_exp WHERE order_no IN (aa1, aa2, aa3, ... , zzz);假设 IN 语句中有 20000 个参数的话就直接使用统计数据来估算这些参数 需要单点区间对应的记录条数了每个参数大约对应 1.012 条记录所以总共需 要回表的记录数就是 20000 x 1.012 21,730 使用统计数据来计算单点区间对应的索引记录条数比 index dive 的方式简单 但是它的致命弱点就是不精确。使用统计数据算出来的查询成本与实际所需 的成本可能相差非常大。 大家需要注意一下在 MySQL 5.7.3 以及之前的版本中 eq_range_index_dive_limit 的默认值为 10之后的版本默认值为 200。所以如果 大家采用的是 5.7.3 以及之前的版本的话很容易采用索引统计数据而不是 index dive 的方式来计算查询成本。当你的查询中使用到了 IN 查询但是却实际没有 用到索引就应该考虑一下是不是由于 eq_range_index_dive_limit 值太小导致的
5.3.3. 再深入 Explain
5.3.3.1. EXPLAIN 输出成本
前面我们已经对 MySQL 查询优化器如何计算成本有了比较深刻的了解。但是 EXPLAIN 语句输出中缺少了一个衡量执行计划好坏的重要属性 —— 成本。不过 MySQL 已经为我们提供了一种查看某个执行计划花费的成本的方式 在 EXPLAIN 单词和真正的查询语句中间加上 FORMATJSON。 这样我们就可以得到一个 json 格式的执行计划里边包含该计划花费的成 本比如这样
explain formatjson SELECT * FROM order_exp WHERE order_no IN (DD00_6S,
DD00_9S, DD00_10S) AND expire_time 2021-03-22 18:28:28 AND
expire_time 2021-03-22 18:35:09 AND insert_time expire_time AND order_note
LIKE %7 排 1% AND order_status 0\G*************************** 1. row ***************************
EXPLAIN: {
query_block: { select_id: 1,# 整个查询语句只有 1 个 SELECT 关键字该关键字对应的 id 号为 1
cost_info: { query_cost: 55.61 # 整个查询的执行成本预计为 55.61},
table: { table_name: order_exp, access_type: range, possible_keys: [ idx_order_no, idx_expire_time
],
key: idx_expire_time, used_key_parts: [ expire_time
],key_length: 5, rows_examined_per_scan: 39, rows_produced_per_join: 0, filtered: 0.13, index_condition: ((mysqladv.order_exp.expire_time 2021-03-22 18:28:28)and (mysqladv.order_exp.expire_time 2021-03-22 18:35:09)),
cost_info: { read_cost: 55.60, eval_cost: 0.01, prefix_cost: 55.61, #单独查询表的成本也就是read_cost eval_cost data_read_per_join: 24 #和连接查询相关的数据量单位字节这里无用
},
used_columns: [ id, order_no, order_note, insert_time, expire_duration, expire_time, order_status
],attached_condition: ((mysqladv.order_exp.order_status 0) and
(mysqladv.order_exp.order_no in (DD00_6S,DD00_9S,DD00_10S)) and
(mysqladv.order_exp.insert_time mysqladv.order_exp.expire_time) and
(mysqladv.order_exp.order_note like %7 排 1%))}}
}
1 row in set, 1 warning (0.00 sec)5.3.3.2. Optimizer Trace
对于 MySQL5.6 之前的版本来说只能通过 EXPLAIN 语句查看到最后优化器 决定使用的执行计划却无法知道它为什么做这个决策。我们可能有这样的疑问 “我就觉得使用其他的执行方案比 EXPLAIN 输出的这种方案强凭什么优化器做 的决定和我想的不一样呢为什么 MySQL 一定要全文扫描不用索引呢” 在 MySQL 5.6 以及之后的版本中MySQL 提出了一个 optimizer trace 的功能 这个功能可以让我们方便的查看优化器生成执行计划的整个过程这个功能的开 启与关闭由系统变量 optimizer_trace 决定
SHOW VARIABLES LIKE optimizer_trace;可以看到 enabled 值为 off表明这个功能默认是关闭的。one_line 的值是控制输出格式的如果为 on 那么所有输出都将在一行中展示我们就保持其默认值为off。
如果想打开这个功能必须首先把 enabled 的值改为 on就像这样
SET optimizer_traceenabledon;然后我们就可以输入我们想要查看优化过程的查询语句当该查询语句执行 完成后就可以到 information_schema 数据库下的 OPTIMIZER_TRACE 表中查看完 整的优化过程。这个 OPTIMIZER_TRACE 表有 4 个列分别是
QUERY表示我们的查询语句。TRACE表示优化过程的 JSON 格式文本。MISSING_BYTES_BEYOND_MAX_MEM_SIZE由于优化过程可能会输出很多如果超过某个限制时多余的文本将不会被显示这个字段展示了被忽略的文本字节数。INSUFFICIENT_PRIVILEGES表示是否没有权限查看优化过程默认值是 0 只有某些特殊情况下才会是 1我们暂时不关心这个字段的值。 当停止查看语句的优化过程时把 optimizer trace 功能关闭。
SET optimizer_traceenabledoff;注意开启 trace 会影响 mysql 性能所以只能临时分析 sql 使用用完之 后立即关闭 。 现在我们有一个搜索条件比较多的查询语句它的执行计划如下
explain SELECT * FROM order_exp WHERE order_no IN (DD00_6S, DD00_9S,
DD00_10S) AND expire_time 2021-03-22 18:28:28 AND insert_time
2021-03-22 18:35:09 AND order_note LIKE %7 排 1%;可以看到该查询可能使用到的索引有 3 个 u_idx_day_status,idx_order_no,idx_expire_time那么为什么优化器最终选择了 idx_order_no 而不选择其他的索引或者直接全表扫描呢这时候就可以通过 otpimzer trace 功能来查看优化器的具体工作过程 记得开启 optimizer trace 功能
SELECT * FROM order_exp WHERE order_no IN (DD00_6S, DD00_9S,
DD00_10S) AND expire_time 2021-03-22 18:28:28 AND insert_time
2021-03-22 18:35:09 AND order_note LIKE %7 排 1%;
SELECT * FROM information_schema.OPTIMIZER_TRACE\G展示的内容极多我们选择优化过程中的一些比较重要的点关注一下
*************************** 1. row *************************** # 分析的查询语句是什么
QUERY: SELECT * FROM order_exp WHERE order_no IN (DD00_6S, DD00_9S, DD00_10S) AND expire_time 2021-03-22 18:28:28 AND insert_time 2021-03-22 18:35:09 AND order_note LIKE %7 排 1% # 优化的具体过程
TRACE: { steps: [
{ join_preparation: { # prepare 阶段
select#: 1, steps: [
{ IN_uses_bisection: true
},{# 类似于查询优化器将查询语句重写后的语句
expanded_query: /* select#1 */ select order_exp.id AS id,order_exp.order_no AS
order_no,order_exp.order_note AS order_note,order_exp.insert_time AS insert_time,order_exp.expire_duration AS
expire_duration,order_exp.expire_time AS expire_time,order_exp.order_status AS order_status from order_exp where
((order_exp.order_no in (DD00_6S,DD00_9S,DD00_10S)) and (order_exp.expire_time 2021-03-22 18:28:28) and
(order_exp.insert_time 2021-03-22 18:35:09) and (order_exp.order_note like %7 排 1%))
}
]
}
},{ join_optimization: { # optimize 阶段
select#: 1, steps: [
{ condition_processing: { # 处理搜索条件
condition: WHERE, # 原始搜索条件
original_condition: ((order_exp.order_no in (DD00_6S,DD00_9S,DD00_10S)) and (order_exp.expire_time
2021-03-22 18:28:28) and (order_exp.insert_time 2021-03-22 18:35:09) and (order_exp.order_note like %7 排 1%)), steps: [
{ transformation: equality_propagation, # 等值传递转换
resulting_condition: ((order_exp.order_no in (DD00_6S,DD00_9S,DD00_10S)) and
(order_exp.expire_time 2021-03-22 18:28:28) and (order_exp.insert_time 2021-03-22 18:35:09) and
(order_exp.order_note like %7 排 1%))
},{ transformation: constant_propagation, # 常量传递转换
resulting_condition: ((order_exp.order_no in (DD00_6S,DD00_9S,DD00_10S)) and
(order_exp.expire_time 2021-03-22 18:28:28) and (order_exp.insert_time 2021-03-22 18:35:09) and
(order_exp.order_note like %7 排 1%))
},{
transformation: trivial_condition_removal, # 去除没用的条件
resulting_condition: ((order_exp.order_no in (DD00_6S,DD00_9S,DD00_10S)) and
(order_exp.expire_time 2021-03-22 18:28:28) and (order_exp.insert_time 2021-03-22 18:35:09) and
(order_exp.order_note like %7 排 1%))
}
]
}
},{ substitute_generated_columns: { # 替换虚拟生成列
}
},{ table_dependencies: [ # 表的依赖信息
{ table: order_exp, row_may_be_null: false, map_bit: 0, depends_on_map_bits: [
]
}
]
},{ ref_optimizer_key_uses: [
]
},{ rows_estimation: [ # 预估不同单表访问方法的访问成本
{ table: order_exp, range_analysis: { table_scan: { # 全表扫描的行数以及成本
rows: 10345, cost: 2168.1
},
potential_range_indexes: [ # 分析可能使用的索引
{ index: PRIMARY, # 主键
usable: false, cause: not_applicable # 主键不可用
},{ index: u_idx_day_status, # u_idx_day_status 可能被使用
usable: true, key_parts: [ insert_time, order_status, expire_time
]
},{ index: idx_order_no, # idx_order_no 可能被使用
usable: true, key_parts: [ order_no, id
]
},{ index: idx_expire_time, # idx_expire_time 可能被使用
usable: true, key_parts: [ expire_time, id
]
}
],setup_range_conditions: [
],group_index_range: {
chosen: false, cause: not_group_by_or_distinct
},analyzing_range_alternatives: { # 分析各种可能使用的索引的成本
range_scan_alternatives: [
{ index: u_idx_day_status, # 使用 u_idx_day_status 的成本分析
ranges: [ # 使用 u_idx_day_status 的范围区间
0x99a92d28c9 insert_time
],index_dives_for_eq_ranges: true, # 是否使用 index dive
rowid_ordered: false, # 使用该索引获取的记录是否按照主键排序
using_mrr: false, # 是否使用 mrr
index_only: false, # 是否是索引覆盖访问
rows: 5172, # 使用该索引获取的记录条数
cost: 6207.4, # 使用该索引的成本
chosen: false, # 是否选择该索引
cause: cost # 因为成本太大所以不选择该索引
},{ index: idx_order_no, ranges: [ DD00_10S order_no DD00_10S, DD00_6S order_no DD00_6S, DD00_9S order_no DD00_9S
],index_dives_for_eq_ranges: true, rowid_ordered: false, using_mrr: false, index_only: false, rows: 58, cost: 72.61, chosen: true
},{
index: idx_expire_time, ranges: [ 0x99a92d271c expire_time
],index_dives_for_eq_ranges: true, rowid_ordered: false, using_mrr: false, index_only: false, rows: 5172, cost: 6207.4, chosen: false, cause: cost
}
],analyzing_roworder_intersect: { # 分析使用索引合并的成本
usable: false, cause: too_few_roworder_scans
}
},chosen_range_access_summary: { # 对于上述单表查询 s1 最优的访问方法
range_access_plan: { type: range_scan, index: idx_order_no, rows: 58, ranges: [ DD00_10S order_no DD00_10S, DD00_6S order_no DD00_6S, DD00_9S order_no DD00_9S
]
},rows_for_plan: 58, cost_for_plan: 72.61, chosen: true
}
}
}
]
},{
# 分析各种可能的执行计划,负责对比各可行计划的开销并选择相对最优的执行计划。
#对多表查询这可能有很多种不同的方案单表查询的方案上边已经分析过了直接选取
idx_order_no 即可
considered_execution_plans: [
{ plan_prefix: [
],table: order_exp, best_access_path: { considered_access_paths: [
{ rows_to_scan: 58, access_type: range, range_details: { used_index: idx_order_no
},resulting_rows: 58, cost: 84.21, chosen: true
}
]
},condition_filtering_pct: 100, rows_for_plan: 58, cost_for_plan: 84.21, chosen: true
}
]
},{
# 尝试给查询添加一些其他的查询条件,增加主要是为了便于 ICP索引条件下推
attaching_conditions_to_tables: {
original_condition: ((order_exp.order_no in (DD00_6S,DD00_9S,DD00_10S)) and (order_exp.expire_time
2021-03-22 18:28:28) and (order_exp.insert_time 2021-03-22 18:35:09) and (order_exp.order_note like %7 排 1%)), attached_conditions_computation: [
],attached_conditions_summary: [
{ table: order_exp, attached: ((order_exp.order_no in (DD00_6S,DD00_9S,DD00_10S)) and (order_exp.expire_time
2021-03-22 18:28:28) and (order_exp.insert_time 2021-03-22 18:35:09) and (order_exp.order_note like %7 排 1%))
}
]
}
},{
# 再稍稍的改进一下执行计划
refine_plan: [
{ table: order_exp, pushed_index_condition: (order_exp.order_no in (DD00_6S,DD00_9S,DD00_10S)), table_condition_attached: ((order_exp.expire_time 2021-03-22 18:28:28) and (order_exp.insert_time
2021-03-22 18:35:09) and (order_exp.order_note like %7 排 1%))}]}]}},{ join_execution: { # execute 阶段
select#: 1, steps: []}}]}# 因优化过程文本太多而丢弃的文本字节大小值为 0 时表示并没有丢弃
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
# 权限字段
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)优化过程大致分为了三个阶段 prepare 阶段 optimize 阶段 execute 阶段 我们所说的基于成本的优化主要集中在 optimize 阶段对于单表查询来说 我们主要关注 optimize 阶段的rows_estimation这个过程这个过程深入分析了对单表查询的各种执行方案的成本 对于多表连接查询来说我们更多需要关注considered_execution_plans这个过程这个过程里会写明各种不同的连接方式所对应的成本。反正优化器最终 会选择成本最低的那种方案来作为最终的执行计划也就是我们使用 EXPLAIN 语句所展现出的那种方案。 如果对使用 EXPLAIN 语句展示出的对某个查询的执行计划很不理解就可以 尝试使用 optimizer trace 功能来详细了解每一种执行方案对应的成本。
5.3.4. 连接查询的成本
5.3.4.1. Condition filtering 介绍
连接查询至少是要有两个表的课程的讲述中可能使用 order_exp 表的派生 表 s1、s2 和 order_exp2。 我们前边说过MySQL 中连接查询采用的是嵌套循环连接算法驱动表会被访问一次被驱动表可能会被访问多次所以对于两表连接查询来说它的查询 成本由下边两个部分构成
单次查询驱动表的成本 多次查询被驱动表的成本具体查询多少次取决于对驱动表查询的结果集 中有多少条记录 对驱动表进行查询后得到的记录条数称之为驱动表的扇出英文名fanout。 很显然驱动表的扇出值越小对被驱动表的查询次数也就越少连接查询的总成本也就越低。当查询优化器想计算整个连接查询所使用的成本时就需要计算出驱动表的扇出值有的时候扇出值的计算是很容易的比如下边这两个查询
查询一
SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2;假设使用 s1 表作为驱动表很显然对驱动表的单表查询只能使用全表扫描的方式执行驱动表的扇出值也很明确那就是驱动表中有多少记录扇出值就是多少。统计数据中s1表的记录行数是10573也就是说优化器就直接会把10573 当作在 s1 表的扇出值。
查询二
SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2
WHERE s1.expire_time 2021-03-22 18:28:28 AND s1.expire_time
2021-03-22 18:35:09;仍然假设 s1 表是驱动表的话很显然对驱动表的单表查询可以使用 idx_expire_time 索引执行查询。此时范围区间( ‘2021-03-22 18:28:28’, ‘2021-03-2218:35:09’)中有多少条记录那么扇出值就是多少。 但是有的时候扇出值的计算就变得很棘手比方说下边几个查询
查询三
SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2 WHERE
s1.order_note xyz;本查询和查询一类似只不过对于驱动表 s1 多了一个 order_note xyz’的搜索条件。查询优化器又不会真正的去执行查询所以它只能猜这 10573 记录里有多少条记录满足 order_note xyz’条件。
查询四
SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2 WHERE
s1.expire_time 2021-03-22 18:28:28 AND s1.expire_time 2021-03-22 18:35:09 AND s1.order_note xyz;本查询和查询二类似只不过对于驱动表 s1 也多了一个 order_note ‘xyz’ 的搜索条件。不过因为本查询可以使用 idx_expire_time 索引所以只需要从符合二级索引范围区间的记录中猜有多少条记录符合 order_note xyz’条件也就是只需要猜在 39 条记录中有多少符合 order_note xyz’条件。
查询五
SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2 WHERE s1.expire_time 2021-03-22 18:28:28 AND s1.expire_time 2021-03-22 18:35:09 AND s1.order_no IN (DD00_6S, DD00_9S, DD00_10S) AND s1.order_note
xyz;本查询和查询四类似不过在驱动表 s1 选取 idx_expire_time 索引执行查询后优化器需要从符合二级索引范围区间的记录中猜有多少条记录符合下边两个条件
order_no IN (DD00_6S, DD00_9S, DD00_10S)
order_note xyz也就是优化器需要猜在 39 条记录中有多少符合上述两个条件的。 说了这么多其实就是想表达在这两种情况下计算驱动表扇出值时需要靠猜 如果使用的是全表扫描的方式执行的单表查询那么计算驱动表扇出时需要猜满足搜索条件的记录到底有多少条。 如果使用的是索引执行的单表扫描那么计算驱动表扇出的时候需要猜满 足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。
MySQL 把这个猜的过程称之为 condition filtering。当然这个过程可能会使用到索引也可能使用到统计数据也可能就是 MySQL 单纯的瞎猜整个评估过程非常复杂所以我们不去细讲。在 MySQL 5.7 之前的版本中查询优化器在计算驱动表扇出时如果是使用全表扫描的话就直接使用表中记录的数量作为扇出值如果使用索引的话就直接使用满足范围条件的索引记录条数作为扇出值。在 MySQL 5.7 中MySQL 引入了这个 condition filtering 的功能就是还要猜一猜剩余的那些搜索条件能把驱动表中的记录再过滤多少条其实本质上就是为了让成本估算更精确。 我们所说的纯粹瞎猜其实是很不严谨的MySQL 称之为启发式规则。
5.3.4.2. 两表连接的成本分析
连接查询的成本计算公式是这样的 连接查询总成本 单次访问驱动表的成本 驱动表扇出数 x 单次访问被驱动表的成本
对于左外连接和右外连接查询来说它们的驱动表是固定的所以想要得到最优的查询方案只需要分别为驱动表和被驱动表选择成本最低的访问方法。可是对于内连接来说驱动表和被驱动表的位置是可以互换的所以需要考虑两个方面的问题不同的表作为驱动表最终的查询成本可能是不同的也就是需要考虑最优的表连接顺序。然后分别为驱动表和被驱动表选择成本最低的访问方法。很显然计算内连接查询成本的方式更麻烦一些下边我们就以内连接为例来看看如何计算出最优的连接查询方案。当然在某些情况下左外连接和右外连接查询在某些特殊情况下可以被优化为内连接查询。 我们来看看内连接比如对于下边这个查询来说
SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2 ON s1.order_no
s2.order_note WHERE s1.expire_time 2021-03-22 18:28:28 AND
s1.expire_time 2021-03-22 18:35:09 AND s2.expire_time 2021-03-22 18:35:09 AND s2.expire_time 2021-03-22 18:35:59;可以选择的连接顺序有两种 s1 连接 s2也就是 s1 作为驱动表s2 作为被驱动表。 s2 连接 s1也就是 s2 作为驱动表s1 作为被驱动表。
查询优化器需要分别考虑这两种情况下的最优查询成本然后选取那个成本更低的连接顺序以及该连接顺序下各个表的最优访问方法作为最终的查询计划。我们定性的分析一下不像分析单表查询那样定量的分析了 使用 s1 作为驱动表的情况 分析对于驱动表的成本最低的执行方案首先看一下涉及 s1 表单表的搜索条件有哪些
s1.expire_time 2021-03-22 18:28:28 AND s1.expire_time 2021-03-22 18:35:09 所以这个查询可能使用到 idx_expire_time 索引从全表扫描和使用 idx_expire_time 这两个方案中选出成本最低的那个很显然使用 idx_expire_time 执行查询的成本更低些。 然后分析对于被驱动表的成本最低的执行方案此时涉及被驱动表 s2 的搜索条件就是 1、s2.order_note 常数这是因为对驱动表 s1 结果集中的每一条记录都需要进行一次被驱动表 s2 的访问此时那些涉及两表的条件现在相当于只涉及 被驱动表 s2 了。 2、s2.expire_time ‘2021-03-22 18:35:09’ AND s2.expire_time ‘2021-03-22 18:35:59’ 很显然第一个条件由于 order_note 没有用到索引所以并没有什么用 此时访问 s2 表时可用的方案也是全表扫描和使用 idx_expire_time 两种假设使用 idx_expire_time 的成本更小。 所以此时使用 s1 作为驱动表时的总成本就是暂时不考虑使用 join buffer 对成本的影响 使用 idx_expire_time 访问 s1 的成本 s1 的扇出 × 使用 idx_expire_time 访问 s2 的成本。 使用 s2 作为驱动表的情况 分析对于驱动表的成本最低的执行方案 首先看一下涉及 s2 表单表的搜索条件有哪些 s2.expire_time ‘2021-03-22 18:35:09’ AND s2.expire_time ‘2021-03-22 18:35:59’ 所以这个查询可能使用到 idx_expire_time 索引从全表扫描和使用 idx_expire_time 这两个方案中选出成本最低的那个假设使用 idx_expire_time 执 行查询的成本更低些。 然后分析对于被驱动表的成本最低的执行方案 此时涉及被驱动表 s1 的搜索条件就是 1、s1.order_no 常数 2、s1.expire_time ‘2021-03-22 18:28:28’ AND s1.expire_time ‘2021-03-22 18:35:09’ 这时就很有趣了使用 idx_order_no 可以进行 ref 方式的访问使用 idx_expire_time 可以使用 range 方式的访问。 那么优化器需要从全表扫描、使用 idx_order_no、使用 idx_expire_time 这几个方案里选出一个成本最低的方案。 这里有个问题因为 idx_expire_time 的范围区间是确定的怎么计算使用 idx_expire_time 的成本我们上边已经说过了可是在没有真正执行查询前 s1.order_no 常数中的常数值我们是不知道的怎么衡量使用 idx_order_no 执行查询的成本呢其实很简单直接使用我们前面说过的索引统计数据就好了 就是索引列平均一个值重复多少次。一般情况下ref 的访问方式要比 range 成本更低这里假设使用 idx_order_no 进行对 s1 的访问。 所以此时使用 s2 作为驱动表时的总成本就是 使用 idx_expire_time 访问 s2 的成本 s2 的扇出 × 使用 idx_order_no 访问 s1 的成本 最后优化器会比较这两种方式的最优访问成本选取那个成本更低的连接顺 序去真正的执行查询。从上边的计算过程也可以看出来一般来讲连接查询成 本占大头的其实是驱动表扇出数 x 单次访问被驱动表的成本所以我们的优化重点其实是下边这两个部分
尽量减少驱动表的扇出对被驱动表的访问成本尽量低 这一点对于我们实际书写连接查询语句时十分有用我们需要尽量在被驱动表的连接列上建立索引这样就可以使用 ref 访问方法来降低访问被驱动表的成本了。如果可以被驱动表的连接列最好是该表的主键或者唯一二级索引列这样就可以把访问被驱动表的成本降到更低了。
5.3.4.3. EXPLAIN 输出连接成本
连接查询在输出成本时和单表查询稍有不同如下
explain formatjson SELECT * FROM order_exp AS s1 INNER JOIN order_exp2
AS s2 ON s1.order_no s2.order_note WHERE s1.expire_time 2021-03-22
18:28:28 AND s1.expire_time 2021-03-22 18:35:09 AND s2.expire_time
2021-03-22 18:35:09 AND s2.expire_time 2021-03-22 18:35:59\G
*************************** 1. row ***************************
EXPLAIN: { query_block: { select_id: 1,# 整个查询语句只有 1 个 SELECT 关键字该关键字对应的 id 号为 1 cost_info: { query_cost: 840.51 # 整个查询的执行成本
},nested_loop: [ # 几个表之间采用嵌套循环连接算法执行
{ table: { table_name: s2, # s2 表是驱动表
access_type: range, # 访问方法为 range
possible_keys: [ idx_expire_time
],key: idx_expire_time, used_key_parts: [ expire_time
],key_length: 5, rows_examined_per_scan: 321, # 查询 s2 表大致需要扫描 321 条记录
rows_produced_per_join: 321, # 驱动表 s2 的扇出是 321 filtered: 100.00, # condition filtering 代表的百分比
index_condition: ((mysqladv.s2.expire_time 2021-03-22
18:35:09) and (mysqladv.s2.expire_time 2021-03-22 18:35:59)), cost_info: { read_cost: 386.21, eval_cost: 64.20, prefix_cost: 450.41, # 查询 s1 表总共的成本read_cost eval_cost data_read_per_join: 152K # 读取的数据量
},used_columns: [ id, order_no, order_note, insert_time, expire_duration, expire_time, order_status
]
}
},{ table: { table_name: s1, # s1 表是被驱动表
access_type: ref, possible_keys: [ idx_order_no,
idx_expire_time
],key: idx_order_no, used_key_parts: [ order_no
],key_length: 152, ref: [ mysqladv.s2.order_note
],rows_examined_per_scan: 1, # 查询一次 s1 表大致需要扫描 1 条记录
rows_produced_per_join: 16, # 被驱动表 s2 的扇出是 16由于没有多余的
表进行连接所以这个值无用
filtered: 4.94, # condition filtering 代表的百分比
index_condition: (mysqladv.s1.order_no
mysqladv.s2.order_note), cost_info: { read_cost: 325.08, eval_cost: 3.21, prefix_cost: 840.51, # 单次查询 s2、多次查询 s1 表总共的成本
data_read_per_join: 7K
},used_columns: [ id, order_no, order_note, insert_time, expire_duration, expire_time, order_status
],attached_condition: ((mysqladv.s1.expire_time
2021-03-22 18:28:28) and (mysqladv.s1.expire_time 2021-03-22
18:35:09))}}]}
}
1 row in set, 1 warning (0.00 sec)我们使用#后边跟随注释的形式为大家解释了EXPLAIN FORMATJSON语句的输出内容s2 表的cost_info中 prefix_cost 就是单独查询 s2 表的成本。 对于 s1 表的cost_info中由于 s1 表是被驱动表所以可能被读取多次这里的 read_cost 和 eval_cost 是访问多次 s2 表后累加起来的值而 s1 表中的prefix_cost 的值代表的是整个连接查询预计的成本。 看完了上面的执行计划的输出可能大家有疑惑驱动表 S2 的查询成本为450.41总查询成本为 840.51也就是说对被驱动表 S1 的查询成本也就是 390左右看起来用 S1 做驱动表好像更省一点。真的这样吗我们把 SQL 语句改造一下将 INNER JOIN 替换为 STRAIGHT_JOIN
explain formatjson SELECT * FROM order_exp AS s1 STRAIGHT_JOIN
order_exp2 AS s2 ON s1.order_no s2.order_note WHERE s1.expire_time
2021-03-22 18:28:28 AND s1.expire_time 2021-03-22 18:35:09 AND
s2.expire_time 2021-03-22 18:35:09 AND s2.expire_time 2021-03-22
18:35:59\G大家可以自行看看所需要的成本是多少并自行解释一下原因
5.3.4.4. 多表连接的成本分析
首先要考虑一下多表连接时可能产生出多少种连接顺序 对于两表连接比如表 A 和表 B 连接 只有 AB、BA 这两种连接顺序。其实相当于 2 × 1 2 种连接顺序。 对于三表连接比如表 A、表 B、表 C 进行连接 有 ABC、ACB、BAC、BCA、CAB、CBA 这么 6 种连接顺序。其实相当于 3 × 2 × 1 6 种连接顺序。 对于四表连接的话则会有 4 × 3 × 2 × 1 24 种连接顺序。 对于 n 表连接的话则有 n × (n-1) × (n-2) × ··· × 1 种连接顺序 就是 n 的阶乘种连接顺序也就是 n!。 有 n 个表进行连接MySQL 查询优化器要每一种连接顺序的成本都计算一 遍么那可是 n!种连接顺序呀。其实真的是要都算一遍不过 MySQL 用了很多办法减少计算非常多种连接顺序的成本的方法 提前结束某种顺序的成本评估 MySQL 在计算各种链接顺序的成本之前会维护一个全局的变量这个变量表示当前最小的连接查询成本。如果在分析某个连接顺序的成本时该成本已经超过当前最小的连接查询成本那就压根儿不对该连接顺序继续往下分析了。比方说 A、B、C 三个表进行连接已经得到连接顺序 ABC 是当前的最小连接成本比方说 10.0在计算连接顺序 BCA 时发现 B 和 C 的连接成本就已经大于 10.0时就不再继续往后分析 BCA 这个连接顺序的成本了。 系统变量 optimizer_search_depth 为了防止无穷无尽的分析各种连接顺序的成本MySQL 提出了optimizer_search_depth 系统变量如果连接表的个数小于该值那么就继续穷举分析每一种连接顺序的成本否则只对optimizer_search_depth 值相同数量的表进行穷举分析。很显然该值越大成本分析的越精确越容易得到好的执行计划但是消耗的时间也就越长否则得到不是很好的执行计划但可以省掉很多分析连接成本的时间。 根据某些规则压根儿就不考虑某些连接顺序 即使是有上边两条规则的限制但是分析多个表不同连接顺序成本花费的时间还是会很长所以 MySQL 干脆提出了一些所谓的启发式规则就是根据以往经验指定的一些规则凡是不满足这些规则的连接顺序压根儿就不分析这样可以极大的减少需要分析的连接顺序的数量但是也可能造成错失最优的执行计划。他们提供了一个系统变量optimizer_prune_level 来控制到底是不是用这些启发式规则。
5.3.5. 调节成本常数
我们前边已经介绍了两个成本常数 读取一个页面花费的成本默认是 1.0 检测一条记录是否符合搜索条件的成本默认是 0.2 其实除了这两个成本常数MySQL 还支持很多它们被存储到了 MySQL 数据库的两个表中
SHOW TABLES FROM mysql LIKE %cost%;因为一条语句的执行其实是分为两层的server 层、存储引擎层。 在 server 层进行连接管理、查询缓存、语法解析、查询优化等操作在存储引擎层执行具体的数据存取操作。也就是说一条语句在 server 层中执行的成本是和它操作的表使用的存储引擎是没关系的所以关于这些操作对应的成本常数就存储在了 server_cost 表中而依赖于存储引擎的一些操作对应的成本常数就存储在了 engine_cost 表中。
5.3.5.1. mysql.server_cost 表
server_cost 表中在 server 层进行的一些操作对应的成本常数具体内容如下
SELECT * FROM mysql.server_cost;我们先看一下 server_cost 各个列都分别是什么意思
cost_name 表示成本常数的名称。cost_value 表示成本常数对应的值。如果该列的值为 NULL 的话意味着对应的成本常数会采用默认值。last_update 表示最后更新记录的时间。comment 注释。 从 server_cost 中的内容可以看出来目前在 server 层的一些操作对应的成 本常数有以下几种disk_temptable_create_cost 默认值 40.0 创建基于磁盘的临时表的成本如果增大这个值的话会让优化器尽量少的创建基于磁盘的临时表。disk_temptable_row_cost 默认值 1.0 向基于磁盘的临时表写入或读取一 条记录的成本如果增大这个值的话会让优化器尽量少的创建基于磁盘的临时表。key_compare_cost 0.1 两条记录做比较操作的成本多用在排序操作上如果增大这个值的话会提升 filesort 的成本让优化器可能更倾向于使用索引完成排序而不是 filesort。memory_temptable_create_cost 默认值 2.0 创建基于内存的临时表的成本如果增大这个值的话会让优化器尽量少的创建基于内存的临时表。 memory_temptable_row_cost 默认值 0.2 向基于内存的临时表写入或读 取一条记录的成本如果增大这个值的话会让优化器尽量少的创建基于内存的临时表。row_evaluate_cost 默认值 0.2 这个就是我们之前一直使用的检测一条记 录是否符合搜索条件的成本增大这个值可能让优化器更倾向于使用索引而不是直接全表扫描。 MySQL 在执行诸如 DISTINCT 查询、分组查询、Union 查询以及某些特殊条件 下的排序查询都可能在内部先创建一个临时表使用这个临时表来辅助完成查询 比如对于 DISTINCT 查询可以建一个带有 UNIQUE 索引的临时表直接把需要去重的记录插入到这个临时表中插入完成之后的记录就是结果集了。在数据量大的情况下可能创建基于磁盘的临时表也就是为该临时表使用MyISAM、InnoDB等存储引擎在数据量不大时可能创建基于内存的临时表也就是使用 Memory存储引擎。大家可以看到创建临时表和对这个临时表进行写入和读取的操作代价还是很高的就行了。 这些成本常数在 server_cost 中的初始值都是 NULL意味着优化器会使用它们的默认值来计算某个操作的成本如果我们想修改某个成本常数的值的话需要做两个步骤 对我们感兴趣的成本常数做 update 更新操作然后使用下边语句即可FLUSH OPTIMIZER_COSTS; 当然在你修改完某个成本常数后想把它们再改回默认值的话可以直接把cost_value 的值设置为 NULL再使用 FLUSH OPTIMIZER_COSTS 语句让系统重新加载。
5.3.5.2. mysql.engine_cost 表
engine_cost 表表中在存储引擎层进行的一些操作对应的成本常数具体内容如下
SELECT * FROM mysql.engine_cost;与 server_cost 相比engine_cost 多了两个列
engine_name 列指成本常数适用的存储引擎名称。如果该值为 default意味着对应的成本 常数适用于所有的存储引擎。device_type 列指存储引擎使用的设备类型这主要是为了区分常规的机械硬盘和固态硬盘不过在 MySQL 5.7.X 这个版本中并没有对机械硬盘的成本和固态硬盘的成本作区分所以该值默认是 0。 我们从 engine_cost 表中的内容可以看出来目前支持的存储引擎成本常数只有两个io_block_read_cost 默认值 1.0 从磁盘上读取一个块对应的成本。请注意我使用的是块而不是页这个词。对于 InnoDB 存储引擎来说一个页就是一个块不过对于 MyISAM 存储引擎来说默认是以 4096 字节作为一个块的。增大这个值会加重 I/O 成本可能让优化器更倾向于选择使用索引执行查询而不是执行全表扫描。memory_block_read_cost 默认值 1.0 与上一个参数类似只不过衡量的是从内存中读取一个块对应的成本。 怎么从内存中和从磁盘上读取一个块的默认成本是一样的这主要是因为 在 MySQL 目前的实现中并不能准确预测某个查询需要访问的块中有哪些块已经加载到内存中有哪些块还停留在磁盘上所以 MySQL 简单的认为不管这个块有没有加载到内存中使用的成本都是 1.0。 与更新 server_cost 表中的记录一样我们也可以通过更新 engine_cost 表中的记录来更改关于存储引擎的成本常数做法一样。
