How to maintain MySQL indexes and data tables

Find and fix table conflicts

The worst thing that can happen to a data table is a conflict. When using the MyISAM storage engine, conflicts are usually caused by crashes. However, all storage engines can suffer from index conflicts when there is a hardware failure, a MySQL internal bug, or an operating system bug.

Conflicting indexes can cause queries to return incorrect results, increase duplicate index errors when there are no duplicate values, and may even cause full table scans or crashes. If you encounter an occasional event, such as an error that you think will not occur, run the CHECK TABLE command to detect whether there are conflicts in the data table (note that some database engines do not support this command, while others support multiple option parameters to specify how to check the table). Typically, the CHECK TABLE command will catch most table and index errors.

You can repair data table errors by using the REPAIR TABLE command, but not all storage engines support this command. At this time, you need to execute a "no operation" ALTER statement, such as changing the engine of a data table to the same as the current engine. For example, you can execute the following statement for an InnoDB data table:

ALTER TABLE innodb_tb1 ENGINE=INNODB;

Alternatively, you can use a storage engine-specific offline repair tool, such as myisamchk, or export the data and re-import it. However, if the conflict occurs in the system area, or in the data row area of ​​the data table instead of the index, you may not be able to use these methods. In this case, you may need to restore the data from your backup or recover the data from the conflicting files.

If you encounter conflicts in InnoDB, this is a serious error and you need to use the correct method to analyze the problem. InnoDB does not normally have conflicts. It is designed to be robust to conflict handling. The conflict may be a manifestation of hardware failure (such as memory area error or disk error), DBA's operational error (such as operating the database file outside the MySQL environment) or InnoDB's own bug (the probability of this is very low). A common reason is similar to the error in creating backups with the rsync utility. There is no query that can be executed at this time - since this would cause InnoDB data conflicts, which you thought you would avoid. If you cause InnoDB data conflicts through a problematic query, it is not your fault, it is an InnoDB bug.

If you really encounter a data conflict, the most important thing is to figure out the cause of the conflict. Before that, don't simply repair the data. Maybe the conflict will disappear automatically. You can modify InnoDB to forced recovery mode to repair data by using the innodb_force_recovery parameter (refer to the MySQL manual). You can also use the open source Percona InnoDB Data Recovery Tool (www.percona.com/software/my…) to extract data from damaged data files.

Update index statistics

Before the MySQL query optimizer decides how to use an index, it calls two APIs to obtain the distribution of index values. The first is the records_in_range method, which takes a range as an argument and returns the number of results in that range. The returned result is exact for MyISAM engine, but it is an estimate for InnoDB.

The second API is the info method, which returns various types of data, including index candidates (i.e., an estimate of the number of records corresponding to each index).

When the storage engine provides the query optimizer with inaccurate data row count information, or the query plan is too complex to estimate the exact number of rows, the optimizer uses index statistics to estimate the number of data rows. The MySQL optimizer makes decisions based on the query cost, and the most important cost criterion is the amount of data that the query will search. If index statistics have never been generated, or are out of date, the optimizer may make incorrect decisions. The solution is to run the ANALYZE TABLE command, which will rebuild the index statistics.

Each storage engine implements index statistics differently, so the frequency and cost of running the ANALUZE TABLE command may vary. Typical storage engines handle index statistics as follows:

• The Memory engine does not store index statistics.
• MyISAM stores index statistics on disk, and ANALYZE TABLE uses a full index scan when computing candidate data rows. The entire table will be locked during this process.
• InnoDB does not store index statistics on disk as of MySQL 5.5. Instead, it does so by random sampling of the indexes and storing the results in memory.
  

Candidates for an index can be examined with the SHOW INDEX FROM command. For example:

This command gives a lot of information about indexes. You can refer to the MySQL manual for details. Of particular interest here is the Cardinality column. This column shows how many unique values ​​the storage engine estimates the index corresponds to. In MySQL 5.0 and later, this information is also available in the INFORMATION_SCHEMA.STATISTICS table, which is very convenient. For example, you can query INFORMATION_SCHEMA to find indexes with low filterability. However, please note that for servers with huge amounts of data, these intermediate tables may cause a significant increase in the server load.

InnoDB statistics are worth further study. The statistical results are calculated by random sampling of index data pages, assuming that the remaining unsampled data is also similarly distributed. In older InnoDB versions, this number of sampled pages was 8, but in recent versions this can be adjusted via the innodb_stats_sample_pages variable. Setting this value to greater than 8 can help generate more representative index statistics, especially for large tables, but the cost will vary.

InnoDB calculates index statistics when a table is first opened, when ANALUZE TABLE is run, and when the table storage size changes significantly (1/16 of a change or 2 billion rows inserted).

InnoDB also computes index statistics for certain queries of INFORMATION_SCHEMA tables, for running SHOW TABLE STATUS , for executing SHOW INDEX queries, or for the MySQL command-line client with the auto-completion setting enabled. This can actually cause serious problems for servers with large amounts of data or very slow I/O speeds. Resampling caused by client programs or monitoring tools can cause a lot of locks and increase server load, which can also affect the startup time of end users. Since the SHOW INDEX command updates the index statistics, you cannot observe the index statistics if you do not change them. You can avoid these problems by disabling the innodb_stats_on_metadata option (off by default). The following command can check the system variables related to InnoDB index statistics.

SHOW GLOBAL VARIABLES WHERE Variable_name like 'innodb_stats%'

If you are using Percona Server with the Percona XtraDB storage engine, which is an alternative to InnoDB, you can do further configuration. The innodb_stats_auto_update option allows you to disable automatic sampling, effectively freezing automatic statistics calculations unless you run ANALYZE TABLE manually. This can free you from flaky queries. This feature was added based on requests from customers with large deployments.

To achieve higher query plan stability and faster system startup, you can use system-level data tables to store index statistics. This method does not require recalculating index statistics when the system is restarted or when InnoDB is started for the first time to open the data table. This feature is available in Percona 5.1 and in the standard MySQL 5.6 release. This Percona Server feature is enabled via the innodb_use_sys_stats_table option. After MySQL version 5.6, it is controlled by the innodb_stats_persistent option, which is ON by default. At the same time, there is another variable that controls a single table. The innodb_stats_auto_recalc variable defaults to ON, which will recalculate the index statistics of the table when the data table changes by more than 10% (the manual can be referred to: dev.mysql.com/doc/refman/…).

If you do not configure automatic updating of index statistics, you should periodically use the ANALYZE TABLE command to update index statistics unless you know that not updating will not result in poor query plans.

The above is the details of how to maintain MySQL indexes and data tables. For more information on MySQL index and data table maintenance, please pay attention to other related articles on 123WORDPRESS.COM!