mysql optimize 清理碎片

  1. 云栖社区>
  2. 博客>
  3. 正文

mysql optimize 清理碎片

转身泪倾城 2016-05-12 17:43:04 浏览2106
展开阅读全文
---定期清理脚本 
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、再来看看优化后的效果 
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会锁定表。

网友评论

登录后评论
0/500
评论
转身泪倾城
+ 关注