Schema optimization and indexing

When you've created a table, have the right data type, and add an index, your work isn't over yet: you also need to maintain your tables and indexes to make them work better. Table maintenance has three main goals: discovering and resolving table corruption, maintaining accurate index statistics, and reducing storage fragmentation.

Locate and repair a damaged table

The worst thing is that the table has been damaged. For MyISAM, most of them are caused by the machine. However, all storage engines will be corrupted by the index due to hardware problems or MySQL internal bugs or operating system causes.

Corrupted indexes can cause queries to return incorrect results, errors that throw duplicate key values when no duplicate values appear, or cause query deadlocks and machines. If you have a strange behavior-such as an unexpected mistake-check table to see if it is corrupted. Check table can generally check for damage to most tables and indexes.

You can use repair table to fix it. But not all engines support this command. This allows you to use ALTER commands, such as modifying the same storage engine as the table.

mysql> ALTER TABLE innodb_tbl engine=innodb;

You can also use offline repair tools for the storage engine. such as MYISAMCHK or delete data and reload. However, if the change occurs in the system, or if the "row data" in the table replaces the index, there is nothing you can do about it. In this case, you can only recover the table from the backup or recover the data from the corrupted file. Will say in detail later.

Update STATISTICS for indexes

The MySQL query optimizer uses two APIs to learn from the storage engine how the index is distributed when deciding how to use the index. The first one is the Records_in_range call. It passes into the endpoint range and returns the value of the record for the range. The second is info (), which returns different types of data, including the cardinality of the index (how much data is available for each key value).

When the storage engine does not provide the optimizer with accurate information about the number of query rows, the optimizer uses the statistical information of the index. This information you can use analyze table to estimate the number of downlink. The MySQL optimizer is cost-based, and the main consumption factor is how much data the query accesses. If this statistic is not generated, or if they expire, the optimizer may have a bad decision. The scenario is to use the Analyze table to generate the statistical data.

The statistics for each storage engine to generate an index vary, and the frequency at which you use the Analyze table is different, as does the same cost of consumption:

The memory storage engine does not store index statistics.

MyISAM stores statistics on the hard disk, and analyze table performs a full index scan to compute. This process is a lock on the table.

InnoDB does not store statistics on the hard disk. But they are estimated by using random indexes to enter the first open table. For Innodb,analyze table, the random way is used. So the statistical results are imprecise and they don't need to be manually updated unless your server is running for a long time. ANALYZE table also does not return lock consumption is also relatively low. So you can update the statistics online without affecting the normal work.

You can use show index from to view index information.

mysql> SHOW INDEX FROM sakila.actor\G
*************************** 1. row ***************************
Table: actor
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: actor_id
Collation: A
Cardinality: 200
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 2. row ***************************
Table: actor
Non_unique: 1
Key_name: idx_actor_last_name
Seq_in_index: 1
Column_name: last_name
Collation: A
Cardinality: 200
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE

A lot of index information is given. The MySQL documentation is described in detail. What we should pay attention to is the cardinality. This shows how many unique values the storage engine has estimated in the index. You can also MySQL5.0 in the INFORMATION_SCHEMA. It's more convenient to get this information in the statistics table. For example, if you can write a query information_schema statement to find a lower-selectivity index.