1. 从第一篇到第二篇进阶学习路线怎么安排记数据、查数据这是大多数人接触 MySQL 的前两步。上一篇基础笔记里我写了怎么连库、怎么建表、怎么写最简单的增删改查整套操作就像学会了一门新语言的打招呼方式能跑通但离用得好还差得很远。这篇MySQL 基础笔记二我会把焦点从能用挪到用好上。具体来说是四个绕不开的核心模块表结构怎么设计才不容易出乱子、数据类型怎么选才不浪费空间又不会踩坑、索引和约束到底解决了什么问题、事务为什么是保证数据不出错的底线。这些内容表面上不如一条SELECT看起来那么立竿见影但实际写业务的时候每天遇到的慢查询、死锁、数据对不上源头几乎都在这几个点上。适合看这篇笔记的人有两类。一类是已经会写基本 SQL、想系统补一下原理的初级开发者另一类是写过一阵子业务、被上线后各种诡异问题折磨过、想回过头来弄明白当初建表为什么应该这么建的同学。这篇笔记不会讲太深的源码也不会扯太远的运维部署就是围绕日常开发最常用到的那层知识尽量把每个选择背后的原因讲清楚。我整理笔记的习惯向来是先问为什么再记是什么。所以这一篇里你看到的不只是语法和参数更多的是我踩坑之后倒推出来的经验比如为什么varchar和char用错会导致磁盘空间白白多耗三分之一为什么索引不是越多越好为什么事务没提交会拖垮整个业务。这些内容在官方文档里也有但散落在各个章节我用自己的顺序把它们串了一遍希望你读的时候能少走我走过的弯路。那咱就开始按一条实际业务表从设计到落地的顺序来。先看建表时期最容易被忽略后期又最难改的东西——表结构和数据类型。2. 表结构与数据类型的正确打开方式2.1 整数类型别上来就int(11)主键选择更要慎重新手建表最常见的操作就是打开某个建表工具照着模板把id int primary key auto_increment写上顺手再加一个int(11)的常规字段感觉就完事了。但整数类型的选择还真不是能用就行这么简单。MySQL 里整数类型主要有tinyint、smallint、mediumint、int、bigint五种区别就是存储字节数和能表示的数值范围。tinyint占 1 字节范围是 -128 到 127无符号是 0 到 255int占 4 字节范围约 -21 亿到 21 亿bigint占 8 字节基本上可以认为大到用不完。这里的常见误解是int(11)里的 11 代表最大长度。其实在 MySQL 8.0 之前这个数字只是显示宽度配合zerofill才有补零的意义对存储空间和取值范围没有任何影响。MySQL 8.0 之后显示宽度已经被废弃所以别再纠结该写 11 还是写 20老老实实根据业务量决定类型是正经。我自己的主键选型经验是并发量不确定、数据量可能上千万的表直接用bigint unsigned auto_increment别省那 4 个字节省出来的那点空间远不够你日后迁主键时折腾的成本。业务上需要对外暴露 ID 的比如订单号、用户 ID建议直接用雪花算法生成 bigint 型分布式 ID不要靠自增主键对外暴露容易被人遍历抓数据。如果只是一个状态字段比如是否删除、是否启用用tinyint就绰绰有余用int纯属浪费。2.2 小数与金额float和double会害你差一分钱整数聊完了说小数。日常开发里金额计算是最敏感的场景但很多人建表时顺手就写了float或者double这时候麻烦已经埋下了。float和double是浮点数按二进制存储天生有精度损失。比如0.1用二进制无法精确表示存进去再读出来可能是0.10000000000000001。算个订单金额单个商品差一厘钱订单多了累计起来对账怎么都对不上。金额、余额、利率这类要求精确计算的字段一律用定点数decimal。decimal(10,2)表示总共 10 位小数占 2 位能表示的范围是 -99999999.99 到 99999999.99对于绝大多数业务场景足够了。如果涉及更精细的费率计算可以扩到decimal(12,4)甚至decimal(20,4)在计算阶段保留更高精度展示时再四舍五入。还有一个小细节字符串类型存数字比如电话号码、身份证号、订单号一定不要用int或bigint去存。一是前面的 0 会丢掉二是这类字段往往不需要做数值运算用varchar存更稳妥。我见过有人用bigint存手机号结果前导 0 的号段全部显示异常这种问题排查起来还得靠数据对比非常冤枉。2.3 字符串与日期varchar和char的取舍以及时间类型选对不选贵字符串类型里最常用的是char和varchar。区别一句话就能说明白char定长存 3 个字符也占满 N 个字符的空间varchar变长实际多长就多用多少空间但额外需要 1 到 2 个字节记录长度。那是不是所有字段都用varchar就行也不是。非常确定不会变长的数据比如身份证号18 位、手机号11 位、MD5 摘要32 位用char反而合适省去了长度记录的开销写入时也不用动态计算偏移。长度会变、长短差距大的字段比如地址、昵称、备注用varchar就对了。给varchar设长度时别习惯性写 255先想想业务上最长的值可能是多少留 1.5 到 2 倍余量就够。varchar(255)和varchar(500)在数据量大了以后对索引大小和排序性能是有实际影响的。日期时间类型也是重灾区。datetime占 8 字节范围从 1000 年到 9999 年跟时区无关存什么就是什么timestamp占 4 字节范围到 2038 年跟时区相关内部存储是 UTC 时间。绝大部分业务场景直接用datetime省心不用考虑 2038 年问题也不用担心服务器时区一变时间全错位。有一个很容易忽略的点时间字段默认值的坑。MySQL 5.6 之后datetime才支持默认值为CURRENT_TIMESTAMP如果你的 MySQL 版本比较老建表时datetime DEFAULT CURRENT_TIMESTAMP会直接报错需要用TIMESTAMP或者在前端代码里赋值。新项目直接上 MySQL 8.0 就没有这个困扰了但如果还在维护老项目建表前先确认版本不然 SQL 写得很顺手一执行才发现完全不兼容。2.4 字符集与排序规则乱码问题八九成是建表时埋的雷只要是中文业务几乎都遇到过乱码。乱码的根源百分之九十九是字符集不一致建表时没统一连接时也没指定。我的习惯是库、表、字段三级全部显式指定utf8mb4排序规则用utf8mb4_general_ci新时代项目也可以用utf8mb4_0900_ai_ciMySQL 8.0 默认是这个。utf8mb4才是真正的UTF-8 全集能存 emoji、生僻字、特殊符号。老项目里常见的utf8其实是 MySQL 的残缺版 UTF-8最多支持 3 字节遇到 emoji 就直接报错或者变问号。连接层也要注意。用客户端工具或者 JDBC 连接串里加上characterEncodingutf8或者utf8mb4并且确认数据库连接参数里没有把字符集覆盖成别的值。我排查过最经典的乱码场景库里数据是正常的工具里查出来也是正常的但程序查出来乱码最后发现是 JDBC 连接串没写编码参数驱动用默认值去连了整个链路对不上。这种问题排查起来非常快但第一次遇到时容易满数据库找原因完全没想到是连接参数的事。从建表角度总结一句字符集和排序规则在建表时一次设对后面基本不用再碰如果没设对跨库迁移、字段合并、模糊查询结果集排序到处都会出幺蛾子。3. 索引与约束让查询性能不再看运气3.1 索引的本质不是加了就快而是查得有依据在没有索引的情况下MySQL 要找一条数据只能从头到尾扫一遍表专业点叫全表扫描。数据量小还好几十万行也能忍到了千万级别一次全表扫描可能就是几百毫秒甚至秒级业务上完全不可接受。索引就是为这个痛点存在的。它像一本书的目录把数据的键值和物理位置建立了映射关系查询时先查目录再定位数据。MySQL 里大部分索引用的数据结构是 B 树它的特点是矮胖三层就能撑起千万级数据量每次查询走的路径基本固定所以性能稳定。但索引不是银弹。索引本身要占存储空间写入时要额外维护索引结构所以每次INSERT、UPDATE、DELETE都要付出额外代价。这就引出建索引的基本原则查询频繁的字段才需要索引写入频繁的字段要克制加索引。业务上最常见的索引场景有这么几类主键索引一般用id聚簇索引数据行物理上按主键顺序存储。唯一索引业务上要求不能重复的字段比如用户手机号、订单号。普通索引常见查询条件比如按状态查、按用户 ID 查列表。联合索引多个字段一起作为查询条件比如(user_id, status)组合查。联合索引有一个核心概念叫最左前缀原则。比如建了(user_id, status, create_time)这个联合索引那么它能高效支撑的查询条件是user_id单独查、user_id status组合查、user_id status create_time组合查。但如果直接跳开user_id用status单独查这个索引就用不上。这就像查字典先查拼音首字母、再查音节、最后查具体字顺序不能乱。理解了这条原则建联合索引时字段顺序就不会拍脑袋了。我自己的经验是联合索引的字段顺序按区分度高、查询固定这两个维度排。比如一个订单查询场景user_id是基本必带的status是常见筛选create_time是排序或者范围条件那联合索引顺序定为(user_id, status, create_time)是合理的。反过来如果status只有两三个值区分度太低放在前面会浪费索引的筛选能力。3.2 索引失效的常见姿势不是建了就一定生效索引建好之后并不是说查询里写了这个字段就一定会走索引。我实操中踩过的坑整理成了一份索引失效清单每次写 SQL 前过一遍脑子第一对索引字段使用了函数或计算。比如WHERE DATE(create_time) 2024-01-01这个写法会让 create_time 上的索引失效因为 MySQL 必须先对每行做函数计算才能比较。解决方案是改成范围查询WHERE create_time 2024-01-01 AND create_time 2024-01-02。第二隐式类型转换。字段是varchar类型查询条件写数字比如WHERE user_phone 12345678901MySQL 会偷偷做类型转换索引直接废掉。正确做法是查询参数跟字段类型保持一致。第三LIKE以 % 开头。WHERE name LIKE %张这种写法因为前缀不确定B 树无法走索引做精确匹配。如果业务确实需要这种模糊搜索建议用全文索引或者搜索引擎MySQL 的普通索引对这种场景无能为力。第四OR连接的条件里有一个没索引。WHERE id 1 OR status 0如果status没有索引MySQL 为了完整结果集可能会放弃走id的索引转而做全表扫描。把OR改成UNION或者确保两边条件都有索引会更好。第五NOT IN、!等否定查询。这些操作在 B 树上很难高效定位大部分情况下会选择扫全表。能用正向条件书写逻辑的就用正向条件写。这里有个很重要的认知EXPLAIN是排查索引是否生效的必备工具。写任何一条 SQL 前先EXPLAIN SELECT ...看输出结果里的type字段——从好到差依次是const、eq_ref、ref、range、index、ALL。看到ALL就说明在扫全表需要抓住时机优化 SQL 或者索引。我工作这些年养成的最值钱的习惯就是写查询先 EXPLAIN上线前慢查日志扫一遍很多性能问题在线下就暴露了。3.3 约束主键、唯一、外键、非空、默认值每个都有存在的理由约束是数据库替你守住的数据底线。没有约束的情况下业务代码哪怕是多线程并发写也容易产生脏数据有了约束至少数据库层面给了一个强兜底。常见约束有五类主键约束每行数据的唯一标识一张表只能有一个主键主键不能为空。InnoDB 引擎下数据文件本身就是按主键排序的聚簇索引结构所以选对主键非常重要。唯一约束保证字段值在表内不重复。比如用户表里的手机号加了唯一约束后并发注册时数据库直接拒绝重复插入比应用层先查再插可靠得多。非空约束保证字段必须有值。业务上必填的信息比如订单金额、用户昵称建议设NOT NULL。默认值约束字段没传值时候的兜底。比如创建时间默认当前时间、状态默认 0很省心。外键约束保证子表引用父表数据的一致性。这个我在实际项目中用得很少因为互联网高并发场景下外键的强一致性检查容易造成锁竞争通常靠应用层逻辑来保证一致性。如果你是做传统企业管理系统数据量不大外键还是可以用的。举一个并发场景下的经典例子一个用户表两个线程同时插入同一个手机号。没有唯一约束两条数据都进来了后面业务里按手机号查用户查出两条就出大乱子。有了唯一约束第二个插入请求直接报错程序捕获这个报错再做提示即可。这就是约束的价值——把业务上不能重复的规则用数据库强制力落地。关于非空约束我的建议是尽量让字段都有默认值尽量都设NOT NULL。因为 MySQL 里索引对 NULL 的处理更复杂查询时三值逻辑TRUE、FALSE、UNKNOWN也容易写出隐蔽的 bug比如WHERE status 0不会返回NULL状态的那一行。如果业务用 0 和 1 表示关闭和开启就别设置成允许 NULL直接默认 0。4. 事务基础理解 ACID 才能不丢数据4.1 事务是个啥说白了就是要么全做要么全不做事务这个概念用生活里最接地气的例子就是转账。A 给 B 转账 100 块数据库里要执行两步A 扣 100B 加 100。如果 A 扣完钱之后B 加钱那一步突然失败了钱就凭空消失了这在现实中绝对不行。事务就是数据库用来保证这种多步操作的一致性的机制。一个事务要么全部执行成功并提交COMMIT要么全部失败并回滚ROLLBACK。事务有四个经典特性合起来叫 ACID原子性Atomicity事务里的操作不可分割要么全成要么全败。MySQL 里靠 undo log 实现回滚执行失败时能恢复到事务开始前的状态。一致性Consistency事务执行前后数据始终符合业务规则。比如余额不能为负、A 扣的钱等于 B 加的钱。这个特性其实是应用层加数据库层共同努力的结果。隔离性Isolation多个事务并发执行时互相之间应该有一定隔离不能互相干扰。持久性Durability一旦事务提交数据永久落盘。MySQL 里主要靠 redo log 保证即使数据库突然宕机重启后也能恢复已提交的数据。事务最基本的使用方式就三行命令START TRANSACTION; -- 这里写业务 SQL UPDATE account SET balance balance - 100 WHERE user_id 1; UPDATE account SET balance balance 100 WHERE user_id 2; COMMIT;如果中间某一步出错就把COMMIT换成ROLLBACK;数据库会把事务里所有操作都撤销掉。注意事务里的 SQL 在COMMIT之前其他会话默认是看不到这些未提交的修改的这个取决于隔离级别下文会细说。我实际开发里最常见的错误是代码里开了事务但因为某个环节抛了异常没捕获导致事务既没COMMIT也没ROLLBACK。这时候连接就一直挂在那个半开的事务上数据库连接池很快被占满应用彻底卡死。排查这种问题可以查information_schema.innodb_trx表看看有哪些事务长时间未提交。这也是我在常见问题章节要重点展开的一个场景。4.2 四种隔离级别你要的是读得快还是不错数据事务并发执行时如果完全不做隔离会出现三种异常读现象脏读一个事务读到了另一个事务未提交的数据。万一那个事务回滚了你读到的就是无效数据。不可重复读同一个事务里前后两次查询同一行数据结果不一样。因为另一个事务在两次查询之间提交了修改。幻读同一个事务里前后两次查询同一个条件的结果集不一样多出来几行或少了几行。比如查某天的订单数量第二次查多了一条像幻觉一样。SQL 标准定义了四种隔离级别MySQL 默认用的是可重复读REPEATABLE READ读未提交READ UNCOMMITTED最低级别啥也防不住可能会脏读。实际生产基本不用。读已提交READ COMMITTED防止脏读但可能出现不可重复读。很多其他数据库比如 PostgreSQL默认是这个级别。可重复读REPEATABLE READ防止脏读和不可重复读。MySQL 默认就是它。听起来好像幻读还在但 InnoDB 通过间隙锁等手段在大部分场景下也把幻读问题解决了。串行化SERIALIZABLE最高隔离级别事务完全串行执行相当于没有并发性能最差。MySQL 的 InnoDB 引擎在可重复读级别下还有一个重要的底层机制叫快照读Snapshot Read。普通SELECT走的是快照读它在你第一次读取时生成一个一致性快照后续查询都从这个快照里取数所以同一个事务里多次查询结果是一致的。而SELECT ... FOR UPDATE、UPDATE、DELETE这类语句走的是当前读读的是最新已提交的数据并且会对涉及的行加锁。我举个具体场景帮助理解一个订单表事务 A 里先SELECT查了一次订单状态事务 B 把状态改了并提交事务 A 里再SELECT一次在可重复读级别下看到的仍然是第一次查到的状态。这个特性对业务可能就是好事也可能是坑。好事是报表统计时数据一致坑是如果你在事务 A 里查完状态之后又要用这个状态做后续判断可能判断的是旧状态。所以在写复杂事务时要清楚哪些语句是快照读哪些是当前读避免逻辑基于过期数据。隔离级别的设置方式-- 当前会话级别 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 全局级别 SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;生产环境切换隔离级别之前一定要先在测试环境跑一遍核心事务的并发场景因为从可重复读切到读已提交后一些基于事务内多次查询结果一致的代码逻辑会发生变化。4.3 锁行锁、间隙锁、死锁这些名词别等出事了才查事务隔离的实现底层靠的是锁机制。InnoDB 支持行级锁锁的粒度比表级锁小很多并发性能更好。行锁大致分成两类共享锁S Lock也叫读锁多个事务可以同时持有共享锁但不能写。排他锁X Lock也叫写锁一个事务持有排他锁时其他事务不能读也不能写普通快照读除外因为快照读走的是版本链。SQL 层面触发锁的方式就是前面提过的当前读。UPDATE、DELETE、SELECT ... FOR UPDATE都会对涉及行加排他锁。如果一个事务 A 更新了一行还没提交事务 B 也去更新同一行B 就会阻塞等待直到 A 提交或回滚。对于秒杀、抢购这类高频更新同一行的场景这个等待可能拖垮整个系统的吞吐量所以要尽量缩短事务时间别在事务里做耗时很长的外部 API 调用或复杂的业务计算。间隙锁是 InnoDB 在可重复读级别下用来解决幻读的关键。它锁的不是某一行而是索引记录之间的间隙防止别的事务在这个间隙里插入新数据。间隙锁带来的副作用是可能扩大锁范围导致并发插入性能下降。在某些压测场景下你会发现明明更新的是不同行的数据但因为它们在同一个间隙里互相还是阻塞了。死锁是我被问过最多的名词之一。死锁就是两个事务互相持有对方需要的锁谁也不让形成僵局。InnoDB 的处理方式是死锁检测检测到死锁后会回滚其中一个事务通常是影响较小、代价较低的那个让另一个继续执行。提示信息一般是Deadlock found when trying to get lock。遇到死锁不要慌关键是分析造成锁循环的 SQL 顺序。最常见的死锁场景是事务 A 先更新表 X 再更新表 Y事务 B 先更新表 Y 再更新表 X。两个事务交错执行就可能互相卡住。解决方案也很简单所有事务里涉及多张表的更新都按同一个顺序进行比如统一先更新 X 再更新 Y死锁概率会大幅下降。另外缩小事务范围、减少锁的持有时间是降低死锁频率的最有效手段。5. 实操演练从建表到事务的完整落地5.1 一个贴近业务场景的建表 SQL光聊理论不给例子等于白说。我来做一个常见的订单模块表把前面提到的知识点全部揉进去。CREATE TABLE orders ( id bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 主键, order_no varchar(32) NOT NULL COMMENT 业务订单号, user_id bigint unsigned NOT NULL COMMENT 下单用户ID, total_amount decimal(12,2) NOT NULL DEFAULT 0.00 COMMENT 订单总金额, status tinyint unsigned NOT NULL DEFAULT 0 COMMENT 订单状态0待支付 1已支付 2已发货 3已完成 4已取消, receiver_name varchar(50) NOT NULL DEFAULT COMMENT 收货人姓名, receiver_phone varchar(20) NOT NULL DEFAULT COMMENT 收货人手机号, address varchar(255) NOT NULL DEFAULT COMMENT 收货地址, remark varchar(255) NOT NULL DEFAULT COMMENT 买家备注, create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_user_status_time (user_id, status, create_time) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单表;逐个看这个建表语句里的决策理由。id用bigint unsigned自增主键考虑的是订单表长期积累后数据量很容易超过 21 亿的int上限四大字节空间换一生的省心。order_no是业务上的展示订单号跟id解耦。对外暴露时给用户看的是order_noid只在系统内部使用加了唯一索引防止重复订单号。业务生成订单号时可以是时间戳 随机数 用户 ID 片段保证唯一性。total_amount用decimal(12,2)精确到分完全避开浮点精度问题。decimal(12,2)最大 99 亿对订单金额够用。status用tinyint unsigned范围 0 到 255订单状态二十个以内随便放。为什么不用枚举enum因为后期加状态要改表结构而且用数字在代码里映射枚举值扩展性更好。字符串字段全部给NOT NULL DEFAULT 杜绝了 NULL 带来的各种隐蔽问题。create_time和update_time用datetime并且update_time使用ON UPDATE CURRENT_TIMESTAMP这样每次修改行数据时数据库会自动更新这个字段减少了应用层代码的负担。索引设计上uk_order_no对应订单号查询idx_user_status_time联合索引对应某个用户的订单列表按状态筛选再按时间排序这个高频查询场景最左前缀原则下单独按user_id查也能走这个索引。看完这个例子你会发现建表其实是一次提前想到未来所有查询路径的预演。表结构设计好了后面写 SQL 时哪里都要索引其实已经定型了。5.2 事务 行锁的实战模拟假设一个下单扣库存的流程用户下订单时需要扣减商品库存同时创建订单记录。这两步操作必须放在同一个事务里否则可能出现下单成功但库存没扣或者库存扣了但订单没建成的数据不一致问题。START TRANSACTION; -- 扣减库存 UPDATE inventory SET stock stock - 1 WHERE product_id 1001 AND stock 1; -- 检查更新影响行数 -- 如果影响行数为 0说明库存不足直接回滚 -- 实际代码中需要判断 ROW_COUNT() INSERT INTO orders (order_no, user_id, total_amount, status) VALUES (20240101001, 10001, 99.00, 0); -- 全部成功提交 COMMIT;这个例子里UPDATE inventory ... WHERE product_id 1001 AND stock 1是很经典的乐观锁式更新。它利用条件stock 1在数据库层面判断库存是否充足如果库存不足影响行数为 0代码里就可以触发ROLLBACK而不是先SELECT查库存再更新。因为先查再更在并发下会有时间差两个请求同时查到库存为 1都可能走更新逻辑结果库存变成负数。这个条件更新写法是我反复强调的在数据库层面解决并发一致性问题的典型例子。事务内的操作执行要快这是铁律。不要在事务里写外部 HTTP 请求、消息推送、文件上传因为事务会一直持有行锁外部服务一慢锁就迟迟不释放数据库连接和锁资源都在空耗。我见过最夸张的线上事故就是事务里调了一个第三方接口接口超时 30 秒事务占着的连接 30 秒才回滚数据库连接池瞬间被打满。5.3 EXPLAIN 实操一次真实查询的优化记录说个我之前处理过的慢查询案例。某后台管理页面要查某个用户 7 天内已支付的订单列表按创建时间倒序原始 SQL 大致长这样SELECT * FROM orders WHERE user_id 10001 AND status 1 AND create_time 2024-01-01 ORDER BY create_time DESC;执行EXPLAIN后发现type是ALL扫描行数是 200 万也就是说这条查询在扫全表。于是我在(user_id, status, create_time)上建了联合索引再跑一次EXPLAINtype变成了range扫描行数骤降到了几千条。为什么这个联合索引能生效因为 WHERE 里的三个条件是等值匹配加范围匹配联合索引(user_id, status, create_time)可以先把user_id精确匹配到一个用户的所有订单再把status精确匹配到已支付最后通过create_time做范围过滤。如果我把create_time放在联合索引的第二位status放第三位那么status的筛选就享受不到索引的精确匹配效率会差不少。这就是建索引前先想清楚哪些字段等值、哪些字段范围的意义。不过要注意SELECT *会让 MySQL 回表查询先走索引找到主键再根据主键回表取整行数据。字段不多的情况下可以考虑把需要查询的字段都放进索引做成覆盖索引就能避免回表。但也不能因此把所有字段都塞进索引索引膨胀会拖慢写入。一般先把高频查询的字段塞进去比如(user_id, status, create_time, order_no, total_amount)这种覆盖索引在报表查询里非常有用。6. 常见问题排查与独家避坑经验6.1 慢查询排查的三板斧遇到线上 SQL 变慢我的排查顺序固定三步从不乱套。第一步抓慢查询日志。MySQL 里开启慢查询日志很简单SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; -- 超过 1 秒的 SQL 记录设定之后慢 SQL 会被记录到日志文件里。先找出哪些 SQL 时间占比最高不要凭感觉优化。第二步对慢 SQL 执行EXPLAIN重点看type和rows。type是ALL或者rows很大说明走了全表扫描或索引选择性太差。第三步针对性地调整索引或者改写 SQL。调整完再EXPLAIN看rows是否明显变小。如果rows没变说明索引建了也没用回到 3.2 的索引失效清单逐条核对。这条链路我用了非常多次基本能覆盖 80% 的慢查询问题。6.2 事务未提交的灾难场景与快速定位事务没提交这个问题新同学特别容易踩。表现是程序执行变慢数据库连接数飙升SHOW PROCESSLIST里看到一堆Sleep状态的连接Time字段还很大。定位方法很直接查询information_schema.innodb_trxSELECT * FROM information_schema.innodb_trx ORDER BY trx_started LIMIT 10;输出里能看到trx_mysql_thread_id然后去SHOW PROCESSLIST里找到对应连接看看它最近执行了什么 SQL。绝大多数情况是代码里开了事务后没有在finally块里做回滚或提交异常一抛事务就悬在那里了。解决思路有两个层面代码层面事务操作必须配合 try-catch-finally确保任何分支都能走到提交或回滚数据库层面可以设置innodb_lock_wait_timeout行锁等待超时时间默认 50 秒可以根据业务调整到更短避免长时间锁等待。但最治本的还是代码规范事务里绝不能有未兜底的异常路径。6.3 用自增主键还是业务主键一个让我吃了亏的教训早期我建表时觉得用户表用手机号当主键就行了反正手机号也不会重复。后来真上线了才发现这是个大坑。原因有几个第一业务主键的规则可能会变。比如业务规则调整后允许两个账号绑同一个手机号历史遗留数据迁移场景主键唯一性的约束就变成了业务发展的障碍。第二手机号是带业务含义的字段如果有第三方接口或运营平台在传参时偶尔会传错误格式主键就报错。而自增主键是纯数据库内部值与应用层完全解耦永远不会因为业务数据不规范而出错。第三InnoDB 的聚簇索引结构决定了用业务主键可能会导致数据页分裂和碎片化。随机字符串主键插入时B 树需要经常调整节点性能比自增主键差不少。从那以后我的原则很明确任何表都加一个与业务无关的自增主键id业务字段该加唯一索引就加唯一索引但主键的活不让业务字段干。唯一的例外是纯字典表、配置表这种数据量很小且确实以某个编码为唯一核心的表可以直接用编码做主键。6.4 字符集乱码的完整排查路径乱码问题在基础笔记一里提过但这里值得再深入一下。乱码出现的环节按链路排查客户端输入 - 连接传输 - 数据库存储 - 查询返回 - 页面展示任一环字符集不一致就会出问题。排查路径我按四个点打看库、表、字段的字符集SHOW CREATE TABLE orders;确认是utf8mb4。看连接字符集确认客户端连接参数里的编码MySQL 命令行可以用SET NAMES utf8mb4;JDBC 用characterEncodingutf8mb4。看 HTTP 层应用返回的 Content-Type 是否charsetutf-8。看数据库驱动版本老版本驱动对utf8mb4支持不完整需要升级。乱码问题最讨厌的地方在于数据一旦以乱码形式写入数据库基本无法自动修复只能通过程序脚本替换或手动 UPDATE。所以建库建表时统一字符集是最省钱的保险。6.5 自增主键不连续为什么不是 bug自增主键在使用过程中会出现空洞比如插入失败也会消耗自增值事务回滚也不会把已经取到的自增值还回去。这样就可能出现 ID 不是连续递增的。很多非技术同事看到这种数据会说数据不对但那其实是完全正常的。MySQL 8.0 里自增值是持久化的MySQL 5.7 及之前版本重启后会根据当前最大值加一重新计算可能出现删除最大 ID 后新 ID 复用的风险。所以在 MySQL 5.7 及之前如果主键 ID 有做外部关联或需要严格递增的语义要注意这个细节。另外注意AUTO_INCREMENT字段必须是索引一般是主键否则建表会直接报错。这个语法细节刚开始踩过一次就会记住。7. 补充分享日常工作流里两个提升效率的小习惯前面从建表讲到索引、事务、排查内容跨度比较大。最后分享两个我个人的工作习惯谈不上高深但对效率提升很有帮助。第一个习惯是统一的建表规范模板。我会维护一份符合团队业务特点的建表 SQL 模板包含主键、公共字段创建时间、更新时间、删除标记、备注等、统一字符集和公共索引约定。新表直接从模板复制改业务字段就行。这样整个团队的库表风格非常一致排查问题时不会每张表都让人意外。比如删除标记这个字段有人用is_deleted有人用is_del数据类型也不一致跨表联查时明明是同一种意思却要用两套规范去理解非常内耗。第二个习惯是给所有索引和约束起名规范。主键就叫PRIMARY唯一索引用uk_开头unique key普通索引用idx_开头index后面跟字段名。比如uk_order_no、idx_user_status_time。这个习惯让我查线上索引元数据时一目了然。没有规范的情况下索引名千奇百怪index_1、aaa、bb都出现过后期做索引优化时根本分不清哪个是哪个。还有个小技巧SHOW INDEX FROM orders;这条命令能看到表上所有索引及其详情检查索引是否冗余比如有两个索引共享了同一前缀字段非常方便。定期清理冗余索引能减少写入开销也能避免优化器选择困难。8. 笔记二该掌握到什么程度以及笔记三的方向写到这里这篇基础笔记二的主体知识已经铺完了。你可能会问这些内容掌握到什么程度算合格我的判断标准很简单给你一张空表你能根据业务需求独立设计出字段、类型、索引并且能说清楚每一个设计选择的理由给你一条慢查询你能用EXPLAIN定位问题并且有清晰的优化思路。达到这两条这一篇就算真正消化了。从学习路径上看笔记三我会继续写两块内容一块是 MySQL 的日志体系包括binlog、redo log、undo log各自负责什么为什么说binlog是主从复制和数据恢复的基石另一块是常用的运维级操作比如mysqldump备份恢复、账号权限管理、主从复制的基本配置。这两块内容日常开发不一定天天碰但一旦碰到都是救命级别的知识。如果这篇笔记对你有点用建议照着里面的 SQL 实际操作一遍尤其自己建一张订单表跑一下EXPLAIN体会一下索引生效和不生效的区别。纸上得来终觉浅数据库这个东西亲手踩过的坑记忆是最牢的。祝大家建表顺利少遇故障遇到的故障都能十分钟内定位。