mysql optimize 清理碎片

---定期清理脚本
0 1 * * 4 root /root/qingli_mysql.sh
# 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、再来看看优化后的效果
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 | 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会锁定表。