select * from (
select
f.device_id,
f.point_id,
f.type
from h_device_pointvalue f
ORDER BY time desc) f1
GROUP BY f1.point_id
这个sql的功能是 找到一个设备上全部全部寄存器上的最新的状态
表结构如下
id device_id point_id type time
1 1 1 2 2016-01-29 12:12:12
2 1 1 3 2016-01-29 12:12:13
3 1 2 1 2016-01-28 00:00:00
4 2 1 1 2016-01-29 00:01:01
5 2 2 2 2016-01-29 11:01:21
6 2 2 3 2016-02-02 00:00:00
select
f.device_id,
f.point_id,
f.type
from h_device_pointvalue f
ORDER BY time desc) f1
GROUP BY f1.point_id
这个sql的功能是 找到一个设备上全部全部寄存器上的最新的状态
表结构如下
id device_id point_id type time
1 1 1 2 2016-01-29 12:12:12
2 1 1 3 2016-01-29 12:12:13
3 1 2 1 2016-01-28 00:00:00
4 2 1 1 2016-01-29 00:01:01
5 2 2 2 2016-01-29 11:01:21
6 2 2 3 2016-02-02 00:00:00
解决方案
5
5
explain下吧
10
大致 实验了一下 楼上的 办法， 结论和 ls的建议相反，针对lz的这个查询， 联合索引的 数据顺序
应该是time 在前边， 而 device id 在后边
本人就用emp表的 变形eee 做的实验， time 换成deptno， deviceid 换成empno 就理解了
mysql> explain select * from (select deptno,empno,ename from eee order by empno desc)f group by f.deptno;
+–+–+–+–+–+–+–+–+–+–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+–+–+–+–+–+–+–+–+–+–+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 136 | Using temporary; Using filesort |
| 2 | DERIVED | eee | ALL | NULL | NULL | NULL | NULL | 136 | Using filesort |
+–+–+–+–+–+–+–+–+–+–+
2 rows in set
mysql> explain select * from (select deptno,empno,ename from eee order by deptno desc)f group by f.empno;
+–+–+–+–+–+–+–+–+–+–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+–+–+–+–+–+–+–+–+–+–+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 136 | Using temporary; Using filesort |
| 2 | DERIVED | eee | index | NULL | deptno_empno_ename | 22 | NULL | 136 | Using index |
+–+–+–+–+–+–+–+–+–+–+
2 rows in set
mysql> show index from eee;
+–+–+–+–+–+–+–+–+–+–+–+–+–+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+–+–+–+–+–+–+–+–+–+–+–+–+–+
| eee | 1 | deptno_empno_ename | 1 | deptno | A | 136 | NULL | NULL | YES | BTREE | | |
| eee | 1 | deptno_empno_ename | 2 | empno | A | 136 | NULL | NULL | | BTREE | | |
| eee | 1 | deptno_empno_ename | 3 | ename | A | 136 | NULL | NULL | YES | BTREE | | |
+–+–+–+–+–+–+–+–+–+–+–+–+–+
3 rows in set
应该是time 在前边， 而 device id 在后边
本人就用emp表的 变形eee 做的实验， time 换成deptno， deviceid 换成empno 就理解了
mysql> explain select * from (select deptno,empno,ename from eee order by empno desc)f group by f.deptno;
+–+–+–+–+–+–+–+–+–+–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+–+–+–+–+–+–+–+–+–+–+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 136 | Using temporary; Using filesort |
| 2 | DERIVED | eee | ALL | NULL | NULL | NULL | NULL | 136 | Using filesort |
+–+–+–+–+–+–+–+–+–+–+
2 rows in set
mysql> explain select * from (select deptno,empno,ename from eee order by deptno desc)f group by f.empno;
+–+–+–+–+–+–+–+–+–+–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+–+–+–+–+–+–+–+–+–+–+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 136 | Using temporary; Using filesort |
| 2 | DERIVED | eee | index | NULL | deptno_empno_ename | 22 | NULL | 136 | Using index |
+–+–+–+–+–+–+–+–+–+–+
2 rows in set
mysql> show index from eee;
+–+–+–+–+–+–+–+–+–+–+–+–+–+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+–+–+–+–+–+–+–+–+–+–+–+–+–+
| eee | 1 | deptno_empno_ename | 1 | deptno | A | 136 | NULL | NULL | YES | BTREE | | |
| eee | 1 | deptno_empno_ename | 2 | empno | A | 136 | NULL | NULL | | BTREE | | |
| eee | 1 | deptno_empno_ename | 3 | ename | A | 136 | NULL | NULL | YES | BTREE | | |
+–+–+–+–+–+–+–+–+–+–+–+–+–+
3 rows in set
CodeBye 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权 , 转载请注明怎么样提高mysql 排序+分组的效率问题！
