16 | “order by”是怎么工作的？

1 select city,name,age from t where city='杭州' order by name limit 1000 ; 的执行过程
2 using filesort 需要排序
3 mysql分配一块称为sort_buffer的内存用于排序
4 流程:
初始化buffer ,
从索引city找到为杭州的第一个主键id
到主键索引取出整条 , 再取3个字段的值存入buffer
继续取下一条主键id
重复上面的步骤
对buffer中的数据排序
取排序结果前1000行
5 上面的操作暂时称为”全字段排序” , 顾名思义
6 如果超出了buffer的size , 则需要用到磁盘临时文件来排序
7 一个查看是否是临时文件排序的方法

/* 打开optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 

/* @a保存Innodb_rows_read的初始值 */
select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 执行语句 */
select city, name,age from t where city='杭州' order by name limit 1000; 

/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

/* @b保存Innodb_rows_read的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 计算Innodb_rows_read差值 */
select @b-@a;

number_of_tmp_files字段

8 文件排序用到合并排序的思想
9 rowid排序 , 改进全字段排序 , 只放入要排序的name和主键id , 减少一行的size , 可放入更多数据
10 排序结束再用主键id值回主键索引里取出全部字段
11 mysql另一个设计思想 , 内存够则多利用内存, 减少磁盘访问 (感觉和mac的内存机制类似)
12 如果从索引中取出来的顺序就是有序的 , 则不需要临时表进行排序
13 using index == 使用了覆盖索引
14 思考题
假设你的表里面已经有了city_name(city, name)这个联合索引，然后你要查杭州和苏州两个城市中所有的市民的姓名，并且按名字排序，显示前100条记录。如果SQL查询语句是这么写的 ：
mysql> select * from t where city in ('杭州',"苏州") order by name limit 100;
那么，这个语句执行的时候会有排序过程吗，为什么？
如果业务端代码由你来开发，需要实现一个在数据库端不需要排序的方案，你会怎么实现呢？
进一步地，如果有分页需求，要显示第101页，也就是说语句最后要改成 “limit 10000,100”， 你的实现方法又会是什么呢？

需要排序 , 因为索引是根据city分段有序的,
如果在业务端实现 , 我会分别取杭州和苏州的 , 再做一次合并排序操作
分别取100 , merge后再取100