MySQL表重组分区后分区信息丢失问题咨询

问题复现 #

以下是完整测试步骤及异常现象：

DROP DATABASE IF EXISTS bug;
CREATE DATABASE bug;
USE bug; 

-- 创建RANGE分区表
CREATE TABLE test (id INT, purchased DATE)
PARTITION BY RANGE (YEAR(purchased)) (
    PARTITION p0 VALUES LESS THAN (2000),
    PARTITION p1 VALUES LESS THAN (2010),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

-- 插入测试数据
INSERT INTO test(id, purchased)
VALUES
    (1, '1990-01-01'),
    (2, '2001-01-01'),
    (3, '2011-01-01');

-- 确认数据正常
SELECT * FROM test;
/*
+------+------------+
| id   | purchased  |
+------+------------+
|    1 | 1990-01-01 |
|    2 | 2001-01-01 |
|    3 | 2011-01-01 |
+------+------------+
*/

-- 查询分区行数，此时显示正常
SELECT PARTITION_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS 
WHERE TABLE_NAME = 'test';
/*
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          1 |
| p1             |          1 |
| p2             |          1 |
+----------------+------------+
*/

-- 重组p0分区为n0和n1
ALTER TABLE test
REORGANIZE PARTITION p0 INTO (
    PARTITION n0 VALUES LESS THAN (1990),
    PARTITION n1 VALUES LESS THAN (2000)
);

-- 表数据仍完整
SELECT * FROM test;
/*
+------+------------+
| id   | purchased  |
+------+------------+
|    1 | 1990-01-01 |
|    2 | 2001-01-01 |
|    3 | 2011-01-01 |
+------+------------+
*/

-- 但分区行数全部显示为0
SELECT PARTITION_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS 
WHERE TABLE_NAME = 'test';
/*
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| n0             |          0 |
| n1             |          0 |
| p1             |          0 |
| p2             |          0 |
+----------------+------------+
*/

-- 直接查询分区能看到数据确实存在
SELECT * FROM test PARTITION (n1, p1, p2);
/*
+------+------------+
| id   | purchased  |
+------+------------+
|    1 | 1990-01-01 |
|    2 | 2001-01-01 |
|    3 | 2011-01-01 |
+------+------------+
*/

执行FLUSH TABLES或重启数据库均无法解决该问题。

原因说明 #

INFORMATION_SCHEMA.PARTITIONS中的TABLE_ROWS字段是MySQL基于统计信息生成的估算值，并非实时精确的行数统计。在执行REORGANIZE PARTITION这类DDL操作后，MySQL不会自动触发分区统计信息的更新，导致该字段显示异常。

解决方法 #

执行ANALYZE TABLE命令手动刷新表的统计信息，即可让TABLE_ROWS显示正确的行数：

ANALYZE TABLE test;

执行后再次查询分区信息，会返回正确结果：

+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| n0             |          0 |
| n1             |          1 |
| p1             |          1 |
| p2             |          1 |
+----------------+------------+

如果需要同时整理表碎片，可以使用OPTIMIZE TABLE（该操作会重建表，耗时更长）：

OPTIMIZE TABLE test;

内容的提问来源于stack exchange，提问作者Zvi