MyISAM表执行COUNT+GROUP BY查询速度极慢问题求助

Hey there! Let's break down how to optimize this high-throughput log table based on your description. First, let's recap your table's key characteristics for clarity:

该表为日志表，每5分钟通过脚本新增约500,000条数据，每行以(time, asn, pop, country)作为唯一键。针对每组asn、pop、country三元组，每次执行脚本时会计算多个指标并写入表中。数据写入后不会修改，但会删除90天以上的旧数据。

1. Pick the Right Storage Engine #

• If you're using MySQL, InnoDB is non-negotiable here. It supports row-level locking and transactions, which are critical for handling 500k writes every 5 minutes. Stay far away from MyISAM—it locks the entire table on writes, which will grind your system to a halt.
• For PostgreSQL, the default engine works, but you’ll get massive gains from adding the TimescaleDB extension—it’s built specifically for time-series data like this, with optimized writes and partition management.

2. Optimize Primary Key & Indexes #

• Your unique key (time, asn, pop, country) should be your primary key. In InnoDB, the primary key is a clustered index, so if time is incrementing (which it should be for logs), writes will be sequential, avoiding costly page splits.
• Skip standalone secondary indexes on asn, pop, or country unless you have a hard query requirement. Each secondary index adds overhead to every write—if you do need to query by those fields, use a covering index that includes your metrics, or pair it with partitioning (more on that below).

3. Boost Write Performance #

• Batch, batch, batch! Don’t do single-row INSERTs. Instead, use bulk inserts like INSERT INTO log_table (time, asn, pop, country, metric1, metric2) VALUES (...), (...), (...)—aim for batches of 1k-5k rows (adjust based on your database’s max packet size). This cuts down on network round-trips and transaction overhead.
• Turn off autocommit for batches. Wrap your bulk inserts in a single transaction—committing once per batch instead of per row saves huge amounts of time.
• Tune your database config:
  • For MySQL: Crank up innodb_buffer_pool_size to 50-70% of your available RAM, set innodb_log_file_size to 2GB (don’t go over 4GB), and if you can tolerate a tiny risk of data loss on crash, set innodb_flush_log_at_trx_commit = 2 (this trades a bit of durability for way faster writes).
  • For PostgreSQL: Adjust shared_buffers to ~25% of RAM, increase wal_buffers, and tweak checkpoint_timeout to reduce frequent checkpointing during high writes.

4. Make Old Data Deletion Pain-Free #

• Use partitioning by time—this is the single most important thing you can do for deleting old data. Instead of running a slow DELETE that locks rows and creates fragmentation, you just drop the entire partition for data older than 90 days.
  • MySQL example: Create daily partitions, then when you need to purge old data, run ALTER TABLE log_table DROP PARTITION p_20240101;—it’s instant.
  • PostgreSQL/TimescaleDB: Set up automatic partitioning, and it’ll create new partitions as needed and drop old ones on a schedule (no manual work required!).
• If partitioning isn’t an option, use table sharding by time (e.g., one table per month) and drop entire tables instead of deleting rows. It’s less flexible than partitioning but way better than DELETE.

5. Save Space & Speed Up Reads #

• Enable compression: InnoDB supports ROW_FORMAT=COMPRESSED to shrink row sizes, while PostgreSQL/TimescaleDB have built-in compression for older partitions (since you don’t modify data after writing, compression is safe and effective).
• Trim unnecessary fields: If you have any columns you don’t query, get rid of them. Smaller rows mean more rows per page, faster writes, and less storage usage.

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