假设我们要查询一个市民表中城市 = 杭州的所有人的名字, 并且按照名字排序
- 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;
那么 sql 语句可以这样写
select city,name,age from t where city='杭州' order by name limit 1000 ;
接下来我们看下 explain 的结果
图中的 Extra 这一列下面的 Using filesort 表示需要排序, MySQL 会为每个连接分配一块内存用于排序, 就是 sort_buffer,sort_buffer_size 可以调整该排序内存大小
因为我们 where 条件用到了 city, 所以我们在 city 上面建立了索引
我们先看下该索引结构
从图中可以看出满足 city = 杭州的条件是 ID_X 到 ID_Y 之间的数据
通常情况下这个语句的执行流程如下:
1. 初始化 sort_buffer, 确定放入 name,age,city 三个字段
2. 从索引 city 中找到第一个符合条件的数据, 也就是 ID_X 这个
3. 取出索引中 id 的值, 回表查询 name,age,city 的数据放入 sort_buffer 中
4. 从索引 city 取下一个符合条件的 id
5. 重复步骤 3,4 直到 city 的值不满足 city = 杭州的条件, 也就是图中 ID_Y
6. 对 sort_buffer 中的数据按照 name 排序
7. 按照排序结果取前 1000 行数据返回给客户端
我们把这个排序过程叫全字段排序
如下图所示
上图按 name 排序这个动作可能在内存中完成也可能需要外部排序, 这取决于排序需要的内存大小和 sort_buffer_size 这个参数
如果排序需要的内存大于 sort_buffer_size 设置的数值, 那么就需要使用磁盘临时文件辅助排序
rowid 排序
在上面的那个全字段排序中, 只对原表查询了一次, 但是如果查询的字段很多的话, 那么 sort_buffer 中就会很多数据, 就会使用到
磁盘临时辅助文件排序, 这样性能会变差.
那么如果 MySQL 认为单行数据过大会怎么办呢?
接下来设置一下这个参数为 16
max_length_for_sort_data 这个参数是 MySQL 专门用来控制用于排序的行数据的单行的长度的一个参数, 如果单行数据的字段的长度超过这个参数设置的值
那么就会使用 rowid 排序, 比如说我们这个例子中 name,age,city 这三个字段的单行数据长度之和要是大于 16, 那么就会使用 rowid 排序
排序流程:
1. 初始化 sort_buffer, 确定放入 id,name
2. 取出 city 索引中第一个满足条件的索引的 id 值
3. 到主键 id 索引里面取出整行, 取出 name,id 字段放入 sort_buffer
4. 去下一个符合条件的索引记录, 放入 sort_buffer 中
5. 重复步骤 3.4 直到不满足 city = 杭州
6. 对 sort_buffer 中的数据按照 name 进行排序
7. 遍历排序结果取出前 1000 行的数据的 id, 去表中查询出 name,age,city 返回给客户端
可以看出来 rowid 排序比全字段排序多了一次表查询就是步骤 7
我们来对比下这两个排序
如果 MySQL 觉得内存不够用就会用到 rowid 排序, 如果内存够用则用全字段排序
也就是说 MySQL 有个设计思想, 就是如果内存够, 就尽量用内存, 尽量减少磁盘的访问
看到这里你是不是觉得 MySQL 排序是一个非常复杂的流程, 性能会不好, 那么是不是所有的 order_by 语句都要排序呢?
不是的, 如果需要排序的字段天然就是有序的, 那么就不需要排序, 啥意思呢, 比如说我们建立一个 city 和 name 的联合索引
alter table t add index city_user(city, name);
作为与 city 索引的对比, 我们看看这个索引
如果建立了这个索引那么执行流程就变成了这样
1. 查询出第一条联合索引中 city,name 里面 city = 杭州的数据的 id 值
2. 到主键索引里面取出整行, 取出 name,age,city 字段
3. 从索引 city,name 去下一个记录主键 id
4. 重复步骤 2,3 直到查到 1000 条记录或者不符合 city = 杭州循环结束
可以看到这个过程不需要排序, 也不需要用到临时表
用 explain 验证一下
那么这个语句还有没有优化空间呢?
有的
我们建立一个三个字段的联合索引
alter table t add index city_user_age(city, name, age);
那么流程就变成了这样
1. 查询出索引中第一条符合条件的数据, 取出 city,name,age 作为结果集的一部分直接返回
2. 从索引继续取下一个符合条件的数据作为结果集的一部分直接返回
3. 重复步骤 2 直到查到 1000 条记录或者不符合 city = 杭州循环结束
这里其实就是用到了覆盖索引, 直接不用回表查询了
当然这里绝对不是说遇到问题就加索引, 这里只是举个例子, 因为毕竟维护索引也是有代价的
来源: https://www.cnblogs.com/sjks/p/10894742.html