mysql - 一个有关大表分组查询优化的问题？

Question

有ci_trail表，字段为：id, uid(用户id), address（地址）, create_time 记录人的定位轨迹，此表大概有100w条数据。想查询每个人最新的一条地址信息。使用如下sql：

explainselect id, uid, address, create_time from ci_trail where id in(    select max(id) from ci_trail group by uid);

查询计划如下图：

可见进行了全表扫描，查询效率很低，请问这种情况应该如何优化sql？

已解决

方案1：

SELECT t.id, t.uid, t.address, t.create_timeFROM ci_trail tJOIN (    SELECT uid, MAX(id) AS max_id    FROM ci_trail    GROUP BY uid) t2 ON t.uid = t2.uid AND t.id = t2.max_id;

方案2：
先将子查询中的id查询出来，然后将id的结果集逗号隔开填充到in中。因为in的内容是常量，所以会快一些。

Answer 1

这个问题可以通过使用索引来优化。在这个情况下，我们需要在 ci_trail 表的 uid 和 id 字段上创建索引。

创建索引可以增加查询的速度，因为它可以帮助数据库更快地查找和排序数据。在 MySQL 中，您可以使用以下命令来创建索引：

CREATE INDEX idx_uid_id ON ci_trail(uid, id);

然后，你的查询可以这样写：

SELECT t1.*FROM ci_trail t1JOIN (    SELECT uid, MAX(id) AS max_id    FROM ci_trail    GROUP BY uid) t2 ON t1.uid = t2.uid AND t1.id = t2.max_id;

这个查询首先找到每个 uid 的最大 id，然后将结果与原始表进行连接，以获取每个用户的最新记录。

请注意，这个查询可能在 ci_trail 表中有大量数据时运行较慢。如果这是一个问题，你可能需要考虑一些更复杂的方法，比如使用数据库特定的功能，如分区或复制，或者在应用程序级别进行一些优化，比如限制需要查询的用户数量。

另外，请注意，无论使用何种查询，都需要对数据库进行适当的优化，比如使用正确的索引，以及确保查询的逻辑和索引的顺序相匹配。这通常可以通过分析查询计划并查看数据库是如何执行查询的来实现。