单表最多可拥有多少个有效索引？多字段检索慢查询优化咨询

Alright, let's tackle that frustrating slow search problem with your 20-million-row table. I’ve worked through similar scenarios with enterprise clients before, so here are actionable, practical fixes you can implement to get that response time back on track:

Building indexes for every possible string field + sort column combo is wasteful (and impossible for 12 columns). Instead, focus on high-impact, targeted indexes:

• Prioritize High-Frequency Search Fields: First, audit which string fields are actually queried most often. For those, create standalone indexes like:

  CREATE INDEX idx_frequent_search_col ON your_table(high_freq_string_col);
  

  If users often sort after searching, bundle the sort column into the index to avoid extra sorting steps:

  CREATE INDEX idx_search_sort ON your_table(search_col, sort_col DESC);
  

• Covering Indexes for Common Workflows: For queries that always return the same set of columns, build a covering index that includes all needed data. This lets the database answer the query directly from the index (no need to "jump back" to the main table):

  -- PostgreSQL example
  CREATE INDEX idx_covering_search ON your_table(search_col, sort_col) INCLUDE (return_col1, return_col2, return_col3);
  
  -- MySQL example (include columns directly in the index)
  CREATE INDEX idx_covering_search ON your_table(search_col, sort_col, return_col1, return_col2, return_col3);
  

• Partitioning for Evenly Distributed Queries: If searches are spread across all string fields, partition the table to reduce the amount of data scanned. For string columns, you could use prefix partitioning (e.g., by first letter) or hash partitioning to split the 20M rows into smaller, more manageable chunks.

Even great indexes won’t help if your queries are written to ignore them:

• Ditch Leading Wildcards: Queries like WHERE string_col LIKE '%keyword' can’t use standard B-tree indexes. If users need partial matches, either encourage suffix matches (LIKE 'keyword%') or use full-text search (more on that below).
• Replace Offset Pagination with Keyset Pagination: Traditional LIMIT 50 OFFSET 10000 forces the database to scan and discard 10,000 rows before returning results. Instead, use the last value from the previous page to jump directly to the next set:

  -- Instead of OFFSET
  WHERE sort_col > 'last_value_from_previous_page' 
    AND search_condition = 'your_query'
  ORDER BY sort_col DESC
  LIMIT 50;
  

• Leverage Full-Text Search: For arbitrary string field searches (especially with partial matches), use your database’s built-in full-text capabilities instead of LIKE. For example:

  -- MySQL
  MATCH(string_col1, string_col2) AGAINST('search_term' IN BOOLEAN MODE);
  
  -- PostgreSQL
  SELECT * FROM your_table 
  WHERE to_tsvector('english', string_col) @@ to_tsquery('english', 'search_term');
  

  Full-text indexes are optimized for text search and will outperform LIKE by orders of magnitude on large datasets.

If your database can’t keep up with the load even after indexing and query tweaks, consider syncing the table data to a dedicated search engine like Elasticsearch or Apache Solr. These tools are built specifically for high-performance, multi-field text search with sorting and pagination. You can sync data via scheduled jobs or real-time change data capture (CDC) to keep the search engine in sync with your main table.

• Use your database’s execution plan tool (like EXPLAIN ANALYZE in PostgreSQL or EXPLAIN in MySQL) to diagnose slow queries. This will show you exactly where the database is spending time (e.g., full table scans, expensive sorts).
• Track query patterns over time. If certain search fields or sort combinations become more popular, adjust your indexes to match those workflows.

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