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.