MySQL 碎片

查看某个表所占空间，以及碎片大小。

select table_name,engine,table_rows,data_length+index_length length,DATA_FREE from information_schema.tables where TABLE_SCHEMA='test';

或者

select table_name,engine,table_rows,data_length+index_length length,DATA_FREE from information_schema.tables where data_free !=0;

+------------+--------+------------+--------+-----------+
| table_name | engine | table_rows | length | DATA_FREE |
+------------+--------+------------+--------+-----------+
| curs       | InnoDB |          0 |  16384 |         0 |
| t          | InnoDB |         10 |  32768 |         0 |
| t1         | InnoDB |          9 |  32768 |         0 |
| tn         | InnoDB |          7 |  16384 |         0 |
+------------+--------+------------+--------+-----------+

table_name 表的名称
engine ：表的存储引擎
table_rows  表里存在的行数
data_length 表的大小（表数据+索引大小）
DATA_FREE ：表碎片的大小
以上单位都是byte字节

整理碎片：
整理碎片过程会锁边，尽量放在业务低峰期做操作

1、myisam存储引擎清理碎片
　　optimize table 表名,表名;
2、innodb存储引擎清理碎片
　　alter table 表名 engine=innodb;

 

整理碎片：
整理碎片过程会锁边，尽量放在业务低峰期做操作
1.MySQL官方建议不要经常(每小时或每天)进行碎片整理，一般根据实际情况，只需要每周或者每月整理一次即可。
2.OPTIMIZE TABLE运行过程中，MySQL会锁定表。
3.默认情况下，直接对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 "shrink table $t_name ended." >>con_table.log
    sleep 1
else 
     echo "shrink failed!" >> con_table.log
fi

done