mysql optimize 清理碎片

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介:
---定期清理脚本 
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会锁定表。
相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
5月前
|
存储 关系型数据库 MySQL
MySQL数据库碎片化:隐患与解决策略
UUID作为主键可能导致MySQL存储碎片,影响性能。频繁的DML操作、字段长度变化和非顺序插入(如UUID)都会造成碎片。碎片增加磁盘I/O,降低查询效率,浪费空间,影响备份速度。建议使用自增ID,固定长度字段,并适时运行OPTIMIZE TABLE来减少碎片。
|
2月前
|
SQL 存储 关系型数据库
MySQL 回收表碎片实践教程
在 MySQL 数据库中,随着数据的增删改操作,表空间可能会出现碎片化,这不仅会占用额外的存储空间,还可能降低表的扫描效率,特别是一些大表,在进行数据清理后会产生大量的碎片。本篇文章我们一起来学习下如何进行碎片回收以及相关注意点。
80 1
MySQL 回收表碎片实践教程
|
2月前
|
SQL 存储 关系型数据库
Mysql主从同步 清理二进制日志的技巧
Mysql主从同步 清理二进制日志的技巧
31 1
|
4月前
|
SQL 关系型数据库 MySQL
MySQL 常见日志清理策略
MySQL 数据库服务器使用多种类型的日志来记录操作和事件,这对于故障诊断、审计和性能分析非常重要。然而,这些日志文件会随着时间的推移而不断增长,可能会占用大量的磁盘空间。因此,定期清理这些日志是必要的,本篇文章我们一起来学习下如何清理 MySQL 中的日志文件。
210 3
|
4月前
|
关系型数据库 MySQL
清理MySQL的binlog日志
清理MySQL的binlog日志
778 0
|
6月前
|
缓存 分布式计算 关系型数据库
数据管理DMS操作报错合集之当进行RDS实例的可用区迁移时,提示“缓存清理”是什么意思
数据管理DMS(Data Management Service)是阿里云提供的数据库管理和运维服务,它支持多种数据库类型,包括RDS、PolarDB、MongoDB等。在使用DMS进行数据库操作时,可能会遇到各种报错情况。以下是一些常见的DMS操作报错及其可能的原因与解决措施的合集。
106 3
|
7月前
|
Prometheus Cloud Native 关系型数据库
实时计算 Flink版产品使用合集之binlog被清理掉的问题,并且binlog有备份,有什么方法来恢复到RDS
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
关系型数据库 MySQL
mysql清理binlog日志
mysql清理binlog日志
115 0
|
SQL 关系型数据库 MySQL
Mysql碎片整理:一些注意事项整理
整理Mysql的一些注意事项整理,不断汇总,可以通过二级标题去筛选,每个二级标题都是独立的。可以在评论区评论留下注意事项,我汇总,积少成多。
179 0
|
存储 SQL 关系型数据库
Mysql碎片整理:存储程序
定义变量;语句结束分隔符;存储函数;存储过程;游标在存储过程和存储函数中的使用;触发器的定义;事件的创建。
104 0