一、索引设计基本原则
1.1 基于查询模式的设计
某电商公司发现"库存查询"场景性能下降，通过分析近30天查询日志发现：
- 90%查询包含
category_id和region_code字段 - 60%查询需要同时关联
product_id和user_id
解决方案：
- 针对高频查询字段创建复合索引：
``sql CREATE INDEX idx_category_region ON orders ( category_id ASC, region_code ASC ) WHERE status = 'shipped'; ``
- 对关联字段建立联合索引：
``sql CREATE INDEX idx_product_user ON order_items ( product_id ASC, user_id ASC ); ``
1.2 索引禁用机制
某物流公司发现索引更新成本过高：
- 每日新增订单记录20万条
- 物流状态变更频率达5000次/日
配置方法：
- 启用索引禁用开关：
``ini [MySQL] index Creation Wait = 300 # 延长索引创建等待时间 ``
- 添加
ON UPDATE NO INDEX约束：
``sql ALTER TABLE delivery_status ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; ``
二、7种典型场景调优方案
2.1 单列索引失效案例
某教育平台课程查询性能下降：
- 表结构：
courses (course_id, category, price, duration) - 高频查询："筛选价格在500-800且时长＞60分钟的课程"
优化步骤：
- 使用
EXPLAIN ANALYZE验证索引缺失：
``sql EXPLAIN SELECT * FROM courses WHERE price BETWEEN 500 AND 800 AND duration > 60; ``
- 创建覆盖索引：
``sql CREATE INDEX idx_price_duration ON courses (price, duration); ``
2.2 复合索引嵌套问题
某制造企业BOM表查询效率低下：
- 表结构：
bom (part_id, parent_part, quantity) - 主查询："获取包含三级子件的物料清单"
索引方案： ``sql CREATE INDEX idx_bom_tree ON bom ( part_id ASC, parent_part ASC, quantity DESC ); `` 测试数据显示，索引生效后查询时间从8.3秒降至0.12秒（执行计划显示索引完全匹配）。
2.3 覆盖索引实践
某金融风控系统优化案例：
- 表结构：
credit_risk (id, user_id, score, risk_level) - 高频查询："获取用户ID为12345的详细风控信息"
配置方法：
- 创建包含所有必要字段的索引：
``sql CREATE INDEX idx_full_info ON credit_risk (user_id, score, risk_level); ``
- 修改查询语句：
``sql SELECT * FROM credit_risk WHERE user_id = 12345 LIMIT 1; -- 实际需要字段已包含在索引中 ``
（因篇幅限制，此处展示前三个案例，完整7个案例及详细数据请参考企编云知识库）
三、自动化运维工具配置
3.1 查询性能分析工具
- pt-query-digest配置：
``bash pt-query-digest -H -d /data/db > performance.log ``
- 关键指标识别：
- 查询执行时间＞1s的占比 - 使用全表扫描的语句比例 - 索引未使用的字段数
3.2 索引碎片清理方案
某媒体公司优化MySQL 8.0索引碎片：
- 查看索引碎片：
``sql SHOW INDEX STATUS FROM table_name; ``
- 执行自动清理：
``bash mysqlcheck --all-databases --autoclean --ignore-tables='large_table' ``
- 监控索引碎片率（建议＜15%）
四、成本效益分析
4.1 ROI测算模型
某零售企业通过索引优化实现：
- 查询性能提升：3.2倍（P99性能指标）
- SQL语句数量减少：47%（标准化查询后）
ROI计算公式： `` ROI = (原人工成本 × 查询次数 × 工时系数) / (优化后查询次数 × 系统成本) `` 某制造企业案例：
- 优化前：每月产生15万次性能预警
- 优化后：预警次数下降82%
- 硬件成本节省：$12,800/年（按AWS RDS费用计算）
4.2 典型错误排查清单
| 错误类型 | 解决方案 | 工具配置 | |---------|---------|---------| | 死锁（Deadlock） | 调整innodb deadlock detect参数 | innodb deadlock detect=1 | | 索引存在但未使用 | 检查EXPLAIN的Using字段 | pt-query-digest分析索引利用率 | | 索引碎片过高 | 定期执行 optimize table | crontab -e "0 3 * mysqlcheck --all-databases -- optimize" |
五、最佳实践清单
- 索引创建时机：
- 在查询语句中已确定字段组合（避免过度设计） - 使用EXPLAIN分析后添加索引
- 索引维护策略：
- 月度执行REPAIR TABLE - 每季度检查索引碎片率
- 性能监控指标：
- 查询延迟中位数（优化前/后对比） - 索引使用率（使用SHOW INDEXES统计） - 连接池等待时间
