MySQL 学习日记写到第九天今天想重点聊三个词存储引擎、索引、触发器。前几天一直在折腾建表、查询、连表、事务这些偏日常操作的东西今天终于要进入 MySQL 性能的核心地带了。我的体会是这三块内容单拎出来看每一块都能写一本书但如果只背结论不追原理用不到真实项目里。所以我尽量把它们放在同一条主线上来讲一条 SQL 进来以后存储引擎决定数据怎么落盘、怎么锁、怎么恢复索引决定数据定位快不快触发器决定数据发生变化时数据库会不会自动替你干活。适合正在从增删改查往性能调优方向进阶的开发者也适合准备系统梳理 MySQL 知识体系的朋友。先交代一下实验环境MySQL 8.0 社区版单机测试库默认引擎就是 InnoDB。后面所有建表语句、查询样例和报错信息都在这个环境里真实跑过。文中案例为了演示方便做了简化但底层机制是通用的。1. 存储引擎先搞明白“引擎”到底在管什么1.1 一张表的背后其实藏着好几类文件刚学 MySQL 的时候我一直以为“表”就是一个抽象概念数据放在数据库里就行。直到有一次我手贱去翻 MySQL 的 data 目录才发现事情没那么简单。在默认配置下InnoDB 每张表的数据和索引会放到一个独立的.ibd文件里表结构定义则交给 MySQL 8.0 的数据字典统一管理。而 MyISAM 引擎的表数据文件和索引文件是分开的.MYD存数据.MYI存索引。这就能看出一点端倪不同引擎对“如何组织数据文件”的答案完全不同而这也正是它们性能、功能差异的根源。存储引擎说白了就是 MySQL 里负责存储、读取、加锁、事务、恢复这些底层事情的组件。它不是你平时写 SQL 时能直接触碰到的东西但它决定了你每条 SQL 的体验上限。InnoDB 和 MyISAM 是大家最常听到的两个但 MySQL 还内置了 MEMORY、ARCHIVE、CSV、BLACKHOLE 等引擎各有各的脾气。1.2 InnoDB 和 MyISAM 的真实差距在哪我最早对这两个引擎的印象来自教程里的结论InnoDB 支持事务MyISAM 不支持。后来真正使用才发现这只是冰山一角两者差异远比“事务有无”更大。用一个我经常举的例子说明。你在某电商平台下单余额扣减和订单生成必须同时成功或者同时失败这就是事务。MyISAM 不支持事务意味着如果一半操作完成、一半失败数据就处于中间状态而且很难自动回滚。InnoDB 有 redo log 和 undo log 支撑崩溃了也能恢复这是它成为默认引擎最重要的原因之一。再往下是锁粒度。MyISAM 默认是表级锁写的时候整张表都不能被其他会话读写InnoDB 支持行级锁删除或更新某一行时只有那一行被锁其他行照常访问。同样是高并发写入场景这两个引擎的表现天差地别。我把平时对比用的一张表放在这里维度InnoDBMyISAM事务支持支持 ACID不支持锁粒度行锁也支持表锁表锁外键支持不支持崩溃恢复通过 redo log 恢复易损坏需修复聚簇索引是否MVCC 多版本并发控制支持不支持全文索引8.0 支持支持典型场景核心业务、高并发读写只读报表、历史归档这里尤其想说一下 MVCC。我第一次听到“多版本并发控制”这个词是完全懵的后来用生活化方式理解就好多了它就像图书馆的同一本书有很多副本读者各看各的副本互不干扰。InnoDB 通过 undo log 维护数据的历史版本让读操作不阻塞写操作、写操作不阻塞读操作这也是它高并发能力的重要来源。MyISAM 没有这套机制读写会互相排队。1.3 如何查看当前表用的什么引擎看完差异最直接的问题是我现在这张表到底是什么引擎掌握下面几个查询方法就够了。-- 方法一建表语句 SHOW CREATE TABLE user\G -- 方法二表状态 SHOW TABLE STATUS LIKE user\G -- 方法三视图查询最灵活 SELECT table_name, engine, table_rows FROM information_schema.TABLES WHERE table_schema test_db;如果确认要换引擎一条 ALTER 就能搞定ALTER TABLE user ENGINE InnoDB;但我个人强烈不建议在生产环境直接执行这条语句。原因很简单ALTER TABLE 换引擎往往意味着重建表大表会锁住很久线上业务大概率会出问题。真要换得先评估表数据量、业务窗口最好用在线变更工具一步步来。1.4 其他引擎要不要学InnoDB 之外我建议至少知道 MEMORY 和 ARCHIVE 两个。MEMORY 引擎把数据全放内存读写极快但服务重启数据就没了适合做临时缓存或中间结果而且通常要主动限制表大小否则内存容易爆。ARCHIVE 引擎只支持 INSERT 和 SELECT主打高压缩、顺序读取适合归档不修改的历史日志。说实在的绝大多数业务表用 InnoDB 就够了其他引擎更多是面试题里出现或者极特殊的场景才用得上不用花太多精力深挖。这里插一句个人心得判断引擎是否合适不要只看引擎特性还要看业务模型。比如一个每秒写入几千条、极少更新的评论流水表InnoDB 依然是最稳妥的因为单条写入也涉及并发、崩溃恢复等隐性问题省那点空间不值得。2. 索引为什么 BTree 能扛住千万级数据2.1 没有索引时 MySQL 是怎么找数据的讨论索引前先看一个反直觉的事实没有索引的时候MySQL 查一条数据最老实的办法是“从头翻到尾”。这就是全表扫描。数据量小比如几千行问题不大但到几百万行甚至上千万行一次查询就可能把磁盘 IO 拉满接口超时、数据库 CPU 飙高接踵而来。我自己实际碰到过一个慢查询案例。某内部系统的订单表有 800 多万行查询条件是WHERE order_status 1这条 SQL 在测试环境秒出上了生产环境后直逼好几秒。原因就是该字段没有索引MySQL 只能逐行扫。加了一个普通索引之后查询直接降到几十毫秒级别。这个对比给我的冲击很大也让我开始认真研究索引的原理。2.2 BTree 到底比普通树强在哪索引最常见的物理结构是 BTree。我一开始也困惑二叉树学过BTree 是什么东西其实可以这样理解BTree 是多叉平衡树每个节点可以存很多个“键值 指针”不像二叉树那样一个节点只存一个键值。因为每个节点恰好占用一个数据页扇出很高所以树的高度很矮。InnoDB 的数据页默认 16KB按主键 8 字节、指针 6 字节粗算一个节点大概能存上千个键值三层到四层的 BTree 就能撑起上千万甚至上亿条记录。这就是为什么索引表很大却依然很快的秘密几万倍的搜索范围最终只走三四个层级就能定位到叶子节点。再加上叶子节点之间用链表串起来范围查询BETWEEN、ORDER BY、也就不需要全表扫只需要顺着链表往后走。BTree 对比普通 B-Tree 还有一个关键优化非叶子节点只存索引键和指针不存数据这样单页能容纳更多键把树压得更矮数据全部集中在叶子节点天然维护了有序性。至于为什么不用哈希索引哈希索引等值查询确实快但一旦你做范围查询或者按前缀排序它就无能为力了所以 InnoDB 默认的索引结构还是 BTree。InnoDB 内部也有自适应哈希索引但那是引擎自管理的东西不需要我们干预。2.3 聚簇索引、二级索引和覆盖索引在 InnoDB 里索引这个概念还要再细分否则后面看执行计划会一头雾水。表的主键索引叫聚簇索引它直接决定数据行在磁盘上的物理存储顺序叶子节点存的是整行数据。其他索引普通索引、唯一索引、联合索引叫二级索引叶子节点存的是主键值。这就带来一个机制通过二级索引查询时先找到主键值再拿主键回聚簇索引里查整行这个过程称作“回表”。回表不是必然发生的。如果你的查询列恰好全部包含在索引本身里MySQL 可以直接从索引拿到所有需要的字段连表都不用回这种索引就是覆盖索引。我写查询优化经验时有一条铁律优先设计覆盖索引让 SQL 只走一棵索引树能省掉大量随机 IO。比如在订单表上建(order_status, created_at)联合索引然后查询只取这两个字段就很可能实现覆盖索引扫描。2.4 联合索引的最左前缀原则联合索引是面试和实战的重灾区。简单说如果你在(a, b, c)三个字段上建了联合索引MySQL 会先按 a 排序a 相同再按 bb 相同再按 c。所以它能加速的查询条件是a、a b、a b c这几类组合但如果你直接查b或者c或者查询条件跳过 a 直接查b c索引就帮不上忙。这就是最左前缀原则。这里有一个常见的理解误区联合索引的字段顺序不能乱写不一定是最常用的放最左。比如你经常用WHERE user_id ? AND created_at ?查某个用户的近期订单那么(user_id, created_at)就比(created_at, user_id)合理因为先通过 user_id 精确过滤再在范围内扫日期。如果反着建created_at 作为第一个字段那这个查询无法快速定位到某个用户的范围效果大打折扣。建索引前先想清楚业务查询的条件形态比盲目把所有字段都加索引重要得多。2.5 索引失效的经典场景我全踩过说完了原理说说索引失效。以下场景我基本都在线上慢查询里见过列出来方便自查对索引列使用函数。WHERE DATE(create_time) 2024-01-01就是对 create_time 套了函数索引直接失效应该写成WHERE create_time 2024-01-01 AND create_time 2024-01-02。隐式类型转换。字段是字符类型查询却传了数字MySQL 可能对字段做了隐式转换索引用不上。左模糊匹配。LIKE %abc必须以通配符开关BTree 无法利用前缀有序性LIKE abc%反而能用索引。OR连接条件。WHERE a 1 OR b 2如果 b 上没有索引优化器可能放弃索引改成全表扫。NOT IN、!这类负向条件在部分场景下也容易导致索引利用率下降。每次遇到慢查询我的习惯是先拿EXPLAIN看一眼执行计划重点看key列是不是确实用上了索引type列是ref还是ALL。ALL就是全表扫描基本可以断定索引没走对。EXPLAIN 是排查索引问题的第一工具没有之一。3. 触发器数据库自动执行的“暗器”3.1 触发器是什么能干什么触发器是挂在某张表上的特殊程序。当这张表发生 INSERT、UPDATE、DELETE 时数据库自动去执行触发器里定义的那段逻辑不需要应用层显式调用。它有点像你给手机设置的自动化流程一到公司自动打开工作模式。平时你可能感觉不到它的存在但条件触发那一刻它确实在后台干活。第一次接触触发器我脑子里全是问号它和普通存储过程有啥区别区别其实很明确存储过程是主动调的触发器是被动触发的而且它无法被直接调用只能由表的增删改事件激活。触发器的核心机制包括两个时间和两个特殊变量BEFORE / AFTER在操作之前还是之后触发。NEW / OLDINSERT 时用 NEW 表示新插入的行UPDATE 时 OLD 和 NEW 都有DELETE 时用 OLD 表示被删除的行。3.2 创建触发器的标准语法来看一个最典型的触发器给订单表增加操作审计日志。假设我们有订单表orders和审计表order_logs结构大致如下CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(64), amount DECIMAL(10,2), status INT, created_at DATETIME ) ENGINE InnoDB; CREATE TABLE order_logs ( id INT PRIMARY KEY AUTO_INCREMENT, order_id INT, action VARCHAR(20), log_time DATETIME ) ENGINE InnoDB;现在想在每次向 orders 插入一条记录后自动往 order_logs 写一条日志可以这样建触发器DELIMITER // CREATE TRIGGER trg_orders_after_insert AFTER INSERT ON orders FOR EACH ROW BEGIN INSERT INTO order_logs(order_id, action, log_time) VALUES (NEW.id, INSERT, NOW()); END // DELIMITER ;几个细节得认真说。DELIMITER //是终端里写触发器必须做的因为触发器体内有多条 SQL分号会被 mysql 客户端当成语句结束符不切换分隔符脚本根本执行不进去。AFTER INSERT决定了触发时机。如果要校验数据合法性比如插入前检查某个字段不能为空就得用BEFORE INSERT这时可以提前改NEW里的字段值起到类似“清洗数据”的作用。FOR EACH ROW表示行级触发每影响一行触发一次。MySQL 不支持语句级触发器这一点和某些其他数据库不太一样。批量插入一万行这个触发器就会执行一万次性能和隐性问题由此而来后面细说。写完以后验证一下-- 插入一条订单 INSERT INTO orders(order_no, amount, status, created_at) VALUES (NO2024001, 88.50, 0, NOW()); -- 查看审计日志 SELECT * FROM order_logs;只要触发器建对order_logs 里就会自动多出对应记录整个过程应用层完全无感知。3.3 触发器最常见的三类用法触发器不是炫技用的它在真实项目里最常见的使用场景我总结为三类。第一类是审计与留痕。数据被谁修改了、改成了什么、什么时候改的这类“水下”信息非常适合用触发器记录特别是那些绕过应用层直接改数据库的运维操作也能被捕获到。比如通过SHOW TRIGGERS配合审计表能追溯很多说不清的数据变更。第二类是冗余字段或汇总字段的自动维护。比如订单表里保存了order_count这种冗余统计字段可以在订单表每次 INSERT 后由触发器自动去汇总表里加一。这样应用层不用写两遍逻辑数据也相对一致。但我必须说一句这类用法要非常谨慎因为汇总表会变成锁与性能的热点后面我会讲为什么。第三类是跨表的数据同步或状态联动。主表状态变化了自动在关联表里插入一条待处理记录或者自动更新关联记录的失效时间。这在某些内部系统里比较常见前提是逻辑足够简单、触发频率不高。4. 触发器用起来简单但坑是真的多4.1 最大的坑业绩逻辑不透明我刚开始觉得触发器简直是神器省了多少应用层代码。直到某次排查一个“诡异”的数据变化某内部系统里一条员工记录明明没人改状态却变了。查了半天最后发现是表上挂着一个 UPDATE 触发器它又偷偷改了另一张表。这种“暗箭”式的逻辑对维护方来说极不友好。新来的同事查数据变更是不会想到去查 trigger 的除非他手动执行SHOW TRIGGERS。所以我的第一条建议是触发器逻辑一定要极其克制最好永远放在“你今天不写明天也完全不会出事”的边界内。跨表协同这种偏业务的事情宁可放在应用层代码里用事务管理至少能被日志追踪、能被代码评审看到。数据库层自动执行的逻辑越少线上排障越轻松。4.2 性能是第二坑行级触发器会被批量操作放大性能问题同样在实战中吃过亏。行级触发器的代价是逐行执行悲观地想一行 SQL 影响十万行触发器也跟着执行十万次。尤其当你写了一个“更新完之后再去更新汇总表”的触发器每行更新都伴随对同一行汇总记录的争夺锁竞争瞬间被放大数据库慢得惨不忍睹。我在实验里跑过一万行的 UPDATE带触发器和不带触发器的耗时差距非常明显这还只是单机低并发下的结果。如果业务确实需要汇总数据更稳的方案是不做实时汇总改成定时任务分批统计或者由应用层在事务里明确地更新汇总表。始终记住一句话触发器适合做轻量、低频、无需访问其他大表的动作不适合做重型业务逻辑。4.3 触发器里最常见的报错和排查方法我在实验里故意踩了一个经典报错在同一张表的 AFTER INSERT 触发器里再去 UPDATE 这张表自己结果 MySQL 直接报错返回错误码 1442原因是触发器所操作的表已经被正在执行的语句占用了不允许再修改。这意味着“在触发器里修改触发它的同一张表”这条路是走不通的想修正只能改 BEFORE 时机去调整 NEW 字段或者彻底换方案。排查触发器状态我有三个常用方法-- 查看所有触发器 SHOW TRIGGERS\G -- 从系统表里精确查询某个表上的触发器 SELECT trigger_name, event_object_table, action_timing, event_manipulation FROM information_schema.TRIGGERS WHERE event_object_table orders; -- 删除触发器 DROP TRIGGER IF EXISTS trg_orders_after_insert;删除触发器时要注意DROP TRIGGER需要操作表上的 TRIGGER 权限并且不能像 DROP TABLE 那样带IF EXISTS在所有版本都通用最好先确认存在再删避免报错。版本差异、大小写敏感这些细节进到不同环境时都要重新确认一遍。4.4 触发器维护的实践经验汇总整理几条经过事实验证的触发器维护经验触发器内不要写过多业务判断逻辑越短越好尽量只做“数据记录”级别的轻操作。触发器多的时候给每个触发器起名带业务缩写比如trg_orders_after_ins否则SHOW TRIGGERS列出来一堆名字你根本不知道它们是干嘛的。使用触发器前先确认应用代码里是否有相同逻辑避免两边同时维护一套逻辑最后出现“应用层更新了触发器没更新”的扯皮情况。代码评审时把触发器纳入评审范围毕竟它也是业务逻辑的一部分不能写在不可见的角落里。如果你确实需要更复杂的触发行为例如基于行数据动态拼接 SQLMySQL 触发器的能力很有限动态 SQL 在触发器里基本不可用这种需求应放到存储过程或应用层处理。5. 今日实验记录一条主线上跑通三个知识点今天的实验我特意设计了一个小场景来串联存储引擎、索引和触发器而不是零散地各玩各的。场景是某内部系统的订单模块我建了订单表和订单日志表做了下面几件事第一件事是更换引擎看区别。我在小表上把存储引擎从 InnoDB 换成 MyISAM再测同一索引条件下等值查询的速度同时测并发更新时的锁表现。结论和预期一致只读查询两者差距不大但只要有两个会话同时更新同一张 MyISAM 表表锁立刻导致另一个会话排队而 InnoDB 行锁下另一会话更新不同行可以并行。亲手看到阻塞效果比任何文档都更有说服力。第二件事是验证 BTree 索引的实际收益。我往测试表里插入了几十万行数据先在无索引情况下执行WHERE条件查询通过EXPLAIN看到 type 是 ALL再在查询列上建索引重新执行后 type 变成 ref耗时也从秒级降到毫秒级。这组对比我一直建议所有新手亲自做一次做完你对索引的价值理解会彻底改观。我还顺带测试了LIKE %xxx%和DATE(create_time)这两种索引失效写法执行计划里 key 直接变 NULL非常一目了然。第三件事是触发器联动。我在订单表上建了 AFTER INSERT 触发器自动往日志表写审计记录然后在命令行插入一条订单数据果然自动落进日志表。随后我故意尝试在 AFTER DELETE 触发器里去 DELETE 同表的另一条记录触发了 MySQL 的 1442 报错正好确认了“触发器里不能改同表”这个硬性规则。整个过程把今天的三个知识点串成了一条完整链路数据存在 InnoDB 里查询依赖索引提速写入后的自动动作交给触发器。最后再分享一个今天整理出来的心得存储引擎是地基索引是加速器触发器是自动化阀门这三个概念在实际系统中不是孤立存在的。你做表设计时选了 InnoDB那么后续的索引设计、行锁行为、崩溃恢复能力就都基于这个前提索引设计又直接影响查询性能而触发器如果写得不克制又会反过来增加写路径的锁等待和 IO 压力。所以学 MySQL 到进阶阶段最值得养成的思维习惯是看到一个优化点先想清楚它处在整条数据链路的哪个环节牵一发动全身的代价在哪里。这也是我写学习日记以来最大的收获。