-
清除表碎片
MyISAM表:optimize table 表名
InnoDB表:alter table 表名 engine=InnoDB
清除碎片操作会暂时锁表,数据量越大,耗费的时间越长
-
- 一、MYSQL表碎片
- #!/bin/sh
- mysql_user=root
- mysql_pass=123123
- time_log=/opt/time
- databases=/opt/databases
- /usr/bin/mysql -u$mysql_user -p$mysql_pass -e "show databases" | grep -v "Database" > /opt/databases
- sed -i "s/information_schema//" $databases
- sed -i "s/mysql//" $databases
- sed -i "s/test//" $databases
- databases1=$(cat /opt/databases)
- for i in $databases1
- do
- echo "database $i starting"
- tables=$(/usr/bin/mysql $i -u$mysql_user -p$mysql_pass -e "show tables" | grep -v "Tables" > /opt/$i)
- tablelist=$(cat /opt/$i)
- echo "optimize database $i starting" >> $time_log
- echo "$i start at $(date +[%Y/%m/%d/%H:%M:%S])" >> $time_log
- for list in $tablelist
- do
- echo $list
- /usr/bin/mysql $i -u$mysql_user -p$mysql_pass -e "optimize table $list"
- done
- echo "$i end at $(date +[%Y/%m/%d/%H:%M:%S])" >> $time_log
- echo >> $time_log
- done
每当MySQL从你的列表中删除了一行内容,该段空间就会被留空。而在一段时间内的大量删除操作,会使这种留空的空间变得比存储列表内容所使用的空间更大。
1.或者查看某个表所占空间,以及碎片大小。
select table_name,engine,table_rows,data_length+index_length length,DATA_FREE from information_schema.tables where TABLE_SCHEMA='db002';
或者
select table_name,engine,table_rows,data_length+index_length length,DATA_FREE from information_schema.tables where TABLE_SCHEMA='db002' and data_free !=0;
2、整理表的碎片
optimize table aaa_safe,aaa_user,t_platform_user,t_user;
提示该表不支持 optimize,但是下边有显示OK.其实已经执行成功了。5.6.X的版本,其实已经支持Innodb了。
整理完毕
针对以前版本:
大致意思是说innodb的数据库不支持optimize,可以用
ALTER TABLE table.name ENGINE='InnoDB';
该方法会对旧表以复制的方式新建一个新表,然后删除旧表。虽然这个过程是安全的,但是在进行操作时还是先进行备份为好
也可以在启动数据库的时候指定--skip-new或者--safe-mode选项来支持optimize功能
[root@wg ~]# service mysql start --skip-new
OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。
对于MyISAM表,OPTIMIZE TABLE按如下方式操作:
1. 如果表已经删除或分解了行,则修复表。
2. 如果未对索引页进行分类,则进行分类。
3. 如果表的统计数据没有更新(并且通过对索引进行分类不能实现修复),则进行更新。
对于BDB表,OPTIMIZE TABLE目前被映射到ANALYZE TABLE上。
对于InnoDB表,OPTIMIZE TABLE被映射到ALTER TABLE上,这会重建表。重建操作能更新索引统计数据并释放成簇索引中的未使用的空间。
备注 :
1.MySQL官方建议不要经常(每小时或每天)进行碎片整理,一般根据实际情况,只需要每周或者每月整理一次即可。
2. OPTIMIZE TABLE 只对MyISAM,BDB和InnoDB表起作用,尤其是MyISAM表的作用最为明显。此外,并不是所有表都需要进行碎片整理,一般只需要对包含上述可变长度的文本数据类型的表进行整理即可。
3.在 OPTIMIZE TABLE 运行过程中,MySQL会锁定表。
4.默认情况下,直接对InnoDB引擎的数据表使用 OPTIMIZE TABLE ,可能会显示「 Table does not support optimize, doing recreate + analyze instead」的提示信息。这个时候,我们可以用 mysqld --skip-new 或者 mysql d --safe-mode 命令来重启MySQL,以便于让其他引擎支持 OPTIMIZE TABLE 。
整理碎片过程会锁边,尽量放在业务低峰期做操作
1、myisam存储引擎回收碎片optimize table aaa_safe,aaa_user,t_platform_user,t_user;
2、innodb存储引擎回收碎片alter table t engine=innodb;
1.MySQL官方建议不要经常(每小时或每天)进行碎片整理,一般根据实际情况,只需要每周或者每月整理一次即可。
2.OPTIMIZE TABLE运行过程中,MySQL会锁定表。4.默认情况下,直接对InnoDB引擎的数据表使用OPTIMIZE TABLE或
脚本回收innodb表碎片
#!/bin/bash
DB=test
USER=root
PASSWD=root123
HOST=192.168.2.202
MYSQL_BIN=/usr/local/mysql/bin
D_ENGINE=InnoDB
$MYSQL_BIN/mysql -h$HOST -u$USER -p$PASSWD $DB -e "select TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA='"$DB"' "';" | grep -v "TABLE_NAME" >tables.txt
for t_name in `cat tables.txt`
do
echo "Starting table $t_name......"
sleep 1
$MYSQL_BIN/mysql -h$HOST -u$USER -p$PASSWD $DB -e "alter table $t_name engine='"$D_ENGINE"'"
if [ $? -eq 0 ]
then
echo "shrinktable $t_name ended." >>con_table.log
sleep 1
else
echo "shrinkfailed!" >> con_table.log
fi
done
三、mysql 碎片整理存储过程与shell
- DELIMITER $$
- DROP PROCEDURE IF EXISTS `mysql`.`sp_optimize_tables`$$
- CREATE PROCEDURE `mysql`.`sp_optimize_tables`(
- IN db_name varchar(255))
- BEGIN
- -- Created by david yeung 20080128.
- -- To optimize all the tables in exact database.
- declare cnt int default 0;
- declare i int default 0;
- select count(*) as total from information_schema.tables where table_schema = db_name into cnt;
- while i < cnt
- do
- -- Get the table's exact name.
- set @stmt = concat('select table_name from information_schema.tables where table_schema = ''',db_name,''' order by table_name asc limit ',i,',1 into @tb_name');
- prepare s1 from @stmt;
- execute s1;
- drop prepare s1;
- set @stmt = '';
- set @stmt = concat('optimize table ',db_name,'.',@tb_name);
- prepare s1 from @stmt;
- execute s1;
- drop prepare s1;
- set @stmt = '';
- set i = i + 1;
- end while;
- -- Refresh tables.
- flush tables;
- END$$
- DELIMITER ;
- mysql> use mysql
- Database changed
- mysql> call sp_optimize_tables('david_test');
- +------------------------------+----------+----------+----------+
- | Table | Op | Msg_type | Msg_text |
- +------------------------------+----------+----------+----------+
- | david_test.test1 | optimize | status | OK |
- +------------------------------+----------+----------+----------+
- 1 row in set (0.26 sec)
- #!/bin/sh
- #
- # Created by david yeung.
- # Optimize all the table one by one.
- #
- cd /usr/local/mysql/bin
- ./mysql -uoptimize_user -poptimize -e "use \"$1\";call mysql.sp_optimize_tables(\"$1\")"
- 不过要注意给optimize_user的select,insert权限。
- #!/bin/sh
- mysql_user=root
- mysql_pass=123123
- time_log=/opt/time
- databases=/opt/databases
- /usr/bin/mysql -u$mysql_user -p$mysql_pass -e "show databases" | grep -v "Database" >/opt/databases
- sed -i "s/information_schema//" $databases
- sed -i "s/mysql//" $databases
- sed -i "s/test//" $databases
- databases1=$(cat /opt/databases)
- for i in $databases1
- do
- echo "database $i starting"
- tables=$(/usr/bin/mysql $i -u$mysql_user -p$mysql_pass -e "show tables" | grep -v "Tables" >/opt/$i)
- tablelist=$(cat /opt/$i)
- echo "optimize database $i starting" >> $time_log
- echo "$i start at $(date +[%Y/%m/%d/%H:%M:%S])" >> $time_log
- for list in $tablelist
- do
- echo $list
- /usr/bin/mysql $i -u$mysql_user -p$mysql_pass -e "optimize table $list"
- done
- echo "$i end at $(date +[%Y/%m/%d/%H:%M:%S])" >> $time_log
- echo >> $time_log
- done