智能索引推荐系统的生产落地：基于深度学习的自动索引生成与验证

一、索引推荐不是"缺哪个列就加哪个索引"

传统的索引推荐工具（如 MySQL 的 sys.diagnose 或 Percona 的 pt-index-usage）运作逻辑很简单：收集 Slow Log 中的查询，对每个查询的 WHERE、JOIN、ORDER BY 列做排列组合，产出候选索引列表。这个过程完全不考虑索引之间的冗余——一个 (a, b) 的联合索引可以覆盖 (a) 的单列索引，而传统工具可能同时推荐两者。更糟的是，它完全无视写入代价——在每秒 5000 次插入的表上建 10 个索引，写入性能可能下降 40%。

深度学习在索引推荐上的切入点，就是将索引选择建模为一个约束优化问题：在给定存储空间和写入吞吐预算的前提下，选择一组索引使得整体查询延迟最小化。这不是一个简单的贪心问题——索引之间有覆盖关系、查询之间有重叠需求、写入负载会随索引数量非线性增长。

flowchart TD
    A[收集工作负载] --> B[Slow Log 解析]
    A --> C[General Log 采样]
    B --> D[提取查询模板与频率]
    C --> D
    D --> E[特征工程: 查询 → 索引需求矩阵]
    E --> F[深度学习模型: 索引组合评分]
    F --> G[约束优化: 存储预算 + 写入代价]
    G --> H[产出最优索引集合]
    H --> I[虚拟索引验证]
    I --> J{预估收益 > 阈值?}
    J -->|是| K[建议创建索引]
    J -->|否| L[丢弃该候选]
    K --> M[监控写入性能变化]

二、特征工程：把 SQL 变成可学习的向量

要让神经网络理解 "这个查询需要什么索引"，第一步是把 SQL 的结构化信息转换为固定维度的特征向量。

特征设计分为三类：

查询结构特征（维度 = 表列数 × 操作类型数）：对于每个（表, 列）对，编码该列是否出现在 WHERE（等值条件、范围条件、IN 条件）、JOIN、ORDER BY、GROUP BY 中。这本质上是一个稀疏的二元矩阵。

负载统计特征（维度 = 表数 × 3）：每个表的查询频率、平均扫描行数、平均返回行数。这些统计量帮助模型理解"哪些查询更重要"——一个每天执行 100 万次的查询比每天执行 10 次的查询更需要好的索引。

现有索引特征（维度 = 现有索引数）：当前已创建的索引列表，用于模型判断新候选索引是否与现有索引重叠。

import numpy as np

def build_query_features(query_templates, schema_info):
    """构建查询特征矩阵"""
    n_queries = len(query_templates)
    total_columns = sum(len(t['columns']) for t in schema_info['tables'])
    feature_dim = total_columns * 6  # 6 种操作类型

    X = np.zeros((n_queries, feature_dim), dtype=np.float32)
    query_weights = np.zeros(n_queries, dtype=np.float32)

    for i, qt in enumerate(query_templates):
        query_weights[i] = np.log1p(qt['frequency'])  # 频率对数归一化

        for table in qt['tables']:
            base_idx = schema_info['table_offset'][table]
            for col in table['columns_used']:
                col_offset = schema_info['column_offset'][table][col]
                # 编码操作类型
                if 'eq_condition' in col['operations']:
                    X[i, col_offset + 0] = 1
                if 'range_condition' in col['operations']:
                    X[i, col_offset + 1] = 1
                if 'in_condition' in col['operations']:
                    X[i, col_offset + 2] = 1
                if 'join' in col['operations']:
                    X[i, col_offset + 3] = 1
                if 'order_by' in col['operations']:
                    X[i, col_offset + 4] = 1
                if 'group_by' in col['operations']:
                    X[i, col_offset + 5] = 1

    return X, query_weights

三、模型架构：GNN + 约束优化

将索引推荐建模为"哪些索引组合能使全局查询代价最低"，有两类主流架构：

