前言

在数据库性能调优的过程中，MySQL的EXPLAIN命令是开发者不可或缺的工具之一。它能够帮助我们理解SQL查询的执行计划，揭示MySQL优化器如何处理查询请求，从而指导我们进行索引优化、查询结构调整等性能提升工作。

EXPLAIN简介

EXPLAIN是一个SQL语句，用于获取MySQL如何执行特定SQL查询的信息。它展示了查询的执行计划，包括表的读取顺序、使用的索引、数据读取方式、预期扫描的行数等关键信息，是优化查询性能的第一步。

如何使用EXPLAIN

使用非常简单，只需在查询语句前加上EXPLAIN关键字即可，例如：

EXPLAIN SELECT * FROM users WHERE username = 'value';

解析EXPLAIN输出

以下是EXPLAIN输出的详细列描述，包括每列的含义及常见值：

列名

含义

常见值及详细解释

优化建议

id

查询中操作的执行顺序，或操作标识符

数字，如1, 2等；代表执行顺序或操作标识，子查询或UNION内的查询会有不同标识

无直接优化建议，但可用于理解查询执行流程，辅助分析其他列信息

select_type

查询类型

SIMPLE, PRIMARY, UNION, SUBQUERY等

简化子查询或联接，减少复杂查询类型以提高效率

table

当前操作作用的表名

表名

理解查询涉及的表，分析表间关系和访问顺序，优化关联策略

partitions

表的分区信息，如果使用了分区

分区名称

根据分区情况优化查询条件，确保分区裁剪有效利用

type

访问类型，表明MySQL如何读取数据

ALL, index, range, ref, eq_ref, const, system(NULL)等

优化至更高效的类型，如从ALL转为index或range，尽可能利用索引避免全表扫描

index:

索引全扫描，适用于覆盖索引查询。

优化建议：如果查询还涉及非索引列，考虑调整索引或查询以利用覆盖索引。

range:

索引范围扫描，用于>, <, BETWEEN等查询。

优化建议：确保索引设计合理，避免范围过大导致扫描过多行。

ref:

索引引用，用于非唯一性索引的等值匹配。

优化建议：确认ref列的值是否高效，考虑调整索引或查询条件。

eq_ref:

唯一性索引扫描，效率高。

优化建议：通常无需优化，确保关联字段已索引。

const, system:

针对常量查询，效率极高。

优化建议：保持查询简洁，确保索引维护良好。

possible_keys

可能用到的索引列表

索引名，逗号分隔；NULL表示无索引可用

若实际未使用，考虑添加或调整索引结构，使查询能利用这些索引

key

实际选用的索引

索引名，NULL表示未使用索引

确保选用的索引是最优的，若无或不理想，考虑调整查询或创建更有效的索引

key_len

实际使用索引的长度，字节数

字节数

辅助理解索引使用程度，过短可能意味着索引未充分利用，检查索引字段是否完整包含查询条件

ref

与索引列相比较的列或常量，用于 eq_ref 和 ref 类型

const（常量）、func（函数结果）、具体列名等

查看是否为预期的比较值，优化查询条件或索引以利用更高效的比较方式

func: 确认函数调用是否高效，考虑是否可替换为更简单的表达式或直接值。

具体列名: 分析关联表的索引使用是否恰当，优化关联字段的选择或索引设计。

rows

预估的扫描行数

数字，预估执行计划需要扫描的行数

优化至更低的行数，通过索引优化或调整查询条件减少扫描行数，提升效率

filtered

表示MySQL通过查询条件过滤后返回结果的比例

百分比值，如25.00%

若filtered值低，考虑优化查询条件或索引，减少不必要的行扫描

Extra

额外的执行信息，包含重要性能提示

Using index:

表示查询只使用了索引，无需访问表数据文件，适用于索引覆盖查询。

优化建议：此状态已较优，但需关注查询是否真正需要所有返回列。

关注Using filesort和Using temporary，优化索引以避免临时表和文件排序，追求Using index以减少回表操作

Using where:

表示MySQL服务器需要使用WHERE子句来过滤结果集，即使使用了索引。

优化建议：检查查询条件，确保索引能够有效过滤数据，减少Using where的需要。

Using temporary:

表示MySQL需要创建临时表来存储结果，常见于GROUP BY、ORDER BY操作。

优化建议：考虑优化查询逻辑或创建合适的索引以避免临时表的使用。

Using filesort:

表示MySQL需要进行排序操作，常见于ORDER BY操作。

优化建议：尝试使用索引来覆盖排序需求，或调整ORDER BY字段的索引顺序。

Range checked for each record (index map: N):

