设计一个存储用户行为日志的数据库表，需要支持按用户ID、时间范围、设备类型等查询，并考虑数据量每天千万条，如何设计索引和分库分表策略，以优化查询性能？
答案
1) 【一句话结论】：针对千万级用户行为日志，需按时间分表（如按天）、按用户/设备分库，并构建复合索引（user_id, ts, device_type），以控制单表数据量并覆盖查询维度，实现高效查询。
2) 【原理/概念讲解】：用户行为日志属于时间序列数据，数据量每天千万条，需通过分库分表将大表拆分为多个小表，降低单表压力。分库分表分为垂直分库（按业务维度，如按用户ID分库）和水平分片（按时间维度，如按天分表）。索引方面，由于查询条件包含用户ID、时间范围、设备类型，采用B+树结构的复合索引，按（user_id, ts, device_type）排序，利用索引覆盖查询条件，减少回表。时间序列数据按时间有序，分表按天可利用时间有序性加速范围查询（如时间范围查询可快速定位多个表）。
3) 【对比与适用场景】：
分库分表策略对比：
| 策略 | 定义 | 特性 | 使用场景 | 注意点 |
|---|---|---|---|---|
| 垂直分库 | 按业务维度（如用户ID）拆分表到不同库 | 每个库数据量小，查询时需连接多个库 | 用户维度查询频繁，如按用户ID查询行为 | 需要分布式事务或最终一致性 |
| 水平分片（按时间） | 按时间维度（如天）拆分表 | 单表数据量可控，时间范围查询高效 | 日志类数据，按时间有序 | 需要表名管理（如带日期后缀） |
索引策略对比：
| 索引类型 | 定义 | 特性 | 使用场景 | 注意点 |
|---|---|---|---|---|
| 单字段索引 | 仅索引一个字段 | 查询时需回表 | 单字段查询 | 无法支持复合查询 |
| 复合索引（覆盖索引） | 索引包含查询所有字段 | 查询时无需回表 | 复合查询（如多条件过滤） | 索引大小随字段数增加 |
4) 【示例】：表结构（伪代码）：
CREATE TABLE user_behavior_log (
log_id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL,
ts TIMESTAMP NOT NULL,
device_type VARCHAR(20) NOT NULL,
action VARCHAR(100),
INDEX idx_user_ts_device (user_id, ts, device_type) -- 复合索引
) ENGINE=InnoDB;
-- 分库分表策略
-- 分库：按user_id的hash取模，如库1存储user_id % 2 = 0的用户
-- 分表：按ts按天分表，表名格式为daily_log_YYYYMMDD
查询示例（SQL）：
SELECT * FROM user_behavior_log
WHERE user_id = 12345
AND ts BETWEEN '2024-01-01 00:00:00' AND '2024-01-02 23:59:59'
AND device_type = 'mobile';
该查询会先通过复合索引定位到user_id=12345的行，再按时间范围和设备类型过滤，索引覆盖所有查询条件，无需回表。
5) 【面试口播版答案】：面试官您好，针对用户行为日志的存储与查询优化，核心思路是结合时间序列特性，通过分库分表控制数据量，再通过索引覆盖查询维度。首先，分库分表：因为每天千万条数据，按用户维度垂直分库（比如每个用户ID对应一个库，或按hash分库），水平分表按时间（如按天分表，表名带日期，如log_20240101），这样每个表数据量可控，查询时只需访问对应表。然后索引设计：针对查询条件（用户ID、时间范围、设备类型），创建复合索引（user_id, ts, device_type），因为查询时通常先按user_id过滤，再按时间范围，最后按设备类型。这样索引覆盖查询条件，减少回表。比如查询某个用户在2024年1月1日到1月2日，设备为手机的行为，索引可以快速定位到对应表和行，提升性能。总结来说，通过分库分表降低单表压力，复合索引覆盖查询维度，能有效支持千万级日志的高效查询。
6) 【追问清单】：
- 问：如何处理时间范围查询？是否需要跨多个表？
回答要点：分表按天，时间范围查询可跨多个表，索引按时间有序，范围查询效率高，无需全表扫描。 - 问：分库分表后，如何保证数据一致性？
回答要点：日志类数据允许最终一致性，采用异步写入（如消息队列），或分布式事务（如两阶段提交，适用于强一致性场景）。 - 问：索引设计是否考虑了覆盖索引？
回答要点：是的，复合索引包含查询所有字段（user_id、ts、device_type），查询时无需回表，减少I/O开销。 - 问：数据量增长后，分表策略如何调整？
回答要点：按时间分表时，可扩展为按月或年分表；分库时，可增加库或调整hash规则，动态扩展。 - 问：分库分表后，查询性能是否受限于网络或分布式协调？
回答要点：通过分库分表减少单表数据量，网络延迟降低；分布式协调（如ShardingSphere）可优化路由，提升查询效率。
7) 【常见坑/雷区】：
- 坑1：分表时只按时间分，忽略用户维度，导致查询时需扫描多个表，性能下降。
雷区：时间范围查询需要跨多个表，增加查询复杂度。 - 坑2：索引设计为单字段索引，无法支持复合查询，导致全表扫描。
雷区：复合查询条件（如多字段过滤）时，单字段索引无法高效过滤，性能差。 - 坑3：分库分表后，未考虑索引维护成本，导致索引大小增长过快。
雷区：频繁的索引更新（如插入大量数据）会增加索引维护开销，影响性能。 - 坑4：时间序列数据按天分表，但查询跨月时，需要合并多个表，可能影响查询效率。
雷区：跨月查询需扫描多个表，增加I/O和延迟。 - 坑5：分库分表后，数据一致性处理不当，导致数据丢失或重复。
雷区：异步写入可能丢失数据，需结合事务或补偿机制保证一致性。