1. MySQL优化全攻略从索引设计到分库分表的实战手册刚接手一个日均百万级流量的电商系统时我发现商品列表页的响应时间经常突破2秒。通过EXPLAIN分析发现核心查询竟然进行了全表扫描。这个经历让我意识到MySQL优化不是锦上添花而是生死攸关的技能栈。本文将分享我在金融、电商领域积累的MySQL优化方法论涵盖索引设计、SQL调优到分库分表的完整知识体系。2. 索引优化B树背后的设计哲学2.1 索引选择的核心逻辑B树索引就像图书馆的目录系统——它决定了数据检索的效率。但索引不是越多越好我的团队曾遇到过一个表建了12个索引导致写入性能下降60%的案例。选择索引列时需要考虑区分度公式count(distinct col)/count(*) ≥ 0.3最左前缀原则联合索引(a,b,c)只能支持a|ab|abc查询覆盖索引EXPLAIN的Extra列出现Using index就是最佳状态-- 糟糕的索引示例区分度不足 ALTER TABLE users ADD INDEX idx_gender(gender); -- 优化后的复合索引 ALTER TABLE orders ADD INDEX idx_user_status(user_id, status);2.2 索引失效的七宗罪隐式类型转换WHERE user_id 10086user_id是int函数操作WHERE DATE(create_time) 2023-01-01模糊查询LIKE %keyword前导通配符OR条件除非所有OR列都有索引!操作WHERE status ! 1排序字段混合ORDER BY a ASC, b DESC索引列计算WHERE score 10 100实战技巧打开optimizer_trace可以查看索引选择过程 SET optimizer_traceenabledon;3. SQL语句优化从执行计划到改写策略3.1 EXPLAIN的深度解读执行计划是SQL优化的地图关键要看type列从优到差 system const eq_ref ref range index ALLrows列预估扫描行数与实际相差5倍以上需analyze tableExtra列Using filesort需要额外排序Using temporary创建临时表Using join buffer关联缓存-- 典型的分页优化避免OFFSET大数值 SELECT * FROM products WHERE id 1000 ORDER BY id LIMIT 10; -- 替代方案记住上次查询的最大ID SELECT * FROM products WHERE id last_max_id ORDER BY id LIMIT 10;3.2 连接查询的优化艺术当处理千万级表的JOIN时我总结出这些经验小表驱动原则永远让结果集小的表作为驱动表避免3表以上JOIN分解为多个查询在应用层处理巧用STRAIGHT_JOIN手动指定驱动表顺序临时表方案对复杂子查询先创建临时表-- 错误示范大表驱动 SELECT * FROM large_table l JOIN small_table s ON l.id s.id; -- 优化方案强制小表驱动 SELECT /* STRAIGHT_JOIN */ * FROM small_table s JOIN large_table l ON s.id l.id;4. 分库分表从架构设计到实战陷阱4.1 拆分策略的选择困境去年设计金融交易系统时我们面临这样的选择策略类型适用场景优点缺点水平拆分单表数据量大扩展性强跨分片查询复杂垂直拆分字段访问频次差异大业务解耦需要联查时性能差时间分片有明显时间特征管理简单热点数据集中最终我们采用用户ID哈希分片时间分片的二级拆分方案使QPS从500提升到12000。4.2 分库分表的中间件选型经过对比测试各方案表现ShardingSphere适合Java生态支持柔性事务MyCat配置复杂但功能全面VitessYouTube出品适合云原生自研方案成本高但可控性强# ShardingSphere配置示例 spring: shardingsphere: datasource: names: ds0,ds1 sharding: tables: t_order: actual-data-nodes: ds$-{0..1}.t_order_$-{0..15} table-strategy: inline: sharding-column: order_id algorithm-expression: t_order_$-{order_id % 16}5. 参数调优MySQL的隐藏开关5.1 内存参数黄金比例在32GB内存的数据库服务器上我的配置原则innodb_buffer_pool_size物理内存的70%22Gkey_buffer_sizeMyISAM表专用通常设128Mquery_cache_size高并发下建议关闭sort_buffer_size每个连接独占不宜过大2-4M[mysqld] innodb_buffer_pool_instances 8 # 匹配CPU核心数 innodb_io_capacity 2000 # SSD硬盘建议值 innodb_flush_neighbors 0 # SSD禁用相邻页刷新5.2 事务隔离级别的选择金融级系统推荐使用READ-COMMITTED行锁而非默认的REPEATABLE-READSET GLOBAL transaction_isolation READ-COMMITTED;这可以有效减少间隙锁带来的死锁问题在我们的支付系统中将死锁率降低了83%。6. 监控与持续优化体系6.1 必须监控的十大指标慢查询率超过0.5%需要预警连接数使用率max_used_connections/max_connections缓存命中率1 - (innodb_buffer_pool_reads/innodb_buffer_pool_read_requests)锁等待时间innodb_row_lock_waits复制延迟seconds_behind_master-- 实时查看锁情况 SELECT * FROM performance_schema.events_waits_current WHERE EVENT_NAME LIKE %lock%;6.2 优化案例电商大促备战去年双11前我们通过以下步骤优化系统SQL审计用pt-query-digest分析慢日志索引优化为TOP 20慢查询添加覆盖索引架构调整将商品库按类目拆分到不同实例预热缓存提前加载热点数据到buffer pool限流降级非核心查询走从库最终系统扛住了平日10倍的流量冲击平均响应时间保持在300ms以内。