logo标签列表

使用多个分组或排序的MySQL查询优化

mysqlsqlquery-optimization
5

更新：表和索引定义

desc activities;x
+----------------+--------------+------+-----+---------+  
| Field          | Type         | Null | Key | Default |  
+----------------+--------------+------+-----+---------+  
| id             | int(11)      | NO   | PRI | NULL    |  
| trackable_id   | int(11)      | YES  | MUL | NULL    |  
| trackable_type | varchar(255) | YES  |     | NULL    |  
| owner_id       | int(11)      | YES  | MUL | NULL    |  
| owner_type     | varchar(255) | YES  |     | NULL    |  
| key            | varchar(255) | YES  |     | NULL    |  
| parameters     | text         | YES  |     | NULL    |  
| recipient_id   | int(11)      | YES  | MUL | NULL    |  
| recipient_type | varchar(255) | YES  |     | NULL    |  
| created_at     | datetime     | NO   |     | NULL    |  
| updated_at     | datetime     | NO   |     | NULL    |  
+----------------+--------------+------+-----+---------+  

show indexes from activities;

+------------+------------+-----------------------------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+  
| Table      | Non_unique | Key_name                                            | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type |  
+------------+------------+-----------------------------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+  
| activities |          0 | PRIMARY                                             |            1 | id             | A         |        7263 |     NULL | NULL   |      | BTREE      |  
| activities |          1 | index_activities_on_trackable_id_and_trackable_type |            1 | trackable_id   | A         |        7263 |     NULL | NULL   | YES  | BTREE      |  
| activities |          1 | index_activities_on_trackable_id_and_trackable_type |            2 | trackable_type | A         |        7263 |     NULL | NULL   | YES  | BTREE      |  
| activities |          1 | index_activities_on_owner_id_and_owner_type         |            1 | owner_id       | A         |        7263 |     NULL | NULL   | YES  | BTREE      |  
| activities |          1 | index_activities_on_owner_id_and_owner_type         |            2 | owner_type     | A         |        7263 |     NULL | NULL   | YES  | BTREE      |  
| activities |          1 | index_activities_on_recipient_id_and_recipient_type |            1 | recipient_id   | A         |        2421 |     NULL | NULL   | YES  | BTREE      |  
| activities |          1 | index_activities_on_recipient_id_and_recipient_type |            2 | recipient_type | A         |        3631 |     NULL | NULL   | YES  | BTREE      |  
+------------+------------+-----------------------------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+  

select count(id) from activities;  
+-----------+  
| count(id) |  
+-----------+  
|      7117 |  
+-----------+

这是我的当前查询的样子：
SELECT act.*, group_concat(act.owner_id order by act.created_at desc) as owner_ids 
FROM (select * from activities order by created_at desc) as act 
INNER JOIN users on users.id = act.owner_id 
WHERE (users.city_id = 1 and act.owner_type = 'User') 
GROUP BY trackable_type, recipient_id, recipient_type 
order by act.created_at desc 
limit 20 offset 0;

进行解释

Explain

我已经尝试过包括索引在内的许多查询操作。有没有什么方法可以优化这个查询？

3
我认为在选择整个数据时，没有必要使用(select * from activities order by created_at desc)子查询，而是直接通过表名进行连接。 - M Khalid Junaid
2
不需要，这就是为什么你在group_concat函数中又有一个order by。 - fancyPants
@fancyPants 移除那个子查询会导致结果的排序不同。 - Hassan Javeed
2
如果您想让我们帮助优化查询，您需要向我们展示表和索引定义以及每个表的行数。也许您的表定义不好。也许索引没有正确创建。也许您认为该列上有索引，但实际上并没有。如果没有看到表和索引定义，我们无法判断。我们还需要行数，因为这可能会极大地影响查询优化。如果您知道如何执行EXPLAIN或获取执行计划，请将结果放在问题中。 - Andy Lester
2
首先，阅读MySQL扩展到GROUP BY：选择“隐藏”列而不进行聚合是MySQL特有的“功能”，这会导致不确定的结果（在其他RDBMS中完全无效）。也许你想要SELECT trackable_type、recipient_id、recipient_type、GROUP_BY(owner_id ORDER BY created_at DESC) ...，这样可能更有意义？但如果是这样，很难看出你打算使用最外层的ORDER BY来完成什么（同样，created_at是一个“隐藏”的列，会导致不确定的结果）。 - eggyal
显示剩余6条评论
5个回答
1

