具有多个分组或排序的 mysql 查询优化

Question

更新:表和索引定义

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;

做一个解释

我对这个查询进行了很多尝试，包括索引等。有什么方法可以优化这个查询吗？

Answer 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