mysql - 为什么mysql优化器不使用完整索引？

Question

我试图理解为什么 MySQL 不使用完整索引来回答查询。让我解释。我通过 MySQL 版本 5.1.73-1 (Debian) 使用 imdb 数据库。我在表 movie_info_idx 上创建并索引“itid_inf25_mid_ndx”，其中包含列(info_type_id、info(25)、movie_id)。 info_type_id 和 movie_id 列是整数(NOT NULL)，info 是 TEXT 类型，因此每个索引条目占用 4+27+4 = 35 个字节。句子的输出:

EXPLAIN
SELECT movie_id
FROM movie_info_idx
WHERE info_type_id = 101
AND info > "9";

显示这些值:

select_type = SIMPLE; table = movie_info_idx; type=range; possible_keys = itid_inf25_mid_ndx; key = itid_inf25_mid_ndx; key_len = 31; ref = NULL; rows = 841; Extra = "Using where"

key_len 列和额外列中没有“使用索引”表明只有总计 4+27 = 31 字节的列 (info_type_id,info(25)) 正在使用索引。我想知道为什么优化器不使用索引中的 movie_id 列来访问 SELECT 子句中的 movie_id？看来优化器将访问基表 movie_info_idx 以获取我想要列出的 movie_id 值。为什么？.

预先感谢您的回复。

Answer 1

一旦 MySQL 使用“范围扫描”查询(匹配多个值)，它通常将不再使用最后一列。

原因是多列索引是一棵树的树。为了扫描最后一列(movie_id)上的索引，它必须在索引树中搜索范围列(info)的每个匹配值。这通常效率很低，因此 MySQL 不会这样做。

为了改善这种情况，请将预期为范围扫描的列放在最后，因此将其排序为(info_type_id, movie_id, info)

更多信息: https://dev.mysql.com/doc/refman/5.6/en/multiple-column-indexes.html