面向亿级数据的MySQL高性能检索与过滤方案咨询

Great question—planning ahead for scale instead of scrambling later is such a smart move, especially when you’re building with future growth in mind. Let’s break down the key areas you need to focus on to keep your MySQL setup performant even with 100M+ records, while nailing that product search and filtering functionality.

• Pick the right primary key: For 100M+ records, INT UNSIGNED (max 4.2B) will technically work, but go with BIGINT UNSIGNED to leave room for future growth without schema changes. Auto-incrementing keys are fine here—they’re fast and easy to manage.
• Normalize your tables: Avoid giant "wide tables" with every possible column. Split data into logical tables (e.g., products, product_categories, product_attributes) and use foreign keys to link them. This reduces redundant data, speeds up writes, and makes queries leaner when you only need specific columns.
• Choose efficient data types: Use the smallest type that fits your data. For example:
  • Use VARCHAR instead of TEXT for product names (unless you need extremely long text)
  • Use ENUM for fixed options like product status (active, inactive) instead of VARCHAR
  • Store numbers as INT/DECIMAL instead of strings—they’re faster to sort and filter

Indexing is make-or-break for search and filtering at scale. Here’s how to get it right:

• Full-text indexing for product search: For "same or similar" name searches, MySQL’s InnoDB full-text indexes are way more efficient than LIKE '%keyword%' (which does full table scans). Create one with:

  CREATE FULLTEXT INDEX idx_product_name ON products(name);
  

  Then query it with:

  SELECT * FROM products WHERE MATCH(name) AGAINST('wireless headphones' IN NATURAL LANGUAGE MODE);
  

  This handles partial matches, relevance ranking, and basic synonym logic out of the box.
• Prefix indexes for strict partial matches: If you need searches like "starts with 'apple'", use a B-tree prefix index:

  CREATE INDEX idx_product_name_prefix ON products(name(50));
  

  Pick a prefix length that balances selectivity (avoid too short, which leads to too many matches) and storage (avoid too long, which bloats the index).
• Composite indexes for filtering: For combined filters (e.g., "category = 'electronics' AND price < 100"), create a composite index following the leftmost prefix rule:

  CREATE INDEX idx_category_price ON products(category_id, price);
  

  Put the most frequently filtered column first. Avoid over-indexing—each index slows down write operations (inserts/updates/deletes).

Even the best indexes won’t save bad queries. Follow these rules:

• Avoid SELECT *: Only fetch the columns you need. This reduces data transfer, memory usage, and disk I/O.
• Use EXPLAIN to debug: Run EXPLAIN before your query to see if it’s using indexes, doing full table scans, or creating temporary tables. For example:

  EXPLAIN SELECT name, price FROM products WHERE category_id = 5 AND price < 100;
  

  Look for type: ref or range (good) instead of ALL (bad, full table scan).
• Optimize pagination: Instead of OFFSET (which scans all previous rows), use keyset pagination with your primary key:

  -- Bad: Slow for large offsets
  SELECT * FROM products LIMIT 100000, 20;
  -- Good: Fast, even for large datasets
  SELECT * FROM products WHERE id > 100000 LIMIT 20;
  

• Avoid correlated subqueries: They run once per row, which gets slow with big data. Rewrite them as joins instead.

When your data hits the 100M mark, single-instance MySQL might not cut it. Here’s your roadmap:

• Read replicas: Set up MySQL master-slave replication to offload read queries (search, filtering) to slave instances. Write operations go to the master, and slaves sync data automatically. This is the easiest first step to scale reads.
• Horizontal sharding: If a single table gets too big (100M+ rows), split it into smaller "shards" across multiple instances. Common sharding strategies:
  • Hash sharding: Split by product ID (e.g., id % 10 to split into 10 shards)
  • Range sharding: Split by date (if products have a creation date) or category
    Tools like Vitess or ProxySQL can help manage sharding logic so your app doesn’t have to handle it directly.
• Cache layer: Add Redis or Memcached to cache frequent queries (e.g., top 100 products, popular search terms). Cache search results for 5-15 minutes to reduce DB load.
• Dedicated search engine: For advanced search (synonyms, spelling correction, faceted filtering), offload search to Elasticsearch or Meilisearch. Sync product data from MySQL to the search engine periodically, and let it handle all search/filtering requests.

• Enable slow query logs: Track queries that take longer than a threshold (e.g., 1 second) to find bottlenecks:

  SET GLOBAL slow_query_log = 'ON';
  SET GLOBAL long_query_time = 1;
  

  Analyze the log regularly to optimize slow queries.
• Optimize table fragmentation: Over time, deletes/updates create data fragments. Run OPTIMIZE TABLE products; during low-traffic periods to rebuild the table and reclaim space (note: this locks the table, so plan accordingly).
• Monitor key metrics: Keep an eye on CPU, memory, disk I/O, connection counts, and query latency. Use tools like MySQL’s Performance Schema to set up alerts for anomalies.

Start small with these practices—you don’t need to implement all of them right away, but having them in your roadmap will make scaling smooth when your user base and data grow. The key is to prioritize indexing and query efficiency first, then layer on scaling solutions as needed.

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