偷偷摘套内射激情视频,久久精品99国产国产精,中文字幕无线乱码人妻,中文在线中文a,性爽19p

MySQL 統(tǒng)計(jì)信息不準(zhǔn)確導(dǎo)致文件大小與系統(tǒng)表大小差異大

作者：鳥山明
數(shù)據(jù)庫 MySQL
數(shù)據(jù)庫實(shí)例的物理文件的大小是根據(jù)系統(tǒng)表計(jì)算的表大小的兩倍以上，原因是統(tǒng)計(jì)數(shù)據(jù)不準(zhǔn)確，具體是索引數(shù)據(jù)頁的數(shù)量不準(zhǔn)確。

引言

MySQL 的統(tǒng)計(jì)信息中包括多個(gè)統(tǒng)計(jì)項(xiàng)，由于基于采樣計(jì)算，因此存在誤差，最常見的是統(tǒng)計(jì)項(xiàng)【表的行數(shù)】不準(zhǔn)確，可能導(dǎo)致執(zhí)行計(jì)劃選擇錯(cuò)誤。

本文通過分析系統(tǒng)表中表大小與物理文件大小差異較大的現(xiàn)象，定位到原因是統(tǒng)計(jì)項(xiàng)【索引數(shù)據(jù)頁的數(shù)量】不準(zhǔn)確導(dǎo)致，文中對統(tǒng)計(jì)信息相關(guān)的知識點(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è)庫的大小。

select 
  table_schema as '數(shù)據(jù)庫', 
  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;

主庫，執(zhí)行結(jié)果如下所示。

+--------------------+------------+------------------+------------------+------------------+---------------+
| 數(shù)據(jù)庫              | 行數(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)

從庫，執(zhí)行結(jié)果如下所示。

+--------------------+------------+------------------+------------------+------------------+---------------+
| 數(shù)據(jù)庫              | 行數(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)

其中：

  • 主從差異大，從庫大小是主庫的 2 倍左右。

主庫，查看具體表的大小，顯示單表 3G 左右，碎片 20M 左右。

圖片圖片

監(jiān)控顯示用戶數(shù)據(jù) 2T。

圖片圖片

由于監(jiān)控?cái)?shù)據(jù)采集自物理文件的大小，因此表現(xiàn)為物理文件大小和系統(tǒng)表大小不一致。

物理文件

庫級別

# 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 .

表級別對比結(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 '模板編號',
  `business_id` varchar(100) NOT NULL COMMENT '業(yè)務(wù)操作單號',
  `related_id` varchar(100) DEFAULT NULL COMMENT '關(guān)聯(lián)業(yè)務(wù)單號，例如：包裹號',
  `remark` varchar(1024) DEFAULT NULL COMMENT '物流跟蹤話術(shù)',
  `is_delete` tinyint(2) NOT NULL DEFAULT '0' COMMENT '刪除標(biāo)識，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ū)。

查看該表對應(yīng)的文件大小，由于是分區(qū)表，因此對應(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)容，會產(chǎn)生碎片，導(dǎo)致物理文件大小大于表的實(shí)際數(shù)據(jù)大小。

2、事務(wù)未提交：在 InnoDB 中，如果存在未提交的事務(wù)，那么這部分?jǐn)?shù)據(jù)會被鎖定，導(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、索引：索引也會占用一部分的空間，如果表含有索引，那么物理文件的大小就會大于表的數(shù)據(jù)大小。

6、預(yù)留空間：MySQL 在創(chuàng)建表或者擴(kuò)容表的時(shí)候，會預(yù)留一部分空間，這也可能導(dǎo)致物理文件大小和表的大小不一致。

總結(jié)就是數(shù)據(jù)頁未充分利用、刪除數(shù)據(jù)后產(chǎn)生的碎片、索引等占用的空間等原因，都能導(dǎo)致物理文件大小和系統(tǒng)表大小不一致。

由于之前有刪除操作，因此懷疑與數(shù)據(jù)刪除有關(guān)系，分別分析刪除行與刪除表是否會導(dǎo)致該現(xiàn)象。

刪除行有兩種場景：

  • 已刪除已提交的行數(shù)據(jù)不會釋放空間，原因是邏輯刪除用于復(fù)用，具體包括行記錄復(fù)用與數(shù)據(jù)頁復(fù)用。這些可以復(fù)用，但是沒有使用的空間稱為碎片，表現(xiàn)為 data_length 減小，data_free 增加，碎片空間可以通過重建表回收。而文中查詢系統(tǒng)表時(shí)包括了碎片；
  • 已刪除未提交的行數(shù)據(jù)不會釋放空間，原因是數(shù)據(jù)保存在 undo log 中用于回滾和 MVCC。如果有大量更新操作，將導(dǎo)致 undo log 無法清理，表現(xiàn)為 undo log 文件過大，如果有非常多的長事務(wù)，還會表現(xiàn)為 history list length 過大。而該實(shí)例中未發(fā)現(xiàn)大量未提交事務(wù)。

刪除表有一種場景：

  • 文件未刪除，原因是系統(tǒng)占用。

因此查看 mysqld 進(jìn)程打開的 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，因此和已刪除未釋放的文件無關(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ù)頁的數(shù)量??頁大小。

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ù)頁的數(shù)量不準(zhǔn)確。

