1 碎片是如何产生的

MySQL碎片指的是MySQL数据文件中一些不连续的空白空间，这些空间无法再被全部利用，久而久之越来越多越来越零碎。

MySQL中碎片的产生主要由频繁的删除、更新和插入操作导致：

• 在InnoDB中，删除记录只是将这些行标记为“已删除”，不是真的从索引中物理删除了，磁盘的文件大小不会收缩。InnoDB的Purge线程会异步的来清理这些没用的索引键和行。

  当执行插入操作时，MySQL会尝试使用空白空间，如果插入的数据刚好按照索引排序落在这个区间，可能会复用这个位置，但如果某个空白空间一直没有被大小合适的数据占用，仍然无法将其彻底占用，就形成了碎片；

• 对于大量的UPDATE，也会产生文件碎片化。Innodb的最小物理存储分配单位是页(page)，而UPDATE也可能导致页分裂，频繁的页分裂，页会变得稀疏，并且被不规则的填充，所以最终数据会有碎片。例如原始数据长度varchar(100)，大规模更新数据长度为50，这样的话，有50的空间被空白了，新入库的数据不能完全利用剩余的50，这就会产生碎片。

  【MySQL的几种删除情况】

  1. drop、truncate不管是InnoDB还是MyISAM都立刻释放磁盘空间
  2. delete from table_name删除全部表数据，MyISAM立刻释放磁盘空间，InnoDB不会立刻释放磁盘空间
  3. delete from table_name where xxx;带条件的删除不管是InnoDB还是MyISAM都不会立刻释放
  4. delete from表后虽然未立刻释放磁盘空间，但下次插入的时候仍然可以使用这部分空间

  2 碎片会带来什么问题

  浪费磁盘空间：由于碎片空间是不连续的，导致这些空间不能充分被利用；

  查询性能下降：由于碎片的存在，导致数据库的磁盘 I/O 操作变成离散随机读写，加重了磁盘 I/O 的负担。查询需要扫描的磁盘空间也更大了导致查询速度下降。

  3 如何清理碎片

  3.1 查看表是否存在碎片

  一种方式是查看表的status，如果data_free字段不为0则表示有碎片存在

  show table status like '表名'
  

  

  第二种方式是从information_schema.tables表中查询表的元数据信息：

  SELECT
  	CONCAT( TRUNCATE ( SUM( data_length ) / 1024 / 1024, 2 ), 'MB' ) AS data_size,
  	CONCAT( TRUNCATE ( SUM( data_free ) / 1024 / 1024, 2 ), 'MB' ) AS data_free,
  	CONCAT( TRUNCATE ( SUM( index_length ) / 1024 / 1024, 2 ), 'MB' ) AS index_size 
  FROM
  	information_schema.TABLES 
  WHERE
  	TABLE_NAME = 'tableName'; 
  

  3.1碎片清理方式

  • optimize table table_name

    Optimize语句可以重新组织表、索引的物理存储，减少存储空间，提高访问的IO效率

    【注意】

    1.使用这个语句需要对目标表具有select、insert权限

    2.检查磁盘空间：剩余空间必须 > 被optimize的表大小

    3.Optimize只对独立表空间（innodb_file_per_table=1）才有作用

    4.数据量越大的表，优化耗时越长，百万条数据大约耗时30s(约25000-30000行/秒,此数据根据机器磁盘性能会有差异)。所以，在磁盘优化时，所有的增删操作将受限，请选择一个业务空档期执行。

    • alter table table_name engine=InnoDB

      本质上是recreate，Alter期间支持DML查询和更新操作

      执行过程

      1.获取MDL（meta data lock）写锁，innodb内部创建与原表结构相同的临时文件

      2.拷贝数据前，MDL写锁退化成读锁，支持DML更新操作

      3.根据主键递增顺序，将一行一行的数据读出并写到临时文件，直到全部写入完成，并且拷贝期间的DML更新操作会记录到Row log中

      4.上锁，再将Row log中的数据应用到临时文件

      5.互换原表和临时表的名字

      6.删除临时表