前言
日常开发中,我们经常会使用到order by,亲爱的小伙伴,你是否知道order by 的工作原理呢?order by的优化思路是怎样的呢?使用order by有哪些注意的问题呢?本文将跟大家一起来学习,攻克order by~
一个使用order by 的简单例子
假设用一张员工表,表结构如下:
CREATE TABLE `staff` (
`id` BIGINT ( 11 ) AUTO_INCREMENT COMMENT '主键id',
`id_card` VARCHAR ( 20 ) NOT NULL COMMENT '身份证号码',
`name` VARCHAR ( 64 ) NOT NULL COMMENT '姓名',
`age` INT ( 4 ) NOT NULL COMMENT '年龄',
`city` VARCHAR ( 64 ) NOT NULL COMMENT '城市',
PRIMARY KEY ( `id`),
INDEX idx_city ( `city` )
) ENGINE = INNODB COMMENT '员工表';
表数据如下:
我们现在有这么一个需求:查询前10个,来自深圳员工的姓名、年龄、城市,并且按照年龄小到大排序。对应的 SQL 语句就可以这么写:
select name,age,city from staff where city = '深圳' order by age limit 10;
这条语句的逻辑很清楚,但是它的底层执行流程是怎样的呢?
order by 工作原理
explain 执行计划
我们先用Explain关键字查看一下执行计划
- 执行计划的key这个字段,表示使用到索引idx_city
- Extra 这个字段的 Using index condition 表示索引条件
- Extra 这个字段的 Using filesort表示用到排序
我们可以发现,这条SQL使用到了索引,并且也用到排序。那么它是怎么排序的呢?
全字段排序
MySQL 会给每个查询线程分配一块小内存,用于排序的,称为 sort_buffer。什么时候把字段放进去排序呢,其实是通过idx_city
索引找到对应的数据,才把数据放进去啦。
我们回顾下索引是怎么找到匹配的数据的,现在先把索引树画出来吧,idx_city索引树如下:
idx_city索引树,叶子节点存储的是主键id。 还有一棵id主键聚族索引树,我们再画出聚族索引树图吧:
我们的查询语句是怎么找到匹配数据的呢?先通过idx_city索引树,找到对应的主键id,然后再通过拿到的主键id,搜索id主键索引树,找到对应的行数据。
加上order by之后&