针对这个sql语句 select city,name,age from t where city='杭州' order by name limit 1000 来分析下 order by 的工作流程, city上有索引

1.全字段排序

通过explain 分析,可以看出 Extra 字段中 Using filesort表示的就是需要排序, mysql会给每个线程分配一块内存用于排序,称为sort_buffer

全字段排序 执行流程:

初始化 sort_buffer,确定放入到 name,city,age这三个字段从 索引city找到第一个满足city='杭州'条件的主键id,到主键id索引取出整行,取name,city,age 三个字段的值,存入到 sort_buffer中从索引city取下一个记录的主键id;重复步骤3,4直到city的值不满足查询条件为止对sort_buffer中的数据按照字段name做快速排序按照排序结果取前1000行返回给客户端

如果要排序的数量小于 sort_buffer_size 就会在 内存中完成,反之,会利用磁盘临时文件辅助排序

查看排序语句是否使用了临时文件

 /* 打开optimizer_trace */ SET OPTIMIZER_TRACE='enabled=on'; /* 执行语句 */ select city,name,age from t where city='杭州' order by name limit 1000 /* 查看 OPTIMIZER_TRACE 输出 */ SELECT * FROM `INFORMATION_SCHEMA`.`OPTIMIZER_TRACE`;

number_of_type_files为0表示未使用临时文件排序,如上图,则说明了12个临时文件进行了排序,排序算法 归并排序?

packed_additional_fields:参数说明在排序过程中对字符串做了 紧凑处理,即使 name 字段的定义是 varchar(16),在排序过程中还是要按实际长度来分配空间的

缺点

如果查询返回的字段很多的话, 那么sort_buffer放不下这么多行数据,就要分成很多个临时文件进行排序,导致排序的性能会很差,所以还出现了另外一种排序算法 rowid排序

2. rowId排序

通过参数max_length_for_sort_data 来设置,在 rowid排序算法中,只有排序的列name字段和主键id

执行流程如下

初始化sort_buffer ,确定放入两个字段, name 和id;从索引city找到第一个满足city='杭州'条件的主键id到主键id索引取出整行,取name,id这两个字段,存入sort_buffer中;从索引city取下一个记录的主键id重复步骤3,4直到不满足查询条件为止对sort_buffer中的数据按照 字段name 进行排序遍历排序结果,取1000条,并按照 id的值回到原表取出city,name,age 三个字段返回给客户端

在rowid排序算法中,会有2次回表操作,相比于全字段排序, 多了一次回表操作

3. 实践经验

针对rowid排序算法,如何减少回表操作?

创建联合索引(city,name),由于联合索引本身就是有序的,所以只需要回表一次就可以了利用索引覆盖机制,也可以减少回表操作,比方说 创建一个索引(city,name,age),就不用再回表操作了5. 可能引起索引失效的几种情况对索引字段进行函数操作,可能会破坏索引值的有序性,导致优化器放弃走树搜索功能隐式类型转换,如里一个字段类型是varchar(32),结果使用的是整型,这里有隐式类型转化问题,可能导致索引失效隐式字符编码转换 ,如里一个表是utf8 ,一个表为uft8mb4,如果这两个表关联的话,可能用不上关联字段的 索引