In-depth understanding of indexes in MySQL (use, classification, matching methods)

This article will give you an in-depth understanding of the index in MySQL, and introduce the advantages, uses, classifications, technical terms and matching methods of the index. I hope it will be helpful to you!

For advanced development, we often have to write some complex SQL, so to prevent writing inefficient SQL, we need to understand some basic knowledge of indexing. Through these basic knowledge we can write more efficient SQL. [Related recommendations: mysql video tutorial]

01 The advantages of indexing

• are greatly reduced The amount of data that the server needs to scan, that is, the amount of IO
• Help the server avoid sorting and temporary tables (try to avoid file sorting, but use index sorting)
• Turn random IO into sequential IO

02 The use of index

• Quickly find rows matching the where clause
• If possible When choosing among multiple indexes, MySQL will usually use the index with the fewest rows found. If the table has a multi-column index, the optimizer can use any left-most prefix of the index to find the row. When there is a table connection, retrieve row data from other tables
• Find the min and max values ​​of a specific index column
• If sorting or grouping can be done with the leftmost prefix of the index, then Tables are sorted and grouped
• In some cases, queries can be optimized to retrieve data values ​​without having to find data rows
• 03 Classification of indexes

The index created by the database by default is for a unique key

• Ordinary index (index of ordinary fields)
• Full-text index (generally created with varchar, char, text types, but rarely used)
• Combined index ( Index established by multiple words)
• 04 Technical terms of index

1. Return to table

#The name field is an ordinary index. Find the primary key from the B-tree of the name column, and then find the final data from the B-tree of the primary key. This is the table return. (The leaf nodes of the primary key index save all the data of the column, but generally all leaf nodes save the corresponding primary key ID)

As shown in the figure: the index structure established by name in a use table sql is

select * from use where name='sun'

First, the primary key Id=2 corresponding to sun will be found through the non-primary key index name, and then the entire row data will be found in the primary key index through id=2, and Return, this is the return table.

2. Covering index

You can query what you need on the non-primary key index Fields that do not need to be returned to the table to query again are called covering indexes.

As shown in the name index in the figure above, the sql is

select id,name from user where name = "1"

. The value of id is already available in the non-primary key index in the first step. There is no need to query row data in the primary key index based on ID.

3. Leftmost matching

In the combined index, the left side is matched first, and then the backward matching is continued; for example, in the user table The joint index composed of name and age, select * from user where name="Mr. Ji" and age = 18 is consistent with the leftmost matching and can be used as an index. However, select * from user where age = 18 does not comply and this index is not used.

Extension;

How to create an index if it is the following two sql

select * from user where name="纪先生" and age = 18;
select * from user where age = 18;

Due to the leftmost matching principle:

What if it is the following three sql

select * from user where name="纪先生" and age = 18;
select * from user where name= "纪先生";

In fact

, because the index also needs to be stored persistently, occupying disk space, and also takes up memory when reading, name age and age name The occupancy is the same, but comparing name and age separately, age definitely takes up less space and name is longer (the larger the index, the more IO times)

Attention! Notice! Notice! :

When reading many articles, I often see some examples of leftmost matching errors:

select * from user where age = 18 and name="Mr. Ji"

Many people think that this cannot be indexed, but it is actually possible. The mysql optimizer will optimize the adjustment sequence and adjust it to name="Mr. Ji" and age = 18

##4. Index pushdown

Use index information as much as possible in the combined index to reduce the number of table returns as much as possible

案例：还是 name+age的组合索引如果没有索引下推的查询是 在组合索引中通过name查询所有匹配的数据，然后回表根据ID查询对于的数据行，之后在筛选出符合age条件的数据。索引下推就是组合索引中通过name查询匹配再根据age找到符合的数据ID，然后回表根据ID查询对应行数据，明显会减少数据的条数

05 索引匹配方式

mysql官网准备了一些学习测试的数据库，可以直接下载通过source导入到我们自己的数据库

官网地址：dev.mysql.com/doc/index-o…

如上图下载zip, 其中包含了sakila-schema.sql和sakila-data.sql,分别是sakila的库，表和数据的创建脚本。

mysql> source /Users/ajisun/Downloads/sakila-db/sakila-schema.sql;
mysql> source /Users/ajisun/Downloads/sakila-db/sakila-data.sql;

需要通过explain来查看索引的执行情况，执行计划以前有文章详细讲过，具体参考执行计划explain

1. 全值匹配

指和某个索引中的所有列进行匹配，例如使用数据库sakila中的staff表

新建一个三个字段的联合索引：

mysql> alter table staff add index index_n1(first_name,last_name,username);

执行sql:

mysql> explain select * from staff where first_name='Mike' and last_name='Hillyer' and username='Mike'复制代码

其中的ref是三个const， 用到三个字段，能全匹配一条数据

2. 最左前缀匹配

只匹配组合索引中前面几个字段

执行sql:

mysql> explain select * from staff where first_name='Mike' and last_name='Hillyer';

ref只出现2个const,比上面全值匹配少一个，就只匹配了前面两个字段

3. 匹配列前缀

可以匹配某一列的的开头部分，像like属性

执行sql:

mysql> explain select * from staff where first_name like 'Mi%';

type=range ,是个范围查询，可以匹配一个字段的一部分，而不需要全值匹配

如果有模糊匹配的字段不要放在索引的最前面，否则有索引也不能使用，如下

4. 匹配一个范围值

可以查找某一个范围的数据

mysql> explain select * from staff where first_name > 'Mike';

5. 精确匹配某一列并范围匹配另一列

可以查询第一列的全部和另一列的部分

mysql> explain select * from staff where first_name = 'Mike' and last_name like 'Hill%';

6. 只访问索引的查询

查询的时候只需要访问索引，不需要访问数据行，其实就是索引覆盖

mysql> explain select first_name,last_name,username from staff where first_name='Mike' and last_name='Hillyer';