和⾯试官聊聊MySQL排序的坑（含：orderbylimit分页出现重复数据问题）1. ⾯试官：MYSQL按多个字段排序需要注意哪些问题？
这是准备考我实操经验呀！巧了前⼀段时间遇到过类似的坑。针对多字段进⾏组合排序时，⾸先order by 后可加多个字段时，字段之间⽤英⽂逗号隔开；其次优先级按照字段先后顺序排序；最后需要针对每个字段分别进⾏⾃定义排序；如果不指定，order by 默认按照升序（ASC）排序。
win10卡死了按哪都按不动⽰例:
表数据如下：
SQL1：
select*from user ORDER BY age, userName desc limit8;
这⾥我们想实现的效果是先按照age倒序排序，再按照userName倒序排序。实际效果是先按照age正序排序，再按照userName倒序排序。
修改SQL如下，即可实现全部倒序的效果：
select*from user ORDER BY age desc, userName desc limit8;
2. ⾯试官：有没有遇到过order by limit分页出现数据重复的问题？怎么解决的？
在MySQL中我们通常使⽤limit关键字进⾏分页查询，⽐如limit(0,10)表⽰列出第⼀页的10条数据，limit(10,10)表⽰列出第⼆页。但是，当limit遇到order by的时候，可能会出现翻到第⼆页的时候，竟然⼜出现了第⼀页的记录。有时很难复现，
SQL如下：
select*from user where gender =1ORDER BY age desc limit5;
select*from user where gender =1ORDER BY age desc limit5,5;
解决⽅案：
javaee eclipse1、索引排序字段，即如果在字段上添加索引，就直接按照索引的有序性进⾏读取并分页，从⽽可以规避遇到的这个问题。
2、如果排序字段都没有索引，则业务上⼀般选择在order by的最后加上id asc，也就是在原有排序的基础上排序结果按照主键ID正序排序。因为MYSQL默认按照主键ID正序排序，所以对原结果不会有影响。相应SQL如下：
select*from user where gender =1ORDER BY age desc, id asc limit5;
select*from user where gender =1ORDER BY age desc, id asc limit5,5;
3、建议使⽤order by limit进⾏分页查询时，order by 的最后jiashang id asc。
原因分析：
在MySQL 5.6的版本上，优化器在遇到order by limit语句的时候，做了⼀个优化，即使⽤priority queue做堆排序；以达到 在不能使⽤索引有序性的时候，如果要排序，并且使⽤了limit n，可以在排序的过程中在保留n条记录 的效果；进⽽减少sort buffer内存的使⽤。也就是说MYSQL5.x低版本没这个问题。简单的编程游戏
但是因为堆排序是⼀个不稳定的排序⽅法，即相同的值可能排序出来的结果和读出来的数据顺序不⼀致。
从SQL的执⾏顺序来看，在进⾏order by 排序时，所有的记录都是以堆排序的⽅式来排列的，若排序字段不具备索引有序性，则在第⼆页数据中，MYSQL见到哪条拿哪条。contenteditable删除无效
另外在功能上，排序是数据库提供的基础功能，⽽分页是衍⽣出来的应⽤需求。MYSQL和Oracle提供
api管理工具的limit n和rownum < n的⽅法，都没有明确定义分页的概念，不同场景对数据分页都没有⾮常⾼的准确性要求。
3. ⾯试官：既然你提到了MySQL5.x低版本不会有排序分页数据重复的问题，聊⼀下MySQL5.x低版本的排序是怎做的？
有两种⽅式，分别是：常规排序、优化排序，MySQL5.6以上采⽤优先队列排序（堆排序算法）。
常规排序
1> 从 保存满⾜where条件的全部记录的临时表中获取全部记录；
2> 将要排序的字段值和row ID 组成键值对（例如：(id, age)），存放到sort buffer中；
3> 如果sort buffer可以存放所有满⾜条件的(id,age)对，则直接采⽤快速排序算法进⾏排序；否则sort buffer满后，进⾏排序并持久化到临时⽂件中；
4> 若排序中产⽣了临时⽂件，则利⽤归并排序算法，保证临时⽂件中记录是有序的；
5> 循环执⾏上述过程，直到所有满⾜条件的记录全部参与排序；
6> 扫描排好序的(id,age)键值对，并利⽤id回表去捞取SELECT需要返回的列（⽐如(age,name)）；
mysql面试题型7> 将获取的结果集返回给⽤户。
优化排序
常规排序除了排序本⾝，还需要额外两次IO：回表、排序存到临时⽂件。优化排序相对于常规排序，减少了回表的IO损耗。主要区别在于放⼊sort buffer的键值对不再由要排序的字段和row ID组成(⽐如：(id,age))；⽽是查询的字段和要排序的字段（⽐如：(age,name)）。只是这种⽅式是有代价的，同样⼤⼩的sort buffer，能存放的(age,name)数⽬要⼩于(id,age)，如果sort buffer不够⼤，可能导致原本不需要写临时⽂件的排序需要写临时⽂件，造成额外的IO。
不过这种⽅式是有开启条件的： 参数max_length_for_sort_data，只有当排序元组⼩于max_length_for_sort_data时，才能利⽤优化排序⽅式，否则只能⽤常规排序⽅式。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。