【MySQL】SQL调优

【MySQL】SQL调优

一、执行计划EXPLAIN：SQL性能分析利器

要优化SQL，首先要读懂执行计划。EXPLAIN 命令能模拟MySQL优化器执行SQL语句的过程，输出查询的关键信息，是判断SQL效率的核心工具。

1.1 执行计划基本用法

在SELECT、DELETE、UPDATE语句前加上EXPLAIN，即可查看执行计划：

-- 查看单表查询执行计划
EXPLAIN SELECT id, name, age FROM index_demo WHERE id = 1020000;

-- 查看多表连接执行计划
EXPLAIN SELECT s.name, a.username FROM student s JOIN account a ON s.id = a.student_id;

1.2 执行计划核心字段详解

执行计划包含12个字段，重点关注type、key、rows、Extra四个字段，其余字段辅助分析：

1.2.1 type列：连接类型（性能从优到差）

type 列表示MySQL如何连接表，直接反映查询效率，优先级如下：
system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL

类型	适用场景	示例
system	表中只有1行数据（MyISAM特有，InnoDB不支持）	查询MyISAM单行情报表
const	主键/唯一索引与常量等值匹配	WHERE id = 1020000（id为主键）
eq_ref	多表连接，关联条件为主键/唯一索引等值匹配	student.id = account.student_id（均为主键）
ref	普通索引与常量匹配，返回多行	WHERE class_id = 1（class_id为普通索引）
ref_or_null	普通索引匹配，包含NULL值查询	WHERE name = 'user1' OR name IS NULL
index_merge	多索引联合查询（OR/AND连接）	WHERE id = 100 OR name = 'user2'
range	索引范围查询（>、<、BETWEEN、IN等）	WHERE id BETWEEN 1000 AND 2000
index	扫描全索引树（无WHERE，仅排序/分组）	SELECT * FROM index_demo ORDER BY sn
ALL	全表扫描（最差）	WHERE age = 18（age无索引）

关键结论：生产环境中应避免ALL（全表扫描）和index（全索引扫描），至少优化到range级别，最优为const/eq_ref。

1.2.2 key列：实际使用的索引

• 若为NULL，表示未使用索引，需检查WHERE条件或创建索引。
• 若显示索引名（如PRIMARY、idx_name），表示使用该索引。
• 示例：key: PRIMARY 表示使用主键索引。

1.2.3 rows列：预估扫描行数

• 表示MySQL预估需要扫描的行数，值越小效率越高。
• 注意：InnoDB中是估算值，非精确值，但可反映查询的大致效率。
• 示例：rows: 1 表示仅需扫描1行，rows: 992356 表示需扫描近百万行（全表扫描）。

1.2.4 Extra列：额外执行信息

Extra 列包含查询的关键执行细节，出现以下值需重点优化：

Extra值	含义	优化建议
Using filesort	非索引列排序，触发文件排序	为排序列创建索引
Using temporary	需创建临时表存储中间结果（如非索引分组）	为分组列创建索引
Using where	全表扫描+条件过滤	为WHERE条件列创建索引
Using index	索引覆盖（查询列均在索引中，无需回表）	保持查询列不超出索引范围
Using index condition	索引下推（存储引擎层过滤条件）	无需优化，属于高效执行

反面案例：Extra: Using filesort; Using temporary 表示既触发文件排序，又创建临时表，性能极差，需紧急优化。

二、索引优化：从基础到高级

索引是SQL调优的核心，合理设计索引能将查询时间从秒级降至毫秒级。重点掌握覆盖索引、回表查询、索引合并等高级技巧。

2.1 覆盖索引：避免回表查询

核心概念

• 覆盖索引：查询列（SELECT子句）全部包含在索引中，无需从主键索引查询数据（回表），直接返回索引中的值。
• 优势：减少磁盘IO，提升查询效率，执行计划中Extra 显示 Using index。

实战示例

-- 1. 创建复合索引（包含mail、age、class_id）
CREATE INDEX idx_mail_age_classid ON index_demo(mail, age, class_id);

-- 2. 查询列均在索引中（触发覆盖索引）
EXPLAIN SELECT mail, age, class_id FROM index_demo WHERE mail = '1020000@qq.com';
-- 执行计划：type=ref，key=idx_mail_age_classid，Extra=Using index

-- 3. 查询列包含非索引列（触发回表查询）
EXPLAIN SELECT mail, age, password FROM index_demo WHERE mail = '1020000@qq.com';
-- 执行计划：type=ref，key=idx_mail_age_classid，Extra=NULL（无Using index）

2.2 回表查询：索引优化的痛点

核心概念

• 回表查询：使用二级索引（非主键索引）查询时，若查询列不在索引中，需通过索引中的主键值，到主键索引中查询完整数据。
• 弊端：增加磁盘IO（两次索引扫描），大数据量下性能下降明显。

优化方案

• 方案1：使用覆盖索引，将查询列加入索引（适合查询列固定的场景）。
• 方案2：查询时仅保留必要列，避免SELECT *。

