mysql索引 - MySQL:索引是否需要整理維護?
問題描述
每月定期需要對數據進行大量的刪除操作,想請教這樣操作后對索引是否有影響,是否需要在刪除操作完成后對索引進行更新之類的操作
如果需要的話這方面的內容搜索哪些關鍵字呢?
問題解答
回答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 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.
相關文章:
1. nignx - docker內nginx 80端口被占用2. docker容器呢SSH為什么連不通呢?3. javascript - 連續點擊觸發mouseleave事件4. docker - 各位電腦上有多少個容器啊?容器一多,自己都搞混了,咋辦呢?5. 關于docker下的nginx壓力測試6. 關docker hub上有些鏡像的tag被標記““This image has vulnerabilities””7. mac里的docker如何命令行開啟呢?8. docker gitlab 如何git clone?9. dockerfile - 我用docker build的時候出現下邊問題 麻煩幫我看一下10. angular.js使用$resource服務把數據存入mongodb的問題。

網公網安備