很多团队在数据库性能优化上花的力气不少但效果往往不尽如人意。慢查询依旧存在CPU 和 IO 压力没有明显缓解DBA 和开发互相甩锅的情况时有发生。问题根源通常不是某一个 SQL 写得太烂而是从索引设计、SQL 写法到整体架构缺乏一条清晰的优化主线。今天这篇东西从索引到架构把数据库性能优化的常见路径和实战经验完整过一遍重点回答两个问题瓶颈到底在哪里以及怎么一步一步把它根除掉。这篇内容适合正在排查线上事故的运维、被慢查询折磨的后端开发以及准备做系统容量评估的架构师。你会看到具体的排查命令、索引设计细节、SQL 改写思路还有读写分离和分库分表的架构演进方案全部都是可以直接抄作业的经验。1. 性能拆解先搞清楚瓶颈长什么样1.1 性能瓶颈的本质资源、等待与无效消耗数据库性能问题的表象千奇百怪有的表现为查询特别慢有的表现为 CPU 飙升有的是连接数被打满还有的是磁盘 IO 一直 100%。但追根溯源瓶颈无非三类资源瓶颈、等待瓶颈、无效消耗瓶颈。资源瓶颈最容易理解CPU 不够用、内存不足、磁盘太慢、网络带宽受限这些都是硬指标。比如一张千万级大表做全表扫描IO 瞬间被拉满这在机械硬盘时代尤其明显SSD 普及后有所缓解但数据量一旦上到亿级别即使全扫 SSD 也会卡。等待瓶颈则是锁等待、IO 等待、网络等待。很多开发者在排查时容易忽略锁等待因为 SQL 本身执行计划看着不错但实际跑下去就是等。行锁、表锁、间隙锁、元数据锁任何一个环节卡住后续事务全部排队。我遇到过最典型的案例是一个批量更新任务每次更新 1 万行持锁时间过长导致业务侧大量更新操作堆积数据库连接池被耗尽。无效消耗则和代码、SQL 写法的质量直接相关。比如 Java 应用里的 for 循环逐条 INSERT比如 N1 查询比如查一张大表却只用了 3 个字段却SELECT *把 20 个字段全捞出来。这些看起来是小问题但并发一上来数据库会在毫无意义的传输和解析上浪费大量资源。诊断瓶颈有一个基本原则先测量再判断最后优化。不要凭感觉说这个查询慢加个索引也不要上来就考虑分库分表大概率方向是错的。1.2 排查工具箱慢查询日志、执行计划与性能视图MySQL 的慢查询日志永远是排查的第一站。建议把慢查询阈值设低一点线上环境我习惯设为 1 秒开发环境甚至可以设 0.5 秒。很多生产事故从日志里其实早就能发现苗头只是没人去看。-- 开启慢查询日志 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; -- 查看是否开启 SHOW VARIABLES LIKE slow_query_log%; SHOW VARIABLES LIKE long_query_time;拿到慢 SQL 之后用EXPLAIN查看执行计划。MySQL 8.0 还支持EXPLAIN ANALYZE可以直接看到每个算子实际执行时间和行数。关注几个关键列type访问类型all 全表扫描一定有问题range 或 ref 比较正常、key实际使用的索引、rows预估扫描行数、Extra是否出现 filesort、temporary这两项往往是性能杀手。EXPLAIN SELECT * FROM orders WHERE user_id 1001 ORDER BY created_at DESC LIMIT 20;除了执行计划系统库performance_schema和sys库也很关键。sys库里有很多现成的视图比如sys.session可以看当前会话和锁等待sys.schema_unused_indexes可以直接找出那些建了但从来没被用过的冗余索引。很多团队索引越建越多最后写入性能被拖垮就是因为从来没清理过无用索引。2. 索引实战SQL 性能的第一道关卡2.1 索引选择的底层逻辑B树与回表聊索引之前得先明确一个底层事实绝大多数关系型数据库用的索引结构是 B树。B树相比 B 树的优势在于非叶子节点只存键值不存数据一个节点能容纳更多键树更矮磁盘 IO 次数更少叶子节点之间有指针串联范围查询可以直接顺着链表遍历性能非常好。主键索引是聚簇索引叶子节点直接存整行数据所以通过主键查询是最快的方式一次 B树查找就能拿到完整数据行。二级索引普通索引的叶子节点存的是主键值所以走二级索引查询时需要先找到主键再回表查到完整行这一趟来回就是额外开销。覆盖索引可以绕开回表问题让查询需要的数据直接存在于二级索引的叶子节点上这在高频查询场景里是性能提升的关键。有一个很容易被忽视的点主键设计。我见过不少表用 UUID 做聚簇索引主键结果插入时叶子节点频繁分裂页碎片化严重写入性能直线下降。自增整型主键能保证插入顺序性避免页分裂这是 MySQL 场景下的最佳实践。如果业务场景确实需要 UUID至少考虑用有序 UUID 或者雪花 ID。2.2 复合索引与最左前缀原则复合索引是优化多条件查询的核心手段。最左前缀原则说的是MySQL 的复合索引(a, b, c)本质上先按 a 排序再按 b 排序最后按 c 排序所以查询条件必须从 a 开始匹配跳跃中间列直接使用后面的列是走不了索引的。拿 mysql where条件a and b应该怎么建索引 这个典型场景来拆解。假如有这样一个查询SELECT * FROM orders WHERE user_id 123 AND status PAID;如果只是想覆盖这一条 SQL那(user_id, status)复合索引可以完美命中user_id 走等值匹配status 在索引内部继续进行过滤。但如果系统里还有大量WHERE user_id ? ORDER BY created_at的查询那(user_id, created_at)可能更合适。没有万能索引只能根据频率最高的那批查询来权衡。这里要特别注意区分等值和范围。复合索引中如果把范围查询的列放前面后面的列就没法走索引了。比如(status, created_at)查询条件是status PAID AND created_at 2024-01-01那 created_at 可以命中但如果反过来建(created_at, status)条件写created_at 2024-01-01 AND status PAIDstatus 字段无法继续走索引因为范围之后的列索引失效。这不是 MySQL 的 bug而是 B树索引结构上的天然限制。设计复合索引时等值条件列放前面范围条件列放后面。2.3 常见索引失效场景与避坑清单索引失效的坑踩一个就会线上吃大亏而且这类问题的排查往往要花很久。下面这些场景是真实环境中出现频率最高的。对索引列使用函数或者隐式类型转换会让优化器放弃索引。WHERE DATE(created_at) 2024-01-01这类写法直接绕开了 created_at 上的索引正确姿势是WHERE created_at 2024-01-01 AND created_at 2024-01-02。隐式转换典型场景是WHERE phone 13800000000如果 phone 字段是 varcharMySQL 可能会把字段转为数值进行比较造成索引失效。前导模糊查询LIKE %keyword无法使用索引但LIKE keyword%可以。这本质上是 B树的有序性决定的前缀确定时索引可以定位区间前缀未知时只能遍历全树。OR条件在 MySQL 里的处理也很微妙。如果A OR B中 A 能走索引B 不能那整体可能退化为全表扫描。MySQL 优化器确实有 index merge 机制但触发条件很苛刻建议把 OR 拆成两个查询 UNION 或者改写为 IN实测更稳。这里多说一句关于排序规则的坑。在 MySQL 中如果使用utf8mb4_general_ci这类大小写不敏感的排序规则字符串索引检索时不区分大小写应用层如果有大写和小写分别索引的需求会出现查不到或错乱的情况。这个坑在 Python 里调用 min 函数时尤其明显——数据里大小写单词混用索引看起来是一回事实际查出来的顺序完全是另一回事。解决办法是字段采用utf8mb4_bin排序规则或者在应用层统一做大小写转换。2.4 各种索引类型怎么选业界常说的索引类型不少除了常规的二级索引、复合索引还有覆盖索引、唯一索引、前缀索引、倒排索引、哈希索引以及用于全文检索的全文索引物化视图索引在不同数据库里也有差异。不同索引适用场景差异挺大。覆盖索引适合高频查询只查少量列。比如某表有 30 个字段业务上只关心订单号和订单金额那就试着建(order_no, amount)这种只含必要列的索引让查询完全不用回表。唯一索引适合业务上对某列唯一性有强约束的场景比如用户表的手机号、会员表的会员卡号。而且唯一索引在查询时能提前终止扫描性能通常比普通索引略好。前缀索引适合字符串列特别长的场景比如文本内容字段。只取前 N 个字符建索引可以显著减小索引体积但会让排序和分组变得不精确。比较长的 URL 列就是一个典型场景可以直接对前缀做索引。倒排索引才是真正解决全文搜索问题的利器和数据库 B树索引属于完全不同的原理和架构。像 MySQL 5.7 的全文索引、Elasticsearch 的倒排索引核心是分词 词项到文档的映射。如果你需要在数据库里做全文搜索应该考虑全文索引或独立搜索引擎而不是试图用 B树索引去LIKE %xx%那只是侥幸。还有 Oracle 的视图加索引问题。普通视图是虚拟表不存储实际数据在视图上创建索引是不可能的物化视图才会实际存储数据可以在物化视图上创建索引实现查询加速。很多从 MySQL 转 Oracle 的人容易踩这个认知误区。3. SQL 写法与查询引擎细节决定速度3.1 大表分页与深翻页优化分页查询可能是最常见也最容易被写坏的功能。LIMIT 10000, 20这种写法在数据量上了百万之后就会明显变慢。原因是数据库需要扫描并丢弃前 1 万行才能返回第 10001 到 10020 行扫描行数远大于实际需要返回的行数。我的建议很简单如果业务上允许不要用深翻页改成基于游标的方式。记住上一页最后一条记录的某个排序字段值下一页带着这个值去查SELECT id, name FROM users WHERE id 100020 ORDER BY id ASC LIMIT 20;这是最快的方式能稳定命中主键索引。如果排序字段不是唯一的就需要用复合索引来保证排序的稳定性比如(status, id)然后查询条件写成status NORMAL AND id 上次最大id。对于实在无法避免深翻页的场景可以考虑延迟关联技巧。先快速从索引中定位所需页的主键范围再回表获取完整数据SELECT * FROM orders INNER JOIN ( SELECT id FROM orders ORDER BY id LIMIT 100000, 20 ) AS tmp ON orders.id tmp.id;这个方式比直接LIMIT 深翻页快很多因为子查询只扫索引。3.2 JOIN、临时表与排序的隐藏开销很多慢查询问题出在 JOIN 的连接策略上。MySQL 默认使用 nested-loop join驱动表每出 1 行就去被驱动表里检索一次所以驱动表的大小直接影响性能。优化器会倾向于选择小表做驱动表但如果统计信息不准或者没有相关索引join 的代价会成倍增长。JOIN 条件中的字段必须有索引这是最基本的要求。两表 join被驱动表的连接列没有索引每次匹配都要全表扫那性能彻底完蛋。另外尽量减少 JOIN 表的数量三张以上 JOIN 的优化难度会指数上升考虑先查一部分数据再二次处理。ORDER BY出现 filesort 也是一个大坑。filesort 并不一定在磁盘完成但一定代表 MySQL 需要额外进行排序操作。最优解是让排序字段走索引——复合索引已经天然有序ORDER BY直接命中索引省掉排序环节性能拉满。临时表的情况更隐蔽。UNION、GROUP BY、DISTINCT都可能产生临时表。当结果集很大时MySQL 会在磁盘上建临时表IO 开销飙升。EXPLAIN的Extra列一旦出现Using temporary就要警惕并尝试改写。GROUP BY 的优化思路通常是保证分组列走索引或者改用COUNT配合精确条件。记住一点临时表不是不能用而是要确保它足够小。3.3 连接池、预处理语句与批处理的正确姿势应用层与数据库的连接建立非常昂贵需要 TCP 握手、认证、创建会话。所以数据库连接池是标配。我在生产环境常用的连接池参数如下参数推荐值说明initialSize5初始化连接数maxActive50最大连接数避免过高压垮数据库minIdle5最小空闲连接maxWait3000ms获取连接的超时时间避免线程无限等待validationQuerySELECT 1连接有效性检查maxActive 不建议调太大。连接数增加会让数据库的上下文切换变多偶尔还会触发连接风暴。如果把 200 个连接一口气放出来数据库忙不过来反而更慢。预处理语句PreparedStatement在 Java、PHP 等语言中应该无脑使用。性能提升来源于两个方面SQL 模板只需要解析一次参数通过协议单独传安全性也更好能天然防住 SQL 注入。大批量写入场景priority 一定是用批量处理。一条条 INSERT 和一次性插入 1000 条性能差距可以达到 10 倍以上。MySQL 的 rewriteBatch 和 rewriteBatchedStatements 参数配合使用批量插入性能会有质变// JDBC 连接串中开启批量重写 jdbc:mysql://localhost:3306/app?useServerPrepStmtstruerewriteBatchedStatementstrueJDBC 里的addBatch()配合executeBatch()如果不开 rewrite 参数MySQL 依然是逐条执行开了之后才会真正拼成多值 INSERT 一次发送性能提升非常明显。4. 架构演进从单库到分布式4.1 读写分离让查询与写入各走各的索引和 SQL 优化是道这是术。但当 QPS 稳定到了单库极限或者读流量远超写流量的时候就要看架构层了。读写分离的核心思路很朴素把主库的压力释放出来只处理写入读流量均匀分摊到多个只读从库。实现方式通常有 MySQL 主从复制 应用层路由或者是中间件代理比如 ProxySQL。值得提醒的是主从复制有延迟。延迟的根源是主库写入产生了 binlog从库通过 IO 线程拉取日志再经过 SQL 线程重放这一步是串行的。一旦主库写入压力大或者跑大批量变更任务从库延迟就可能飙到秒级甚至分钟级。业务上凡是要求强一致性的读必须强制路由到主库比如支付结果、订单状态那些允许秒级延迟的报表查询、列表查询才适合走从库。主从架构不能解决数据量单机存不下的问题。一个 2T 的数据库即使拆分读写单机 IO 和容量就是天花板。读写分离解决的是并发读和写互相抢占资源的问题数据容量问题要靠后面的分库分表。数据库同步工具在读写分离架构里是极其重要的基础设施。主从复制是 MySQL 原生能力但跨数据库实例之间的数据同步比如 MySQL 到 ES、MySQL 到 ClickHouse通常需要专门工具。这类同步工具需要满足两个要求实时性足够第二是支持断点续传否则一个网络抖动就永久丢数据那是灾难。整体同步链路的监控也很重要延迟、落库位点这些指标都要可视化。4.2 分库分表什么时候做怎么做分库分表是数据库性能优化的终极大招也是风险最大的操作。不到万不得已不建议主动分库分表。判断标准有这三点单表数据量超过千万甚至亿级别或者单库 QPS 长期吃紧写入吞吐无法通过加缓存、读写分离解决或者磁盘容量即将达到硬件上限。满足其一就得考虑架构演进了。分库分表有两个维度。垂直拆分是把一张大表按字段拆成多张表按业务维度拆分库。比如订单表有 30 个字段把详情拆到订单扩展表热点字段留在主表减少单表宽度和 IO 开销。水平拆分是把同一张表的数据按某种规则散到多个库多张表里比如按 user_id 取模或者按订单号分片。取模的方式简单易实现但后续扩容要重新迁移数据按范围分片比如按时间分区扩容容易但可能出现热点分片。分片键的选择是最关键的设计。一旦选错后续所有查询都得改写。如果业务查询绝大多数都带 user_id那 user_id 就是天选分片键。但有些跨分片查询无法避免只能通过汇总层或者中间件层做二次聚合。这也是为什么很多人从分库分表走向分布式数据库——开源分布式数据库内置了分片、分布式事务、全局索引对开发者更友好。分库分表后的全局主键是另一个难点。业务上的全局唯一 ID 不能再用数据库自增必须用全局发号器比如雪花算法或者号段模式。很多基础架构团队直接用第三方组件做 ID 生成。关键点在于ID 必须是趋势递增这样能大幅降低分片索引的随机 IO 压力。4.3 缓存与微服务架构下的数据一致性博弈缓存永远比数据库快几个数量级。Redis 承载每秒几十万次 QPS 毫无压力数据库能做到这个量的极少。但缓存引入后最大的敌人是缓存和数据库的数据一致性。缓存策略上Cache Aside 是最通用也最容易被接受的一种模式读的时候先查缓存缓存不存在就查数据库再写回缓存写的时候先更新数据库再删除缓存。双删缓存这种操作我建议谨慎使用它本质是对其他方案妥协的产物更优雅的处理方式是写操作更新数据库同步发送消息消费者延迟删除缓存。核心原则是不能让旧数据长期占据缓存。缓存穿透、缓存击穿、缓存雪崩这三个问题是面试题常客也是线上事故常客。穿透是查询一个不存在的 key缓存和数据库都没有击穿是指缓存失效瞬间有大量请求打到数据库雪崩是大量 key 同时过期。解决方案分别是布隆过滤器拦截不存在的 key热点数据用互斥锁重建缓存过期时间加随机数打散。微服务架构下各个服务独立部署数据库可能被多个服务共享或服务隔离。服务化了以后事务从本地事务变成分布式事务。2PC / TCC / 最终一致性没有一个方案是万能的。多数业务场景尤其是电商下单、支付这种链路最终一致性加上消息对账才是主流。如果每个业务都强一致性能和可靠性反而都会出问题。架构演进这条主线其实很像盖房子扩建一开始一间小房间后来住的人多了开始多隔几间读写分离再后来房间也不够用了只能盖新楼分库分表最后干脆设计成一个园区微服务 分布式数据库 缓存层。每一步都有成本每一步都要在你当前的瓶颈真正到来时再动工。5. 监控体系与常见问题速查把优化成果固化下来5.1 监控指标与告警配置优化完一轮如果监控没有跟上相当于做完手术没缝针早晚还得崩。监控数据库性能我用的是三层指标体系资源层是最基本的CPU、内存、磁盘 IO、网络带宽这些指标直接反映硬件的健康状况。MySQL 的 CPU 和 IO 占比高不一定是坏事但如果所有磁盘都在读说明有大面积的全表扫描正在发生需要立刻定位 SQL。服务层是数据库自身的状态。连接数、线程运行数、慢查询数量、锁等待次数、临时表数量、缓冲池命中率这些都是核心指标。缓冲池命中率如果低于 95%说明内存可能不够或者热点数据没有被有效缓存。业务层是 SQL 的延迟和错误率。通过调用链监控能看到每次数据库访问耗时如果 P99 延迟持续变高通常意味着某些查询在扫描大数据量或者锁竞争加剧。建议搭建一套覆盖这三层的监控体系。Prometheus Grafana 加 mysqld_exporter 是开源方案里比较成熟的组合配置简单社区文档也全。告警阈值方面我习惯在慢查询数量持续 5 分钟超过阈值时告警锁等待超过 3 秒告警连接数超过 maxActive 的 80% 告警。不要一个指标高了就全量告警守住少量高价值指标即可。5.2 高频问题速查表现象可能原因排查方向解决建议一条 SQL 突然变慢执行计划改变、统计信息过期EXPLAIN 对比前后计划ANALYZE TABLE 更新统计信息必要时 use index 强制索引CPU 持续 100%大量慢查询、无索引扫描、排序归并慢查询日志 processlist 看 Time补索引、改 SQL、限制并发连接数耗尽连接池配置过高、SQL 慢导致持连接久看 processlist 的 Command 和 Time调连接池参数、优化慢 SQL数据库死锁事务顺序不一致、间隙锁冲突SHOW ENGINE INNODB STATUS统一事务内语句的访问顺序适度缩小事务范围主从延迟高大事务、DDL、binlog 量过大SHOW SLAVE STATUS拆分大事务控制单次 DML 行数低峰期执行 DDL查询结果集大但字段很少SELECT * 全字段返回检查应用 SQL 习惯改写为只查所需字段批量更新导致锁等待超时大批量 update 持锁时间长会话列表看 Lock time分批执行每批 500~1000 行这个表是我在多次故障复盘后整理的基本覆盖了中小团队最常见的八类问题。遇到类似现象照着排查方向走大部分情况都能在短时间内定位。5.3 优化效果的验证与回归优化完不能拍拍屁股就完事一定要做前后对比同时防止按下葫芦浮起瓢。量化验证的方式很直接记录优化前后的 SQL 平均耗时、扫描行数、CPU 使用率、QPS、锁等待次数等指标。建议先在测试环境或压测环境做全量回归重点是确认新增索引没有让写入变慢。回归测试要覆盖至少三类场景该查询的正常参数、临界参数比如分页到极限深度、全表扫描场景。如果原来 3 秒的查询优化到 50 毫秒目标达成但同时要观察同表的 INSERT、UPDATE 耗时有没有显著上升。我习惯每个季度或者半年做一次慢查询全量 review。把过去半年的慢查询日志拉出来按执行次数和累计耗时排序重复出现的 SQL 优先治理。有些 SQL 其实执行一次很快但每秒调用上百次累计成本反而最高。这个 review 机制比临时救火有效得多因为它是持续地压低全链路的性能债务。绝大多数系统只要养成了季度 review 的习惯性能问题基本不会发展到需要分库分表那一步。做了这么多年数据库性能优化我个人最大的体会是不要总指望某一个技巧拯救全场性能优化是一个系统工程。索引设计是地基中的地基SQL 写法是日常习惯监控体系是安全网架构演进是最后的底牌。这四个层面的优先级顺序也是我要反复强调的——先修索引和 SQL 的问题再谈架构。很多人拿到慢 SQL 的第一反应是上缓存、上分库分表结果发现数据都同步不过来问题根本没解决。反过来先把索引设计到位SQL 写得清爽连接池配置合理很多系统根本走不到架构那一步。最后再分享一个小技巧每次上线数据库变更顺手把变更前后的慢查询数量、平均延迟截图保存。半年后再看你就能清晰感知到自己的每一次改动给系统带来了什么这种感觉比任何监控大盘都真实。
从索引到架构：数据库性能优化的完整排查与实践指南
很多团队在数据库性能优化上花的力气不少但效果往往不尽如人意。慢查询依旧存在CPU 和 IO 压力没有明显缓解DBA 和开发互相甩锅的情况时有发生。问题根源通常不是某一个 SQL 写得太烂而是从索引设计、SQL 写法到整体架构缺乏一条清晰的优化主线。今天这篇东西从索引到架构把数据库性能优化的常见路径和实战经验完整过一遍重点回答两个问题瓶颈到底在哪里以及怎么一步一步把它根除掉。这篇内容适合正在排查线上事故的运维、被慢查询折磨的后端开发以及准备做系统容量评估的架构师。你会看到具体的排查命令、索引设计细节、SQL 改写思路还有读写分离和分库分表的架构演进方案全部都是可以直接抄作业的经验。1. 性能拆解先搞清楚瓶颈长什么样1.1 性能瓶颈的本质资源、等待与无效消耗数据库性能问题的表象千奇百怪有的表现为查询特别慢有的表现为 CPU 飙升有的是连接数被打满还有的是磁盘 IO 一直 100%。但追根溯源瓶颈无非三类资源瓶颈、等待瓶颈、无效消耗瓶颈。资源瓶颈最容易理解CPU 不够用、内存不足、磁盘太慢、网络带宽受限这些都是硬指标。比如一张千万级大表做全表扫描IO 瞬间被拉满这在机械硬盘时代尤其明显SSD 普及后有所缓解但数据量一旦上到亿级别即使全扫 SSD 也会卡。等待瓶颈则是锁等待、IO 等待、网络等待。很多开发者在排查时容易忽略锁等待因为 SQL 本身执行计划看着不错但实际跑下去就是等。行锁、表锁、间隙锁、元数据锁任何一个环节卡住后续事务全部排队。我遇到过最典型的案例是一个批量更新任务每次更新 1 万行持锁时间过长导致业务侧大量更新操作堆积数据库连接池被耗尽。无效消耗则和代码、SQL 写法的质量直接相关。比如 Java 应用里的 for 循环逐条 INSERT比如 N1 查询比如查一张大表却只用了 3 个字段却SELECT *把 20 个字段全捞出来。这些看起来是小问题但并发一上来数据库会在毫无意义的传输和解析上浪费大量资源。诊断瓶颈有一个基本原则先测量再判断最后优化。不要凭感觉说这个查询慢加个索引也不要上来就考虑分库分表大概率方向是错的。1.2 排查工具箱慢查询日志、执行计划与性能视图MySQL 的慢查询日志永远是排查的第一站。建议把慢查询阈值设低一点线上环境我习惯设为 1 秒开发环境甚至可以设 0.5 秒。很多生产事故从日志里其实早就能发现苗头只是没人去看。-- 开启慢查询日志 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; -- 查看是否开启 SHOW VARIABLES LIKE slow_query_log%; SHOW VARIABLES LIKE long_query_time;拿到慢 SQL 之后用EXPLAIN查看执行计划。MySQL 8.0 还支持EXPLAIN ANALYZE可以直接看到每个算子实际执行时间和行数。关注几个关键列type访问类型all 全表扫描一定有问题range 或 ref 比较正常、key实际使用的索引、rows预估扫描行数、Extra是否出现 filesort、temporary这两项往往是性能杀手。EXPLAIN SELECT * FROM orders WHERE user_id 1001 ORDER BY created_at DESC LIMIT 20;除了执行计划系统库performance_schema和sys库也很关键。sys库里有很多现成的视图比如sys.session可以看当前会话和锁等待sys.schema_unused_indexes可以直接找出那些建了但从来没被用过的冗余索引。很多团队索引越建越多最后写入性能被拖垮就是因为从来没清理过无用索引。2. 索引实战SQL 性能的第一道关卡2.1 索引选择的底层逻辑B树与回表聊索引之前得先明确一个底层事实绝大多数关系型数据库用的索引结构是 B树。B树相比 B 树的优势在于非叶子节点只存键值不存数据一个节点能容纳更多键树更矮磁盘 IO 次数更少叶子节点之间有指针串联范围查询可以直接顺着链表遍历性能非常好。主键索引是聚簇索引叶子节点直接存整行数据所以通过主键查询是最快的方式一次 B树查找就能拿到完整数据行。二级索引普通索引的叶子节点存的是主键值所以走二级索引查询时需要先找到主键再回表查到完整行这一趟来回就是额外开销。覆盖索引可以绕开回表问题让查询需要的数据直接存在于二级索引的叶子节点上这在高频查询场景里是性能提升的关键。有一个很容易被忽视的点主键设计。我见过不少表用 UUID 做聚簇索引主键结果插入时叶子节点频繁分裂页碎片化严重写入性能直线下降。自增整型主键能保证插入顺序性避免页分裂这是 MySQL 场景下的最佳实践。如果业务场景确实需要 UUID至少考虑用有序 UUID 或者雪花 ID。2.2 复合索引与最左前缀原则复合索引是优化多条件查询的核心手段。最左前缀原则说的是MySQL 的复合索引(a, b, c)本质上先按 a 排序再按 b 排序最后按 c 排序所以查询条件必须从 a 开始匹配跳跃中间列直接使用后面的列是走不了索引的。拿 mysql where条件a and b应该怎么建索引 这个典型场景来拆解。假如有这样一个查询SELECT * FROM orders WHERE user_id 123 AND status PAID;如果只是想覆盖这一条 SQL那(user_id, status)复合索引可以完美命中user_id 走等值匹配status 在索引内部继续进行过滤。但如果系统里还有大量WHERE user_id ? ORDER BY created_at的查询那(user_id, created_at)可能更合适。没有万能索引只能根据频率最高的那批查询来权衡。这里要特别注意区分等值和范围。复合索引中如果把范围查询的列放前面后面的列就没法走索引了。比如(status, created_at)查询条件是status PAID AND created_at 2024-01-01那 created_at 可以命中但如果反过来建(created_at, status)条件写created_at 2024-01-01 AND status PAIDstatus 字段无法继续走索引因为范围之后的列索引失效。这不是 MySQL 的 bug而是 B树索引结构上的天然限制。设计复合索引时等值条件列放前面范围条件列放后面。2.3 常见索引失效场景与避坑清单索引失效的坑踩一个就会线上吃大亏而且这类问题的排查往往要花很久。下面这些场景是真实环境中出现频率最高的。对索引列使用函数或者隐式类型转换会让优化器放弃索引。WHERE DATE(created_at) 2024-01-01这类写法直接绕开了 created_at 上的索引正确姿势是WHERE created_at 2024-01-01 AND created_at 2024-01-02。隐式转换典型场景是WHERE phone 13800000000如果 phone 字段是 varcharMySQL 可能会把字段转为数值进行比较造成索引失效。前导模糊查询LIKE %keyword无法使用索引但LIKE keyword%可以。这本质上是 B树的有序性决定的前缀确定时索引可以定位区间前缀未知时只能遍历全树。OR条件在 MySQL 里的处理也很微妙。如果A OR B中 A 能走索引B 不能那整体可能退化为全表扫描。MySQL 优化器确实有 index merge 机制但触发条件很苛刻建议把 OR 拆成两个查询 UNION 或者改写为 IN实测更稳。这里多说一句关于排序规则的坑。在 MySQL 中如果使用utf8mb4_general_ci这类大小写不敏感的排序规则字符串索引检索时不区分大小写应用层如果有大写和小写分别索引的需求会出现查不到或错乱的情况。这个坑在 Python 里调用 min 函数时尤其明显——数据里大小写单词混用索引看起来是一回事实际查出来的顺序完全是另一回事。解决办法是字段采用utf8mb4_bin排序规则或者在应用层统一做大小写转换。2.4 各种索引类型怎么选业界常说的索引类型不少除了常规的二级索引、复合索引还有覆盖索引、唯一索引、前缀索引、倒排索引、哈希索引以及用于全文检索的全文索引物化视图索引在不同数据库里也有差异。不同索引适用场景差异挺大。覆盖索引适合高频查询只查少量列。比如某表有 30 个字段业务上只关心订单号和订单金额那就试着建(order_no, amount)这种只含必要列的索引让查询完全不用回表。唯一索引适合业务上对某列唯一性有强约束的场景比如用户表的手机号、会员表的会员卡号。而且唯一索引在查询时能提前终止扫描性能通常比普通索引略好。前缀索引适合字符串列特别长的场景比如文本内容字段。只取前 N 个字符建索引可以显著减小索引体积但会让排序和分组变得不精确。比较长的 URL 列就是一个典型场景可以直接对前缀做索引。倒排索引才是真正解决全文搜索问题的利器和数据库 B树索引属于完全不同的原理和架构。像 MySQL 5.7 的全文索引、Elasticsearch 的倒排索引核心是分词 词项到文档的映射。如果你需要在数据库里做全文搜索应该考虑全文索引或独立搜索引擎而不是试图用 B树索引去LIKE %xx%那只是侥幸。还有 Oracle 的视图加索引问题。普通视图是虚拟表不存储实际数据在视图上创建索引是不可能的物化视图才会实际存储数据可以在物化视图上创建索引实现查询加速。很多从 MySQL 转 Oracle 的人容易踩这个认知误区。3. SQL 写法与查询引擎细节决定速度3.1 大表分页与深翻页优化分页查询可能是最常见也最容易被写坏的功能。LIMIT 10000, 20这种写法在数据量上了百万之后就会明显变慢。原因是数据库需要扫描并丢弃前 1 万行才能返回第 10001 到 10020 行扫描行数远大于实际需要返回的行数。我的建议很简单如果业务上允许不要用深翻页改成基于游标的方式。记住上一页最后一条记录的某个排序字段值下一页带着这个值去查SELECT id, name FROM users WHERE id 100020 ORDER BY id ASC LIMIT 20;这是最快的方式能稳定命中主键索引。如果排序字段不是唯一的就需要用复合索引来保证排序的稳定性比如(status, id)然后查询条件写成status NORMAL AND id 上次最大id。对于实在无法避免深翻页的场景可以考虑延迟关联技巧。先快速从索引中定位所需页的主键范围再回表获取完整数据SELECT * FROM orders INNER JOIN ( SELECT id FROM orders ORDER BY id LIMIT 100000, 20 ) AS tmp ON orders.id tmp.id;这个方式比直接LIMIT 深翻页快很多因为子查询只扫索引。3.2 JOIN、临时表与排序的隐藏开销很多慢查询问题出在 JOIN 的连接策略上。MySQL 默认使用 nested-loop join驱动表每出 1 行就去被驱动表里检索一次所以驱动表的大小直接影响性能。优化器会倾向于选择小表做驱动表但如果统计信息不准或者没有相关索引join 的代价会成倍增长。JOIN 条件中的字段必须有索引这是最基本的要求。两表 join被驱动表的连接列没有索引每次匹配都要全表扫那性能彻底完蛋。另外尽量减少 JOIN 表的数量三张以上 JOIN 的优化难度会指数上升考虑先查一部分数据再二次处理。ORDER BY出现 filesort 也是一个大坑。filesort 并不一定在磁盘完成但一定代表 MySQL 需要额外进行排序操作。最优解是让排序字段走索引——复合索引已经天然有序ORDER BY直接命中索引省掉排序环节性能拉满。临时表的情况更隐蔽。UNION、GROUP BY、DISTINCT都可能产生临时表。当结果集很大时MySQL 会在磁盘上建临时表IO 开销飙升。EXPLAIN的Extra列一旦出现Using temporary就要警惕并尝试改写。GROUP BY 的优化思路通常是保证分组列走索引或者改用COUNT配合精确条件。记住一点临时表不是不能用而是要确保它足够小。3.3 连接池、预处理语句与批处理的正确姿势应用层与数据库的连接建立非常昂贵需要 TCP 握手、认证、创建会话。所以数据库连接池是标配。我在生产环境常用的连接池参数如下参数推荐值说明initialSize5初始化连接数maxActive50最大连接数避免过高压垮数据库minIdle5最小空闲连接maxWait3000ms获取连接的超时时间避免线程无限等待validationQuerySELECT 1连接有效性检查maxActive 不建议调太大。连接数增加会让数据库的上下文切换变多偶尔还会触发连接风暴。如果把 200 个连接一口气放出来数据库忙不过来反而更慢。预处理语句PreparedStatement在 Java、PHP 等语言中应该无脑使用。性能提升来源于两个方面SQL 模板只需要解析一次参数通过协议单独传安全性也更好能天然防住 SQL 注入。大批量写入场景priority 一定是用批量处理。一条条 INSERT 和一次性插入 1000 条性能差距可以达到 10 倍以上。MySQL 的 rewriteBatch 和 rewriteBatchedStatements 参数配合使用批量插入性能会有质变// JDBC 连接串中开启批量重写 jdbc:mysql://localhost:3306/app?useServerPrepStmtstruerewriteBatchedStatementstrueJDBC 里的addBatch()配合executeBatch()如果不开 rewrite 参数MySQL 依然是逐条执行开了之后才会真正拼成多值 INSERT 一次发送性能提升非常明显。4. 架构演进从单库到分布式4.1 读写分离让查询与写入各走各的索引和 SQL 优化是道这是术。但当 QPS 稳定到了单库极限或者读流量远超写流量的时候就要看架构层了。读写分离的核心思路很朴素把主库的压力释放出来只处理写入读流量均匀分摊到多个只读从库。实现方式通常有 MySQL 主从复制 应用层路由或者是中间件代理比如 ProxySQL。值得提醒的是主从复制有延迟。延迟的根源是主库写入产生了 binlog从库通过 IO 线程拉取日志再经过 SQL 线程重放这一步是串行的。一旦主库写入压力大或者跑大批量变更任务从库延迟就可能飙到秒级甚至分钟级。业务上凡是要求强一致性的读必须强制路由到主库比如支付结果、订单状态那些允许秒级延迟的报表查询、列表查询才适合走从库。主从架构不能解决数据量单机存不下的问题。一个 2T 的数据库即使拆分读写单机 IO 和容量就是天花板。读写分离解决的是并发读和写互相抢占资源的问题数据容量问题要靠后面的分库分表。数据库同步工具在读写分离架构里是极其重要的基础设施。主从复制是 MySQL 原生能力但跨数据库实例之间的数据同步比如 MySQL 到 ES、MySQL 到 ClickHouse通常需要专门工具。这类同步工具需要满足两个要求实时性足够第二是支持断点续传否则一个网络抖动就永久丢数据那是灾难。整体同步链路的监控也很重要延迟、落库位点这些指标都要可视化。4.2 分库分表什么时候做怎么做分库分表是数据库性能优化的终极大招也是风险最大的操作。不到万不得已不建议主动分库分表。判断标准有这三点单表数据量超过千万甚至亿级别或者单库 QPS 长期吃紧写入吞吐无法通过加缓存、读写分离解决或者磁盘容量即将达到硬件上限。满足其一就得考虑架构演进了。分库分表有两个维度。垂直拆分是把一张大表按字段拆成多张表按业务维度拆分库。比如订单表有 30 个字段把详情拆到订单扩展表热点字段留在主表减少单表宽度和 IO 开销。水平拆分是把同一张表的数据按某种规则散到多个库多张表里比如按 user_id 取模或者按订单号分片。取模的方式简单易实现但后续扩容要重新迁移数据按范围分片比如按时间分区扩容容易但可能出现热点分片。分片键的选择是最关键的设计。一旦选错后续所有查询都得改写。如果业务查询绝大多数都带 user_id那 user_id 就是天选分片键。但有些跨分片查询无法避免只能通过汇总层或者中间件层做二次聚合。这也是为什么很多人从分库分表走向分布式数据库——开源分布式数据库内置了分片、分布式事务、全局索引对开发者更友好。分库分表后的全局主键是另一个难点。业务上的全局唯一 ID 不能再用数据库自增必须用全局发号器比如雪花算法或者号段模式。很多基础架构团队直接用第三方组件做 ID 生成。关键点在于ID 必须是趋势递增这样能大幅降低分片索引的随机 IO 压力。4.3 缓存与微服务架构下的数据一致性博弈缓存永远比数据库快几个数量级。Redis 承载每秒几十万次 QPS 毫无压力数据库能做到这个量的极少。但缓存引入后最大的敌人是缓存和数据库的数据一致性。缓存策略上Cache Aside 是最通用也最容易被接受的一种模式读的时候先查缓存缓存不存在就查数据库再写回缓存写的时候先更新数据库再删除缓存。双删缓存这种操作我建议谨慎使用它本质是对其他方案妥协的产物更优雅的处理方式是写操作更新数据库同步发送消息消费者延迟删除缓存。核心原则是不能让旧数据长期占据缓存。缓存穿透、缓存击穿、缓存雪崩这三个问题是面试题常客也是线上事故常客。穿透是查询一个不存在的 key缓存和数据库都没有击穿是指缓存失效瞬间有大量请求打到数据库雪崩是大量 key 同时过期。解决方案分别是布隆过滤器拦截不存在的 key热点数据用互斥锁重建缓存过期时间加随机数打散。微服务架构下各个服务独立部署数据库可能被多个服务共享或服务隔离。服务化了以后事务从本地事务变成分布式事务。2PC / TCC / 最终一致性没有一个方案是万能的。多数业务场景尤其是电商下单、支付这种链路最终一致性加上消息对账才是主流。如果每个业务都强一致性能和可靠性反而都会出问题。架构演进这条主线其实很像盖房子扩建一开始一间小房间后来住的人多了开始多隔几间读写分离再后来房间也不够用了只能盖新楼分库分表最后干脆设计成一个园区微服务 分布式数据库 缓存层。每一步都有成本每一步都要在你当前的瓶颈真正到来时再动工。5. 监控体系与常见问题速查把优化成果固化下来5.1 监控指标与告警配置优化完一轮如果监控没有跟上相当于做完手术没缝针早晚还得崩。监控数据库性能我用的是三层指标体系资源层是最基本的CPU、内存、磁盘 IO、网络带宽这些指标直接反映硬件的健康状况。MySQL 的 CPU 和 IO 占比高不一定是坏事但如果所有磁盘都在读说明有大面积的全表扫描正在发生需要立刻定位 SQL。服务层是数据库自身的状态。连接数、线程运行数、慢查询数量、锁等待次数、临时表数量、缓冲池命中率这些都是核心指标。缓冲池命中率如果低于 95%说明内存可能不够或者热点数据没有被有效缓存。业务层是 SQL 的延迟和错误率。通过调用链监控能看到每次数据库访问耗时如果 P99 延迟持续变高通常意味着某些查询在扫描大数据量或者锁竞争加剧。建议搭建一套覆盖这三层的监控体系。Prometheus Grafana 加 mysqld_exporter 是开源方案里比较成熟的组合配置简单社区文档也全。告警阈值方面我习惯在慢查询数量持续 5 分钟超过阈值时告警锁等待超过 3 秒告警连接数超过 maxActive 的 80% 告警。不要一个指标高了就全量告警守住少量高价值指标即可。5.2 高频问题速查表现象可能原因排查方向解决建议一条 SQL 突然变慢执行计划改变、统计信息过期EXPLAIN 对比前后计划ANALYZE TABLE 更新统计信息必要时 use index 强制索引CPU 持续 100%大量慢查询、无索引扫描、排序归并慢查询日志 processlist 看 Time补索引、改 SQL、限制并发连接数耗尽连接池配置过高、SQL 慢导致持连接久看 processlist 的 Command 和 Time调连接池参数、优化慢 SQL数据库死锁事务顺序不一致、间隙锁冲突SHOW ENGINE INNODB STATUS统一事务内语句的访问顺序适度缩小事务范围主从延迟高大事务、DDL、binlog 量过大SHOW SLAVE STATUS拆分大事务控制单次 DML 行数低峰期执行 DDL查询结果集大但字段很少SELECT * 全字段返回检查应用 SQL 习惯改写为只查所需字段批量更新导致锁等待超时大批量 update 持锁时间长会话列表看 Lock time分批执行每批 500~1000 行这个表是我在多次故障复盘后整理的基本覆盖了中小团队最常见的八类问题。遇到类似现象照着排查方向走大部分情况都能在短时间内定位。5.3 优化效果的验证与回归优化完不能拍拍屁股就完事一定要做前后对比同时防止按下葫芦浮起瓢。量化验证的方式很直接记录优化前后的 SQL 平均耗时、扫描行数、CPU 使用率、QPS、锁等待次数等指标。建议先在测试环境或压测环境做全量回归重点是确认新增索引没有让写入变慢。回归测试要覆盖至少三类场景该查询的正常参数、临界参数比如分页到极限深度、全表扫描场景。如果原来 3 秒的查询优化到 50 毫秒目标达成但同时要观察同表的 INSERT、UPDATE 耗时有没有显著上升。我习惯每个季度或者半年做一次慢查询全量 review。把过去半年的慢查询日志拉出来按执行次数和累计耗时排序重复出现的 SQL 优先治理。有些 SQL 其实执行一次很快但每秒调用上百次累计成本反而最高。这个 review 机制比临时救火有效得多因为它是持续地压低全链路的性能债务。绝大多数系统只要养成了季度 review 的习惯性能问题基本不会发展到需要分库分表那一步。做了这么多年数据库性能优化我个人最大的体会是不要总指望某一个技巧拯救全场性能优化是一个系统工程。索引设计是地基中的地基SQL 写法是日常习惯监控体系是安全网架构演进是最后的底牌。这四个层面的优先级顺序也是我要反复强调的——先修索引和 SQL 的问题再谈架构。很多人拿到慢 SQL 的第一反应是上缓存、上分库分表结果发现数据都同步不过来问题根本没解决。反过来先把索引设计到位SQL 写得清爽连接池配置合理很多系统根本走不到架构那一步。最后再分享一个小技巧每次上线数据库变更顺手把变更前后的慢查询数量、平均延迟截图保存。半年后再看你就能清晰感知到自己的每一次改动给系统带来了什么这种感觉比任何监控大盘都真实。
