如何使用 MySQL 的 EXPLAIN 语句进行查询分析？

Explain 属性先容

explain 主要用来 SQL 分析，它主要的属性详解如下：

• id : 查询的执行顺序的标识符，值越大优先级越高。简单查询的 id 通常为 1，复杂查询（如包含子查询或 UNION）的 id 会有多个。
  
• select_type （紧张）：查询的类型，如 SIMPLE（简单查询）、PRIMARY（主查询）、SUBQUERY（子查询）等。
  
• table : 查询的数据表。
  
• type （紧张）：访问类型，如 ALL（全表扫描）、index（索引扫描）、range（范围扫描）等。一般来说，性能从好到差的顺序是：const > eq_ref > ref > range > index > all。
  
• possible_keys : 可能用到的索引。
  
• key （紧张）：实际用到的索引。
  
• key_len : 用到索引的长度。
  
• ref : 表现索引的哪一列被使用。
  
• rows （紧张）：估计要扫描的行数，值越小越好。
  
• filtered : 表现查询条件过滤掉的行的百分比。一个高百分比表现查询条件的选择性好。
  
• Extra （紧张）：额外信息，如 Using index （表现使用覆盖索引）、Using where （表现使用 WHERE 条件进行过滤）、Using temporary （表现使用临时表）、Using filesort （表现需要额外的排序步骤）。
  

type 详解：

• system：表现查询的表只有一行（系统表）。这是一个特殊的情况，不常见。
  
• const：表现查询的表最多只有一行匹配结果。这通常发生在查询条件是                                        主键                                  主键                     主键或                                        唯一索引                                  唯一索引                     唯一索引，并且是常量比力。
  
• eq_ref：表现对于每个来自前一张表的行，MySQL 仅访问一次这个表。这通常发生在连接查询中使用主键或唯一索引的情况下。
  
• ref：MySQL 使用非唯一索引扫描来查找行。查询条件使用的索引优劣唯一的（如平凡索引）。
  
• range：表现 MySQL 会扫描表的一部分，而不是全部行。范围扫描通常出现在使用索引的范围查询中（如 BETWEEN 、 >, <, >=, <= ）。
  
• index：表现 MySQL 扫描索引中的所有行，而不是表中的所有行。即使索引列的值覆盖查询，也需要扫描整个索引。
  
• all（性能最差）：表现 MySQL 需要扫描表中的所有行，即全表扫描。通常出现在没有索引的查询条件中。
  

SQL 实战

• 建表语句
  

1. CREATE TABLE employees (
2.     employee_id INT PRIMARY KEY,
3.     first_name VARCHAR(50),
4.     last_name VARCHAR(50),
5.     department_id INT,
6.     salary DECIMAL(10, 2),
7.     hire_date DATE,
8.     INDEX (department_id)
9. );

复制代码

• 填充数据
  

• 脚本下载地点：https://restful.doublefenzhuan.me/public/bad6bf7e-3efa-48ff-9239-8b20bbb812e2-employee_data.sql
  

• 执行分析语句
  

1. EXPLAIN SELECT employee_id, first_name, last_name, salary
2. FROM employees
3. WHERE department_id = 5 AND salary BETWEEN 50000 AND 100000
4. ORDER BY salary DESC;

复制代码

• 执行结果：
  
  
  
  

分析 EXPLAIN 结果的思路和过程：

• 起首看 type 列：
  

• ref 表现使用非唯一索引扫描，这个服从还可以，但不是最优的 const
  
• 如果看到 ALL 就说明全表扫描，需要立刻优化
  
• 最好是能看到 const, eq_ref 如许的类型
  

• 分析 rows 列：
  

• 当前表现 482 行，需要评估这个数字是否公道
  
• 如果 rows 数值过大，说明扫描的行数太多，需要思量优化
  

• 检查 Extra 列：
  

• 出现 “Using where” 说明需要在服务器层进行数据过滤
  
• “Using filesort” 表现需要额外的排序操作，应该尽量制止
  
• “Using index” 表现使用了覆盖索引，这是较好的情况
  

优化方案

针对 type=ref 的优化：

• 当前使用的是单列索引(department_id)
  
• 可以创建联合索引: (department_id, salary)
  
• 因为查询同时用到这两个字段作为条件
  

ALTER TABLE employees ADD INDEX idx_dept_salary (department_id, salary);
执行

分析优化第一次后的结果

• 变化对比：
  

• type 从 ref 变成了 range：
  
  
  
  • ref 类型特点：表现使用非唯一索引的等值查询，只用到了 department_id = 5 这个等值条件。
    
  • range 类型特点：表现索引范围扫描，发生在使用 >, <, BETWEEN, IN, LIKE 等操作符时(这里是因为 salary BETWEEN 50000 AND 100000 条件)
    
  
  
• rows 从 482 减少到 241
  
• key 使用了新建的 idx_dept_salary 索引
  
• Extra 表现 “Using index condition; Backward index scan”
  

• 优化结果分析：
  

• 扫描行数减少了约50%，说明索引结果不错
  
• range 类型表树模围扫描，这是因为 salary BETWEEN 条件
  
• Backward index scan 说明在处理 ORDER BY salary DESC
  

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。