mysql.innodb_index_stats數(shù)據(jù)表中 stat_name 列與 stat_value 分別表示各種類型統(tǒng)計(jì)信息與對應(yīng)的值：

  • 如果 stat_name = size，則 stat_value 列顯示索引中的總大?。▎挝?page）；
  • 如果 stat_name = n_leaf_pages，則 stat_value 列顯示索引中的葉子頁數(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ù)頁的數(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ū)對應(yīng)主鍵索引的數(shù)據(jù)頁數(shù)量差異較大。

partition

stat_value

p202305

67582

p202306

110908

p202307

3174

p202308

3174

根據(jù)數(shù)據(jù)頁的數(shù)量計(jì)算索引大小，其中由于是壓縮表，因此頁大小等于 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，顯示兩者相等，表明索引大小計(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ū)對應(yīng)主鍵索引的數(shù)據(jù)頁數(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)

對比大小，顯示該表的差異從 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)確。

知識點(diǎn)

統(tǒng)計(jì)信息

關(guān)于統(tǒng)計(jì)信息，主要存在以下三個(gè)問題：

  • 數(shù)據(jù)如何計(jì)算
  • 數(shù)據(jù)如何存儲
  • 數(shù)據(jù)如何更新

下面分別進(jìn)行簡單介紹。

存儲方式

InnoDB 提供了兩種存儲統(tǒng)計(jì)數(shù)據(jù)的方式：

  • 永久性存儲統(tǒng)計(jì)數(shù)據(jù)，保存在磁盤上，服務(wù)器重啟后依然存在；
  • 非永久性存儲統(tǒng)計(jì)數(shù)據(jù)，保存在內(nèi)存中，服務(wù)器關(guān)閉時(shí)清除數(shù)據(jù)，重啟后重新收集。

系統(tǒng)參數(shù)用于控制是否永久性存儲統(tǒng)計(jì)數(shù)據(jù)，MySQL 5.6 版本之前默認(rèn) OFF，表示保存在內(nèi)存中，自 MySQL 5.6 版本起默認(rèn) ON，表示保存在磁盤中。

mysql> select @@innodb_stats_persistent;
+---------------------------+
| @@innodb_stats_persistent |
+---------------------------+
|                         1 |
+---------------------------+
1 row in set (0.00 sec)

其中，永久性存儲具體是保存在以下兩張系統(tǒng)表中：

  • innodb_table_stats，保存表的統(tǒng)計(jì)數(shù)據(jù)，每一條記錄對應(yīng)一個(gè)表的統(tǒng)計(jì)數(shù)據(jù)；
  • innodb_index_stats，保存索引的統(tǒng)計(jì)數(shù)據(jù)，每一條記錄對應(yīng)一個(gè)索引的第一個(gè)統(tǒng)計(jì)項(xiàng)的統(tǒng)計(jì)數(shù)據(jù)。

兩張表每個(gè)列的用途見下表。

innodb_table_stats 表

字段名

描述

database_name

數(shù)據(jù)庫名

table_name

表名

last_update

本條記錄最后更新的時(shí)間

n_rows

表中記錄的條數(shù)

clustered_index_size

表的聚簇索引占用的頁面數(shù)量

sum_of_other_index_sizes

表的其他索引占用的頁面數(shù)量

其中有兩個(gè)統(tǒng)計(jì)項(xiàng)：

  • n_rows，表中記錄的條數(shù)
  • clustered_index_size & sum_of_other_index_sizes，索引的頁面數(shù)量

innodb_index_stats 表

字段名

描述

database_name

數(shù)據(jù)庫名

table_name

表名

index_name

索引名

last_update

本條記錄最后更新的時(shí)間

stat_name

統(tǒng)計(jì)項(xiàng)的名稱

stat_value

對應(yīng)的統(tǒng)計(jì)項(xiàng)的值

sample_size

為生成統(tǒng)計(jì)數(shù)據(jù)而采樣的頁面數(shù)量

stat_description

對應(yīng)的統(tǒng)計(jì)項(xiàng)的描述

