企业官网建设流程全解析

智能负载路由：根据查询特征自动选择OLTP或OLAP引擎的多模式架构

一、同一个SQL，在不同的引擎上执行，速度差了一百倍

公司内部有一个有趣的数据：在MySQL上执行30秒的聚合查询，在ClickHouse上只需要0.3秒；而ClickHouse上执行3毫秒的小点查，在MySQL上0.5毫秒就完成了。这两种数据截然不同的性能特征揭示了一个深刻的架构事实——不存在一种引擎在所有场景下都是最优的。

HTAP架构的核心理念之一是在一个数据库中同时处理OLTP和OLAP负载。但HTAP的实现在现阶段仍不成熟，大多数团队使用的仍然是MySQL+ClickHouse的双引擎架构。在这个架构中，一个关键的设计决策是：如何决定一条SQL查询应该路由到MySQL还是ClickHouse？

传统的做法是基于"数据主题"做静态路由——交易数据走MySQL，报表数据走ClickHouse。但更智能的方式是基于"查询特征"做动态路由——分析每条SQL的特征，自动判断它是否适合在行存或列存引擎上执行。

flowchart TB A[SQL查询] --> B[SQL特征提取器] B --> C[特征向量] C --> D[分类模型] D --> E{路由决策} E -->|OLTP型| F[MySQL路由] E -->|OLAP型| G[ClickHouse路由] E -->|混合型| H[成本评估] F --> I[MySQL执行] G --> J[ClickHouse执行] H --> K[预估两引擎代价] K --> L[选择代价低的引擎] I --> M[返回结果] J --> M

二、查询特征提取——什么决定一条SQL适合哪个引擎

特征一：查询类型。点查（主键查找、唯一索引查找）天然属于OLTP场景，归MySQL处理。聚合查询（GROUP BY + 聚合函数）天然属于OLAP场景，归ClickHouse处理。JOIN查询需要分情况——简单的Nested Loop JOIN（小表驱动大表）适合MySQL，Hash JOIN（大表与中等表关联）适合ClickHouse。

特征二：扫描行数 vs 返回行数。一个核心判断指标是扫描行数与返回行数的比值。比值小于10（扫描少量数据返回大部分数据）适合MySQL；比值大于1000（扫描大量数据返回少量数据）适合ClickHouse的列式扫描优化。

特征三：查询涉及的表大小。小表（<1GB）的查询在MySQL和ClickHouse上性能差异不大；大表（>100GB）的聚合查询在ClickHouse上优势明显。表大小随时间变化，所以路由决策也应该是动态的。

特征四：查询的写入意图。如果SQL包含INSERT/UPDATE/DELETE（或事务上下文中的SELECT FOR UPDATE），必须路由到MySQL。ClickHouse不擅长高频的实时写入。

特征五：数据时效性要求。MySQL到ClickHouse的数据同步存在延迟（通过Canal/Kafka同步通常在秒级）。如果查询要求"读己之所写"的一致性，必须路由到MySQL。

三、分类模型的设计与训练

模型选择。使用梯度提升树（LightGBM）作为分类器。训练样本来自历史查询日志——对于每条SQL，在MySQL和ClickHouse上分别执行并收集实际耗时，哪个引擎快就标注为哪个类别。对于同时标注为两类的查询（两个引擎执行时间差异小于10%），可以归为"任一引擎"类别。

离线训练 + 在线推理。离线阶段使用一周的历史查询数据训练模型，每周更新一次以适应数据量增长和查询模式变化。在线推理时，模型在微秒级内完成分类，不影响查询延迟。

决策保底机制。当模型分类置信度低于阈值（<80%）时，退化为静态路由规则（根据数据主题判断）。当目标引擎不可用（如ClickHouse宕机）时，自动降级到MySQL（可能性能较差但保证可用性）。

import lightgbm as lgb class QueryRouter: """ 基于LightGBM的智能查询路由器。 根据查询特征自动选择最优执行引擎。 """ def __init__(self, model_path: str): self.model = lgb.Booster(model_file=model_path) self.confidence_threshold = 0.8 self.fallback_engine = "mysql" def extract_features(self, sql: str, table_stats: dict) -> dict: """提取SQL查询的多维特征""" parsed = parse_sql(sql) return { "has_aggregation": int(any("COUNT" in sql, "SUM" in sql)), "join_count": len(parsed.get("joins", [])), "estimated_scan_rows": estimate_scan_rows(sql, table_stats), "estimated_return_rows": estimate_return_rows(sql, table_stats), "scan_return_ratio": estimate_scan_rows(sql) / max(estimate_return_rows(sql), 1), "table_size_gb": table_stats.get("total_size_gb", 0), "has_order_by": int("ORDER BY" in sql.upper()), "has_limit": int("LIMIT" in sql.upper()), "is_write": int(any(kw in sql.upper() for kw in ["INSERT", "UPDATE", "DELETE"])), "requires_strong_consistency": int("FOR UPDATE" in sql.upper()), "sql_length": len(sql), "predicate_count": count_predicates(sql), } def route(self, sql: str, table_stats: dict) -> str: """路由决策：返回目标引擎名称""" features = self.extract_features(sql, table_stats) feature_array = [features[k] for k in sorted(features.keys())] # 写入操作直接走MySQL if features["is_write"] or features["requires_strong_consistency"]: return "mysql" # 模型推理 proba = self.model.predict([feature_array])[0] confidence = max(proba) if confidence < self.confidence_threshold: return self._static_route(sql) # 低置信度，降级为静态路由 # 引擎标签映射: 0=mysql, 1=clickhouse target = "mysql" if proba[0] > proba[1] else "clickhouse" return target def _static_route(self, sql: str) -> str: """静态路由规则：根据数据主题判断""" if any(t in sql.lower() for t in ["report", "analytics", "stats"]): return "clickhouse" return "mysql"

四、两引擎数据一致性的保障

智能路由的最大挑战不在于分类准确性，而在于两套引擎之间的数据一致性。当一条查询被路由到ClickHouse，看到的数据与MySQL中的最新状态不一致时，业务逻辑可能出错。

一致性策略：对于实时性要求为"秒级"的查询，使用ClickHouse（其数据同步延迟通常在1~5秒内）；对于要求"读己之所写"的查询，始终路由到MySQL；对于不关心数据最新性的离线分析，优先路由到ClickHouse。

一致性校验：定期在两条引擎上执行相同的抽样查询，对比结果集。当差异超过阈值时告警，可能是同步管道延迟或数据丢失。

五、总结

智能负载路由是在双引擎架构下实现HTAP体验的一个务实方案——它不需要重新设计数据库内核，而是通过智能的查询分发，让每种查询都在最适合的引擎上执行。

对于使用MySQL+ClickHouse双引擎架构的团队，建议分三步实施智能路由：先用静态路由规则（根据表名或业务标签）跑通流程；然后基于查询日志建立简单的启发式规则（聚合→ClickHouse，点查→MySQL）；最后引入ML模型进行精细化的动态路由。关键的成功因素不是模型的精度，而是建立健壮的降级和一致性保障机制。