为何MySQL查询未使用name索引排序？强制使用也无效

表结构 #

参考WordPress数据库设计，表创建语句如下：

CREATE TABLE wp_terms (
    term_id    bigint(20) unsigned auto_increment,
    name       varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
    slug       varchar(200),
    term_group bigint(10) DEFAULT 0,

    PRIMARY KEY ( term_id )
) ENGINE=InnoDB;

索引信息 #

执行show index from wp_terms;得到以下结果：

MySQL> show index from wp_terms;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| 表名     | 是否非唯一 | 索引名   | 索引中的序号 | 列名        | 排序规则   | 基数        | 子部分长度 | 压缩状态 | 可为空 | 索引类型   | 注释     | 索引注释       | 是否可见 | 表达式     |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| wp_terms |          1 | name     |            1 | name        | A         |      716638 |      191 |   NULL |      | BTREE      |         |               | YES     | NULL       |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

执行的查询 #

执行查询语句select name from wp_terms order by name limit 1;，结果如下：

MySQL> select name from wp_terms order by name limit 1;
+--------------------+
| name               |
+--------------------+
|  ****************  |
+--------------------+
1 row in set (0.83 sec)

执行计划 #

执行explain查看查询计划：

MySQL> explain select name from wp_terms order by name limit 1;
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | 查询类型     | 表名     | 分区       | 访问类型 | 可能的索引     | 使用索引 | 索引长度 | 关联列 | 扫描行数 | 过滤比例   | 额外信息        |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | SIMPLE      | wp_terms | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 802726 |   100.00 | Using filesort |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+----------------+

问题原因分析 #

核心问题：name是前缀索引，无法支撑完整排序需求 #

从索引信息可以看到，name索引只取了字段前191个字符建立（Sub_part=191），属于前缀索引。MySQL的BTREE前缀索引仅能用于前缀匹配类查询，而order by name需要基于字段的完整字符序列排序——不同的name值可能前191个字符完全相同，但后续字符不同，前缀索引无法区分这些值的完整顺序，因此优化器不会将其列为possible_keys。

即使强制使用force index(name)，优化器也会判断出该索引无法提供完整排序依据，最终仍会回退到全表扫描+文件排序，因为前缀索引里没有足够信息确定整个name字段的排序顺序，无法直接通过索引获取排序后的第一条数据。

解决方向 #

若要让索引支持该排序查询，需将name索引改为全字段索引，执行以下语句重建索引：

ALTER TABLE wp_terms DROP INDEX name;
ALTER TABLE wp_terms ADD INDEX name(name);

重建后优化器可利用全字段索引的有序性，直接通过索引快速定位排序后的第一条数据，避免全表扫描和文件排序。

内容的提问来源于stack exchange，提问作者Alex C