2.3 索引合并优化

核心概念

• 索引合并：MySQL同时使用多个索引查询，将结果取交集/并集，适用于多条件OR/AND查询。
• 执行计划：type=index_merge，Extra=Using union(idx1, idx2)。

实战示例

-- 1. 单表多索引查询（OR连接）
EXPLAIN SELECT * FROM index_demo WHERE id = 100000 OR name = 'user_1020021';
-- 执行计划：type=index_merge，key=PRIMARY, idx_name，Extra=Using union(PRIMARY,idx_name)

-- 2. 多条件AND连接（交集）
EXPLAIN SELECT * FROM index_demo WHERE id > 100000 AND name = 'user_1020021';
-- 执行计划：type=ref，key=idx_name（优先使用普通索引过滤）

2.4 索引下推（ICP）

核心概念

• 索引下推：MySQL 5.6+ 特性，存储引擎层利用索引过滤条件，减少回表行数。
• 适用场景：范围查询（>、<、LIKE）+ 多条件过滤，且条件列在复合索引中。

示例对比

-- 创建复合索引：idx_age_classid(age, class_id)
CREATE INDEX idx_age_classid ON index_demo(age, class_id);

-- 开启索引下推（默认开启）
EXPLAIN SELECT * FROM index_demo WHERE age < 18 AND class_id > 1;
-- 执行计划：Extra=Using index condition（索引下推生效）

-- 关闭索引下推（测试用）
SET optimizer_switch = 'index_condition_pushdown=off';
EXPLAIN SELECT * FROM index_demo WHERE age < 18 AND class_id > 1;
-- 执行计划：Extra=Using where（无索引下推，回表后过滤）

三、查询语句优化：改写SQL提升效率

针对WHERE、ORDER BY、GROUP BY等常见场景，通过SQL改写和索引优化，避免全表扫描和低效操作。

3.1 WHERE子句优化

核心技巧

1. 避免无效条件：删除恒成立/不成立的条件（如WHERE 1=1、WHERE id > 256（id为TINYINT））。
2. 常量合并：MySQL会自动优化(a < 5 AND b = a) AND a = 3 为 b=3 AND a=3。
3. 优先使用常量表：主键/唯一索引等值查询的表（const/system表）优先执行。
4. 避免函数操作索引列：索引列参与函数运算会导致索引失效。

优化示例

-- 优化前：函数操作索引列（索引失效，全表扫描）
EXPLAIN SELECT * FROM index_demo WHERE id + 1 = 1000020;
-- type=ALL，key=NULL

-- 优化后：改写为索引列直接比较（索引生效）
EXPLAIN SELECT * FROM index_demo WHERE id = 1000019;
-- type=const，key=PRIMARY

3.2 ORDER BY优化

核心技巧

1. 利用索引排序：索引本身有序，避免文件排序（Using filesort）。
2. 复合索引排序：遵循最左原则，排序顺序与索引一致（ASC/DESC可混合）。
3. 避免非索引列排序：为排序列创建索引。

优化示例

-- 1. 创建复合索引：idx_name_age(name, age)
CREATE INDEX idx_name_age ON index_demo(name, age);

-- 优化前：非索引列排序（Using filesort）
EXPLAIN SELECT * FROM index_demo WHERE id < 1020000 ORDER BY age LIMIT 10;
-- Extra=Using where; Using filesort

-- 优化后：索引列排序（无filesort）
EXPLAIN SELECT * FROM index_demo WHERE name = 'user_1020021' ORDER BY age LIMIT 10;
-- type=ref，key=idx_name_age，Extra=NULL

3.3 GROUP BY优化

核心技巧

1. 索引分组：为GROUP BY列创建索引，避免临时表（Using temporary）。
2. 避免SELECT *：仅查询分组列和聚合列。
3. 结合WHERE过滤：先过滤再分组，减少分组数据量。

优化示例

-- 优化前：非索引分组（Using temporary）
EXPLAIN SELECT gender, AVG(age) FROM index_demo GROUP BY gender;
-- Extra=Using temporary

-- 优化后：为分组列创建索引（无temporary）
CREATE INDEX idx_gender ON index_demo(gender);
EXPLAIN SELECT gender, AVG(age) FROM index_demo GROUP BY gender;
-- key=idx_gender，Extra=NULL

3.4 范围查询优化

核心技巧

1. 索引范围查询：为范围列（>、<、BETWEEN、IN）创建索引。
2. 复合索引范围限制：复合索引中，范围条件后的列无法使用索引。
3. 避免过度范围：缩小查询范围，减少扫描行数。

示例

-- 创建复合索引：idx_age_classid(age, class_id)
CREATE INDEX idx_age_classid ON index_demo(age, class_id);

-- 范围条件后的列无法使用索引
EXPLAIN SELECT * FROM index_demo WHERE age < 18 AND class_id > 1;
-- key_len=2（仅使用age列索引，class_id未使用）

四、索引失效：10个高频避坑场景

