如何优化MySQL查询（分组和排序）

Question

大家好，我有一个需要优化的查询。它能运行，但在性能方面表现不佳。

查询语句如下：

SELECT  *
FROM    (
        SELECT  *
        FROM    views
        WHERE   user_id = '1'
        ORDER BY
                page DESC
        ) v
GROUP BY
        v.session

我正在追踪不同页面的浏览量，并想知道每个会话中最高的页面，以便了解他们在任何给定会话中点击了多远（他们需要查看每个页面直到最后）。

基本上我想做的是在分组前对结果进行排序。以上方法能够实现，但代价很大。

有谁能告诉我如何做到这一点吗？谢谢！

更新：

说明：

"1" "PRIMARY"   "<derived2>"    "ALL"   \N  \N  \N  \N  "3545"  "Using temporary; Using filesort"

"2" "DERIVED"   "views" "index" \N  "page"  "5" \N  "196168"    "Using where"

模式：

ID       int(8) unsigned  (NULL)     NO      PRI     (NULL)   auto_increment  select,insert,update,references         
page     int(8)           (NULL)     YES     MUL     (NULL)                   select,insert,update,references         
user_id  int(8)           (NULL)     YES             (NULL)                   select,insert,update,references         
session  int(8)           (NULL)     YES             (NULL)                   select,insert,update,references         
created  datetime         (NULL)     NO                                       select,insert,update,references       

索引信息：

views            0  PRIMARY              1  ID           A               196008    (NULL)  (NULL)          BTREE    

views            1  page                 1  page         A                  259    (NULL)  (NULL)  YES     BTREE 

- thekevinscott

---

当询问mysql性能优化时，请始终提供以下内容：1.查询语句 2.EXPLAIN查询输出 3.适用表的SHOW CREATE TABLE。谢谢。 - hobodave

你能发布一些关于你的模式的细节吗？ - Macros

Answer 1

我正在追踪不同页面的浏览量，并想知道每个会话中最高的页面，以便了解他们在任何给定会话中点击了多远（他们需要完整查看每个页面直到结尾）。在进行分组之前排序是一种非常不可靠的方法。MySQL扩展了GROUP BY语法：您可以在SELECT和ORDER BY子句中使用未分组和未聚合的字段。在这种情况下，每个会话都输出page的随机值。文档明确指出，您永远不应该假设它将是哪个值。然而，在实践中，将返回从第一行扫描的值。

由于您在子查询中使用了 ORDER BY page DESC，因此该行恰好是每个会话中具有最大 page 的行。

您不应该依赖它，因为这种行为未经记录，如果在下一个版本中返回其他行，则不会被视为错误。

但是您甚至不必使用这样的恶意技巧。

只需使用聚合函数即可：

SELECT  MAX(page)
FROM    views
WHERE   user_id = '1'
GROUP BY
        session

这是一种文档完备且规范的方式，可以实现您想要的功能。

为了使查询运行更快，请在 (user_id, session, page) 上创建复合索引。

如果您需要表中的所有列，而不仅仅是聚合列，请使用以下语法：

SELECT  v.*
FROM    (
        SELECT  DISTINCT user_id, session
        FROM    views
        ) vo
JOIN    views v
ON      v.id =
        (
        SELECT  id
        FROM    views vi
        WHERE   vi.user_id = vo.user_id
                AND vi.session = vo.session
        ORDER BY
                page DESC
        LIMIT 1
        )

这假设 views 表中的 id 是一个 PRIMARY KEY。

- Quassnoi

---

创建一个由(user_id、session、page)组成的复合索引，以使查询运行更快。这个语句为我节省了很多时间。谢谢！！！ - Sadikhasan

Answer 2

我认为你的子查询是不必要的。你可以通过这个更简单（且更快）的查询获得相同的结果：

SELECT *
FROM views 
WHERE user_id = '1' 
GROUP BY session
ORDER BY page DESC

此外，您应该在每个您正在分组、排序或“where-ing”的字段上建立索引。在这种情况下，您需要在user_id、session和page上建立索引。

- Roee Adler

---

你比我快了——子查询是完全不必要的，而ORDER BY应该仅应用于最终的SELECT语句。 - OMG Ponies

@op 试图仅为每个会话检索 MAX(page)。对未分组和未聚合的列进行排序和选择是 MySQL 的一种扩展，它输出每个会话 ID 中遇到的第一个 page。当 GROUP BY 应用于子查询时，该 page 恰好是每个会话中最小的 page，这产生了 SELECT 中的 MAX(page)。您的解决方案不能保证首先返回最小页面。 - Quassnoi

我的查询使用了内连接。两个表进行连接的字段没有被索引。将其添加到索引中解决了我的问题。 - Usman Shaukat

Answer 3

我建议在user_id和page上创建一个复合（多列）索引。这假设内部查询是较慢的部分。

- George Mastros

Answer 4

问题出在子查询上。SELECT * FROM (SELECT * FROM)

你应该使用连接操作。你的“page”字段是什么数据类型？

- hobodave