代码之家 › 专栏 › 技术社区 › Josh K

帮助优化mysql查询

query-optimization mysql

Josh K · 技术社区 · 14 年前

我有一个mysql查询,如下所示

SELECT
    count(`clicks`.`user_id`) as total,
    `users`.`fullname`
FROM
    `users`,
    `clicks`,
WHERE
    `users`.`id` = `clicks`.`user_id`
GROUP BY
    `clicks`.`user_id`
ORDER BY
    `total` desc
LIMIT
    0,20;

我正在统计一些按键式游戏。它有一个用户表和一个点击表。它记录来自特定用户的单击。用户可以随时单击该按钮。一天点击20次,另一天点击30次,等等。它们不是一排的。

目前约有180千次点击,可供2千名用户使用。此查询平均运行时间为1.38秒。如果可能的话,我想加快速度。

6 回复 | 直到 14 年前

OMG Ponies 14 年前

是 USERS.id 是否定义为表的主键?应该是…
假设是InnoDB, CLICKS.user_id 对其具有外键约束,以便将其值与 用户标识 ?
用户标识 和 Culk.UsSeriID 是数字数据类型(即:int),而不是基于文本?
应为以下项添加索引(如果索引不存在):
- Culk.UsSeriID
- USERS.fullname
如果存在索引, have you tried refreshes the table statistics :
```
ANALYZE TABLE USERS;
ANALYZE TABLE CLICKS;
```

Teekin 14 年前

如果您对columns clicks.user_id和users.id进行了索引,那么该查询可能与它得到的速度一样快。

关于它,有一点我可以想象,要为很多的迟缓负责,那就是按条款排序。既然它是一个聚合字段,那么它可能必须首先获取所有数据,然后在没有太多优化的情况下对其进行排序。当事情进展缓慢时,排序是一个很好的候选者。

不过,另一个想法是维护一个包含单击总数的单独表。如果您需要这些记录,那么您可能最终不得不每次单击运行2个查询…一个用于现有表,另一个用于更新用户/单击表,该表只有用户ID和单击计数以及您认为合适的其他内容。这样,即使有大量的用户,选择也会变得非常快,因为您只检索绝对必要的最小行数,而不是一大堆行数,这些行数无论如何都只能聚合。

VeeArr 14 年前

确保已在上创建索引 users.id 和 clicks.user_id . 您也可以尝试在执行联接之前计算单击次数,但我怀疑如果这实际上能够提高性能,那么引擎无论如何都会为您做这件事。

Peter Tillemans 14 年前

为初学者在clicks.userid上创建索引。这会有所不同

Teekin 14 年前

我只是想到别的事情。使用内部联接可能会获得更好的结果。

未经测试的:

SELECT
    count(`clicks`.`user_id`) as total,
    `users`.`fullname`
FROM
    `users`
    INNER JOIN `clicks` ON `clicks`.`user_id` = `users`.`id`
GROUP BY
    `clicks`.`user_id`
ORDER BY
    `total` desc
LIMIT
    0,20;

Matt 14 年前

尝试此操作(未测试):

SELECT
    C.total,
    `users`.`fullname`
FROM
    `users`
INNER JOIN
    (SELECT COUNT(*) AS total, user_id 
     FROM 
         `clicks` 
     GROUP BY 
        `user_id` 
     ORDER BY 
        COUNT(*) 
     LIMIT 0,20) C
ON C.user_id = users.user_id

ORDER BY
    C.total desc

先数行可能会节省一些时间。