mysql optimize 清理碎片

---定期清理腳本 

0 1  * * 4 root /root/qingli_mysql.sh 

[root@newmysql5 ~]# cat qingli_mysql.sh 

#!/bin/bash 

date=`date +"%y-%m-%d %h:%m:%s"` 

echo $date >>/root/qingli.log 

tables=$(mysql -u root -p"tina" 2>/dev/null -e "select concat(table_schema,'.',table_name) from information_schema.tables where data_free>0 and engine !='memory';" |grep -v "concat") 

for table in $tables 

do 

  mysql -u root -p"****" 2>/dev/null -e "optimize table $table;" >>/root/qingli.log 

done 

---- 

1、清理mysql碎片 

查詢存在碎片的表和碎片的大小： 

mysql>select concat('optimize table ',table_schema,'.',table_name,';'),data_free,engine from information_schema.tables where data_free>0 and engine !='memory'; 

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

| concat('optimize table ',table_schema,'.',table_name,';') | data_free | engine | 

| optimize table 117demo.scan_url;                          |   5242880 | innodb | 

| optimize table antiyfeature.basic_csv_file;               |   4194304 | innodb | 

| optimize table antiyfeature.gen_avl_entry;                |   4194304 | innodb | 

| optimize table antiyfeature.sample_info;                  |   4194304 | innodb | 

| optimize table avlyun_googleplay.app_package;             |   7340032 | innodb | 

| optimize table avlyun_googleplay.app_update;              | 376438784 | innodb | 

| optimize table enginedn.ads_opc_avl;                      |   4194304 | innodb | 

| optimize table enginedn.avl_info;                         |   4194304 | innodb | 

| optimize table enginedn.basic_avl_info;                   |   4194304 | innodb | 

| optimize table enginedn.basic_csv_file;                   |   4194304 | innodb | 

| optimize table enginedn.gen_change_record;                |   4194304 | innodb | 

| optimize table enginedn.opc_avl_info;                     |   4194304 | innodb | 

| optimize table enginedn.package_channel;                  |   4194304 | innodb | 

| optimize table enginedn.package_info;                     |   4194304 | innodb | 

| optimize table enginedn.scdf_avl_info;                    |   4194304 | innodb | 

| optimize table enginedn.sign_avl_info;                    |   4194304 | innodb | 

| optimize table mobile_event.program_url;                  |   4194304 | innodb | 

| optimize table mobile_event.sample;                       |   4194304 | innodb | 

| optimize table mobile_event.sample_url;                   |   4194304 | innodb | 

| optimize table mobile_event.virus_url;                    |   4194304 | innodb | 

| optimize table mysql.innodb_index_stats;                  |   4194304 | innodb | 

| optimize table sohu.detail_sohu;                          |   7340032 | innodb | 

23 rows in set (0.13 sec)   --共有39個表有碎片，較小的已經提前清理，剩下的需要在空閑時間清理，預計耗時1h30min，可放在淩晨執行~ 

清理步驟： 

執行指令optimize table  table_name; 

注意：optimize table隻對myisam, bdb和innodb表起作用。在optimize table運作過程中，mysql會鎖定表。 

即使您對可變長度的行進行了大量的更新，您也不需要經常運作，每周一次或每月一次即可，隻對特定的表運作。

| optimize table antiy_bbs.bbs_common_session;              |      2492 | memory |    --不支援這種格式 

mysql> optimize table antiy_bbs.bbs_common_session; 

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

| table                        | op       | msg_type | msg_text                                                  | 

| antiy_bbs.bbs_common_session | optimize | note     | the storage engine for the table doesn't support optimize | 

| optimize table sohu.basic_sohu;                           |   3145728 | innodb |   --3m耗時2min47s 

--出現這種，就表示已經清理了 

mysql> optimize table antiy_bbs.bbs_ucenter_newpm; 

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

| table                       | op       | msg_type | msg_text                    | 

| antiy_bbs.bbs_ucenter_newpm | optimize | status   | table is already up to date | 

1 row in set (0.25 sec) 

mysql的文檔說明了，當innodb時，mysql會以alter table去執行這個指令。 是以最終還是會看到 ok 的狀态。 

mysql> optimize table foo; 

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

| table    | op       | msg_type | msg_text                                                          | 

| test.foo | optimize | note     | table does not support optimize, doing recreate + analyze instead | 

