📚 课程目录第4章索引选择变更场景2

4. 索引选择变更场景2：数据源切换（如从MySQL迁移到PostgreSQL）引发的索引重选

数据源切换是CI/CD流水线中最为剧烈的索引变更场景之一。当调度平台的后端存储从MySQL迁移至PostgreSQL时，由于两种数据库在索引实现、查询优化器行为、存储引擎特性上的根本差异，原有的索引策略几乎必然失效。本章将深入分析这一场景下的索引重选原理、自动检测机制与回滚策略。

4.1 数据源切换对索引的冲击分析

MySQL与PostgreSQL在索引层面存在多个关键差异，这些差异直接导致索引选择必须重新评估：

对比维度	MySQL (InnoDB)	PostgreSQL	索引重选影响
索引类型	B+Tree（主）、Hash（Memory引擎）	B-Tree、Hash、GiST、GIN、BRIN	需评估是否启用高级索引类型
聚簇索引	主键默认聚簇，二级索引含主键引用	无聚簇索引（CLUSTER命令为一次性）	查询计划可能从索引扫描变为堆扫描
NULL值处理	NULL不参与索引（唯一索引允许多个NULL）	NULL参与索引，唯一索引只允许一个NULL	唯一约束与查询过滤条件需调整
部分索引	不支持	支持 WHERE 条件索引	可大幅缩小索引体积，提升写入性能
并发索引创建	DDL锁表（5.6前），Online DDL（5.6+）	CONCURRENTLY 不阻塞读写	流水线中可在线重建索引
查询优化器	基于代价（CBO），统计信息自动收集	基于代价（CBO），ANALYZE需手动或autovacuum	统计信息收集策略需重新配置

    关键发现： 调度平台中约70%的慢查询在数据源切换后，其执行计划发生了根本性变化。其中，涉及时间范围查询、状态过滤和任务依赖链的SQL受影响最大。

4.2 索引重选自动检测流程

在CI/CD流水线中，数据源切换后的索引重选应遵循以下自动化检测流程：

元数据采集阶段： 自动连接新数据源，提取所有表的DDL、现有索引定义、统计信息（pg_class.reltuples / information_schema.statistics）。
查询负载回放： 从调度平台历史日志中提取过去7天的TOP 100慢查询，在新数据源上执行EXPLAIN (ANALYZE, BUFFERS)。
索引缺失检测： 使用pg_stat_user_tables（PG）或performance_schema（MySQL）识别全表扫描的查询，生成候选索引列表。
索引冗余检测： 对比新旧索引定义，标记因类型差异而失效的索引（如MySQL的联合索引在PG中可能被部分索引替代）。
代价模型对比： 对每个候选索引，计算其在PG中的预估代价，与MySQL原索引的代价进行归一化对比。

实践建议： 在流水线中集成 pg_hint_plan（PG）或 Index Hints（MySQL）来强制验证索引选择，避免优化器因统计信息不准确而做出错误决策。

4.3 自动回滚策略设计

索引重选变更必须配备自动回滚能力。以下是一个基于版本化索引定义的回滚策略：

# 索引变更版本化示例（YAML格式）
index_migration_v2:
  version: "2.0.0"
  source_db: "mysql"
  target_db: "postgresql"
  changes:
    - table: "task_instance"
      old_index: "idx_task_status_create_time"
      old_ddl: "INDEX idx_task_status_create_time (status, create_time)"
      new_index: "idx_task_status_create_time_partial"
      new_ddl: "CREATE INDEX CONCURRENTLY idx_task_status_create_time_partial 
                ON task_instance (status, create_time) 
                WHERE status IN ('RUNNING', 'FAILED')"
      rollback_ddl: "DROP INDEX CONCURRENTLY IF EXISTS idx_task_status_create_time_partial"
      validation_query: "EXPLAIN (ANALYZE, TIMING OFF) 
                         SELECT * FROM task_instance 
                         WHERE status = 'RUNNING' AND create_time > NOW() - INTERVAL '1 day'"
      expected_cost_threshold: 5000
  rollback_trigger:
    - condition: "avg_execution_time > expected_cost_threshold * 1.5"
    - condition: "index_scan_ratio < 0.3"

回滚触发条件： 自动回滚不应仅依赖单一指标。建议组合使用执行时间、索引扫描比例、锁等待时间三个维度，当任意两个维度同时超标时触发回滚。

4.4 数据源切换索引重选流程图

下图展示了从MySQL迁移到PostgreSQL时，CI/CD流水线中索引重选与自动回滚的完整流程：

4.5 实战案例：调度平台任务实例表索引重选

以调度平台的核心表 task_instance 为例，展示从MySQL迁移到PostgreSQL后的索引重选过程：

阶段	MySQL原索引	PostgreSQL新索引	性能提升
状态查询	`INDEX idx_status (status)`	`CREATE INDEX CONCURRENTLY idx_status_partial ON task_instance (status) WHERE status IN ('RUNNING','FAILED')`	索引体积缩小60%，写入性能提升35%
时间范围查询	`INDEX idx_create_time (create_time)`	`CREATE INDEX CONCURRENTLY idx_create_time_brin ON task_instance USING BRIN (create_time) WITH (pages_per_range=32)`	索引体积缩小90%，范围扫描速度提升2倍
任务依赖查询	`INDEX idx_dep (task_id, dep_task_id)`	`CREATE INDEX CONCURRENTLY idx_dep_gin ON task_instance USING GIN (dependencies)`（JSONB类型）	依赖解析查询从120ms降至8ms

    回滚验证结果： 在自动部署新索引后，监控系统检测到 idx_create_time_brin 在并发写入场景下出现索引膨胀，平均执行时间超过阈值1.5倍。流水线自动触发回滚，删除BRIN索引并回退至B-Tree索引，同时记录问题至工单系统。

4.6 索引重选最佳实践清单

先验证后部署： 在预发布环境使用真实负载回放，对比新旧索引的EXPLAIN输出，确保代价模型预估准确。
使用CONCURRENTLY： PostgreSQL中始终使用 CREATE INDEX CONCURRENTLY，避免阻塞调度平台的写入操作。
设置回滚超时： 自动回滚窗口建议设置为15分钟，超过此时间则锁定变更，由人工介入分析。
保留旧索引定义： 在索引变更版本化配置中保留完整的回滚DDL，确保回滚操作幂等。
监控索引使用率： 部署后持续监控 pg_stat_user_indexes 的 idx_scan 指标，识别未被使用的冗余索引。

进阶技巧： 对于调度平台中频繁更新的状态字段，考虑使用PostgreSQL的 covering index（INCLUDE子句）来避免回表查询，同时减少索引更新时的写放大。