• 欢迎访问搞代码网站，推荐使用最新版火狐浏览器和Chrome浏览器访问本网站！
  • 如果您觉得本站非常有看点，那么赶紧使用Ctrl+D 收藏搞代码吧
> 数据库 > mysql > mysql optimize 清算碎片_mysql

mysql optimize 清算碎片_mysql

mysql 搞代码 7年前 (2018-06-07) 114次浏览 已收录 0个评论

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”antiy?918″ 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会锁定表。

欢迎大家阅读《mysql optimize 清算碎片_mysql》,跪求各位点评，by 搞代码

搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]‍，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：mysql optimize 清算碎片_mysql

喜欢 (0)赏
[搞代码]
分享 (0)
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi，您需要填写昵称和邮箱！

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址