MySQL中使用Replace快速匹配字符串及大表VARCHAR多条件查询

Hey there! Let's tackle your problem step by step—dealing with 7 million rows means performance is critical, so we'll focus on both optimizing your multi-condition queries and using REPLACE effectively without slowing down your database.

First: Optimizing Multi-Match VARCHAR Queries #

Your search_upload_detailed_results table has several VARCHAR columns you're filtering on. The best approach depends on whether you're doing exact matches or fuzzy matches:

1. Exact Multi-Match Queries (e.g., IN/= conditions) #

If your WHERE clause uses exact matches like company IN ('Google', 'Microsoft') or partNumber = 'ABC-123', the biggest performance win comes from adding proper indexes:

• Add single-column indexes for columns you frequently filter on individually:

  CREATE INDEX idx_sudr_company ON search_upload_detailed_results(company);
  CREATE INDEX idx_sudr_partNumber ON search_upload_detailed_results(partNumber);
  

• If you often filter on multiple columns together (e.g., company AND country), create a composite index instead:

  CREATE INDEX idx_sudr_company_country ON search_upload_detailed_results(company, country);
  

  Note: Index creation on 7M rows will take time—run this during off-peak hours to avoid disrupting your service.

2. Fuzzy Multi-Match Queries (e.g., LIKE '%xxx%') #

Standard indexes won't help with wildcard-leading LIKE clauses (they force full table scans). Instead, use full-text indexes for fast fuzzy matching:

• Add a full-text index to the columns you need to search:

  ALTER TABLE search_upload_detailed_results 
  ADD FULLTEXT INDEX ft_sudr_part_mfg(partNumber, mfg);
  

• Query using MATCH() AGAINST() with boolean mode for multi-keyword matching:

  SELECT id, surId, company, partNumber
  FROM search_upload_detailed_results
  WHERE MATCH(partNumber, mfg) AGAINST('"X123" "Samsung"' IN BOOLEAN MODE);
  

  This is way faster than LIKE for large datasets, as it leverages the full-text index to skip irrelevant rows.

Second: Efficient REPLACE Usage for String Matching #

Using REPLACE directly on column values in a WHERE clause (e.g., REPLACE(partNumber, '-', '') = 'ABC123') will break index usage, forcing a full table scan. Here's how to fix that:

1. Precompute Cleaned Values with Stored Generated Columns #

Create a stored generated column that pre-applies your REPLACE logic, then index it:

ALTER TABLE search_upload_detailed_results
ADD COLUMN partNumber_clean VARCHAR(100) AS (REPLACE(partNumber, '-', '')) STORED;

CREATE INDEX idx_sudr_part_clean ON search_upload_detailed_results(partNumber_clean);

Now you can query directly against the cleaned column with index support:

SELECT * FROM search_upload_detailed_results
WHERE partNumber_clean = 'ABC123';

This is orders of magnitude faster than applying REPLACE on the fly for 7M rows.

2. Batch Updates with REPLACE #

If you need to update rows using REPLACE (e.g., replacing a substring across the table), avoid running a single large update—it will lock the table for too long. Instead, batch the updates:

WHILE EXISTS (SELECT 1 FROM search_upload_detailed_results WHERE partNumber LIKE '%OLD-PREFIX%') DO
    UPDATE search_upload_detailed_results
    SET partNumber = REPLACE(partNumber, 'OLD-PREFIX', 'NEW-PREFIX')
    WHERE partNumber LIKE '%OLD-PREFIX%'
    LIMIT 1000; -- Adjust batch size based on your server's capacity
    COMMIT;
END WHILE;

This minimizes lock time and keeps your database responsive during the update.

Quick Additional Tips for Large Tables #

• Avoid SELECT *—only fetch the columns you need to reduce data transfer overhead.
• For pagination, use key-based pagination (e.g., WHERE id > last_id LIMIT 100) instead of OFFSET, since OFFSET becomes slow with large row counts.

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