印刻数据库

外观

MariaDB 执行计划分析 ​

执行计划概述 ​

什么是执行计划 ​

执行计划是 MariaDB 查询优化器生成的查询执行方案，包含了查询如何执行的详细信息，如使用的索引、表连接顺序、数据访问方式等。通过分析执行计划，可以了解查询的执行过程，找出性能瓶颈，从而进行针对性优化。

执行计划的重要性 ​

  • 帮助理解查询的执行过程
  • 识别性能瓶颈
  • 验证索引是否被正确使用
  • 比较不同查询写法的性能差异
  • 评估优化效果

执行计划生成过程 ​

  1. 词法分析：将 SQL 语句分解为标记
  2. 语法分析：检查 SQL 语句的语法正确性
  3. 语义分析：验证表、列和函数的存在性
  4. 查询优化：查询优化器生成多个执行计划，选择成本最低的一个
  5. 执行计划生成：生成最终的执行计划

查看执行计划 ​

EXPLAIN 命令 ​

EXPLAIN 命令是查看执行计划的主要工具，它可以显示 MariaDB 如何执行 SELECT 语句。

sql
-- 基本用法
EXPLAIN SELECT * FROM mytable WHERE created_at < '2025-01-01' ORDER BY id DESC LIMIT 100;

-- 显示扩展执行计划
EXPLAIN EXTENDED SELECT * FROM mytable WHERE created_at < '2025-01-01' ORDER BY id DESC LIMIT 100;

-- 显示分区信息
EXPLAIN PARTITIONS SELECT * FROM mytable WHERE created_at < '2025-01-01' ORDER BY id DESC LIMIT 100;

-- 显示格式化的 JSON 执行计划
EXPLAIN FORMAT=JSON SELECT * FROM mytable WHERE created_at < '2025-01-01' ORDER BY id DESC LIMIT 100;

-- 显示优化后的 SQL 语句
EXPLAIN EXTENDED SELECT * FROM mytable WHERE created_at < '2025-01-01' ORDER BY id DESC LIMIT 100;
SHOW WARNINGS;

EXPLAIN ANALYZE ​

MariaDB 10.1.2 及以上版本支持 EXPLAIN ANALYZE 命令，它可以实际执行查询并显示详细的执行统计信息。

sql
-- 显示实际执行计划
EXPLAIN ANALYZE SELECT * FROM mytable WHERE created_at < '2025-01-01' ORDER BY id DESC LIMIT 100;

SHOW WARNINGS ​

SHOW WARNINGS 命令可以显示优化器对查询的改写和优化信息，与 EXPLAIN EXTENDED 配合使用。

sql
EXPLAIN EXTENDED SELECT * FROM mytable WHERE created_at < '2025-01-01' ORDER BY id DESC LIMIT 100;
SHOW WARNINGS;

执行计划字段解读 ​

基本字段 ​

字段名称描述
id查询块 ID，用于标识查询中的不同部分
select_type查询类型，如 SIMPLE、PRIMARY、UNION、SUBQUERY 等
table访问的表名
partitions访问的分区，适用于分区表
type访问类型，如 ALL、index、range、ref、eq_ref、const、system 等
possible_keys可能使用的索引列表
key实际使用的索引
key_len使用的索引长度，用于判断索引使用的列数
ref与索引比较的列或常量
rows估计需要扫描的行数
filtered过滤后的行数百分比
Extra额外信息，如 Using index、Using where、Using filesort 等

select_type 详解 ​

  1. SIMPLE：简单查询，不包含子查询或 UNION
  2. PRIMARY：主查询，包含子查询或 UNION 时的外层查询
  3. SUBQUERY：子查询中的第一个 SELECT
  4. DERIVED：派生表，FROM 子句中的子查询
  5. UNION：UNION 中的第二个或后续 SELECT
  6. UNION RESULT：UNION 的结果集
  7. DEPENDENT SUBQUERY：依赖外部查询结果的子查询
  8. DEPENDENT UNION：依赖外部查询结果的 UNION
  9. MATERIALIZED：物化子查询

type 详解 ​

从最优到最差的访问类型：

  1. system：表只有一行记录（系统表）
  2. const：通过主键或唯一索引访问，只返回一行记录
  3. eq_ref：表连接时，被连接表使用主键或唯一索引访问
  4. ref：使用非唯一索引访问，可能返回多行
  5. range：使用索引范围扫描，如 BETWEEN、IN、>、< 等
  6. index：全索引扫描，比 ALL 好但比 range 差
  7. ALL：全表扫描，性能最差
  8. NULL：不访问表，直接返回结果