方案 A — 监督学习 + Beam Search：训练一个模型预测"给定查询集 + 给定索引集，查询总代价是多少"。然后用 Beam Search 在候选索引空间中搜索最优组合。优点是推理逻辑透明、可解释，缺点是搜索空间不连续时需要较好的剪枝策略。

方案 B — 图神经网络（GNN）：将查询和列建模为二部图中的节点，边表示"查询使用了该列"。GNN 通过消息传递学习列与查询之间的隐式关系，最终为每个候选索引输出一个"价值分数"。优点是可以捕获查询之间共享列的协同效应，缺点是可解释性差。

实际落地中，方案 A 更适合初期——它的输出（"加索引 A 预计降低 40% 查询代价，同时增加 12% 写入开销"）容易被 DBA 理解和验证。方案 B 适合在积累足够标注数据后做进一步优化。

# 代价估算简化版
def estimate_index_benefit(candidate_index, query_features, query_weights):
    """估算候选索引的收益"""
    total_benefit = 0.0
    write_penalty = 0.0

    for i, qf in enumerate(query_features):
        # 检查候选索引是否能覆盖该查询的过滤列
        coverage = sum(
            1 for col in candidate_index.columns
            if qf[col_offset(col)] > 0
        )
        if coverage > 0:
            # 覆盖越完整,收益越大
            benefit = coverage / len(candidate_index.columns)
            total_benefit += benefit * query_weights[i]

    # 写入代价: 每增加一个索引,INSERT 增加约 5%~10% 延迟
    write_penalty = len(candidate_index.columns) * 0.03 * \
                    sum(query_weights) / len(query_weights)

    return total_benefit - write_penalty

四、生产验证的关键——虚拟索引

索引推荐系统最大的信任障碍是：它推荐的索引到底有没有用？在生产环境直接 CREATE INDEX 再观测效果，风险太高——万一索引没用，还要承担一次昂贵的 DROP INDEX。

MySQL 虽不原生支持虚拟索引，但可以通过 EXPLAIN 的 Hints 来模拟索引存在时的执行计划。具体做法是：用 EXPLAIN FORMAT=JSON 加上 FORCE INDEX 指定一个不存在的索引名，MySQL 虽然会报错，但如果用 MariaDB 或 TiDB（支持虚拟索引语法 CREATE VIRTUAL INDEX），可以直接评估。对于纯 MySQL 环境，可以在测试库上临时创建索引、跑 EXPLAIN、收集预估的代价变化后立即删除——整个过程在秒级完成。

验证通过后，推荐的索引建议应包括：索引 DDL 语句、预估的查询延迟降低比例、预估的写入吞吐下降比例、建议的创建时间窗口（如凌晨 3~5 点业务低谷期）。

flowchart TD
    A[候选索引建议] --> B{环境选择}
    B -->|有 TiDB/MariaDB| C[CREATE VIRTUAL INDEX]
    B -->|纯 MySQL| D[测试库: CREATE → EXPLAIN → DROP]
    C --> E[对工作负载中所有查询执行 EXPLAIN]
    D --> E
    E --> F[计算总代价变化]
    F --> G{收益 > 阈值?}
    G -->|是| H[产出索引创建工单]
    G -->|否| I[归档: 确认该索引无用]
    H --> J[指定创建窗口 + 监控告警]

五、总结

基于深度学习的智能索引推荐，目前具备实际工程价值的不是端到端的自动化（"AI 自动创建索引，无人值守"），而是 AI 推荐 + DBA 审核的半自动模式。关键工程投入应该放在特征工程的质量（能否准确地将 SQL 语义翻译为模型可理解的向量表示）和虚拟索引验证（能否在不影响生产的前提下验证推荐的可靠性）上。从零开始搭建这套系统，最务实的路线是：先用规则引擎覆盖简单场景（"全表扫描 + 无可用索引 = 建索引"），再用 ML 模型处理复杂场景（多列组合、联合索引顺序选择），最后用虚拟索引验证 AI 产出的每一个建议。