日日操夜夜添-日日操影院-日日草夜夜操-日日干干-精品一区二区三区波多野结衣-精品一区二区三区高清免费不卡

公告：魔扣目錄網為廣大站長提供免費收錄網站服務，提交前請做好本站友鏈：【 網站目錄：http://www.ylptlb.cn 】， 免友鏈快審服務（50元/站），

點擊這里在線咨詢客服
新站提交
  • 網站：51998
  • 待審：31
  • 小程序：12
  • 文章：1030137
  • 會員：747

MySQL 統計信息不準確導致文件大小與系統表大小差異大

發布時間：2023-12-08 21:40:59 作者：網友整理

引言

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 左右。

MySQL 統計信息不準確導致文件大小與系統表大小差異大

監控顯示用戶數據 2T。

MySQL 統計信息不準確導致文件大小與系統表大小差異大

由于監控數據采集自物理文件的大小，因此表現為物理文件大小和系統表大小不一致。

物理文件

庫級別

# 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/ target=_blank class=infotextkey>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 表示聚簇索引的大小，具體等于數據頁的數量??頁大小。

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）表中記錄的條數

計算過程可以簡化為：

  • 按照一定的算法從聚簇索引中選取幾個葉子節點頁面；
  • 統計每個頁面中包括的記錄數量，然后計算一個頁面中平均包含的記錄數量；
  • 每個頁面平均包含的記錄數量??全部葉子節點的數量，結果就是該表的 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。

MySQL 統計信息不準確導致文件大小與系統表大小差異大

其中：

  • root page 的 FSEG Header 中有兩組指針，分別由 number 和 offset 組成，指向了該索引結構（B+ 樹）的 segment；
  • FSEG 是區的一種狀態，表示附屬于某個段的區。

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

區通過鏈表進行管理，鏈表基節點List Base Node中保存鏈表的頭節點和尾節點的指針以及這個鏈表中包含了多少個節點即List Length。

MySQL 統計信息不準確導致文件大小與系統表大小差異大

其中：

  • 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_pages??索引列的個數。

更新方式

更新統計數據的方式分為以下兩種：

  • 自動更新，系統參數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 倍。

索引的大小等于數據頁的數量??頁大小，其中數據頁的數量依賴統計數據，顯然在除了數據行以外，數據頁的數量也是一個重要的統計項。

統計數據的永久性存儲具體保存在 innodb_table_stats 與 innodb_index_stats 數據表中。

其中主要有以下三個統計項：

  • 表中記錄的條數，等于采樣的每個頁面平均包含的記錄數量??全部葉子節點的數量；
  • 索引的頁面數量，索引的兩個段分別查看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/

分享到：
標簽：MySQL
用戶無頭像

網友整理

注冊時間：

網站：5 個   小程序：0 個  文章：12 篇

  • 51998

    網站

  • 12

    小程序

  • 1030137

    文章

  • 747

    會員

趕快注冊賬號，推廣您的網站吧！
最新入駐小程序

數獨大挑戰2018-06-03

數獨一種數學游戲，玩家需要根據9

答題星2018-06-03

您可以通過答題星輕松地創建試卷

全階人生考試2018-06-03

各種考試題，題庫，初中，高中，大學四六

運動步數有氧達人2018-06-03

記錄運動步數，積累氧氣值。還可偷

每日養生app2018-06-03

每日養生,天天健康

體育訓練成績評定2018-06-03

通用課目體育訓練成績評定