MySQL之表碎片簡介
- 2019 年 11 月 6 日
- 筆記
information_schema的tables表
對於mysql和Infobright等資料庫，information_schema資料庫中的表都是只讀的，不能進行更新、刪除和插入等操作，也不能加觸發器，因為它們實際只是一個視圖，不是基本表，沒有關聯的文件。MySQL的information_schema.tables存儲了數據表的元數據資訊，它詳細表述了某個表屬於哪個schema，表類型，表引擎，創建時間等資訊。這裡我們首先看看information_schame中的表tables的各個欄位的含義（程式碼可左滑）：
mysql 19:36:08>>desc information_schema.tables; +-----------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+---------------------+------+-----+---------+-------+ | TABLE_CATALOG | varchar(512) | NO | | | | | TABLE_SCHEMA | varchar() | NO | | | | | TABLE_NAME | varchar(64) | NO | | | | | TABLE_TYPE | varchar() | NO | | | | | ENGINE | varchar(64) | YES | | NULL | | | VERSION | bigint() unsigned | YES | | NULL | | | ROW_FORMAT | varchar(20) | YES | | NULL | | | TABLE_ROWS | bigint() unsigned | YES | | NULL | | | AVG_ROW_LENGTH | bigint(21) unsigned | YES | | NULL | | | DATA_LENGTH | bigint() unsigned | YES | | NULL | | | MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | | | INDEX_LENGTH | bigint() unsigned | YES | | NULL | | | DATA_FREE | bigint(21) unsigned | YES | | NULL | | | AUTO_INCREMENT | bigint() unsigned | YES | | NULL | | | CREATE_TIME | datetime | YES | | NULL | | | UPDATE_TIME | datetime | YES | | NULL | | | CHECK_TIME | datetime | YES | | NULL | | | TABLE_COLLATION | varchar() | YES | | NULL | | | CHECKSUM | bigint(21) unsigned | YES | | NULL | | | CREATE_OPTIONS | varchar() | YES | | NULL | | | TABLE_COMMENT | varchar(2048) | NO | | | | +-----------------+---------------------+------+-----+---------+-------+ rows in set (. sec)
其中各個欄位的含義如下：
TABLE_CATALOG
表所述目錄的名稱
TABLE_SCHEMA
表所屬的模式（資料庫）的名稱
TABLE_NAME
表名稱
TABLE_TYPE
表類型
ENGINE
存儲引擎
VERSION
.frm的版本號
ROW_FORMAT
該行的存儲格式（Fixed， Dynamic，Compressed， Redundant，Compact）
TABLE_ROWS
行數。某些存儲引擎（例如 MyISAM）存儲確切的計數。對於其他存儲引擎，例如InnoDB，該值是近似值，並且可以從實際值變化多達40％至50％。在這種情況下，用於SELECT COUNT(*)獲得準確的計數。對於information_schame，table_rows是null
AVG_ROW_LENGTH
平均行長度
DATA_LENGTH
對myisam來講，它是數據文件的長度，以位元組為單位
對於innodb來講，它是聚簇索引分配的大致記憶體量，以位元組為單位。具體來說，它是聚集索引大小（以頁為單位）乘以 InnoDB頁面大小。
MAX_DATA_LENGTH
數據文件的最大長度，在給定數據指針大小的情況下可以存儲在表中的數據的總位元組數。
INDEX_LENGTH
對myisam來講，它是索引文件的長度，以位元組為單位
對innodb來講，它是為非聚簇索引分配的近似記憶體量，以位元組為單位，它是非聚集索引大小（以頁為單位）乘以InnoDB頁面大小的總和
DATA_FREE
已分配但未使用的位元組數。往往用來評估表碎片
AUTO_INCREMENT
下一個AUTO_INCREMENT值。
CREATE_TIME
創建表的時間
UPDATE_TIME
上次更新數據文件時。對於某些存儲引擎，此值為NULL。例如， InnoDB在其系統表空間中存儲多個表， 並且數據文件時間戳不適用。即使 文件每次表模式與每個InnoDB在單獨的表 .ibd文件， 改變緩衝 可以延緩寫入到數據文件，因此，文件的修改時間是從最後插入的時間不同，更新或刪除。對於MyISAM，使用數據文件時間戳;
CHECK_TIME
上次檢查表的時間
TABLE_COLLATION
表的默認排序規則，也稱之為字元校驗編碼集
CHECKSUM
實時校驗和值
CREATE_OPTIONS
顯示 partitioned表是否已分區。它還顯示了ENCRYPTION在創建或更改每個表文件表空間時使用的選項。
TABLE_COMMENT
創建表時使用的注釋（或有關MySQL無法訪問表資訊的資訊）
表碎片整理
上面tables表中提到的data_free欄位，就是表碎片的一個指標，當我們發現了表存在碎片時，有兩種方法進行整理優化：
第一種是MySQL自身的優化：MySQL對數據進行掃描時，它掃描的對象實際是列表的容量需求上限，也就是數據被寫入的區域中處於峰值位置的部分。如果在一個碎片率很高的表進行新的插入操作，MySQL將嘗試利用那些留空的區域，但是由於插入數據的不確定性，這些留空的記憶體區域仍然無法被徹底佔用。
第二種是人為干預的優化：使用optimize語法進行優化，即
Optimize table tbl_name
