1. 索引优化与写性能的平衡本质
当数据库规模达到百万级数据量时，DBA们都会面临一个经典困境：索引就像一把双刃剑。我处理过的一个电商平台案例中，商品表添加5个索引后查询速度提升40倍，但促销期间的订单写入延迟却从5ms飙升到120ms。这揭示了MySQL存储引擎的核心工作机制——每次INSERT/UPDATE/DELETE操作都需要同步维护所有相关索引的B+树结构。
InnoDB引擎采用MVCC机制实现写操作时，实际会产生以下隐藏开销：
- 检查唯一性约束（对唯一索引）
- 定位索引树插入位置
- 分裂页（当页空间不足时）
- 记录undo log（用于事务回滚）
- 维护聚簇索引的隐藏列（DB_TRX_ID等）
2. 索引设计黄金法则
2.1 选择性阈值判定
通过计算区分度来判断索引价值：
SELECT COUNT(DISTINCT column_name)/COUNT(*) AS selectivity FROM table_name;经验阈值：
0.2：适合建索引
- <0.1：考虑放弃索引
2.2 联合索引排列组合
用户行为日志表的优化案例：
-- 错误顺序（区分度低的在前） ALTER TABLE user_logs ADD INDEX idx_gender_create_time(gender, create_time); -- 正确顺序（区分度高的在前） ALTER TABLE user_logs ADD INDEX idx_create_time_gender(create_time, gender);实测表明后者查询效率提升8倍，写入速度差异小于3%。
3. 写入性能拯救方案
3.1 批量操作黑科技
对比实验数据（单位：ms）：
| 操作方式 | 1万条数据耗时 | 索引影响系数 |
|---|---|---|
| 单条INSERT | 12,500 | 1x |
| 批量INSERT(100条/次) | 350 | 0.028x |
| LOAD DATA INFILE | 82 | 0.0065x |
3.2 延迟索引技巧
电商大促期间的实战方案：
-- 创建临时表（无索引） CREATE TABLE temp_orders LIKE orders; ALTER TABLE temp_orders DROP INDEX idx_user_id; -- 数据导入 LOAD DATA INFILE '/tmp/orders.csv' INTO TABLE temp_orders; -- 业务低峰期重建索引 ALTER TABLE temp_orders ADD INDEX idx_user_id(user_id); RENAME TABLE orders TO orders_old, temp_orders TO orders;4. 监控与调优实战
4.1 性能瓶颈定位
关键监控指标：
-- 查看索引使用频率 SELECT * FROM sys.schema_index_statistics WHERE table_schema = 'your_db'; -- 检测冗余索引 SELECT * FROM sys.schema_redundant_indexes;4.2 动态调整策略
基于QPS的索引开关方案：
-- 业务高峰期 ALTER TABLE users ALTER INDEX idx_last_login INVISIBLE; -- 夜间分析时段 ALTER TABLE users ALTER INDEX idx_last_login VISIBLE;5. 架构级解决方案
5.1 读写分离拓扑
推荐部署架构：
主库（写） -> Binlog -> 从库1（读+索引A） -> 从库2（读+索引B） -> 从库3（无索引+OLAP）5.2 新型存储引擎
TokuDB引擎测试对比：
| 指标 | InnoDB | TokuDB |
|---|---|---|
| 写入速度 | 1x | 3.2x |
| 索引维护开销 | 高 | 极低 |
| 压缩率 | 1x | 5-10x |
在归档日志场景中，TokuDB的索引写入开销仅为InnoDB的17%。
