MYSQL 毛病那么多，optimize table 为什么做不了

AustinDatabases

发布于 2024-04-18 13:09:23

1000

发布于 2024-04-18 13:09:23

MySQL 在数据增长中，会遇到一个问题数据在清理后，无法将数据表空间回收，大多数的人员在处理这个问题的时候，可以通过optimize table 的方案来解决.

 CREATE TABLE `test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `empid` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

 optimize table test.test;
+-----------+----------+----------+-------------------------------------------------------------------+
| Table     | Op       | Msg_type | Msg_text                                                          |
+-----------+----------+----------+-------------------------------------------------------------------+
| test.test | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.test | optimize | status   | OK                                                                |
+-----------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.03 sec)

但在某些情况下，optimize table 的操作会遇到失败的情况，而引起这个问题的主要原因是，数据表中有唯一索引，而具备唯一索引的表，正在出入重复的数据时，导致的optimize table 的执行错误。

通过存储过程我们插入数据，在此同时我们写另一个存储过程不断的往test表中插入重复的数据，持续的插入，然后我们在另一个连接中，持续的运行optimize table。

mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+----------+----------+-------------------------------------------------------------------+
| Table     | Op       | Msg_type | Msg_text                                                          |
+-----------+----------+----------+-------------------------------------------------------------------+
| test.test | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.test | optimize | error    | Duplicate entry '100' for key 'test.name'                         |
| test.test | optimize | status   | Operation failed                                                  |
+-----------+----------+----------+-------------------------------------------------------------------+


mysql> delimiter //
mysql> CREATE PROCEDURE dotest()
    -> BEGIN
    -> DECLARE i INT DEFAULT 1;
    -> WHILE (i <= 2000000) DO
    -> INSERT INTO `test` (id, name, empid) values (i, i, i);
    -> SET i = i+1;
    -> END WHILE;
    -> END
    -> //
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> call dotest();

上图中可以看到在optimize table 的时候，有报错信息，其中在操作失败前面，会提示Duplicate entry 操作，然后operation failed。

简单解释是因为，在optimize table 操作时会对唯一索引进行重新的整理，并且重新生成索引会对数据进行检查，当插入重复数据的时候，无法满足唯一约束条件，而导致OT操作失败。

运行DDL操作时，运行ALTER TABLE或optimize table语句的线程会应用来自其他连接线程并发运行在相同表上的DML操作的在线日志。当应用DML操作时，可能会遇到重复键条目错误（ERROR 1062 (23000)：Duplicate entry），即使重复条目只是临时的，并且稍后会被在线日志中的另一个条目回滚。这类似于InnoDB中的外键约束检查的概念，约束必须在事务期间保持。

同样更换了 alter table engine=innodb; 产生的结果是一致的，都是无法进行。