该课程来自极客时间《MySQL实战45讲》
一、举例
表的部分定义如下:
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;
假设你要查询城市是“杭州”的所有人名字,并且按照姓名排序返回前1000个人的姓名、年龄
sql语句可以写为
select city,name,age from t where city='杭州' order by name limit 1000;
二、name字段无序
2.1、全字段排序
为避免全表扫描,在city字段上加索引
创建完索引后,可以用explain查看执行情况
在Extra字段中,“Using filesort”表示需要排序(因为name字段无序)
在MySql中会给每个线程分配一块内存用于排序,称为sort_buffer
city索引示意图如下:
orderby语句执行流程如下:
-
初始化sort_buffer,确定放入name、city、age这三个字段;
-
从索引city找到第一个满足city='杭州’条件的主键id,也就是图中的ID_X;
-
到主键id索引取出整行,取name、city、age三个字段的值,存入sort_buffer中;
-
从索引city取下一个记录的主键id;
-
重复步骤3、4直到city的值不满足查询条件为止,对应的主键id也就是图中的ID_Y;
-
对sort_buffer中的数据按照字段name做快速排序;
-
按照排序结果取前1000行返回给客户端。
2.1.1、排序的完成地点
内存中的为sort_buffer,但当sort_buffer容量小于要排序的数据量时,就需要使用外部排序
外部排序是利用磁盘临时文件辅助排序,且外部排序一般使用归并排序算法
将需要排序的数据分成x份,每一份单独排序后存在不同的临时文件中,最后把x份有序文件进行合并成一个有序的大文件
2.2、rowid排序
在上面这个算法过程里面,只对原表的数据读了一遍,剩下的操作都是在sort_buffer和临时文件中执行的。
但这个算法有一个问题,就是如果查询要返回的字段很多的话,那么sort_buffer里面要放的字段数太多,这样内存里能够同时放下的行数很少,要分成很多个临时文件,排序的性能会很差。
如果排序的单行长度太大,可以采用rowid排序。
max_length_for_sort_data,是MySQL中专门控制用于排序的行数据的长度的一个参数。
它的意思是,如果单行的长度超过这个值,MySQL就认为单行太大,要换一个算法。
新的算法放入sort_buffer的字段,只有要排序的列(name字段)和主键id
但这时,排序的结果就因为少了city和age字段的值,不能直接返回了
执行流程如下:
-
初始化sort_buffer,确定放入两个字段,即name和id;
-
从索引city找到第一个满足city='杭州’条件的主键id,也就是图中的ID_X;
-
到主键id索引取出整行,取name、id这两个字段,存入sort_buffer中;
-
从索引city取下一个记录的主键id;
-
重复步骤3、4直到不满足city='杭州’条件为止,也就是图中的ID_Y;
-
对sort_buffer中的数据按照字段name进行排序;
-
遍历排序结果,取前1000行,并按照id的值回到原表中取出city、name和age三个字段返回给客户端。
对比全字段排序,由于排序结果缺少city和age字段的值,不能直接返回,所以多了第七步,需要回原表取前1000行
2.3、全字段排序与rowid排序的对比
如果内存够,就要多利用内存,尽量减少磁盘访问。
如果MySQL实在是担心排序内存太小,会影响排序效率,才会采用rowid排序算法,这样排序过程中一次可以排序更多行,但是需要再回到原表去取数据
如果MySQL认为内存足够大,会优先选择全字段排序,把需要的字段都放到sort_buffer中,这样排序后就会直接从内存里面返回查询结果了,不用再回到原表去取数据
对于InnoDB表来说,rowid排序会要求回表多造成磁盘读,因此不会被优先选择
不是所有orderby都需要排序操作的,如果原来的name就是有序的呢?
三、name字段有序
创建city和name的联合索引
alter table t add index city_user(city, name);
这样流程就会是
-
从索引(city,name)找到第一个满足city='杭州’条件的主键id;
-
到主键id索引取出整行,取name、city、age三个字段的值,作为结果集的一部分直接返回;
-
从索引(city,name)取下一个记录主键id;
-
重复步骤2、3,直到查到第1000条记录,或者是不满足city='杭州’条件时循环结束。
用explain查看,可以看出是不需要临时表和排序的
由于返回的字段有三个,现在是city和name的联合索引,我们自然会想到
使用city、name、age的联合索引,就符合返回结果条件,这就会是联合索引,无需回原表取值
创建联合索引
alter table t add index city_user_age(city, name, age);
执行流程为
-
从索引(city,name,age)找到第一个满足city='杭州’条件的记录,取出其中的city、name和age这三个字段的值,作为结果集的一部分直接返回;
-
从索引(city,name,age)取下一个记录,同样取出这三个字段的值,作为结果集的一部分直接返回;
-
重复执行步骤2,直到查到第1000条记录,或者是不满足city='杭州’条件时循环结束。
explain结果
Extra中多了“Using index”,使用了覆盖索引
四、问题
假设你的表里面已经有了city_name(city, name)这个联合索引,然后你要查杭州和苏州两个城市中所有的市民的姓名,并且按名字排序,显示前100条记录。如果SQL查询语句是这么写的 :
mysql> select * from t where city in ('杭州',"苏州") order by name limit 100;
那么,这个语句执行的时候会有排序过程吗,为什么?
如果业务端代码由你来开发,需要实现一个在数据库端不需要排序的方案,你会怎么实现呢?
进一步地,如果有分页需求,要显示第101页,也就是说语句最后要改成 “limit 10000,100”, 你的实现方法又会是什么呢?
答案:虽然有(city,name)联合索引,对于单个city内部,name是递增的。但是由于这条SQL语句不是要单独地查一个city的值,而是同时查了"杭州"和" 苏州 "两个城市,因此所有满足条件的name就不是递增的了。也就是说,这条SQL语句需要排序。
那怎么避免排序呢?
这里,我们要用到(city,name)联合索引的特性,把这一条语句拆成两条语句,执行流程如下:
执行select * from t where city=“杭州” order by name limit 100; 这个语句是不需要排序的,客户端用一个长度为100的内存数组A保存结果。
执行select * from t where city=“苏州” order by name limit 100; 用相同的方法,假设结果被存进了内存数组B。
现在A和B是两个有序数组,然后你可以用归并排序的思想,得到name最小的前100值,就是我们需要的结果了。
如果把这条SQL语句里“limit 100”改成“limit 10000,100”的话,处理方式其实也差不多,即:要把上面的两条语句改成写:
select * from t where city="杭州" order by name limit 10100;
和
select * from t where city="苏州" order by name limit 10100。
这时候数据量较大,可以同时起两个连接一行行读结果,用归并排序算法拿到这两个结果集里,按顺序取第10001~10100的name值,就是需要的结果了。
当然这个方案有一个明显的损失,就是从数据库返回给客户端的数据量变大了。
所以,如果数据的单行比较大的话,可以考虑把这两条SQL语句改成下面这种写法:
select id,name from t where city="杭州" order by name limit 10100;
和
select id,name from t where city="苏州" order by name limit 10100。
然后,再用归并排序的方法取得按name顺序第10001~10100的name、id的值,然后拿着这100个id到数据库中去查出所有记录。
上面这些方法,需要你根据性能需求和开发的复杂度做出权衡。