優化MySQL myisam、innodb表碎片

針對MyISAM表類型采用 OPTIMIZE TABLE table_name SQL語句清理碎片.

InnoDB 使用的 Clustered Index,索引和資料綁定在一起,重排序是不現實的.是以不支援 MyISAM 式的 OPTIMIZE,而是綁定到了ALTER TABLE 指令上面.可以通過執行以下語句來整理碎片,提高索引速度:

ALTER TABLE table_name ENGINE = Innodb;

這其實是一個NULL操作,表面上看什麼也不做,實際上重新整理碎片了.當執行優化操作時,實際執行的是一個空的 ALTER 指令,但是這個指令也會起到優化的作用,它會重建整個表,删掉未使用的空白空間.

什麼是mysql碎片?怎樣知道表的碎片有多大呢?

簡單的說,删除資料必然會在資料檔案中造成不連續的空白空間,而當插入資料時,這些空白空間則會被利用起來.于是造成了資料的存儲位置不連續,以及實體存儲順序與理論上的排序順序不同,這種是資料碎片.

資料碎片分為兩種,一種是單行資料碎片,另一種是多行資料碎片.

前者的意思就是一行資料,被分成N個片段,存儲在N個位置.後者的就是多行資料并未按照邏輯上的順序排列.

當有大量的删除和插入操作時,必然會産生很多未使用的空白空間,這些空間就是多出來的額外空間.索引也是檔案資料,是以也會産生索引碎片,理由同上,大概就是順序紊亂的問題.

Engine不同,OPTIMIZE 的操作也不一樣的,MyISAM 因為索引和資料是分開的,是以 OPTIMIZE 可以整理資料檔案,并重排索引.

OPTIMIZE 操作會暫時鎖住表,而且資料量越大,耗費的時間也越長,它畢竟不是簡單查詢操作.是以把 Optimize 指令放在程式中是不妥當的,不管設定的命中率多低,當通路量增大的時候,整體命中率也會上升,這樣肯定會對程式的運作效率造成很大影響.比較好的方式就是做個shell,定期檢查mysql中 `information_schema`.`TABLES`字段,檢視 DATA_FREE 字段,大于0話,就表示有碎片.腳本多長時間運作一次,可以根據實際情況來定,比如每周跑一次.

CHECK TABLE 或 REPAIR TABLE,檢查或維護 MyISAM 表

OPTIMIZE TABLE,優化 MyISAM 表

ANALYZE TABLE,分析 MyISAM 表