1. 为什么MySQL优化是每个开发者必须掌握的技能十年前我刚接触MySQL时曾经天真地认为数据库就是简单的CRUD操作。直到某天线上系统突然崩溃排查发现是因为一个没有索引的查询拖垮了整个数据库。那次事故让我深刻认识到MySQL优化不是选修课而是每个后端开发者的必修技能。MySQL作为最流行的开源关系型数据库承载着互联网80%以上的结构化数据存储。但很多开发者在使用时都存在这样的误区只要SQL能跑出结果就行性能问题等出现再说。实际上好的MySQL优化应该像预防医学一样在系统设计阶段就考虑周全。2. 索引优化的艺术与科学2.1 索引的工作原理揭秘理解索引的底层原理是优化的第一步。MySQL的InnoDB引擎采用B树结构存储索引这种数据结构就像图书馆的目录系统——不需要遍历所有书架通过目录就能快速定位书籍位置。一个常见的误解是索引越多越好。我曾接手过一个表竟然有20多个索引导致写入性能极差。实际上每个索引都需要占用存储空间并在数据修改时维护索引越多维护成本越高。2.2 最该创建的四种索引类型主键索引InnoDB的表必须有主键如果没有显式指定引擎会自动创建一个6字节的隐藏主键。建议使用自增INT/BIGINT作为主键避免使用UUID等随机值这会导致频繁的页分裂。唯一索引确保字段值唯一性如用户名、手机号等。UNIQUE索引在查询时和普通索引性能相当但能保证数据完整性。复合索引多个字段组成的索引遵循最左前缀原则。比如索引(a,b,c)能优化WHERE a?、WHERE a? AND b?等条件但无法优化WHERE b?的查询。覆盖索引当索引包含查询所需的所有字段时引擎可以直接从索引获取数据而无需回表。通过EXPLAIN看到Using index就表示使用了覆盖索引。2.3 索引避坑指南索引失效的常见场景-- 使用函数导致索引失效 SELECT * FROM users WHERE DATE(create_time) 2023-01-01; -- 隐式类型转换 SELECT * FROM users WHERE phone 13800138000; -- phone是varchar类型 -- 使用!或操作符 SELECT * FROM users WHERE status ! 1; -- 使用前导通配符LIKE SELECT * FROM users WHERE name LIKE %张;索引选择性原则选择性不重复值数量/总行数。选择性越高索引效果越好。比如性别字段只有2-3个值建索引几乎没用而用户ID选择性接近1是最佳索引候选。实战经验使用SELECT COUNT(DISTINCT column)/COUNT(*) FROM table计算字段的选择性大于0.1的字段才考虑建索引。3. SQL语句优化的核心技巧3.1 读懂EXPLAIN执行计划EXPLAIN是优化SQL的瑞士军刀。关键指标解读列名重点关注值优化建议typeALL(全表扫描)必须添加索引index(全索引扫描)检查是否真正需要所有列range(索引范围扫描)理想状态keyNULL未使用索引rows数值越大性能越差考虑优化查询条件或索引ExtraUsing filesort需要优化排序Using temporary创建了临时表可能需优化3.2 高频优化场景实战场景1分页查询优化糟糕的实现SELECT * FROM orders ORDER BY id LIMIT 100000, 10;优化方案-- 方案1使用id上次最大id SELECT * FROM orders WHERE id 100000 ORDER BY id LIMIT 10; -- 方案2延迟关联 SELECT t.* FROM orders t JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 10) tmp ON t.id tmp.id;**场景2避免SELECT ***总是明确指定需要的字段-- 不好的写法 SELECT * FROM users WHERE id 1; -- 好的写法 SELECT id, name, email FROM users WHERE id 1;场景3JOIN优化确保JOIN字段有索引小表驱动大表小表放在左边避免多表JOIN超过3个表考虑反范式化设计3.3 事务与锁的注意事项事务隔离级别默认REPEATABLE-READ在大多数场景够用读多写少可考虑READ-COMMITTED避免长事务事务越短越好大事务拆分为小事务死锁排查SHOW ENGINE INNODB STATUS; -- 查看最近死锁信息4. 分库分表何时用与怎么用4.1 分库分表的三种时机数据量单表超过500万行非绝对取决于字段大小和查询复杂度吞吐量QPS超过2000SSD服务器业务需求需要隔离不同业务数据如多租户4.2 分片策略对比策略优点缺点适用场景范围分片易于扩展可能热点集中有时间序列特征的数据哈希分片数据分布均匀难以范围查询随机访问为主的场景目录分片灵活可控需要维护路由表业务规则复杂的场景4.3 分库分表中间件选型ShardingSphereApache项目功能全面支持多种分片策略MyCat老牌中间件文档丰富但更新较慢VitessYouTube出品适合超大规模场景踩坑提醒分库分表后跨分片查询、分布式事务、全局唯一ID都是需要解决的难题。建议初期尽量使用单库单表必要时再考虑分片。5. 性能监控与持续优化5.1 关键性能指标监控慢查询超过1秒的查询都需要关注-- 开启慢查询日志 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;连接数避免超过max_connections的80%SHOW STATUS LIKE Threads_connected;缓冲池命中率应保持在95%以上SELECT (1 - (SELECT variable_value FROM performance_schema.global_status WHERE variable_name Innodb_buffer_pool_reads) / (SELECT variable_value FROM performance_schema.global_status WHERE variable_name Innodb_buffer_pool_read_requests)) * 100 AS buffer_pool_hit_ratio;5.2 常用优化参数调优# InnoDB缓冲池大小建议物理内存的50-70% innodb_buffer_pool_size 4G # 日志文件大小太大恢复慢太小频繁切换 innodb_log_file_size 256M # 并发连接数根据实际需求调整 max_connections 200 # 查询缓存MySQL 8.0已移除低版本可关闭 query_cache_type 06. 真实案例电商系统优化实录去年我主导了一个电商平台的MySQL优化项目系统在促销期间频繁崩溃。通过以下步骤实现了10倍性能提升索引优化通过慢查询日志找出TOP 20慢SQL为它们添加合适的复合索引SQL重写将多个单行INSERT合并为批量INSERT减少网络开销架构调整将商品库按类目垂直分库订单表按月水平分表缓存策略对商品详情等热点数据引入Redis缓存优化前后关键指标对比指标优化前优化后平均响应时间1200ms150ms最大QPS5005000CPU使用率90%40%这个案例让我深刻体会到MySQL优化不是一次性的工作而是需要持续监控和调整的过程。每个系统都有其独特的数据特点和访问模式需要因地制宜地制定优化策略。