索引创建后不一定生效，以下场景会导致索引失效，需重点规避：

4.1 违反最左原则（复合索引）

-- 复合索引：idx_mail_age_classid(mail, age, class_id)
EXPLAIN SELECT * FROM index_demo WHERE age = 20; -- 未使用mail列，索引失效
-- type=ALL，key=NULL

4.2 隐式类型转换

-- sn为VARCHAR类型，查询时未加引号（隐式转换为INT）
EXPLAIN SELECT * FROM index_demo WHERE sn = 1020000; -- 索引失效
-- type=ALL，key=NULL

-- 优化：加引号（匹配字段类型）
EXPLAIN SELECT * FROM index_demo WHERE sn = '1020000'; -- 索引生效

4.3 LIKE以%开头

-- LIKE以%开头，无法使用索引
EXPLAIN SELECT * FROM index_demo WHERE name LIKE '%user_1020000';
-- type=ALL，key=NULL

-- 优化：%仅在末尾
EXPLAIN SELECT * FROM index_demo WHERE name LIKE 'user_1020000%';
-- type=range，key=idx_name

4.4 索引列参与函数/表达式运算

-- 索引列id参与表达式运算
EXPLAIN SELECT * FROM index_demo WHERE id + 1 = 1000020; -- 索引失效

-- 优化：改写为索引列直接比较
EXPLAIN SELECT * FROM index_demo WHERE id = 1000019; -- 索引生效

4.5 使用<>、NOT IN、IS NOT NULL

-- 使用<>，索引失效（数据分布均匀时）
EXPLAIN SELECT * FROM index_demo WHERE name <> 'user_1020000';
-- type=ALL，key=NULL

-- 优化：用范围查询替代（若业务允许）
EXPLAIN SELECT * FROM index_demo WHERE name < 'user_1020000' OR name > 'user_1020000';

4.6 OR连接非索引列

-- OR连接非索引列age，导致全表扫描
EXPLAIN SELECT * FROM index_demo WHERE id = 100000 OR age = 20;
-- type=ALL，key=NULL

-- 优化：为age创建索引（触发索引合并）
CREATE INDEX idx_age ON index_demo(age);
EXPLAIN SELECT * FROM index_demo WHERE id = 100000 OR age = 20;
-- type=index_merge，key=PRIMARY, idx_age

4.7 MySQL判断全表扫描更快

• 场景：表数据量极少（如<100行），或过滤条件返回大部分数据（如WHERE age > 0）。
• 优化：无需处理，MySQL优化器自动选择最优方案。

4.8 使用SELECT *

• 场景：SELECT * 可能导致回表查询，且无法使用覆盖索引。
• 优化：仅查询必要列，尽量让查询列覆盖索引。

4.9 联合索引中范围条件后列

-- 复合索引：idx_age_classid(age, class_id)
-- age为范围条件，class_id无法使用索引
EXPLAIN SELECT * FROM index_demo WHERE age < 18 AND class_id > 1;
-- key_len=2（仅age列生效）

4.10 数据重复率过高的列建索引

• 场景：如gender列（仅0/1两个值），索引过滤效果差，不如全表扫描。
• 优化：不建索引，或结合其他列创建复合索引。

五、索引使用原则：高效索引设计指南

1. 每张表必须有主键：推荐BIGINT自增主键（避免UUID，无序导致索引分裂）。
2. 高频查询列优先建索引：WHERE、JOIN、ORDER BY、GROUP BY列优先索引。
3. 避免过多索引：索引会降低INSERT/UPDATE/DELETE效率（维护索引需开销）。
4. 复合索引遵循最左原则：创建复合索引时，将过滤性强、高频使用的列放在前面。
5. 覆盖索引优先：查询列尽量包含在索引中，避免回表。
6. 避免重复索引：如idx_a和idx_a_b，idx_a为冗余索引，可删除。
7. 定期清理无用索引：通过sys.schema_unused_indexes视图查询未使用的索引。
8. 避免在大事务中创建索引：防止锁表，影响业务。

六、总结与进阶方向

SQL调优的核心是“索引优化+查询改写”：通过合理设计索引减少扫描行数，通过改写SQL避免索引失效和低效操作。关键要点总结：

1. 执行计划看type（至少range）和Extra（无filesort/temporary）。
2. 索引优先覆盖索引，避免回表查询。
3. 规避10个索引失效场景，遵循最左原则。
4. 高频查询列建索引，低频列不建索引，定期清理无用索引。

进阶学习方向

1. 分库分表：水平分表（按主键范围）、垂直分表（按列拆分），解决大数据量索引失效问题。
2. 读写分离：主库写、从库读，分摊查询压力。
3. SQL监控工具：使用Slow Query Log（慢查询日志）、Performance Schema监控低效SQL。
4. 数据库参数优化：调整innodb_buffer_pool_size（缓存池）、sort_buffer_size（排序缓存）等。
5. 分区表：对大表按时间/范围分区，提升查询效率。