在InnoDB上重建索引 [英] Rebuild index on InnoDB

问题描述

我有200万行和50列的大表。

当我更新/插入大量数据时，我会丢弃所有索引并使用2个查询重新生成它们。这工作正常

I have Large table with 2 million rows and 50 columns.
When iam updating/inserting large amount of data iam dropping all indexes and rebuild them again using 2 queries. this works fine

但是iam想要为
示例使用另一个查询：

But iam thinking to use another query for that example :

ALTER TABLE [table_name]  ENGINE = InnoDB

我从mysql读取 http://dev.mysql.com/doc/refman /5.6/en/optimize-table.html

InnoDB详细信息

InnoDB Details

对于InnoDB表，OPTIMIZE TABLE映射到ALTER TABLE ... FORCE，
，它重建表以更新 x统计信息，并在群集中释放未使用的
空间指数。当你在InnoDB表上运行时，它显示在
OPTIMIZE TABLE的输出中

For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE ... FORCE, which rebuilds the table to update index statistics and free unused space in the clustered index. This is displayed in the output of OPTIMIZE TABLE when you run it on an InnoDB table

此外我认为优化会重建索引？

Also i think optimize will rebuild the index?

 OPTIMIZE TABLE [table_name] 

你推荐什么，我的表有超过200万行和50列

what do you recommend, my table have more then 2 million rows and 50 columns

推荐答案

<你有没有在你的drop + rebuild之前和之后做 SHOW TABLE STATUS ？ Index_length的变化很大吗？可能不是两倍。

Have you been doing SHOW TABLE STATUS before and after your drop+rebuild? Does Index_length change much? Probably not by even a factor of two.

我几乎从不建议在InnoDB中重建任何东西。这不值得。一个明显的例外与 FULLTEXT 索引有关。

I almost never recommend rebuilding anything in InnoDB. It's not worth it. A glaring exception has to do with with FULLTEXT indexes.

是虚拟 ALTER 将重建索引。那么 OPTIMIZE TABLE 。两者都将碎片化（在某种程度上）二级索引BTrees和主要BTree（包含数据和 PRIMARY KEY ）。

Yes the dummy ALTER will rebuild the indexes. So will OPTIMIZE TABLE. Both will "defragment" (to some extent) the secondary index BTrees and the main BTree (which contains the data and PRIMARY KEY).

使用 ANALYZE TABLE ，可以更便宜地更新统计数据 。通常不需要这样做。 5.6有一个更好的方法来维护统计数据。

The statistics can be much more cheaply updated using just ANALYZE TABLE. Even that is not often needed. 5.6 has a much better way of maintaining the stats.

如果你还没有使用 innodb_file_per_table = ON ，我建议您设置（ SET GLOBAL ... ）并执行 ALTER TABLE tbl ENGINE = InnoDB; 最后一次。

If you are not already using innodb_file_per_table=ON, I suggest you set that (SET GLOBAL ...) and do ALTER TABLE tbl ENGINE=InnoDB; one last time.

在线更改

更改 ft _ * ，您需要重建索引。这意味着 ALTER （或 OPTIMIZE ，实现为 ALTER ）。较新版本的MySQL具有 ALGORITHM = INPLACE ，这使得 ALTER 对正在运行的系统几乎没有影响。但是，有一些限制。查看手册。

To change ft_*, you need to rebuild the index. This implies an ALTER (or OPTIMIZE, which is implemented as ALTER). Newer versions of MySQL have ALGORITHM=INPLACE which makes ALTER have little or no impact on the running system. But, there are limitations. Check the manual.

替代非INPLACE ALTER 是 pt-query -digest 或 gh-ost 。看看它们中的任何一个是否适用于你的情况。

An alternative to a non-INPLACE ALTER is pt-query-digest or gh-ost. See if either of them will work for your case.

重建表之后，你可以 DROP INDEX ... 和 ADD INDEX ... 。同样，我不知道这些是否适用于FT索引就地。无论如何，在此过程中您将失去该索引的使用。

Short of "rebuilding the table", you can DROP INDEX ... and ADD INDEX .... Again, I don't know if these work for FT indexes "inplace". Anyway, you would lose the use of that index during the process.

这篇关于在InnoDB上重建索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！