MySQL SQL 为啥排序没了

今天分析一个特殊的案例， 非常有意思

root@mysql3357.sock>[employees]>show create table salaries2 G
*************************** 1. row ***************************
       Table: salaries2
Create 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=utf8mb4
1 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 by
 to_date desc  )c where num = 1;
+----+-------------+------------+------------+-------+---------------+-------------+---------+-------+---------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key         | key_len | ref   | rows    | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+-------------+---------+-------+---------+----------+-------------+
|  1 | PRIMARY     |  | NULL       | ref   |    |  | 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 warningsG
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* 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` desc 
 order 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 by
 to_date desc  )c where num = 1;
+----+-------------+------------+------------+-------+---------------+-------------+---------+-------+---------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key         | key_len | ref   | rows    | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+-------------+---------+-------+---------+----------+-------------+
|  1 | PRIMARY     |  | NULL       | ref   |    |  | 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 这里，有个 而且是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     |  | 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)

最终结果跟我们的预期一致

我们在学习的过程中，碰到很多稀奇古怪的问题，但是我们还是从原理出发

一步一步 慢慢的分析并且掌握

转自公众号：SQL开发与优化