1. 项目背景与需求分析
去年接手了一个手机市场数据分析项目，团队需要搭建一套完整的数据库系统来处理每日产生的海量市场数据。这个项目最核心的挑战在于：如何设计一个既能满足实时查询需求，又能支撑复杂分析任务的数据库架构。
手机市场数据有几个典型特征：数据维度多（包含机型、价格、销量、区域等20+字段）、更新频率高（部分渠道数据每小时更新）、分析需求复杂（需要支持同比环比、市场份额计算等）。我们最终选择了MySQL作为主数据库，配合Redis做缓存层，这个组合在保证性能的同时也控制了成本。
关键决策点：选择关系型数据库而非NoSQL，是因为我们需要频繁执行多表关联查询，这是关系型数据库的强项。
2. 数据库架构设计
2.1 表结构设计
核心表包括：
- 机型基础表（存储手机型号、品牌等静态信息）
- 销售事实表（记录每日销售数据）
- 渠道维度表
- 区域维度表
- 时间维度表
采用星型模型设计，销售事实表通过外键与各维度表关联。这里有个设计细节：我们将价格区间单独做成维度表而非直接存储具体价格，这样既保护了商业隐私，又方便后续做价格段分析。
CREATE TABLE sales_fact ( id BIGINT PRIMARY KEY AUTO_INCREMENT, model_id INT NOT NULL, channel_id SMALLINT NOT NULL, region_id SMALLINT NOT NULL, date_id DATE NOT NULL, price_range_id TINYINT NOT NULL, sales_count INT NOT NULL, inventory_count INT NOT NULL, FOREIGN KEY (model_id) REFERENCES phone_models(id), FOREIGN KEY (channel_id) REFERENCES sales_channels(id), -- 其他外键约束... ) ENGINE=InnoDB;2.2 索引策略
针对查询特点建立了复合索引：
- (date_id, region_id) 用于区域销售趋势分析
- (model_id, date_id) 用于机型销售追踪
- (channel_id, date_id) 用于渠道分析
特别注意：对于基数很小的字段（如性别、价格区间）不单独建索引，而是作为复合索引的后缀字段。
3. 性能优化实践
3.1 分区表应用
按日期对销售事实表做了RANGE分区，每月一个分区。这使我们可以快速删除过期数据（直接DROP PARTITION），同时显著提升时间范围查询性能。
ALTER TABLE sales_fact PARTITION BY RANGE (TO_DAYS(date_id)) ( PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')), PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')), -- 其他月份分区... );3.2 缓存层设计
使用Redis实现三级缓存：
- 热点数据缓存：存储实时销量Top10机型
- 预计算结果缓存：存储常见分析查询结果
- 会话缓存：存储用户自定义查询条件
缓存更新策略采用"写时失效+定时刷新"组合模式，平衡了实时性和性能。
4. ETL流程实现
4.1 数据清洗规则
原始数据需要经过以下处理：
- 机型名称标准化（去除"全网通""5G版"等后缀）
- 价格取整到百位（1899→1800）
- 异常值过滤（销量>1000且库存=0的数据视为异常）
def clean_phone_model(name): patterns = [r'\(.*\)', r'全网通', r'5G版'] for p in patterns: name = re.sub(p, '', name) return name.strip()4.2 增量更新机制
设计了一个智能增量更新方案：
- 通过binlog监控源数据变化
- 使用MD5校验数据变更
- 仅更新有变化的记录
这使每日数据同步时间从最初的2小时缩短到15分钟。
5. 踩坑经验总结
5.1 字符集问题
早期遇到过一个严重问题：部分渠道数据包含emoji表情，导致ETL失败。解决方案是统一使用utf8mb4字符集：
ALTER TABLE sales_fact CONVERT TO CHARACTER SET utf8mb4;5.2 连接池配置
默认连接池设置在高并发下表现很差，经过调优后配置：
- 初始连接数：10
- 最大连接数：100
- 连接超时：30秒
- 空闲连接回收间隔：5分钟
5.3 监控体系
建立了完整的监控指标：
- 查询响应时间P99
- 连接池使用率
- 缓存命中率
- ETL任务耗时
使用Grafana制作了实时监控看板，当任何指标超过阈值时会自动触发告警。
6. 扩展思考
这套架构后续可以扩展的方向：
- 增加ClickHouse作为分析专用库，解决复杂查询性能问题
- 引入数据版本管理，支持历史数据回溯
- 实现自动化报表生成，定时推送分析结果
实际运行半年后，系统稳定支撑日均100万+的数据写入和5000+的分析查询，证明了架构的可靠性。最大的体会是：数据库设计必须从业务需求出发，不能盲目追求新技术，合适的就是最好的。