Extra 详解 ​

  1. Using index：使用索引覆盖查询，不需要回表访问数据
  2. Using where：使用 WHERE 条件过滤，但不使用索引
  3. Using index condition：使用索引条件下推，先在存储引擎层过滤数据
  4. Using filesort：需要额外的排序操作，性能较差
  5. Using temporary：需要使用临时表，性能较差
  6. Using join buffer：使用连接缓冲区优化连接操作
  7. Using sort_union：使用排序合并算法处理 OR 条件
  8. Using union：使用合并算法处理 OR 条件
  9. Using intersect：使用交集算法处理 AND 条件
  10. Impossible where：WHERE 条件永远为假，不返回任何行
  11. Select tables optimized away：优化器直接计算出结果，不访问表
  12. Zero limit：LIMIT 0，不执行查询

执行计划分析方法 ​

单表查询分析 ​

  1. 检查访问类型：优先选择 const、eq_ref、ref 等高效访问类型，避免 ALL 和 index
  2. 检查索引使用：确保使用了合适的索引，避免全表扫描
  3. 检查扫描行数：估计扫描行数应尽量少
  4. 检查过滤比例：filtered 字段应尽量高
  5. 检查 Extra 信息：避免 Using filesort 和 Using temporary

多表连接分析 ​

  1. 检查连接顺序：小表驱动大表，减少连接次数
  2. 检查连接类型：确保连接列有索引，使用 eq_ref 或 ref 连接类型
  3. 检查索引使用：确保连接列使用了合适的索引
  4. 检查 Extra 信息：避免 Using join buffer，说明连接列没有索引

子查询分析 ​

  1. 检查 select_type：避免 DEPENDENT SUBQUERY，它会为外部查询的每一行执行一次
  2. 考虑改写为 JOIN：某些子查询可以改写为 JOIN，提高性能
  3. 使用物化子查询：MariaDB 10.6+ 支持物化子查询，提高子查询性能

排序和分组分析 ​

  1. 检查 Using filesort：尽量避免，考虑使用索引排序
  2. 检查 Using temporary：尽量避免，考虑使用索引分组
  3. 确保排序和分组使用相同的索引：遵循最左前缀原则

执行计划优化技巧 ​

优化访问类型 ​

  1. 添加合适的索引：为 WHERE 条件、JOIN 条件、ORDER BY 和 GROUP BY 中的列添加索引
  2. 优化索引结构：使用联合索引，遵循最左前缀原则
  3. 避免索引失效：
    • 避免在查询条件中使用函数或表达式
    • 避免使用 !=、<>、NOT IN 等操作符
    • 避免使用 OR 连接不同的索引列
    • 确保查询条件与索引列类型一致

优化连接操作 ​

  1. 小表驱动大表：将行数少的表作为驱动表
  2. 确保连接列有索引：提高连接效率
  3. 使用合适的连接类型：根据业务需求选择 INNER JOIN、LEFT JOIN、RIGHT JOIN 等
  4. 避免笛卡尔积：确保连接条件正确

优化子查询 ​

  1. 改写为 JOIN：某些子查询可以改写为 JOIN，提高性能
  2. 使用 EXISTS 代替 IN：对于大数据集，EXISTS 通常比 IN 更高效
  3. 使用物化子查询：MariaDB 10.6+ 支持物化子查询，提高子查询性能
  4. 避免相关子查询：相关子查询会为外部查询的每一行执行一次

优化排序和分组 ​

  1. 使用索引排序：为 ORDER BY 列添加索引
  2. 使用索引分组：为 GROUP BY 列添加索引
  3. 确保排序和分组使用相同的索引：遵循最左前缀原则
  4. 避免 SELECT DISTINCT：DISTINCT 通常需要排序，影响性能

执行计划分析案例 ​

案例 1：全表扫描优化 ​

原始查询：

sql
SELECT * FROM users WHERE age > 30;

执行计划：

+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 100000 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+

分析：

  • type 为 ALL，全表扫描
  • possible_keys 为 NULL，没有合适的索引
  • rows 为 100000，需要扫描大量行

优化方案： 为 age 列添加索引

sql
CREATE INDEX idx_age ON users(age);

优化后的执行计划：

+----+-------------+-------+------------+-------+---------------+--------+---------+------+-------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref  | rows  | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+-------+----------+-----------------------+
|  1 | SIMPLE      | users | NULL       | range | idx_age       | idx_age| 4       | NULL | 33333 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+-------+----------+-----------------------+

优化效果：

  • type 变为 range，使用索引范围扫描
  • key 为 idx_age，使用了创建的索引
  • rows 变为 33333，扫描行数减少
  • Extra 为 Using index condition，使用了索引条件下推

案例 2：排序优化 ​

原始查询：

sql
SELECT * FROM orders WHERE user_id = 1 ORDER BY created_at DESC;

执行计划：

