MySQL SQL 调优与执行计划

在实际的生产环境中，SQL 调优是 MySQL 数据库管理的重中之重。通过对 EXPLAIN 执行计划的深入剖析以及对常见慢查询的优化，可以大幅提升系统的响应速度和并发处理能力。

一、 EXPLAIN 执行计划深度解析

在执行查询语句之前，MySQL 优化器（Optimizer）会预估查询的执行成本，并生成一种执行计划，决定采用何种索引和表连接方式。通过 EXPLAIN 关键字，可以查看 MySQL 优化器如何执行 SQL 查询。

EXPLAIN SELECT * FROM users WHERE age = 30 AND status = 1;

1. 核心字段说明

id：SELECT 查询的序列号。id 值越大，执行优先级越高；id 相同，从上往下执行。
select_type：查询类型。
- SIMPLE：简单查询，不包含子查询或 UNION。
- PRIMARY：复杂查询中最外层的查询。
- SUBQUERY：SELECT 列表中的子查询。
- DERIVED：FROM 子句中的子查询（派生表）。
table：查询涉及的表名或表的别名。
type：连接类型/访问类型（非常重要！代表查询的好坏）。
- 性能由高到低：system > const > eq_ref > ref > range > index > ALL。
- const：通过主键或唯一索引等值查询，最多匹配一行数据，极快。
- eq_ref：多表连接时，从表使用主键或唯一索引作为连接条件。
- ref：使用普通索引等值查询，可能匹配多行数据。
- range：使用索引进行范围查询（如 BETWEEN、<、>、IN 等）。
- index：全索引扫描，遍历整个索引树。
- ALL：全表扫描，遍历聚集索引的叶子节点，性能最差。
  
  调优目标：尽量将查询优化到 range 级别以上，最好能达到 ref 或 const。
possible_keys：查询过程中可能使用到的索引。
key：查询过程中实际使用的索引。如果为 NULL，说明未使用索引。
key_len：查询中实际使用到的索引长度（字节数）。可以通过它来判断复合索引中究竟使用了哪几个列。
ref：显示索引的哪一列被使用了，如果可能的话，是一个常数。
rows：MySQL 预估为了找到所需行而需要读取的平均行数。
Extra：包含不适合在其他列中显示但十分重要的额外信息。
- Using index：使用了覆盖索引，避免了回表查询，性能极佳。
- Using index condition：使用了索引下推（ICP）技术。
- Using where：MySQL 在存储引擎层面没有完成所有数据过滤，需要在 Server 层通过 WHERE 条件进一步过滤。
- Using filesort：MySQL 需要使用外部甚至磁盘排序，而无法利用索引顺序。这说明存在性能瓶颈，应尽量通过建立合适的索引来避免。
- Using temporary：MySQL 需创建一张临时表来保存中间结果以完成查询。常见于 ORDER BY 和 GROUP BY 等操作，性能开销极大，必须优化。

二、索引失效诊断与常见场景

明明在表上建了索引，但执行计划的 type 列却显示为 ALL，这种现象称为“索引失效”。以下是导致索引失效的“罪魁祸首”：

左模糊匹配：使用 LIKE '%xxx' 或 LIKE '%xxx%' 会导致无法匹配 B+树的最左前缀结构，进而退化为全表扫描或全索引扫描。
违背最左前缀法则：对于联合索引 (a, b, c)，如果查询条件缺少列 a，如 WHERE b = 1 AND c = 2，此时无法使用联合索引。
在索引列上做任何运算或函数操作：例如 WHERE DATE(create_time) = '2023-10-01' 或 WHERE id + 1 = 10，MySQL 会放弃使用索引，因为修改后的值在 B+树节点中找不到对应关系。
隐式类型转换：如果列是 VARCHAR 类型，但查询条件传入的是数字（如 WHERE phone = 13800000000），MySQL 会隐式地在列上套用转换函数（等价于 CAST(phone AS INT)），导致索引失效。
范围查询右侧的列索引失效：针对联合索引 (a, b, c)，假如有查询条件 WHERE a = 1 AND b > 2 AND c = 3，此时只能使用到 a 和 b 字段，c 字段无法走索引。
不等于（!= 或 <>）：大范围的数据过滤或非等值匹配。
IS NULL / IS NOT NULL：视具体分布情况而定。如果 MySQL 优化器预估走全表扫描的成本比走索引还要低（即需大量回表），则会直接走全表扫描。

三、深度 SQL 调优实战指南

1. 结构化表与索引设计

控制单表规模：单表数据量一旦逼近 2000 万，B+树可能向 4 层演进，导致多一次磁盘 I/O。应考虑清理归档历史数据或实施分库分表。
字段类型调优：
- 尽量使用较小的数据类型，比如用 TINYINT 代替 INT。
- 建议使用 VARCHAR 而非 CHAR 存储变长字符串，但要预留合理的上限。
精减索引数量：一张表的索引数量最好不要超过 5 个。大量且臃肿的索引会极大地拖慢 INSERT、UPDATE 速度，因为需要同步维护多棵 B+树。

2. 编写高性能 SQL 的艺术

告别 SELECT *：仅选取必要的列，最大程度促发覆盖索引（不用回表）。
深翻页（Deep Paging）优化：当执行 SELECT * FROM table LIMIT 1000000, 10 时，MySQL 会依次获取 1000010 条记录，然后舍弃前 1000000 条，性能奇差。 解决方案（延迟关联 / 子查询法）：先通过覆盖索引极速查出所需分页的 ID，再使用这些 ID 与原表通过内连接获取全部数据。
```
-- 优化前
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;

-- 优化后 (利用覆盖索引极速拉取 10 条主键 ID，再回表查询)
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 1000000, 10
) tmp ON o.id = tmp.id;
```
分页使用自增主键：配合 WHERE id > 上一次的最大ID 来避免深翻页过程的遍历。

3. Join 连接与排序

小表驱动大表：这是 Nested-Loop Join 算法的核心原则。用记录少的表（或经由 WHERE 过滤后结果集小的表）去驱动大表，从而降低内层大表的循环探测次数。
关联字段务必建索引：多表 JOIN 的核心，连接键如果没有索引会触发极为糟糕的 Block Nested-Loop Join 或大量全表扫描。
利用索引进行排序 (Avoid filesort)：如果查询既有 WHERE 过滤又有 ORDER BY 排序，可以通过将两者合并成一个联合索引来消除 Using filesort 排序。例如：针对 SELECT * FROM user WHERE city = 'Beijing' ORDER BY age，应当建立联合索引 (city, age)。MySQL 会利用 B+树的天然顺序性直接输出。

MySQL SQL 调优与执行计划​

一、 EXPLAIN 执行计划深度解析​

1. 核心字段说明​

二、 索引失效诊断与常见场景​

三、 深度 SQL 调优实战指南​

1. 结构化表与索引设计​

2. 编写高性能 SQL 的艺术​

3. Join 连接与排序​