使用 count(*) 统计后的字段作为 order by 的字段怎么优化

[ShutTheFu2kUP]

四百万行数据，GROUP BY 后统计，然后 DESC 排序后，还要分页

LOG( 统计该用户操作的日志表 )

id 主键
user_id 用户 ID
date 创建日期

SQL( date, user_id 这两个字段建立复合索引 )

SELECT
    user_id,
    count(*) AS count
FROM
    log
GROUP BY
    date, user_id
ORDER BY
    date DESC, user_id DESC
LIMIT 0, 10

以上 SQL 语句可以走索引，但是这时候如果要 count 字段进行排序，explain 就走全表了，执行了 1 分半，有其他办法优化吗？

SELECT
    user_id,
    count(*) AS count
FROM
    log
GROUP BY
    date, user_id
ORDER BY
    count DESC, date DESC, user_id DESC
LIMIT 0, 10

[setsunakute]

select `user`, count from (
SELECT
`date`,
user_id,
count(*) AS count
FROM
log
GROUP BY
date, user_id
) as a
order by count DESC, `date` DESC, user_id DESC limit 0, 10;
这样试试?

[ShutTheFu2kUP]

@setsunakute 貌似还是一个结果，子查询不走索引，我启动强制索引，虽然 explain 的 key 有索引，但是还是 row 还是全表的行数

[ShutTheFu2kUP]

是我自己傻了...子查询还是走索引的，只是因为子查询里没有 LIMIT，所以行数还是全表的行数...

[reus]

不走全表，是没可能算出结果的，你怎么优化都不能违背基本逻辑。
可以给 date 加范围条件，如果业务允许的话。

[ShutTheFu2kUP]

@reus 是的..在不重构表的情况下我也只能想到这个方法了..

[saulshao]

这种我之前的办法都是把 count 结果直接写到表里....然后查询这个表...

[zhengwhizz]

首先要确认你的业务场景，从语句来看只是要知道用户每天的操作次数，这其实属于数据统计了，你的日志表为原始数据表，每次请求都去拿原始表肯定很慢，所以要建立一个统计表(userid, count, date )，然后在每次用户有操作时 count 加 1 （实时性要求高的情况），或者定时脚本把前一天的统计了放进去。这种设计还可以满足时间段的统计，只需要 sum 下即可。

[Caballarii]

redis

[Leigg]

兄 die，你是要全表排序啊，怎么避免扫全表。需求，表设计，库选择，总有一个是有问题的。
非要在现有的基础上解决这个问题，楼上的建议是不错的。

[ShutTheFu2kUP]

@zhengwhizz 嗯，谢谢大佬，我的思路也是如果重构就用字段+1 的方式。定时统计也是一种解决办法，之前没有想到，感谢指导