preface

When doing SQL optimization in MySQL, there are often situations where there is some confusion about whether MySQL can take advantage of indexes. Such as:

  1. MySQL stopped matching range queries when it encountered a range query condition.

  2. How does MySQL use indexes in fuzzy matching LIKE?

  3. Under what circumstances can MySQL use indexes to sort?

Today, I will use a model, all these problems one by one to answer, let you use of MySQL index mechanism have a further understanding, here small make up corresponding summary study notes, a detailed MySQL500 page to share it to everybody, the world of programming has always open to all the people who love programming, this is a free, equal, Shared world, I have always believed so.

First, knowledge supplement

key_len

A key_len column in the EXPLAIN execution plan is used to indicate the length of the selected index in bytes for this query, which is usually used to determine how many columns of the federated index were selected.

Here the key_len size is computed as follows:

  1. Generally, key_len is equal to the length of the index column type in bytes, for example, int is 4 bytes and bigint is 8 bytes.

  2. If the value is a string, consider the character set. For example, CHAR(30) UTF8. Key_len must be at least 90 bytes.

  3. If the column type definition allows NULL, its key_len needs an additional bytes.

  4. If the column type is variable length, such as VARCHAR (TEXT\BLOB does not allow indexes to be created for whole columns; creating partial indexes is also considered a dynamic column type), its key_len needs an additional 2 bytes.

What criteria can be used for indexes

First of all, THANK you very much to Dembo, who gave me a good inspiration. I made this picture based on his article and my own understanding:

At first glance, isn’t it very dizzy? Don’t worry, let’s take a look at the picture, which is divided into three parts:

Index Key: the MySQL Index is used to determine the range of data to be scanned.

Index Filter:MySQL determines which data can be indexed. After ICP is enabled, the Index can be used.

Table Filter: the MySQL database cannot use indexes to Filter data. After retrieving row data from the Table, the MySQL database filters data from the server layer. Let’s go through it.

Index Key

Index Key is used to determine the scan range of MySQL, which is divided into upper and lower boundaries.

MySQL > select first key from where (‘ = ‘, ‘>’); MySQL > select first key from where (‘ = ‘, ‘>’); If it exists and is (>), the key value is added to the lower boundary to stop matching the next index key.

If not, stop matching the lower boundary.

An 🌰

idx_c1_c2_c3(c1,c2,c3)where c1>=1 and c2>2 and c3=1
Copy the code

Analysis:

first key (c1,c2)

C1 is ‘>=’, add lower boundary delimiter, continue to match the next

C2 is ‘>’, add lower boundary delimiter, stop matching

The last key is similar to the first key. First, check whether it is one of (=,<=). If yes, add delimit and continue with the next key value matching

An 🌰

idx_c1_c2_c3(c1,c2,c3)where c1<=1 and c2=2 and c3<3
Copy the code

First key (c1,c2,c3) c1 =’ <=’, add upper boundary delimiter, continue matching next C2 =’ =’, continue matching next C3 =’ <‘, add upper boundary delimiter, stop matching

Note: The simple memory here is,

If the comparison symbol contains a ‘=’ sign, and ‘>=’ also contains a ‘=’, then the key can be used to continue matching the following key value;

If there is no ‘=’, that is,’ >’,'<‘, the following key will not match.

At the same time, upper and lower boundaries can not be mixed, which boundary can use the index of the key value is the number of final use index key value.

An Index Filter can be filtered by an Index.

That is, the part of the field in the Index Key value that cannot be used to determine the Index Key.

An 🌰

idex_c1_c2_c3

where c1>=1 and c2<=2 and c3 =1

index key –> c1

index filter–> c2 c3

Why is the index key just c1?

C2 is used to determine the upper boundary, but c1 does not appear on the upper boundary (<=,=), while c1 is >= on the lower boundary,c2 does not appear, so the index key only has c1 field.

C2 and C3 both appear in the index and are treated as index filters.

Table Filter

If the index cannot be used for filtering, only table filter can be used.

At this point, the engine layer will return the row data to the Server layer, and then the Server layer performs the Table filter.

3. Handle Between and Like

If there is a “between” and “like” in the query, how does MySQL handle it?

Between

Select * from ‘a’ where c1>=’a’ and ‘b’ where c1>=’a’ and c1 <=’b

Like

Select * from c1 where c1 like ‘%a’; select * from c1 where c1 like ‘%a’; select * from c1 where c1 like ‘%a’

Where c1 =’a %’ and c1<‘b’ is the same as where c1>=’a’ and c1<‘b’.

Fourth, index sort

In a database, if sorting cannot be done using indexes, the cost of sorting increases as the volume of data filtered increases, even with limits.

However, the database will choose to sort all the result sets, and then take the sorted limit records, and MySQL has optimized for the limit that can be sorted by index, which can reduce the cost.

Make sure it uses index It is very important to have ORDER BY with LIMIT executed without scanning and sorting full result set, So it is important for it to use index — in this case index range scan will be started and query execution stopped as soon as soon as required amount of rows generated.

CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c1` int(11) NOT NULL DEFAULT '0', `c2` int(11) NOT NULL DEFAULT '0', `c3` int(11) NOT NULL DEFAULT '0', `c4` int(11) NOT NULL DEFAULT '0', `c5` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `idx_c1_c2_c3` (`c1`,`c2`,`c3`)) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4select * from t1; +----+----+----+----+----+----+| id | c1 | c2 | c3 | c4 | c5 |+----+----+----+----+----+----+| 1 | 3 | 3 | 2 | 0 | 0 || 2 | 2 | | | | 0 5 4 0 | | | | 2 | 3 | 3 | 4 0 0 | | | 1 | 3 | 4 2 | | 0 0 | | | 1 | 3 | 3 | 0 5 | | 0 6 | 2 | 3 | | | | 0 5 7 0 | | 2 | 3 | | | | | 0 0 6 + - + - + - + - + - + - + 7 rows in the set (0.00 SEC) select c1, c2 and c3 from t1; +----+----+----+| c1 | c2 | c3 |+----+----+----+| 1 | 3 | 2 || 1 | 3 | 3 || 2 | 3 | 5 || 2 | 4 | 5 || 3 | 2 | 4 || 3 | 2 6 | | | 2 | 3 | 3 | + - + - + - + 7 rows in the set (0.00 SEC)Copy the code

Select c1,c2,c3 from T1; Select c1,c2,c3 from T1 order by c1,c2,c3; select c1,c2,c3 from T1 order by c1,c2,c3; So what are the order rules for indexes?

C1 =3 — > C2 ordered, c3 disordered

C1 =3,c2=2 — > c3 ordered

C1 in(1,2) — > c2 disordered, c3 disordered

There’s a little pattern, idx_

c1_

C2_c3, so how do you know if a field is ordered?

C1 is first in the index, and c2 is second. C2 is ordered only if C1 uniquely determines a value. If C1 has multiple values, then C2 is not necessarily ordered, and so is C3

Five, the summary

For MySQL index, I just mentioned the model in the case of single table query, through this article, you should know how to use MySQL index in most cases, xiaobi.com summarized a detailed MySQL500 page study notes, share with you, welcome everyone to exchange. If you like the article, remember to pay attention to me and give a thumbs-up. Thank you for your support!