+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------------+
| id | select_type | table  | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra                       |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------------+
|  1 | SIMPLE      | orders | NULL       | ref  | idx_user_id   | idx_user_id | 4     | const |   10 |   100.00 | Using index condition; Using filesort |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------------+

分析：

  • type 为 ref，使用了 user_id 索引
  • Extra 包含 Using filesort，需要额外排序

优化方案： 创建联合索引，包含 user_id 和 created_at

sql
CREATE INDEX idx_user_id_created_at ON orders(user_id, created_at DESC);

优化后的执行计划：

+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys           | key                     | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | orders | NULL       | ref  | idx_user_id,idx_user_id_created_at | idx_user_id_created_at | 8       | const |   10 |   100.00 | Using index condition |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+

优化效果：

  • Extra 中的 Using filesort 消失，使用索引排序
  • 提高了查询性能，避免了额外的排序操作

执行计划分析工具 ​

MariaDB 内置工具 ​

  1. EXPLAIN：基本执行计划查看
  2. EXPLAIN EXTENDED：扩展执行计划，显示优化后的 SQL
  3. EXPLAIN PARTITIONS：显示分区信息
  4. EXPLAIN FORMAT=JSON：JSON 格式的执行计划，包含更详细的信息
  5. EXPLAIN ANALYZE：显示实际执行统计信息

第三方工具 ​

  1. Percona Monitoring and Management (PMM)：

    • 提供可视化的执行计划分析
    • 支持比较不同查询的执行计划
    • 提供优化建议

  2. MySQL Workbench：

    • 可视化执行计划查看
    • 支持执行计划比较
    • 提供索引建议

  3. phpMyAdmin：

    • 基本执行计划查看
    • 适合简单查询分析

执行计划分析最佳实践 ​

  1. 定期分析慢查询的执行计划：

    • 结合慢查询日志，分析执行计划
    • 找出性能瓶颈，进行针对性优化

  2. 比较不同查询写法的执行计划：

    • 尝试不同的查询写法
    • 比较执行计划，选择最优方案

  3. 验证索引使用情况：

    • 确保创建的索引被正确使用
    • 删除未使用的索引，减少维护成本

  4. 分析复杂查询的执行计划：

    • 分解复杂查询为多个简单查询
    • 分析每个部分的执行计划
    • 优化每个部分，然后组合

  5. 使用 EXPLAIN ANALYZE 验证实际执行情况：

    • EXPLAIN 显示的是估计值
    • EXPLAIN ANALYZE 显示实际执行统计信息
    • 结合使用，更准确地分析性能

常见问题（FAQ） ​

Q: 为什么执行计划显示使用了索引，但查询仍然很慢？ ​

A: 可能的原因：

  • 索引选择性差，返回了大量行
  • 索引覆盖不全，需要回表访问数据
  • 服务器负载高，资源不足
  • 锁等待或死锁
  • 查询涉及多个表连接，连接效率低

Q: 如何理解执行计划中的 rows 字段？ ​

A: rows 字段是查询优化器估计的需要扫描的行数，不是实际行数。它基于索引统计信息计算得出，可能与实际行数有差异。可以使用 EXPLAIN ANALYZE 查看实际扫描行数。

Q: 为什么相同的查询在不同时间的执行计划不同？ ​

A: 可能的原因：

  • 表统计信息更新，导致优化器选择不同的执行计划
  • 数据分布发生变化
  • 服务器负载变化
  • 优化器版本变化
  • 配置参数变化

Q: 如何强制使用特定索引？ ​

A: 使用 FORCE INDEX 提示：

sql
SELECT * FROM mytable FORCE INDEX (idx_name) WHERE name = 'test';

注意：应谨慎使用，优化器通常会选择最优索引。

Q: 如何查看优化器对查询的改写？ ​

A: 使用 EXPLAIN EXTENDED 配合 SHOW WARNINGS：

sql
EXPLAIN EXTENDED SELECT * FROM mytable WHERE created_at < '2025-01-01';
SHOW WARNINGS;

可以查看优化器对查询的改写和优化信息。

总结 ​

执行计划分析是 MariaDB 性能优化的重要手段，通过分析执行计划，可以了解查询的执行过程，找出性能瓶颈，从而进行针对性优化。执行计划的主要字段包括 id、select_type、table、type、possible_keys、key、key_len、ref、rows、filtered 和 Extra。

执行计划分析的主要步骤包括：

  1. 查看执行计划
  2. 解读执行计划字段
  3. 识别性能瓶颈
  4. 提出优化方案
  5. 验证优化效果

建议 DBA 定期分析慢查询的执行计划，结合其他监控数据，持续优化数据库性能。同时，使用 EXPLAIN ANALYZE 可以查看实际执行统计信息，更准确地分析性能问题。