优化2800万行MySQL用户通知关联表的聚合查询

Got it, let's break down how to efficiently fetch those 100 users with 120+ notifications from your 28M-row pivot table.

First, here's the core query that delivers the required results:

SELECT user_id, COUNT(notification_id) AS notification_count
FROM your_pivot_table_name
GROUP BY user_id
HAVING notification_count >= 120
ORDER BY notification_count DESC
LIMIT 100;

But with 28 million rows, a naive run of this query will crawl unless we add targeted optimizations. The biggest win here is indexing:

• Add a composite covering index on (user_id, notification_id). This lets MySQL perform a "covering index scan" — it can calculate the notification count directly from the index without pulling data from the main table, which cuts down on massive I/O overhead.

  Run this to create the index:

  CREATE INDEX idx_user_notification ON your_pivot_table_name(user_id, notification_id);
  

A few quick clarifications on why this works so well:

• The index is ordered by user_id, so grouping by user_id becomes trivial for MySQL — it just counts consecutive entries for each user without scanning the entire table.
• Including notification_id in the index turns it into a covering index, meaning all data needed for the query lives in the index itself. No costly "bookmark lookups" back to the main table are required.
• Since notification_id is a foreign key (and presumably non-null), you can swap COUNT(notification_id) for COUNT(*) if you want — it's slightly more efficient, but the difference is negligible once the covering index is in place.

Don't forget to replace your_pivot_table_name with the actual name of your pivot table!

内容的提问来源于stack exchange，提问作者lesssugar