其中有三個(gè)統(tǒng)計(jì)項(xiàng)：

  • n_leaf_pages，索引的葉子節(jié)點(diǎn)實(shí)際占用多少頁面；
  • size，索引總共占用多少頁面（包括已經(jīng)分配給葉子節(jié)點(diǎn)或非葉子節(jié)點(diǎn)段但尚未使用的頁面）；
  • n_diff_pfxNN，表示對應(yīng)的索引列不重復(fù)的值有多少，其中 NN 可以被替換為 01、02 等。

查詢一張表的統(tǒng)計(jì)數(shù)據(jù)進(jìn)行舉例說明。

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ù)
  • 索引的頁面數(shù)量
  • 索引列不重復(fù)的值有多少

下面分別介紹每個(gè)統(tǒng)計(jì)項(xiàng)的計(jì)算方式。

統(tǒng)計(jì)項(xiàng)計(jì)算方式

1）表中記錄的條數(shù)

計(jì)算過程可以簡化為：

  • 按照一定的算法從聚簇索引中選取幾個(gè)葉子節(jié)點(diǎn)頁面；
  • 統(tǒng)計(jì)每個(gè)頁面中包括的記錄數(shù)量，然后計(jì)算一個(gè)頁面中平均包含的記錄數(shù)量；
  • 每個(gè)頁面平均包含的記錄數(shù)量??全部葉子節(jié)點(diǎn)的數(shù)量，結(jié)果就是該表的 n_rows 值。

