mysql等值查询和范围查询索引如何设计_mysql实战分析_技术教程

等值查询优先单列索引；多等值条件用联合索引，字段顺序需匹配查询顺序且高选择性字段前置；范围查询中断最左前缀匹配；ORDER BY需与索引方向一致才能避免排序；函数操作、隐式转换、前置通配符LIKE、OR跨字段等会导致索引失效。

等值查询用单列索引还是联合索引？

等值查询（如 WHERE user_id = 123）最直接的优化方式是给该字段建单列索引。但若查询常带多个等值条件（如 WHERE status = 'active' AND region = 'shanghai'），联合索引更高效——MySQL 能利用最左前缀原则一次性定位数据，避免回表或多次索引扫描。

关键点：

联合索引字段顺序必须匹配查询中等值条件的出现顺序，(status, region) 支持 status = ? AND region = ?，但不支持仅 region = ?
等值字段应前置，后续字段才可能被用于排序或范围过滤
如果某字段选择性极低（如 is_deleted 只有 0/1），放在联合索引最左会显著降低索引效率，应后置或排除

范围查询（>、>=、BETWEEN、LIKE 'abc%'）怎么用索引？

范围查询会中断联合索引的最左前缀匹配。一旦某个字段用了范围条件，其右侧所有字段都无法再用于索引查找（但仍可用于过滤或排序，前提是左侧全为等值）。

例如索引 (a, b, c)：

WHERE a = 1 AND b > 10 AND c = 5：只用到 a 和 b，c 不参与查找（因 b 是范围），但可在引擎层做条件过滤
WHERE a > 1 AND b = 2：仅用到 a，b 完全失效
WHERE a = 1 AND b LIKE 'x%'：b 是范围（前缀匹配视为范围），c 不参与查找

实践中，把高选择性且大概率用于范围的字段尽量靠右；必要时拆分索引，比如高频 created_at > ? 场景，可单独建 (created_at) 索引，避免拖累其他等值查询。

ORDER BY 和 LIMIT 如何与索引协同？

如果 ORDER BY 字段能被索引完全覆盖（且顺序一致），MySQL 就无需额外排序；配合 LIMIT 还能提前终止扫描。但注意：ASC/DESC 必须与索引定义严格一致（MySQL 8.0+ 支持混合方向，但 5.7 及以前要求全部同向）。

示例：

CREATE INDEX idx_user_status_ctime ON users (status, created_at);

以下语句能走索引排序：

SELECT * FROM users WHERE status = 'active' ORDER BY created_at ASC LIMIT 10
SELECT id FROM users WHERE status = 'active' ORDER BY created_at DESC（MySQL 8.0+ OK；5.7 需建 (status, created_at DESC)）

而 ORDER BY created_at DESC, id ASC 即使有索引也大概率触发 filesort，因为多方向不一致 + 非连续索引字段。

哪些常见操作会让索引“静默失效”？

索引存在，但执行计划显示 type: ALL 或 key: NULL，往往不是没建索引，而是某些写法绕过了索引使用逻辑。

典型情况：

对索引字段做函数操作：WHERE YEAR(created_at) = 2025 → 改成 WHERE created_at >= '2025-01-01' AND created_at
隐式类型转换：user_id 是 INT，但写成 WHERE user_id = '123'，可能触发全表扫描（尤其当字段有大量重复值时）
LIKE 以通配符开头：WHERE name LIKE '%john' 无法使用索引；LIKE 'john%' 可以
OR 连接不同字段：WHERE a = 1 OR b = 2，即使 a 和 b 各有索引，也可能放弃索引走全表（可改用 UNION 拆解）