MySQL多态事件记录存储选型：单表vs分表，优化Group By与Count性能

Let’s cut straight to the chase: single-table storage is the clear winner here for optimizing your GROUP BY and COUNT queries, given your data volumes and query pattern. Here’s a breakdown of why, plus some optimizations to make it even better:

Why Single Table is Better #

1. No Join Overhead (the Big One) #

Split tables would force you to either UNION ALL the two datasets first before grouping, or join them on common fields—both of which tank performance. Your event A table is massive (200M rows/day), so any cross-table operation adds significant IO and computation overhead. With a single table, the database can scan and aggregate data in one pass, no extra steps needed.

2. Maximized Index Efficiency #

Your core query groups by caid, sid, coid, uid, sub-type. With a single table, you can create a covering index tailored exactly to this query:

CREATE INDEX idx_grouping ON events (caid, sid, coid, uid, sub-type);

MySQL can use this index to answer your GROUP BY + COUNT(*) query without ever touching the main table data (an "index-only scan"). This is orders of magnitude faster than scanning two separate tables, merging their results, and then aggregating.

If you split tables, you’d need identical indexes on both tables, and still have to combine aggregated results from each—wasting time and resources.

3. Minimal Storage Redundancy #

Event B only makes up 1% of your daily data (2M vs. 200M rows). Adding 9 extra fields to the single table for that tiny subset is negligible in terms of total storage. The tradeoff is well worth avoiding the performance hit of split tables.

Why Split Tables Are a Bad Fit Here #

• Double the Work: Every query would require scanning both tables, merging data, then grouping. For a 200M-row table, this is a massive unnecessary load.
• Higher Maintenance: You’d need logic to route inserts to the correct table, maintain duplicate indexes, and handle schema changes across two tables instead of one.
• No Real Benefit: Split tables shine when datasets have wildly different access patterns or schema requirements, but your two event types share the same core query pattern—so splitting gains you nothing.

Optimizations for the Single Table #

To get even better performance out of the single table setup:

• Use InnoDB: It’s optimized for large datasets and supports efficient index-only scans for COUNT(). MyISAM’s COUNT() is fast but lacks transaction support and isn’t ideal for high-write workloads like yours.
• Optimize Field Types: Use the smallest possible data types for your grouping fields (e.g., INT instead of VARCHAR if IDs are numeric, fixed-length CHAR instead of variable-length VARCHAR for string IDs). Smaller indexes mean faster scans.
• Consider Partitioning: If your table grows to tens of billions of rows over time, partition by timestamp (e.g., daily partitions). This lets you limit queries to specific date ranges, reducing the amount of data scanned. Note this is still a single table—just partitioned for easier management and faster range queries.

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