1. MySQL优化全景图从单机到分布式MySQL作为最流行的开源关系型数据库其性能优化一直是开发者关注的焦点。经过多年实战我发现MySQL优化本质上是在解决三个核心矛盾查询速度与数据量的矛盾、并发访问与锁冲突的矛盾、单机性能与业务增长的矛盾。针对这些矛盾我们需要建立分层次的优化体系基础层优化索引设计与SQL调优解决单表查询效率问题架构层优化分库分表策略突破单机性能瓶颈补充优化连接池配置、参数调优等辅助手段重要提示优化前必须先建立完整的监控体系没有度量就没有优化。推荐使用Percona PMM或阿里云DAS等工具采集QPS、慢查询、锁等待等关键指标。1.1 索引设计的黄金法则索引是把双刃剑——好的索引能让查询飞起来坏的索引反而会拖慢写入速度。根据我的踩坑经验这些原则必须牢记最左前缀原则联合索引(a,b,c)只能用于a、ab或abc组合的查询ac查询无法走索引。我曾遇到一个案例某订单表有(status, create_time)的联合索引但查询WHERE create_time 2023-01-01 AND status1时全表扫描调整条件顺序后性能提升200倍。基数区分度原则选择区分度高的列建索引。比如性别字段只有两种值建索引几乎无效。可以用这个公式计算区分度SELECT COUNT(DISTINCT column)/COUNT(*) FROM table;结果越接近1区分度越高。覆盖索引优先当索引包含所有查询字段时无需回表。例如-- 需要回表 SELECT * FROM users WHERE name LIKE 张%; -- 覆盖索引 SELECT id, name FROM users WHERE name LIKE 张%;1.2 SQL语句的魔鬼细节即使有好的索引糟糕的SQL仍然会导致性能灾难。这些坑我几乎都踩过隐式类型转换当字段定义为varchar但传入数字时索引会失效-- 索引失效user_id是varchar类型 SELECT * FROM orders WHERE user_id 10086; -- 正确写法 SELECT * FROM orders WHERE user_id 10086;OR条件陷阱OR条件可能导致索引失效改用UNION ALL-- 低效写法 SELECT * FROM logs WHERE typelogin OR user_id123; -- 优化方案 SELECT * FROM logs WHERE typelogin UNION ALL SELECT * FROM logs WHERE user_id123 AND type!login;LIMIT分页优化传统分页随着offset增大会越来越慢-- 低效写法 SELECT * FROM articles ORDER BY id LIMIT 100000, 20; -- 优化方案记住上次的最大ID SELECT * FROM articles WHERE id 100000 ORDER BY id LIMIT 20;2. 分库分表实战从理论到落地当单表数据超过500万行或QPS超过3000时就该考虑分库分表了。根据业务特点通常有两种拆分方式2.1 垂直拆分大表瘦身术按字段关联性将宽表拆分为多个子表。去年我们优化过一个电商系统将包含80个字段的商品表拆解为商品基础表id,spu_id,name,price... 商品扩展表id,description,spec_json... 商品库存表id,stock,lock_stock...拆分后写入延迟降低了70%因为每次更新只需操作部分字段。2.2 水平拆分数据分片策略根据分片键将数据分散到不同库表。常用分片算法对比算法类型代表方案优点缺点适用场景Hash取模user_id % 16分布均匀扩容困难用户数据范围分片按时间范围易于扩容可能热点日志数据一致性Hash虚拟节点扩容影响小实现复杂需要弹性扩容血泪教训分片键的选择至关重要。我们曾用订单ID哈希分片结果发现商家查询需要扫描所有分片。后来改用商家ID作为分片键商家维度的查询性能提升10倍。2.3 分库分表中间件选型目前主流方案对比ShardingSphere推荐优势功能全面支持读写分离社区活跃坑点复杂SQL解析可能出错需要充分测试// SpringBoot配置示例 spring.shardingsphere.datasource.namesds0,ds1 spring.shardingsphere.sharding.tables.order.actual-data-nodesds$-{0..1}.order_$-{0..15} spring.shardingsphere.sharding.tables.order.table-strategy.inline.sharding-columnorder_id spring.shardingsphere.sharding.tables.order.table-strategy.inline.algorithm-expressionorder_$-{order_id % 16}MyCat优势成熟稳定支持跨库JOIN缺点性能开销较大已逐步被ShardingSphere取代自研方案适合有特殊需求的团队需要处理分布式事务、全局ID等复杂问题3. 高级优化技巧突破性能瓶颈3.1 索引跳跃扫描优化MySQL 8.0的新特性当联合索引前导列区分度低时也能利用索引。例如索引(status, create_time)-- 5.7及以下版本无法使用索引 SELECT * FROM orders WHERE create_time 2023-01-01; -- 8.0版本可以跳跃扫描status3.2 并行查询加速适合OLAP场景的大查询-- 启用并行查询 SET SESSION optimizer_switchparallel_queryon; SELECT /* PARALLEL(8) */ * FROM large_table WHERE create_time BETWEEN ...;3.3 冷热数据分离将历史数据归档到单独实例主库只保留热数据。我们采用的分层存储方案热数据3个月MySQL主库 SSD 温数据1年MySQL只读实例 普通磁盘 冷数据N年TiDB归档集群4. 避坑指南那些年踩过的雷4.1 索引失效的诡异场景除了常见的LIKE左模糊、函数转换这些情况也容易忽略字符集不一致JOIN字段字符集不同会导致索引失效ICP特性失效当WHERE条件包含子查询时索引条件下推可能失效统计信息过期当数据变化超过10%时记得执行ANALYZE TABLE4.2 分库分表的隐藏成本分布式事务跨库更新需要引入Seata等框架性能下降明显全局ID生成雪花算法可能产生大范围查询的IO问题跨库JOIN需要改造为多次查询内存计算复杂度陡增4.3 连接池配置玄学这些参数对性能影响巨大# Druid推荐配置8核16G机器 spring.datasource.druid.initial-size5 spring.datasource.druid.max-active20 spring.datasource.druid.min-idle5 spring.datasource.druid.max-wait3000 spring.datasource.druid.validation-querySELECT 1经过多次压测我们发现连接数不是越大越好当超过CPU核心数的2倍时上下文切换开销反而会降低吞吐量。