B+树索引（7）之索引适用场景（上）

前言

前面索引相关的文章都是侧重讲解索引的结构，索引数据存储方式、保存的逻辑、B+树的构成等等，后面开始聊聊如何正确使用索引，因为不正确的使用索引是会让索引失效的，相关索引文章参考如下。

索引的代价

B+树索引（6）之MyISAM索引方案

B+树索引（5）之索引注意事项

B+树索引（4）之索引分类

B+树索引（3）之索引推导优化

B+树索引（2）之索引的推导

B+树索引（1）之为什么需要索引

为了讲解方便创建测试表person_info，个人信息表

CREATE TABLE person_info(
    id INT NOT NULL auto_increment,
    name VARCHAR(100) NOT NULL,
    birthday DATE NOT NULL,
    phone_number CHAR(11) NOT NULL,
    country varchar(100) NOT NULL,
    PRIMARY KEY (id),
    KEY idx_name_birthday_phone_number (name, birthday, phone_number)
);
1
2
3
4
5
6
7
8
9

该表包含两个索引一个是主键索引也称为聚簇索引，第二个索引称为组合索引就是二级索引该索引包含三个字段，分别为name, birthday, phone_number，在之前的文章中我们提到过组合索引排序规则如下

先按照name字段值排序，当name字段值相同时按照birthday字段值排序，当birthday字段值还相同时就根据phone_number字段值排序

根据这个索引排序规则我们就可以得到如下这个B+组合索引树（为方便表达只保留真实数据的部分）。

索引适用场景

全值匹配

什么样的查询条件可以称为全值匹配呢？

索引列和搜索条件中的列是一致的，就被称为全值匹配。

以上面组合索引idx_name_birthday_phone_number为例，那么就是查询条件中包含name, birthday, phone_number，如下

select * from person_info where name='Aaron' and birthday='1974-08-12' and phone_number='13474749741';
1

因为组合索引是按照索引定义列顺序排序，也就是name, birthday, phone_number

• 先按照name排序，就能很容易找到name='Aaron’的列。

• name相同后又会根据birthday排序，所以很容易找到birthday='1974-08-12’的列。

• 当name和birthday都相同，那么还会根据phone_number排序，直到找到phone_number='13474749741’的列。

在使用全值匹配索引规则时我们需要注意这和where后面的列值顺序无关，也就是说如下所示SQL同样可以应用idx_name_birthday_phone_number索引

select * from person_info where phone_number='13474749741' and birthday='1974-08-12' and name='Aaron';
1

为什么呢？因为我们在客户端执行SQL后会发送给Mysql服务端，服务端的优化器可以自动优化SQL这时就会根据索引的顺序调整。

匹配左边的列

这个原则其实就是全值匹配的一种简写，为什么这么说呢？因为我们在很多时候可能不会用到太多的条件，这时我们可以尽量使用索引定义包含最左边的列，什么意思呢？也就是如下SQL

-- 索引匹配最左列原则
select * from person_info where name='Aaron';

select * from person_info where name='Aaron' and birthday='1974-08-12';
1
2
3
4

只有上面这种写法才能符合匹配左边的列原则，因为B+树的排序顺序为name, birthday, phone_number，只有当name相同时才会根据birthday排序，只有name和birthday列都相同才会根据phone_number排序，也就是说如果单纯使用birthday或者phone_number列查询时记录不一定是按birthday或者phone_number列顺序排列，无序自然需要全表扫描。

-- 索引失效查询方式
select * from person_info where birthday='1974-08-12';

select * from person_info where phone_number='13474749741';
1
2
3
4

当然还需要注意一种情况

select * from person_info where name='Aaron' and phone_number='13474749741';
1

这种情况是name会使用索引而phone_number不会使用索引，因为只有name和birthday都相同才会使用phone_number列排序，其余情况phone_number并不一定有序。

匹配列前缀

这个原则衍生自模糊查询，正确SQL如下

select * from person_info where name like 'Aar%';
1

在Mysql中字符串排序一般通过字符集的比较规则，一般比较规则都是通过单个字符逐个比较，以上面B+树测试数据为例，名字排序应该如下

Aaron
Aaron
.....
Aaron
Asa
Ashburn
.....
Ashburn
Baird
Barlow
....
Barlow
1
2
3
4
5
6
7
8
9
10
11
12

这个原则和匹配左边的列原理是一样的，因为字符串都是逐个比较，如果不清楚前面某个字符如下

select * from person_info where name like '%arl%';
1

那么中间字符不一定按照顺序排列，所以会走全表扫描。