一、问题背景
最近遇到一个生产环境的多表关联查询性能问题，主表需要同时关联两个表：一个是配置小表（约1200行），一个是业务大表（约12万行）。查询响应时间从毫秒级逐渐恶化到秒级，急需优化。
二、表结构模拟
1. 小表 - 配置表（约1200行）
id BIGINT PRIMARY KEY AUTO_INCREMENT,
invoicing_group_no VARCHAR(50) NOT NULL COMMENT '开票组编号',
group_name VARCHAR(100) COMMENT '组名称',
status TINYINT DEFAULT 1 COMMENT '状态 1-启用 0-禁用',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
KEY idx_status (status)
) COMMENT='开票组配置表，约1200行数据';
2. 大表 - 审批主表（约12万行）
id BIGINT PRIMARY KEY AUTO_INCREMENT,
claim_approval_no VARCHAR(50) NOT NULL COMMENT '审批单号',
approval_status VARCHAR(20) COMMENT '审批状态',
amount DECIMAL(12,2) COMMENT '审批金额',
applicant_id BIGINT COMMENT '申请人ID',
apply_date DATE COMMENT '申请日期',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uk_approval_no (claim_approval_no),
KEY idx_apply_date (apply_date),
KEY idx_applicant (applicant_id)
) COMMENT='审批主表，约12万行数据';
3. 主表 - 业务主表（约100万行）
id BIGINT PRIMARY KEY AUTO_INCREMENT,
business_no VARCHAR(50) NOT NULL COMMENT '业务单号',
invoicing_group_no VARCHAR(50) NOT NULL COMMENT '关联开票组',
claim_approval_no VARCHAR(50) COMMENT '关联审批单号',
amount DECIMAL(10,2) COMMENT '金额',
status TINYINT DEFAULT 0 COMMENT '状态 0-待处理 1-已处理 2-已取消',
create_user_id BIGINT COMMENT '创建人',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uk_business_no (business_no),
KEY idx_status (status),
KEY idx_create_user (create_user_id),
KEY idx_created_at (created_at)
) COMMENT='业务主表，约100万行数据，需关联小表和大表';
三、查询场景
典型的联表查询：
SELECT m.*, g.group_name, a.approval_status, a.amount as approval_amount
FROM main_table m
JOIN group_table g ON m.invoicing_group_no = g.invoicing_group_no
JOIN approval_table a ON m.claim_approval_no = a.claim_approval_no
WHERE m.invoicing_group_no = 'GROUP001'
AND m.status = 1
AND g.status = 1
ORDER BY m.created_at DESC
LIMIT 100;
四、单表查询索引设计规则
在考虑联表索引前，先回顾单表查询索引的基本原则：
1. WHERE条件优先
- 最常用于WHERE条件的字段建索引
- 联合索引中，区分度高的放前面
- 示例：
INDEX(status, created_at)用于WHERE status=1 ORDER BY created_at
2. 等值查询在前，范围查询在后
INDEX(status, created_at) -- 适合 WHERE status=1 AND created_at > '2024-01-01'
-- 差索引：范围在前，等值在后
INDEX(created_at, status) -- 范围查询会中断索引使用
3. ORDER BY和GROUP BY优化
- 排序字段尽量放入索引
- 避免filesort，利用索引天然有序
- 示例：
INDEX(status, created_at)天然支持ORDER BY created_at
4. 覆盖索引原则
- 包含所有查询字段，避免回表
- 示例：
SELECT id, status, created_at可用INDEX(status, created_at, id)
5. 前缀索引技巧
- 字符串字段可只索引前N个字符
- 示例：
INDEX(column_name(20)) - 需平衡选择性和存储空间
五、联表查询索引优化方案
方案1：支持小表驱动的联合索引（推荐）
ALTER TABLE main_table ADD INDEX idx_group_approval (invoicing_group_no, claim_approval_no, status, created_at);
-- 小表：关联字段索引
ALTER TABLE group_table ADD INDEX idx_invoicing_group (invoicing_group_no, status);
-- 大表：关联字段索引
ALTER TABLE approval_table ADD INDEX idx_claim_approval (claim_approval_no);
为什么这个顺序？
invoicing_group_no在前：支持小表(1200行)快速过滤claim_approval_no第二：过滤后结果关联大表(12万行)status和created_at：覆盖查询条件和排序
方案2：区分度优先的联合索引
适用场景：
- 查询中经常单独用
claim_approval_no过滤 - 大表关联条件选择性更强
六、执行计划对比
通过EXPLAIN分析两种方案：
方案1执行计划（小表驱动）
2. SIMPLE m ref idx_group_approval idx_group_approval 52 const,const 2500 100.00
3. SIMPLE a eq_ref idx_claim_approval idx_claim_approval 52 m.claim_approval_no 1 100.00
✅ 优点：小表先过滤，结果集小，大表关联效率高
方案2执行计划
2. SIMPLE g eq_ref idx_invoicing_group idx_invoicing_group 52 m.invoicing_group_no 1 100.00
3. SIMPLE a eq_ref idx_claim_approval idx_claim_approval 52 m.claim_approval_no 1 100.00
⚠️ 注意：虽然执行顺序不同，但两者性能差异不大，取决于具体数据分布
七、优化建议总结
- 联合索引顺序：不是越大表的外键越靠前，要看优化器的执行策略
- 小表驱动原则：多数情况下，优化器会优先用小表过滤
- 覆盖索引：尽量让索引包含所有查询字段
- 定期分析：使用
ANALYZE TABLE更新统计信息 - 监控调整：通过慢查询日志持续优化
关键结论：在"小表驱动大表"的场景中，联合索引应该把"小表关联字段"放在前面，即使它的区分度较低。这能最大化支持优化器的执行策略，获得最佳性能。
八、性能验证
优化后性能对比：
- 优化前：2.3秒
- 优化后：0.15秒
- 提升：15倍
这个案例再次证明，理解MySQL优化器的工作原理，比单纯记忆规则更重要。结合实际数据分布和查询模式，才能做出最佳的索引设计决策。
