如何识别复合主键场景下的冗余索引以优化写入性能？

Great question—let’s break this down step by step, since redundant indexes are a common culprit for write performance hits, especially on large tables like your 6M+ row one.

Redundant indexes offer no unique query performance benefits but add overhead to inserts, updates, and deletes. The exact method to find them depends on your database system, but here are the most practical approaches for major platforms:

For MySQL/MariaDB #

The built-in sys schema has a dedicated view for this:

SELECT 
  table_schema, 
  table_name, 
  redundant_index_name, 
  redundant_index_columns, 
  dominant_index_name, 
  dominant_index_columns 
FROM sys.schema_redundant_indexes;

This view flags indexes where another index already covers the same (or a superset of) columns in the same order, making the redundant one unnecessary.

For PostgreSQL #

Query the system catalogs to compare index column sequences with this snippet:

SELECT 
  idx1.schemaname,
  idx1.tablename,
  idx1.indexname AS redundant_index,
  idx2.indexname AS covering_index,
  idx1.indexdef AS redundant_def,
  idx2.indexdef AS covering_def
FROM pg_indexes idx1
JOIN pg_indexes idx2 ON 
  idx1.schemaname = idx2.schemaname 
  AND idx1.tablename = idx2.tablename 
  AND idx1.indexname != idx2.indexname
WHERE 
  idx1.indexdef LIKE idx2.indexdef || '%'
  OR idx2.indexdef LIKE idx1.indexdef || '%'
ORDER BY idx1.schemaname, idx1.tablename;

This highlights indexes that are prefixes of another index (or vice versa). Narrow it down to your specific table by adding AND idx1.tablename = 'your_table_name'.

For Oracle #

Use DBA_INDEXES and DBA_IND_COLUMNS to spot overlapping index sequences:

SELECT 
  i1.table_owner,
  i1.table_name,
  i1.index_name AS redundant_index,
  i2.index_name AS covering_index
FROM dba_indexes i1
JOIN dba_indexes i2 ON 
  i1.table_owner = i2.table_owner 
  AND i1.table_name = i2.table_name 
  AND i1.index_name != i2.index_name
JOIN dba_ind_columns c1 ON 
  i1.index_name = c1.index_name 
  AND i1.table_owner = c1.table_owner
JOIN dba_ind_columns c2 ON 
  i2.index_name = c2.index_name 
  AND i2.table_owner = c2.table_owner
WHERE 
  c1.column_position = c2.column_position 
  AND c1.column_name = c2.column_name
GROUP BY 
  i1.table_owner, i1.table_name, i1.index_name, i2.index_name
HAVING COUNT(*) = LEAST(i1.num_columns, i2.num_columns);

This finds indexes where one is a perfect prefix of the other.

Your hunch is 100% correct. If your composite primary key is (col1, col2), the auto-created primary key index is ordered first by col1, then col2. Database indexes support prefix matching, so this PK index already efficiently serves any query filtering, sorting, or joining on col1 alone. A separate index on col1 adds zero value and only slows down writes.

How to Verify This #

Method 1: Check the Execution Plan

Run a query filtering only on col1 and inspect the execution plan to confirm it uses the PK index instead of the separate col1 index.

• MySQL:

  EXPLAIN SELECT * FROM your_table WHERE col1 = 'sample_value';
  

• PostgreSQL:

  EXPLAIN ANALYZE SELECT * FROM your_table WHERE col1 = 'sample_value';
  

• Oracle:

  EXPLAIN PLAN FOR SELECT * FROM your_table WHERE col1 = 'sample_value';
  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
  

If the plan references your PK index (not the standalone col1 index), that’s concrete proof the separate index is redundant.

Method 2: Check Index Usage Statistics

Most databases track which indexes are actually being used. If the standalone col1 index has zero or negligible usage, it’s safe to drop.

• MySQL:

  SELECT * FROM sys.schema_unused_indexes WHERE table_name = 'your_table';
  

• PostgreSQL:

  SELECT indexname, idx_scan FROM pg_stat_user_indexes WHERE relname = 'your_table';
  

  Look for your col1 index—if idx_scan is 0, it’s never been used.
• Oracle:
  First enable usage tracking, then check:

  ALTER INDEX your_col1_index MONITORING USAGE;
  SELECT index_name, used FROM V$OBJECT_USAGE WHERE table_name = 'YOUR_TABLE';
  

• Always back up the index (or table) as a precaution.
• Double-check for edge cases (e.g., extremely niche queries that might rely on the redundant index—though this is unlikely here).
• Test write performance after dropping—you should see measurable improvements in insert/update/delete speeds.

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