我认为你根本不需要使用offset 0，看起来你也可以不使用子查询。如果你不使用users表中的字段，你可以使用in（或exists）来使它更清晰：

select
    a.trackable_type, a.recipient_id, a.recipient_type,
    max(a.created_at) as max_created_at,
    group_concat(a.owner_id order by a.created_at desc) as owner_ids 
from activities as a
where
    a.owner_type = 'User' and
    a.owner_id in (select u.id from users as u where u.city_id = 1)
group by a.trackable_type, a.recipient_id, a.recipient_type
order by max_created_at desc
limit 20;

对我来说，看起来如果你在activities上创建owner_type, owner_id的索引（你的索引owner_id, owner_type不适用于你的查询），以及在users上创建city_id的索引，你的查询肯定可以获得性能提升。

@HassanJaveed，所以你可以在查询中添加偏移量。不知道为什么你接受了nimdil的答案，这通常是我的答案，但是晚了一天。 - Roman Pekar
1

MySQL有时会表现出奇怪的行为，所以我想试一试。我假设ID是用户表上的主键。

SELECT 
    act.trackable_type, act.recipient_id, act.recipient_type,
max(act.created_at) as max_created_at,
    group_concat(act.owner_id order by act.created_at DESC) as owner_ids 
FROM  activities act 
WHERE act.owner_id in (select id from users where city_id = 1)
AND act.owner_Type = 'User'
GROUP BY trackable_type, recipient_id, recipient_type 
ORDER BY max_created_at
LIMIT 20
这似乎是所有提供的解决方案中最快的。谢谢 :) - Hassan Javeed
太好了！很高兴能帮忙。 - nimdil
0

首先，我会让查询更易读 :-)

您不需要使用带有ORDER BY的派生表，而是使用列列表代替ACT.*。

SELECT ACT.TRACKABLE_TYPE, ACT.RECIPIENT_ID, ACT.RECIPIENT_TYPE, MAX(ACT.CREATED_AT) AS max_created,
   GROUP_CONCAT(ACT.OWNER_ID ORDER BY ACT.CREATED_AT DESC) AS OWNER_IDS 
FROM ACTIVITIES AS ACT 
JOIN USERS ON USERS.ID = ACT.OWNER_ID 
WHERE (USERS.CITY_ID = 1 AND ACT.OWNER_TYPE = 'USER') 
GROUP BY ACT.TRACKABLE_TYPE, ACT.RECIPIENT_ID, ACT.RECIPIENT_TYPE
ORDER BY max_created DESC 
LIMIT 20 OFFSET 0;

当你将WHERE条件移动到派生表中时，可能会有所帮助：

SELECT ACT.TRACKABLE_TYPE, ACT.RECIPIENT_ID, ACT.RECIPIENT_TYPE, MAX(ACT.CREATED_AT) AS max_created,
   GROUP_CONCAT(ACT.OWNER_ID ORDER BY ACT.CREATED_AT DESC) AS OWNER_IDS 
FROM ACTIVITIES AS ACT 
JOIN (SELECT ID FROM USERS WHERE CITY_ID = 1) USERS 
  ON USERS.ID = ACT.OWNER_ID 
WHERE ACT.OWNER_TYPE = 'USER'
GROUP BY ACT.TRACKABLE_TYPE, ACT.RECIPIENT_ID, ACT.RECIPIENT_TYPE
ORDER BY max_created DESC 
LIMIT 20 OFFSET 0;
我会试着去做这两件事并回复您 :) 谢谢 - Hassan Javeed
0

您能告诉我们您的用户表的大小吗？就像下面的查询结果一样：

select count(id) from users WHERE users.city_id = 1;

如果这是一个小数，我建议使用
SELECT act.trackable_type, act.recipient_id, act.recipient_type, max(act.created_at) as max_created_at,
    group_concat(act.owner_id order by act.created_at DESC) as owner_ids 
