MySQL SQL查询性能优化的30个实战技巧

索引构建与使用原则

索引是提升检索效率的核心，但错误的索引策略反而会成为性能杀手。

第1步：精准建立索引

目的：减少磁盘IO，加快数据定位。 操作：对 WHERE 和 ORDER BY 子句中频繁涉及的字段建立索引。优先选择区分度高的列（如唯一ID），避免在低区分度列（如性别）上过度依赖索引。

第2步：维护复合索引顺序

目的：确保最左前缀匹配规则生效。 操作：当使用复合索引时，查询条件必须包含索引定义中的第一个字段，否则索引将失效。尽量让查询条件的顺序与索引创建时的字段顺序保持一致。

第3步：控制索引数量

目的：平衡读写性能。
操作：索引虽加速读操作，但会降低写（INSERT/UPDATE）效率。单个表的索引数量建议不超过6个，定期审查并移除长期未使用或重复的索引。

避免索引失效的写法陷阱

许多看似正常的SQL语句会导致引擎放弃索引，进行全表扫描（Full Table Scan）。

第4步：避免在索引列上使用函数或表达式

目的：保持索引列的原生值以供引擎快速比对。 错误示例：

-- 错误：导致函数运算，索引失效
SELECT id FROM t WHERE num/2 = 100;
SELECT id FROM t WHERE substring(name,1,3)='abc';

-- 正确：将运算移至等号右侧
SELECT id FROM t WHERE num = 100 * 2;
SELECT id FROM t WHERE name LIKE 'abc%';

SELECT id FROM t WHERE createdate >= '2005-11-30' AND createdate < '2005-12-01';

第5步：谨慎使用 NULL、OR 和不等于操作

目的：防止优化器无法估算数据分布从而放弃索引。 操作：

• NULL判断：WHERE num IS NULL 会导致全表扫描。建议在业务允许范围内设置默认值（如0），改用 = 查询。
• OR连接：WHERE num=10 OR num=20 可能失效。建议使用 UNION ALL 合并多个查询。
• 不等于：避免使用 != 或 <>，这会迫使引擎扫描全表。

第6步：替换通配符与前缀搜索

目的：利用索引的快速定位能力。 错误示例：LIKE '%abc%'（前导通配符导致无法使用B+树索引）。 改进：若需全文检索需求，应使用MySQL的 FULLTEXT 全文索引功能，而非修改为前缀匹配，除非业务逻辑确实只匹配前缀（如 'abc%'）。

第7步：数值范围查询优选 BETWEEN

目的：简化条件判断，利于优化器统计信息评估。 操作：对于连续数值区间，优先使用 BETWEEN 1 AND 3 替代 IN (1,2,3)。

查询结构与执行优化

第8步：使用 EXISTS 替代 IN

目的：优化子查询的执行效率。 操作：在关联子查询中，若子查询返回大量数据，使用 EXISTS 通常比 IN 更高效，因为它可以在找到第一条匹配记录后立即返回，无需遍历全部结果集。

-- 推荐写法
SELECT num FROM a WHERE EXISTS (SELECT 1 FROM b WHERE num=a.num);

第9步：明确指定字段列表

目的：减少网络传输开销与内存占用。 操作：严禁使用 SELECT *。始终列出所需的具体字段，避免返回冗余数据。

第10步：合理使用 UNION ALL

目的：拆分复杂条件，复用单一索引路径。 操作：将带有 OR 的查询拆分为多个独立查询并通过 UNION ALL 连接，使每个部分都能单独命中索引。

存储结构与数据类型选择

第11步：优化字符串存储长度

目的：节省存储空间，提升对比速度。 操作：优先使用 VARCHAR/NVARCHAR 代替定长的 CHAR/NCHAR。仅在固定长度场景（如身份证号）使用定长类型。

第12步：数值型字段优先

目的：降低CPU计算复杂度。 操作：纯数值的字段（如金额、编号）不要设计为字符型。数字比较只需一次操作，而字符串比较需逐个字符比对，且存储占用更大。

第13步：慎用聚簇索引更新

目的：避免高昂的物理排序开销。 说明：CLUSTERED 索引决定了数据的物理存储顺序。频繁更新聚簇索引列会导致整表数据重新排列，性能损耗极大。若需频繁更新，需评估是否应将其他高频查询字段设为聚簇索引。

临时表与游标的高级应用

第14步：临时表的合理管理

目的：释放锁资源，避免系统表阻塞。 操作：

• 插入优化：大数据量导入时使用 SELECT INTO；小数据量先 CREATE TABLE 再 INSERT 以避免日志膨胀。
• 清理机制：存储过程结束时，务必执行 TRUNCATE TABLE 后 DROP TABLE，以彻底释放锁并清空空间。
• 替代方案：优先尝试使用表变量（Table Variables），但需注意其索引限制（通常仅限主键）。

第15步：用集合操作替代游标

目的：发挥SQL引擎批量处理优势。 操作：尽量避免使用 CURSOR 逐行处理数据。对于超过1万行的数据集，强烈建议改写为基于集合的 SET 操作。仅在必须引用多表且逻辑极其复杂的极端情况下，才考虑使用 FAST_FORWARD 类型的只进游标，并需谨慎评估性能影响。

全局环境与事务控制

第16步：关闭 NOCOUNT 消息干扰

目的：减少客户端与服务端的通信开销。 操作：在所有存储过程和触发器的开头设置 SET NOCOUNT ON，结束前设置 OFF。这能阻止每次执行后向客户端发送 DONE_IN_PROC 消息，显著提升批量操作效率。

第17步：精简大事务与大数据输出

目的：提高系统并发吞吐量。 操作：避免长时间持有锁的大事务操作。若查询结果数据量过大，首先审视业务需求是否合理，必要时增加分页或过滤条件，减少单次IO负载。

以上就是MySQL SQL查询性能优化的详细内容，更多关于数据库调优的资料请关注本站其它相关文章！