当涉及范围时，索引中的较高基数列排在第一位?

Question

CREATE TABLE `files` (
`did` int(10) unsigned NOT NULL DEFAULT '0',
`filename` varbinary(200) NOT NULL,
`ext` varbinary(5) DEFAULT NULL,
`fsize` double DEFAULT NULL,
`filetime` datetime DEFAULT NULL,
PRIMARY KEY (`did`,`filename`),
KEY `fe` (`filetime`,`ext`),          -- This?
KEY `ef` (`ext`,`filetime`)           -- or This?
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

表中有一百万行。 文件时间大多是不同的。ext值的数量有限。 因此，filetime具有较高的基数，而ext具有低得多的基数。

查询涉及ext和filetime：

WHERE ext = '...'
AND filetime BETWEEN ... AND ...

这两个指数中哪一个更好？ 为什么呢？

Answer 1

首先，让我们尝试FORCE INDEX选择ef或fe。 时间太短，无法清楚地了解哪个更快，但'EXPLAIN 显示了差异：

首先强制filetime范围。 (注意：WHERE中的顺序没有影响。

mysql> EXPLAIN SELECT COUNT(*), AVG(fsize)
FROM files FORCE INDEX(fe)
WHERE ext = 'gif' AND filetime >= '2015-01-01'
AND filetime <  '2015-01-01' + INTERVAL 1 MONTH;
+----+-------------+-------+-------+---------------+------+---------+------+-------+-----------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows  | Extra                 |
+----+-------------+-------+-------+---------------+------+---------+------+-------+-----------------------+
|  1 | SIMPLE      | files | range | fe            | fe   | 14      | NULL | 16684 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+-------+-----------------------+

首先强制低基数ext：

mysql> EXPLAIN SELECT COUNT(*), AVG(fsize)
FROM files FORCE INDEX(ef)
WHERE ext = 'gif' AND filetime >= '2015-01-01'
AND filetime <  '2015-01-01' + INTERVAL 1 MONTH;
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | files | range | ef            | ef   | 14      | NULL |  538 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+

显然，rows说ef更好。 但是，让我们检查优化器跟踪。 输出相当庞大;我只会展示有趣的部分。 无需FORCE;跟踪将显示这两个选项，然后选择更好的选项。

...
"potential_range_indices": [
...
{
"index": "fe",
"usable": true,
"key_parts": [
"filetime",
"ext",
"did",
"filename"
]
},
{
"index": "ef",
"usable": true,
"key_parts": [
"ext",
"filetime",
"did",
"filename"
]
}
],

。

"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "fe",
"ranges": [
"2015-01-01 00:00:00 <= filetime < 2015-02-01 00:00:00"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 16684,
"cost": 20022,               <-- Here's the critical number
"chosen": true
},
{
"index": "ef",
"ranges": [
"gif <= ext <= gif AND 2015-01-01 00:00:00 <= filetime < 2015-02-01 00:00:00"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 538,
"cost": 646.61,               <-- Here's the critical number
"chosen": true
}
],

。

"attached_conditions_computation": [
{
"access_type_changed": {
"table": "`files`",
"index": "ef",
"old_type": "ref",
"new_type": "range",
"cause": "uses_more_keyparts"   <-- Also interesting
}
}

使用fe(范围列优先(，可以使用范围，但它估计扫描 16684 行钓鱼ext='gif'.

使用ef(首先是低基数ext(，它可以使用索引的两列，并在 BTree 中更有效地向下钻取。 然后它找到了大约 538 行，所有这些行对查询都很有用——不需要进一步过滤。

结论：

• INDEX(filetime, ext)只使用了第一列。
• INDEX(ext, filetime)使用了这两列。
• 将=测试中涉及的列放在索引的第一位，而不考虑基数。
• 查询计划不会超出第一个"范围"列。
• "基数">与复合索引和此类查询无关。

("使用索引条件"意味着存储引擎(InnoDB(将使用索引中用于过滤的列之外的列。