mysql order by

/* 打开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;

你可以用上面介绍的方法，来确定一个排序语句是否使用了临时文件。

sort_buffer_size足够大对limit数据比较小的情况，优化效果是很明显的。

每个merge_buffers()都会增加sort_merge_passes

max_length_for_sort_data是为了让MySQL选择”< sort_key, rowid >”还是”< sort_key, additional_fields >”的模式。

而max_sort_length是键值对的大小无法确定时（比如用户要查询的数据包含了 SUBSTRING_INDEX(col1, ‘.’,2)）MySQL会对每个键值对分配max_sort_length个字节的内存，这样导致内存空间浪费，磁盘外部排序次数过多。

read_rnd_buffer_size：由于row ID不是顺序的，导致回表时是随机IO，为了进一步优化性能（变成顺序IO），MySQL会读一批row ID，并将读到的数据按排序字段顺序插入缓存区中(内存大小read_rnd_buffer_size)。

innodb_sort_buffer_size

这个参数其实跟我们这里讨论的SQL排序没有什么关系。innodb_sort_buffer_size设置的是在创建InnoDB 索引时，使用到的sort buffer的大小。 innodb_sort_buffer_size 可以提高数据的写入索引的速度.

order by 优化建议

排序和查询的字段尽量少。只查询你用到的字段，不要使用select * ；使用limit查询必要的行数据；

要排序或者查询的字段，尽量不要用不确定字符函数，避免MySQL直接分配max_sort_length，导致sort buffer空间不足；

使用索引来优化或者避免排序；

增加sort_buffer_size大小，避免磁盘排序；

不得不使用original 排序算法时，增加read_rnd_buffer_size；

字段长度定义合适就好（避免过长）；

tmpdir建议独立存放，放在高速存储设备上。