B+树索引（9）之索引应用排序的注意事项

B+树索引（9）之索引应用排序的注意事项

前言

前面有两篇文章讲解了索引的适用场景，包含全值匹配、最左匹配、左前缀匹配、范围查找、排序、分组等，相关文章参考如下链接

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

B+树索引（8）之索引适用场景（下）

其中提到了因为索引树的数据页和数据页之间采用主键或者指定列排序，数据页的记录与记录之间采用相同方式排序，正是因为这一特性，所以索引可以用于排序，为了文章的完整性这里贴出之前文章提到的测试表以及组合索引idx_name_birthday_phone_number的数据结构

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

那么如下SQL就可以使用索引特性直接排序（先按照name值排序，name值相同按照birthday值排序，name值和birthday列都相同按照phone_number排序）。

select * from person_info order by name, birthday, phone_number;
1

联合索引列排序的顺序

对于联合主键来讲order by的列值顺序和索引的定义顺序需要保持相同，什么意思呢？如下SQL

-- 类似最左匹配，只要包含了索引列就要和索引定义列顺序保持一致
select * from person_info order by name, birthday, phone_number;
select * from person_info order by name, birthday;
select * from person_info order by name;

-- 如果索引左边列为常量当然也是可以的，因为name相同会按照birthday值排序
-- birthday相同又会按照phone_number值排序
select * from person_info where name = 'A' order by birthday, phone_number LIMIT 10;
1
2
3
4
5
6
7
8

如果出现如下类似SQL将不能使用联合索引

select * from person_info order by birthday,name,phone_number;
1

为什么呢？因为联合索引先按照name值排序，name值相同按照birthday值排序，name值和birthday列都相同按照phone_number排序，如果顺序不对自然是无序的。

另外这里需要和全值匹配区分，SQL如下

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

这个SQL虽然不是按照索引定义顺序编写，但Mysql服务器中的优化器会根据索引列进行优化，而上面的无序order by 无法优化，因为优化的前提是保证SQL执行结果不变的前提下，尽量优化SQL性能。

不可以使用索引排序的情况

排序顺序ASC,DESC混用

首先我们需要先清楚一点在order by没指定排序顺序的前提下默认是ASC也就是升序排列，这个默认顺序其实也就是索引的数据页与数据页，记录与记录间的排序规则，如果多个列都只应用一种排序规则那么就很好解决，排序SQL如下

-- 默认asc
order by name, birthday limit 10

order by name desc, birthday desc limit 10
1
2
3
4

查找顺序如图

如果出现ASC和DESC混用的情况，如下

 -- name没写默认是asc升序排列，birthday定义desc降序排列
 select * from person_info order by name, birthday desc limit 10;
1
2

那么查询将不再使用索引，查询过程如下

• 先找到索引树叶子节点最左边（也就是最小的）的name值假设为Aaron，然后找到name为Aaron的所有记录项，然后从右向左（因为birthday值是降序排列）查询10条数据。

• 如果数据不足10条，那么会从左到右（因为name值默认升序排列）搜索第二小的name值，然后重复上面的查询流程。

显然这种查询方式还不如之间全表扫描来的快，所以排序方式混用不会用到索引。

排序列包含非索引列

什么意思呢？如测试表person_info的idx_name_birthday_phone_number索引只定义了name、birthday、 phone_number三列，组合索引不包含country列，如果在查询时采用如下方式排序

select * from person_info order by name, country limit 10;
1

虽然name值在索引列，但是上面SQL的语义是记录先按照name排序，如果name相同会按照country排序，而组合索引中并不是这样，所以这种排序也用不到组合索引。

排序列使用函数

这个场景就很好理解了，存在SQL如下

-- UPPER(name)：将索引列name的值转为大写
SELECT * FROM person_info ORDER BY UPPER(name) LIMIT 10;
1
2

当我们将name列的值转为UPPER(name)时，其实name值已经变了，自然无法利用索引搜索。