MySQL：索引是否需要整理维护？

Question

每月定期需要对数据进行大量的删除操作，想请教这样操作后对索引是否有影响，是否需要在删除操作完成后对索引进行更新之类的操作

如果需要的话这方面的内容搜索哪些关键字呢？

Answer 1

可以使用OPTIMIZE定期优化表，每个星期或者每个月一次，具体可以参考下面链接
http://dev.mysql.com/doc/refm...

摘自官网的描述

Use OPTIMIZE TABLE in these cases, depending on the type of table:

After doing substantial insert, update, or delete operations on an
InnoDB table that has its own .ibd file because it was created with
the innodb_file_per_table option enabled. The table and indexes are
reorganized, and disk space can be reclaimed for use by the operating
system.

After doing substantial insert, update, or delete operations on
columns that are part of a FULLTEXT index in an InnoDB table. Set the
configuration option innodb_optimize_fulltext_only=1 first. To keep
the index maintenance period to a reasonable time, set the
innodb_ft_num_word_optimize option to specify how many words to update
in the search index, and run a sequence of OPTIMIZE TABLE statements
until the search index is fully updated.

After deleting a large part of a MyISAM or ARCHIVE table, or making
many changes to a MyISAM or ARCHIVE table with variable-length rows
(tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns). Deleted
rows are maintained in a linked list and subsequent INSERT operations
reuse old row positions. You can use OPTIMIZE TABLE to reclaim the
unused space and to defragment the data file. After extensive changes
to a table, this statement may also improve performance of statements
that use the table, sometimes significantly.