不能直接在大表上建全局索引，因ALTER TABLE ADD INDEX会锁表、阻塞写入且耗时达小时级；历史数据不更新却需维护全部B+树指针，浪费资源，而查询集中于近期数据。
为什么不能直接在大表上建全局索引
日志表单日增量超千万行时，ALTER TABLE ADD INDEX 会锁表、阻塞写入，且索引构建耗时可能达小时级。更关键的是，历史数据几乎不更新，但全局索引仍需为每条旧记录维护 B+ 树节点指针，浪费内存与磁盘 I/O —— 尤其当查询集中在最近 3 天数据时，95% 的索引页根本不会被访问。
用分区表 + 每日子表索引替代全局索引
核心思路是把“按日滚动”从应用逻辑下沉到存储层：用 PARTITION BY RANGE (TO_DAYS(log_time)) 拆分主表，并为每个分区（即每日数据）单独建立局部索引。MySQL 8.0+ 支持 ALGORITHM=INSTANT 添加局部索引，不锁表、秒级完成。
实操要点：
- 建表时必须显式指定
PARTITION BY RANGE COLUMNS(log_time)（注意不是TO_DAYS()函数，否则无法 INSTANT） - 每日凌晨执行
ALTER TABLE log_table REORGANIZE PARTITION p_old INTO (PARTITION p_20240520 VALUES LESS THAN ('2024-05-21'), PARTITION p_old VALUES LESS THAN (MAXVALUE))切出新分区 - 对新分区立即执行
ALTER TABLE log_table ADD INDEX idx_trace_id ON log_table(trace_id) ALGORITHM=INSTANT—— 此操作仅扫描当日数据，毫秒级 - 旧分区若长期无查询，可用
ALTER TABLE log_table DROP INDEX idx_trace_id PARTITION (p_20240515)单独删索引，不影响其他分区
查询时如何避免全分区扫描
即使有分区裁剪，WHERE log_time >= '2024-05-20' AND trace_id = 'xxx' 仍可能因优化器误判而扫描多个分区。必须强制引导：
实操要点：
- 在
WHERE子句中显式写出分区键范围：log_time >= '2024-05-20' AND log_time ，让优化器精准定位到单一分区 - 检查执行计划是否出现
type: range和key: idx_trace_id，而非type: index或key: NULL - 禁止在分区键上用函数，如
DATE(log_time) = '2024-05-20'会导致全分区扫描
应用层写入时的分区感知陷阱
如果应用直接 INSERT INTO log_table 而不校验 log_time 值，可能因系统时间偏差或乱序写入导致数据落入错误分区（比如凌晨 00:02 的日志被分到昨日分区），后续查不到。
必须守住的底线：
- 所有写入 SQL 必须带
log_time字段，禁止用NOW()或CURRENT_TIMESTAMP默认值 - 应用侧生成
log_time时，统一用 UTC 时间戳转为YYYY-MM-DD HH:MM:SS字符串，避免时区转换误差 - 数据库侧加触发器校验：
BEFORE INSERT中若log_time < DATE_SUB(NOW(), INTERVAL 1 DAY)则报错，防止误写冷数据
分区边界和索引生命周期必须由 DBA 与应用开发共同对齐，任何一方绕过约定都会让“只追加”变成“难维护”。
