mysql - 为什么我的查询要检查 1000 行，即使表已编入索引？

Question

该表有大约 20K 行和以下创建代码:

CREATE TABLE `inventory` (
      `ID` int(11) NOT NULL AUTO_INCREMENT,
      `TID` int(11) DEFAULT NULL,
      `RID` int(11) DEFAULT NULL,
      `CID` int(11) DEFAULT NULL,
      `value` text COLLATE utf8_unicode_ci,
      PRIMARY KEY (`ID`),
      KEY `index_TID_CID_value` (`TID`,`CID`,`value`(25))
    );

这是解释查询的结果

mysql> explain select rowID from inventory where TID=4 and CID=28 and value=3290843588097;
+----+-------------+------------+------+------------------------+-----------------------+---------+-------------+------+-------------+
| id | select_type | table      | type | possible_keys          | key                   | key_len | ref         | rows | Extra       |
+----+-------------+------------+------+------------------------+-----------------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | inventory  | ref  | index_TID_CID_value    | index_TID_CID_value   | 10      | const,const | 9181 | Using where |
+----+-------------+------------+------+------------------------+-----------------------+---------+-------------+------+-------------+
1 row in set (0.00 sec)

TID=4 和 CID=28 的组合在表中有大约 13K 行。

我的问题是:

1. 为什么解释结果告诉我大约 9k 行将是检查得到最终结果？

2. 为什么列 ref 只显示 const,const 因为 3 列包含在多列索引中不应该 ref 是 const,const,const ?

2016 年 10 月 7 日更新

查询:

select rowID from inventory where TID=4 and CID=28 and value=3290843588097;

我跑了大约 10 次，取了最后 5 次的时间(它们是一样的)

• 没有索引 - 0.02 秒
• 索引(TID、CID)- 0.03 秒
• 索引(TID、CID、值)- 0.00 秒

今天同样的解释查询看起来也不同了，如何??注意键 len 已更改为 88 并且 ref 已更改为 const,const, const 并且要检查的行已减少到 2。

mysql> explain select rowID from inventory where TID=4 and CID=28 and value='3290843588097';
+----+-------------+-----------+------+----------------------+---------------------+---------+-------------------+------+-------------+
| id | select_type | table     | type | possible_keys        | key                 | key_len | ref               | rows | Extra       |
+----+-------------+-----------+------+----------------------+---------------------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | inventory | ref  | index_TID_CID_value  | index_TID_CID_value | 88      | const,const,const |    2 | Using where |
+----+-------------+-----------+------+----------------------+---------------------+---------+-------------------+------+-------------+
1 row in set (0.04 sec)

Answer 1

明确回答您的问题。

1. 由于引擎需要搜索索引树以找到与您的 where 子句条件匹配的行 ID，因此解释计划为您提供了约 9k 行查询。索引将生成索引列值的每个可能组合到与该组合关联的 rowID 列表的映射。实际上，引擎正在搜索这些组合以找到正确的组合；这是通过扫描组合来完成的，因此数量约为 9k。

2. 由于您的 where 子句条件涉及所有三个索引列，引擎正在优化搜索，方法是利用前两列的索引，然后短路第三列并获取所有 rowID 结果那个组合。

在您的特定用例中，我假设您想要优化搜索性能。我建议您只在 TID 和 CID(不是值)上创建一个索引。这样做的原因是，您目前在大约 20k 条记录中只有这些值的 2 种组合。这意味着使用仅包含 2 列的索引，引擎在对所有三个值进行搜索时几乎可以立即删除一半的记录。 (这都是假设该索引将应用于具有更大数据集的表。)由于您的指标基于较小的数据集，因此您可能看不到使用索引与不使用索引之间的性能差异的数量级。