IO密集场景下如何从MariaDB快速提取海量CDR数据？

关键信息 #

cdrs表结构 #

+-----------------+---------------+------+-----+---------+----------------+
| Field           | Type          | Null | Key | Default | Extra          |
+-----------------+---------------+------+-----+---------+----------------+
| id              | bigint(12)    | NO   | PRI | NULL    | auto_increment |
| server_id       | tinyint(2)    | NO   |     | 0       |                |
| cdr_id          | bigint(13)    | NO   | MUL | 0       |                |
| user_id         | int(11)       | NO   | MUL | 0       |                |
| transaction_id  | int(11)       | YES  | MUL | NULL    |                |
| sip_id          | int(11)       | NO   | MUL | 0       |                |
| call_type       | tinyint(2)    | YES  | MUL | NULL    |                |
| did_from        | char(24)      | YES  |     | NULL    |                |
| did_from_alias  | bigint(18)    | YES  | MUL | 0       |                |
| did_to          | char(24)      | YES  |     | NULL    |                |
| did_to_alias    | bigint(18)    | YES  | MUL | 0       |                |
| call_status     | char(12)      | YES  | MUL | NULL    |                |
| start_time      | int(11)       | NO   | PRI | 0       |                |
| duration        | decimal(13,3) | YES  |     | NULL    |                |
| billed_duration | decimal(13,3) | NO   |     | 0.000   |                |
| rate            | decimal(10,4) | YES  |     | NULL    |                |
| amount          | decimal(10,4) | YES  |     | NULL    |                |
| usf             | decimal(10,4) | YES  |     | NULL    |                |
| total           | decimal(10,4) | YES  | MUL | NULL    |                |
| country         | varchar(96)   | YES  |     | NULL    |                |
| country_id      | int(8)        | NO   |     | 0       |                |
| code            | varchar(8)    | NO   |     |         |                |
+-----------------+---------------+------+-----+---------+----------------+

表索引信息 #

+---------+------------+------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table   | Non_unique | Key_name         | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+---------+------------+------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| cdrs |          0 | PRIMARY          |            1 | id             | A         |   573346816 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| cdrs |          0 | PRIMARY          |            2 | start_time     | A         |   573346816 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| cdrs |          1 | i_cdr_id         |            1 | cdr_id         | A         |   573346816 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| cdrs |          1 | i_user_id        |            1 | user_id        | A         |      158909 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| cdrs |          1 | i_call_type      |            1 | call_type      | A         |       19887 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| cdrs |          1 | i_transaction_id |            1 | transaction_id | A         |   143336704 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| cdrs |          1 | i_total          |            1 | total          | A         |      163953 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| cdrs |          1 | i_start_time     |            1 | start_time     | A         |    24928122 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| cdrs |          1 | i_call_status    |            1 | call_status    | A         |        9877 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| cdrs |          1 | i_sip_id         |            1 | sip_id         | A         |      353481 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| cdrs |          1 | i_did_to         |            1 | did_to_alias   | A         |   286673408 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| cdrs |          1 | i_did_from       |            1 | did_from_alias | A         |    57334681 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
+---------+------------+------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+

问题现状 #

• 表行数已超int最大值，每日GB级增长，未来将达数十至数百GB，部署在Raid10 SSD存储上。
• 每月1号客户提取上月通话记录时，因数据不在InnoDB缓存，查询为IO密集型：
  • 示例查询：SELECT * FROM cdrs WHERE user_id='<SOME_USER_ID' AND start_time>=1654099200 AND start_time<1656691200，返回52627431条数据耗时50秒。
  • 部分高量客户提取1小时数据需30分钟，性能严重不足。

已尝试无效方案 #

• 汇总表：无法满足客户获取原始数据的需求。
• 按月分区：性能提升不明显。
• MariaDB ColumnStore：对CDR原始数据提取无显著帮助。
• Redis：数据量达1.1TB且持续增长，无法承载。

可行优化方案 #

1. 新增复合索引（优先级最高） #

当前查询的核心过滤条件是user_id + start_time范围，现有独立索引无法高效支撑联合过滤。创建复合索引：

CREATE INDEX idx_user_start ON cdrs(user_id, start_time);

该索引可直接定位目标用户的时间范围数据，避免全表扫描或二次查找，大幅减少IO次数。若允许调整查询语句，可改为覆盖索引查询（仅返回所需字段）进一步降低IO；即使必须返回全字段，复合索引依然能极大提速。

2. 冷数据归档与分层存储 #

• 将超过6个月的历史CDR数据归档到只读从库或低成本存储介质，确保原始数据可直接获取。
• 主库仅保留最近3个月的热数据，减少主库数据量与IO压力，查询历史数据时路由至归档库。

3. 分库分表（长期扩容方案） #

按user_id进行哈希或范围分片，将大表拆分为多个小表：

• 分片后每个节点数据量大幅降低，查询时仅需访问目标用户所在分片，避免跨分片扫描。
• 结合时间维度分片（如按季度），进一步缩小查询范围。

4. 优化查询输出方式 #

• 使用SELECT ... INTO OUTFILE直接将查询结果写入磁盘文件，比通过应用层返回结果集快数倍，用户可直接下载文件。
• 开启MySQL 8.0+/MariaDB的并行查询功能，利用多核CPU并行扫描数据，提升大结果集查询速度。

5. 数据结构瘦身 #

• 移除冗余字段：country字段可通过country_id关联字典表获取，无需存储在CDR表中，减少单条记录大小，提升每页存储行数。
• 调整字段类型：将varchar(96)的country改为char(2)（ISO国家代码），code字段根据实际内容调整为更小类型，进一步压缩数据体积。

6. 缓存预热（临时应急方案） #

每月查询前，提前执行轻量查询预热缓存：

SELECT COUNT(*) FROM cdrs FORCE INDEX(idx_user_start) WHERE user_id='<SOME_USER_ID' AND start_time>=xxx AND start_time<xxx;

将目标数据加载到InnoDB缓冲池，避免查询时全IO读取，仅适用于数据量较小的场景。

内容的提问来源于stack exchange，提问作者Joséph Flames