MySQL Innodb存储引擎 Table does not support optimize, doing recreate + analyze instead 解决方案

忘是亡心i 2023-09-28 08:45 93阅读 0赞

## 一、背景 ##

电商平台每天几百万的库存流水记录，库存流水记录越来越大，后面所以要定期清理（做delete操作），只保存仅15天的，但是由于库存流水表中有很多变长的字段（varchar），所以采用delete语句删除数据之后，磁盘空间的复用率比较低，因此需要采取`OPTIMIZE TABLE`的方式整理磁盘空间。

执行的SQL如下：

OPTIMIZE TABLE inventory_xxx;

SQL执行完之后，提示信息如下：

> Table does not support optimize, doing recreate + analyze instead

![在这里插入图片描述][e59983b9a8f04ff19c8ccf00403d3ecf.png]

此外：MySQL官方建议不要经常(每小时或每天)进行碎片整理；一般根据实际情况，只需要每周或者每月整理一次。

### 1、OPTIMIZE TABLE的特点 ###

> 1.  OPTIMIZE TABLE只对MyISAM，BDB和InnoDB表起作用，尤其是MyISAM表的作用最为明显。然而，并不是所有表都需要进行碎片整理，一般只需要对包含变长的文本数据类型(varchar)的表进行碎片整理。
> 2.  在OPTIMIZE TABLE运行过程中，MySQL会锁定表。
> 3.  默认情况下，直接对InnoDB引擎的数据表使用OPTIMIZE TABLE，可能会显示「 Table does not support optimize, doing recreate + analyze instead」的提示信息。需要在mysqld启动mysql的时候加上`--skip-new` 或 `--safe-mode`。

### 2、optimize优化原理 ###

对mysql进行大量删改操作时，磁盘上的空间并没有被立即收回(数据空间、索引位)，而是等待新的数据来填充空缺。

*  这些空间可以被后续的`insert`操作重复利用，但如果记录是变长的，则并不是所有的空间都能被重复利用；
 *  因此会形成一个空间碎片，影响磁盘IO

optimize可以把分散(fragmented)存储的数据重新挪到一起(defragmentation)，清除碎片，回收闲置的数据库存储空间。

我们知道optimize操作仅能对MyISAM表、BDB表、InnoDB表进行重建，针对不同的存储引擎，optimize做的事情也不同：

#### 1> InnoDB表 ####

对于InnoDB表，OPTIMIZE TABLE操作会被映射到ALTER TABLE上，重建表。

*  重建操作会 更新索引统计数据 并 释放聚簇索引中 未使用的空间。

#### 2> MyISAM表 ####

对于MyISAM表，OPTIMIZE TABLE按如下方式操作：

> 1.  如果表含有删除的列、分裂的列，则修复表。
> 2.  如果索引页没有排序，则将索引页进行排序。
> 3.  如果表的统计数据没有更新(并且通过对索引进行分类不能实现修复)，则更新。

#### 3> BDB表 ####

对于BDB表，OPTIMIZE TABLE目前被映射到ANALYZE TABLE上。

## 二、解决方案 ##

我们可以用`mysqld --skip-new` 或 `mysqld --safe-mode`命令来重启MySQL，以便于让引擎支持OPTIMIZE TABLE。

![在这里插入图片描述][ef352ff9ef7142c6816eefc4899a3301.png]

再次执行SQL：`optimize table user`，结果如下：  
![在这里插入图片描述][84b87457519246d798f793f592e9d748.png]

## 三、查看表占用的磁盘空间 ##

SQL如下：

SELECT
    	TABLE_NAME,
    	( DATA_LENGTH + INDEX_LENGTH )/ 1048576 AS size_Mb,
    	TABLE_ROWS 
    FROM
    	information_schema.TABLES 
    WHERE
    	TABLE_SCHEMA = 'xx_db' 
    	AND TABLE_NAME = 'xx_table';

示例查询结果：  
![在这里插入图片描述][b027588841a84dd8bc9191675cf229c8.png]

[e59983b9a8f04ff19c8ccf00403d3ecf.png]: https://img-blog.csdnimg.cn/e59983b9a8f04ff19c8ccf00403d3ecf.png
[ef352ff9ef7142c6816eefc4899a3301.png]: https://img-blog.csdnimg.cn/ef352ff9ef7142c6816eefc4899a3301.png
[84b87457519246d798f793f592e9d748.png]: https://img-blog.csdnimg.cn/84b87457519246d798f793f592e9d748.png
[b027588841a84dd8bc9191675cf229c8.png]: https://img-blog.csdnimg.cn/b027588841a84dd8bc9191675cf229c8.png