FROM  activities act 
WHERE act.owner_id in (select id from users where city_id = 1)
AND act.owner_Type = 'User'
GROUP BY trackable_type, recipient_id, recipient_type 
ORDER BY max_created_at
LIMIT 20

否则，使用join会更好

SELECT ACT.TRACKABLE_TYPE, ACT.RECIPIENT_ID, ACT.RECIPIENT_TYPE, MAX(ACT.CREATED_AT) AS max_created_at,
   GROUP_CONCAT(ACT.OWNER_ID ORDER BY ACT.CREATED_AT DESC) AS OWNER_IDS 
FROM ACTIVITIES ACT 
JOIN USERS ON (USERS.CITY_ID = 1 AND USERS.ID = ACT.OWNER_ID)
WHERE ACT.OWNER_TYPE = 'USER'
GROUP BY ACT.TRACKABLE_TYPE, ACT.RECIPIENT_ID, ACT.RECIPIENT_TYPE
ORDER BY max_created DESC 
LIMIT 20;
0

首先，这是一个非常棘手的查询，基于解释其含义和如何改进它，可以为开发人员职位建立一个有趣的面试题=)。

  1. MySQL使用嵌套循环连接，这意味着当有一个连接时，MySQL从一个表开始，并对于每个匹配的行在连接中的第二个表中循环相关行。

  2. 当没有索引时，对于每一行MySQL都会访问磁盘并获取用于条件的字段，并对另一个表中的每一行执行相同的操作。访问磁盘是昂贵且耗时的，最好从内存中获取信息，因此可以从索引中获取数据。

  3. 连接的顺序由MySQL优化器选择。但是，您可以通过创建特殊索引（有时还有提示）来提示MySQL。

  4. 当您执行像这样的操作(select * from activities order by created_at desc)时，您将整个表加载到一个临时未索引的表中，这在任何情况下都不是好事。但最糟糕的是，MySQL应该从表开始连接，否则它需要在嵌套循环中检查表的每一行的条件。

  5. 使用索引进行排序或分组（也需要排序）是什么意思？这意味着按索引顺序读取数据。但由于MySQL使用嵌套循环连接，只有当包含排序字段的表来自连接中的第一个表时，才能利用索引进行排序。

  6. created_at字段未包含在group by子句中，这意味着您不关心选择哪个组（它们可能在组内相同）

  7. 在复杂查询中，特别是在分页查询中，通常最好仅选择所需行的ID，并将ID与其余字段的表进行反向连接（您排序的数据越少，速度就越快）。

  8. 总之，我们需要使用索引从activities表开始连接，在嵌套循环中连接到users并获取ID，然后反向连接到活动表以获取其余值。

因此，您需要在活动上创建相当长的复合索引(owner_type, trackable_type, recipient_id, recipient_type, owner_id, created_at)，并且在用户上创建可能过于奢侈但必需的索引(id, city_id)。

现在，将查询重写为：

SELECT *
FROM
  (SELECT a.id, group_concat(a.owner_id order by a.created_at desc) as owner_ids
   FROM activities a
   JOIN users u ON a.owner_id = u.id AND u.city_id = 1
   WHERE a.owner_type = 'User'
   GROUP BY trackable_type, recipient_id, recipient_type
   ORDER BY a.created_at desc
   limit 20 offset 0) as owners
JOIN activities a USING (id);

你应该查看 EXPLAIN，可能并且在子查询中使用 STRAIGHT_JOIN 而不是 JOIN，以确保正确的连接顺序。

这个解决方案似乎是一个资源密集型的解决方案，而且确实是。但这应该是你接下来实验的好基准。您可能应该开始引入一些其他用于分组的字段（在索引中具有varchar 255，特别是其中两个不是高效的），因此您应该考虑一些足够的前缀，并将它们明确地引入为排序器或强制带有前缀的索引。您可以创建一个特殊的分组器字段，该字段是从（trackable_type、recipient_id、recipient_type）转换成某个东西的函数。这个 owner_type =' User' 也不是那么好，最好比较整数等。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接