High-performance MySQL (5) Indexing strategy-maintenance of indexes and tables

The maintenance table has three main purposes:

1, find and repair the damaged table.

For MyISAM storage engines, table corruption is usually caused by a system crash. Other engines can also cause damage to the index due to hardware problems, MySQL's own flaws, or operating system problems.

Corrupted indexes can cause queries to return incorrect results or bogus primary key conflicts, and can cause database crashes when they are serious.

In such cases, you can try a check table to check for table corruption and some storage engines do not support this command.

You can use the repair table to repair a corrupted table, but also not all engines support the command.

If the engine is not supported, you can use the alter operation to rebuild the table, or export the data and then re-import it.

InnoDB is generally not corrupted, and if there is a corruption, it is either a database hardware issue, such as a memory or disk problem, or a database administrator operation.

Common errors are caused by attempts to use Rsync backup InnoDB.

If it is a query, it must be a bug, not a query problem.

2, maintain accurate index statistics information.

The MySQL query optimizer uses 2 APIs to understand the distribution of index values for the storage engine to determine how to use the index.

The first API is Records_in_range (), which gets the number of records in this range by passing in two boundary values to the storage engine.

For some engines, the interface returns an exact value, such as MyISAM, and an estimated value for InnoDB.

The second API is info (), which returns various types of data, including the cardinality of the index.

When the return information is inaccurate, the optimizer uses index statistics to estimate the number of scanned rows. If the table has no statistics or the statistics are inaccurate, the optimizer is likely to make the wrong decision.

You can run analyze table to regenerate statistics.

Memory engine does not store index statistics

MyISAM index statistics are stored on disk, analyze table requires a full table scan and the entire process requires a lock table.

After MySQL5.5, InnoDB does not store index statistics on disk, but is evaluated and stored in memory through random index access.

Use the show index from command to view the index cardinality (cardinality)

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/

InnoDB will be the first to open the table, or to execute analyze table, or the table size changes more than 1/16 or Show table status, or show index time will calculate the statistical information, if the server has a large number of data, this is a serious problem, As soon as show index looks at indexing statistics, the statistics update is triggered and you can close

Innodb_stats_on_metadata parameter to close.

Once you turn off automatic Updates, you need to periodically use analyze table to manually update them, otherwise the problem is big.

3. Reduce index and data fragmentation

B-tree indexes can be fragmented, which can reduce query efficiency. Fragmented indexes may be stored on disk in poor or unordered ways.

You can rearrange the data by optimize table or by exporting and then importing it.

For a storage engine that does not support optimize table, you can rebuild the table with an ALTER table that does nothing.

Altertable<table> engine=<engine>;

You can also delete the index, rebuild the table, and then re-create the index to implement it.

The introduction of the index to the first here, tomorrow into the Query Performance optimization section!

This article comes from "phper-every day a little ~" blog, please be sure to keep this source http://janephp.blog.51cto.com/4439680/1314057