mysql中AnalyzeTable優化

Analyze Table

MySQL的Optimizer（優化元件）在優化SQL語句時，首先需要收集一些相關資訊，其中就包括表的cardinality（可以翻譯為“散列程度”），它表示某個索引對應的列包含多少個不同的值——如果cardinality大大少于資料的實際散列程度，那麼索引就基本失效了。

我們可以使用SHOW INDEX語句來檢視索引的散列程度

文法

ANALYZE TABLE 表名1 [,表名2…] ;

SHOW INDEX FROM user;

TABLE   KEY_NAME COLUMN_NAME CARDINALITY

------- -------- ----------- -----------

user   PRIMARY  name     14

因為此時user表中不同的name數量遠遠多于14，索引基本失效。

下面我們通過Analyze Table語句來修複索引：

ANALYZE TABLE user;

SHOW INDEX FROM user;

結果是：

TABLE   KEY_NAME COLUMN_NAME CARDINALITY

------- -------- ----------- -----------

user   PRIMARY  name    1000

此時索引已經修複，查詢效率大大提高。

需要注意的是，如果開啟了binlog，那麼Analyze Table的結果也會寫入binlog，我們可以在analyze和table之間添加關鍵字local取消寫入。

Checksum Table

資料在傳輸時，可能會發生變化，也有可能因為其它原因損壞，為了保證資料的一緻，我們可以計算checksum（校驗值）。

使用MyISAM引擎的表會把checksum存儲起來，稱為live checksum，當資料發生變化時，checksum會相應變化。

在執行Checksum Table時，可以在最後指定選項qiuck或是extended；quick表示傳回存儲的checksum值，而extended會重新計算checksum，如果沒有指定選項，則預設使用extended。

Checksum Table user;

Optimize Table

碎片産生的原因

（1）表的存儲會出現碎片化，每當删除了一行内容該段空間就會變為空白、被留白，而在一段時間内的大量删除操作，會使這種留白的空間變得比存儲清單内容所使用的空間更大；

（2）當執行插入操作時，MySQL會嘗試使用空白空間，但如果某個空白空間一直沒有被大小合适的資料占用，仍然無法将其徹底占用，就形成了碎片；

（3）當MySQL對資料進行掃描時，它掃描的對象實際是清單的容量需求上限，也就是資料被寫入的區域中處于峰值位置的部分；

Optimize Table語句對MyISAM和InnoDB類型的表都有效，但是，OPTILMIZE TABLE語句隻能優化表中的VARCHAR、BLOB或TEXT類型的字段。

如果表經常更新，就應當定期運作Optimize Table語句，保證效率。

1.檢視某個表的碎片

 SHOW TABLE STATUS LIKE 'user';

結果中Data_free列的值就是碎片大小

2.列出所有已經産生碎片的表

select table_schema db, table_name, data_free, engine from information_schema.tables where table_schema not in ('information_schema', 'mysql')  and data_free > 0;      

在MySQL 5.5中,預設情況下所有表共享一個名為ibdata的中央表空間.此表空間中所有表的data_Free将報告相同的數字,即整個表空間中空閑頁面的空間量,而不僅僅是一個表.您還可以為每個表配置設定一個單獨的表空間(innodb_file_per_table = 1),對于單獨表空間中的表,您将看到data_free的每個表的不同值.

與Analyze Table一樣，Optimize Table也可以使用local來取消寫入binlog。

Optimize Table user;

innodb表可能提示：

Table does not support optimize, doing recreate + analyze instead

提示該表不支援 optimize,但是下邊有顯示OK.其實已經執行成功了。5.6.X的版本，其實已經支援Innodb了。

對于InnoDB的表、上面的内容并非報錯、這是MySQL會幫你映射到:alter table table_name engine='InnoDB';

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

建議：清除碎片操作會暫時鎖表，資料量越大，耗費的時間越長，可以做個腳本，定期在通路低谷時間執行，例如每周三淩晨，檢查DATA_FREE字段，大于自己認為的警戒值的話，就清理一次。

下面提供一個腳本參考：

//shell腳本如下:

mysql_user=root
mysql_pass=xxxx
time_log=/opt/database/time
databases=/opt/database/databases
mysql -u$mysql_user -p$mysql_pass -e "show databases" | grep -v "Database" > $databases
sed -i "s/information_schema//" $databases
sed -i "s/mysql//" $databases
sed -i "s/test//" $databases
sed -i "s/performance_schema//" $databases
databases1=$(cat /opt/database/databases)
for i in $databases1
do
echo "database $i staring"
tables=$(mysql $i -u$mysql_user -p$mysql_pass -e "show tables" | grep -v "Tables" > /opt/database/$i)
tablelist=$(cat /opt/database/$i)
echo "optimize database $i starting" >> $time_log
echo "$i start at $(date +[%Y/%m/%d/%H/%M/%S])" >> $time_log
for list in $tablelist
do
echo $list
mysql $i -u$mysql_user -p$mysql_pass -e "alter table $list engine=InnoDB"
done
echo "$i end as $(date +[%Y/%m/%d/%H/%M/%S])" >> $time_log
echo >> $time_log
done      

Check Table

資料庫經常可能遇到錯誤，譬如資料寫入磁盤時發生錯誤，或是索引沒有同步更新，或是資料庫未關閉MySQL就停止了。

遇到這些情況，資料就可能發生錯誤：

Incorrect key file for table: ' '. Try to repair it.

此時，我們可以使用Check Table語句來檢查表及其對應的索引。

譬如我們運作

CHECK TABLE user;

結果是

TABLE      OP   MSG_TYPE MSG_TEXT

-------------- ----- -------- --------

dbname.users check status   OK

MySQL會儲存表最近一次檢查的時間，每次運作check table都會存儲這些資訊：

執行

SELECT   TABLE_NAME, CHECK_TIME

FROM    INFORMATION_SCHEMA.TABLES

WHERE   TABLE_NAME = 'user'

AND     TABLE_SCHEMA = 'dbname';  /*dbname是資料庫名*/

結果是

TABLE_NAME   CHECK_TIME

----------   -------------------

users     2017-04-25 12:44:25

Check Table還可以指定其它選項：

UPGRADE：用來測試在更早版本的MySQL中建立的表是否與目前版本相容。

QUICK：速度最快的選項，在檢查各列的資料時，不會檢查連結（link）的正确與否，如果沒有遇到什麼問題，可以使用這個選項。

FAST：隻檢查表是否正常關閉，如果在系統掉電之後沒有遇到嚴重問題，可以使用這個選項。

CHANGED：隻檢查上次檢查時間之後更新的資料。

MEDIUM：預設的選項，會檢查索引檔案和資料檔案之間的連結正确性。

EXTENDED：最慢的選項，會進行全面的檢查。

Repair Table

用于修複表，隻對MyISAM和ARCHIVE類型的表有效。

這條語句同樣可以指定選項：

QUICK：最快的選項，隻修複索引樹。

EXTENDED：最慢的選項，需要逐行重建索引。

USE_FRM：隻有當MYI檔案丢失時才使用這個選項，全面重建整個索引。