20.MySQL整改-表碎片整理

一、表碎片处理的背景

         当表中存在很多碎片的时候，会导致扫描的行数变多，使得查询性能变差。所以当表中存在过多的碎片的时候，需要及时对碎片进行清理。

二、表碎片检查

1.检查哪些表存在的碎片

select table_schema '库名',

table_name '表名',

truncate(data_length/1024/1024,2) '数据空间(MB)',

truncate(index_length/1024/1024,2) '索引空间(MB)',

truncate(data_free/1024/1024,2) '碎片空间(MB)',

concat(truncate(data_free/(data_length+ index_length+data_free)*100,2),'%') '碎片率',

table_rows '行数',

truncate((data_length+index_length+data_free)/1024/1024,2) '总空间(MB)'  

from information_schema.tables

where table_schema not in  ('mysql','sys','information_schema','performance_schema')

and data_length is not null

order by (data_length+index_length+data_free)

desc limit 10;

 

 

2.消除碎片

mysql> alter table   testdb.itpux_obj  engine=innodb;        

Query OK, 0 rows affected (0.94 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table   testdb.itpux_m5   engine=innodb;

Query OK, 0 rows affected (0.76 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table  testdb.hj_sales engine=innodb;

Query OK, 0 rows affected (2.18 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

可以看到表的碎片空间，明显减少，由原来的4M，减少到2M。本地是测试环境，数据量较少，不够明显。当数据量很大的时候，可以明显的看到碎片减少，并能提升性能。

3.查看执行计划

explain select * from testdb.hj_sales where id=10;

 

可以看到用到了主键，返回1行，执行计划中用到了const; 说明整理碎片对执行计划没有什么影响。