我需要帮助优化查询。有一个数据透视表,其中包含与每个用户的通知ID匹配的用户ID:
+----+---------+-----------------+
| id | user_id | notification_id |
+----+---------+-----------------+
| 1 | 234 | 3 |
| 2 | 546 | 34 |
| 3 | 646 | 11 |
+----+---------+-----------------+
两者 user_id
以及 notification_id
是外键。这个表有2800万行。
这样做的目的是获得超过120个通知的用户的100个ID,按通知最多的用户排序:
SELECT user_id, COUNT(feed_notification_id) AS notification_count
FROM sd_user_feed_notification
GROUP BY user_id
HAVING notification_count >= 120
ORDER BY notification_count DESC
LIMIT 100
问题是上面的查询运行了200多秒,因为它基本上必须遍历所有行才能聚合通知。
外键已经是索引。查询本身非常简单。
有什么办法可以优化它吗?
mysql版本:5.6
2条答案
按热度按时间yftpprvb1#
sd_user_feed_notification
听起来像是一个Map表user
以及feed_notification
. 如果是这样的话,就去掉fks并遵循这里给出的多对多表的规则。这将包括(反之亦然)。在这一点上,所有上述评论都将得到处理。此外,该表只有2列,所以它和索引一样小——或者尽可能快。
在实际情况下,摆脱
INDEX(a)
当你添加INDEX(a,b)
. 但不要掉下去INDEX(b)
. 复合索引中列的顺序很重要。更多5rgfhyps2#
如果没有综合指数
(user_id, feed_notification_id)
,则很可能查询不完全由索引满足。也就是说,执行计划正在对底层表页执行查找,以检查feed_notification_id
为空(一COUNT(expr)
聚合将不包括表达式计算结果为null的行。)我们(likley)可以通过从索引中满足的查询获得更好的性能,例如,通过删除对的引用
feed_notification_id
列。如果我们保证
feed_notification_id
如果不为null,则会得到一个等价的结果:(我们希望explain输出在额外的列中显示“using index”。)
因此,查询将只是一个索引的完整扫描,而不查找基础表。
这仍然需要评估2800万行。a和
ORDER BY
在聚合表达式上,无法绕过“using filesort”操作。如果我们必须使用现有的查询,那么(该查询的)最佳性能将是使用复合索引
ON sd_user_feed_notification (user_id, feed_notification_id)
.添加该索引将呈现一个索引
ON sd_user_feed_notification (user_id)
冗余。后续行动
问:(1)然后我是否应该删除user\u id和notification\u id上的单个索引,并仅在查询时使用复合索引?
问:(2)这不会影响其他针对该表的查询吗?
a:如果我们把综合指数加在
(user_id,feed_notification_id)
,那么我们就可以删除(user_id)
. 此复合索引适合支持外键约束。任何受益于旧(单例)的查询
user_id
列)索引可以受益于替换(复合)索引(使用user_id
作为第一列。)一些查询将受益更多,消除了对底层表中页面的查找(以检索
notification_id
.)替换索引将更大,但在查找与单个用户相关的行时,它将通过消除大量行来提高性能。
新的综合指数并不能取代市场上的指数
feed_notification_id
列。我们仍然需要一个以该列为前导列的索引(我们可以用一个综合指数来代替
(feed_notification_id,user_id)
.索引中列的顺序很重要。
如果(user\u id,feed\u notification\u id)的组合是唯一的,那么我们可以将索引定义为唯一索引,并强制执行该索引。
另外,如果这个表纯粹是一个链接/关联/联接表,而不是一个实体表(即,没有对这个表的外键引用),那么为了性能,我会考虑删除
id
列(大概定义为PRIMARY
(群集)键。我倾向于这样的表定义: