MySQL学习笔记-9-order by

在数据库实战开发的过程中，我们经常会遇到一些这样那样的问题，然后要卡好半天，等问题解决了才发现原来一些细节知识点还是没有掌握好。今天golang学习网就整理分享《MySQL学习笔记-9-order by》，聊聊MySQL，希望可以帮助到正在努力赚钱的你。

select city,name,age from t where city='杭州' order by name limit 1000;

I-1全字段排序执行过程

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

I-2全字段排序缺点：

1、造成sort_buffer中存放不下很多数据，因为除了排序字段还存放其他字段，对sort_buffer的利用效率不高
2、当所需排序数据量很大时，会有很多的临时文件，排序性能也会很差

I-3全字段排序优点：

MySQL认为内存足够大时会优先选择全字段排序，因为这种方式比rowid 排序避免了一次回表操作

全字段排序相关知识点：
MySQL会为每个线程分配一个内存（sort_buffer）用于排序该内存大小为sort_buffer_size：
如果排序的数据量小于sort_buffer_size，排序将会在内存中完成；
如果排序数据量很大，内存中无法存下这么多数据，则会使用磁盘临时文件来辅助排序，也称外部排序；
在使用外部排序时，MySQL会分成好几份单独的临时文件用来存放排序后的数据，然后在将这些文件合并成一个大文件。

II-1rowid排序执行过程

1、初始化sort_buffer，确定放入两个字段，即name和id；
2、从索引city找到第一个满足city='杭州’条件的主键id，也就是图中的ID_X；
3、到主键id索引取出整行，取name、id这两个字段，存入sort_buffer中；
4、从索引city取下一个记录的主键id；重复步骤3、4直到不满足city='杭州’条件为止，也就是图中的ID_Y；
5、对sort_buffer中的数据按照字段name进行排序；遍历排序结果，取前1000行，并按照id的值回到原表中取出city、name和age 三个字段返回给客户端。

II-2rowid排序缺点：

回表的操作是随机IO，会造成大量的随机读，不一定就比全字段排序减少对磁盘的访问

II-3rowid排序优点：

更好的利用内存的sort_buffer进行排序操作，尽量减少对磁盘的访问

rowid排序相关知识点：
mysql会通过遍历索引将满足条件的数据读取到sort_buffer，并且按照排序字段进行快速排序
如果查询的字段不包含在辅助索引中，需要按照辅助索引记录的主键返回聚集索引取出所需字段
该方式会造成随机IO，在MySQL5.6提供了MRR的机制，会将辅助索引匹配记录的主键取出来在内存中进行排序，然后在回表
按照情况建立联合索引来避免排序所带来的性能损耗，允许的情况下也可以建立覆盖索引来避免回表

III-1联合索引排序：创建一个city和name的联合索引

alter table t add index city_user(city, name);

1、从索引 (city,name) 找到第一个满足city='杭州’条件的主键id；
2、到主键id索引取出整行，取name、city、age三个字段的值，作为结果集的一部分直接返回；
3、从索引 (city,name) 取下一个记录主键id；
4、重复步骤2、3，直到查到第1000条记录，或者是不满足city='杭州’条件时循环结束。

III-2联合索引缺点：

维护索引需要成本

III-3联合索引优点：

查询过程不需要临时表，也不需要排序，性能优化很多。

IV-1覆盖索引排序：city、name和age的联合索引

alter table t add index city_user_age(city, name, age);

1、从索引 (city,name,age) 找到第一个满足city='杭州’条件的记录，取出其中的city、name和age这三个字段的值，作为结果集的一部分直接返回；
2、从索引 (city,name,age) 取下一个记录，同样取出这三个字段的值，作为结果集的一部分直接返回；
3、重复执行步骤2，直到查到第1000条记录，或者是不满足 city='杭州’条件时循环结束。

IV-2联合索引缺点：

维护索引需要成本

IV-3联合索引优点：

查询过程不需要临时表，也不需要排序，性能优化很多。

如何确定一个排序语句是否使用了临时文件：

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

好了，本文到此结束，带大家了解了《MySQL学习笔记-9-order by》，希望本文对你有所帮助！关注golang学习网公众号，给大家分享更多数据库知识！