1、数据源:获取deviceid为
09CBA754DE9C4669AF49
最新的主机信息
mysql> select deviceid,hostname,local_ip,from_unixtime(lasttime) from hosts where instr(local_ip,"144.217");
+----------------------+----------------------------------+---------------+-------------------------+
| deviceid | hostname | local_ip | from_unixtime(lasttime) |
+----------------------+----------------------------------+---------------+-------------------------+
| 09CBA754DE9C4669AF49 | redhat6.7 | 10.21.144.217 | 2020-11-24 15:54:34 |
| 09CBA754DE9C4669AF49 | redhat6.7 | 10.21.144.217 | 2020-12-01 14:45:06 |
| 09CBA754DE9C4669AF49 | redhat-6.7 | 10.21.144.217 | 2020-12-02 11:14:33 |
| 09CBA754DE9C4669AF49 | r | 10.21.144.217 | 2020-11-12 08:38:54 |
| 09CBA754DE9C4669AF49 | redhat6.701234567890123456789012 | 10.21.144.217 | 2020-11-12 08:45:20 |
| 09CBA754DE9C4669AF49 | redhat6.7 | 10.21.144.217 | 2020-11-16 08:28:19 |
| 09CBA754DE9C4669AF49 | redhat-6.7 | 10.21.144.217 | 2020-12-02 14:02:07 |
+----------------------+------
2、根据时间降序获取最新的hostname(redhat-6.7):
mysql> select deviceid,hostname,local_ip,from_unixtime(lasttime) from hosts where instr(local_ip,"144.217") order by lasttime desc;
+----------------------+----------------------------------+---------------+-------------------------+
| deviceid | hostname | local_ip | from_unixtime(lasttime) |
+----------------------+----------------------------------+---------------+-------------------------+
| 09CBA754DE9C4669AF49 | redhat-6.7 | 10.21.144.217 | 2020-12-02 14:05:18 |
| 09CBA754DE9C4669AF49 | redhat-6.7 | 10.21.144.217 | 2020-12-02 11:14:33 |
| 09CBA754DE9C4669AF49 | redhat6.7 | 10.21.144.217 | 2020-12-01 14:45:06 |
| 09CBA754DE9C4669AF49 | redhat6.7 | 10.21.144.217 | 2020-11-24 15:54:34 |
| 09CBA754DE9C4669AF49 | redhat6.7 | 10.21.144.217 | 2020-11-16 08:28:19 |
| 09CBA754DE9C4669AF49 | redhat6.701234567890123456789012 | 10.21.144.217 | 2020-11-12 08:45:20 |
| 09CBA754DE9C4669AF49 | r | 10.21.144.217 | 2020-11-12 08:38:54 |
+----------------------+----------------------------------+---------------+-------------------------+
7 rows in set (0.00 sec)
3、根据时间先降序后聚合--不加limit 10000
mysql> select * from ( select deviceid,hostname,local_ip,from_unixtime(lasttime) from hosts where instr(local_ip,"144.217") order by lasttime desc ) tt group by tt.deviceid;
+----------------------+-----------+---------------+-------------------------+
| deviceid | hostname | local_ip | from_unixtime(lasttime) |
+----------------------+-----------+---------------+-------------------------+
| 09CBA754DE9C4669AF49 | redhat6.7 | 10.21.144.217 | 2020-11-24 15:54:34 |
+----------------------+-----------+---------------+-------------------------+
1 row in set (0.00 sec)
不加limit 10000,发现排序居然没有生效,得到的结果不是我们想要的。
4、根据时间先降序后聚合--
加limit 10000(必须要有,不然子查询不会被执行)
:得到的结果准确
mysql> select * from ( select deviceid,hostname,local_ip,from_unixtime(lasttime) from hosts where instr(local_ip,"144.217") order by lasttime desc limit 100000 ) tt group by tt.deviceid;
+----------------------+------------+---------------+-------------------------+
| deviceid | hostname | local_ip | from_unixtime(lasttime) |
+----------------------+------------+---------------+-------------------------+
| 09CBA754DE9C4669AF49 | redhat-6.7 | 10.21.144.217 | 2020-12-02 14:05:18 |
+----------------------+------------+---------------+-------------------------+
1 row in set (0.01 sec)
5、explain 查看执行计划,
发现在没有 limit 的情况,会少了一个derived 操作,mysql会认为这时候不需要排序,内部做了优
化,
所以这种情况下加limit限制就可以了:
![](https://i-blog.csdnimg.cn/blog_migrate/996a649297094505808b8f98afb350d6.png)
参考: