摘要：这篇文章带大家一起探讨一些常见原因，方便大家更好地理解 MySQL 查询优化器是如何选择索引的，以及在出现类似问题时，可逐项进行对照排查，
在数据库优化中，最让人头疼的事情之一莫过于精心设计的索引没有发挥作用。为什么会出现这种情况？
这篇文章带大家一起探讨一些常见原因，方便大家更好地理解 MySQL 查询优化器是如何选择索引的，以及在出现类似问题时，可逐项进行对照排查，
以一个简单的 people 表作为例子，表结构如下：
CREATE TABLE `people` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `first_name` varchar(50) NOT NULL, `last_name` varchar(50) NOT NULL, `state` char(2) NOT NULL, PRIMARY KEY (`id`), KEY `first_name` (`first_name`), KEY `state` (`state`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 #技术分享 COLLATE=utf8mb4_unicode_ci后续会以该表结构为基础，通过添加或删除索引来展示不同场景。
在分析索引未生效的原因之前，首先需要判断 MySQL 是否使用了索引。可以通过 EXPLAIN 命令来查看查询优化器的分析结果，了解哪些索引被考虑，以及最终选择使用了哪个索引。
例如，以下查询会试图通过 first_name 索引查找数据：
返回结果如下：
| id | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | | ---
| 1 | people | ref | first_name | first_name | 202 | const | 180 | 100.00 | |
从结果中可以看到：
possible_keys 表示查询优化器会考虑的索引，这里是 first_name 索引。key 表示实际被选中的索引，也是 first_name 。关于 EXPLAIN 的使用，可参考《 仅仅会用 MySQL 的 EXPLAIN 还不够，还需要会用 EXPLAIN ANALYZE 》这篇文章。
在本例中，first_name 索引不仅被优化器考虑（considered），而且最终被选中（chosen）。这是两个相关但不同的步骤：首先，优化器会根据查询筛选可用的索引；然后，选择性能较优的索引。
确认索引是否被使用后，接下来分析一些索引未生效的常见原因。
当查询可以利用多个索引时，MySQL 优化器会选择其中最优的索引。如果你的查询可以同时使用多个索引，但最终未选择预期的索引，很可能是因为另一个索引的效率更好。
例如，以下查询同时使用 first_name 和 state 字段：
SELECT *WHERE first_name = 'Aaron' AND state = 'TX'运行 EXPLAIN 后结果如下：
| id | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | | ---
| 1 | people | ref | first_name,state | first_name | 202 | const | 180 | 50.00 | Using where |
在这个例子中，first_name 索引比 state 索引的选择性更高，因此优化器选择了 first_name 索引。
索引的性能往往与选择性和基数相关：
基数（Cardinality） 是列中不同值的数量。选择性（Selectivity） 是指这些值的独特程度（计算公式为 COUNT(DISTINCT column) / COUNT(*) ）。比如，可以通过以下查询计算基数和选择性：
SELECT COUNT(DISTINCT first_name) as first_name_cardinality, COUNT(DISTINCT state) as state_cardinality, COUNT(DISTINCT first_name) / COUNT(*) as first_name_selectivity, COUNT(DISTINCT state) / COUNT(*) as state_selectivityFROM people;结果如下：
| first_name_cardinality | state_cardinality | first_name_selectivity | state_selectivity | | ---
| 3009 | 2 | 0.0060 | 0.0000 |
first_name 字段的不同值非常多，因此选择性较高。state 列选择性极低，导致通过 state 索引进行过滤时，效果较差。高选择性索引通常性能较优，而低选择性索引在过滤数据时作用有限。
此外，唯一索引（如 id 的主键索引）通常具有完美选择性。
索引的选择性是基于整个表数据分布进行计算的，但选择性在具体查询场景中可能不一样。例如：
假如表中有 100 万行，其中 99% 的用户类型是 user ，只有 1% 为 admin ，总体来看 type 列选择性很低。但如果你的查询条件是 type = 'admin' ，此时索引的作用就很明显。
优化器会根据查询条件和数据分布动态评估索引的价值。
MySQL 的索引基数统计信息是通过随机采样维护的，可能出现因统计信息过时而导致优化器做出错误决策的情况。可以通过以下命令更新统计信息：
ANALYZE TABLE people;如果统计数据采样精度不足，可以通过调整 MySQL 的相关参数改善采样质量。
某些情况下，优化器会选择直接扫描整个表而不是使用索引。这可能发生在以下场景：
表的大小较小，表扫描成本几乎可以忽略。查询需要获取大部分表数据，索引的过滤能力不足，导致索引的额外开销反而拖慢查询速度。虽然表扫描看起来反直觉，但在特定情况下确实更高效。
理解索引的底层结构（如 B+ 树），有助于分析某些查询为什么无法用到索引。主要有以下几个场景：
MySQL 的索引只能用于匹配字符串的前缀部分，不能用于字符串中的后缀或包含部分。例如：
查询 LIKE 'Aa%' 可以使用索引。查询 LIKE '%ron' 则无法使用索引。如果你需要复杂的字符串搜索，可以考虑使用全文索引（Fulltext Index）或专门设计的数据模型。
复合索引要求使用时遵循“左前缀”规则，例如：
ALTER TABLE people ADD INDEX multi (first_name, state);若连接的字段类型或字符集不一致，索引将无法生效。例如：
VARCHAR(10) 和 CHAR(10) 类型和长度相同，索引可用。VARCHAR(10) 和 CHAR(15) 则因长度不同导致索引不可用。确保字段定义一致是索引生效的前提。
某些查询因对字段使用了函数或运算导致索引无法使用。例如：
SELECT * FROM people WHERE YEAR(created_at) = 2023;上述查询无法使用 created_at 索引，因为 MySQL 没法直接基于函数计算进行优化。替代方案如下：
SELECT * FROM people WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31';通过范围查询可以正常使用索引。
MySQL 支持隐藏索引，隐藏索引不会被查询优化器使用。例如：
ALTER TABLE people ALTER INDEX first_name INVISIBLE;Hidden 索引可以用于测试索引删除的影响，若查询性能下降可以随时恢复索引。
强制使用索引EXPLAIN SELECT * FROM people USE INDEX (state) WHERE first_name = 'Aaron' AND state = 'TX'但使用 USE INDEX 应该谨慎，因为可能在数据量增长后需要重新评估是否强制使用某索引。
索引优化涉及多个方面，包括查询优化器运作、数据分布、索引结构等。了解索引未生效的原因并合理优化查询，可以显著提升数据库性能。索引虽强大，但只有正确规划和使用才能发挥最大效用。
来源：墨码行者
