MySQL性能优化：19个关键查询调优与索引策略详解

使用 EXPLAIN 分析 SQL 执行计划

进行 MySQL 优化的第一步是善用 EXPLAIN 查看 SQL 的执行细节。通过 EXPLAIN 输出的结果，我们可以识别潜在的性能瓶颈，重点关注以下字段：

• type：连接类型。理想情况下至少达到 range 级别，必须杜绝出现 all（全表扫描）。
• key：实际使用的索引名称。如果为 NULL，说明未使用索引，可尝试强制索引或检查索引是否生效。
• key_len：索引使用的字节长度，越短通常意味着更高效的索引选择。
• rows：预估需要扫描的行数，该值仅供参考。
• extra：额外信息。需警惕 Using filesort（文件排序）和 Using temporary（使用临时表），这通常意味着性能较差。

SQL 写法层面的常规优化

第1步：控制 IN 子句的数量

MySQL 会将 IN 中的常量存储在有序数组中进行匹配，但如果数值过多，内存消耗和处理时间也会增加。对于连续的数值范围，建议使用 BETWEEN 而非 IN，或者通过连接（JOIN）来替代。

第2步：明确指定 SELECT 字段

尽量避免使用 SELECT *。明确指出需要的字段名称可以减少 CPU、IO、内存和网络带宽的消耗，同时有利于覆盖索引（Covering Index）的实现。此外，当表结构变更时，硬编码的字段名比通配符更安全，避免前端因返回数据变化而出错。

第3步：限制返回行数时使用 LIMIT 1

当只需要获取一条数据（如判断记录是否存在）时，加上 LIMIT 1 可以告诉 MySQL 找到目标后停止搜索，使 type 列达到最高的 const 级别，大幅提升效率。

第4步：谨慎使用 OR 和 UNION

关于 OR： 如果 OR 两边的字段中有一个没有索引，且其他条件也无索引，会导致整条语句不走索引。此时可用 UNION ALL 代替 OR，因为 UNION ALL 不需要去重和排序，性能优于 UNION。

关于 UNION vs UNION ALL： UNION 需要进行合并后的唯一性过滤（涉及排序），增加 CPU 运算和延迟。只要确认两个结果集无重复数据，务必优先使用 UNION ALL。

第5步：最小化不必要的排序

如果排序字段没有建立索引，尽量避免使用 ORDER BY，以减少 Using filesort 的发生概率。

第6步：避免 ORDER BY RAND()

SELECT ... ORDER BY RAND() LIMIT N 是常见的性能杀手，它会对所有行进行随机排序。若需获取随机数据，可通过计算偏移量或使用 JOIN 子查询的方式优化：

-- 优化前：低效
SELECT id FROM `dynamic` ORDER BY RAND() LIMIT 1000;

-- 优化后：利用随机ID范围检索
SELECT id FROM `dynamic` t1 
JOIN (SELECT RAND() * (SELECT MAX(id) FROM `dynamic`) AS nid) t2 
ON t1.id > t2.nid 
LIMIT 1000;

IN 与 EXISTS 的选择及 NOT EXISTS 替换

IN 和 EXISTS 的核心区别在于驱动表的改变：

• IN：先执行子查询。适合“外表小而内表大”的场景。
• EXISTS：以外层表为驱动表，逐行匹配子查询。适合“外表大而内表小”的场景。

-- IN 示例
SELECT * FROM A WHERE id IN (SELECT id FROM B);

替代 NOT IN： NOT IN 存在逻辑陷阱（如子查询包含 NULL 则结果为空）且效率低下。推荐使用 LEFT JOIN ... IS NULL 模式：

-- 高效替代 not in
SELECT colname ... FROM A LEFT JOIN B ON A.id = B.id WHERE B.id IS NULL;

分页与分段查询优化

第7步：优化深分页（Deep Paging）

当 LIMIT offset, size 中的 offset 非常大时（如 limit 866613, 20），MySQL 仍需扫描并丢弃前面的大量数据。优化方案是利用主键索引进行范围查询：

-- 假设上一页最后一条数据的id为 866612
SELECT id, name FROM product WHERE id > 866612 LIMIT 20;

第8步：大数据量分段查询

