計算MySQL表碎片的SQL整理
- 2019 年 10 月 7 日
- 筆記
這是學習筆記的第 2111 篇文章
在之前整理過一版MySQL的數據字典，整理了一圈，發現遠比想像的複雜。
當然整理的過程不光是知識梳理的過程，也是轉化為實踐場景的一個過程，通過這樣一個體系，對於整個MySQL對象生命周期管理有了較為深入的認識，這裡我來拋磚引玉，來作為深入學習MySQL數據字典的一個入口，這個問題就是：如何較為準確的計算MySQL碎片情況？
我想碎片的情況在數據庫中是很少有清晰的界定，不過它的的確確會帶來副作用，通過修復碎片情況我們可以提高SQL的執行效率，同時能夠釋放大量的空間。
最近在思考中感悟到：我們所做的很多事情，難點主要都在於查找，比如我告訴你test庫的表test_data存在大量碎片，需要修復一下，這個難度是完全可控的，我們可以很麻利的處理好，但是如果我告訴你需要收集下碎片情況，然後做一下改進，而不告訴你具體的情況，其實難度就會高几個層次。
我們這個場景主要會用到兩個數據字典表：
information_schema.tables
information_schema.INNODB_SYS_TABLESPACES
我們依次來看一下兩個數據字典的輸出信息：
查詢常規的數據字典tables得到的信息基本可以滿足我們的大多數需求。
mysql> select *from information_schema.tables where table_name='tgp_redis_command'G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: tgp_db
TABLE_NAME: tgp_redis_command
TABLE_TYPE: BASE TABLE
ENGINE: InnoDB
VERSION: 10
ROW_FORMAT: Dynamic
TABLE_ROWS: 477103
AVG_ROW_LENGTH: 111
DATA_LENGTH: 53035008
MAX_DATA_LENGTH: 0
INDEX_LENGTH: 0
DATA_FREE: 5242880
AUTO_INCREMENT: 478096
CREATE_TIME: 2019-08-16 10:54:02
UPDATE_TIME: 2019-09-23 21:12:05
CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
CHECKSUM: NULL
CREATE_OPTIONS:
TABLE_COMMENT: redis命令執行記錄表
1 row in set (0.00 sec)
通過tables字典我們可以得到通過邏輯計算出來的預估表大小，包括數據和索引的空間情況，還有平均行長度來作為校驗。
但是在這裡我們總是會感覺有些隔靴搔癢，因為我們通過計算得到了邏輯大小，但是我們還是無從得知物理文件的大小，如果逐個去通過du方式計算，這個成本是很高的，而且如果有很多的表，這種模式的效率和代價是不大合理的，所幸MySQL 5.7版本中的innodb_sys_tablespaces這個數據字典做了擴容，有了新的字段FILE_SIZE，可以完美的解決我們的疑慮，使用innodb_sys_tablespaces得到的結果如下：
mysql> select *from INNODB_SYS_TABLESPACES where name like 'tgp_db/tgp_redis_command'G
*************************** 1. row ***************************
SPACE: 818
NAME: tgp_db/tgp_redis_command
FLAG: 33
FILE_FORMAT: Barracuda
ROW_FORMAT: Dynamic
PAGE_SIZE: 16384
ZIP_PAGE_SIZE: 0
SPACE_TYPE: Single
FS_BLOCK_SIZE: 4096
FILE_SIZE: 62914560
ALLOCATED_SIZE: 62918656
1 row in set (0.00 sec)
比如常規來說我們要得到表tgp_redis_command的物理文件大小（即.ibd文件），可以通過INNODB_SYS_TABLESPACES 來查詢得到，這是一個緩存中刷新得到的實時的值，遠比我們通過du等方式計算要快捷方便許多。
可以做一個簡單的計算，表裡的數據量為：
mysql> select count(*) from tgp_redis_command;
+———-+
| count(*) |
+———-+
| 478093 |
+———-+
1 row in set (0.06 sec)
物理文件的大小，和innodb_sys_tablespaces的結果是完全一致的。
# ll *redis*
-rw-r—– 1 mysql mysql 9176 Aug 16 10:54 tgp_redis_command.frm
-rw-r—– 1 mysql mysql 62914560 Sep 23 21:14 tgp_redis_command.ibd
所以表的大小邏輯計算為data_length+index_length=53035008+0，大約是50M左右，而物理文件大小是60M左右，那麼碎片率大約是（60-50）/60約等於16.7%
我們做一下數據的truncate操作，發現物理文件的大小很快收縮了。
mysql> select *from INNODB_SYS_TABLESPACES where name like 'tgp_db/tgp_redis_command'G
*************************** 1. row ***************************
SPACE: 818
NAME: tgp_db/tgp_redis_command
FLAG: 33
FILE_FORMAT: Barracuda
ROW_FORMAT: Dynamic
PAGE_SIZE: 16384
ZIP_PAGE_SIZE: 0
SPACE_TYPE: Single
FS_BLOCK_SIZE: 4096
FILE_SIZE: 98304
ALLOCATED_SIZE: 102400
1 row in set (0.00 sec)
mysql> select *from information_schema.tables where table_name='tgp_redis_command'G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: tgp_db
TABLE_NAME: tgp_redis_command
TABLE_TYPE: BASE TABLE
ENGINE: InnoDB
VERSION: 10
ROW_FORMAT: Dynamic
TABLE_ROWS: 0
AVG_ROW_LENGTH: 0
DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
INDEX_LENGTH: 0
DATA_FREE: 0
AUTO_INCREMENT: 1
CREATE_TIME: 2019-08-16 10:54:02
UPDATE_TIME: 2019-09-24 09:51:22
CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
CHECKSUM: NULL
CREATE_OPTIONS:
TABLE_COMMENT: redis命令執行記錄表
1 row in set (0.00 sec)
[root@hb30-dba-mysql-tgp-124-34 tgp_db]# ll *redis*
-rw-r—– 1 mysql mysql 9176 Aug 16 10:54 tgp_redis_command.frm
-rw-r—– 1 mysql mysql 98304 Sep 24 09:55 tgp_redis_command.ibd
當然這種計算方式是不夠完整的，而且不夠清晰，我們可以寫一個簡單的SQL來做下統計，就是把那些需要修復的表列出來即可。
SQL如下：
SELECT
t.table_schema,
t.table_name,
t.table_rows,
t.data_length+
t.index_length data_size,
t.index_length index_size,
t.avg_row_length,
t.avg_row_length * t.table_rows logic_size,
s.FILE_SIZE,
truncate(s.FILE_SIZE/ (t.data_length+ t.index_length)*1.1*2 ,0)tab_frag
FROM
information_schema.tables t,
information_schema.INNODB_SYS_TABLESPACES s
WHERE
t.table_type = 'BASE TABLE'
and concat(t.table_schema,'/',t.table_name)=s.name
and t.table_schema not in ('sys','information_schema','mysql','test')
— and t.table_schema in('tgp_db','test')
and s.FILE_SIZE >102400000
and (t.data_length+ t.index_length)*1.1*2 < s.FILE_SIZE
order by s.FILE_SIZE;
以如下的輸出為例，我們可以看到整個碎片率極高，基本就是邏輯大小為100M，實際大小為500M，類似這種情況。
其中對於邏輯大小的計算做了一些取捨，默認在MySQL中變化的數據在10%以外是會重新去統計計算的，所以我們可以把基數調整的稍大一些為1.1，然後以這個為基線，如果碎片率超過了200%則計入統計結果中。
通過這種方式我們可以很快的分析出那些要具體修復的表，而整個性能的分析也可以更加清晰。
稍後，把它包裝為一個批量異步任務，通過異步任務來得到儘可能完整的碎片表列表，然後集中去處理就好了。