| test.foo | optimize | status   | ok                                                                | 

//////////////////////////////////// 

注意：生産上不要随便操作，因為會鎖表。 

mysql> show index from basic_sohu from sohu; 

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

| table      | non_unique | key_name | seq_in_index | column_name | collation | cardinality | sub_part | packed | null | index_type | comment | index_comment | 

| basic_sohu |          0 | primary  |            1 | id          | a         |        8764 |     null | null   |      | btree      |         |               | 

1 row in set (0.00 sec) 

mysql中optimize table的作用 (2009-04-01 17:44:39)轉載▼ 

标簽： 雜談 分類： 工作 

1、先來看看多次删除插入操作後的表索引情況 

mysql> show index from `tbl_name`; 

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

| table | non_unique | key_name | seq_in_index | column_name | collation | cardinality | sub_part | packed | null | index_type | comment | 

| tbl_name | 0 | primary | 1 | stepid | a | 1 | null | null | | btree | | 

| tbl_name | 1 | flowid | 1 | flowid | a | 1 | null | null | | btree | | 

| tbl_name | 1 | wagercount | 1 | wagercount | a | 1 | null | null | | btree | | 

| tbl_name | 1 | wagerid_3 | 1 | wagerid | a | 1 | null | null | | btree | | 

| tbl_name | 1 | wagerid_3 | 2 | steptype | a | 1 | null | null | | btree | | 

| tbl_name | 1 | wagerid_3 | 3 | paramresult | a | 1 | 255 | null | | btree | | 

| tbl_name | 1 | steptype_2 | 1 | steptype | a | 1 | null | null | | btree | | 

| tbl_name | 1 | steptype_2 | 2 | paramresult | a | 1 | 255 | null | | btree | | 

| tbl_name | 1 | wagerid_2 | 1 | wagerid | a | 1 | null | null | | btree | | 

| tbl_name | 1 | wagerid_2 | 2 | steptype | a | 1 | null | null | | btree | | 

| tbl_name | 1 | wagerid_2 | 3 | paramresult | a | 1 | 255 | null | | btree | | 

11 rows in set (0.01 sec) 

2、優化表 

mysql> optimize table tbl_name; 

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

| table | op | msg_type | msg_text | 

| test.tbl_name | optimize | status | ok | 

1 row in set (40.60 sec) 

3、再來看看優化後的效果 

| tbl_name | 0 | primary | 1 | stepid | a | 172462 | null | null | | btree | | 

| tbl_name | 1 | flowid | 1 | flowid | a | 86231 | null | null | | btree | | 

| tbl_name | 1 | wagercount | 1 | wagercount | a | 4311 | null | null | | btree | | 

| tbl_name | 1 | wagerid_3 | 1 | wagerid | a | 86231 | null | null | | btree | | 

| tbl_name | 1 | wagerid_3 | 2 | steptype | a | 172462 | null | null | | btree | | 

| tbl_name | 1 | wagerid_3 | 3 | paramresult | a | 172462 | 255 | null | | btree | | 

| tbl_name | 1 | steptype_2 | 1 | steptype | a | 9 | null | null | | btree | | 

| tbl_name | 1 | steptype_2 | 2 | paramresult | a | 86231 | 255 | null | | btree | | 

| tbl_name | 1 | wagerid_2 | 1 | wagerid | a | 86231 | null | null | | btree | | 

| tbl_name | 1 | wagerid_2 | 2 | steptype | a | 172462 | null | null | | btree | | 

| tbl_name | 1 | wagerid_2 | 3 | paramresult | a | 172462 | 255 | null | | btree | | 

最後，來看看手冊中關于 optimize 的描述： 

optimize [local | no_write_to_binlog] table tbl_name [, tbl_name] ... 

如果您已經删除了表的一大部分，或者如果您已經對含有可變長度行的表（含有varchar, blob或text列的表）進行了很多更改，則應使用 

optimize table。被删除的記錄被保持在連結清單中，後續的insert操作會重新使用舊的記錄位置。您可以使用optimize table來重新 

利用未使用的空間，并整理資料檔案的碎片。 

在多數的設定中，您根本不需要運作optimize table。即使您對可變長度的行進行了大量的更新，您也不需要經常運作，每周一次或每月一次 

即可，隻對特定的表運作。 

optimize table隻對myisam, bdb和innodb表起作用。 

注意，在optimize table運作過程中，mysql會鎖定表。