MYSQL表碎片整理

朴灿烈づ我的快乐病毒、 2024-02-18 14:16 82阅读 0赞

1.  清除表碎片
    
    MyISAM表：optimize table 表名
    
    InnoDB表：alter table 表名 engine=InnoDB
    
    清除碎片操作会暂时锁表，数据量越大，耗费的时间越长
    
      
    
2.    
    
3.  一、MYSQL表碎片
4.  \#!/bin/sh 
5.  mysql\_user=root 
6.  mysql\_pass=123123 
7.  time\_log=/opt/**time**
8.  databases=/opt/databases 
9.  /usr/bin/mysql -u$mysql\_user -p$mysql\_pass -e "show databases" | grep -v "Database" > /opt/databases 
10. sed -i "s/information\_schema//" $databases 
11. sed -i "s/mysql//" $databases 
12. sed -i "s/test//" $databases 
13. databases1=$(cat /opt/databases) 
14. **for** i in $databases1 
15. do 
16. echo "database $i starting"
17. tables=$(/usr/bin/mysql $i -u$mysql\_user -p$mysql\_pass -e "show tables" | grep -v "Tables" > /opt/$i) 
18. tablelist=$(cat /opt/$i) 
19. echo "optimize database $i starting" >> $time\_log 
20. echo "$i start at $(date +\[%Y/%m/%d/%H:%M:%S\])" >> $time\_log 
21. **for** list in $tablelist 
22. do 
23. echo $list 
24. /usr/bin/mysql $i -u$mysql\_user -p$mysql\_pass -e "optimize table $list"
25. done 
26. echo "$i end at $(date +\[%Y/%m/%d/%H:%M:%S\])" >> $time\_log 
27. echo >> $time\_log 
28. done

二：说明

经常会使用VARCHAR、TEXT、BLOB等可变长度的文本数据类型。不过，当我们使用这些数据类型之后，我们就不得不做一些额外的工作——MySQL数据表碎片整理。   
每当MySQL从你的列表中删除了一行内容，该段空间就会被留空。而在一段时间内的大量删除操作，会使这种留空的空间变得比存储列表内容所使用的空间更大。

当MySQL对数据进行扫描时，它扫描的对象实际是列表的容量需求上限，也就是数据被写入的区域中处于峰值位置的部分。如果进行新的插入操作，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;  
![29893219_1450071884mhn3.png][]  
  
2、整理表的碎片  
optimize table aaa\_safe,aaa\_user,t\_platform\_user,t\_user;  
![29893219_1450072785AuqG.png][]  
提示该表不支持 optimize,但是下边有显示OK.其实已经执行成功了。5.6.X的版本，其实已经支持Innodb了。  
整理完毕  
![29893219_1450072003S9bN.png][]  
  
针对以前版本：  
大致意思是说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][mysql 1]/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\`

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 ##

1、 存储过程

1.  DELIMITER $$  
    
2.  DROP PROCEDURE IF EXISTS \`mysql\`.\`sp\_optimize\_tables\`$$  
    
3.  CREATE PROCEDURE \`mysql\`.\`sp\_optimize\_tables\`(  
    
4.  IN db\_name varchar(255))  
    
5.  BEGIN  
    
6.  \-- Created by david yeung 20080128.  
    
7.  \-- To optimize all the tables in exact database.  
    
8.  declare cnt int default 0;  
    
9.  declare i int default 0;  
    
10. select count(\*) as total from information\_schema.tables where table\_schema = db\_name into cnt;  
    
11. while i < cnt  
    
12. do  
    
13. \-- Get the table's exact name.  
    
14. 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');  
    
15. prepare s1 from @stmt;  
    
16. execute s1;  
    
17. drop prepare s1;  
    
18. set @stmt = '';  
    
19. set @stmt = concat('optimize table ',db\_name,'.',@tb\_name);  
    
20. prepare s1 from @stmt;  
    
21. execute s1;  
    
22. drop prepare s1;  
    
23. set @stmt = '';  
    
24. set i = i + 1;  
    
25. end while;  
    
26. \-- Refresh tables.  
    
27. flush tables;  
    
28. END$$  
    
29. DELIMITER ;

2、调用示例：

3、在SHELL中调用

1.  \#!/bin/sh  
    
2.  \#  
    
3.  \# Created by david yeung.  
    
4.  \# Optimize all the table one by one.  
    
5.  \#  
    
6.  cd /usr/local/mysql/bin  
    
7.  ./mysql -uoptimize\_user -poptimize -e "use \\"$1\\";call mysql.sp\_optimize\_tables(\\"$1\\")"  
    
8.  不过要注意给optimize\_user的select,insert权限。

2、 碎片整理 的shell 脚本

1.  \#!/bin/sh  
    
2.  mysql\_user=root  
    
3.  mysql\_pass=123123  
    
4.  time\_log=/opt/time  
    
5.  databases=/opt/databases  
    
6.  /usr/bin/mysql -u$mysql\_user -p$mysql\_pass -e "show databases" | grep -v "Database" >/opt/databases  
    
7.  sed -i "s/information\_schema//" $databases  
    
8.  sed -i "s/mysql//" $databases  
    
9.  sed -i "s/test//" $databases  
    
10. databases1=$(cat /opt/databases)  
    
11. for i in $databases1  
    
12. do  
    
13. echo "database $i starting"  
    
14. tables=$(/usr/bin/mysql $i -u$mysql\_user -p$mysql\_pass -e "show tables" | grep -v "Tables" >/opt/$i)  
    
15. tablelist=$(cat /opt/$i)  
    
16. echo "optimize database $i starting" >> $time\_log  
    
17. echo "$i start at $(date +\[%Y/%m/%d/%H:%M:%S\])" >> $time\_log  
    
18. for list in $tablelist  
    
19. do  
    
20. echo $list  
    
21. /usr/bin/mysql $i -u$mysql\_user -p$mysql\_pass -e "optimize table $list"  
    
22. done  
    
23. echo "$i end at $(date +\[%Y/%m/%d/%H:%M:%S\])" >> $time\_log  
    
24. echo >> $time\_log  
    
25. done

[29893219_1450071884mhn3.png]: http://blog.itpub.net/attachment/201512/14/29893219_1450071884mhn3.png
[29893219_1450072785AuqG.png]: http://blog.itpub.net/attachment/201512/14/29893219_1450072785AuqG.png
[29893219_1450072003S9bN.png]: http://blog.itpub.net/attachment/201512/14/29893219_1450072003S9bN.png
[mysql]: http://cpro.baidu.com/cpro/ui/uijs.php?adclass=0&app_id=0&c=news&cf=1001&ch=0&di=128&fv=18&is_app=0&jk=d1c63058484df912&k=mysql&k0=mysql&kdi0=0&luki=9&mcpm=0&n=10&p=baidu&q=97086099_cpr&rb=0&rs=1&seller_id=1&sid=12f94d485830c6d1&ssp2=1&stid=9&t=tpclicked3_hc&td=1691595&tu=u1691595&u=http%3A%2F%2Fwww%2E365mini%2Ecom%2Fpage%2Fmysql%2Doptimize%2Dtable%2Ehtm&urlid=0
[mysql 1]: http://www.2cto.com/database/MySQL/