小谈 MySQL 第十一话·InnoDB 碎片整理

最新推荐文章于 2023-04-19 18:22:57 发布

if 0 = -I can

最新推荐文章于 2023-04-19 18:22:57 发布

阅读量1.9k

点赞数 4

分类专栏： MySQL 文章标签：碎片整理

本文链接：https://blog.csdn.net/weixin_42018518/article/details/109303800

版权

MySQL 专栏收录该内容

31 篇文章 4 订阅

订阅专栏

官方建议不要经常（每小时或每天）进行碎片整理，一般根据实际情况，只需要每周或每月整理一次即可：

一、MySQL 表碎片化（Table Fragmentation）的原因

二、MySQL 中如何找出碎片化严重的表

1、使用 show table status from db_name like 'tb_name' \G

2、查询 information_schema.TABLES 获取表的碎片化信息

三、MySQL 中如何减低表的碎片

1、OPTIMIZE TABLE XXXX

2、ALTER TABLE XXXX ENGINE = INNODB

问题 01：那么是用 OPTIMIZE TABLE 还是 ALTER TABLE xxxx ENGINE= INNODB 好呢❓

问题 02：ALTER TABLE xxxx ENGINE= INNODB 索引碎片会整理么❓

一、MySQL 表碎片化（Table Fragmentation）的原因

MySQL Engine 不同，碎片化的原因也会有所差别，这里仅以 InnoDB 为主。

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

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

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

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

First at all you must understand that Mysql tables get fragmented when a row is updated, so it's a normal situation. When a table is created, lets say imported using a dump with data, all rows are stored with no fragmentation in many fixed size pages. When you update a variable length row, the page containing this row is divided in two or more pages to store the changes, and these new two (or more) pages contains blank spaces filling the unused space.

表的数据存储也可能碎片化。然而数据存储的碎片化比索引更加复杂。有三种类型的数据碎片化（摘自《高性能 MySQL》）：

① 行碎片（Row fragmentation）

这种碎片指的是数据行被存储为多个地方的多个片段。即使查询只从索引中访问一行记录。行碎片也会导致性能下降。
② 行间碎片（Intra-row fragmentaion）

行间碎片是指逻辑上顺序的页，或者行在磁盘上不是顺序存储的。行间碎片对诸如全表扫描和聚簇索引扫描之类的操作有很大的影响，因为这些操作原本能够从磁盘上顺序存储的数据中获益。
③ 剩余空间碎片（Free space fragmentation）

剩余空间碎片是指数据页中有大量的空余空间。这会导致服务器读取大量不需要的数据。从而造成浪费。

对于 MyISAM 表，这三类碎片化都有可能发生。但 InnoDB 不会出现短小的行碎片，InnoDB 会移动短小的行并重写到一个片段中。

二、MySQL 中如何找出碎片化严重的表

关于 MySQL 中的碎片化，一般有两种方法找出碎片：

1、使用 show table status from db_name like 'tb_name' \G

单个表的碎片化情况查询。如果没有 DML 操作，Data_free 的大小为 0（单位 KB）。

2、查询 information_schema.TABLES 获取表的碎片化信息

mysql> 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 * 1024 ), 2), 'G') TABLE_DATA_SIZE, 
   	CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') TABLE_IDX_SIZE, 
    CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') 'TOTAL SIZE', 
    ROUND(index_length / data_length, 2)  TABLE_IDX_FRAC, 
	CONCAT(ROUND(( data_free / 1024 / 1024 / 1024),2), 'G') AS TABLE_DATA_FREE 
FROM information_schema.TABLES  
WHERE table_name='message_test_wufei';
+---------------------------+--------------+------------+------------+-----------------+----------------+------------+----------------+-----------------+
| TABLE_NAME                | TABLE_ENGINE | TABLE_TYPE | TABLE_ROWS | TABLE_DATA_SIZE | TABLE_IDX_SIZE | TOTAL SIZE | TABLE_IDX_FRAC | TABLE_DATA_FREE |
+---------------------------+--------------+------------+------------+-----------------+----------------+------------+----------------+-----------------+
| testdb.message_test_wufei | InnoDB       | BASE TABLE |   36302847 | 30.62G          | 6.30G          | 36.92G     |           0.21 | 1.05G           |
+---------------------------+--------------+------------+------------+-----------------+----------------+------------+----------------+-----------------+

三、MySQL 中如何减低表的碎片

在 MySQL 中可以使用 OPTIMIZE TABLE XXXX、ALTER TABLE XXXX ENGINE = INNODB 这两种方法降低碎片：

1、OPTIMIZE TABLE XXXX

OPTIMIZE TABLE XXXX 会重组表和物理存储，减少对存储空间使用和提升访问表时的 IO 效率。对每个表所做的确切更改取决于该表使用的存储引擎。

OPTIMIZE TABLE XXXX 的支持表类型：InnoDB、MyISAM、ARCHIVE、NDB；OPTIMIZE操作会暂时锁表，而且数据量越大，耗费的时间就越长。

对于 MyISAM， PTIMIZE TABLE 的工作原理如下：

如果表有已删除的行或拆分行（split rows），修复该表
如果未对索引页面进行排序，对它们进行排序
如果表的统计信息不是最新的（并且无法通过对索引进行排序来完成修复），更新它们

对于 InnoDB，OPTIMIZE TABLE 的工作原理如下：

OPTIMIZE TABLE 映射到 ALTER TABLE ... FORCE（在 InnoDB 表中等价于 ALTER TABLE ... FORCE），它重建表以更新索引统计信息并释放聚簇索引中未使用的空间。当在 InnoDB 表上运行时，它会显示在 OPTIMIZE TABLE 的输出中，如下所示：

mysql> OPTIMIZE TABLE foo;
 
+----------+----------+----------+-------------------------------------------------------------------+
 
| Table    | Op       | Msg_type | Msg_text                                                          |
 
+----------+----------+----------+-------------------------------------------------------------------+
 
| test.foo | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
 
| test.foo | optimize | status   | OK                                                                |
 
+----------+----------+----------+-------------------------------------------------------------------+

提示该表不支持 optimize，但是下边有显示 OK，其实已经执行成功了，5.6.X+ 的版本，其实已经支持Innodb了。

详情可进一步参考官网：https://dev.mysql.com/doc/refman/5.7/en/optimize-table.html

2、ALTER TABLE XXXX ENGINE = INNODB

这其实是一个 NULL 操作，当执行优化操作时，实际执行的是一个空的 ALTER 命令，它会重建整个表，删掉未使用的空表空间。

Running ALTER TABLE tbl_name ENGINE=INNODB on an existing InnoDB table performs a “null” ALTER TABLE operation, which can be used to defragment an InnoDB table, as described in Section 15.11.4, “Defragmenting a Table”. Running ALTER TABLE tbl_name FORCE on an InnoDB table performs the same function.

问题 01：那么是用 OPTIMIZE TABLE 还是 ALTER TABLE xxxx ENGINE= INNODB 好呢❓

对于 InnoDB 存储引擎，ALTER TABLE xxxx ENGINE= INNODB 是执行一个空的 ALTER TABLE 操作；而 OPTIMIZE TABLE 等价于 ALTER TABLE ... FORCE。

有些情况下，OPTIMIZE TABLE 和 ALTER TABLE xxxx ENGINE= INNODB 基本上是一样的。

个人建议：

① MyISAM表
mysql> optimize table 表名;
② InnoDB表
mysql> alter table 表名 engine=InnoDB;

Engine 不同，OPTIMIZE 的操作也不一样的，MyISAM 因为索引和数据是分开的，所以 OPTIMIZE 可以整理数据文件，并重排索引。InnoDB 因为聚簇索引的叶子节点包含行中所有字段的值，ALTER TABLE xxxx ENGINE= INNODB 更适合。

问题 02：ALTER TABLE xxxx ENGINE= INNODB 索引碎片会整理么❓

ALTER TABLE xxxx ENGINE= INNODB 会重新整理在聚簇索引上的数据和索引（重建整个表），如果想验证，可以对比执行该命令前后 index_length 的大小。

3、其它工具

使用 pt 工具或者 gh-ost 降低表的碎片化，后续测试整理，估计也是封装了上面两个命令。

四、实际生产环境的应用

1、对于空表

空表可以在主库执行 ALTER TABLE xxxx ENGINE= INNODB 进行整理，清理整个集群中该空表碎片。

2、对于大表

如果对于一张大表，直接在主库执行 ALTER TABLE xxxx ENGINE= INNODB ，会造成严重的主从同步延迟（清除碎片操作会暂时锁表，数据量越大，耗费的时间越长），影响线上业务流量。

这时，需要不记录 binlog的前提下在主从各节点分别去执行 ALTER TABLE xxxx ENGINE= INNODB 命令。

① 在执行清理碎片命令之前禁止记录 binlog
mysql> show variables like 'sql_log_bin';
mysql> SET sql_log_bin = OFF;
② 清理碎片
mysql> ALTER TABLE xxxx ENGINE = INNODB;
③ 解除 binlog 禁止
mysql> SET sql_log_bin = ON;
mysql> show variables like 'sql_log_bin';

if 0 = -I can

关注

4
点赞
踩
6

收藏

觉得还不错? 一键收藏
5
评论
小谈 MySQL 第十一话·InnoDB 碎片整理

官方建议不要经常（每小时或每天）进行碎片整理，一般根据实际情况，只需要每周或者每月整理一次即可：目录一、MySQL 表碎片化（Table Fragmentation）的原因二、MySQL 中如何找出碎片化严重的表1、使用 show table status from db_name like 'tb_name' \G2、查询 information_schema.TABLES 获取表的碎片化信息三、MySQL 中如何减低表的碎片1、OPTIMIZE TABLE XXXX2、
复制链接

扫一扫