mysql中的索引选择性与查询效率分析

索引选择性影响MySQL是否使用索引：选择性低（如gender）易触发全表扫描，因回表成本高于顺序扫描；计算公式为COUNT(DISTINCT col)/COUNT(*)，>0.2通常较优；WHERE与ORDER BY共用索引时需兼顾过滤与排序能力，否则出现using filesort。

索引选择性到底影响什么

索引选择性低（比如 gender 字段只有 ‘M’/’F’ 两种值），mysql 很可能直接放弃走索引，改用全表扫描。这不是 MySQL “偷懒”，而是因为回表成本太高：查 10 万行索引后，再随机读 10 万次磁盘取完整行，比顺序扫一遍聚簇索引还慢。

选择性计算公式是：count(DISTINCT column) / COUNT(*)。> 0.2 通常算不错；FORCE INDEX，但那往往是掩盖更深层设计问题。

• 日期字段如 created_at 选择性通常高，但加了范围查询（BETWEEN）后，实际有效选择性会随时间推移下降
• 复合索引的最左前缀列选择性必须足够，否则整个索引都容易被跳过
• EXPLaiN 中的 key 列为空，type 是 ALL，基本就是选择性失效的信号

为什么 LIKE '%abc' 让索引完全失效

前导通配符破坏了 B+ 树的有序遍历能力。B+ 树只能从左到右匹配前缀，'%abc' 意味着“结尾是 abc”，数据库无法定位起始页，只能扫全索引甚至全表。

对比来看：LIKE 'abc%' 可以用索引，LIKE 'ab%c' 也能用（匹配到 'ab' 后再过滤），但只要开头是 % 或 _，优化器就直接排除该索引。

• 想模糊搜结尾？考虑倒序存储 + LIKE 'cba%'，或上 FULLTEXT 索引（仅限 MyISAM / InnoDB 的全文索引支持）
• INSTR(column, 'abc') > 0、LOCATE('abc', column) > 0 同样无法走索引，本质一样
• 如果业务真需要高频后缀搜索，GENERATED COLUMN + STOred + INDEX 是更可控的选择

ORDER BY 和 WHERE 共用索引时的优先级陷阱

MySQL 不会为 WHERE 和 ORDER BY 分别选两个索引。它只挑一个索引，要么加速过滤，要么加速排序——除非这个索引能同时覆盖两者。

例如表有 (status, created_at) 复合索引，查询 WHERE status = 'active' ORDER BY created_at DESC 就能高效走索引；但如果写成 WHERE status IN ('active', 'pending') ORDER BY created_at，IN 导致范围扩大，ORDER BY 部分大概率失效，执行计划里会出现 Using filesort。

• WHERE 条件中含 !=、NOT IN、OR（未被重写为 union）时，复合索引的排序能力基本归零
• ORDER BY 字段类型要和索引定义一致：索引是 int，但 ORDER BY CAST(x AS char) 会丢索引
• 覆盖索引（select 字段全在索引中）能避免回表，但不解决 filesort 问题；得靠索引结构本身支撑排序逻辑

查看真实选择性与索引使用情况的实操命令

别只信 SHOW INDEX，它不告诉你数据分布。用下面这条查实际选择性：

SELECT    COUNT(DISTINCT status) / COUNT(*) AS selectivity,   COUNT(*) AS total_rows FROM orders;

再结合 EXPLAIN format=jsON 看优化器决策依据，重点关注 used_key_parts 和 rows 字段。如果 rows 远大于结果集数量，说明索引没起到预期过滤作用。

临时关闭索引统计（用于测试）可用：SET session optimizer_switch = 'use_index_extensions=off';，但生产慎用——这只是验证手段，不是调优方案。

真正难的不是建索引，是判断「这个查询是否值得为它付出写放大和锁开销」。很多看似慢的查询，加索引后反而拖垮写入吞吐，尤其是高频更新的小字段。