MySQL SQL 为啥排序没了

  • A+
所属分类:体育平台

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




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     | <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: 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     | <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)


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


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

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

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


MySQL SQL 为啥排序没了

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: