Skip to content

性能优化案例

核心原则

SQL 语句的写法会显著影响其性能，即使逻辑上看起来相同。一个常见的性能陷阱是：对索引字段进行函数操作（显式或隐式），导致优化器放弃使用索引的 B+ 树快速搜索功能，退化为全索引扫描或全表扫描。

案例分析与避坑指南

案例一：对索引字段使用显式函数

  • 问题场景： 查询某个月份的记录，如 SELECT COUNT(*) FROM tradelog WHERE MONTH(t_modified) = 7; (t_modified 有索引)。
  • 性能瓶颈：
    • MONTH() 函数作用于索引列 t_modified。
    • 函数运算破坏了索引 B+ 树的有序性。优化器无法利用索引快速定位，因为它不知道 MONTH() 计算后的值在 B+ 树的哪个范围。
    • 结果： 优化器放弃 B+ 树搜索，可能选择全索引扫描（Using index 但扫描整个索引，如 EXPLAIN 中 rows 接近全表行数）或全表扫描。
  • 解决方案：
    • 改写 SQL，将函数操作移到常量一边，使查询条件基于索引列本身的范围。
    • SELECT COUNT(*) FROM tradelog WHERE (t_modified >= '2016-07-01' AND t_modified < '2016-08-01') OR (t_modified >= '2017-07-01' AND t_modified < '2017-08-01') OR ... ;
  • 延伸思考： 即使是不改变有序性的函数（如 id + 1 = 10000），MySQL 优化器也不会主动优化为 id = 10000 - 1 来使用索引。必须手动改写 SQL。

案例二：隐式类型转换

  • 问题场景： 查询条件中的字面量类型与索引列类型不匹配，如 SELECT * FROM tradelog WHERE tradeid = 110717; (tradeid 是 VARCHAR 类型，但提供了数字 110717)。
  • 性能瓶颈：
    • MySQL 类型转换规则： 字符串与数字比较时，会将字符串转换为数字。
    • 上述 SQL 相当于 SELECT * FROM tradelog WHERE CAST(tradeid AS signed int) = 110717;
    • CAST() 函数作用于索引列 tradeid。
    • 结果： 触发了案例一的原则，导致全表扫描或全索引扫描。
  • 解决方案：
    • 确保查询条件中的字面量类型与列定义匹配。
    • SELECT * FROM tradelog WHERE tradeid = '110717'; (加上引号)。
  • 反例思考： 如果是 SELECT * FROM tradelog WHERE id = "83126"; (id 是 INT 类型，提供了字符串 "83126")，通常能用上索引。因为转换规则是将字符串 "83126" 转为数字，函数作用在输入参数上，而不是索引列 id 上。

案例三：隐式字符编码转换（常见于 JOIN 操作）

  • 问题场景： JOIN 操作的关联字段在两个表中的字符集不同（如 tradelog.tradeid 是 utf8mb4，trade_detail.tradeid 是 utf8），SQL 如 SELECT d.* FROM tradelog l JOIN trade_detail d ON d.tradeid = l.tradeid WHERE l.id = 2;
  • 性能瓶颈：
    • MySQL 字符集转换规则： 为了避免数据丢失，通常将范围较小的字符集转换为范围较大的字符集（utf8 -> utf8mb4）。
    • 当 tradelog (utf8mb4) 作为驱动表，trade_detail (utf8) 作为被驱动表时，JOIN 条件相当于在被驱动表上执行 SELECT * FROM trade_detail WHERE CONVERT(tradeid USING utf8mb4) = $l.tradeid_value;。
    • CONVERT() 函数作用于被驱动表的索引列 trade_detail.tradeid。
    • 结果： trade_detail 上的 tradeid 索引失效，导致对 trade_detail 的全表扫描。
  • 解决方案：
    1. 最佳方案：统一字符集。 修改表的列定义，使关联字段的字符集保持一致（推荐 utf8mb4）。 ALTER TABLE trade_detail MODIFY tradeid VARCHAR(32) CHARACTER SET utf8mb4 DEFAULT NULL;
    2. 临时方案：修改 SQL。 在 SQL 中显式转换驱动表的字段字符集，使函数作用在输入值上，而不是被驱动表的索引列上。 SELECT d.* FROM tradelog l JOIN trade_detail d ON d.tradeid = CONVERT(l.tradeid USING utf8) WHERE l.id = 2;
  • 验证： 反向 JOIN (trade_detail 作驱动表)，通常能用上 tradelog 的索引，因为转换发生在 $d.tradeid_value 上 (SELECT ... FROM tradelog WHERE tradeid = CONVERT($d.tradeid_value USING utf8mb4);)。

总结与最佳实践

  1. 核心牢记： 避免在 WHERE 或 ON 子句中对索引列本身使用任何函数（显式函数、隐式类型转换 CAST、隐式字符集转换 CONVERT）。
  2. 保持类型一致： 确保查询条件中的字面量、变量类型与列定义一致。
  3. 统一字符集： 在数据库设计阶段就应统一关联字段的字符集，推荐使用 utf8mb4。
  4. 勤用 EXPLAIN： 在开发、测试、上线前，使用 EXPLAIN 分析 SQL 执行计划，特别是关注 key 列是否使用了预期索引，rows 列扫描行数是否合理，Extra 列是否有 Using index（覆盖索引好）或 Using filesort/Using temporary（通常不好）等信息。
  5. SQL 审查： 将 SQL 性能检查纳入代码审查流程。