MySQL历史数据查询过慢求助：大存储量汇率表性能优化

Hey there, let's tackle this slow historical query issue with your MySQL exchange rate table. Based on what you've shared—8M+ rows since 2017, frequent 15-second updates (with slower initial intervals), fast current queries but sluggish historical ones—I've got several actionable optimizations to suggest:

First, let's make sure your indexes are actually working for historical queries:

• Validate index usage: Run EXPLAIN ANALYZE on your slow historical query. If you see type: ALL (full table scan) or key: NULL, your query isn't hitting an index. Most historical queries filter by date/timestamp and currency pairs, so create a composite index tailored to those filters—something like (date_column, currency_from, currency_to) (adjust column names to match your schema).
• Prune unused indexes: Too many indexes slow down your 15-second updates. Use sys.schema_unused_indexes (MySQL 8.0+) or check INFORMATION_SCHEMA.STATISTICS with query usage data to drop indexes that don't serve your update script or queries.

Time-series data like exchange rates is perfect for partitioning, which lets MySQL scan only relevant data instead of the entire 8M-row table:

• Use range partitioning by month or quarter (adjust based on how granular your historical queries are):

  ALTER TABLE exchange_rates 
  PARTITION BY RANGE (TO_DAYS(date_column)) (
      PARTITION p201709 VALUES LESS THAN (TO_DAYS('2017-10-01')),
      PARTITION p201710 VALUES LESS THAN (TO_DAYS('2017-11-01')),
      -- Add partitions up to the current month
      PARTITION p_current VALUES LESS THAN MAXVALUE
  );
  

  Set up a scheduled event or cron job to auto-create new partitions as time passes. This triggers partition pruning, so historical queries only scan the date range they target.

If you rarely query data older than a certain threshold (e.g., 3+ years), move it to an archive table to lighten your main table:

• Create an archive table with matching schema:

  CREATE TABLE exchange_rates_archive LIKE exchange_rates;
  

• Move data in batches during off-peak hours to avoid locking the main table:

  -- Insert old data into archive
  INSERT INTO exchange_rates_archive 
  SELECT * FROM exchange_rates WHERE date_column < '2020-01-01';
  
  -- Delete from main table
  DELETE FROM exchange_rates WHERE date_column < '2020-01-01';
  

  Automate this process with a monthly/quarterly scheduled event to keep your main table lean.

Even with good indexes, poorly structured queries can drag:

• Avoid SELECT *: Only fetch the columns you need (e.g., just rate instead of all table columns) to reduce data transfer and memory usage.
• Precompute aggregates: If you're running queries like "daily average rate for 2018", build a summary table:

  CREATE TABLE daily_exchange_rates_summary (
      date DATE,
      currency_from VARCHAR(3),
      currency_to VARCHAR(3),
      avg_rate DECIMAL(10,6),
      min_rate DECIMAL(10,6),
      max_rate DECIMAL(10,6),
      PRIMARY KEY (date, currency_from, currency_to)
  );
  

  Update this summary table periodically (hourly/daily) using the main table data, then query the summary instead of scanning millions of raw rows.
• Use LIMIT intentionally: If you don't need all historical results, add a LIMIT clause—but pair it with proper filtering and indexing to avoid full scans.

Adjust these settings to boost performance for your growing table:

• Increase innodb_buffer_pool_size to let MySQL cache more data in memory (aim for 50-70% of available RAM if your server is dedicated to MySQL).
• Set innodb_flush_log_at_trx_commit to 2 if you can tolerate minimal data loss on server crash—this speeds up your frequent updates, which indirectly helps read performance.
• Ensure innodb_file_per_table is enabled (default in newer MySQL versions) to simplify partitioning and table maintenance.

Frequent updates and deletes cause fragmentation, which slows down scans:

• Run OPTIMIZE TABLE exchange_rates; during off-peak hours to defragment the table. If using partitioning, optimize individual partitions instead of the whole table to minimize downtime:

  ALTER TABLE exchange_rates OPTIMIZE PARTITION p201709;
  

  Note that OPTIMIZE TABLE locks the table, so schedule it when traffic is lowest.

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