mysql表碎片的查詢自己回收

在MySQL中，我們經(jīng)常會使用VARCHAR、TEXT、BLOB等可變長度的文本數(shù)據(jù)類型。不過，當(dāng)我們使用這些數(shù)據(jù)類型之后，我們就不得不做一些額外的工作——MySQL數(shù)據(jù)表碎片整理。
每當(dāng)MySQL從你的列表中刪除了一行內(nèi)容，該段空間就會被留空。而在一段時(shí)間內(nèi)的大量刪除操作，會使這種留空的空間變得比存儲列表內(nèi)容所使用的空間更大。

當(dāng)MySQL對數(shù)據(jù)進(jìn)行掃描時(shí)，它掃描的對象實(shí)際是列表的容量需求上限，也就是數(shù)據(jù)被寫入的區(qū)域中處于峰值位置的部分。如果進(jìn)行新的插入操作，MySQL將嘗試?yán)眠@些留空的區(qū)域，但仍然無法將其徹底占用。

1.或者查看某個(gè)表所占空間，以及碎片大小。

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  表里存在的行數(shù)
data_length 表的大?。ū頂?shù)據(jù)+索引大?。?/span>
DATA_FREE ：表碎片的大小
以上單位都是byte字節(jié)

整理碎片：
整理碎片過程會鎖邊，盡量放在業(yè)務(wù)低峰期做操作

1、myisam存儲引擎回收碎片
optimize table aaa_safe,aaa_user,t_platform_user,t_user;
2、innodb存儲引擎回收碎片
alter table t engine=innodb;
：
1.MySQL官方建議不要經(jīng)常(每小時(shí)或每天)進(jìn)行碎片整理，一般根據(jù)實(shí)際情況，只需要每周或者每月整理一次即可。
2.OPTIMIZE TABLE運(yùn)行過程中，MySQL會鎖定表。
4.默認(rèn)情況下，直接對InnoDB引擎的數(shù)據(jù)表使用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