MySQL：如何在3列中选取无重复值？（百万级数据性能优先）

Hey there, let’s break this down—getting distinct 3-column combinations from a million-row table while keeping performance snappy is totally achievable, but we need to fix the GROUP BY approach and lean on indexing to make it work.

First: Fix the GROUP BY (or Use DISTINCT) #

You mentioned GROUP BY didn’t work, but that’s likely a misunderstanding of how it’s applied. For your use case, both GROUP BY and DISTINCT will return unique combinations of col1, col2, and col3—they’re functionally equivalent here when you’re only selecting those three columns.

Try these queries:

• Using GROUP BY:

  SELECT col1, col2, col3 FROM your_table GROUP BY col1, col2, col3;
  

• Using DISTINCT (often more intuitive for this use case):

  SELECT DISTINCT col1, col2, col3 FROM your_table;
  

If these still aren’t giving you the unique results you expect, double-check for hidden duplicates:

• Leading/trailing spaces in string columns (e.g., 'apple' vs ' apple '). Use TRIM() to clean them first if needed:

  SELECT DISTINCT TRIM(col1), TRIM(col2), TRIM(col3) FROM your_table;
  

• Case sensitivity (for string columns). If your collation is case-sensitive, 'Apple' and 'apple' are treated as distinct. Use LOWER() or UPPER() to normalize:

  SELECT DISTINCT LOWER(col1), LOWER(col2), LOWER(col3) FROM your_table;
  

The Performance Game-Changer: Composite Indexing #

A million rows will be slow without proper indexing—MySQL has to scan the entire table each time. Create a composite index on your three columns to let the database pull results directly from the index (no need to read the full table):

CREATE INDEX idx_col1_col2_col3 ON your_table (col1, col2, col3);

This is a covering index for your query, meaning MySQL can retrieve all needed data from the index alone, which drastically speeds up the query.

If You Need to Store the Unique Combinations #

If you plan to query these unique sets often, save them to a new table instead of running the deduplication query every time:

Option 1: Create a new table with unique combinations #

CREATE TABLE unique_combinations AS
SELECT DISTINCT col1, col2, col3 FROM your_table;

Option 2: Add a composite primary key to enforce uniqueness long-term #

This ensures future inserts don’t add duplicates to the new table:

CREATE TABLE unique_combinations (
    col1 INT, -- Match your actual column types
    col2 VARCHAR(50),
    col3 DATE,
    PRIMARY KEY (col1, col2, col3)
) AS SELECT DISTINCT col1, col2, col3 FROM your_table;

Quick Troubleshooting #

• If GROUP BY still feels "ineffective", make sure you’re not selecting additional columns that break the grouping (MySQL’s ONLY_FULL_GROUP_BY mode will block invalid GROUP BY queries if enabled).
• For very large tables, consider running the deduplication query during off-peak hours to avoid impacting live traffic.

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