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

网友投稿 1511 2022-09-02

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

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

一、背景

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

执行的SQL如下:

OPTIMIZE TABLE inventory_xxx;

SQL执行完之后,提示信息如下:

Table does not support optimize, doing recreate + analyze instead

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

1、OPTIMIZE TABLE的特点

OPTIMIZE TABLE只对MyISAM,BDB和InnoDB表起作用,尤其是MyISAM表的作用最为明显。然而,并不是所有表都需要进行碎片整理,一般只需要对包含变长的文本数据类型(varchar)的表进行碎片整理。在OPTIMIZE TABLE运行过程中,MySQL会锁定表。默认情况下,直接对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按如下方式操作:

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

3> BDB表

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

二、解决方案

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

再次执行SQL:​​optimize table user​​,结果如下:

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

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';

示例查询结果:

版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。

上一篇:HDU 2829 Lawrence(DP+四边形不等式优化)
下一篇:流行的前后端分离,咱们来看看它的优缺点!(为什么要前后端分离?有什么优缺点?)
相关文章

 发表评论

暂时没有评论,来抢沙发吧~