MySql表碎片管理

碎片原因

Random insertions into or deletions from a secondary index can cause the index to become fragmented. Fragmentation means that the physical ordering of the index pages on the disk is not close to the index ordering of the records on the pages, or that there are many unused pages in the 64-page blocks that were allocated to the index.

随机插入和删除将导致碎片

InnoDB表的数据存储在页（page）中，每个页可以存放多条记录。这些记录以树形结构组织，这颗树称为B+树索引。表中数据和辅助索引都是使用B+树结构。维护表中所有数据的这颗B+树索引称为聚簇索引，通过主键来组织的。聚簇索引的叶子节点包含行中所有字段的值，辅助索引的叶子节点包含索引列和主键列。

在InnoDB中，删除一些行，这些行只是被标记为“已删除”，而不是真的从索引中物理删除了，因而空间也没有真的被释放回收。InnoDB的Purge线程会异步的来清理这些没用的索引键和行。但是依然没有把这些释放出来的空间还给操作系统重新使用，因而会导致页面中存在很多空洞。如果表结构中包含动态长度字段，那么这些空洞甚至可能不能被InnoDB重新用来存新的行，因为空间空间长度不足。

另外，删除数据就会导致页（page）中出现空白空间，大量随机的DELETE操作，必然会在数据文件中造成不连续的空白空间。而当插入数据时,这些空白空间则会被利用起来.于是造成了数据的存储位置不连续。物理存储顺序与逻辑上的排序顺序不同,这种就是数据碎片。

对于大量的UPDATE，也会产生文件碎片化 ， Innodb的最小物理存储分配单位是页(page)，而UPDATE也可能导致页分裂（page split），频繁的页分裂，页会变得稀疏，并且被不规则的填充，所以最终数据会有碎片。

碎片查询

SELECT
  CONCAT(table_schema, '.', table_name) AS TABLE_NAME,
  engine AS TABLE_ENGINE,
  table_type AS TABLE_TYPE,
  table_rows AS TABLE_ROWS,
  CONCAT(ROUND(data_length / (1024 * 1024), 2), 'M') AS TB_DATA_SIZE,
  CONCAT(ROUND(index_length / (1024 * 1024), 2), 'M') AS TB_IDX_SIZE,
  CONCAT(
    ROUND((data_length + index_length) / (1024 * 1024), 2),
    'M'
  ) AS TOTAL_SIZE,
  CASE
    WHEN data_length = 0 THEN 0
    ELSE ROUND(index_length / data_length, 2)
  END AS TB_INDX_RATE,
  CONCAT(ROUND(data_free / 1024 / 1024, 2), 'MB') AS TB_DATA_FREE,
  CASE
    WHEN (data_length + index_length) = 0 THEN 0
    ELSE ROUND(data_free / (data_length + index_length), 2)
  END AS TB_FRAG_RATE
FROM
  information_schema.TABLES
ORDER BY
  data_free DESC;

碎片清理

ALTER TABLE tbl_name ENGINE=INNODB

ANALYZE TABLE tbl_name

参考链接

MySQL :: MySQL 8.0 Reference Manual :: 15.11.4 Defragmenting a Table

MySQL :: MySQL 8.0 Reference Manual :: 13.7.3.1 ANALYZE TABLE Statement