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

问题描述

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

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

问题解答

可以使用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 anInnoDB table that has its own .ibd file because it was created withthe innodb_file_per_table option enabled. The table and indexes arereorganized, and disk space can be reclaimed for use by the operatingsystem.

After doing substantial insert, update, or delete operations oncolumns that are part of a FULLTEXT index in an InnoDB table. Set theconfiguration option innodb_optimize_fulltext_only=1 first. To keepthe index maintenance period to a reasonable time, set theinnodb_ft_num_word_optimize option to specify how many words to updatein the search index, and run a sequence of OPTIMIZE TABLE statementsuntil the search index is fully updated.

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