最近在使用 MySQL 分区表的时候,研究了一下多列 Range 分区,也就是
PARTITION BY RANGE COLUMNS(`a`, `b`, `c`) (
PARTITION p1 VALUES LESS THAN (0, 0, MAXVALUE),
PARTITION p2 VALUES LESS THAN (10, 10, MAXVALUE),
PARTITION p3 VALUES LESS THAN (20, 20, MAXVALUE)
)
在多列的情况下,MySQL 的分区策略和单列略有不同,这也是比较坑的地方,查遍所有文档都没人提到。。。
先说说单列 Range 分区。比如,如果这么写:
PARTITION BY RANGE(`a`) (
PARTITION p1 VALUES LESS THAN (0),
PARTITION p2 VALUES LESS THAN (10),
PARTITION p3 VALUES LESS THAN (20)
)
那么,p1
中的数据是 a
值小于 0 的,注意,是小于,不包括 0 。然后,p2
中的数据是 a
值在 [0, 10)
之间的,注意右边是开区间,不包括 10 。同样的,p3
中的数据是 a
值在 [10, 20)
之间的,不包括 20 。
也就是说,如果有这么一条数据:
INSERT INTO test_table (`a`, `b`, `c`) VALUES (10,10,20);
由于 a=10
,所以会落入 p3
分区。
再来看多列分区,使用第一个多列分区语句,执行 INSERT,会发现,数据插入到了 p2
分区,而不是想象中的 p3
分区。
这里么的原因,就涉及到 MySQL 内部的比较了。当使用单列分区时,MySQL 的比较方法是:
if a < 0 then p1
if a < 10 then p2
if a < 20 then p3
当采用多列分区的时候,比较方法就相应的变成了:
if (a,b,c) < (0 , 0 , MAXVALUE) then p1
if (a,b,c) < (10, 10, MAXVALUE) then p2
if (a,b,c) < (20, 20, MAXVALUE) then p3
那咱们再来看看直接执行这个比较会怎么样:
mysql> SELECT 10 < 10;
+---------+
| 10 < 10 |
+---------+
| 0 |
+---------+
1 row in set (0.01 sec)
mysql> SELECT 9 < 10;
+--------+
| 9 < 10 |
+--------+
| 1 |
+--------+
1 row in set (0.01 sec)
mysql> SELECT (10,10) < (10,10);
+-------------------+
| (10,10) < (10,10) |
+-------------------+
| 0 |
+-------------------+
1 row in set (0.00 sec)
mysql> SELECT (10,9) < (10,10);
+------------------+
| (10,9) < (10,10) |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
惊喜来了!(10,10) < (10,10)
毫不意外的被判定为 false
,但是 (10,9) < (10,10)
确是 true
的!
再来一些尝试:
mysql> SELECT (11,9) < (10,10);
+------------------+
| (11,9) < (10,10) |
+------------------+
| 0 |
+------------------+
1 row in set (0.00 sec)
mysql> SELECT (9,11) < (10,10);
+------------------+
| (9,11) < (10,10) |
+------------------+
| 1 |
+------------------+
1 row in set (0.01 sec)
mysql> SELECT (9,10) < (10,10);
+------------------+
| (9,10) < (10,10) |
+------------------+
| 1 |
+------------------+
1 row in set (0.01 sec)
惊呆了,(9,11) < (10,10)
居然也是 true
!
来,实际测试一下:
CREATE TABLE `test_table` (
`a` INT(20) NOT NULL,
`b` INT(11) NOT NULL
) ENGINE=INNODB DEFAULT CHARSET=UTF8MB4
PARTITION BY RANGE COLUMNS(`a`, `b`) (
PARTITION p1 VALUES LESS THAN (0, 0),
PARTITION p2 VALUES LESS THAN (10, 10),
PARTITION p3 VALUES LESS THAN (20, 20)
);
INSERT INTO `test_table` VALUES (10,10);
INSERT INTO `test_table` VALUES (10,9);
INSERT INTO `test_table` VALUES (9,11);
执行之后发现,第一条记录毫不意外的在 p3
,但是第二条记录和第三条记录却都在 p2
!
那么这时候执行查询会发生什么呢?
mysql> SELECT * FROM `test_table`;
+----+----+
| a | b |
+----+----+
| 10 | 9 |
| 9 | 11 |
| 10 | 10 |
+----+----+
3 rows in set (0.00 sec)
mysql> EXPLAIN PARTITIONS SELECT * FROM `test_table` WHERE a=10;
+------+-------------+------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+------------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | test_table | p2,p3 | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
+------+-------------+------------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> EXPLAIN PARTITIONS SELECT * FROM `test_table` WHERE b=10;
+------+-------------+------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+------------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | test_table | p1,p2,p3 | ALL | NULL | NULL | NULL | NULL | 5 | Using where |
+------+-------------+------------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
可以看到,当我们使用多列中的第一列做查询时,MySQL 会识别出 p1
分区一定没有数据,所以优化中直接去掉了这个分区,但是搜索了 p2 p3
两个分区。
这也是疑惑点之一,按照 MySQL 的规则,似乎 p2
也一定不会有数据,为啥要搜 p2
?
再来看下面一个查 b
的语句,会发现根本没有用分区,直接全分区搜索。。。看来 MySQL 也知道可能有一些 b
值并不是存在相应的分区中,需要全表扫描。
具体原因可能需要深入分析 MySQL 源码,这里就先说这么一个需要注意的现象,防止踩坑。。。