MySQL 索引选择

时间:2022-09-29 NPy 人气:0

前言

索引的本质是存储引擎用于快速查询记录的一种数据结构。特别是数据表中数据特别多的时候，索引对于数据库的性能就愈发重要。

在数据量比较大的时候，不恰当的索引对于数据库的性能的影响是非常大的。在实际的应用中常常会遇见使用错误的索引而导致一系列问题，所以，选择正确的索引对于 MySQL 数据库来说相当重要。

下面我们就来一起聊聊在 MySQL 数据库中该怎么选择正确的索引。

在了解怎么选择索引之前，我先给你举一个例子。如果我们在字典中用拼音查询某一个字，首先我们得根据拼音字母进而找到对应的页码。索引也是这个原理。

当我们查询一条数据的时候，我们首先在索引中查询到对应的值，然后根据匹配到的索引去找到对应数据。

例如：

mysql> select name from city where fid = 1;
+--------------+
| name         |
+--------------+
| 浦东新区      |
+--------------+
1 row in set (0.00 sec)

如果我们在fid字段上建立索引，那么 MySQL 数据库就会使用索引找到fid = 1的行，然后返回包含fid = 1的行中的所有数据。

对于 MySQL 数据库来说，索引是由存储引擎实现的，所以不同的存储引擎提供的索引也不一样。下面我们就来了解一下 MySQL 数据库中各种索引的优缺点。

MySQL 单字段索引问题

在 MySQL 数据库中，索引不能够使用表达式，具体如下：

mysql> explain select * from city where fid + 1 = 2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

mysql> explain select * from city where fid = 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | ALL  | index_1       | NULL | NULL    | NULL |    5 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

从结果上讲，select * from city where fid + 1 = 2; 和 select * from city where fid = 1;是完全一致的。

但是，在explain表达式中可以看出select * from city where fid + 1 = 2;是无法命中索引的。这是因为 MySQL 数据库无法解析fid + 1 = 2这个表达式，所以我们在使用索引时，索引的列不能够是一个表达式。

总之，通常情况下，对于单个字段的索引来说，必须直接使用，不能够使用一个表达式。

组合索引

我们经常会遇见这样一个场景，假设要求查询fid=1或者name='青浦区'，这个时候我们查询的SQL语句如下：

select * from city where fid = 1 or name = '青浦区';

这个时候，我们如果要想提高查询速度，一般就会选择在fid字段和name字段上分别加上一个索引，但实际上这种做法是不恰当的。

具体如下：

mysql> explain select * from city where name = '青浦区' or fid = 1;
+----+-------------+-------+------------+------+-----------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys   | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+-----------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | ALL  | index_1,index_2 | NULL | NULL    | NULL |    5 |   100.00 | Using where |
+----+-------------+-------+------------+------+-----------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

我们可以看出，本次查询并没有使用到任何索引。

具体步骤如下：

首先，根据name字段全表扫描查询出name = '青浦区'包含的所有结果；
其次，再根据fid字段全表扫描查询出fid = 1包含的所有结果；
最后，通过UNION ALL将所有的结果组合到一起并返回。

在这一过程中，MySQL 数据库需要通过全表扫描两次才能查询出结果。如果有更多的条件，查询的次数会更多。所以，在大多数情况下，多个条件查询在多个字段上建立索引并不能够提高MySQL的查询性能。

为了解决多个字段同时需要索引的这一问题，MySQL 5.0之后的版本中提供了一个组合索引。它主要是将所有的字段组合建立一个索引，这样就可以直接利用索引匹配，而不需要全表扫描了。

具体如下：

mysql> explain select * from city where name = '青浦区' or fid = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | city  | NULL       | index | index_3       | index_3 | 772     | NULL |    5 |    36.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

我们可以看出，利用了组合索引之后的查询是使用到了索引，具体如下：

首先，根据索引匹配出name = '青浦区'的所有的内容；
第二次查询仍然是根据fid字段全表扫描查询出fid = 1包含的所有结果；
最后，通过UNION ALL将所有的结果组合到一起并返回。

在这一过程中，MySQL 数据库需要通过索引匹配两次就能查询出结果。所以，在大多数情况下，当有多个条件查询时，组合索引可以有效地提高MySQL的查询性能。

