In-depth analysis of MySQL indexes

Preface

We know that index selection is the work of the optimizer stage, but the optimizer is not omnipotent and it may choose the wrong index to be used. Generally, the optimizer considers the following factors when selecting an index: the number of rows to be scanned, whether to sort, and whether to use a temporary table.

Use explain to analyze SQL

explain is a good self-test command. Frequent use of explain can help us write more reasonable SQL statements and establish more reasonable indexes:

mysql> explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+------------------------------------+
| 1 | SIMPLE | t | NULL | range | a,b | b | 5 | NULL | 50223 | 1.00 | Using index condition; Using where |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+------------------------------------+
1 row in set, 1 warning (0.01 sec)

in:

Table field: indicates which table it is about;
type field: system, const, eq_reg, ref, range, index, all. Generally speaking, it should reach above range level;

system, const: the queried variable can be converted into a constant, such as id=1; id is the primary key or unique key;
eq_ref: accesses the index and returns the data of a single row. It usually appears when joining. The index used for the query is the primary key or unique key.
ref: accesses the index and returns a certain value (possibly multiple rows), usually occurs when = is used;
range: Use an index to return row information within a range, such as using >, <, between
Index: Scan the entire table in the order of the index. Although there is an index and no sorting is required, the entire table must be scanned.
all: full table scan

key field: the index actually used;

key_len field: the length of the index used (the shorter the better without losing precision);

ref field: shows which column of the index is used;

rows field: the number of rows of data that MySQL considers necessary for retrieval;

Extra field: additional information for query, mainly including the following:

using index: using an index
using where: where condition is used
using tmporary: Use a temporary table to process the current query
Using filesort: Use additional sorting, such as the order field has no index
range checked for eache record(index map:N): No index available
Using index for group-by: table name can find all the data needed for grouping in the index, without querying the actual table

Generally, when you encounter Using temporary and Using filesort, you need to find ways to optimize them because the index is not used.

How does MySQL calculate the number of rows to retrieve?

In practice, the number of scanned rows counted by MySQL is not an exact value and can sometimes be far off. The number of scanned rows is calculated based on the cardinality of the index.

In MySQL, the index cardinality is obtained through sampling statistics: the system selects N data pages by default, calculates the average of different values ​​on the data page, and then multiplies it by the number of index pages to get the cardinality. In addition, MySQL will trigger the operation of redoing index statistics when the number of changed data rows exceeds 1/M.

In MySQL, there are two ways to store index statistics, which can be selected by setting the innodb_stats_persistent parameter:

When set to on, the statistics will be stored persistently. At this time, the default N is 20 and M is 10.

When set to off, statistics are stored only in memory. At this time, the default N is 8 and M is 16.

Generally speaking, there is not much difference between the data obtained from cardinality statistics and the actual number of rows. However, for data tables where data is frequently deleted and added, there may be a situation where the data table has 100,000 records but the cardinality statistics show 200,000. This may be caused by MVCC. Because MySQL's InnoDB transaction support needs to maintain multiple data versions, it is possible that some transactions have not yet ended and are still using data that has been deleted for a long time, resulting in the inability to release the deleted data space, while the newly added data has opened up new space. In this case, the number of data pages in the cardinality statistics may be incorrect, resulting in a large error.

A good way to correct this is to execute the analyze table table name command, which is used to recalculate index information.

What should we do if we choose the wrong index?

When we correctly create the necessary indexes, in most cases, the optimizer will not choose the wrong index. What should we do when we encounter the situation where the index is chosen incorrectly?

1. Use force index to force the use of a certain index.

2. Change your thinking and optimize the SQL statement so that you can use the index that you need.

3. Create a more appropriate index or delete an unreasonable index that was used by mistake. (Sometimes, this index may really be redundant and not optimal, and the optimizer just happens to use it).

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.