因此，n_rows 值的精確與否取決于統(tǒng)計(jì)時(shí)采樣的頁面數(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）索引的頁面數(shù)量

每個(gè)索引占用兩個(gè)段（segment），一個(gè)葉子節(jié)點(diǎn)段，一個(gè)非葉子節(jié)點(diǎn)段。

因此索引的頁面數(shù)量等于對應(yīng)的葉子節(jié)點(diǎn)段與非葉子節(jié)點(diǎn)段分別占用的頁面數(shù)量之和。

數(shù)據(jù)字典中存在每個(gè)表中各個(gè)索引對應(yīng)的根頁面位置，而根頁面的Page Header中保存葉子節(jié)點(diǎn)段與非葉子節(jié)點(diǎn)段對應(yīng)的Segment header。

圖片圖片

其中：

  • root page 的 FSEG Header 中有兩組指針，分別由 number 和 offset 組成，指向了該索引結(jié)構(gòu)（B+ 樹）的 segment；
  • FSEG 是區(qū)的一種狀態(tài)，表示附屬于某個(gè)段的區(qū)。

段是以區(qū)（extent）為單位申請存儲空間的，對于 16KB 的頁來說，連續(xù)的 64 個(gè)頁就是一個(gè)區(qū)，也就是說一個(gè)區(qū)默認(rèn)占用 1MB 空間大小。

區(qū)通過鏈表進(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ū)都對應(yīng)著一個(gè)XDES Entry結(jié)構(gòu)，這個(gè)結(jié)構(gòu)記錄了對應(yīng)的區(qū)的一些屬性。

通過鏈表的List Length字段讀出該段占用的數(shù)量，每個(gè)區(qū)占用 64 個(gè)頁，就可以統(tǒng)計(jì)出整個(gè)段占用的空間，進(jìn)而得到索引的頁面數(shù)量。

3）索引列不重復(fù)的值有多少

計(jì)算過程可以簡化為：

  • 按照一定的算法從聚簇索引中選取幾個(gè)葉子節(jié)點(diǎn)頁面；
  • 統(tǒng)計(jì)所有頁面中包括的不重復(fù)值的數(shù)量。

其中對于有多個(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)

其中：

  • 對于普通二級索引，并不能保證索引列值唯一，因此還會統(tǒng)計(jì)二級索引列?主鍵的不重復(fù)的值的數(shù)量；
  • 對于主鍵和唯一二級索引，本身保證索引列值唯一，因此不需要統(tǒng)計(jì)索引列?主鍵的不重復(fù)的值的數(shù)量；
  • 對于有多個(gè)列的聯(lián)合索引來說，采樣的頁面數(shù)量是：innodb_stats_persistent_sample_pages??索引列的個(gè)數(shù)。

更新方式

更新統(tǒng)計(jì)數(shù)據(jù)的方式分為以下兩種：

  • 自動更新，系統(tǒng)參數(shù)innodb_stats_auto_recalc用于控制服務(wù)器是否自動重新計(jì)算統(tǒng)計(jì)數(shù)據(jù)，默認(rèn) ON。每個(gè)表都維護(hù)一個(gè)變量，用于記錄對該表進(jìn)行增刪改的記錄條數(shù)，當(dāng)發(fā)生變動的記錄數(shù)量超過表大小的 10% 以后，如果開啟自動更新，將自動重新計(jì)算統(tǒng)計(jì)數(shù)據(jù)，這個(gè)過程是異步的，因此存在延遲；
  • 手動更新，手動調(diào)用analyze table語句時(shí)立即重新計(jì)算統(tǒng)計(jì)數(shù)據(jù)，這個(gè)過程是同步的。注意當(dāng)表中索引較多或采樣頁面非常多時(shí)，這個(gè)過程可能會比較慢。

實(shí)際上，手動更新還有一種方式，即直接修改 innodb_table_stats 與 innodb_index_stats 數(shù)據(jù)表，然后執(zhí)行flush table命令重新加載更改過的數(shù)據(jù)。但是正常情況下不需要使用這種方式，因此不詳細(xì)介紹。

下面進(jìn)行 ANALYZE TABLE 復(fù)雜度分析，便于理解什么場景下手動調(diào)用analyze table語句會慢。

ANALYZE TABLE 復(fù)雜度分析

執(zhí)行 ANALYZE TABLE 命令的復(fù)雜度取決于以下三個(gè)條件：

  • 采樣的頁面數(shù)量，由innodb_stats_persistent_sample_pages系統(tǒng)參數(shù)控制；
  • 表中索引列的數(shù)量；
  • 分區(qū)的數(shù)量，默認(rèn) 1，表示沒有分區(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 是采樣的頁數(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ì)算掃描的頁數(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ù)庫實(shí)例的物理文件的大小是根據(jù)系統(tǒng)表計(jì)算的表大小的兩倍以上，原因是統(tǒng)計(jì)數(shù)據(jù)不準(zhǔn)確，具體是索引數(shù)據(jù)頁的數(shù)量不準(zhǔn)確。

在定位到差異最大的表以后，通過更新統(tǒng)計(jì)信息，將差異從 4 倍縮小為 1.09 倍。

索引的大小等于數(shù)據(jù)頁的數(shù)量??頁大小，其中數(shù)據(jù)頁的數(shù)量依賴統(tǒng)計(jì)數(shù)據(jù)，顯然在除了數(shù)據(jù)行以外，數(shù)據(jù)頁的數(shù)量也是一個(gè)重要的統(tǒng)計(jì)項(xiàng)。

統(tǒng)計(jì)數(shù)據(jù)的永久性存儲具體保存在 innodb_table_stats 與 innodb_index_stats 數(shù)據(jù)表中。

其中主要有以下三個(gè)統(tǒng)計(jì)項(xiàng)：

  • 表中記錄的條數(shù)，等于采樣的每個(gè)頁面平均包含的記錄數(shù)量??全部葉子節(jié)點(diǎn)的數(shù)量；
  • 索引的頁面數(shù)量，索引的兩個(gè)段分別查看List Length字段讀出該段占用的數(shù)量，每個(gè)區(qū)占用 64 個(gè)頁，就可以統(tǒng)計(jì)出整個(gè)段占用的空間，進(jìn)而得到索引的頁面數(shù)量；
  • 索引列不重復(fù)的值有多少，等于采樣的所有頁面中包括的不重復(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的最深處-磁盤文件結(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é)任編輯：武曉燕 來源： 丹柿小院
MySQL統(tǒng)計(jì)信息
相關(guān)推薦

2022-12-13 10:05:13

MySQL數(shù)據(jù)庫

2010-10-29 15:26:29

Oracle日志文件

2011-04-02 16:38:49

Cactirrd

2020-07-22 07:45:00

JavaScript開發(fā)技術(shù)

2010-07-12 13:57:55

SQL Server

2011-03-31 15:23:39

Cactirrd

2010-05-27 16:58:11

Linux查看文件大小

2009-12-11 10:34:15

Linux獲取文件大小

2010-02-02 10:46:51

C++獲取文件大小

2010-10-15 14:39:55

MySQL單表大小

2019-07-31 08:55:01

LinuxUbuntu技術(shù)

2009-11-24 14:32:03

PHP限制上傳文件大小

2010-03-26 14:43:09

CentOS系統(tǒng)

2009-11-16 13:46:28

PHP上傳文件大小限制

2009-11-16 09:45:51

PHP上傳文件大小

2009-06-26 10:05:10

獲取文件大小

2010-06-18 13:25:01

SQL Server查

2009-12-16 10:58:03

Linux修改文件系統(tǒng)

2010-06-28 09:43:05

SQL Server數(shù)

2019-09-03 11:23:13

MySQL技術(shù)磁盤
點(diǎn)贊
收藏

51CTO技術(shù)棧公眾號