当扫描行数达到百万级时，单次查询可能超时。在应用中采用分段循环查询（例如每次查1000条，根据上次结束位置继续查）并合并结果，可降低单次内存压力。

避免索引失效的场景

第9步：避免 NULL 值判断

对字段进行 IS NULL 或 IS NOT NULL 判断往往会导致引擎放弃索引进行全表扫描。设计表时应尽量设置字段默认值而非允许 NULL。

第10步：避免前缀模糊查询与使用全文索引

LIKE '%word' 或 LIKE '%word%' 会导致索引失效。虽然 LIKE 'word%' 可以使用索引，但若需全词匹配，普通索引无法满足。

解决方案：使用全文索引（Fulltext Index）。

-- 创建全文索引
ALTER TABLE `dynamic_201606` ADD FULLTEXT INDEX `idx_user_name` (`user_name`);

-- 使用全文索引查询
SELECT id, fnum, fdst FROM dynamic_201606 
WHERE MATCH(user_name) AGAINST('zhangsan' IN BOOLEAN MODE);

第11步：避免在 WHERE 中对字段进行函数或表达式操作

例如 WHERE age * 2 = 36 会导致索引失效。应将运算移至等号右侧：WHERE age = 36 / 2。

第12步：防止隐式类型转换

当传入参数的类型与数据库字段类型不一致时（如字符串传给 INT 字段），MySQL 会进行隐式转换导致索引失效。开发时需确保参数类型与定义一致。

联合索引与 JOIN 的高级优化

第13步：遵守联合索引的最左前缀原则

对于联合索引 (A, B, C)，查询条件必须从 A 开始连续使用才能命中索引。例如只查 B 或 B+C 均无法使用该索引。因此，在建表时应将最常作为查询条件的字段放在最左侧。

第14步：注意范围查询对后续索引的影响

在联合索引中，如果遇到范围查询（如 >, BETWEEN, <），范围列之后的索引字段将失效。这是由 B+ 树的结构特性决定的。

第15步：合理使用 FORCE INDEX

当 MySQL 优化器选择的索引不理想时，可使用 FORCE INDEX 强制其走特定索引，但需谨慎评估，因为这可能阻碍查询计划随数据分布的变化而自动调整。

JOIN 连接的底层优化策略

理解 MySQL JOIN 的驱动表机制至关重要：

• LEFT JOIN：左边的表（A表）是驱动表。
• INNER JOIN：MySQL 自动选择数据量较小的表作为驱动表。
• RIGHT JOIN：右边的表（B表）是驱动表。

第16步：模拟 FULL JOIN

MySQL 不支持 FULL OUTER JOIN，可以通过 LEFT JOIN 加 UNION ALL 实现：

SELECT * FROM A LEFT JOIN B ON B.name = A.name WHERE B.name IS NULL
UNION ALL
SELECT * FROM B;

第17步：INNER JOIN 优于 LEFT JOIN

在能使用 INNER JOIN 的场景下，避免使用 LEFT JOIN，因为后者强制左边为驱动表，可能丧失优化器选择最优驱动表的机会。

第18步：被驱动表必须有索引

无论哪种 JOIN，被驱动表的 Join 字段必须有索引，否则会产生极慢的全表扫描嵌套。

第19步：利用小表驱动大表与 STRAIGHT_JOIN

为了减少嵌套循环次数，应尽可能让结果集小的表作为驱动表。如果发现优化器选错了驱动表（尤其是存在 GROUP BY 或 ORDER BY 导致文件排序时），可以使用 STRAIGHT_JOIN 强制连接顺序：

-- 强制左边表A为驱动表（仅适用于 INNER JOIN）
SELECT * FROM A STRAIGHT_JOIN B ON ...;

正确使用 STRAIGHT_JOIN 有时能将查询时间缩短数倍。

总结

以上涵盖了 MySQL 性能优化的 19 个关键环节。从基础的 EXPLAIN 分析到复杂的 JOIN 策略，核心逻辑均围绕“减少扫描行数”、“利用索引”、“降低 CPU 运算”**展开。在实际生产中，请结合具体的业务数据和版本特性进行测试验证。

以上就是 MySQL 优化常用的 19 种有效方法的详细内容，更多关于 MySQL 性能调优的资料请关注本站其它相关文章！