该课程来自极客时间《MySQL实战45讲》
文章目录
- 一、举例
- 二、name字段无序
- 2.1、全字段排序
- 2.1.1、排序的完成地点
- 2.2、rowid排序
- 2.3、全字段排序与rowid排序的对比
- 三、name字段有序
- 四、问题
一、举例
表的部分定义如下：
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到数据库中去查出所有记录。
上面这些方法，需要你根据性能需求和开发的复杂度做出权衡。
