前言
表结构如下,现在从select city,name,age from t where city='西安' order by name limit 1000 ;
这条语句分析下order by的原理。
CREATE TABLE `t` (
`id` INT (11) NOT NULL,
`city` VARCHAR (16) NOT NULL,
`name` VARCHAR (16) NOT NULL,
`age` INT (11) NOT NULL,
`addr` VARCHAR (128) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `city` (`city`)
) ENGINE = INNODB;
排序方式一
先用explain来看这条语句的执行计划:
Extra列的Using filesort表示进行了排序,MySQL会为每个线程分配一块内存(称为sort_buffer)用于排序。city字段上建立了索引,上述SQL语句执行流程如下:
- 初始化sort_buffer确定放入city,name,age字段
- 从city索引上找到第一个满足city="西安"的记录,取id
- 回到主键索引上根据id取出city,name,age字段,放入sort_buffer
- 从city索引上取下一个记录
- 重复第3和第4步,直到遇到一个不满足city="西安"的记录
- 对sort_buffer中的数据按照name做快速排序
- 从sort_buffer排好序的结果中取前1000条返回客户端
从这个排序步骤中可以看出,假设表中有10000条记录满足city=“西安”,则参与排序的数据是10000条,也就是说其中9000条数据是无用的,无需返回给客户端
上面提到了sort_buffer用于排序的内存,这块内存的大小由参数sort_buffer_size而定,当要排序的所有数据足够放入sort_buffer时,就可以像上述步骤一样,直接在sort_buffer内存中排序,但是当sort_buffer内存不够放入要排序的数据时,就需要用到外部排序(归并排序利用磁盘临时文件辅助排序)。
小结
这种排序方式把select后面的所有字段都放入sort_buffer中,如果sort_buffer不足够存放所有的数据就要用到临时文件,select后面的字段越多,用到的临时文件就越多,效率就越低。所以说,如果select后面的字段太多的话,这种排序方式效率低。
MySQL中的max_length_for_sort_data参数用来决定排序是单行数据的最大值,如果单行数据大小超过这个值,则MySQL不会采用上面的那种排序方式。
排序方式二
如果单行数据的大小超过了max_length_for_sort_data设置的值,则排序方式为:
- 初始化sort_buffer确定放入name和id
- 从city锁引上找city="西安"的记录,取id
- 回到主键索引上根据id取出name字段,放入sort_buffer
- 从city索引上取下一个记录
- 重复第3和第4步,直到遇到一个不满足city="西安"的记录
- 对sort_buffer中的数据按照name做快速排序
- 遍历sort_buffer中排好序的结果,取前1000条结果的每条结果的id,再回到主键索引上取city,name,age字段字段返回给客户端
注意最后一步,并不是把前1000条结果的city,name,age都组好了一次返回给客户端,而是边取边返回,以减小内存开销。
这种方式的一个弊端是比方式一多进行了一次回表操作,使读磁盘的次数增多了。但是优势是需要用到的临时文件数量减少了,排序的效率提高了。
方式一和方式二的比较
只有当MySQL判断到sort_buffer太小,排序过程中需要用到太多临时文件影响排序性能的时候,才会用第二种方式。也就是,内存足够大,那就使用第一种方式减少回表访问磁盘的次数。
优化
使用了order by不一定需要用到排序,如果在city索引上,name字段本来就是有序的,那就不需要再进行排序了。
可以这样做:
alter table t add index city_user(city, name);
建立city和name的联合索引,在(city, name)索引上,节点按照city有序,city值相同的节点,再按照name排序
再来看执行计划:
SQL语句执行流程变为这样:
- 从(city, name)索引上取city="西安"的记录,取id
- 回到主键索引,根据id取city,name,age字段,返回给客户端
- 从(city, name)索引上取下一个记录
- 重复第2、3步直到遇到不满足city="西安"的记录
继续优化
上一个流程有回表操作,如果建立索引如下:
alter table t add index city_user_age(city, name, age);
Using index表示使用了覆盖索引:
则SQL语句执行流程变为这样:
- 从(city, name, age)索引上找city="西安"的记录,取city, name, age返回给客户端
- 从(city, name, age)索引上取下一个记录,取city, name, age返回给客户端
- 重复第2步,直到遇到不满足city="西安"的记录