Explain参数解释和查询成本分析

Explain参数解释和查询成本分析 #

Explain #

通过执行 explain [sql] 得到执行计划

如果from中包含子查询，会查并生成临时表

explain执行完之后执行show warnings，有时候可以直接拷贝出来直接使用，大多时候可以用于参考执行

结果列分析 #

id #

有几个select就有几个id，id按select出现的顺序增长，id大的先执行，null最后执行，相同则从上往下执行

select_type #

  • simple 简单查询，没有子查询和union
  • primary 复杂查询中最外层迭代select
  • subquery 包含在select中的子查询，不在from中
  • dependent subquery select语句出现的子查询，依赖外部查询
  • derived 包含在from子句的子查询，会把结果放在临时表（派生表）

table #

这一行的查询在访问哪个表

  • from子句有子查询时，table列是格式，表示当前查询依赖id=N的查询，会先执行id=N的查询
  • 有union时，union result的table列的值为union 1,2，1和2表示参与union的select的id

type #

表示关联类型或访问类型，表示mysql决定如何查找表中的行，查找数据行记录的大概范围, 从最优到最差依次为：

system > const > eq_ref > ref > range > index > ALL 一般来说应该最少把查询保持在range级别，最好达到ref

  • system MyIsm出现，表为空或者记录唯一时
  • const 主键索引或唯一索引的等值查询
  • eq_ref join的时候有驱动表和被驱动表，被驱动表走唯一或者主键索引时
  • ref 非主键或非唯一索引的等值查询，关联查询时被驱动表的驱动键走非唯一索引
  • range 一般索引的范围查询
  • index 全表扫描，非聚簇索引的叶子节点
  • All 聚簇索引的全表扫描

possible keys列 #

显示查询可能使用哪些索引来查找

  • posible_keys有列而key为null，是因为根据成本分析，走索引比全表扫描成本高，选择了全表扫描
  • 如果possible_keys为null，则没有索引。应检查where子句看是否可以创造索引提升查询性能

key列 #

显示MySQL实际采用哪个索引优化对该表的访问

  • 如果没有用索引，该列为null
  • 如果想强制MySQL使用或者忽视possible_keys列中的索引，可以在查询时使用force index或ignore index 有时候数据量变化是会出现索引失效，可以考虑加上force index

key_len #

表示索引使用的字节数，可以判断索引的使用情况

特别是组合索引情况下，判断索引有多少部分被使用到

如abc组合索引，查询条件为ac，正常会使用a索引部分，有时会索引下推，用到ac索引

ref列 #

显示在key列记录的索引中，表查找值所用到的列或常量

如 const、table.col1

rows列 #

是MySQL根据索引统计信息估计要读取并检测的行数，不是结果集的行数

filtered列 #

是已筛选列，表示按表条件筛选的表行的估计百分比，最大值是100%，意味着没有筛选

越小表示过滤量的增加

rows显示检查的估计行数

rows * filtered 显示与下表连接的行数，如果rows=1000，filtered=50.00，那么与下表联表的行数为1000*50%=500

extra列 #

  • using index 使用覆盖索引
  • using where 使用where语句处理结果，且查询列未被索引覆盖
  • using indxe condition 查询列不完全被索引覆盖，where条件中是一个前导列的范围
  • using temporary mysql需要创建临时表处理查询，必须优化，临时表开销大，首先想到用索引优化
  • using filesort 使用外部排序而不是索引排序，数据较小的时候从内存排序，否则需要在磁盘完成排序。这种情况下应考虑使用索引优化

全表扫描成本分析 #

MySQL的成本：

  1. MySQL执行一个查询可以有不同执行方案，MySQL会选择成本最低的方案执行查询
  2. MySQL中一条查询语句的执行成本：IO成本+CPU成本
  3. IO成本：一般MyISAM和InnoDB都是将数据和索引存储在磁盘，查询时需要将数据和索引加载到内存，这个加载过程损耗的时间为IO成本
  4. CPU成本：读取和检测记录是否满足对应的搜索条件、对结果集进行排序的操作损耗的时间为CPU成本

对Innodb来说，全表扫描的含义是把聚簇索引中的记录都依次和给定的所有条件进行一下比较，把符合搜索条件的记录加入到结果集，所以需要将聚簇索引所在的页面加载到内存，再检测记录是否符合搜索条件

查询成本 = 把二叉树叶子节点load进内存的IO成本 + 和叶子节点中每条数据compare的CPU成本

要load多少次数据到内存？ 叶子节点有多少条数据？

表统计信息表 #

MySQL为每个表维护了一系列的统计信息，可以通过以下语句查询指定表统计信息

show table status like 'table_name'

内部的值和实际有一点偏差，类似估算值,从中可以查到Data_length字段，是数据表总大小

rows是表记录数，对Innodb来说是一个估计值

IO成本 #

Data_length = 聚簇索引的页面数量 * 每个页面的大小（16kb）

聚簇索引的页面数量=Data_length ÷ 16 ÷ 1027

IO成本 = 聚簇索引的页面数量 * 1.0 + 1.1

页面数 * 加载一个页面的IO成本常数 + 微调值

微调值是硬编码在代码中，没有注释且值十分小，不影响大方向分析

CPU成本 #

= 统计数据表中的rows * 0.2 + 1.0

是统计记录数 * 访问一条记录需要的CPU成本常数 + 微调值

总成本 = IO成本 + CPU成本

上面分析的是聚簇索引，直接扫数据，没有回表

可以自行分析二级索引

索引优化 #

  • 联合索引第一个字段用范围查询不走索引 第一个字段就用范围查询，回表效率不高，不如全表扫描

  • 联合索引第一个字段Like，走索引 可以在索引遍历过程中，对索引包含的所有字段先做判断，过滤不符合条件的记录再回表，减少回表次数

  • 覆盖索引优化 如果有查询经常要取某几个字段和筛某几个字段，可以搞成联合索引，这样可以直接从索引树上获取数据，不用回表

Join优化 #

  • 嵌套循环连接Nested-Loop Join(NLJ)算法 一次一行循环地从驱动表读取数据，取关联字段在被驱动表找到满足条件的行，再取两表结果合集

  • 基于块地嵌套循环连接Block Nested-Loop Join(BNL)算法 把驱动表的数据读取到join_buffer中，然后扫描被驱动表，把被驱动表数据拉出和驱动表数据对比

应该尽量嵌套循环进行连接，使用NLJ算法

2025-05-03