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会锁定表。