讲完单字段索引和组合索引之后，下面我们可以聊一下唯一索引和普通索引的区别以及使用场景。

唯一索引和普通索引

说起唯一索引和普通索引，有朋友可能就非常熟悉。普通索引的主要特征就是提高了查询的速度，唯一索引的主要特征除了提高查询的速度外就是所有字段的值唯一。

那么，我现在提一个问题，唯一索引和普通索引都应该在什么场景下使用呢？一定是需要唯一值的场景下才使用唯一索引吗？下面我们就来对比着聊一下普通索引和唯一索引。

为了加强了解，我们从读写性能方面来聊一下普通索引和唯一索引。

假设现在我们有一个订单系统，订单号唯一，那么我们看一下订单号在使用唯一索引和普通索引的情况下读的性能。

具体如下：

mysql> select * from sp_order where order_id = 52355096;
+----+----------+---------+--------------+-------------+-----------+------------+-------------+-------------+
| id | order_id | user_id | order_number | order_price | order_pay | pay_status | create_time | update_time |
+----+----------+---------+--------------+-------------+-----------+------------+-------------+-------------+
|  1 | 52355096 |     410 | DD52355096   |      332.44 | 2         | 1          |  1509051984 |  1507411372 |
+----+----------+---------+--------------+-------------+-----------+------------+-------------+-------------+
1 row in set (0.00 sec)

在 order_id 字段上设置唯一索引时，具体步骤如下：

MySQL 首先会在B-Tree的子树上查询 order_id = 52355096；
再根据查询到的索引值，通过主键索引查询出对应的记录；
组装结果并返回。

在 order_id 字段上设置普通索引时，具体步骤如下：

MySQL 首先会在B-Tree的子树上查询 order_id = 52355096；
继续向下匹配，直至匹配到 order_id 不等于 52355096时；
再根据查询到的索引值，通过主键索引查询出对应的记录；
组装结果并返回。

唯一索引与普通索引之间对比之后，可以发现：普通索引比唯一索引多了一个步骤，就是唯一索引匹配成功之后直接返回，而普通索引还需要往下继续匹配直至条件不符合为止。

那么，在这个过程当中，普通索引与唯一索引之间的性能差多少呢？其实是微乎其微的。这是因为B-Tree算法将相邻或相近的数据都放在相邻的子树之中，索引查询性能相差无几。

聊完普通索引与唯一索引读的性能之后，我们再来聊一下写的性能。

具体如下：

mysql> update sp_order set order_price = '888' where order_id = 52355096;

对于MySQL来说，写的过程如下。

首先判断需要修改的数据是否在Buffer Pool之中。
- 如果该数据在Buffer Pool之中，则直接修改逻辑记录到Buffer Pool中的数据。
- 如果该数据不在Buffer Pool之中，MySQL 会将这一修改的过程记录在Change Buffer之中。之后如果该条数据被查询到，则会将该修改过程merge到Buffer Pool之中，确保数据一致性。
之后，再统一写入磁盘。

那么对于普通索引来说，完全适用于这一过程；但是对于唯一索引来说，按着这种方式修改数据则会影响 MySQL 数据库的性能。这是因为唯一索引在修改数据之前，还需要判断该条数据是否唯一，这样的话就需要将所有的数据全部扫描一遍，进而达到数据唯一。那么这样就不需要使用Change Buffer了，因为在修改之前，唯一索引会将所有的数据全部读取到Buffer Pool之中，直接在内存修改即可。但是不可避免的是，唯一索引会将所有的数据全部独到内存之中，无异于一次全表扫描。

于是，我们可以得出：唯一索引和普通索引都适用于读的场景，而唯一索引不适用于写的场景。

总结

本次我从根本上给你介绍了各种索引的情况。

对于单个字段的索引来说，要直接使用，而不能写成一个表达式，写成表达式将会无法命中索引。
对于多个字段需要索引来说，一般需要创建组合索引，这样有利于命中索引，但是一定要注意组合索引的前缀性。
对于索引的类型，我还给你介绍了唯一索引和普通索引，在读的场景比较多的情况下普通索引和唯一索引都能胜任，不过在写场景比较多的情况下，普通索引的性能要优于唯一索引。

在实际应用中，我们通常建议使用普通索引，对于需要唯一的字段，我们一般在代码的层面去控制其唯一性。

加载全部内容