MySQL “表不支持优化，而是进行重新创建+分析”是什么意思？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30635603/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What does "Table does not support optimize, doing recreate + analyze instead" mean?
提问by Vikrant More
I am working on MySQL 5.5 and trying to do index rebuild using an OPTIMIZE TABLEquery. I am getting the error below:
我正在使用 MySQL 5.5 并尝试使用OPTIMIZE TABLE查询进行索引重建。我收到以下错误：
Table does not support optimize, doing recreate + analyze instead
表不支持优化，改为重新创建+分析
What does this mean? Is MySQL engine not allowing Index Rebuild? What is being done behind this message, at MySQL 5.5 Engine level?
这是什么意思？MySQL 引擎是否不允许索引重建？在 MySQL 5.5 引擎级别，此消息背后正在做什么？
回答by spencer7593
That's really an informational message.
这确实是一条信息性消息。
Likely, you're doing OPTIMIZE on an InnoDBtable (table using the InnoDB storage engine, rather than the MyISAMstorage engine).
很可能，您正在InnoDB表（使用 InnoDB 存储引擎，而不是MyISAM存储引擎的表）上执行 OPTIMIZE 。
InnoDB doesn't support the OPTIMIZE the way MyISAM does. It does something different. It creates an empty table, and copies all of the rows from the existing table into it, and essentially deletes the old table and renames the new table, and then runs an ANALYZE to gather statistics. That's the closest that InnoDB can get to doing an OPTIMIZE.
InnoDB 不像 MyISAM 那样支持 OPTIMIZE。它做了一些不同的事情。它创建一个空表，并将现有表中的所有行复制到其中，实质上是删除旧表并重命名新表，然后运行 ANALYZE 以收集统计信息。这是 InnoDB 最接近优化的方法。
The message you are getting is basically MySQL server repeating what the InnoDB storage engine told MySQL server:
您收到的消息基本上是 MySQL 服务器重复 InnoDB 存储引擎告诉 MySQL 服务器的内容：
Table does not support optimizeis the InnoDB storage engine saying...
表不支持优化是 InnoDB 存储引擎说的...
"I (the InnoDB storage engine) don't do an OPTIMIZE operation like my friend (the MyISAM storage engine) does."
“我（InnoDB 存储引擎）不像我的朋友（MyISAM 存储引擎）那样做 OPTIMIZE 操作。”
"doing recreate + analyze instead"is the InnoDB storage engine saying...
“进行重新创建 + 分析”是 InnoDB 存储引擎所说的......
"I have decided to perform a differentset of operations which will achieve an equivalent result."
“我已决定执行一组不同的操作，以实现相同的结果。”
回答by sdesvergez
OPTIMIZE TABLEworks fine with InnoDB engine according to the official support article : http://dev.mysql.com/doc/refman/5.5/en/optimize-table.html
OPTIMIZE TABLE根据官方支持文章，InnoDB 引擎工作正常：http: //dev.mysql.com/doc/refman/5.5/en/optimize-table.html
You'll notice that optimize InnoDB tables will rebuild table structure and update index statistics (something like ALTER TABLE).
您会注意到优化 InnoDB 表将重建表结构并更新索引统计信息（类似于ALTER TABLE）。
Keep in mind that this message could be an informational mention only and the very important information is the status of your query : just OK !
请记住，此消息可能只是一个信息提及，非常重要的信息是您的查询状态：就可以了！
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 |
+----------+----------+----------+-------------------------------------------------------------------+
回答by tquang
Best option is create new table with same properties
最好的选择是创建具有相同属性的新表
CREATE TABLE <NEW.NAME.TABLE> LIKE <TABLE.CRASHED>;
INSERT INTO <NEW.NAME.TABLE> SELECT * FROM <TABLE.CRASHED>;
Rename NEW.NAME.TABLE and TABLE.CRASH
重命名 NEW.NAME.TABLE 和 TABLE.CRASH
RENAME TABLE <TABLE.CRASHED> TO <TABLE.CRASHED.BACKUP>;
RENAME TABLE <NEW.NAME.TABLE> TO <TABLE.CRASHED>;
After work well, delete
工作正常后，删除
DROP TABLE <TABLE.CRASHED.BACKUP>;
回答by ASHOK MANGHAT
The better option is create a new table copy the rows to the destination table, drop the actual table and rename the newly created table . This method is good for small tables,
更好的选择是创建一个新表，将行复制到目标表，删除实际表并重命名新创建的表。这种方法适合小桌子，
