MySQL 统计信息不准确导致文件大小与系统表大小差异大
吾爱主题
阅读：163
2023-11-23 16:16:00
评论：0
引言
MySQL 的统计信息中包括多个统计项，由于基于采样计算，因此存在误差，最常见的是统计项【表的行数】不准确，可能导致执行计划选择错误。
本文通过分析系统表中表大小与物理文件大小差异较大的现象，定位到原因是统计项【索引数据页的数量】不准确导致，文中对统计信息相关的知识点进行了介绍。
现象
时间：20231026
现象：监控显示数据 2T，系统表显示不到 1T，有分区表，并且有删除操作
分析
系统表
执行以下 SQL 获取实例上每个库的大小。
select
table_schema as '数据库',
sum(table_rows) as '行数',
sum(
truncate(data_length / 1024 / 1024 / 1024, 2)
) as '数据容量(GB)',
sum(
truncate(index_length / 1024 / 1024 / 1024, 2)
) as '索引容量(GB)',
sum(
truncate(data_free / 1024 / 1024 / 1024, 2)
) as '碎片空间(GB)',
sum(
truncate(
(
data_length + index_length + data_free
)/ 1024 / 1024 / 1024,
2
)
) as '总容量(GB)'
from
information_schema.tables
group by
table_schema;
主库，执行结果如下所示。
+--------------------+------------+------------------+------------------+------------------+---------------+ | 数据库 | 行数 | 数据容量(GB) | 索引容量(GB) | 碎片空间(GB) | 总容量(GB) | +--------------------+------------+------------------+------------------+------------------+---------------+ | information_schema | NULL | 0.00 | 0.00 | 98.60 | 98.60 | | mysql | 254074 | 0.03 | 0.00 | 0.00 | 0.04 | | performance_schema | 13328253 | 0.00 | 0.00 | 0.00 | 0.00 | | sys | 6 | 0.00 | 0.00 | 0.00 | 0.00 | | tracking_46 | 1903822252 | 185.59 | 88.38 | 4.47 | 280.90 | | tracking_47 | 1958199360 | 190.14 | 90.70 | 4.56 | 287.67 | | tracking_detail_46 | 677699672 | 58.23 | 25.56 | 0.59 | 84.68 | | tracking_detail_47 | 667959727 | 57.23 | 24.87 | 0.60 | 83.00 | +--------------------+------------+------------------+------------------+------------------+---------------+ 8 rows in set (0.26 sec)
从库，执行结果如下所示。
+--------------------+------------+------------------+------------------+------------------+---------------+ | 数据库 | 行数 | 数据容量(GB) | 索引容量(GB) | 碎片空间(GB) | 总容量(GB) | +--------------------+------------+------------------+------------------+------------------+---------------+ | information_schema | NULL | 0.00 | 0.00 | 0.00 | 0.00 | | mysql | 146406 | 0.06 | 0.00 | 0.00 | 0.07 | | performance_schema | 1519229 | 0.00 | 0.00 | 0.00 | 0.00 | | sys | 6 | 0.00 | 0.00 | 0.00 | 0.00 | | tracking_46 | 3006864458 | 309.54 | 144.75 | 3.46 | 460.18 | | tracking_47 | 3006350150 | 310.27 | 144.79 | 3.53 | 460.63 | | tracking_detail_46 | 1511754256 | 112.39 | 56.80 | 0.62 | 170.12 | | tracking_detail_47 | 1515881664 | 112.67 | 56.89 | 0.57 | 170.43 | +--------------------+------------+------------------+------------------+------------------+---------------+ 8 rows in set (0.31 sec)
其中：
- 主从差异大，从库大小是主库的 2 倍左右。
主库，查看具体表的大小，显示单表 3G 左右，碎片 20M 左右。
图片
监控显示用户数据 2T。
图片
由于监控数据采集自物理文件的大小，因此表现为物理文件大小和系统表大小不一致。
物理文件
库级别
# du --max-depth=1 -h . 259G ./tracking_detail_46 100M ./mysql 680K ./sys 16K ./tracking_details_92 259G ./tracking_detail_47 775G ./tracking_46 774G ./tracking_47 16K ./tracking_details_94 16K ./tracking_details_93 16K ./tracking_details_95 4.0K ./tracking 1.1M ./performance_schema 2.2T .
表级别对比结果，输出结果按照差异倒排，其中单位是 byte。
{
"tracking_detail_46.tracking_info_1497":{
"table_size_file":9127723008,
"table_size_sys":2268192768,
"table_size_gap":6859530240
},
"tracking_detail_47.tracking_info_1519":{
"table_size_file":9127723008,
"table_size_sys":2286411776,
"table_size_gap":6841311232
},
"tracking_detail_46.tracking_info_1490":{
"table_size_file":9127723008,
"table_size_sys":2368405504,
"table_size_gap":6759317504
},
...
}
差异最大的表 tracking_detail_46.tracking_info_1497。
查看表结构
mysql> show create table tracking_detail_46.tracking_info_1497 \G
*************************** 1. row ***************************
Table: tracking_info_1497
Create Table: CREATE TABLE `tracking_info_1497` (
`id` bigint(20) NOT NULL COMMENT '主键',
`tenant_id` varchar(32) DEFAULT NULL COMMENT '租户',
`source_ele_id` bigint(20) NOT NULL COMMENT '原始要素表主键',
`template_id` int(6) NOT NULL COMMENT '模板编号',
`business_id` varchar(100) NOT NULL COMMENT '业务操作单号',
`related_id` varchar(100) DEFAULT NULL COMMENT '关联业务单号，例如：包裹号',
`remark` varchar(1024) DEFAULT NULL COMMENT '物流跟踪话术',
`is_delete` tinyint(2) NOT NULL DEFAULT '0' COMMENT '删除标识，0有效，1删除',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
`partition_time` datetime NOT NULL COMMENT '分区时间',
`ts` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '时间戳',
PRIMARY KEY (`id`,`partition_time`),
KEY `idx_business_id` (`business_id`,`template_id`,`source_ele_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 COMMENT='物流跟踪明细'
/*!50500 PARTITION BY RANGE COLUMNS(partition_time)
(PARTITION p202305 VALUES LESS THAN ('2023-06-01 00:00:00') ENGINE = InnoDB,
PARTITION p202306 VALUES LESS THAN ('2023-07-01 00:00:00') ENGINE = InnoDB,
PARTITION p202307 VALUES LESS THAN ('2023-08-01 00:00:00') ENGINE = InnoDB,
PARTITION p202308 VALUES LESS THAN ('2023-09-01 00:00:00') ENGINE = InnoDB,
PARTITION p202309 VALUES LESS THAN ('2023-10-01 00:00:00') ENGINE = InnoDB,
PARTITION p202310 VALUES LESS THAN ('2023-11-01 00:00:00') ENGINE = InnoDB,
PARTITION p202311 VALUES LESS THAN ('2023-12-01 00:00:00') ENGINE = InnoDB,
PARTITION p202312 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = InnoDB,
PARTITION p202401 VALUES LESS THAN ('2024-02-01 00:00:00') ENGINE = InnoDB,
PARTITION p202402 VALUES LESS THAN ('2024-03-01 00:00:00') ENGINE = InnoDB,
PARTITION p202403 VALUES LESS THAN ('2024-04-01 00:00:00') ENGINE = InnoDB,
PARTITION p202404 VALUES LESS THAN ('2024-05-01 00:00:00') ENGINE = InnoDB,
PARTITION p202405 VALUES LESS THAN ('2024-06-01 00:00:00') ENGINE = InnoDB,
PARTITION p202406 VALUES LESS THAN ('2024-07-01 00:00:00') ENGINE = InnoDB,
PARTITION p202407 VALUES LESS THAN ('2024-08-01 00:00:00') ENGINE = InnoDB,
PARTITION p202408 VALUES LESS THAN ('2024-09-01 00:00:00') ENGINE = InnoDB,
PARTITION p202409 VALUES LESS THAN ('2024-10-01 00:00:00') ENGINE = InnoDB,
PARTITION p202410 VALUES LESS THAN ('2024-11-01 00:00:00') ENGINE = InnoDB,
PARTITION p202411 VALUES LESS THAN ('2024-12-01 00:00:00') ENGINE = InnoDB,
PARTITION p202412 VALUES LESS THAN ('2025-01-01 00:00:00') ENGINE = InnoDB) */
1 row in set (0.00 sec)
其中：
- 分区表
- 压缩表
经确认，该实例上的表都是分区表，相当于分表+分区。
查看该表对应的文件大小，由于是分区表，因此对应多个文件。
-rw-r----- 1 mysql mysql 1.6G Oct 25 17:45 ./tracking_detail_46/tracking_info_1497#P#p202305.ibd -rw-r----- 1 mysql mysql 1.7G Oct 26 15:55 ./tracking_detail_46/tracking_info_1497#P#p202306.ibd -rw-r----- 1 mysql mysql 1.2G Oct 26 11:37 ./tracking_detail_46/tracking_info_1497#P#p202307.ibd -rw-r----- 1 mysql mysql 1.3G Oct 26 10:48 ./tracking_detail_46/tracking_info_1497#P#p202308.ibd -rw-r----- 1 mysql mysql 1.5G Oct 26 17:25 ./tracking_detail_46/tracking_info_1497#P#p202309.ibd -rw-r----- 1 mysql mysql 1.2G Oct 26 18:03 ./tracking_detail_46/tracking_info_1497#P#p202310.ibd -rw-r----- 1 mysql mysql 64K Jun 20 17:18 ./tracking_detail_46/tracking_info_1497#P#p202311.ibd -rw-r----- 1 mysql mysql 64K Jun 20 17:18 ./tracking_detail_46/tracking_info_1497#P#p202312.ibd -rw-r----- 1 mysql mysql 64K Jun 20 17:18 ./tracking_detail_46/tracking_info_1497#P#p202401.ibd -rw-r----- 1 mysql mysql 64K Jun 20 17:18 ./tracking_detail_46/tracking_info_1497#P#p202402.ibd -rw-r----- 1 mysql mysql 64K Jun 20 17:18 ./tracking_detail_46/tracking_info_1497#P#p202403.ibd -rw-r----- 1 mysql mysql 64K Jun 20 17:18 ./tracking_detail_46/tracking_info_1497#P#p202404.ibd -rw-r----- 1 mysql mysql 64K Jun 20 17:18 ./tracking_detail_46/tracking_info_1497#P#p202405.ibd -rw-r----- 1 mysql mysql 64K Jun 20 17:18 ./tracking_detail_46/tracking_info_1497#P#p202406.ibd -rw-r----- 1 mysql mysql 64K Jun 20 17:18 ./tracking_detail_46/tracking_info_1497#P#p202407.ibd -rw-r----- 1 mysql mysql 64K Jun 20 17:18 ./tracking_detail_46/tracking_info_1497#P#p202408.ibd -rw-r----- 1 mysql mysql 64K Jun 20 17:18 ./tracking_detail_46/tracking_info_1497#P#p202409.ibd -rw-r----- 1 mysql mysql 64K Jun 20 17:18 ./tracking_detail_46/tracking_info_1497#P#p202410.ibd -rw-r----- 1 mysql mysql 64K Jun 20 17:19 ./tracking_detail_46/tracking_info_1497#P#p202411.ibd -rw-r----- 1 mysql mysql 64K Jun 20 17:21 ./tracking_detail_46/tracking_info_1497#P#p202412.ibd
差异原因分析
下面是 chatgpt 回答的差异原因分析。
MySQL 物理文件大小和系统表大小不一致的原因可能有以下几点：
1、碎片化：当我们频繁地更新或删除表中的内容，会产生碎片，导致物理文件大小大于表的实际数据大小。
2、事务未提交：在 InnoDB 中，如果存在未提交的事务，那么这部分数据会被锁定，导致物理文件的大小不变，而系统表的大小则可能已经改变。
3、日志文件：InnoDB 的物理文件大小包括数据文件和日志文件两部分，而系统表的大小只计算数据文件。
4、多版本并发控制（MVCC）：在 InnoDB 中因为 MVCC 的存在，使得实际数据行存在多个版本，使得物理文件大小大于系统表大小。
5、索引：索引也会占用一部分的空间，如果表含有索引，那么物理文件的大小就会大于表的数据大小。
6、预留空间：MySQL 在创建表或者扩容表的时候，会预留一部分空间，这也可能导致物理文件大小和表的大小不一致。
总结就是数据页未充分利用、删除数据后产生的碎片、索引等占用的空间等原因，都能导致物理文件大小和系统表大小不一致。
由于之前有删除操作，因此怀疑与数据删除有关系，分别分析删除行与删除表是否会导致该现象。
删除行有两种场景：
- 已删除已提交的行数据不会释放空间，原因是逻辑删除用于复用，具体包括行记录复用与数据页复用。这些可以复用，但是没有使用的空间称为碎片，表现为 data_length 减小，data_free 增加，碎片空间可以通过重建表回收。而文中查询系统表时包括了碎片；
- 已删除未提交的行数据不会释放空间，原因是数据保存在 undo log 中用于回滚和 MVCC。如果有大量更新操作，将导致 undo log 无法清理，表现为 undo log 文件过大，如果有非常多的长事务，还会表现为 history list length 过大。而该实例中未发现大量未提交事务。
删除表有一种场景：
- 文件未删除，原因是系统占用。
因此查看 mysqld 进程打开的 deleted 文件。
[root@MSS-pz564g9cew ~]# ps -ef UID PID PPID C STIME TTY TIME CMD root 1 0 0 Jun20 ? 00:00:03 /usr/sbin/init root 705 1 0 Jun20 ? 00:00:13 /usr/sbin/sshd -D root 736 1 0 Jun20 ? 00:00:00 /usr/sbin/rsyslogd -n root 739 1 0 Jun20 ? 00:00:12 /usr/sbin/crond -n root 743 1 0 Jun20 ? 00:39:41 /usr/bin/docker-api --config-file=/etc/docker-api.toml root 797 1 0 Jun20 ? 00:00:00 /export/data/zabbix/sbin/zabbix_agentd root 799 797 0 Jun20 ? 01:32:42 /export/data/zabbix/sbin/zabbix_agentd: collector [idle 1 sec] root 800 797 0 Jun20 ? 00:00:21 /export/data/zabbix/sbin/zabbix_agentd: listener #1 [waiting for connection] root 801 797 0 Jun20 ? 00:00:21 /export/data/zabbix/sbin/zabbix_agentd: listener #2 [waiting for connection] root 802 797 0 Jun20 ? 00:00:21 /export/data/zabbix/sbin/zabbix_agentd: listener #3 [waiting for connection] root 803 797 0 Jun20 ? 00:00:21 /export/data/zabbix/sbin/zabbix_agentd: listener #4 [waiting for connection] root 804 797 0 Jun20 ? 00:00:21 /export/data/zabbix/sbin/zabbix_agentd: listener #5 [waiting for connection] root 805 797 0 Jun20 ? 00:10:06 /export/data/zabbix/sbin/zabbix_agentd: active checks #1 [idle 1 sec] mysql 3371 1 0 Jun20 ? 00:00:00 /bin/sh /export/servers/mysql/bin/mysqld_safe --defaults-file=/export/servers/mysql/etc/my.cnf mysql 4628 3371 99 Jun20 ? 165-22:28:24 /export/servers/mysql/bin/mysqld --defaults-file=/export/servers/mysql/etc/my.cnf --basedir=/export/servers/mysql --datadir=/export/data/mysql/data --plugin-dir=/export/servers/mysql/l root 45907 705 1 16:04 ? 00:00:00 sshd: root@pts/0 root 45909 45907 0 16:04 pts/0 00:00:00 -bash root 45924 45909 0 16:04 pts/0 00:00:00 ps -ef root 129505 1 11 Oct20 ? 2-20:31:08 /usr/bin/python /usr/lib/python2.7/site-packages/trove/cmd/trove-guestagent --config-dir=/export/etc/trove/conf.d/ root 129567 1 2 Oct20 ? 16:15:01 /usr/bin/python /usr/lib/python2.7/site-packages/trove/cmd/opentsdb-agent --config-file=/etc/opentsdb_agent/opentsdb_agent.conf td-agent 129697 1 0 Oct20 ? 00:05:07 /opt/td-agent/embedded/bin/ruby /opt/td-agent/embedded/bin/fluentd --log /export/data/td-agent/td-agent.log --daemon /var/run/td-agent/td-agent.pid --log-rotate-age 2 --log-rotate-size 104 td-agent 129702 129697 0 Oct20 ? 02:28:18 /opt/td-agent/embedded/bin/ruby -Eascii-8bit:ascii-8bit /opt/td-agent/embedded/bin/fluentd --log /export/data/td-agent/td-agent.log --daemon /var/run/td-agent/td-agent.pid --log-rotate-age [root@MSS-pz564g9cew ~]# [root@MSS-pz564g9cew ~]# [root@MSS-pz564g9cew ~]# lsof -p 4628 | grep deleted mysqld 4628 mysql 5u REG 253,3 225269 2147483908 /export/data/mysql/tmp/ibKCmSCB (deleted) mysqld 4628 mysql 6u REG 253,3 0 2147483909 /export/data/mysql/tmp/ibb414ul (deleted) mysqld 4628 mysql 7u REG 253,3 0 2147483910 /export/data/mysql/tmp/ibS2Rhn5 (deleted) mysqld 4628 mysql 8u REG 253,3 4207 2147483911 /export/data/mysql/tmp/ibSHfCOD (deleted) mysqld 4628 mysql 13u REG 253,3 0 2147483912 /export/data/mysql/tmp/ibN8igSs (deleted)
其中：
- 未删除的文件都是 mysqld 进程占用的临时文件，重启后可以释放，但是文件都很小，最大 225269 bytes，因此和已删除未释放的文件无关。
统计信息
由于该表是分区表，因此进一步查看系统表判断具体哪些分区的差异大。
mysql> SELECT PARTITION_NAME,DATA_LENGTH,INDEX_LENGTH,DATA_FREE,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'tracking_info_1497'; +----------------+-------------+--------------+-----------+------------+ | PARTITION_NAME | DATA_LENGTH | INDEX_LENGTH | DATA_FREE | TABLE_ROWS | +----------------+-------------+--------------+-----------+------------+ | p202305 | 553631744 | 193921024 | 0 | 5219137 | | p202306 | 908558336 | 387973120 | 0 | 8723301 | | p202307 | 26001408 | 16064512 | 0 | 402100 | | p202308 | 26001408 | 15007744 | 0 | 376422 | | p202309 | 34398208 | 20283392 | 0 | 517278 | | p202310 | 28098560 | 16572416 | 0 | 403775 | | p202311 | 9199616 | 6053888 | 0 | 143255 | | p202312 | 8192 | 8192 | 0 | 0 | | p202401 | 8192 | 8192 | 0 | 0 | | p202402 | 8192 | 8192 | 0 | 0 | | p202403 | 8192 | 8192 | 0 | 0 | | p202404 | 8192 | 8192 | 0 | 0 | | p202405 | 8192 | 8192 | 0 | 0 | | p202406 | 8192 | 8192 | 0 | 0 | | p202407 | 8192 | 8192 | 0 | 0 | | p202408 | 8192 | 8192 | 0 | 0 | | p202409 | 8192 | 8192 | 0 | 0 | | p202410 | 8192 | 8192 | 0 | 0 | | p202411 | 8192 | 8192 | 0 | 0 | | p202412 | 8192 | 8192 | 0 | 0 | +----------------+-------------+--------------+-----------+------------+ 20 rows in set (0.00 sec)
其中：
- p202305、p202306 的 DATA_LENGTH 是 p202307、p202308 的 10-20 倍左右；
- p202305、p202306 的 TABLE_ROWS 是 p202307、p202308 的 10-20 倍左右。
查看分区准确行数
mysql> select date_format(partition_time,'%Y-%m') mont ,count(*) from tracking_detail_46.tracking_info_1497 group by date_format(partition_time,'%Y-%m'); +---------+----------+ | mont | count(*) | +---------+----------+ | 2023-05 | 10571445 | | 2023-06 | 13659671 | | 2023-07 | 10874195 | | 2023-08 | 12275399 | | 2023-09 | 13722214 | | 2023-10 | 13669851 | | 2023-11 | 10710033 | +---------+----------+ 7 rows in set (2 min 2.82 sec)
其中：
- p202305 与 p202306 的行数与 p202307、p202308 接近。
表明行数的统计信息误差较大，因此怀疑表大小与行数类似，也是统计信息不准确导致差异大。
首先需要确认 DATA_LENGTH 的计算逻辑。
根据官方文档，DATA_LENGTH 表示聚簇索引的大小，具体等于数据页的数量X页大小。
For InnoDB, DATA_LENGTH is the approximate amount of space allocated for the clustered index, in bytes. Specifically, it is the clustered index size, in pages, multiplied by the InnoDB page size.
因此怀疑数据页的数量不准确。
mysql.innodb_index_stats数据表中 stat_name 列与 stat_value 分别表示各种类型统计信息与对应的值：
- 如果 stat_name = size，则 stat_value 列显示索引中的总大小（单位 page）；
- 如果 stat_name = n_leaf_pages，则 stat_value 列显示索引中的叶子页数；
- 如果 stat_name = n_diff_pfx01，则 stat_value 列显示索引第一列中的不同值的数量。当 stat_name = n_diff_pfx02，stat_value 列显示索引前两列中的不同值的数量，依此类推。此外，在stat_name = n_diff_pfxNN 的情况下，stat_description 列显示了计算的索引列。
查看mysql.innodb_index_stats表，其中仅查询主键索引的索引数据页的数量。
mysql> select * from mysql.innodb_index_stats where database_name='tracking_detail_46' and table_name like 'tracking_info_1497%' and index_name='PRIMARY' and stat_name = 'size'; +--------------------+------------------------------+------------+---------------------+-----------+------------+-------------+------------------------------+ | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description | +--------------------+------------------------------+------------+---------------------+-----------+------------+-------------+------------------------------+ | tracking_detail_46 | tracking_info_1497#P#p202305 | PRIMARY | 2023-05-16 11:37:14 | size | 67582 | NULL | Number of pages in the index | | tracking_detail_46 | tracking_info_1497#P#p202306 | PRIMARY | 2023-06-19 19:54:23 | size | 110908 | NULL | Number of pages in the index | | tracking_detail_46 | tracking_info_1497#P#p202307 | PRIMARY | 2023-07-02 07:53:22 | size | 3174 | NULL | Number of pages in the index | | tracking_detail_46 | tracking_info_1497#P#p202308 | PRIMARY | 2023-08-01 23:45:17 | size | 3174 | NULL | Number of pages in the index | | tracking_detail_46 | tracking_info_1497#P#p202309 | PRIMARY | 2023-09-02 03:58:29 | size | 4199 | NULL | Number of pages in the index | | tracking_detail_46 | tracking_info_1497#P#p202310 | PRIMARY | 2023-10-02 05:41:08 | size | 3430 | NULL | Number of pages in the index | | tracking_detail_46 | tracking_info_1497#P#p202311 | PRIMARY | 2023-11-01 04:24:55 | size | 1123 | NULL | Number of pages in the index | | tracking_detail_46 | tracking_info_1497#P#p202312 | PRIMARY | 2023-03-27 20:27:05 | size | 1 | NULL | Number of pages in the index | | tracking_detail_46 | tracking_info_1497#P#p202401 | PRIMARY | 2023-03-27 20:27:05 | size | 1 | NULL | Number of pages in the index | | tracking_detail_46 | tracking_info_1497#P#p202402 | PRIMARY | 2023-03-27 20:27:05 | size | 1 | NULL | Number of pages in the index | | tracking_detail_46 | tracking_info_1497#P#p202403 | PRIMARY | 2023-03-27 20:27:05 | size | 1 | NULL | Number of pages in the index | | tracking_detail_46 | tracking_info_1497#P#p202404 | PRIMARY | 2023-03-27 20:27:05 | size | 1 | NULL | Number of pages in the index | | tracking_detail_46 | tracking_info_1497#P#p202405 | PRIMARY | 2023-03-27 20:27:05 | size | 1 | NULL | Number of pages in the index | | tracking_detail_46 | tracking_info_1497#P#p202406 | PRIMARY | 2023-03-27 20:27:05 | size | 1 | NULL | Number of pages in the index | | tracking_detail_46 | tracking_info_1497#P#p202407 | PRIMARY | 2023-03-27 20:27:05 | size | 1 | NULL | Number of pages in the index | | tracking_detail_46 | tracking_info_1497#P#p202408 | PRIMARY | 2023-03-27 20:27:05 | size | 1 | NULL | Number of pages in the index | | tracking_detail_46 | tracking_info_1497#P#p202409 | PRIMARY | 2023-03-27 20:27:05 | size | 1 | NULL | Number of pages in the index | | tracking_detail_46 | tracking_info_1497#P#p202410 | PRIMARY | 2023-03-27 20:27:05 | size | 1 | NULL | Number of pages in the index | | tracking_detail_46 | tracking_info_1497#P#p202411 | PRIMARY | 2023-03-27 20:27:05 | size | 1 | NULL | Number of pages in the index | | tracking_detail_46 | tracking_info_1497#P#p202412 | PRIMARY | 2023-03-27 20:27:05 | size | 1 | NULL | Number of pages in the index | +--------------------+------------------------------+------------+---------------------+-----------+------------+-------------+------------------------------+ 20 rows in set (0.01 sec)
其中不同分区对应主键索引的数据页数量差异较大。
partition |
stat_value |
p202305 |
67582 |
p202306 |
110908 |
p202307 |
3174 |
p202308 |
3174 |
根据数据页的数量计算索引大小，其中由于是压缩表，因此页大小等于 8 KB。
mysql> SELECT SUM(stat_value) AS pages, index_name , SUM(stat_value) * 8 * 1024 AS size FROM mysql.innodb_index_stats WHERE table_name LIKE 'tracking_info_1497%' AND database_name = 'tracking_detail_46' AND stat_name = 'size' GROUP BY index_name; +--------+-----------------+------------+ | pages | index_name | size | +--------+-----------------+------------+ | 193603 | PRIMARY | 1585995776 | | 80076 | idx_business_id | 655982592 | +--------+-----------------+------------+ 2 rows in set (0.00 sec)
对比 information_schema.tables 表中记录的 DATA_LENGTH 与 INDEX_LENGTH，显示两者相等，表明索引大小计算正确。
mysql> select * from information_schema.tables
where table_name = 'tracking_info_1497' AND table_schema = 'tracking_detail_46' \G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: tracking_detail_46
TABLE_NAME: tracking_info_1497
TABLE_TYPE: BASE TABLE
ENGINE: InnoDB
VERSION: 10
ROW_FORMAT: Compressed
TABLE_ROWS: 15785619
AVG_ROW_LENGTH: 100
DATA_LENGTH: 1585995776
MAX_DATA_LENGTH: 0
INDEX_LENGTH: 655982592
DATA_FREE: 26214400
AUTO_INCREMENT: NULL
CREATE_TIME: 2023-10-11 20:10:00
UPDATE_TIME: 2023-11-16 11:18:15
CHECK_TIME: NULL
TABLE_COLLATION: utf8mb4_general_ci
CHECKSUM: NULL
CREATE_OPTIONS: row_format=COMPRESSED KEY_BLOCK_SIZE=8 partitioned
TABLE_COMMENT: 物流跟踪明细
1 row in set (0.01 sec)
更新统计信息，验证是否是统计信息导致的差异。
mysql> analyze table tracking_detail_46.tracking_info_1497; +---------------------------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------------------------------------+---------+----------+----------+ | tracking_detail_46.tracking_info_1497 | analyze | status | OK | +---------------------------------------+---------+----------+----------+ 1 row in set (0.31 sec)
重新查看索引的统计信息
mysql> SELECT PARTITION_NAME,DATA_LENGTH,INDEX_LENGTH,DATA_FREE,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'tracking_info_1497'; +----------------+-------------+--------------+-----------+------------+ | PARTITION_NAME | DATA_LENGTH | INDEX_LENGTH | DATA_FREE | TABLE_ROWS | +----------------+-------------+--------------+-----------+------------+ | p202305 | 1185398784 | 419889152 | 0 | 11515577 | | p202306 | 1168072704 | 542638080 | 0 | 12883994 | | p202307 | 730333184 | 432537600 | 0 | 10481848 | | p202308 | 847249408 | 488636416 | 0 | 12156027 | | p202309 | 952107008 | 546308096 | 0 | 14038632 | | p202310 | 942145536 | 543670272 | 0 | 13551039 | | p202311 | 728236032 | 425197568 | 0 | 11022861 | | p202312 | 8192 | 8192 | 0 | 0 | | p202401 | 8192 | 8192 | 0 | 0 | | p202402 | 8192 | 8192 | 0 | 0 | | p202403 | 8192 | 8192 | 0 | 0 | | p202404 | 8192 | 8192 | 0 | 0 | | p202405 | 8192 | 8192 | 0 | 0 | | p202406 | 8192 | 8192 | 0 | 0 | | p202407 | 8192 | 8192 | 0 | 0 | | p202408 | 8192 | 8192 | 0 | 0 | | p202409 | 8192 | 8192 | 0 | 0 | | p202410 | 8192 | 8192 | 0 | 0 | | p202411 | 8192 | 8192 | 0 | 0 | | p202412 | 8192 | 8192 | 0 | 0 | +----------------+-------------+--------------+-----------+------------+ 20 rows in set (0.00 sec) mysql> select * from mysql.innodb_index_stats where database_name='tracking_detail_46' and table_name like 'tracking_info_1497%' and index_name='PRIMARY' and stat_name = 'size'; +--------------------+------------------------------+------------+---------------------+-----------+------------+-------------+------------------------------+ | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description | +--------------------+------------------------------+------------+---------------------+-----------+------------+-------------+------------------------------+ | tracking_detail_46 | tracking_info_1497#P#p202305 | PRIMARY | 2023-11-16 11:20:12 | size | 144702 | NULL | Number of pages in the index | | tracking_detail_46 | tracking_info_1497#P#p202306 | PRIMARY | 2023-11-16 11:20:12 | size | 142587 | NULL | Number of pages in the index | | tracking_detail_46 | tracking_info_1497#P#p202307 | PRIMARY | 2023-11-16 11:20:12 | size | 89152 | NULL | Number of pages in the index | | tracking_detail_46 | tracking_info_1497#P#p202308 | PRIMARY | 2023-11-16 11:20:12 | size | 103424 | NULL | Number of pages in the index | | tracking_detail_46 | tracking_info_1497#P#p202309 | PRIMARY | 2023-11-16 11:20:12 | size | 116224 | NULL | Number of pages in the index | | tracking_detail_46 | tracking_info_1497#P#p202310 | PRIMARY | 2023-11-16 11:20:12 | size | 115008 | NULL | Number of pages in the index | | tracking_detail_46 | tracking_info_1497#P#p202311 | PRIMARY | 2023-11-16 11:20:12 | size | 88896 | NULL | Number of pages in the index | | tracking_detail_46 | tracking_info_1497#P#p202312 | PRIMARY | 2023-11-16 11:20:12 | size | 1 | NULL | Number of pages in the index | | tracking_detail_46 | tracking_info_1497#P#p202401 | PRIMARY | 2023-11-16 11:20:12 | size | 1 | NULL | Number of pages in the index | | tracking_detail_46 | tracking_info_1497#P#p202402 | PRIMARY | 2023-11-16 11:20:12 | size | 1 | NULL | Number of pages in the index | | tracking_detail_46 | tracking_info_1497#P#p202403 | PRIMARY | 2023-11-16 11:20:12 | size | 1 | NULL | Number of pages in the index | | tracking_detail_46 | tracking_info_1497#P#p202404 | PRIMARY | 2023-11-16 11:20:12 | size | 1 | NULL | Number of pages in the index | | tracking_detail_46 | tracking_info_1497#P#p202405 | PRIMARY | 2023-11-16 11:20:12 | size | 1 | NULL | Number of pages in the index | | tracking_detail_46 | tracking_info_1497#P#p202406 | PRIMARY | 2023-11-16 11:20:12 | size | 1 | NULL | Number of pages in the index | | tracking_detail_46 | tracking_info_1497#P#p202407 | PRIMARY | 2023-11-16 11:20:12 | size | 1 | NULL | Number of pages in the index | | tracking_detail_46 | tracking_info_1497#P#p202408 | PRIMARY | 2023-11-16 11:20:12 | size | 1 | NULL | Number of pages in the index | | tracking_detail_46 | tracking_info_1497#P#p202409 | PRIMARY | 2023-11-16 11:20:12 | size | 1 | NULL | Number of pages in the index | | tracking_detail_46 | tracking_info_1497#P#p202410 | PRIMARY | 2023-11-16 11:20:12 | size | 1 | NULL | Number of pages in the index | | tracking_detail_46 | tracking_info_1497#P#p202411 | PRIMARY | 2023-11-16 11:20:12 | size | 1 | NULL | Number of pages in the index | | tracking_detail_46 | tracking_info_1497#P#p202412 | PRIMARY | 2023-11-16 11:20:12 | size | 1 | NULL | Number of pages in the index | +--------------------+------------------------------+------------+---------------------+-----------+------------+-------------+------------------------------+ 20 rows in set (0.00 sec)
不同分区对应主键索引的数据页数量接近
partition |
stat_value |
p202305 |
144702 |
p202306 |
142587 |
p202307 |
89152 |
p202308 |
103424 |
重新查看索引大小
mysql> SELECT SUM(stat_value) AS pages, index_name
, SUM(stat_value) * 8 * 1024 AS size
FROM mysql.innodb_index_stats
WHERE table_name LIKE 'tracking_info_1497%'
AND database_name = 'tracking_detail_46'
AND stat_name = 'size'
GROUP BY index_name;
+--------+-----------------+------------+
| pages | index_name | size |
+--------+-----------------+------------+
| 800006 | PRIMARY | 6553649152 |
| 414915 | idx_business_id | 3398983680 |
+--------+-----------------+------------+
2 rows in set (0.01 sec)
mysql> select * from information_schema.tables
where table_name = 'tracking_info_1497' AND table_schema = 'tracking_detail_46' \G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: tracking_detail_46
TABLE_NAME: tracking_info_1497
TABLE_TYPE: BASE TABLE
ENGINE: InnoDB
VERSION: 10
ROW_FORMAT: Compressed
TABLE_ROWS: 85650217
AVG_ROW_LENGTH: 76
DATA_LENGTH: 6553649152
MAX_DATA_LENGTH: 0
INDEX_LENGTH: 3398983680
DATA_FREE: 26214400
AUTO_INCREMENT: NULL
CREATE_TIME: 2023-10-11 20:10:00
UPDATE_TIME: 2023-11-16 11:21:15
CHECK_TIME: NULL
TABLE_COLLATION: utf8mb4_general_ci
CHECKSUM: NULL
CREATE_OPTIONS: row_format=COMPRESSED KEY_BLOCK_SIZE=8 partitioned
TABLE_COMMENT: 物流跟踪明细
1 row in set (0.00 sec)
对比大小，显示该表的差异从 4 倍缩小为 1.09 倍。
mysql> select (6553649152+3398983680)/9127723008; +------------------------------------+ | (6553649152+3398983680)/9127723008 | +------------------------------------+ | 1.0904 | +------------------------------------+ 1 row in set (0.00 sec) mysql> select 9127723008/2268192768; +-----------------------+ | 9127723008/2268192768 | +-----------------------+ | 4.0242 | +-----------------------+ 1 row in set (0.00 sec)
表明物理文件的大小准确，统计信息不准确。
知识点
统计信息
关于统计信息，主要存在以下三个问题：
- 数据如何计算
- 数据如何存储
- 数据如何更新
下面分别进行简单介绍。
存储方式
InnoDB 提供了两种存储统计数据的方式：
- 永久性存储统计数据，保存在磁盘上，服务器重启后依然存在；
- 非永久性存储统计数据，保存在内存中，服务器关闭时清除数据，重启后重新收集。
系统参数用于控制是否永久性存储统计数据，MySQL 5.6 版本之前默认 OFF，表示保存在内存中，自 MySQL 5.6 版本起默认 ON，表示保存在磁盘中。
mysql> select @@innodb_stats_persistent; +---------------------------+ | @@innodb_stats_persistent | +---------------------------+ | 1 | +---------------------------+ 1 row in set (0.00 sec)
其中，永久性存储具体是保存在以下两张系统表中：
- innodb_table_stats，保存表的统计数据，每一条记录对应一个表的统计数据；
- innodb_index_stats，保存索引的统计数据，每一条记录对应一个索引的第一个统计项的统计数据。
两张表每个列的用途见下表。
innodb_table_stats 表
字段名 |
描述 |
database_name |
数据库名 |
table_name |
表名 |
last_update |
本条记录最后更新的时间 |
n_rows |
表中记录的条数 |
clustered_index_size |
表的聚簇索引占用的页面数量 |
sum_of_other_index_sizes |
表的其他索引占用的页面数量 |
其中有两个统计项：
- n_rows，表中记录的条数
- clustered_index_size & sum_of_other_index_sizes，索引的页面数量
innodb_index_stats 表
字段名 |
描述 |
database_name |
数据库名 |
table_name |
表名 |
index_name |
索引名 |
last_update |
本条记录最后更新的时间 |
stat_name |
统计项的名称 |
stat_value |
对应的统计项的值 |
sample_size |
为生成统计数据而采样的页面数量 |
stat_description |
对应的统计项的描述 |
其中有三个统计项：
- n_leaf_pages，索引的叶子节点实际占用多少页面；
- size，索引总共占用多少页面（包括已经分配给叶子节点或非叶子节点段但尚未使用的页面）；
- n_diff_pfxNN，表示对应的索引列不重复的值有多少，其中 NN 可以被替换为 01、02 等。
查询一张表的统计数据进行举例说明。
mysql> select * from mysql.innodb_table_stats where database_name='test_zk' and table_name='t3_bak'; +---------------+------------+---------------------+---------+----------------------+--------------------------+ | database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes | +---------------+------------+---------------------+---------+----------------------+--------------------------+ | test_zk | t3_bak | 2023-08-08 12:35:47 | 9976096 | 27448 | 13747 | +---------------+------------+---------------------+---------+----------------------+--------------------------+ 1 row in set (0.00 sec) mysql> select * from mysql.innodb_index_stats where database_name='test_zk' and table_name='t3_bak'; +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description | +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | test_zk | t3_bak | PRIMARY | 2023-08-08 12:35:47 | n_diff_pfx01 | 9976096 | 20 | id | | test_zk | t3_bak | PRIMARY | 2023-08-08 12:35:47 | n_leaf_pages | 23981 | NULL | Number of leaf pages in the index | | test_zk | t3_bak | PRIMARY | 2023-08-08 12:35:47 | size | 27448 | NULL | Number of pages in the index | | test_zk | t3_bak | idx_name_a | 2023-08-08 12:35:47 | n_diff_pfx01 | 1 | 2 | name | | test_zk | t3_bak | idx_name_a | 2023-08-08 12:35:47 | n_diff_pfx02 | 9988160 | 20 | name,a | | test_zk | t3_bak | idx_name_a | 2023-08-08 12:35:47 | n_diff_pfx03 | 9988160 | 20 | name,a,id | | test_zk | t3_bak | idx_name_a | 2023-08-08 12:35:47 | n_leaf_pages | 12005 | NULL | Number of leaf pages in the index | | test_zk | t3_bak | idx_name_a | 2023-08-08 12:35:47 | size | 13747 | NULL | Number of pages in the index | +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ 8 rows in set (0.00 sec)
去重后共有以下三个统计项：
- 表中记录的条数
- 索引的页面数量
- 索引列不重复的值有多少
下面分别介绍每个统计项的计算方式。
统计项计算方式
1）表中记录的条数
计算过程可以简化为：
- 按照一定的算法从聚簇索引中选取几个叶子节点页面；
- 统计每个页面中包括的记录数量，然后计算一个页面中平均包含的记录数量；
- 每个页面平均包含的记录数量X全部叶子节点的数量，结果就是该表的 n_rows 值。
因此，n_rows 值的精确与否取决于统计时采样的页面数量，具体由 innodb_stats_persistent_sample_pages 系统变量控制，默认 20。
mysql> select @@innodb_stats_persistent_sample_pages; +----------------------------------------+ | @@innodb_stats_persistent_sample_pages | +----------------------------------------+ | 20 | +----------------------------------------+ 1 row in set (0.00 sec)
2）索引的页面数量
每个索引占用两个段（segment），一个叶子节点段，一个非叶子节点段。
因此索引的页面数量等于对应的叶子节点段与非叶子节点段分别占用的页面数量之和。
数据字典中存在每个表中各个索引对应的根页面位置，而根页面的Page Header中保存叶子节点段与非叶子节点段对应的Segment header。
图片
其中：
- root page 的 FSEG Header 中有两组指针，分别由 number 和 offset 组成，指向了该索引结构（B+ 树）的 segment；
- FSEG 是区的一种状态，表示附属于某个段的区。
段是以区（extent）为单位申请存储空间的，对于 16KB 的页来说，连续的 64 个页就是一个区，也就是说一个区默认占用 1MB 空间大小。
区通过链表进行管理，链表基节点List Base Node中保存链表的头节点和尾节点的指针以及这个链表中包含了多少个节点即List Length。
图片
其中：
- XDES Entry的全称是Extent Descriptor Entry，每一个区都对应着一个XDES Entry结构，这个结构记录了对应的区的一些属性。
通过链表的List Length字段读出该段占用的数量，每个区占用 64 个页，就可以统计出整个段占用的空间，进而得到索引的页面数量。
3）索引列不重复的值有多少
计算过程可以简化为：
- 按照一定的算法从聚簇索引中选取几个叶子节点页面；
- 统计所有页面中包括的不重复值的数量。
其中对于有多个列的联合索引将从前往后依次统计列的组合的不重复的值有多少。
mysql> select stat_name,stat_value,sample_size,stat_description from mysql.innodb_index_stats where database_name='test_zk' and table_name='t3_bak' and index_name='idx_name_a' and stat_name like 'n_diff_pfx%'; +--------------+------------+-------------+------------------+ | stat_name | stat_value | sample_size | stat_description | +--------------+------------+-------------+------------------+ | n_diff_pfx01 | 1 | 2 | name | | n_diff_pfx02 | 9988160 | 20 | name,a | | n_diff_pfx03 | 9988160 | 20 | name,a,id | +--------------+------------+-------------+------------------+ 3 rows in set (0.00 sec)
其中：
- 对于普通二级索引，并不能保证索引列值唯一，因此还会统计二级索引列+主键的不重复的值的数量；
- 对于主键和唯一二级索引，本身保证索引列值唯一，因此不需要统计索引列+主键的不重复的值的数量；
- 对于有多个列的联合索引来说，采样的页面数量是：innodb_stats_persistent_sample_pagesX索引列的个数。
更新方式
更新统计数据的方式分为以下两种：
- 自动更新，系统参数innodb_stats_auto_recalc用于控制服务器是否自动重新计算统计数据，默认 ON。每个表都维护一个变量，用于记录对该表进行增删改的记录条数，当发生变动的记录数量超过表大小的 10% 以后，如果开启自动更新，将自动重新计算统计数据，这个过程是异步的，因此存在延迟；
- 手动更新，手动调用analyze table语句时立即重新计算统计数据，这个过程是同步的。注意当表中索引较多或采样页面非常多时，这个过程可能会比较慢。
实际上，手动更新还有一种方式，即直接修改 innodb_table_stats 与 innodb_index_stats 数据表，然后执行flush table命令重新加载更改过的数据。但是正常情况下不需要使用这种方式，因此不详细介绍。
下面进行 ANALYZE TABLE 复杂度分析，便于理解什么场景下手动调用analyze table语句会慢。
ANALYZE TABLE 复杂度分析
执行 ANALYZE TABLE 命令的复杂度取决于以下三个条件：
- 采样的页面数量，由innodb_stats_persistent_sample_pages系统参数控制；
- 表中索引列的数量；
- 分区的数量，默认 1，表示没有分区。
ANALYZE TABLE 复杂性的近似公式为：
innodb_stats_persistent_sample_pages * 表中索引列的数量 * 分区数
ANALYZE TABLE 复杂度可以描述为：
O(n_sample
* (n_cols_in_uniq_i
+ n_cols_in_non_uniq_i
+ n_cols_in_pk * (1 + n_non_uniq_i))
* n_part)
其中：
- n_sample 是采样的页数
- n_cols_in_uniq_i 是所有唯一索引中所有列的总数（不包括主键列）
- n_cols_in_non_uniq_i 是所有非唯一索引中所有列的总数
- n_cols_in_pk 是主键中的列数（如果未定义主键，InnoDB 在内部创建单列主键）
- n_non_uniq_i 是表中非唯一索引的数量
- n_part 是分区数。如果未定义分区，则该表被视为单个分区
根据复杂度公式可以预估 ANALYZE TABLE 命令的执行用时。
比如前文中更新统计信息的表结构。
PRIMARY KEY (`id`,`partition_time`), KEY `idx_business_id` (`business_id`,`template_id`,`source_ele_id`)
可以确定以下值：
- n_cols_in_uniq_i 是所有唯一索引中所有列的总数（不包括主键列），0
- n_cols_in_non_uniq_i 是所有非唯一索引中所有列的总数，3
- n_cols_in_pk 是主键中的列数（如果未定义主键，InnoDB 在内部创建单列主键），2
- n_non_uniq_i 是表中非唯一索引的数量，1
- n_part 是分区数，20
计算扫描的页数等于 2800
mysql> select 20*(0+3+2*(1+1))*20; +---------------------+ | 20*(0+3+2*(1+1))*20 | +---------------------+ | 2800 | +---------------------+ 1 row in set (0.00 sec)
执行用时 0.31s
mysql> analyze table tracking_detail_46.tracking_info_1497; +---------------------------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------------------------------------+---------+----------+----------+ | tracking_detail_46.tracking_info_1497 | analyze | status | OK | +---------------------------------------+---------+----------+----------+ 1 row in set (0.31 sec)
结论
数据库实例的物理文件的大小是根据系统表计算的表大小的两倍以上，原因是统计数据不准确，具体是索引数据页的数量不准确。
在定位到差异最大的表以后，通过更新统计信息，将差异从 4 倍缩小为 1.09 倍。
索引的大小等于数据页的数量X页大小，其中数据页的数量依赖统计数据，显然在除了数据行以外，数据页的数量也是一个重要的统计项。
统计数据的永久性存储具体保存在 innodb_table_stats 与 innodb_index_stats 数据表中。
其中主要有以下三个统计项：
- 表中记录的条数，等于采样的每个页面平均包含的记录数量X全部叶子节点的数量；
- 索引的页面数量，索引的两个段分别查看List Length字段读出该段占用的数量，每个区占用 64 个页，就可以统计出整个段占用的空间，进而得到索引的页面数量；
- 索引列不重复的值有多少，等于采样的所有页面中包括的不重复值的数量。
参考教程
- MySQL InnoDB配置统计信息
https://www.cnblogs.com/wanbin/p/9554091.html
- MySQL Document: Estimating ANALYZE TABLE Complexity for InnoDB Tables
https://dev.mysql.com/doc/refman/5.7/en/innodb-analyze-table-complexity.html
- 《MySQL 是怎样运行的》
- MySQL的最深处-磁盘文件结构
https://cloud.tencent.com/developer/article/2043729
- InnoDB : Tablespace Space Management
https://dev.mysql.com/blog-archive/innodb-tablespace-space-management/
原文地址：https://mp.weixin.qq.com/s/LuPe_faOjmDvdv0I3Xb9Hw
声明
1.本站遵循行业规范，任何转载的稿件都会明确标注作者和来源；2.本站的原创文章，请转载时务必注明文章作者和来源，不尊重原创的行为我们将追究责任；3.作者投稿可能会经我们编辑修改或补充。
