mysql中查询性能优化中的函数使用技巧

避免在WHERE、ORDER BY、GROUP BY中对索引字段使用函数，否则会导致索引失效、全表扫描或filesort；应改用范围查询、生成列+索引或拆分JSON为普通列。

避免在 WHERE 条件中对字段使用函数

这是 MySQL 查询变慢最常见也最容易被忽视的原因。一旦在索引列上套用函数（比如 DATE(created_at)、UPPER(name)），MySQL 就无法使用该列上的索引，导致全表扫描。

常见错误写法：

SELECT * FROM orders WHERE DATE(created_at) = '2025-05-20';

正确做法是把函数移到右边，让左边保持纯列名：

SELECT * FROM orders WHERE created_at >= '2025-05-20 00:00:00' AND created_at < '2024-05-21 00:00:00';

• 用范围条件替代 DATE()、HOUR() 等时间函数
• 字符串比较优先用 LIKE 'prefix%' 而非 LOWER(col) = 'xxx'
• 如果必须大小写不敏感，建索引时直接用 COLLATE utf8mb4_0900_as_cs 或更合适的校对规则，而非运行时转换

谨慎使用 COUNT(*) 和 COUNT(字段) 的语义差异

COUNT(*) 和 COUNT(col) 不仅语义不同，执行路径也可能完全不同——尤其当 col 没有索引或允许 NULL 时。

• COUNT(*) 统计行数，InnoDB 会尽量走最小索引（如主键）快速估算，有时甚至用内部计数器（无 WHERE 时）
• COUNT(col) 必须逐行检查 col 是否非 NULL，若 col 无索引，就会触发全表扫描
• 想统计某状态的记录数，优先写成 COUNT(*) WHERE status = 'done'，而不是 COUNT(status)

特别注意：在带 GROUP BY 的聚合查询里，误用 COUNT(col) 还可能掩盖 NULL 分组问题。

用 JSON 函数前先确认是否真的需要 JSON 字段

MySQL 5.7+ 提供了 JSON_EXTRACT()、->、JSON_CONTAINS() 等函数，但它们几乎无法利用索引（除非建虚拟列 + 索引）。很多性能问题源于过早把结构化数据塞进 JSON 字段。

• 如果经常按某个键过滤（如 data->'$.user_id'），不如拆成普通列 + 索引
• 必须用 JSON 时，可建生成列：

  ALTER TABLE events ADD user_id INT AS (data->>'$.user_id') STORED;

  再对 user_id 加索引
• JSON_CONTAINS() 在大数据集上比 LIKE 更慢，且不能用索引；匹配数组元素建议改用关系表

ORDER BY 中的函数会导致 filesort 且无法走索引

只要 ORDER BY 子句里出现函数（如 ORDER BY UPPER(name)），MySQL 就无法复用索引排序，强制触发 filesort —— 内存不足时还会写磁盘临时文件，性能断崖式下跌。

• 排序字段应与索引定义完全一致：索引是 (status,

  

  created_at)，就别写 ORDER BY status, DATE(created_at)
• 如需按表达式排序（如按年月分组后排序），提前算好并存为生成列，再对该列建索引
• 用 EXPLAIN 看 Extra 列：出现 Using filesort 就要警惕

函数本身不是敌人，但把它放在索引路径的关键位置（WHERE 左侧、ORDER BY、GROUP BY 表达式中），等于主动绕开优化器最有效的武器。真正省事的写法，往往是最贴近索引结构的写法。