原標(biāo)題：計(jì)算MySQL表碎片的SQL整理

這是學(xué)習(xí)筆記的第 2111 篇文章

在之前整理過(guò)一版MySQL的數(shù)據(jù)字典，整理了一圈，發(fā)現(xiàn)遠(yuǎn)比想象的復(fù)雜。

當(dāng)然整理的過(guò)程不光是知識(shí)梳理的過(guò)程，也是轉(zhuǎn)化為實(shí)踐場(chǎng)景的一個(gè)過(guò)程，通過(guò)這樣一個(gè)體系，對(duì)于整個(gè)MySQL對(duì)象生命周期管理有了較為深入的認(rèn)識(shí)，這里我來(lái)拋磚引玉，來(lái)作為深入學(xué)習(xí)MySQL數(shù)據(jù)字典的一個(gè)入口，這個(gè)問(wèn)題就是：如何較為準(zhǔn)確的計(jì)算MySQL碎片情況？

我想碎片的情況在數(shù)據(jù)庫(kù)中是很少有清晰的界定，不過(guò)它的的確確會(huì)帶來(lái)副作用，通過(guò)修復(fù)碎片情況我們可以提高SQL的執(zhí)行效率，同時(shí)能夠釋放大量的空間。

最近在思考中感悟到：我們所做的很多事情，難點(diǎn)主要都在于查找，比如我告訴你test庫(kù)的表test_data存在大量碎片，需要修復(fù)一下，這個(gè)難度是完全可控的，我們可以很麻利的處理好，但是如果我告訴你需要收集下碎片情況，然后做一下改進(jìn)，而不告訴你具體的情況，其實(shí)難度就會(huì)高幾個(gè)層次。

我們這個(gè)場(chǎng)景主要會(huì)用到兩個(gè)數(shù)據(jù)字典表：

information_schema.tables

information_schema.INNODB_SYS_TABLESPACES

我們依次來(lái)看一下兩個(gè)數(shù)據(jù)字典的輸出信息：

查詢常規(guī)的數(shù)據(jù)字典tables得到的信息基本可以滿足我們的大多數(shù)需求。

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命令執(zhí)行記錄表

1 row in set (0.00 sec)

通過(guò)tables字典我們可以得到通過(guò)邏輯計(jì)算出來(lái)的預(yù)估表大小，包括數(shù)據(jù)和索引的空間情況，還有平均行長(zhǎng)度來(lái)作為校驗(yàn)。

但是在這里我們總是會(huì)感覺(jué)有些隔靴搔癢，因?yàn)槲覀兺ㄟ^(guò)計(jì)算得到了邏輯大小，但是我們還是無(wú)從得知物理文件的大小，如果逐個(gè)去通過(guò)du方式計(jì)算，這個(gè)成本是很高的，而且如果有很多的表，這種模式的效率和代價(jià)是不大合理的，所幸MySQL 5.7版本中的innodb_sys_tablespaces這個(gè)數(shù)據(jù)字典做了擴(kuò)容，有了新的字段FILE_SIZE，可以完美的解決我們的疑慮，使用innodb_sys_tablespaces得到的結(jié)果如下：

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)

比如常規(guī)來(lái)說(shuō)我們要得到表tgp_redis_command的物理文件大小(即.ibd文件)，可以通過(guò)INNODB_SYS_TABLESPACES 來(lái)查詢得到，這是一個(gè)緩存中刷新得到的實(shí)時(shí)的值，遠(yuǎn)比我們通過(guò)du等方式計(jì)算要快捷方便許多。

可以做一個(gè)簡(jiǎn)單的計(jì)算，表里的數(shù)據(jù)量為：

mysql> select count(*) from tgp_redis_command;

+----------+

| count(*) |

+----------+

| 478093 |

+----------+

1 row in set (0.06 sec)

物理文件的大小，和innodb_sys_tablespaces的結(jié)果是完全一致的。

# 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

所以表的大小邏輯計(jì)算為data_length+index_length=53035008+0，大約是50M左右，而物理文件大小是60M左右，那么碎片率大約是(60-50)/60約等于16.7%

我們做一下數(shù)據(jù)的truncate操作，發(fā)現(xiàn)物理文件的大小很快收縮了。

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命令執(zhí)行記錄表

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

當(dāng)然這種計(jì)算方式是不夠完整的，而且不夠清晰，我們可以寫一個(gè)簡(jiǎn)單的SQL來(lái)做下統(tǒng)計(jì)，就是把那些需要修復(fù)的表列出來(lái)即可。

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;

以如下的輸出為例，我們可以看到整個(gè)碎片率極高，基本就是邏輯大小為100M，實(shí)際大小為500M，類似這種情況。

其中對(duì)于邏輯大小的計(jì)算做了一些取舍，默認(rèn)在MySQL中變化的數(shù)據(jù)在10%以外是會(huì)重新去統(tǒng)計(jì)計(jì)算的，所以我們可以把基數(shù)調(diào)整的稍大一些為1.1，然后以這個(gè)為基線，如果碎片率超過(guò)了200%則計(jì)入統(tǒng)計(jì)結(jié)果中。

通過(guò)這種方式我們可以很快的分析出那些要具體修復(fù)的表，而整個(gè)性能的分析也可以更加清晰。

稍后，把它包裝為一個(gè)批量異步任務(wù)，通過(guò)異步任務(wù)來(lái)得到盡可能完整的碎片表列表，然后集中去處理就好了。返回搜狐，查看更多

責(zé)任編輯：

總結(jié)

以上是生活随笔為你收集整理的mysql 碎片率_计算MySQL表碎片的SQL整理的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。