前言给新手和正在进阶的开发者看。学MySQL这些年我见过太多人把时间浪费在重复的增删改查上结果一到面试、一到线上问题就抓瞎。原因很简单——只记住了语法没理解数据库是怎么运转的。这篇东西我不打算跟你念手册而是按我自己的实战路径来拆从你怎么写出一条正确的SQL到你怎么设计一张撑得住的表再到你遇到慢查询、锁等待、主从延迟时脑子里该有的排查顺序。这条路走下来你对MySQL的理解会上一个台阶应付日常开发和面试基本够用。1. 先别急着写代码把数据模型想清楚1.1 为什么说大部分SQL问题都是设计问题我工作前两年写SQL基本靠猜查得慢就加索引加了没用就改写改完还是慢就骂服务器。直到有一次负责重构一张核心业务表才真正明白什么叫设计决定上限。举个很常见的例子。很多团队做订单表喜欢把所有状态都塞进一个status字段然后查询的时候写一大串WHERE status IN (...) AND type ...。表面看没什么问题但数据量到千万级之后这个写法会让你索引设计特别别扭——你想建复合索引但状态字段的区分度极低而且业务的查询条件组合很不固定索引根本覆盖不过来。真正合理的做法是先做字段拆分和语义梳理。订单状态是强状态机模型我们完全可以用主状态加子状态的方式拆开比如order_status主流程状态取值少但查询稳定pay_status支付维度状态单独走一套流转refund_status售后维度状态低频但重要这样做的好处是每个维度的状态独立变化、独立查询索引设计就变得干净了——(user_id, order_status)这种组合索引可以稳定命中而且不会因为一个维度状态变多导致索引区分度下降。这就是典型的按业务实体的生命周期拆维度而不是把所有状态混在一个字段里。另外一个我常看到的问题就是滥用JSON字段。MySQL的JSON类型的确方便灵活得像个文档数据库。但有团队把订单的商品明细整个塞进JSON然后查询的时候用JSON_CONTAINS、JSON_EXTRACT去捞数据。短时间没问题等数据量一大你会发现这种查询完全用不上索引即使你用虚拟列生成索引维护成本也远比想象高。原则很简单需要单独查询、统计、关联的字段永远不要塞进JSON。JSON适合存只展示不查询的完整快照数据。1.2 一张业务表应该怎么设计字段这一节我直接给你一个我常用的设计模板不是标准答案但踩坑概率低先确定实体主键。很多人纠结用自增ID还是雪花ID我的建议简单粗暴单库单表用自增ID够用、性能最好分库分表或需要跨库合并的场景用雪花类全局ID。别在这个问题上过度设计。然后是字段三原则能用数值就不要用字符串比如状态、类型、渠道这些枚举值用TINYINT或SMALLINT映射查询快、存储小能用定长就不要用变长这个看场景短字符串用CHAR走常量存储在InnoDB下有性能优势但别极端该用VARCHAR还是用时间字段统一用DATETIME别混用时间戳DATETIME可读性好还能用索引业务代码里转换成本一次就够了还有两个看着不起眼但特别重要的字段create_time和update_time。几乎每个表都该有在MYSQL 5.6之后的版本这两个字段可以用默认值自动维护create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间这两个字段不光是规范问题排查数据问题的时候create_time是你定位数据写入时间线的救命稻草。关于逻辑删除有的团队用is_deleted字段有的直接物理删。我个人的实践是核心业务数据原则上逻辑删除但必须给is_deleted字段建索引而且所有查询条件里都要带上is_deleted 0否则数据会莫名其妙少一截。另外逻辑删除字段的默认值和取值一定要统一别一会儿0/1一会儿NULL/非NULL后面写SQL的人会疯掉。2. 增删改查把SQL写得像人话2.1 查询别用SELECT *更要命的是你以为自己在优化老生常谈的SELECT *问题我还是要再强调一次。SELECT *不是行数多慢的问题而是它破坏了覆盖索引的收益。举个例子。有一张用户表上面有个索引(status, create_time)如果你只需要查状态和创建时间那SELECT status, create_time FROM user WHERE status 1就可以走覆盖索引根本不用回表。但你写SELECT *优化器就得拿着主键回表把整行数据捞出来。数据量一大这个差距就是毫秒和秒级的区别。那有人会说我就是要查所有字段啊怎么办那你得换个思路——产品和接口层需要什么字段就查什么字段。在业务层定一个DTO只拉需要的列这样索引设计和查询计划都更可控。再说一个很多人忽略的点WHERE条件的顺序不会影响查询速度MySQL优化器会自己调整。真正影响速度的是你有没有把可用的索引送到优化器面前比如在索引列上做了函数运算-- 这样写会放弃索引 SELECT * FROM order WHERE DATE(create_time) 2024-01-01 -- 应该改写为范围查询 SELECT * FROM order WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00第二种写法不仅能用上索引而且语义上更精确因为它把某一天转成了一个时间区间范围扫描是索引最喜欢做的事情之一。2.2 INSERT、UPDATE、DELETE背后的锁与代价很多人觉得写操作简单反正语句写对了就行。但写操作的代价往往是读操作的数倍尤其是在并发场景。先说INSERT。批量插入的时候新手喜欢一条一条循环插入这会产生大量提交和日志IO非常慢。正确做法是用一条INSERT语句插入多行INSERT INTO user (name, age, create_time) VALUES (A, 20, NOW()), (B, 21, NOW()), (C, 22, NOW());单条多值INSERT的插入速度大概是一条条插的5到10倍。但要注意如果单条SQL数据量过大比如超过几千行可能拖垮主从同步和binlog传输所以一般控制在500到1000行一批比较稳妥。再说UPDATE。最容易被忽视的问题是加锁范围。MySQL的更新语句会根据WHERE条件锁定匹配的行如果WHERE条件不能用索引就可能锁全表——准确说是锁住所有扫描到的行。典型例子UPDATE order SET status 2 WHERE user_id 123;如果user_id没有索引MySQL只能全表扫一遍把每一条匹配的行都锁上期间其他事务对这些行的写操作全部阻塞线上就卡死了。所以每次写UPDATE和DELETE我都会条件反射地去看一眼WHERE条件有没有可用的索引。还有一个常被忽悠的问题UPDATE和SELECT的隔离级别。在默认的可重复读RR级别下UPDATE用的是当前读它会读到其他事务已经提交的最新数据这容易给人造成锁没用的错觉。建议你把隔离级别想明白业务允许的情况下把隔离级别设置成读已提交RC这样能减少很多间隙锁带来的死锁和阻塞问题这也是很多大厂线上实践的选择。DELETE的坑则是物理删除会带来性能黑洞。删数据不只是执行一条SQL它还要记录undo日志、维护二级索引海量删除会引发大量页分裂和碎片甚至拖垮性能。所以要批量删除大表数据的时候别一条SQL杀几百万行正确姿势是分批删小睡一下DELETE FROM log WHERE create_time 2024-01-01 LIMIT 5000;循环执行每批之间稍微停顿几十毫秒给主从同步和锁释放留出时间。这是我从实际故障里学出来的经验非常重要。3. 索引为什么你的查询还是慢3.1 索引的数据结构为什么是B树而不是别的聊索引之前先把底层数据结构整明白不然你永远在玄学调优。MySQL InnoDB引擎用的是B树而不是二叉树也不是跳表。用B树的核心理由在于它是为磁盘IO设计的。B树的每个节点可以存很多键值树高很低一般三到四层就能存千万级数据。也就是说你在千万行表里查一条数据最多只需要三四次磁盘IO这是磁盘存储时代的最优解。B树和非叶子节点不存数据只存索引键这意味着同样的内存能加载更多索引节点缓存命中率更高。而它的叶子节点用双向链表串起来天然支持范围查询——你查1月到3月的数据不用来回跳树顺着链表扫过去就行。有人会问那为什么不直接用哈希索引哈希索引确实快等值查询O(1)级别的速度但它没法做范围查询也没法做排序和前缀匹配。MySQL的InnoDB其实支持自适应哈希索引但那是内部优化的业务上我们主要用的还是B树索引。3.2 复合索引和最左前缀原则到底该怎么建复合索引是解决复杂查询的主要武器但也是最容易被用错的。复合索引的核心规则是最左前缀原则查询条件只有包含复合索引最左边的列时索引才会生效。比如(a, b, c)这个复合索引它能加速a、ab、abc三种条件的查询但如果你直接查b或bc索引就用不上。基于这个原则建复合索引前要先分析你的查询模式把区分度高、查询频繁的列放在最左边。举个例子。订单表有商家ID、订单状态、创建时间三个字段业务查询经常是某个商家某时间段内的订单状态统计。那复合索引建(merchant_id, status, create_time)就比较合理因为它能把最常出现在WHERE条件里的merchant_id放最左后续条件再依次命中。但如果你不确定查询条件组合建太多复合索引反而坏事——每个索引都要占用存储空间每次插入更新都要维护写性能会被拖垮。一条比较实用的经验是一个表不要超过5到6个索引能用复合索引覆盖的就别建多个单列索引。还有一个重要概念是覆盖索引。所谓覆盖就是查询的所有列都能从索引直接拿到不用回表。比如你查SELECT status FROM order WHERE merchant_id 1如果(merchant_id, status)的复合索引存在那么这条查询会直接走二级索引返回速度极快。在写高频查询的时候我通常会把SELECT的列也考虑进索引设计目的就是制造覆盖索引。3.3 EXPLAIN每个进阶选手的必修课你写完一条SQL怎么知道它会不会慢答案是EXPLAIN。EXPLAIN SELECT id, name FROM user WHERE status 1 AND create_time 2024-01-01;看几个关键列就够type这一列包含从好到差的几个值const、eq_ref、ref、range、index、ALL。ALL就是全表扫描这是你要警惕的type至少要到range级别才算健康。key这一列显示实际用到的索引。如果显示NULL说明这次查询是乱扫通常有问题。rows这一列是预估扫描的行数。数值越小越好。如果扫描行数能从百万级别变成几千级别优化效果肉眼可见。还有一个比较少人提但很有价值的技巧用EXPLAIN ANALYZEMySQL 8.0能看到实际执行时间和行数这比预估的rows更真实。我在排查慢SQL时经常直接上这个命令可以精确地看到每一步的耗时快速锁定瓶颈所在。举个例子我遇到过一次慢查询某个帖子列表接口数据量一千万响应时间从500ms涨到3秒。用EXPLAIN ANALYZE一看发现问题不在主表而在关联的left join子表上关联字段没走索引导致每一行都要做一次全表查找。后来在子表的关联字段上建了索引接口直接回到200ms以内。这就是分析的威力。4. 事务与锁并发场景的必修课4.1 事务隔离级别别用默认值就觉得万事大吉很多人一直用MySQL默认的可重复读REPEATABLE READRR从没想过为什么。RR级别下InnoDB通过MVCC实现快照读保证同一个事务里多次查询结果一致这在某些业务场景下确实重要比如对账和报表。但RR也带来了明显的副作用间隙锁。在RR级别下InnoDB不仅锁住命中的记录还会锁住记录之间的间隙防止幻读。什么是幻读就是一个事务里两次范围查询第二次多出了一些本来不存在的行。为了防止幻读RR会锁间隙这就容易导致各种奇怪的锁等待。很多线上系统其实不需要RR。如果业务只是普通订单、用户、内容系统读多写少读已提交READ COMMITTEDRC完全够用——它只锁记录不锁间隙并发度更高死锁概率更低。RC唯一的问题是同一个事务里两次查询可能读到不同结果这在绝大多数业务场景下是可接受的。所以我的建议是如果你们业务没有严格的一致性读取要求把全局隔离级别改成RC。这是成本极低但收益明显的调优项。4.2 行锁、间隙锁和死锁线上死锁排查实录先讲一个我踩过的真实场景。有一个库存扣减服务两个并发请求同时操作同一个商品的库存代码逻辑是先查库存、判断库存够不够、再扣减。因为判断和扣减之间没有加锁两个请求同时读到库存剩1都满足扣减条件结果两个请求都扣减成功库存变成了-1。这事的本质是读到了过期的快照数据。解决方式至少有两种方案一在扣减SQL里直接加条件让更新操作原子化UPDATE stock SET amount amount - 1 WHERE product_id ? AND amount 1;如果影响行数为0说明库存不足直接返回失败。方案二显式加行锁先锁住查询的行SELECT * FROM stock WHERE product_id ? FOR UPDATE;这两种方式都是把检查-修改的窗口期去掉让并发操作串行化。再说死锁。死锁的典型场景是两个事务以相反顺序锁住了多个资源。比如事务A先锁用户表再锁订单表事务B先锁订单表再锁用户表两边都在等对方释放资源就死锁了。排查死锁现场的原生办法是看SHOW ENGINE INNODB STATUS它会打印LATEST DETECTED DEADLOCK的详细信息包含涉及的事务和锁住的记录。但要注意这个输出非常长最好把关键段落截出来里面LATEST DETECTED DEADLOCK之后的几百行就是你要看的信息。要从根上减少死锁几个接地气的做法所有事务都按固定顺序访问表比如统一先处理用户信息再处理订单信息尽量缩短事务时间把无关查询拿到事务外面拆分大事务别一个事务里更新几百行再插入几百行更新数据时尽量命中主键不然行锁会升级成更宽的锁4.3 MVCC可重复读的底层逻辑MVCCMulti-Version Concurrency Control多版本并发控制是InnoDB实现隔离级别的核心。简单说它让每个事务能看到一份基于版本的数据快照读操作和写操作互不阻塞。InnoDB在每行记录后面维护了版本信息通过两个隐藏列实现DB_TRX_ID最近修改该行的事务ID和DB_ROLL_PTR回滚指针指向undo日志中该行旧版本的数据。不同事务读数据时对照自己的视图来判断哪个版本可见。打个生活化的比方。MVCC相当于给每行数据拍了多张照片每个事务手里拿着一份看到哪个版本的规则表。你读数据时MySQL会找到你规则表里符合条件的那张照片给你看其他事务想改数据就去改当前版本并生成新照片完全不影响你看旧照片。这就是为什么RR级别下快照读普通SELECT不会被其他事务的更新阻塞也不会读到还没提交的数据。但要注意当前读SELECT ... FOR UPDATE、UPDATE、DELETE永远读最新版本这就是我之前讲的更新操作使用了当前读那句话背后的机制。5. 从单表到分布式真正的高级手段5.1 主从复制延迟问题不可忽视到了这个阶段单机MySQL已经扛不住读写并发压力了最常见的架构是主从复制主库写从库读读写分离。主从复制的原理并不复杂主库把数据变更写入binlog从库的IO线程把binlog拉过来写到自己的relay logSQL线程再把relay log里的日志执行一遍完成数据同步。但这里有一个经典坑主从延迟。尤其是在大事务、DDL操作、或者从库配置较弱的情况下从库同步会明显落后主库。业务上最直接的后果是你在主库写了一条数据立刻去从库读结果读不到——也就是常说的读写分离的一致性问题。应对延迟我常用的几个手段核心数据强制走主库读只把非核心的、允许短暂延迟的数据分到从库监控seconds_behind_master指标超过阈值报警关键查询使用SELECT ... FOR UPDATE强制走主库这个不推荐太重了还是业务层标记刚写入的数据读主库最实际从库升级硬件、增加并行复制参数让SQL线程并行度更高5.2 分库分表不要被这个词吓到分库分表听起来高大上其实核心就是把一张大表拆成很多张小表把一个大库拆成很多个小库让单表数据量和单库连接数降下来。分表的第一原则是能不拆就不拆。这真不是废话很多系统的数据量到千万级其实还能扛一旦拆了业务代码要改查询要路由操作用户跨表就麻烦复杂度成倍上涨。我见过不少团队在数据量只有百万的时候就开始分表结果把自己折腾得苦不堪言。真正需要拆的时候常见的分片字段就是业务实体的核心维度大多数场景按用户ID分片少数按订单ID、时间分片。分片方式主流有两种范围分片比如按月份分表order_202401、order_202402。优点是实现简单、扩展方便缺点是热点集中在最新表哈希分片order_0、order_1到order_15按用户ID哈希取模路由。优点是数据分布均匀缺点是需要提前规划好分片数量后续扩容要迁移数据分库分表的运维复杂度主要体现在几个方面全局主键要改成雪花ID跨分片的查询要用中间层聚合分布式事务需要引入额外方案。所以拆之前先问三个问题数据量真的到瓶颈了吗写并发真的扛不住了吗架构上真的没有其他优化手段了吗三个都答是再动手。5.3 读写分离后的连接管理架构从单机变成主从之后应用程序的连接管理也要跟着变。最直接的方案是使用中间层如数据库代理来做读写分离应用只管连一个地址代理自动把读写请求分流。但如果不想引入太多组件在代码层面做路由也可以维护一个数据源集合根据方法名或注解来判断走主库还是走从库。注意这里有个容易踩的坑事务内不能切换数据源。一个事务里先写再读如果读走了从库就会因为事务隔离级别问题读到旧数据甚至报错。所以实际落地时我先给必须走主库的读操作打上强制标签同时禁止在事务中间动态切换数据源这个问题就能避免。6. 性能优化进阶定位慢SQL、压测与监控6.1 慢查询日志和性能分析的正确姿势遇到线上变慢第一步不是重构而是找到那些真正慢的SQL。MySQL有一套现成的工具慢查询日志。首先确认已开启慢查询日志SHOW VARIABLES LIKE slow_query_log;如果没开临时开启和设置阈值SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;long_query_time的单位是秒线上一般设置1秒就够用了。但要注意慢查询日志记录的是执行完成的SQL如果一个SQL因为锁等待卡了10秒它执行完也会被记录。但如果你在锁等待期间就想发现它那得配合performance_schema的等待事件或者直接看SHOW PROCESSLIST里State为Waiting for table metadata lock的线程。拿到慢SQL之后我再教一个非常实用的分析流程用EXPLAIN ANALYZE看执行计划确定访问类型、扫描行数、实际耗时重点看有没有ALL全表扫描、有没有filesort排序、有没有临时表根据执行计划尝试添加或调整索引改写SQL把子查询改成JOIN、把函数操作挪出WHERE模拟压测用mysqld自带的压测工具或者业务请求验证效果我自己的习惯是每一条慢SQL都记录到一个文档里标注问题原因、优化措施、优化前后耗时。这样积累几个月你对索引和SQL性能的直觉会非常准再遇到类似问题基本一眼就能判断。6.2 大表DDL改表结构竟然会锁住线上业务这个话题值得单独拎出来写一节因为太多人在上面吃过亏。在MySQL 5.6之前ALTER TABLE加字段会锁全表期间所有写操作全部阻塞这对于7x24小时在线的业务来说几乎不可接受。即使5.6之后引入了在线DDLOnline DDL也不是所有DDL操作都能完全在线有些操作表数据量大时仍然会消耗大量IO和CPU。所以在大表上做结构变更时我的实操方式是选择业务低峰期操作凌晨2到4点之间最稳妥先用一个小表测试确认SQL执行时间和锁行为如果表非常大比如超过千万行优先使用pt-online-schema-change这类工具它通过临时表触发器的方式把DDL变更为增量拷贝尽量不影响线上读写变更过程中持续监控主从延迟一旦延迟超过阈值就暂停如果DDL真堵住了线上切忌随手KILL线程。先看锁等待链头找到哪个会话持有元数据锁再决定是等它结束还是优雅地终止相关事务。6.3 压测模拟真实场景把SQL打回原形上线前压测是成本最低的兜底。很多人对压测有误区觉得压测就是拿工具猛打一个接口看CPU有没有飙高。其实真正有用的压测是去模拟线上真实的负载特征和并发模型。我最常用的压测工具是sysbench。它可以做单表/多表的读写混合压力测试。举个实际的用法sysbench --db-drivermysql --mysql-host127.0.0.1 --mysql-port3306 \ --mysql-userroot --mysql-password123456 \ --table-size1000000 --threads16 --time60 \ oltp_read_write run这里面有几个参数我解释下--table-size决定数据量--threads决定并发线程数--time决定压测时长。压测结果会有TPS每秒事务数和QPS每秒查询数以及延迟分布重点看p95和p99延迟——这两个值才是用户体验的真实感受平均延迟在峰值场景下参考意义不大。压测过程中顺手看主库的慢查询日志和锁状态通常能发现一些平时不起眼、但并发一高就原形毕露的SQL。我的经验是单线程跑得快的SQL高并发下不一定快单线程就慢的SQL高并发下一定是灾难。所以优化顺序永远是先把单条慢SQL干掉再考虑系统层面的并发参数。7. 一条完整的MySQL进阶路线图学MySQL最忌讳东一榔头西一棒子。我个人建议的进阶路线大概是这样的第一阶段基础功1到2周掌握数据库设计三范式但也要知道什么时候刻意违反它冗余换性能把CRUD的语法写熟练包括多表JOIN、子查询、GROUP BY、HAVING熟悉常用聚合函数和日期函数会处理NULL和空字符串的坑第二阶段性能意识2到4周把索引原理吃透B树、复合索引、最左前缀、覆盖索引学会用EXPLAIN和EXPLAIN ANALYZE做执行计划分析独立完成至少三张百万级数据表的慢SQL优化第三阶段并发与架构1到2个月理解事务隔离级别、MVCC、行锁/间隙锁/死锁动手搭一套主从复制观察binlog和relay log读两三篇分库分表相关的源码和运维实践尝试在小项目里做读写分离和连接管理第四阶段生产环境实战持续独立负责一个MySQL相关的线上问题排查从现象、定位、修复、到复盘积累一套自己的监控指标慢查询数、锁等待、主从延迟、连接数学会用备份恢复的手段保住数据定期演练这条路看着周期长但每一步都是给后面打地基。数据库这东西理论和实践脱节特别严重你光看文档永远不知道自己写的SQL在真实负载下会出什么幺蛾子。8. 避坑手册那些让我印象深刻的教训这一节是真正的花钱买来的经验都是我或身边同事在线上踩过的坑写出来给大家避雷。教训一字段类型选错索引直接失效。有一次排查慢查询发现某表关联字段一个是VARCHAR一个是INTMySQL在JOIN时会隐式类型转换导致索引失效全表扫描。把类型统一成一致查询时间直接下降了95%。排查索引问题之前先看一眼字段类型是不是一致这个细节太容易忽略。教训二事务里混入了远程调用。某个代码在一个事务里面调用了外部接口外部接口响应慢导致数据库连接长时间持有最后连接池耗尽整个服务雪崩。事务就是事务不要把网络IO、远程调用、甚至消息发送塞进去。所有外部依赖都应该是拿到事务外先做完再在事务里只做数据库操作。教训三表连接顺序不是你想的那样。LEFT JOIN的结果和表连接顺序本来无关但优化器会根据统计信息自己决定实际的执行顺序。如果你发现某个JOIN明显应该走小表驱动大表却走了反的多半是没有对应索引或者统计信息过期了。ANALYZE TABLE也许能帮你解决这种问题。教训四备份不是一份就完事。我曾经因为误删了一张核心配置表恢复时才发现备份机制只保留了最近7天的数据而这次误删后业务日志已经把无关的脏数据写进去了。从那以后我学到的经验是备份要异地、要校验、要保留多份时间点而且每季度至少搞一次恢复演练别把恢复当儿戏。教训五批量导入数据时先关掉约束检查很多新手从外部导入数据为了让速度变快会临时关掉外键检查和唯一约束。这样导入是快但如果数据本身有重复或不一致导入后就很难清理了。建议只在可控的数据导入场景下临时关约束导入完必须重新开启校验并且做一次完整性核查。9. 写在最后的个人体会算下来我跟MySQL打交道的时间不短了最深的感受是这门技术没有太多玄学一切的慢都有原因一切的锁都有逻辑。多数的线上故障都源于对底层机制的理解不够而非运气不好。如果你正在从零开始学我的忠告是把基础啃扎实多建几张真实数据量的表去折腾多写慢SQL然后想一切办法优化它别急着追新版本、新技术。MySQL的很多核心机制从5.x到8.x都几乎没变你把这些底层的东西搞明白后面无论用什么数据库都会事半功倍。另外想说DBA或后端工程师的核心竞争力不在于你背了多少命令而在于你解决问题时脑子里是否有一条清晰的排查路径。遇到问题不要慌先看日志、再做执行计划分析、再考虑索引和架构按这个顺序走80%的问题都能在半小时内定位。希望这篇内容能帮你在MySQL的路上少走些弯路。练好基本功处理好每一个细节你也能从增删改查一步步走到能拍板架构的位置。