MySQL 学习路径这个话题我在不同场合被问过太多次了。问的人里有刚转行的后端新人有写了两三年业务代码但一遇到慢查询就发怵的开发者也有需要自己维护一套数据服务的小团队负责人。大家的起点不一样但困惑高度相似网上资料一大堆官方文档厚得像砖头视频教程看完了还是不知道怎么下手。我自己的判断是问题不在于资料少而在于绝大多数人把 MySQL 当成一门“要背的知识”在学而不是当成一套“要动手调的系统”在练。这套路径我想讲的是后者从写出一条正确的 SQL 开始到能读懂执行计划、能定位锁等待、能判断一张表该不该拆一步一步把能力垒起来。它不需要你事先懂多少底层原理但要求你肯在本地把库建起来、把数据灌进去、把每条语句跑一遍。适合谁适合已经会写基本增删改查、想在半年内把 MySQL 从“会用”推到“能扛事”的开发者也适合需要独立负责数据层设计的技术负责人。1. 先把学习路径的骨架立起来1.1 三个阶段划分与时间投入的现实估算我不太喜欢那种把学习路径切成七八个阶段的方案切得太细人容易在阶段之间反复横跳最后每个阶段都只摸了个皮毛。我的划分是三大块会用、会调、会管。会用指的是能把业务需求翻译成表结构和 SQL并且知道自己的语句大概会走什么路径会调指的是遇到性能问题能定位、能改、能验证会管指的是备份、恢复、复制、容量规划这些运维侧的事。时间上给个参考值按每天能投入一到两小时算会用这一块大概四到六周会调八到十周会管四到六周。这个节奏不是拍脑袋来的是我观察过若干次带人的过程后总结的。会用阶段短是因为它的反馈最快写完一条语句马上能看到结果会调阶段长是因为它依赖真实的数据量和真实的慢查询你得先攒够“案件”才有得练会管阶段反而能压缩因为它的知识点相对固定操作流程一旦跑通重复几次就熟了。有一个容易被忽略的点这三个阶段不是严格串行的。我的建议是在会用阶段进行到一半的时候就把慢查询日志打开让它在后台默默记录。等你进入会调阶段手里已经攒了一批真实的慢语句比任何教程里的案例都贴近你自己的操作习惯。这个小小的重叠能让第二阶段的效率提升一大截因为你不是在学“别人遇到的问题”而是在解决“自己制造的问题”。1.2 每个阶段必须交付的“作品”光有计划没有产出学完就忘。我给每个阶段都设了一个必须交付的东西可以理解成通关凭证。会用阶段的交付物是一份完整的小型业务库设计包含不少于八张表要有主外键关系、要有枚举型的字典表、要有带时间范围的流水表还要写至少三十条覆盖多表关联、分组聚合、子查询的查询语句。这份东西的价值在于它会逼你面对真实设计里的取舍这个字段该不该冗余、这个关联该不该拆成两次查询、这个状态字段用整数还是用字符串。会调阶段的交付物是一份慢查询治理报告。做法是先往库里灌入百万级以上的模拟数据然后人为构造十条以上的慢语句逐条用执行计划分析给出改写方案并记录改写前后的耗时对比。这份报告不需要给别人看但它会强迫你把“感觉慢”变成“有数据支撑的慢”。会管阶段的交付物是一套可复现的备份恢复流程从全量备份、增量恢复到误删数据后的定点恢复全部写成脚本并且真的演练过至少一次完整的灾难恢复。我见过太多人学了备份命令但从来没实际恢复过真出事的时候手忙脚乱。提示这三个交付物都不要追求完美第一版能跑通就行。真正产生价值的是第二遍、第三遍的修改过程而不是那个初始版本。1.3 几种常见的路径偏差第一种偏差是只学不练。看视频的时候觉得每个知识点都懂了合上电脑什么都写不出来。MySQL 这门东西的抽象程度不高几乎所有概念都能用一句 SQL 或者一个执行计划验证不练等于没学。第二种偏差是过早钻底层。刚学会写 JOIN 就去啃 InnoDB 的页结构、行格式、undo 日志组织方式。这些知识当然有价值但它解决的是“为什么这样设计”的问题而你当下要解决的是“我的语句为什么慢”。顺序反了学习曲线会陡到让人放弃。我的建议是把底层原理放到会调阶段的中后期那时候你已经有具体的性能现象作为锚点读原理才有代入感。第三种偏差是迷信参数调优。很多人的第一反应是去改配置文件把缓冲池调大、把刷盘策略调激进。实测下来八成的性能问题出在语句和索引上参数能带来的提升通常是个位数百分比而且改错了还有风险。参数该懂但它不该是你的第一把刀。2. SQL 基本功从会写到写对2.1 建表这件事决定了后面八成的麻烦我个人的经验是一个项目的数据库设计质量在建表那一刻就基本定型了。后面无论怎么优化索引、怎么改写语句都很难弥补表结构本身的缺陷。所以路径的第一站必须是建表而且要认真对待每一个字段。先说几个几乎不会错的默认约定。字符集统一用utf8mb4排序规则用对应的通用规则不要在一个库里混用多种字符集否则跨表关联时会发生隐式转换索引直接失效而且这种失效在执行计划里不一定显眼。主键统一用自增的BIGINT UNSIGNED不要用业务字段做主键也不要用随机字符串做主键。原因很直接InnoDB 的表是按主键顺序组织数据的自增主键意味着新数据总是追加在末尾写入是顺序的随机主键会让插入位置在整棵树里跳来跳去页分裂频繁写入性能下降得很明显。时间字段的选择有个小坑。TIMESTAMP占四个字节范围只到 2038 年而且它的值会随时区设置变化DATETIME占八个字节范围大存什么就是什么。业务里的创建时间、更新时间我一般用DATETIME避免时区带来的困惑但如果表非常大、时间字段又多用TIMESTAMP省下来的空间也值得考虑。这里没有标准答案关键是你得知道自己在选什么。金额字段一律用DECIMAL绝对不要用FLOAT或DOUBLE。浮点数存不了精确的十进制小数做累加的时候会出现尾差财务对账时这种误差是致命的。长度按业务上限给比如金额用DECIMAL(15,2)能覆盖到万亿级。状态字段用TINYINT加注释不要用字符串一是省空间二是比较效率高。CREATE TABLE order_flow ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL COMMENT 业务单号, user_id BIGINT UNSIGNED NOT NULL COMMENT 用户标识, amount DECIMAL(15,2) NOT NULL DEFAULT 0.00 COMMENT 金额, status TINYINT NOT NULL DEFAULT 0 COMMENT 0待支付 1已支付 2已取消, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_user_created (user_id, created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单流水;这段建表语句里有几个刻意的设计。order_no建了唯一索引因为业务上它必须唯一靠唯一索引来保证比在代码里查一遍再插入可靠得多后者在并发下必然出问题。idx_user_created是一个联合索引顺序是用户在前时间在后这是为了支撑“查某个用户最近 N 条订单”这个最常见的查询具体原因在第三章会展开讲。2.2 查询语句的渐进式训练法我把查询能力分成四层每层都有明确的训练方法。第一层是单表条件筛选与排序。这一层要练的是条件顺序和索引的配合。训练方法很简单建一张十万行以上的表写十条不同的 WHERE 组合每条都用执行计划看一遍走的是哪个索引、扫了多少行。这一层练熟了你会对“什么样的条件能用上索引”形成肌肉记忆。第二层是多表关联。这一层的难点不在语法在于关联顺序和驱动表的选择。我建议的练法是同一份需求分别用 JOIN 写一遍、用子查询写一遍、拆成两次查询在应用层拼装一遍然后比较三者的执行计划和耗时。你会发现有些场景下 JOIN 明显更优有些场景下拆开反而更快这中间的判断依据是驱动表的行数和关联字段的索引情况不是某种固定的写法。第三层是分组聚合与窗口函数。分组聚合要注意的是分组字段和聚合字段的索引配合以及 HAVING 和 WHERE 的区别——前者在聚合后过滤后者在聚合前过滤能用 WHERE 就别用 HAVING因为 WHERE 阶段就能用索引减少扫描量。窗口函数在较新的版本里已经支持得很好处理“取每组前 N 条”这类需求时比传统的自关联写法清晰太多性能也往往更好。第四层是递归与复杂分析。递归查询适合处理树形结构比如组织架构、类目层级。这一层的使用频率不高但遇到的时候如果没有概念会很痛苦。练法就是找一张带父子关系的表写一条递归查询把整棵树展开再写一条查询某个节点的所有祖先。2.3 数据类型选择的成本账很多人觉得数据类型是小事随便给个VARCHAR(255)就行。我把这笔账算给你看你就知道为什么值得认真对待。假设一张表有一千万行某个字段从VARCHAR(255)改成VARCHAR(32)。字符集是utf8mb4变长字段在行内只存实际长度加长度前缀看起来改动不大。但问题在于InnoDB 的索引页大小是固定的 16KB字段的最大长度会影响索引能存多少条记录。如果一个联合索引里包含这个字段字段的最大长度从 255 降到 32单个索引页能容纳的记录数可能翻几倍索引树的高度可能从四层降到三层。三层到四层听起来只差一层但每次查询的磁盘读取次数可能从三次变成四次在缓存命中率不高的情况下这个差别相当可观。再看一个更常见的例子用INT还是BIGINT做外键。INT UNSIGNED的上限是四十多亿绝大多数业务表永远到不了BIGINT占八个字节是前者的两倍。如果一张表有五个外键字段一千万行的话光这几个字段就相差 200MB 的存储索引体积同样翻倍。当然主键用BIGINT是对的因为主键的值一旦用尽再改是灾难性的但普通外键字段如果你能确认业务量级用INT UNSIGNED完全够。场景常见误选推荐选择差异说明主键INTBIGINT UNSIGNED预留增长空间避免后期改主键金额FLOAT/DOUBLEDECIMAL(15,2)精确小数避免累加误差状态VARCHAR(20)TINYINT省空间比较快配合注释即可读短文本VARCHAR(1000)VARCHAR(实际上限)影响索引页容量与内存占用布尔标记VARCHAR(1)TINYINT(1) 或 BIT语义清晰存储紧凑长文本VARCHAR(5000)TEXT 单独存表避免大字段拖累主表扫描还有一个容易被忽略的点允许 NULL 的字段在索引和比较中有额外开销。InnoDB 对 NULL 值有专门的处理索引里会额外存储标记信息统计信息里也要单独考虑。业务上如果某个字段确实必须有值就加上NOT NULL DEFAULT不要图省事全部允许 NULL。这个习惯在表小的时候看不出差别表大了之后统计信息的准确性会受影响优化器选错执行计划的概率会上升。3. 索引与执行计划性能优化的主战场3.1 从数据结构到索引设计的映射索引的本质是用额外的存储和写入开销换取查询时的定位效率。InnoDB 用的是 B 树这个结构有两个特点值得记住一是所有数据都存在叶子节点非叶子节点只存键值和指针二是叶子节点之间用链表相连所以范围查询很高效。这两个特点直接推导出两条设计原则。第一索引列越短越好因为非叶子节点要存键值键值短了一个页能放下更多的键树就更矮从根到叶的路径就更短。第二范围查询适合放在联合索引的最后一列因为一旦用了范围条件后面的列在索引里就无法继续用于精确定位了。我常用来解释联合索引的类比是电话簿。电话簿按“姓、名”排序你要找“张”姓的所有人能快速定位你要找“张”姓且名字是“三”的人也能快速定位但你要找名字是“三”而不管姓的人就没法用这个排序了。这就是最左前缀原则的直观含义。联合索引的列顺序怎么定我的判断顺序是等值条件优先区分度高的优先范围条件放最后排序字段尽量跟上。举例来说查询是“查某用户在某时间段内的订单按时间倒序”那么(user_id, created_at)就是很自然的顺序user_id等值匹配created_at既是范围条件又能顺带满足排序一条索引解决三个需求还避免了额外排序。3.2 执行计划的正确读法执行计划是排查性能问题的第一手材料但很多人只盯着type那一列看这是不够的。我按重要性给几个关键列排个序。type列反映访问类型从好到差大致是system、const、eq_ref、ref、range、index、ALL。看到ALL就是全表扫描看到index是全索引扫描这两个都要警惕。但要注意ALL不一定是坏事——如果表只有几百行或者查询要返回大部分数据全表扫描反而比走索引更划算因为走索引还要回表回表的随机读可能比顺序全扫更慢。rows列是优化器估算的扫描行数。这个数字是估算值基于统计信息不一定准但它的量级很有参考价值。如果rows是几十万而你预期只返回几十行说明索引没用上或者用错了。filtered列表示扫描到的行中有多少比例能通过后续条件过滤。这个列常被忽略但它能解释“为什么扫了十万行只返回一百行还这么慢”——因为过滤是在扫描之后做的扫描量本身就构成了成本。Extra列信息量最大。看到Using filesort说明有额外排序看到Using temporary说明用了临时表这两个都是性能信号。看到Using index是好消息说明覆盖索引生效了查询只读索引不回表。看到Using index condition说明用上了索引条件下推也是好事。EXPLAIN SELECT order_no, amount FROM order_flow WHERE user_id 10086 AND created_at 2024-01-01 ORDER BY created_at DESC LIMIT 20;对这条语句理想情况是typerefkeyidx_user_createdrows在几十到几百之间Extra里出现Using index condition而没有Using filesort。如果Extra里出现了Using filesort说明排序没被索引覆盖需要检查索引顺序是否和排序方向一致。注意EXPLAIN给出的是估算不是实测。真正判断优化效果要用实际执行并记录耗时或者看EXPLAIN ANALYZE较新版本支持给出的真实执行数据。我踩过的坑就是照着EXPLAIN的rows优化了半天结果实际耗时没变因为瓶颈根本不在扫描行数上。3.3 索引失效的典型场景清单这一节我列的是实战中反复遇到的失效场景几乎每条我都实际遇到过。第一种是在索引列上做函数运算或类型转换。比如WHERE DATE(created_at) 2024-01-01对列用了函数索引直接失效。正确写法是范围查询WHERE created_at 2024-01-01 AND created_at 2024-01-02。类型转换的坑更隐蔽如果字段是字符串类型而传入的是数字或者反过来会发生隐式转换导致索引失效。这种情况在执行计划里未必显眼需要你对自己的表结构足够熟悉。第二种是联合索引跳过最左列。索引是(a, b, c)查询条件只有b和c用不上。这个大家都知道但变体很多WHERE a 1 AND c 3能用上a这一列c用不上WHERE a 1 AND b 2只能用上a因为a是范围条件后面的列无法精确定位。第三种是以通配符开头的模糊匹配。LIKE %关键字用不上索引LIKE 关键字%可以。如果业务上确实需要前后模糊考虑全文索引或者外部的检索方案不要指望普通索引。第四种是否定条件。!、NOT IN、NOT LIKE这类条件优化器往往判断走索引的收益不如全扫尤其是在数据分布均匀的情况下。第五种是OR 连接的条件中有一侧没有索引。只要 OR 的某一侧无法使用索引整个条件可能退化为全表扫描。改写方式是用UNION ALL把两部分拆开。第六种是排序方向与索引不一致。联合索引按升序存储如果查询要求一列升序一列降序索引无法直接满足排序会触发文件排序。较新的版本支持降序索引可以在建索引时显式指定方向但要注意版本兼容性。失效场景典型写法改写方案列上做函数WHERE DATE(col) 2024-01-01改为范围条件隐式类型转换字符串列传数字保证类型一致跳过最左列索引(a,b)只查b调整索引顺序或补索引前置通配符LIKE %kw改后缀匹配或换检索方案否定条件!、NOT IN改为正向条件或拆分查询OR 混合一侧无索引拆成UNION ALL排序方向冲突一升一降建降序索引或调整排序3.4 索引不是越多越好写入侧的代价新手容易走另一个极端给每个查询字段都建一个索引。这在读多写少的场景里问题不大但在写入频繁的表上会带来明显负担。每多一个索引每次插入就要多维护一棵 B 树更新索引列也要同步修改。一张表如果有八个二级索引一次插入实际上要写九次聚簇索引加八个二级索引磁盘写入量和日志量都成倍增加。更麻烦的是索引多了之后优化器选择执行计划的搜索空间变大选错计划的概率也会上升。我的经验是一张表的索引数量控制在五个以内比较舒服超过这个数就要审视一下是不是查询模式太发散。审视的方法是把这张表上所有高频查询列出来看每个索引被哪些查询用到有没有可以合并的、有没有从来没被用到的。未被使用的索引是纯粹的负担该删就删。较新的版本提供了索引使用情况的统计视图可以定期查一下把使用次数为零的索引清理掉。4. 事务、锁与并发控制4.1 隔离级别与多版本并发的配合关系事务这块很多人背得出四个隔离级别但说不清每个级别到底解决了什么问题、代价是什么。我用一个具体场景把这条链路串起来。假设两个事务同时对同一行做操作。读未提交级别下一个事务能读到另一个事务还没提交的修改这叫脏读几乎所有业务都不能接受。读已提交解决了脏读但同一个事务里两次读同一行可能得到不同结果这叫不可重复读。可重复读保证同一事务内多次读结果一致但可能出现幻读——第二次读的时候多出了几行。串行化则通过加锁让事务排队代价是并发度大幅下降。重点在于MySQL 的默认可重复读级别实现方式不是靠给每一行加锁而是靠多版本并发控制。每次修改都会生成一个旧版本读操作根据事务开始时的快照去读对应的版本读写互不阻塞。这就是为什么在高并发下读多写少的场景性能依然很好——读根本不需要等写。但这个机制有个容易误解的地方快照读和当前读是两回事。普通的SELECT是快照读读的是历史版本而UPDATE、DELETE、SELECT ... FOR UPDATE是当前读读的是最新版本并且要加锁。很多“为什么我读到了旧数据”的困惑根源就在于没分清这两种读。4.2 锁等待与死锁的排查实录锁相关的问题我遇到最多的两类是锁等待超时和死锁。锁等待超时的典型现象是某个操作卡住不动等了一段时间报超时错误。默认的等待阈值是五十秒很多业务等不了这么久会把这个值调小。排查顺序是这样先看当前有哪些事务在跑、各自持有什么锁、在等什么锁。较新版本里查询事务和锁等待的视图已经很完善一条查询就能看到阻塞关系。我印象比较深的一次是一个批量更新任务把一批行锁住了同时另一个单行更新在等同一批里的某一行形成了长链阻塞。表面上看是批量任务太慢根本原因是没有控制批量大小一次更新了太多行持锁时间太长。改成按主键分批、每批几百行、批间留一点间隔问题就消失了。这个经验我后来一直沿用任何批量写操作都必须分批且每批的行数要受控。死锁的现象是两个事务互相等对方持有的锁谁也走不下去。数据库会主动检测并回滚其中一个所以死锁本身不是致命问题但频繁发生说明业务逻辑有问题。获取死锁信息的直接方式是查看最近一次死锁的详细记录里面会写明两个事务各自执行了什么语句、持有什么锁、等什么锁。最常见的死锁成因是加锁顺序不一致。两个事务分别先锁 A 再锁 B、先锁 B 再锁 A必然撞车。解决办法是统一加锁顺序比如永远按主键升序处理。另一个常见成因是间隙锁可重复读级别下范围条件不仅锁住存在的行还会锁住区间间隙防止其他事务插入。两个事务各自持有相邻区间的间隙锁再互相申请对方的区间就会死锁。这类问题可以通过缩小范围条件的区间、或者调整索引让范围更精确来缓解。提示死锁被回滚的那个事务业务代码里必须能正确处理重试。我见过不少实现只管正常路径一遇到死锁就抛异常给用户体验很差。重试逻辑要注意加次数上限和退避不要无脑循环。4.3 长事务的治理思路长事务是所有并发问题的放大器。事务开得越久持有的锁越久生成的旧版本数据越多清理压力越大回滚段膨胀整体性能都会受影响。长事务的来源通常有三类。第一类是应用层开启事务后做了耗时的非数据库操作比如调用外部接口、处理文件。这类问题的修法是把非数据库操作挪到事务外面事务里只留必要的读写。第二类是批量任务一次处理太多数据。前面说的分批策略同样适用。第三类是交互式操作比如在客户端里手动执行了BEGIN之后忘了提交。这个只能靠规范约束加上定期巡检发现。巡检的方法很直接定期查询当前运行时长超过阈值的事务把它们的开始时间、执行的语句、所属连接都列出来。我一般把阈值设在三十秒超过就告警。这个习惯帮我提前发现过好几次隐藏的逻辑问题。5. 性能优化与容量规划5.1 用慢查询日志构建优化闭环性能优化最怕的是凭感觉。慢查询日志的价值在于把“感觉慢”变成“可度量、可排序、可验证”。开启方式很简单把慢查询阈值设成一个较小的值比如一秒打开日志开关。这里有个细节值得说日志记录的是执行时间超过阈值的语句但阈值设得太低会产生大量日志影响性能设得太高又会漏掉问题语句。我的做法是初期设一秒跑一段时间后看日志分布如果日志量很大说明系统里有大量亚秒级但高频的语句这时候要结合聚合统计来看而不是逐条看原始日志。日志里最有价值的信息是执行次数和总耗时。一条语句单次只花零点一秒但一天执行一百万次总耗时就是二十多个小时这种语句的优化收益远大于一条偶尔跑十秒的报表查询。所以我的排序原则是按总耗时排序而不是按单次耗时排序。优化的闭环是这样从日志里挑出总耗时最高的几条用执行计划分析提出改写或加索引的方案改完后用同样的数据量和同样的语句做对比测试记录前后耗时。这个过程至少走三轮因为很多优化是连锁的改了第一条可能暴露出第二条的问题。5.2 几个真正值得调的参数虽然我说过参数不是第一把刀但有那么几个参数确实值得了解因为它们影响的是全局行为。缓冲池大小是最重要的一个。它的作用是缓存数据和索引页减少磁盘读取。经验值是设成物理内存的百分之五十到七十具体要看这台机器上还跑不跑别的服务。设得太小缓存命中率低读操作频繁落盘设得太大可能触发系统层面的内存交换性能反而断崖式下跌。连接数上限需要结合应用的连接池配置来定。常见的问题是应用侧连接池开得很大数据库侧上限却很小导致连接被拒。反过来上限设得过大也有风险每个连接都要占内存连接数暴涨时可能把内存吃光。我的做法是让上限略高于所有应用连接池之和留一点余量给运维排查用。还有一个容易被忽略的是空闲连接的回收时间。连接建立后长期空闲占着资源不干活。设置一个合理的空闲超时让闲置连接自动断开能缓解连接数压力。但要注意这个值必须大于应用侧连接池的最小空闲检测周期否则会出现应用以为连接还在、实际已被服务端断开的情况表现出来就是间歇性的连接错误。参数方向调大倾向调小倾向判断依据缓冲池读多写少、内存充足机器共用、内存紧张缓存命中率与系统内存水位连接数上限应用连接池较大内存有限、连接暴涨应用侧池配置之和加余量空闲超时连接复用频繁连接数紧张与连接池检测周期配合慢查询阈值日志量过大想抓更多问题语句先宽后严结合聚合统计5.3 什么时候该考虑分库分表这个话题我被问得很多答案可能和很多人的预期不一样大多数情况下你不该分。先看判断标准。单表数据量的经典参考值是两千万行但这不是硬线实际要看行宽和访问模式。一张只有五六个窄字段的表几千万行也未必有问题一张有十几个宽字段、还带大文本的表几百万行就可能很吃力。更靠谱的判断方式是看单表的数据文件大小和索引大小如果索引已经超过缓冲池能缓存的范围缓存命中率会持续走低那才是真正的瓶颈信号。在考虑拆分之前有一系列成本低得多的手段值得先试优化索引、改写语句、归档历史数据、把大字段拆到独立表、加读写分离分担读压力。这些手段能把系统的承载能力往上推一大截而且改动风险小。如果确实到了必须拆的地步先做垂直拆分——按业务把不同的表分到不同的库这个改动相对可控。水平拆分把一张表按某个维度切到多个库是最后手段因为它会引入跨分片查询、分布式事务、全局唯一标识、数据迁移这一系列复杂问题运维成本陡增。我做过的水平拆分里踩得最深的坑是分片键的选择。选得不好会出现数据倾斜——某个分片的数据量是其他分片的几倍等于没拆。分片键要满足两个条件分布均匀且大多数查询都带这个条件。如果业务查询模式很发散没有哪个字段能同时满足那就说明还不具备拆分条件应该先改查询模式。6. 备份恢复与复制容易被跳过的必修课6.1 备份策略的层次设计备份这件事只有真正丢过数据的人才会重视。我的建议是不要等到出事才学把它放进学习路径的固定环节。备份分两个层次。逻辑备份导出的是 SQL 语句好处是可读、可跨版本、可 selective 恢复单表缺点是慢数据量大时导出和导入都耗时而且对业务有压力。物理备份直接复制数据文件快得多适合大数据量缺点是版本和平台绑定较紧不能随便跨版本用。我的常规策略是两者结合每周做一次全量物理备份每天做一次增量逻辑备份按需用于单表恢复和小范围数据订正。保留周期按业务合规要求定至少要能覆盖“发现问题的时间差”——很多数据问题是几天后才被发现的如果备份只留一天等于没有。备份做完了必须验证。验证方式不是看文件大小对不对而是真的在另一套环境里恢复一遍然后比对关键表的行数和校验值。这个演练我建议每季度做一次因为恢复流程里涉及的命令、路径、权限隔几个月就可能忘光。6.2 复制的基本原理与常见延迟复制是读写分离和数据冗余的基础。基本原理是主库把变更写进二进制日志从库拉取日志并在本地重放。这里的关键概念是日志格式基于语句的格式记录原始 SQL日志小但某些函数会导致主从数据不一致基于行的格式记录每一行的变更日志大但结果确定。现在的默认选择一般是基于行的格式我建议保持这个选择一致性比日志体积重要得多。复制延迟是最常见的问题。现象是从库的数据比主库慢读从库可能读到旧数据。延迟的原因通常是主库写入量大而从库重放能力跟不上或者从库上跑了重的查询抢占了资源。应对方式有几层从库上禁止跑大报表查询把分析类负载隔离到专门的实例控制大事务一次改太多行的操作会形成一个巨大的日志事件从库重放时是原子的会卡很久监控延迟指标并设置告警延迟超过阈值就把读流量切回主库。还有一个细节从库重放是单线程还是多线程直接决定了追赶能力。较新的版本支持并行重放配置得当的话延迟能显著降低。这块的配置需要根据实际负载调没有放之四海皆准的值。7. 练习环境与实操数据准备7.1 本地环境的搭建要点学 MySQL 一定要有能随便折腾的本地环境。最省事的方式是用容器跑一个实例一条命令起来端口映射到本机数据目录挂载到本地卷上这样容器删了数据还在。配置文件可以通过挂载的方式覆盖改参数不用进容器。资源分配上给个参考练习用的实例内存给两到四G其中缓冲池设一到两G足够支撑百万级数据的各种实验。磁盘留二十G以上因为你要灌数据、要做备份、要留恢复演练的空间。版本选择上建议用当前主流的稳定大版本不要用太老的版本因为新版本在索引、执行计划、窗口函数、降序索引这些方面都有改进学老版本的很多“经验”在新版本里已经不适用了。同时也不要追最新的尝鲜版本练习环境求的是行为可预期。7.2 练手数据怎么造没有数据练不了手但造数据也有讲究。太小的数据集看不出性能差异太大的数据集跑一次查询要等很久影响练习节奏。我的建议是分三档准备。第一档是千行级用于验证语句的正确性。这个量级下任何语句都是瞬间返回可以快速试错。第二档是百万行级用于观察索引效果和执行计划差异。这个量级足以让全表扫描和索引查找产生肉眼可见的耗时差又不至于慢到无法忍受。第三档是千万行级只在做特定实验时用比如验证分页深翻页的优化、验证大表加索引的耗时、验证分片前后的对比。这个量级的数据准备需要点技巧用批量插入比逐条插入快几个数量级一次插入几千行、循环若干次比写一个循环插一千万次快上百倍。造数据时要注意数据分布的仿真。如果所有行的某个字段值都是均匀分布优化器的估算会非常准你就看不到“统计信息不准导致选错计划”这类真实问题。刻意造一些倾斜数据——比如某个用户有一百万条记录其他用户各有一百条——才能复现真实场景里的各种怪现象。这一点我觉得是练习数据准备里最容易被忽略、但对能力提升帮助最大的部分。8. 常见问题速查与避坑经验8.1 高频问题速查表下面这张表是我这些年被问得最多的问题按现象、原因、处理三列整理可以直接拿去对照。现象常见原因处理方向查询突然变慢统计信息过期导致计划变化更新统计信息复核执行计划写入越来越慢索引过多或页分裂清理无用索引检查主键是否自增连接数打满连接泄漏或池配置不当查活跃连接与空闲连接分布主从数据不一致日志格式或非确定函数改行格式避免非确定写法磁盘占用暴涨旧版本数据未清理、日志未归档检查回滚段与日志保留策略分页越翻越慢大偏移量的深度分页改用游标式分页批量更新卡住一次锁太多行分批处理控制批大小死锁频繁加锁顺序不一致统一按主键升序处理深度分页这一条值得单独说两句。LIMIT 1000000, 20这种写法数据库要先扫描并丢弃前一百万行再取二十行越往后越慢。改成游标式分页用上一页最后一条记录的排序键作为下一页的起点条件扫描量就恒定在每页大小跟页码无关。这个改动的收益在数据量大时非常明显。8.2 几条踩过才知道的经验第一条不要在生产环境直接跑EXPLAIN之后就改索引。加索引本身是有成本的大表加索引可能锁表或者消耗大量IO影响在线业务。稳妥的做法是先在有同等数据量的影子环境验证确认收益后再排期在低峰期操作。第二条改语句之前先留证据。我见过有人优化完发现更慢了想回滚却记不清原来的写法。养成习惯把原始语句、执行计划、耗时都记下来改完再对比。这个习惯看起来笨但它是唯一能让你确认“优化真的有效”的方法。第三条不要迷信网上抄来的参数模板。每台机器的内存、磁盘、负载特征都不一样别人机器上的最优值到你这里可能是灾难。参数要基于自己的监控数据来调调完要有对比观察期。第四条边界数据要比正常数据多测。空值、超长字符串、零值、负数、跨年跨月的时间这些边界在测试环境里经常被忽略上线后集中爆发。我在练习阶段就养成了专门造边界数据的习惯这个习惯后来帮我挡掉了不少线上问题。第五条版本升级前先读变更说明。优化器行为、默认参数、弃用特性每个大版本都可能有变化。有些在老版本上跑得好好的语句升级后执行计划变了性能可能下降。升级前在影子环境跑一遍核心查询做对比能提前发现问题。8.3 关于学习节奏的一点个人体会最后说说节奏。我个人的体会是MySQL 这门东西不适合集中突击更适合小步高频。每天花一小时其中四十分钟动手、二十分钟看原理持续三个月效果远好于连续两周每天八小时然后放下。原因是它的知识点之间有大量隐含关联比如索引和锁的关系、事务和日志的关系、复制和日志格式的关系这些关联需要时间在脑子里慢慢连起来突击只能记住孤立的知识点。另外一个体会是找一个真实的小项目来承载学习。哪怕只是给自己做一个小工具只要有真实的表结构、真实的查询需求、真实的数据增长学习就有了抓手。纯看教程最大的问题是所有问题都是别人替你定义好的而真实项目里你会自己遇到“这个字段到底该建索引吗”“这个查询为什么慢”这类问题解决问题的过程才是能力增长的来源。我在带人的时候第一件事总是让对方先把自己的小项目库建起来后面的所有知识点都往这个库上套效果比按章节学要好得多。