SQL数据库混合负载优化_OLTP与OLAP平衡_技术教程

关键在于分层隔离+按需路由+资源感知：读写分离与查询分级路由、冷热数据分层及物化视图预计算、索引策略差异化设计、资源组运行时干预。

SQL数据库要同时支撑OLTP（高并发事务）和OLAP（复杂分析查询），关键不在“一刀切”的配置，而在于分层隔离+按需路由+资源感知。硬扛混合负载往往导致事务延迟飙升、报表卡顿，真正有效的优化是从数据访问模式出发做结构性拆分与协同。

OLTP写操作必须低延迟、强一致性；OLAP读操作需要大扫描、高内存、容忍秒级延迟。两者共享同一套缓冲池和锁管理器，必然争抢资源。

用中间件（如ProxySQL、MaxScale）或应用层逻辑识别SQL类型：INSERT/UPDATE/DELETE/点查走主库；SELECT含GROUP BY、JOIN多表、WHERE含时间范围且无主键条件的，自动路由到只读副本
为OLAP副本开启专用参数：innodb_buffer_pool_size可设更高（如70%内存），关闭query_cache（已弃用但旧版本仍可能开启），启用read_buffer_size和sort_buffer_size适度调大
避免“伪只读”：确保OLAP副本不接受写请求，应用连接串明确指定read_only=1，并监控Com_insert/Com_update在只读节点是否非零

90%的OLTP访问集中在最近7天订单、用户会话等热数据；而OLAP常分析过去12个月趋势。把全量数据堆在一张表里，索引膨胀、统计信息失真、执行计划抖动。

按时间或业务维度分区：MySQL 8.0+用PARTITION BY RANGE (TO_DAYS(created_at))，将历史分区转为ARCHIVE引擎或迁至列存（如ClickHouse）
对高频分析口径建物化汇总表：例如每小时聚合一次“各城市当日下单UV/GMV”，用事件驱动（binlog监听）或定时任务（5分钟粒度）更新，OLAP查询直接读这张轻量表
用WITH RECURSIVE或临时表替代多层子查询嵌套，减少OLAP执行时的临时表空间压力和CPU开销

OLTP索引追求“窄、快、唯一”，覆盖点查和短事务；OLAP索引要支持范围扫描、跳扫、位图过滤，甚至容忍一定冗余。

主键保持紧凑：避免UUID或长字符串，优先自增整型或雪花ID；联合索引遵循最左匹配，但OLTP侧控制在3列以内
为OLAP常见过滤字段单独建索引：比如status、region_id、created_date，即使选择率不高，配合INDEX MERGE也能加速多条件组合
定期清理无效索引：用sys.schema_unused_indexes视图识别连续7天未被rows_examined使用的索引，尤其警惕OLAP报表临时加的索引未下线

当无法完全物理隔离时，靠资源组限制OLAP查询对系统的影响，比kill慢查询更可控。

创建两个资源组：rg_oltp绑定高优先级CPU核，rg_olap限制最大CPU使用率≤40%，内存硬上限设为2GB
在OLAP连接初始化时执行：SET RESOURCE GROUP rg_olap；关键OLTP服务连接池启动时固定绑定rg_oltp
配合performance_schema监控：查events_statements_summary_by_digest中平均执行时间＞2s且rows_examined＞10万的语句，加入慢日志白名单并推动改写