多对多关联表查询性能优化咨询（无主键/唯一键场景）

Hey there! Let's work through this favorites table performance problem you've got. First, let's cover the quick fixes you can apply to your current setup, then dive into better storage patterns if you're looking to refine things long-term.

The core issue here is that your favorites table lacks a unique constraint and proper indexing, which kills query performance and allows duplicate entries (a user could accidentally favorite the same item multiple times). Here's how to fix that:

• Add a Composite Primary Key
  Since a user shouldn't be able to favorite the same item more than once, the combination of user_id and item_id is inherently unique. Making this your primary key solves two problems at once: it enforces data uniqueness, and MySQL automatically creates a clustered index for the primary key, which drastically speeds up queries like "get all favorites for user X" or "check if user X has favorited item Y".

  Run this command:

  ALTER TABLE favorites ADD PRIMARY KEY (user_id, item_id);
  

• Add a Reverse Composite Index (If Needed)
  The primary key index above is optimized for queries starting with user_id. If you frequently run queries that start with item_id (like "how many users have favorited item Y" or "get all users who favorited item Y"), add a secondary composite index to cover those cases:

  CREATE INDEX idx_favorites_item_user ON favorites (item_id, user_id);
  

  This ensures those item-centric queries don't have to do a full table scan.

If you're open to adjusting the table structure, here are some alternatives that might fit your use case better:

• Surrogate Primary Key + Unique Constraint
  Some teams prefer using a standalone auto-incrementing primary key (like favorite_id) for the favorites table, especially if they plan to add extra columns later (e.g., favorite_timestamp, notes). In this case, you still need to enforce uniqueness on user_id and item_id with a unique constraint, plus add the same composite indexes as above for performance.

  Example schema adjustment:

  ALTER TABLE favorites 
  ADD COLUMN favorite_id bigint AUTO_INCREMENT PRIMARY KEY FIRST,
  ADD UNIQUE KEY uk_user_item (user_id, item_id);
  

  This gives you the flexibility of a single-column primary key while maintaining data integrity and query performance.

• Denormalization for Read-Heavy Workloads
  If your system has way more read operations (users viewing their favorites) than write operations (adding/removing favorites), you could denormalize the data by adding a JSON column to the users table to store a list of favorite item_ids, like favorite_items JSON.

  This lets you fetch a user's entire favorites list with a single query to the users table, no joins needed. The tradeoff is that updating favorites (adding/removing items) becomes more complex (you have to modify the JSON array), and aggregating data (like counting how many users favorited an item) becomes inefficient. This is only a good fit for read-dominant scenarios.

• Specialized Storage for Massive Datasets
  If you're dealing with millions/billions of favorite records and need to run analytical queries (e.g., "show favorite trends over the last month"), consider moving this data to a columnar database or time-series store. But this is a big architecture shift—stick with the relational table approach unless you have specific scaling needs that can't be met with MySQL.

For most standard applications, adding the composite primary key to your existing favorites table is the fastest, most effective fix. It solves the indexing issue, prevents duplicate entries, and gives you great performance for common query patterns. The surrogate key approach is a solid second choice if you need extra flexibility for future schema changes.

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