主页 / 问题 / 1204563
Accepted
Алексей Ковальчук
Asked:2021-11-14 21:43:49 +0000 UTC2021-11-14 21:43:49 +0000 UTC

为什么索引不用于排序？

  • 772

我有一张桌子：

CREATE TABLE `player_statistics` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `player_id` INT(11) NOT NULL,
    `statistic_id` INT(11) NOT NULL,
    `value` INT(11) NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `statistic_player_value` (`statistic_id`, `player_id`, `value`)
)

如您所见，有一个statistic_player_value包含 3 列的索引：statistic_id、player_id、value。

我用这个查询测试索引：

explain SELECT *
FROM `player_statistics` 
WHERE `statistic_id`=1 AND `player_id` =15
ORDER BY `value` desc
LIMIT 10;

结果：

+------+-------------+-------------------+------+-----------------------------------------------+------------------------+---------+-------------+------+--------------------------+
| id   | select_type | table             | type | possible_keys                                 | key                    | key_len | ref         | rows | Extra                    |
+------+-------------+-------------------+------+-----------------------------------------------+------------------------+---------+-------------+------+--------------------------+
|    1 | SIMPLE      | player_statistics | ref  | player_id,statistic_id,statistic_player_value | statistic_player_value | 8       | const,const |    1 | Using where; Using index |
+------+-------------+-------------------+------+-----------------------------------------------+------------------------+---------+-------------+------+--------------------------+

如您所见，它key_len等于 8，这意味着索引没有完全使用。player_id也只statistic_id使用了一个索引。我希望它key_len应该是12。

例如，此查询返回key_len12：

explain SELECT *
FROM `player_statistics` 
WHERE `statistic_id`=1 AND player_id =15 AND `value` = 1

那么问题是什么，为什么 order by 不使用索引呢？

mysql

1 个回答

  1. Best Answer

    您的复合索引有效，因为您在WHERE带有字段的查询中使用它

    key_len清楚地表明它是全部使用还是仅部分使用

    由于您有INTEGER 字段在数据库中分配了 4 个字节的内存，并且您在 WHERE 中使用了两个字段，因此在过滤两个字段时您的查询将得到 8。

    或 4 个字节 - 每个字段一个。

    ！！因为过滤已经完成，我们看到没有使用 12 个字节，而是在您的情况下使用了 8 个字节。

    复合索引可以包含多于一列和最多 16 列，但它们的总长度限制为 900 字节。

    MySQL 查询优化器尝试为此查询提出最佳执行计划。

    在此处输入图像描述

    我提供详细信息： 史前史以下截图：

    USING WHERE - 并不意味着不使用索引。这意味着还要检查结果是否符合条件。在 key 列中可以看到拉取数据时索引的存在。

    在此处输入图像描述 在此处输入图像描述 在此处输入图像描述

    单个字段的情况： 在此处输入图像描述 在此处输入图像描述

    问题：为什么感觉 INDEX 不适用于 ORDER BY？ 回复dev.mysql.com

    在页面中搜索字符串ORDER BY Execution Plan Information Available 在此处输入图像描述

    我提前简短地回答：我们没有遇到 Extra 列中的文本 Using filesort - 一切正常！如果不使用索引，请参阅使用文件排序

    • 如果 possible_keys 和/或至少填写了 key，并且 Extra 中没有 Using filesort 并且没有 Using temporary，那么索引就起作用了。

    检查索引的运行情况：删除索引并用 EXPLAIN 进行同样的查询，如果行数变大一个数量级，则索引以前工作过。

    由于 sql 版本，或数据库类型，或使用的查询 + 优化器决定显示什么和不显示什么，部分信息可能无法使用 EXPLAIN 显示

    • 4

相关问题