索引的更新mysql_MySQL何时更新索引

When exactly are the indexes updated after an update/insert? Is it before the update/insert query returns, is it sometime after after the query returns, or is it when a query using the indexes is executed.

解决方案

Indexes updates are two-fold.

The first part is inserting/updating/deleting the entry into/from the index. The index gets updated as soon as a record is changed, and this process blocks the query until completion.

This allows faster retrieval of records based on an a condition on an indexed column, the most well-understood purpose of an index.

The second part is updating statistics of the index. This allows the optimiser to determine if for a given query it is even worth using the index. Imagine a query like SELECT * FROM users WHERE disabled = 0. Assume most users are in fact active. If the index statistics are up-to-date, the optimiser will realise that most records from the table will be returned by the query, the table will almost entirely need to be scanned. It will likely decide not to use the index and scan the table straight away.

This update does not take place automatically except in very specific situations. These statistics should be manually updated on a regular basis with an ANALYZE TABLE [table_name]