最新消息:XAMPP默认安装之后是很不安全的,我们只需要点击左方菜单的 "安全"选项,按照向导操作即可完成安全设置。

MySQL SQL 为啥排序没了?

XAMPP相关 admin 23浏览 0评论

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


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)

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

 

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

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

转载请注明:XAMPP中文组官网 » MySQL SQL 为啥排序没了?