More reasonable update on modify_count and row_count for global stats

[xuyifangreeneyes]

Enhancement

mysql> set @@tidb_partition_prune_mode = 'dynamic';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t (a int) PARTITION BY RANGE( a ) (
    ->     PARTITION p0 VALUES LESS THAN (10),
    ->     PARTITION p1 VALUES LESS THAN (20)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t values (1), (2), (3), (11), (12), (13);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

-- wait about 1 min to dump stats delta

mysql> show stats_meta;
+---------+------------+----------------+---------------------+--------------+-----------+
| Db_name | Table_name | Partition_name | Update_time         | Modify_count | Row_count |
+---------+------------+----------------+---------------------+--------------+-----------+
| test    | t          | global         | 2022-11-07 23:44:23 |            6 |         6 |
| test    | t          | p0             | 2022-11-07 23:44:23 |            3 |         3 |
| test    | t          | p1             | 2022-11-07 23:44:23 |            3 |         3 |
+---------+------------+----------------+---------------------+--------------+-----------+
3 rows in set (0.00 sec)

mysql> analyze table t;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

mysql> show stats_meta;
+---------+------------+----------------+---------------------+--------------+-----------+
| Db_name | Table_name | Partition_name | Update_time         | Modify_count | Row_count |
+---------+------------+----------------+---------------------+--------------+-----------+
| test    | t          | global         | 2022-11-07 23:44:59 |            0 |         6 |
| test    | t          | p0             | 2022-11-07 23:44:59 |            0 |         3 |
| test    | t          | p1             | 2022-11-07 23:44:59 |            0 |         3 |
+---------+------------+----------------+---------------------+--------------+-----------+
3 rows in set (0.00 sec)

mysql> insert into t values (4), (5), (14);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

-- wait about 1 min to dump stats delta

mysql> show stats_meta;
+---------+------------+----------------+---------------------+--------------+-----------+
| Db_name | Table_name | Partition_name | Update_time         | Modify_count | Row_count |
+---------+------------+----------------+---------------------+--------------+-----------+
| test    | t          | global         | 2022-11-07 23:45:23 |            3 |         9 |
| test    | t          | p0             | 2022-11-07 23:45:23 |            2 |         5 |
| test    | t          | p1             | 2022-11-07 23:45:23 |            1 |         4 |
+---------+------------+----------------+---------------------+--------------+-----------+
3 rows in set (0.00 sec)

mysql> analyze table t partition p1;
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> show stats_meta;
+---------+------------+----------------+---------------------+--------------+-----------+
| Db_name | Table_name | Partition_name | Update_time         | Modify_count | Row_count |
+---------+------------+----------------+---------------------+--------------+-----------+
| test    | t          | global         | 2022-11-07 23:46:13 |            0 |         7 |
| test    | t          | p0             | 2022-11-07 23:45:23 |            2 |         5 |
| test    | t          | p1             | 2022-11-07 23:46:13 |            0 |         4 |
+---------+------------+----------------+---------------------+--------------+-----------+
3 rows in set (0.00 sec)

After analyze table t partition p1, modify_count and row_count of global stats are 0 and 7 respectively. However, the more reasonable modify_count and row_count is 2 and 9.

When we merge partition stats to global stats after analyzing several partitions, we set modify_count to 0 and set row_count to the sum of each partition's analyze_row_count. However, the better way is to set modify_count to the sum of each partition's modify_count and set row_count to the sum of each partition's real_time_row_count.

PS: analyze_row_count is the count calculated by last analyze, dumping stats delta doesn't change it. real_time_row_count is the count based on analyze_row_count and updated by dumping stats delta.