问答博客资讯标签用户活动
logo极客观点logo项目管理logoHarmonyOS
开发者社区
javascript
前端
python
node.js
react
vue.js
php
laravel
go
人工智能
mysql
linux
ios
java
android
css
typescript
spring
程序员
logoONES 研发管理logo思否企业问答logo安谋科技 XPU

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

头像
yanwushu
    1.2k24149201

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

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

    查询计划如下图：

    image.png

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

    已解决

    方案1：

    SELECT t.id, t.uid, t.address, t.create_time
FROM ci_trail t
JOIN (
    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的内容是常量，所以会快一些。

    sql优化mysql
    阅读 438
    avatarAI BotBETA

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

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

    CREATE INDEX idx_uid_id ON ci_trail(uid, id);

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

    SELECT t1.*
FROM ci_trail t1
JOIN (
    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 表中有大量数据时运行较慢。如果这是一个问题，你可能需要考虑一些更复杂的方法，比如使用数据库特定的功能，如分区或复制，或者在应用程序级别进行一些优化，比如限制需要查询的用户数量。

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

    撰写回答
    你尚未登录，登录后可以
    • 和开发者交流问题的细节
    • 关注并接收问题和回答的更新提醒
    • 参与内容的编辑和改进，让解决方法与时俱进
    推荐问题