Example Analysis of MySQL Index

This article shares with you the content of a sample analysis of MySQL indexes. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Preface

We know that index selection is the work of the optimizer phase, but the optimizer is not omnipotent, it is possible to choose the wrong index to use. In general, the factors considered by the optimizer in selecting an index are the number of rows scanned, whether to sort, and whether to use temporary tables.

Using explain to analyze sql

Explain is a good self-test command, and using explain frequently can help us write more reasonable sql statements and build 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 | Using index condition Using where | +-+- -- + 1 row in set 1 warning (0.01 sec)

Where:

Table field: indicates which table to refer to

Type field: system,const,eq_reg,ref,range,index,all. Generally speaking, it is necessary to reach the range level or above.

System, const: you can convert query variables to constants, such as id=1;id as primary key or unique key

Eq_ref: accesses an index and returns a single row of data, usually when connected. The index used by a query is a primary key or a unique key.

Ref: accesses the index and returns some worthwhile data (possibly multiple rows), which usually occurs when =

Range: use an index to return row information in a range, such as using >