mysql函数如何配合索引使用_mysql查询优化方案

使用YEAR()、DATE()等函数会导致索引失效，因函数破坏列值有序性；正确做法是用范围查询，如create_time >= '2023-01-01' AND create_time

WHERE 条件中用 YEAR()、DATE() 等函数会导致索引失效

MySQL 在执行查询时，如果在索引列上套用函数（比如 WHERE YEAR(create_time) = 2023），优化器无法直接定位索引 B+ 树中的范围，只能全表扫描。本质是：函数改变了原始值的有序性，索引失去“可比较性”。

• ✅ 正确写法：WHERE create_time >= '2023-01-01' AND create_time （假设 create_time 有索引）
• ❌ 错误写法：WHERE YEAR(create_time) = 2023 或 WHERE DATE(create_time) = '2023-06-01'
• ⚠️ 注意：即使 create_time 是 DATETIME 类型，DATE() 函数仍会触发隐式转换和索引跳过

LIKE 查询带前导通配符（%abc）无法走索引

索引是按字符串前缀有序存储的，LIKE '%abc' 必须从每个值末尾匹配，B+ 树无法跳过查找路径；而 LIKE 'abc%' 可以利用最左前缀快速定位起始位置。

• ✅ 支持索引：WHERE name LIKE 'john%'（前提是 name 列有索引）
• ❌ 不支持索引：WHERE name LIKE '%ohn' 或 WHERE name LIKE '%oh%'
• ? 替代方案：对模糊搜索需求高，考虑 FULLTEXT 索引或外部搜索引擎（如 Elasticsearch）

ORDER BY 和 GROUP BY 必须严格匹配索引顺序才能避免 filesort

MySQL 使用索引完成排序或分组的前提是：索引字段顺序与 ORDER BY 或 GROUP BY 子句完全一致，且不混用 ASC/DESC（8.0+ 支持混合，但需显式声明）。

知了zKnown

知了zKnown：致力于信息降噪 / 阅读提效的个人知识助手。

下载

• ✅ 走索引排序：INDEX (a, b, c) + ORDER BY a, b 或 ORDER BY a DESC, b DESC
• ❌ 触发 Using filesort：ORDER BY b, a 或 ORDER BY a ASC, b DESC（除非 MySQL 8.0+ 且索引已定义为 (a ASC, b DESC)）
• ⚠️ 特别注意：SELECT * + ORDER BY 索引列，若索引不是覆盖索引，仍需回表，性能取决于数据量和随机 IO 成本

函数索引（MySQL 8.0+）能解决部分表达式索引需求

MySQL 8.0 引入函数索引，允许对表达式建索引，比如对 UPPER(email) 或 DATE(created_at) 建索引，让原本无法走索引的查询变成可索引查询。

• ✅ 创建方式：CREATE INDEX idx_email_upper ON users ((UPPER(email)))
• ✅ 查询生效：WHERE UPPER(email) = 'JOHN@EXAMPLE.COM'
• ⚠️ 限制：函数索引只支持确定性函数；不能包含用户变量、存储函数、子查询；索引名必须用双括号包裹表达式
• ⚠️ 注意：低版本（email_upper 字段并维护其值

索引是否生效，最终得看 EXPLAIN 的 key 和 Extra 字段。很多人只看 type=ref 就以为没问题，却忽略了 Extra: Using where; Using filesort —— 这说明排序没走索引，实际仍是性能瓶颈。