How to make good use of MySQL indexes? You must know these things!

How to make good use of MySQL indexes? The following article will share with you some things you must know if you want to make good use of MySQL indexes. I hope it will be helpful to you!

This article will talk about how to make good use of MySQL indexes.

In order to explain better, I created a table user_innodb with the storage engine InnoDB, and initialized 500W pieces of data in batches. It contains the primary key id, name field (name), gender field (gender, 0,1 represents different genders), mobile phone number field (phone), and a joint index is created for the name and phone fields.

CREATE TABLE `user_innodb` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`id`)，
  INDEX IDX_NAME_PHONE (name, phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

1. The cost of index

Index can be very effective in improving query efficiency. Since it is so good, can I create an index for each field? I advise you not to be impulsive.

Everything has two sides, and indexes are no exception. Excessive use of indexes will cost us both space and time.

1.1 Space cost

The index is a B tree. Every time you create an index, you need to create a B tree. The nodes of each B tree are It is a data page. Each data page will occupy 16KB of disk space by default. Each B-tree will contain many data pages. So, create a large number of indexes and your disk space will be consumed quickly.

1.2 The cost in time

You can use the "money ability" to solve the cost in space, but we may be helpless about the cost in time.

Maintenance of linked lists

I will take the primary key index as an example. The records in each node of the B-tree in the primary key index are in order from small to large primary key values. Connected using a one-way linked list. As shown in the figure below:

#If I want to delete the record with the primary key id of 1 now, it will destroy the sorting of the records in the three data pages, and I need to delete the records in the three data pages. The records within are rearranged, and the same is true for insertion and modification operations.

Note: Let me remind you that the deletion operation will not immediately rearrange the records in the data page, but will mark the deleted record with a deletion mark until it is appropriate. When the time comes, the record is removed from the linked list, but it will always involve sorting maintenance, which will inevitably consume performance.

Suppose this table has 12 fields, and we set indexes for all 12 fields of this table. If we delete one record, we need to involve N data pages of 12 B-trees. Internal record sorting and maintenance.

What’s worse is that when you add, delete or modify records, it may trigger the recycling and splitting of data pages. Taking the above picture as an example, if I delete the record with id 13, then data page 124 no longer needs to exist and will be recycled by the InnoDB storage engine; if I insert a record with id 12, if there is insufficient space on data page 32 To store this record, InnoDB needs to perform page splitting. We don't need to know the details of page recycling and page splitting, but we can imagine how complicated this operation can be.

If you create an index for each field, you can imagine the performance loss caused by all these index maintenance operations.

Query plan

Before executing the query statement, the MySQL query optimizer will optimize a query statement based on cost and generate an execution plan. If too many indexes are created, the optimizer will calculate the search cost of each index, which will take too much time during the analysis process and ultimately affect the execution efficiency of the query statement.

2. The cost of table return

2.1 What is table return

I will repeat what is table return, We can find the leaf nodes in the B-tree through the secondary index, but the content of the leaf nodes in the secondary index is not complete, only the value of the index column and the primary key value. We need to take the primary key value and go to the leaf node of the clustered index (primary key index) to get the complete user record. This process is called table return.

In the above figure, I take the name secondary index as an example, and only draw the leaf nodes of the secondary index and the leaf nodes of the clustered index, omitting the two Non-leaf nodes of B-tree.

The three lines extending from the leaf nodes of the secondary index represent the table return operation.

2.2 The cost of returning the table

The cost of finding the leaf nodes of the secondary index based on the name field is still relatively small for two reasons:

• 叶子节点所在的页通过双向链表进行关联，遍历的速度比较快；
• MySQL会尽量让同一个索引的叶子节点的数据页在磁盘空间中相邻，尽力避免随机IO。

但是二级索引叶子节点中的主键id的排布就没有任何规律了，毕竟name索引是对name字段进行排序的。进行回表的时候，极有可能出现主键id所在的记录在聚簇索引叶子节点中反复横跳的情况（正如上图中回表的3条线表示的那样），也就是随机IO。如果目标数据页恰好在内存中的话效果倒也不会太差，但如果不在内存中，还要从磁盘中加载一个数据页的内容（16KB）到内存中，这个速度可就太慢了。

是不是说完了回表的代价之后，我会给出一种更高效的搜索方式？不是，回表已经是一种比较高效的搜索方式了，我们需要做的就是尽量地减少回表操作带来的损耗，总结起来就是两点：

• 能不回表就不回；
• 必须回表就减少回表的次数。

接下来先给大家介绍两个与回表相关的重要概念，这两个概念涉及到的方法也是索引使用原则的一部分，因为比较重要，在这里我把这两个概念先解释给大家听。

3. 索引覆盖、索引下推

3.1 索引覆盖

想一下，如果非聚簇索引的叶子节点上有你想要的所有数据，是不是就不需要回表了呢？比如我为name和phone字段创建了一个联合索引，如下图：

如果我们恰好只想搜索name、phone以及主键字段，

SELECT id, name,  phone FROM user_innodb WHERE name = "蝉沐风";

可以直接从叶子节点获取所有数据，根本不需要回表操作。

我们把索引中已经包含了所有需要读取的列数据的查询方式称为覆盖索引（或索引覆盖）。

3.2 索引下推

3.2.1 概念

还是拿name和phone的联合索引为例，我们要查询所有name为「蝉沐风」，并且手机尾号为6606的记录，查询SQL如下：

SELECT * FROM user_innodb WHERE name = "蝉沐风" AND phone LIKE "%6606";

由于联合索引的叶子节点的记录是先按照name字段排序，name字段相同的情况下再按照phone字段排序，因此把%加在phone字段前面的时候，是无法利用索引的顺序性来进行快速比较的，也就是说这条查询语句中只有name字段可以使用索引进行快速比较和过滤。正常情况下查询过程是这个样子的：

• InnoDB使用联合索引查出所有name为蝉沐风的二级索引数据，得到3个主键值：3485，78921，423476；

• 拿到主键索引进行回表，到聚簇索引中拿到这三条完整的用户记录；

• InnoDB把这3条完整的用户记录返回给MySQL的Server层，在Server层过滤出尾号为6606的用户。

如下面两幅图所示，第一幅图表示InnoDB通过3次回表拿到3条完整的用户记录，交给Server层；第二幅图表示Server层经过phone LIKE "%6606"条件的过滤之后找到符合搜索条件的记录，返给客户端。

存储引擎把数据给Server层

Server层返回过滤数据

值得我们关注的是，索引的使用是在存储引擎中进行的，而数据记录的比较是在Server层中进行的。现在我们把上述搜索考虑地极端一点，假如数据表中10万条记录都符合name='蝉沐风'的条件，而只有1条符合phone LIKE "%6606"条件，这就意味着，InnoDB需要将99999条无效的记录传输给Server层让其自己筛选，更严重的是，这99999条数据都是通过回表搜索出来的啊！关于回表的代价你已经知道了。

现在引入索引下推。准确来说，应该叫做索引条件下推（Index Condition Pushdown，ICP），就是过滤的动作由下层的存储引擎层通过使用索引来完成，而不需要上推到Server层进行处理。ICP是在MySQL5.6之后完善的功能。

再回顾一下，我们第一步已经通过name = "蝉沐风"在联合索引的叶子节点中找到了符合条件的3条记录，而且phone字段也恰好在联合索引的叶子节点的记录中。这个时候可以直接在联合索引的叶子节点中进行遍历，筛选出尾号为6606的记录，找到主键值为78921的记录，最后只需要进行1次回表操作即可找到符合全部条件的1条记录，返回给Server层。

很明显，使用ICP的方式能有效减少回表的次数。

另外，ICP是默认开启的，对于二级索引，只要能把条件甩给下面的存储引擎，存储引擎就会进行过滤，不需要我们干预。

3.2.2 演示

查看一下当前ICP的状态：

SHOW VARIABLES LIKE 'optimizer_switch';

执行以下SQL语句，并用EXPLAIN查看一下执行计划，此时的执行计划是Using index condition

EXPLAIN SELECT * FROM user_innodb WHERE name = "蝉沐风" AND phone LIKE "%6606";

然后关闭ICP

SET optimizer_switch="index_condition_pushdown=off";

再查看一下ICP的状态

再次执行查询语句，并用EXPLAIN查看一下执行计划，此时的执行计划是Using where

EXPLAIN SELECT * FROM user_innodb WHERE name = "蝉沐风" AND phone LIKE "%6606";

注：即使满足索引下推的使用条件，查询优化器也未必会使用索引下推，因为可能存在更高效的方式。

由于之前我给name字段创建了索引，导致一直没有使用索引下推，EXPLAIN语句显示使用了name索引，而不是name和phone的联合索引；删除name索引之后，才获得上述截图的效果。大家做实验的时候需要注意。

---

到目前为止大家应该清楚了索引和回表带来的性能问题，讲这些自然不是为了恐吓大家让大家远离索引，相反，我们要以正确的方式积极拥抱索引，最大限度降低其带来的负面影响，放大其优势。如何用好索引，从两个方面考虑：

• 高效发挥已经创建的索引的作用（避免索引失效）
• 为合适的列创建合适的索引（索引创建原则）

4. 什么时候索引会失效？

4.1 违反最左前缀原则

拿我们文章开始创建的联合索引为例，该联合索引的B+树数据页内的记录首先按照name字段进行排序，name字段相同的情况下，再按照phone字段进行排序。

所以，如果我们直接使用phone字段进行搜索，无法利用索引的顺序性。

EXPLAIN SELECT * FROM user_innodb WHERE phone = "13203398311";

EXPLAIN可以查看搜索语句的执行计划，其中，possible_keys列表示在当前查询中，可能用到的索引有哪一些；key列表示实际用到的索引有哪一些。

但是一旦加上name的搜索条件，就会使用到联合索引，而且不需要在意name在WHERE子句中的位置，因为查询优化器会帮我们优化。

EXPLAIN SELECT * FROM user_innodb WHERE phone = "13203398311" AND name = '蝉沐风';

4.2 使用反向查询（!=, <>,NOT LIKE）

MySQL在使用反向查询（!=, <>, NOT LIKE）的时候无法使用索引，会导致全表扫描，覆盖索引除外。

EXPLAIN SELECT * FROM user_innodb WHERE name != '蝉沐风';

4.3 LIKE以通配符开头

当使用name LIKE '%沐风'或者name LIKE '%沐%'这两种方式都会使索引失效，因为联合索引的B+树数据页内的记录首先按照name字段进行排序，这两种搜索方式不在意name字段的开头是什么，自然就无法使用索引，只能通过全表扫描的方式进行查询。

EXPLAIN SELECT * FROM user_innodb WHERE name LIKE '%沐风';

但是使用通配符结尾就没有问题

EXPLAIN SELECT * FROM user_innodb WHERE name LIKE '蝉沐%';

4.4 对索引列做任何操作

如果不是单纯使用索引列，而是对索引列做了其他操作，例如数值计算、使用函数、（手动或自动）类型转换等操作，会导致索引失效。

4.4.1 使用函数

EXPLAIN SELECT * FROM user_innodb WHERE LEFT(name,3) = '蝉沐风';

MySQL8.0新增了函数索引的功能，我们可以给函数作用之后的结果创建索引，使用以下语句

ALTER TABLE user_innodb ADD KEY IDX_NAME_LEFT ((left(name,3)));

再次执行EXPLAIN语句，此时索引生效

4.4.2 使用表达式

EXPLAIN SELECT * FROM user_innodb WHERE id + 1 = 1100000;

换一种方式，单独使用id，就能高效使用索引：

EXPLAIN SELECT * FROM user_innodb WHERE id = 1100000 - 1;

4.4.3 使用类型转换

例1

user_innodb中的phone字段为varchar类型，实验之前我们先给phone字段创建个索引

ALTER TABLE user_innodb ADD INDEX IDX_PHONE (phone);

随便搜索一个存在的手机号，看一下索引是否成功

EXPLAIN SELECT * FROM user_innodb WHERE phone = '13203398311';

可以看到能使用到索引，现在我们稍微修改一下，把phone = '13203398311'修改为phone = 13203398311，这意味着我们将字符串的搜索条件改成了整形的搜索条件，再看一下还会不会使用到索引：

EXPLAIN SELECT * FROM user_innodb WHERE phone = 13203398311;

显示索引失效。

例2

我们再看一个例子，主键id类型是bigint，但是在搜索条件中我估计使用字符串类型：

EXPLAIN SELECT * FROM user_innodb WHERE id = '1099999';

总结

稍微总结一下这个问题，当索引字段类型为字符串时，使用数字类型进行搜索不会用到索引；而索引字段类型为数字类型时，使用字符串类型进行搜索会使用到索引。

要搞明白这个问题，我们需要知道MySQL的数据类型转换规则是什么。简单地说就是MySQL会自动将数字转化为字符串，还是将字符串转化为数字。

一个简单的方法是，通过SELECT '10' > 9的结果来确定MySQL的类型转换规则：

• 结果为1，说明MySQL会自动将字符串类型转化为数字，相当于执行了SELECT 10 > 9;
• 结果为0，说明MySQL会自动将数字转化为字符串，相当于执行了SELECT '10' > '9'。

mysql> SELECT '10' > 9;
+----------+
| '10' > 9 |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

上面的执行结果为1，说明MySQL遇到类型转换时，会自动将字符串转换为数字类型，因此对于例1:

EXPLAIN SELECT * FROM user_innodb WHERE phone = 13203398311;

就相当于

EXPLAIN SELECT * FROM user_innodb WHERE CAST(phone AS signed int) = 13203398311;

也就是对索引字段使用了函数，按照前文的介绍，对索引使用函数是不会使用到索引的。

对于例2:

EXPLAIN SELECT * FROM user_innodb WHERE id = '1099999';

就相当于

EXPLAIN SELECT * FROM user_innodb WHERE id = CAST('1099999' AS unsigned int);

没有在索引字段添加任何操作，因此能够使用到索引。

4.5 OR连接

使用OR连接的查询语句，如果OR之前的条件列是索引列，但是OR之后的条件列不是索引列，则不会使用索引。举例：

EXPLAIN SELECT * FROM user_innodb WHERE id = 1099999 OR gender = 0;

---

上面总结了一些索引失效的场景，这些经验的总结往往对SQL的优化很有益处，但同时需要注意的是这些经验并非金科玉律。

比如使用<>查询时，在某些时候是可以用到索引的：

EXPLAIN SELECT * FROM user_innodb WHERE id <> 1099999;

最终是否使用索引，完全取决于MySQL的优化器，而优化器的判定依据就是cost开销（Cost Base Optimizer），优化器并非基于具体的规则，也不是基于语义，就是单纯地执行开销小的方案罢了。所以在·EXPLAIN·的结果中你会看到possible_keys一列，优化器会把这里边的索引都试一遍（是不是又加深了对不能随便创建索引的认识呢？），然后选一个开销最小的，如果都不太行，那就直接全表扫描好了。

而cost开销，和数据库版本、数据量等都有关系，因此如果想更精准地提升索引功能性，拥抱EXPLAIN吧！

5. 索引创建（使用）原则

之前讲过的索引覆盖和索引下推都可以作为索引创建的原则，就是在创建索引的时候，尽量发挥索引覆盖和索引下推的优势。

尽量避免上述提及到的索引可能失效的情况的出现，同样是索引的使用原则。

除此之外，再给大家介绍一些。

5.1 不为离散度低的列创建索引

先来看一下列的离散度公式：COUNT(DISTINCT(column_name)) / COUNT(*)，列的不重复值的个数与所有数据行的比例。简而言之，如果列的重复值越多，列的离散度越低。重复值越少，离散度就越高。

举个例子，gender（性别）列只有0、1两个值，列的离散度非常低，假如我们为该列创建索引，我们会在二级索引中搜索到大量的重复数据，然后进行大量回表操作。大量回表哈？你懂了吧。

不要为重复值多的列创建索引

5.2 只为用于搜索、排序或分组的列创建索引

我们只为出现在WHERE子句中的列或者出现在ORDER BY和GROUP BY子句中的列创建索引即可。仅出现在查询列表中的列不需要创建索引。

5.3 用好联合索引

用2条SQL语句来说明这个问题：

1. SELECT * FROM user_innodb WHERE name = '蝉沐风' AND phone = '13203398311';

2. SELECT * FROM user_innodb WHERE name = '蝉沐风';

语句1和语句2都能够使用索引，这带给我们的一个索引设计原则就是：

不要为联合索引的第一个索引列单独创建索引

因为联合索引本身就是先按照name列进行排序，因此联合索引对name的搜索是有效的，不需要单独为name再创建索引了。也正因为此

建立联合索引的时候，一定要把最常用的列放在最左边

5.4 对过长的字段，建立前缀索引

如果一个字符串格式的列占用的空间比较大（就是说允许存储比较长的字符串数据），为该列创建索引，就意味着该列的数据会被完整地记录在每个数据页的每条记录中，会占用相当大的存储空间。

对此，我们可以为该列的前几个字符创建索引，也就是在二级索引的记录中只会保留字符串的前几个字符。比如我们可以为phone列创建索引，索引只保留手机号的前3位：

ALTER TABLE user_innodb ADD INDEX IDX_PHONE_3 (phone(3));

然后执行下面的SQL语句：

EXPLAIN SELECT * FROM user_innodb WHERE phone = '1320';

由于在IDX_PHONE_3索引中只保留了手机号的前3位数字，所以我们只能定位到以132开头的二级索引记录，然后在遍历所有的这些二级索引记录时再判断它们是否满足第4位数为0的条件。

当列中存储的字符串包含的字符较多时，为该字段建立前缀索引可以有效节省磁盘空间

5.5 频繁更新的值，不要作为主键或索引

因为可能涉及到数据页分裂的情况，会影响性能。

5.6 随机无序的值，不建议作为索引，例如身份证、UUID

【相关推荐：mysql视频教程】

The above is the detailed content of How to make good use of MySQL indexes? You must know these things!. For more information, please follow other related articles on the PHP Chinese website!