今天分析一个特殊的案例， 非常有意思
root@mysql3357.sock>[employees]>show create table salaries2 \G*************************** 1. row ***************************Table: salaries2Create Table: CREATE TABLE `salaries2` (`emp_no` int(11) NOT NULL,`salary` int(11) NOT NULL,`from_date` date NOT NULL,`to_date` date NOT NULL,KEY `ix_to_date` (`to_date`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb41 row in set (0.00 sec)root@mysql3357.sock>[employees]>desc select ai,num from ( select count(1) num , to_date ai from salaries2 group by to_date order byto_date desc )c where num = 1;+----+-------------+------------+------------+-------+---------------+-------------+---------+-------+---------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+------------+------------+-------+---------------+-------------+---------+-------+---------+----------+-------------+| 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 8 | const | 10 | 100.00 | NULL || 2 | DERIVED | salaries2 | NULL | index | ix_to_date | ix_to_date | 3 | NULL | 2752484 | 100.00 | Using index |+----+-------------+------------+------------+-------+---------------+-------------+---------+-------+---------+----------+-------------+2 rows in set, 1 warning (0.01 sec)root@mysql3357.sock>[employees]>select ai,num from ( select count(1) num , to_date ai from salaries2 group by to_date order by to_date desc )c where num = 1 limit 10;+------------+-----+| ai | num |+------------+-----+| 1985-12-09 | 1 || 1985-10-23 | 1 || 1985-09-08 | 1 || 1985-06-27 | 1 || 1985-03-11 | 1 || 1985-12-08 | 1 || 1985-09-01 | 1 || 1985-06-22 | 1 || 1985-03-01 | 1 || 1985-08-29 | 1 |+------------+-----+10 rows in set (1.59 sec)
如上所示，SQL 中有 order by to_date desc 本应该在从大到小排序 但是从执行结果中，可以发现跟我们想的不一样
那这是为啥嗯？又应该怎么解决呢 ?
root@mysql3357.sock>[employees]>show warnings\G*************************** 1. row ***************************Level: NoteCode: 1003Message: /* select#1 */ select `c`.`ai` AS `ai`,`c`.`num` AS `num`from (/* select#2 */ select count(1) AS `num`,`employees`.`salaries2`.`to_date` AS `ai`from `employees`.`salaries2`group by `employees`.`salaries2`.`to_date` descorder by `employees`.`salaries2`.`to_date` desc) `c`where (`c`.`num` = 1)1 row in set (0.00 sec)
我们一般碰到这种问题，就会看show warnings 看下是否是因为SQL 转换过程中发生省略order by 结果发现没有， order by 还在。
root@mysql3357.sock>[employees]>desc select ai,num from ( select count(1) num , to_date ai from salaries2 group by to_date order byto_date desc )c where num = 1;+----+-------------+------------+------------+-------+---------------+-------------+---------+-------+---------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+------------+------------+-------+---------------+-------------+---------+-------+---------+----------+-------------+| 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 8 | const | 10 | 100.00 | NULL || 2 | DERIVED | salaries2 | NULL | index | ix_to_date | ix_to_date | 3 | NULL | 2752484 | 100.00 | Using index |+----+-------------+------------+------------+-------+---------------+-------------+---------+-------+---------+----------+-------------+2 rows in set, 1 warning (0.01 sec)
我们再看下执行计划，看看有没有可分析的地方，这个执行计划
第一部分ID=2 可以看出使用了 ix_to_date 这个索引，从而替代了排序
再看第二部分id=1 这里，有个 <auto_key0> 而且是ref 从这里可以推出
这是SQL中对应的是 where num = 1;
也就是说，第一部分用（id=2）日期是有排序的，
但是在第二部分有生成了临时索引，这个索引是以key num 我们知道索引是有排序的
也就是说这时候 排序是以num 所以最终结果是日期没有顺序
那原因知道了，我们应该怎么解决呢 ？
我们还是从执行计划出发，我们只需要不产生auto key 就可以了
那我们修改SQL如下
root@mysql3357.sock>[employees]>desc select count(1) num , to_date ai from salaries2 group by to_date having num=1 order by to_date desc-> ;+----+-------------+-----------+------------+-------+---------------+------------+---------+------+---------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-----------+------------+-------+---------------+------------+---------+------+---------+----------+-------------+| 1 | SIMPLE | salaries2 | NULL | index | ix_to_date | ix_to_date | 3 | NULL | 2752484 | 100.00 | Using index |+----+-------------+-----------+------------+-------+---------------+------------+---------+------+---------+----------+-------------+1 row in set, 1 warning (0.00 sec)root@mysql3357.sock>[employees]> select count(1) num , to_date ai from salaries2 group by to_date having num=1 order by to_date desc limit 10 ;+-----+------------+| num | ai |+-----+------------+| 1 | 1986-01-31 || 1 | 1986-01-29 || 1 | 1986-01-28 || 1 | 1986-01-24 || 1 | 1986-01-18 || 1 | 1986-01-14 || 1 | 1986-01-12 || 1 | 1986-01-03 || 1 | 1985-12-31 || 1 | 1985-12-29 |+-----+------------+10 rows in set (1.61 sec)
可以看出，ai 部分是倒叙的
这是我们用了 having 省略一层子查询来达到省略autokey 的目的
那还有没有别的方法呢 ？
root@mysql3357.sock>[employees]>desc select ai,num from ( select count(1) num , to_date ai from salaries2 group by to_date order by to_date desc )c where num+0 = 1;+----+-------------+------------+------------+-------+---------------+------------+---------+------+---------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+------------+------------+-------+---------------+------------+---------+------+---------+----------+-------------+| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 2752484 | 100.00 | Using where || 2 | DERIVED | salaries2 | NULL | index | ix_to_date | ix_to_date | 3 | NULL | 2752484 | 100.00 | Using index |+----+-------------+------------+------------+-------+---------------+------------+---------+------+---------+----------+-------------+
我们想省略autokey ,那怎么弄呢？我们知道几个不能用索引的规则
其中有条是对索引列进行加工就可以
所以我们修改成如下 where num+0 = 1;
root@mysql3357.sock>[employees]> select ai,num from ( select count(1) num , to_date ai from salaries2 group by to_date order by to_date desc )c where num+0 = 1 limit 10 ;+------------+-----+| ai | num |+------------+-----+| 1986-01-31 | 1 || 1986-01-29 | 1 || 1986-01-28 | 1 || 1986-01-24 | 1 || 1986-01-18 | 1 || 1986-01-14 | 1 || 1986-01-12 | 1 || 1986-01-03 | 1 || 1985-12-31 | 1 || 1985-12-29 | 1 |+------------+-----+10 rows in set (1.74 sec)
最终结果跟我们的预期一致
我们在学习的过程中，碰到很多稀奇古怪的问题，但是我们还是从原理出发
一步一步 慢慢的分析并且掌握
转载请注明：XAMPP中文组官网 » MySQL SQL 为啥排序没了?