说明MySQL无法有效利用索引范围扫描，而是逐行检查。

优化建议：重新考虑索引设计，确保查询条件能够有效利用索引。

慢查询案例分析及优化方案

案例1：全表扫描

查询语句:

SELECT * FROM Users WHERE username LIKE '%search_term%';

EXPLAIN输出:

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

10.00

Using where

问题分析:

全表扫描是因为LIKE操作符以通配符开头，无法利用索引。

优化方案:

  • 改变查询逻辑，避免前导通配符，如使用username LIKE 'search_term%'。

  • 或者，考虑使用全文索引进行模糊搜索。

案例2：索引未完全覆盖

查询语句:

SELECT id, name, email FROM Customers WHERE country='US';

EXPLAIN输出:

id

select_type

table

partitions

type

possible_keys

key

key_len

ref

rows

filtered

Extra

1

SIMPLE

Customers

NULL

ref

idx_country

idx_country

3

const

50000

100.00

Using index condition; Using where

问题分析:

尽管使用了索引idx_country，但由于查询了非索引列id和email，导致了回表操作。

优化方案:

  • 创建一个覆盖索引(country, id, name, email)，以避免回表。

案例3：范围查询导致索引失效

查询语句:

SELECT * FROM Orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

EXPLAIN输出:

id

select_type

table

partitions

type

possible_keys

key

key_len

ref

rows

filtered

Extra

1

SIMPLE

Orders

NULL

range

idx_order_date

idx_order_date

4

NULL

365000

100.00

Using where

问题分析:

虽然使用了索引，但是范围查询导致索引扫描了大量行。

优化方案:

  • 如果查询经常发生且性能要求高，考虑分区表按日期分区。

案例4：未利用索引的排序

查询语句:

SELECT * FROM Products ORDER BY price DESC LIMIT 10;

EXPLAIN输出:

id

select_type

table

partitions

type

possible_keys

key

key_len

ref

rows

filtered

Extra

1

SIMPLE

Products

NULL

ALL

NULL

NULL

NULL

NULL

500000

100.00

Using filesort

问题分析:

因为没有针对price的索引，所以进行了全表扫描并使用了文件排序。

优化方案:

  • 添加索引(price)，以便利用索引进行排序。

案例5：联接查询未优化

查询语句:

SELECT Orders.*, Customers.name 
FROM Orders 
JOIN Customers ON Orders.customer_id = Customers.id 
WHERE Customers.country = 'US';

EXPLAIN输出:

id

select_type

table

partitions

type

possible_keys

key

key_len

ref

rows

filtered

Extra

1

SIMPLE

Customers

NULL

ref

idx_country

idx_country

3

const

50000

100.00

Using where; Using index

1

SIMPLE

Orders

NULL

ref

idx_customer_id

idx_customer_id

4

Customers.id

5000

100.00

-

问题分析:

  • 虽然使用了索引，但是由于JOIN顺序和索引设计，可能导致多次索引查找。

优化方案:

  • 确保Orders表上的customer_id索引是复合索引(customer_id, other_used_columns)，以减少回表。

  • 考虑调整JOIN的顺序，先从较小的表开始。

案例6：子查询效率低下

查询语句:

SELECT * FROM Products 
WHERE product_id IN (SELECT product_id FROM Orders WHERE status='shipped');

EXPLAIN输出:

id

select_type

table

partitions

type

possible_keys

key

key_len

ref

rows

filtered

Extra

1

PRIMARY

Products

NULL

ALL

NULL

NULL

NULL

NULL

500000

100.00

Using where

1

DEPENDENT SUBQUERY

Orders

NULL

ref

idx_status

idx_status

4

const

100000

100.00

Using index

问题分析:

  • 子查询导致了Products表的全表扫描。

优化方案:

  • 使用JOIN代替子查询：

SELECT p.* 
FROM Products p 
JOIN Orders o ON p.product_id = o.product_id AND o.status='shipped';

  • 确保Orders表上的status列有索引，并考虑复合索引(status, product_id)以优化JOIN。

优化建议总结：

  • 索引优化：根据possible_keys与key列，检查是否有效利用索引，考虑添加或调整索引以覆盖查询条件。

  • 减少回表：通过创建覆盖索引，使得查询所需数据全部包含在索引中，避免额外的回表操作。

  • 避免全表扫描：优化查询条件和索引，确保至少一个条件能有效利用索引，减少ALL类型的查询。

  • 减少排序与临时表：对于Using filesort和Using temporary，考虑调整索引以支持排序和避免临时表的产生。

  • 精简查询逻辑：简化子查询和复杂连接，减少SUBQUERY和复杂JOIN的使用，提高查询效率。