mysql分区表 问题,MySQL 分区表的一些问题

最近在使用 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 源码，这里就先说这么一个需要注意的现象，防止踩坑。。。