如何提高mysql 排序+分组的效率问题

灰原哀和柯南 2016-01-29 02:37:57
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
...全文
391 5 打赏 收藏 转发到动态 举报
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
allen519 2016-02-11
  • 打赏
  • 举报
回复
大概 实验了一下 楼上的 办法, 结论和 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
allen519 2016-02-11
  • 打赏
  • 举报
回复
set profiling =1; select ..... from ... show profiles ; 中间 可以用 reset query cache ; show profile for query NO. ; 另外 就是在time 和device_ id 上建立联合索引, 但是 具体谁在左边 我现在还不是很确定
  • 打赏
  • 举报
回复
创建索引: create index idx_h_device_pointvalue_t on h_device_pointvalue(device_id,time desc) 另外,语句建议改为: select * from ( select f.device_id, f.point_id, f.type from h_device_pointvalue f ORDER BY device_id,time desc) f1 GROUP BY f1.point_id
rick-he 2016-01-30
  • 打赏
  • 举报
回复
explain下吧
ACMAIN_CHM 2016-01-29
  • 打赏
  • 举报
回复
以文本方式贴出 explain select ... show index from .. 以供分析。

56,675

社区成员

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