我的理解是,在设计复合索引时,应将用于范围测试的列放置在该索引的末尾,因为复合索引中列的使用在第一个范围列之后停止。
事实是EXPLAIN
,优化器还使用了范围列之后的一列。这就是为什么我很困惑,不知道如何正确设计我的复合索引。
下面是我的表格的简化示例。每个日期DateP
都有多个对象Object
。每个Object
都有 3 个底层子对象subObject
。subObject
是每个的唯一名称,它是通过添加数字而DateP
派生的。有一个整数标识符,通常为 0、1 或 2,并且在同一个.Object
subObject
subObjectId
Object
子对象 | 目的 | 日期P | 子对象ID | 柜台1 |
---|---|---|---|---|
啊啊1 | 啊啊 | 2019-06-01 | 0 | 10 |
啊啊2 | 啊啊 | 2019-06-01 | 1 | 13 |
啊啊3 | 啊啊 | 2019-06-01 | 2 | 11 |
BB1 | bbb | 2019-06-01 | 0 | 9 |
bb2 | bbb | 2019-06-01 | 1 | 6 |
BB3 | bbb | 2019-06-01 | 2 | 7 |
啊啊1 | 啊啊 | 2019-06-02 | 0 | 14 |
啊啊2 | 啊啊 | 2019-06-02 | 1 | 12 |
啊啊3 | 啊啊 | 2019-06-02 | 2 | 16 |
CREATE TABLE
代码如下。表引擎是MyISAM,服务器是MySQL 8。
CREATE TABLE `testTab` (
`DateP` date NOT NULL,
`Object` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`subObject` char(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`subObjectId` int NOT NULL,
`counter1` int DEFAULT NULL,
......
PRIMARY KEY (`subObject`,`DateP`,`subObjectId`) USING BTREE,
KEY `Object` (`Object`,`DateP`,`subObjectId`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
其中最独特的列是KEY
,Object
其次是DateP
。最不独特的是subObjectId
。据我所知,列的唯一性对于它们在复合索引中的顺序也很重要 - 最唯一的应该是最左边的。
下面您可以看到EXPLAIN
取决于WHERE
子句中的条件的不同版本。
- 综合索引中的所有三列均已显示。中间是 的范围测试
DateP
。尽管如此,subObjectId
在范围测试后优化器也会使用该列。key_len
是31。
EXPLAIN SELECT DateP,Object,SUM(counter1)
FROM testTab
WHERE Object='aaa' AND DateP>='2019-06-01' AND DateP<='2019-06-10' AND (subObjectId=0 OR subObjectId=1)
GROUP BY Object,DateP
+----+-------------+---------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | testTab | NULL | range | Object | Object | 31 | NULL | 28 | 19.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
- 第一列和第二列在子句中呈现
WHERE
,因此它以范围测试结束。key_len
是27。
EXPLAIN SELECT DateP,Object,SUM(counter1)
FROM testTab
WHERE Object='aaa' AND DateP>='2019-06-01' AND DateP<='2019-06-10'
GROUP BY Object,DateP
+----+-------------+---------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | testTab | NULL | range | Object | Object | 27 | NULL | 29 | 100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
- 中仅使用第一列
WHERE
。key_len
是24。
EXPLAIN SELECT DateP,Object,SUM(counter1)
FROM testTab
WHERE Object='aaa'
GROUP BY Object,DateP
+----+-------------+---------+------------+------+---------------+--------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+--------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | testTab | NULL | ref | Object | Object | 24 | const | 417 | 100.00 | Using index condition |
+----+-------------+---------+------------+------+---------------+--------+---------+-------+------+----------+-----------------------+
我的结论是,尽管中间有一列用于范围测试,但复合索引中的所有三列都可以使用。但是我可以期望优化器总是尊重第三列吗?
在我目前的设计中,最后KEY
设计了柱子。DateP
KEY `Object` (`Object`,`subObjectId`,`DateP`) USING BTREE
但这个顺序不适合GROUP BY
子句,即:
GROUP BY Object,DateP
在这种情况下,对于分组,我猜只Object
使用了列,因为我跳过了中间的列DateP
。查询执行以Using temporary
.
+----+-------------+---------+------------+-------+---------------+--------+---------+------+------+----------+----------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+--------+---------+------+------+----------+----------------------------------------+
| 1 | SIMPLE | testTab | NULL | range | Object | Object | 31 | NULL | 20 | 100.00 | Using index condition; Using temporary |
+----+-------------+---------+------------+-------+---------------+--------+---------+------+------+----------+----------------------------------------+