MySQL表空间的碎片整理

         当我们对于一个数据量非常大的表进行大量的删除操作后，这个表将会存在非常多的碎片，导致数据库服务器性能下降，这个时候就要求我们对这些表要定期的进行碎片整理以保证数据库的性能。其中一个命令可以帮助我们完成这个——OPTIMIZETABLE [tablename],当然这种方式只适用于多表空间的存储方式。这里以InnoDB的多表空间存储为例（最后附上修改数据库表存储方式的方法）。

1.    首先创建表

       SETFOREIGN_KEY_CHECKS=0;--（不检查外键关联）

       DROP TABLE IF EXISTS `department_info`;
       CREATETABLE `department_info` (
      `departmentid`int(11) NOT NULL,
       `departmentname` varchar(30) NOT NULL,
      `departmentdesc`text
       )ENGINE=InnoDB DEFAULT CHARSET=utf8; 

2.    向表中插入大量数据

       insertinto department_info values(1,'test',repeat('haha',100));
       insertinto department_info values(2,'test',repeat('haha1',100));
       insertinto department_info values(3,'test',repeat('haha1',100));
       insertinto department_info select * from department_info;

       insertinto department_info select * from department_info;

         …………执行多次…………

3.   查看存储的数据文件目录（通过查看mysql安装目录下的my.ini文件可以知道数据存储路径），看到这时对应的数据表信息有多大，然后执行删除操作

执行DELETE FROM department_info再次查看数据存储文件，发现大小没变，这就是引起我们日常说的表碎片的起因了。

执行碎片整理操作，OPTIMIZE TABLE department_info

至此表碎片整理完成。

附：

该变表空间存储方式为多表空间存储方式过程：

关闭Mysql服务，打开MYSQL安装目录，记事本打开my.ini文件，加入语句innodb_per_table=1保存，重启Mysql服务即可（之前建立的表仍按照原来的存储方式存储）

查看表空间存储方式是否为多表空间存储方式：

show variables like "innodb_file_per_table";