MySQL -- 索引使用规则

最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳过某一列，索引将部分失效（后面的字段索引失效）。

假设user表有username varchar(50)、phone varchar(20)、email varchar(50)三个字段组成的联合索引idx_user_username_phone_email

按照最左前缀法则有以下情况

使用索引最左侧字段进行条件查询，命中索引，且索引长度为152，表示只用到索引中的username字段

1
2
3
4
5
6
7


mysql> explain select * from `user` where `username` = 'Bob';

+----+-------------+-------+------------+------+-------------------------------+-------------------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys                 | key                           | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------------------+-------------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ref  | idx_user_username_phone_email | idx_user_username_phone_email | 152     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+-------------------------------+-------------------------------+---------+-------+------+----------+-------+

使用索引中的全部三个字段，命中索引 且key_len为366，说明三个字段全部命中

1
2
3
4
5
6
7


mysql> explain select * from `user` where `username` = 'Bob' and `phone`='18888888888' and `email`='1888888888@163.com';

+----+-------------+-------+------------+------+-------------------------------+-------------------------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys                 | key                           | key_len | ref               | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------------------+-------------------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ref  | idx_user_username_phone_email | idx_user_username_phone_email | 366     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+-------------------------------+-------------------------------+---------+-------------------+------+----------+-------+

使用 username和email查询，结果 命中索引，但只有username字段是有效的，因为按照最左前缀原则，如果跳过中间列(phone)，则索引将部分失效

1
2
3
4
5
6
7


mysql> explain select * from `user` where `username` = 'Bob' and `email`='1888888888@163.com';

+----+-------------+-------+------------+------+-------------------------------+-------------------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys                 | key                           | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+-------------------------------+-------------------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | user  | NULL       | ref  | idx_user_username_phone_email | idx_user_username_phone_email | 152     | const |    1 |    10.00 | Using index condition |
+----+-------------+-------+------------+------+-------------------------------+-------------------------------+---------+-------+------+----------+-----------------------+

索引失效情况

使用索引中的第二列phone和第三列email查询，未命中索引，因为查询条件中的字段没有从索引的最左列开始（最左前缀法则）

1
2
3
4
5
6
7


mysql> explain select * from `user` where `phone`='18888888888' and `email`='1888888888@163.com';

+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99677 |     1.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+

使用模糊匹配进行查询，未命中索引

1
2
3
4
5
6
7


mysql> explain select * from `user` where `phone` like '188%';

+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99677 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+

条件语句中使用函数，截取用户名username的前缀进行查询，未命中索引

1
2
3
4
5
6
7


mysql> explain select * from `user` where substring(username, 1, 3) = 'dev';

+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99677 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+

字符串不加单引号''，只命中了最左侧的索引字段 未加单引号的字段未命中索引

1
2
3
4
5
6
7


mysql> explain select * from user where username = 'bob' and phone = 18895766335;

+----+-------------+-------+------------+------+-------------------------------+-------------------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys                 | key                           | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+-------------------------------+-------------------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | user  | NULL       | ref  | idx_user_username_phone_email | idx_user_username_phone_email | 152     | const |    1 |    10.00 | Using index condition |
+----+-------------+-------+------------+------+-------------------------------+-------------------------------+---------+-------+------+----------+-----------------------+

OR 作为连接的条件，用OR分开的条件，如果OR前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都 不会命中。

1
2
3
4
5
6
7


mysql> explain select * from user where id = 10 or phone = '18895766335';

+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL | 99677 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+

数据分布影响

如果MySQL评估使用索引比全表更慢，则不使用索引。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18


# 结果是使用索引的，因为记录中没有为null的数据，相对整体记录数占比较小，但如果字段属性加了 `not null` 的限制，不管查询条件是is null 还是 is not null，都将不会使用索引
mysql> explain select * from user where username is null;

+----+-------------+-------+------------+------+-------------------------------+-------------------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys                 | key                           | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+-------------------------------+-------------------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | user  | NULL       | ref  | idx_user_username_phone_email | idx_user_username_phone_email | 153     | const |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+------+-------------------------------+-------------------------------+---------+-------+------+----------+-----------------------+


# 查询条件为 not null 时没有使用索引。
mysql> explain select * from user where username is not null;

+----+-------------+-------+------------+------+-------------------------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys                 | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+-------------------------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | idx_user_username_phone_email | NULL | NULL    | NULL | 99484 |    50.00 | Using where |
+----+-------------+-------+------------+------+-------------------------------+------+---------+------+-------+----------+-------------+

SQL提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

use index

1

explain select * from user use index(idx_user_username_phone_email) where username = 'Bob';

ignore index

1

explain select * from user ignore index(idx_user_username_phone_email) where username='Bob';

force index

1

explain select * from user force index(idx_user_username_phone_email) where username='Bob';

覆盖索引

尽量使用覆盖索引（查询使用了覆盖索引，并且需要返回的列，在该索引中已经全部能够找到），减少select *。

1
2
3
4


# select 中的字段在索引中都可以直接查到，因此不用回表，若查询字段使用`*`，
# 或者加入不在索引中的字段,比如`password`，查询需要先通过联合索引查到二级索引值，
# 取出主键ID，根据主键ID再回表查询出`password`字段值。
explain select id, username, email, `password` from user where username = 'user_99999';

前缀索引

当字段类型为字符串（varchar, text等）时，有时候需要索引很长的字符串，这会让索引变的很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

语法：

1

create index idx_tb_column on tb(column(n));

前缀长度

可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。可根据一下结果做参考：

1
2
3


select count(distinct email) / count(*) from user;

select count(distinct substring(email, 15)) / count(*) from user;

单列索引和联合索引

单列索引：即一个索引值包含单个列
联合索引：即一个索引包含了多个列

😀 提示：在业务场景中，如果存在多个查询条件，考虑对查询字段建立索引时，建议建立联合索引，而非单列索引。

索引设计原则

1.针对数据量较大，且查询比较频繁的表建立索引。
2.针对常作为查询条件（WHERE）、排序（ORDER BY）、分组（GROUP BY）操作的字段建立索引。
3.尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的频率越高。
4.如果是字符串类型的字段，字段的长度较长，可以针对字段的特点，建立前缀索引。
5.尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6.要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价就越大，会影响增删改的效率。
7.如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好的确定哪个索引最有效的用于查询。