MySQL 統(tǒng)計(jì)信息不準(zhǔn)確導(dǎo)致文件大小與系統(tǒng)表大小差異大
作者：鳥(niǎo)山明
數(shù)據(jù)庫(kù)實(shí)例的物理文件的大小是根據(jù)系統(tǒng)表計(jì)算的表大小的兩倍以上，原因是統(tǒng)計(jì)數(shù)據(jù)不準(zhǔn)確，具體是索引數(shù)據(jù)頁(yè)的數(shù)量不準(zhǔn)確。
引言
MySQL 的統(tǒng)計(jì)信息中包括多個(gè)統(tǒng)計(jì)項(xiàng)，由于基于采樣計(jì)算，因此存在誤差，最常見(jiàn)的是統(tǒng)計(jì)項(xiàng)【表的行數(shù)】不準(zhǔn)確，可能導(dǎo)致執(zhí)行計(jì)劃選擇錯(cuò)誤。
本文通過(guò)分析系統(tǒng)表中表大小與物理文件大小差異較大的現(xiàn)象，定位到原因是統(tǒng)計(jì)項(xiàng)【索引數(shù)據(jù)頁(yè)的數(shù)量】不準(zhǔn)確導(dǎo)致，文中對(duì)統(tǒng)計(jì)信息相關(guān)的知識(shí)點(diǎn)進(jìn)行了介紹。
現(xiàn)象
時(shí)間：20231026
現(xiàn)象：監(jiān)控顯示數(shù)據(jù) 2T，系統(tǒng)表顯示不到 1T，有分區(qū)表，并且有刪除操作
分析
系統(tǒng)表
執(zhí)行以下 SQL 獲取實(shí)例上每個(gè)庫(kù)的大小。
select
table_schema as '數(shù)據(jù)庫(kù)',
sum(table_rows) as '行數(shù)',
sum(
truncate(data_length / 1024 / 1024 / 1024, 2)
) as '數(shù)據(jù)容量(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 '總?cè)萘?GB)'
from
information_schema.tables
group by
table_schema;主庫(kù)，執(zhí)行結(jié)果如下所示。
+--------------------+------------+------------------+------------------+------------------+---------------+
| 數(shù)據(jù)庫(kù) | 行數(shù) | 數(shù)據(jù)容量(GB) | 索引容量(GB) | 碎片空間(GB) | 總?cè)萘?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)從庫(kù)，執(zhí)行結(jié)果如下所示。
+--------------------+------------+------------------+------------------+------------------+---------------+
| 數(shù)據(jù)庫(kù) | 行數(shù) | 數(shù)據(jù)容量(GB) | 索引容量(GB) | 碎片空間(GB) | 總?cè)萘?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)其中：
- 主從差異大，從庫(kù)大小是主庫(kù)的 2 倍左右。
主庫(kù)，查看具體表的大小，顯示單表 3G 左右，碎片 20M 左右。
圖片
監(jiān)控顯示用戶數(shù)據(jù) 2T。
圖片
由于監(jiān)控?cái)?shù)據(jù)采集自物理文件的大小，因此表現(xiàn)為物理文件大小和系統(tǒng)表大小不一致。
物理文件
庫(kù)級(jí)別
# 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 .表級(jí)別對(duì)比結(jié)果，輸出結(jié)果按照差異倒排，其中單位是 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。
查看表結(jié)構(gòu)
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 '模板編號(hào)',
`business_id` varchar(100) NOT NULL COMMENT '業(yè)務(wù)操作單號(hào)',
`related_id` varchar(100) DEFAULT NULL COMMENT '關(guān)聯(lián)業(yè)務(wù)單號(hào)，例如：包裹號(hào)',
`remark` varchar(1024) DEFAULT NULL COMMENT '物流跟蹤話術(shù)',
`is_delete` tinyint(2) NOT NULL DEFAULT '0' COMMENT '刪除標(biāo)識(shí)，0有效，1刪除',
`create_time` datetime DEFAULT NULL COMMENT '創(chuàng)建時(shí)間',
`update_time` datetime DEFAULT NULL COMMENT '更新時(shí)間',
`partition_time` datetime NOT NULL COMMENT '分區(qū)時(shí)間',
`ts` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '時(shí)間戳',
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='物流跟蹤明細(xì)'
/*!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)其中：
- 分區(qū)表
- 壓縮表
經(jīng)確認(rèn)，該實(shí)例上的表都是分區(qū)表，相當(dāng)于分表?分區(qū)。
查看該表對(duì)應(yīng)的文件大小，由于是分區(qū)表，因此對(duì)應(yīng)多個(gè)文件。
-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 物理文件大小和系統(tǒng)表大小不一致的原因可能有以下幾點(diǎn)：
1、碎片化：當(dāng)我們頻繁地更新或刪除表中的內(nèi)容，會(huì)產(chǎn)生碎片，導(dǎo)致物理文件大小大于表的實(shí)際數(shù)據(jù)大小。
2、事務(wù)未提交：在 InnoDB 中，如果存在未提交的事務(wù)，那么這部分?jǐn)?shù)據(jù)會(huì)被鎖定，導(dǎo)致物理文件的大小不變，而系統(tǒng)表的大小則可能已經(jīng)改變。
3、日志文件：InnoDB 的物理文件大小包括數(shù)據(jù)文件和日志文件兩部分，而系統(tǒng)表的大小只計(jì)算數(shù)據(jù)文件。
4、多版本并發(fā)控制（MVCC）：在 InnoDB 中因?yàn)?MVCC 的存在，使得實(shí)際數(shù)據(jù)行存在多個(gè)版本，使得物理文件大小大于系統(tǒng)表大小。
5、索引：索引也會(huì)占用一部分的空間，如果表含有索引，那么物理文件的大小就會(huì)大于表的數(shù)據(jù)大小。
6、預(yù)留空間：MySQL 在創(chuàng)建表或者擴(kuò)容表的時(shí)候，會(huì)預(yù)留一部分空間，這也可能導(dǎo)致物理文件大小和表的大小不一致。
總結(jié)就是數(shù)據(jù)頁(yè)未充分利用、刪除數(shù)據(jù)后產(chǎn)生的碎片、索引等占用的空間等原因，都能導(dǎo)致物理文件大小和系統(tǒng)表大小不一致。
由于之前有刪除操作，因此懷疑與數(shù)據(jù)刪除有關(guān)系，分別分析刪除行與刪除表是否會(huì)導(dǎo)致該現(xiàn)象。
刪除行有兩種場(chǎng)景：
- 已刪除已提交的行數(shù)據(jù)不會(huì)釋放空間，原因是邏輯刪除用于復(fù)用，具體包括行記錄復(fù)用與數(shù)據(jù)頁(yè)復(fù)用。這些可以復(fù)用，但是沒(méi)有使用的空間稱為碎片，表現(xiàn)為 data_length 減小，data_free 增加，碎片空間可以通過(guò)重建表回收。而文中查詢系統(tǒng)表時(shí)包括了碎片；
- 已刪除未提交的行數(shù)據(jù)不會(huì)釋放空間，原因是數(shù)據(jù)保存在 undo log 中用于回滾和 MVCC。如果有大量更新操作，將導(dǎo)致 undo log 無(wú)法清理，表現(xiàn)為 undo log 文件過(guò)大，如果有非常多的長(zhǎng)事務(wù)，還會(huì)表現(xiàn)為 history list length 過(guò)大。而該實(shí)例中未發(fā)現(xiàn)大量未提交事務(wù)。
刪除表有一種場(chǎng)景：
- 文件未刪除，原因是系統(tǒng)占用。
因此查看 mysqld 進(jìn)程打開(kāi)的 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 進(jìn)程占用的臨時(shí)文件，重啟后可以釋放，但是文件都很小，最大 225269 bytes，因此和已刪除未釋放的文件無(wú)關(guān)。
統(tǒng)計(jì)信息
由于該表是分區(qū)表，因此進(jìn)一步查看系統(tǒng)表判斷具體哪些分區(qū)的差異大。
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 倍左右。
查看分區(qū)準(zhǔn)確行數(shù)
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 的行數(shù)與 p202307、p202308 接近。
表明行數(shù)的統(tǒng)計(jì)信息誤差較大，因此懷疑表大小與行數(shù)類似，也是統(tǒng)計(jì)信息不準(zhǔn)確導(dǎo)致差異大。
首先需要確認(rèn) DATA_LENGTH 的計(jì)算邏輯。
根據(jù)官方文檔，DATA_LENGTH 表示聚簇索引的大小，具體等于數(shù)據(jù)頁(yè)的數(shù)量??頁(yè)大小。
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.
因此懷疑數(shù)據(jù)頁(yè)的數(shù)量不準(zhǔn)確。
mysql.innodb_index_stats數(shù)據(jù)表中 stat_name 列與 stat_value 分別表示各種類型統(tǒng)計(jì)信息與對(duì)應(yīng)的值：
- 如果 stat_name = size，則 stat_value 列顯示索引中的總大小（單位 page）；
- 如果 stat_name = n_leaf_pages，則 stat_value 列顯示索引中的葉子頁(yè)數(shù)；
- 如果 stat_name = n_diff_pfx01，則 stat_value 列顯示索引第一列中的不同值的數(shù)量。當(dāng) stat_name = n_diff_pfx02，stat_value 列顯示索引前兩列中的不同值的數(shù)量，依此類推。此外，在stat_name = n_diff_pfxNN 的情況下，stat_description 列顯示了計(jì)算的索引列。
查看mysql.innodb_index_stats表，其中僅查詢主鍵索引的索引數(shù)據(jù)頁(yè)的數(shù)量。
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)其中不同分區(qū)對(duì)應(yīng)主鍵索引的數(shù)據(jù)頁(yè)數(shù)量差異較大。
partition | stat_value |
p202305 | 67582 |
p202306 | 110908 |
p202307 | 3174 |
p202308 | 3174 |
根據(jù)數(shù)據(jù)頁(yè)的數(shù)量計(jì)算索引大小，其中由于是壓縮表，因此頁(yè)大小等于 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)對(duì)比 information_schema.tables 表中記錄的 DATA_LENGTH 與 INDEX_LENGTH，顯示兩者相等，表明索引大小計(jì)算正確。
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: 物流跟蹤明細(xì)
1 row in set (0.01 sec)更新統(tǒng)計(jì)信息，驗(yàn)證是否是統(tǒng)計(jì)信息導(dǎo)致的差異。
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)重新查看索引的統(tǒng)計(jì)信息
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)不同分區(qū)對(duì)應(yīng)主鍵索引的數(shù)據(jù)頁(yè)數(shù)量接近
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: 物流跟蹤明細(xì)
1 row in set (0.00 sec)對(duì)比大小，顯示該表的差異從 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)表明物理文件的大小準(zhǔn)確，統(tǒng)計(jì)信息不準(zhǔn)確。
知識(shí)點(diǎn)
統(tǒng)計(jì)信息
關(guān)于統(tǒng)計(jì)信息，主要存在以下三個(gè)問(wèn)題：
- 數(shù)據(jù)如何計(jì)算
- 數(shù)據(jù)如何存儲(chǔ)
- 數(shù)據(jù)如何更新
下面分別進(jìn)行簡(jiǎn)單介紹。
存儲(chǔ)方式
InnoDB 提供了兩種存儲(chǔ)統(tǒng)計(jì)數(shù)據(jù)的方式：
- 永久性存儲(chǔ)統(tǒng)計(jì)數(shù)據(jù)，保存在磁盤(pán)上，服務(wù)器重啟后依然存在；
- 非永久性存儲(chǔ)統(tǒng)計(jì)數(shù)據(jù)，保存在內(nèi)存中，服務(wù)器關(guān)閉時(shí)清除數(shù)據(jù)，重啟后重新收集。
系統(tǒng)參數(shù)用于控制是否永久性存儲(chǔ)統(tǒng)計(jì)數(shù)據(jù)，MySQL 5.6 版本之前默認(rèn) OFF，表示保存在內(nèi)存中，自 MySQL 5.6 版本起默認(rèn) ON，表示保存在磁盤(pán)中。
mysql> select @@innodb_stats_persistent;
+---------------------------+
| @@innodb_stats_persistent |
+---------------------------+
| 1 |
+---------------------------+
1 row in set (0.00 sec)其中，永久性存儲(chǔ)具體是保存在以下兩張系統(tǒng)表中：
- innodb_table_stats，保存表的統(tǒng)計(jì)數(shù)據(jù)，每一條記錄對(duì)應(yīng)一個(gè)表的統(tǒng)計(jì)數(shù)據(jù)；
- innodb_index_stats，保存索引的統(tǒng)計(jì)數(shù)據(jù)，每一條記錄對(duì)應(yīng)一個(gè)索引的第一個(gè)統(tǒng)計(jì)項(xiàng)的統(tǒng)計(jì)數(shù)據(jù)。
兩張表每個(gè)列的用途見(jiàn)下表。
innodb_table_stats 表
字段名 | 描述 |
database_name | 數(shù)據(jù)庫(kù)名 |
table_name | 表名 |
last_update | 本條記錄最后更新的時(shí)間 |
n_rows | 表中記錄的條數(shù) |
clustered_index_size | 表的聚簇索引占用的頁(yè)面數(shù)量 |
sum_of_other_index_sizes | 表的其他索引占用的頁(yè)面數(shù)量 |
其中有兩個(gè)統(tǒng)計(jì)項(xiàng)：
- n_rows，表中記錄的條數(shù)
- clustered_index_size & sum_of_other_index_sizes，索引的頁(yè)面數(shù)量
innodb_index_stats 表
字段名 | 描述 |
database_name | 數(shù)據(jù)庫(kù)名 |
table_name | 表名 |
index_name | 索引名 |
last_update | 本條記錄最后更新的時(shí)間 |
stat_name | 統(tǒng)計(jì)項(xiàng)的名稱 |
stat_value | 對(duì)應(yīng)的統(tǒng)計(jì)項(xiàng)的值 |
sample_size | 為生成統(tǒng)計(jì)數(shù)據(jù)而采樣的頁(yè)面數(shù)量 |
stat_description | 對(duì)應(yīng)的統(tǒng)計(jì)項(xiàng)的描述 |
其中有三個(gè)統(tǒng)計(jì)項(xiàng)：
- n_leaf_pages，索引的葉子節(jié)點(diǎn)實(shí)際占用多少頁(yè)面；
- size，索引總共占用多少頁(yè)面（包括已經(jīng)分配給葉子節(jié)點(diǎn)或非葉子節(jié)點(diǎn)段但尚未使用的頁(yè)面）；
- n_diff_pfxNN，表示對(duì)應(yīng)的索引列不重復(fù)的值有多少，其中 NN 可以被替換為 01、02 等。
查詢一張表的統(tǒng)計(jì)數(shù)據(jù)進(jìn)行舉例說(shuō)明。
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)去重后共有以下三個(gè)統(tǒng)計(jì)項(xiàng)：
- 表中記錄的條數(shù)
- 索引的頁(yè)面數(shù)量
- 索引列不重復(fù)的值有多少
下面分別介紹每個(gè)統(tǒng)計(jì)項(xiàng)的計(jì)算方式。
統(tǒng)計(jì)項(xiàng)計(jì)算方式
1）表中記錄的條數(shù)
計(jì)算過(guò)程可以簡(jiǎn)化為：
- 按照一定的算法從聚簇索引中選取幾個(gè)葉子節(jié)點(diǎn)頁(yè)面；
- 統(tǒng)計(jì)每個(gè)頁(yè)面中包括的記錄數(shù)量，然后計(jì)算一個(gè)頁(yè)面中平均包含的記錄數(shù)量；
- 每個(gè)頁(yè)面平均包含的記錄數(shù)量??全部葉子節(jié)點(diǎn)的數(shù)量，結(jié)果就是該表的 n_rows 值。
因此，n_rows 值的精確與否取決于統(tǒng)計(jì)時(shí)采樣的頁(yè)面數(shù)量，具體由 innodb_stats_persistent_sample_pages 系統(tǒng)變量控制，默認(rèn) 20。
mysql> select @@innodb_stats_persistent_sample_pages;
+----------------------------------------+
| @@innodb_stats_persistent_sample_pages |
+----------------------------------------+
| 20 |
+----------------------------------------+
1 row in set (0.00 sec)2）索引的頁(yè)面數(shù)量
每個(gè)索引占用兩個(gè)段（segment），一個(gè)葉子節(jié)點(diǎn)段，一個(gè)非葉子節(jié)點(diǎn)段。
因此索引的頁(yè)面數(shù)量等于對(duì)應(yīng)的葉子節(jié)點(diǎn)段與非葉子節(jié)點(diǎn)段分別占用的頁(yè)面數(shù)量之和。
數(shù)據(jù)字典中存在每個(gè)表中各個(gè)索引對(duì)應(yīng)的根頁(yè)面位置，而根頁(yè)面的Page Header中保存葉子節(jié)點(diǎn)段與非葉子節(jié)點(diǎn)段對(duì)應(yīng)的Segment header。
圖片
其中：
- root page 的 FSEG Header 中有兩組指針，分別由 number 和 offset 組成，指向了該索引結(jié)構(gòu)（B+ 樹(shù)）的 segment；
- FSEG 是區(qū)的一種狀態(tài)，表示附屬于某個(gè)段的區(qū)。
段是以區(qū)（extent）為單位申請(qǐng)存儲(chǔ)空間的，對(duì)于 16KB 的頁(yè)來(lái)說(shuō)，連續(xù)的 64 個(gè)頁(yè)就是一個(gè)區(qū)，也就是說(shuō)一個(gè)區(qū)默認(rèn)占用 1MB 空間大小。
區(qū)通過(guò)鏈表進(jìn)行管理，鏈表基節(jié)點(diǎn)List Base Node中保存鏈表的頭節(jié)點(diǎn)和尾節(jié)點(diǎn)的指針以及這個(gè)鏈表中包含了多少個(gè)節(jié)點(diǎn)即List Length。
圖片
其中：
- XDES Entry的全稱是Extent Descriptor Entry，每一個(gè)區(qū)都對(duì)應(yīng)著一個(gè)XDES Entry結(jié)構(gòu)，這個(gè)結(jié)構(gòu)記錄了對(duì)應(yīng)的區(qū)的一些屬性。
通過(guò)鏈表的List Length字段讀出該段占用的數(shù)量，每個(gè)區(qū)占用 64 個(gè)頁(yè)，就可以統(tǒng)計(jì)出整個(gè)段占用的空間，進(jìn)而得到索引的頁(yè)面數(shù)量。
3）索引列不重復(fù)的值有多少
計(jì)算過(guò)程可以簡(jiǎn)化為：
- 按照一定的算法從聚簇索引中選取幾個(gè)葉子節(jié)點(diǎn)頁(yè)面；
- 統(tǒng)計(jì)所有頁(yè)面中包括的不重復(fù)值的數(shù)量。
其中對(duì)于有多個(gè)列的聯(lián)合索引將從前往后依次統(tǒng)計(jì)列的組合的不重復(fù)的值有多少。
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)其中：
- 對(duì)于普通二級(jí)索引，并不能保證索引列值唯一，因此還會(huì)統(tǒng)計(jì)二級(jí)索引列?主鍵的不重復(fù)的值的數(shù)量；
- 對(duì)于主鍵和唯一二級(jí)索引，本身保證索引列值唯一，因此不需要統(tǒng)計(jì)索引列?主鍵的不重復(fù)的值的數(shù)量；
- 對(duì)于有多個(gè)列的聯(lián)合索引來(lái)說(shuō)，采樣的頁(yè)面數(shù)量是：innodb_stats_persistent_sample_pages??索引列的個(gè)數(shù)。
更新方式
更新統(tǒng)計(jì)數(shù)據(jù)的方式分為以下兩種：
- 自動(dòng)更新，系統(tǒng)參數(shù)innodb_stats_auto_recalc用于控制服務(wù)器是否自動(dòng)重新計(jì)算統(tǒng)計(jì)數(shù)據(jù)，默認(rèn) ON。每個(gè)表都維護(hù)一個(gè)變量，用于記錄對(duì)該表進(jìn)行增刪改的記錄條數(shù)，當(dāng)發(fā)生變動(dòng)的記錄數(shù)量超過(guò)表大小的 10% 以后，如果開(kāi)啟自動(dòng)更新，將自動(dòng)重新計(jì)算統(tǒng)計(jì)數(shù)據(jù)，這個(gè)過(guò)程是異步的，因此存在延遲；
- 手動(dòng)更新，手動(dòng)調(diào)用analyze table語(yǔ)句時(shí)立即重新計(jì)算統(tǒng)計(jì)數(shù)據(jù)，這個(gè)過(guò)程是同步的。注意當(dāng)表中索引較多或采樣頁(yè)面非常多時(shí)，這個(gè)過(guò)程可能會(huì)比較慢。
實(shí)際上，手動(dòng)更新還有一種方式，即直接修改 innodb_table_stats 與 innodb_index_stats 數(shù)據(jù)表，然后執(zhí)行flush table命令重新加載更改過(guò)的數(shù)據(jù)。但是正常情況下不需要使用這種方式，因此不詳細(xì)介紹。
下面進(jìn)行 ANALYZE TABLE 復(fù)雜度分析，便于理解什么場(chǎng)景下手動(dòng)調(diào)用analyze table語(yǔ)句會(huì)慢。
ANALYZE TABLE 復(fù)雜度分析
執(zhí)行 ANALYZE TABLE 命令的復(fù)雜度取決于以下三個(gè)條件：
- 采樣的頁(yè)面數(shù)量，由innodb_stats_persistent_sample_pages系統(tǒng)參數(shù)控制；
- 表中索引列的數(shù)量；
- 分區(qū)的數(shù)量，默認(rèn) 1，表示沒(méi)有分區(qū)。
ANALYZE TABLE 復(fù)雜性的近似公式為：
innodb_stats_persistent_sample_pages * 表中索引列的數(shù)量 * 分區(qū)數(shù)ANALYZE TABLE 復(fù)雜度可以描述為：
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 是采樣的頁(yè)數(shù)
- n_cols_in_uniq_i 是所有唯一索引中所有列的總數(shù)（不包括主鍵列）
- n_cols_in_non_uniq_i 是所有非唯一索引中所有列的總數(shù)
- n_cols_in_pk 是主鍵中的列數(shù)（如果未定義主鍵，InnoDB 在內(nèi)部創(chuàng)建單列主鍵）
- n_non_uniq_i 是表中非唯一索引的數(shù)量
- n_part 是分區(qū)數(shù)。如果未定義分區(qū)，則該表被視為單個(gè)分區(qū)
根據(jù)復(fù)雜度公式可以預(yù)估 ANALYZE TABLE 命令的執(zhí)行用時(shí)。
比如前文中更新統(tǒng)計(jì)信息的表結(jié)構(gòu)。
PRIMARY KEY (`id`,`partition_time`),
KEY `idx_business_id` (`business_id`,`template_id`,`source_ele_id`)可以確定以下值：
- n_cols_in_uniq_i 是所有唯一索引中所有列的總數(shù)（不包括主鍵列），0
- n_cols_in_non_uniq_i 是所有非唯一索引中所有列的總數(shù)，3
- n_cols_in_pk 是主鍵中的列數(shù)（如果未定義主鍵，InnoDB 在內(nèi)部創(chuàng)建單列主鍵），2
- n_non_uniq_i 是表中非唯一索引的數(shù)量，1
- n_part 是分區(qū)數(shù)，20
計(jì)算掃描的頁(yè)數(shù)等于 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)執(zhí)行用時(shí) 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)結(jié)論
數(shù)據(jù)庫(kù)實(shí)例的物理文件的大小是根據(jù)系統(tǒng)表計(jì)算的表大小的兩倍以上，原因是統(tǒng)計(jì)數(shù)據(jù)不準(zhǔn)確，具體是索引數(shù)據(jù)頁(yè)的數(shù)量不準(zhǔn)確。
在定位到差異最大的表以后，通過(guò)更新統(tǒng)計(jì)信息，將差異從 4 倍縮小為 1.09 倍。
索引的大小等于數(shù)據(jù)頁(yè)的數(shù)量??頁(yè)大小，其中數(shù)據(jù)頁(yè)的數(shù)量依賴統(tǒng)計(jì)數(shù)據(jù)，顯然在除了數(shù)據(jù)行以外，數(shù)據(jù)頁(yè)的數(shù)量也是一個(gè)重要的統(tǒng)計(jì)項(xiàng)。
統(tǒng)計(jì)數(shù)據(jù)的永久性存儲(chǔ)具體保存在 innodb_table_stats 與 innodb_index_stats 數(shù)據(jù)表中。
其中主要有以下三個(gè)統(tǒng)計(jì)項(xiàng)：
- 表中記錄的條數(shù)，等于采樣的每個(gè)頁(yè)面平均包含的記錄數(shù)量??全部葉子節(jié)點(diǎn)的數(shù)量；
- 索引的頁(yè)面數(shù)量，索引的兩個(gè)段分別查看List Length字段讀出該段占用的數(shù)量，每個(gè)區(qū)占用 64 個(gè)頁(yè)，就可以統(tǒng)計(jì)出整個(gè)段占用的空間，進(jìn)而得到索引的頁(yè)面數(shù)量；
- 索引列不重復(fù)的值有多少，等于采樣的所有頁(yè)面中包括的不重復(fù)值的數(shù)量。
參考教程
- MySQL InnoDB配置統(tǒng)計(jì)信息
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 是怎樣運(yùn)行的》
- MySQL的最深處-磁盤(pán)文件結(jié)構(gòu)
https://cloud.tencent.com/developer/article/2043729
- InnoDB : Tablespace Space Management
https://dev.mysql.com/blog-archive/innodb-tablespace-space-management/
責(zé)任編輯：武曉燕
來(lái)源：
丹柿小院
