MySQL之OPTIMIZE TABLE应用简介

最新推荐文章于 2025-04-28 22:07:19 发布

C赏心悦目

最新推荐文章于 2025-04-28 22:07:19 发布

阅读量6.7k

点赞数 4

分类专栏：数据库文章标签：数据仓库

原文链接：https://blog.youkuaiyun.com/weixin_39997438/article/details/106972643

版权

数据库专栏收录该内容

25 篇文章

订阅专栏

本文详细介绍了MySQL中删除数据后如何通过OPTIMIZE TABLE进行表优化，包括删除数据的影响、优化的步骤以及优化前后文件大小的对比，强调了在写操作频繁的表上定期执行OPTIMIZE TABLE的重要性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

文章目录
简介
使用场景
STEP1：准备阶段
STEP2：删除操作前查看
STEP3：删除数据
STEP4：查看删除后的文件
STEP5、使用optimize table优化
STEP6、查看优化后的文件
简介
MySQL删除表数据（DELETE）时，MySQL并不会回收这条记录占据的存储空间以及索引位，而是空在那里，等待新的数据来弥补这个空洞。若一时半会没有数据来填补这个空洞，就会形成资源浪费。OPTIMIZE TABLE操作会整理数据文件的碎片，释放多余的空间。所以对于写操作比较频繁的表，需要根据实际情况定期进行optimize。

OPTIMIZE TABLE是由各存储引擎实现的，所以并不是所有存储引擎都可以应用这个操作，目前只有MyISAM、BDB和InnoDB可以实现该功能。且OPTIMIZE TABLE运行过程中，MySQL会锁定表。

-- 语法
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb_name [, tbl_name] ...
-- 通常案例
OPTIMIZE TABLE 表名;

急于应用的读者可以止步于此。

使用场景
1、一张表在上一次OPTIMIZE后已经进行过大量删除操作（DELETE）。
2、一张表在上一次OPTIMIZE后已经对含有可变长度行的表（含有VARCHAR, BLOB或TEXT列的表）进行了很多更改（UPDATE）

在多数的设置中，数据库不需要运行OPTIMIZE TABLE。即使有必要进行该操作也不必频繁执行，按照需求制定执行周期，或每周一次、或每月一次等即可，且只对特定的表执行。

STEP1：准备阶段
创建测试表，并创建100w条数据，将col1字段设置为普通索引，将(col2, col3)设置为组合索引。

建表

DROP TABLE IF EXISTS demo_table;
CREATE TABLE demo_table (
`id` bigint(20) PRIMARY KEY NOT NULL AUTO_INCREMENT,
`col1` varchar(10),
`col2` varchar(36),
`col3` varchar(36)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
ALTER TABLE demo_table ADD INDEX idx_col1(col1);
ALTER TABLE demo_table ADD INDEX idx_col2_col3(col2(20), col3(20));

创建测试数据

-- 使用存储过程创建，本文案例实际创建了100w条数据
DROP PROCEDURE IF EXISTS proc_initData;
DELIMITER $
CREATE PROCEDURE proc_initData()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 1000000 DO
INSERT INTO demo_table(col1,col2,col3) VALUES (i, i div 10, i div 100);
SET i = i + 1;
END WHILE;
END $
CALL proc_initData();
-- 更新一下Cardinality参数（后文提到）
ANALYZE TABLE demo_table;

STEP2：删除操作前查看

查看数据量

select count(1) as total from demo_table;

查看硬盘中表文件大小

# 查看硬盘中表文件大小
[root@centos-7 kkb] ls |grep demo_table |xargs -i du {}
表数据文件大小明细

查看索引信息

show index from demo_table;

表索引明细

字段说明：
Table：表名。
Non_unique：是否唯一索引（0:是、1:否）。
Key_name：索引名。
Seq_in_index：索引中的列序列号，从1开始。
Column_name：索引对应列。
Collation：列在索引中到存储方式。（A:升序、NULL:无分类）。
Cardinality：索引中唯一值的数目的估计值。
Sub_part：如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
Packed：指示关键字如何被压缩。如果没有被压缩，则为NULL。
Null：索引列是否存在空值（YES:存在、(空):不存在）。
Index_type：索引存储数据结构。

Cardinality可以通过运行ANALYZE TABLE或myisamchk -a更新。根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。

STEP3：删除数据

-- 删除一半数据
delete from demo_table where id>500000;

STEP4：查看删除后的文件

我们预计的结果是，删除了表中一半的数据后，该表对应的.MYD和.MYI文件也应缩小为之前的一半。再次查看文件大小：

表数据文件大小

可以发现，结果没有达到我们预期的效果，.MYD、.MYI文件大小似乎一点都没有变化。此时我们顺便查看一下索引信息：

在这里插入图片描述

对比之前的数据，本次索引查询中Cardinality字段倒是如我们预期的，基本上是上次数据量的一半。

STEP5、使用optimize table优化

optimize table demo_table;
在这里插入图片描述

STEP6、查看优化后的文件

查看对应.MYD和.MYI文件文件大小

在这里插入图片描述

可以看到优化后数据文件达到了我们预期的效果！

————————————————
版权声明：本文为优快云博主「薛定谔的码」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.youkuaiyun.com/weixin_39997438/article/details/106972643