1. 项目概述从零构建MySQL知识体系如果你刚接触数据库或者已经用了一段时间MySQL却总觉得知识零散那今天这篇内容就是为你准备的。我们经常听到DDL、DQL这些缩写面试时也总被问到事务隔离级别、脏读幻读但这些东西到底怎么串起来在实际写代码、调Bug时又该怎么用我把自己这些年从踩坑到填坑的经验梳理了一遍不搞教科书那套理论堆砌就从一个一线开发者的视角带你重新走一遍MySQL的核心知识脉络。你会发现事务隔离级别不是为了考试而是为了解决你明天就可能遇到的并发Bug多表查询也不只是JOIN的语法糖里面藏着性能优化的钥匙。我们不止讲“是什么”更重点拆解“为什么”和“怎么用”目标是让你看完就能形成一个清晰、可用的知识框架直接应用到日常开发里。2. SQL语言分类不只是四种缩写刚开始学SQL时大家都会背DDL、DML、DQL、DCL。但死记硬背很容易忘我的方法是根据它们的“权力”和“目的”来理解。你可以把数据库想象成一个仓库而SQL就是你管理这个仓库的指令集。2.1 DDL仓库的蓝图与结构工程师DDL数据定义语言。它的核心权力是定义和修改数据库本身的结构。执行DDL语句的人就像是仓库的建筑师或结构工程师他关心的是仓库有几层、每层多大、房间怎么隔断、货架怎么摆放。核心语句CREATE,ALTER,DROP,TRUNCATE,RENAME。操作对象数据库、表、视图、索引等“容器”本身。一个关键特性在大多数数据库包括MySQL的InnoDB引擎中DDL操作通常是隐式提交的。这意味着你执行一个ALTER TABLE语句它会立即生效并且会提交你当前未提交的事务。这是一个非常重要的细节很多人在不知情的情况下踩过坑。注意在生产环境执行ALTER TABLE修改大表结构是高风险操作可能导致表锁长时间阻塞读写。现在通常推荐使用pt-online-schema-changePercona Toolkit或GitHub开源的gh-ost等工具进行在线DDL以减少业务影响。2.2 DML仓库的日常运营管理员DML数据操作语言。它的权力范围是仓库里货物数据的日常操作。管理员负责货物的进出、摆放和更新。核心语句INSERT,UPDATE,DELETE。操作对象表里的数据行。核心区别DML操作的是数据内容不改变表结构。它需要显式地使用COMMIT提交或ROLLBACK回滚在自动提交关闭的情况下这为事务控制提供了基础。2.3 DQL仓库的查询与盘点员DQL数据查询语言。虽然理论上它只是DML的一个子集因为SELECT不修改数据但由于其极端重要性和使用频率我们习惯把它单独拎出来。查询员负责根据各种条件从仓库里找到需要的货物信息。唯一但强大的语句SELECT。它的复杂性SELECT语句远不止SELECT * FROM table那么简单。它包含了WHERE条件过滤、JOIN多表关联、GROUP BY分组聚合、HAVING分组后过滤、ORDER BY排序、LIMIT分页等一系列子句是SQL学习的重中之重也是性能优化的主要战场。2.4 DCL仓库的安全与权限总监DCL数据控制语言。它掌管着谁能进仓库、能进哪个区域、能进行什么操作。这是系统管理员或DBA的核心工具。核心语句GRANT授权REVOKE收回权限。操作对象用户及其权限。最佳实践遵循最小权限原则。不要轻易给用户尤其是应用账户ALL PRIVILEGES。通常应用账户只需要特定数据库的SELECT, INSERT, UPDATE, DELETE权限最多加上CREATE TEMPORARY TABLES和EXECUTE存储过程。GRANT OPTION权限更要严格控制。把这四类分清楚你在写SQL时就能更有章法。比如当你需要加一个索引你知道这是DDL范畴要考虑对线上业务的影响当你需要批量更新数据你知道这是DML要放在事务里控制并且想好回滚方案。3. 函数与约束保证数据正确的左右手函数和约束是保证数据质量和简化查询的两种重要工具。它们一个主动函数一个被动约束共同维护着数据的“健康”。3.1 内置函数你的数据处理工具箱MySQL提供了丰富的内置函数避免了你很多重复造轮子的工作。我们可以把它们分类记忆字符串函数处理文本。CONCAT(str1, str2, ...)字符串拼接。注意如果参数中有NULL结果就是NULL可以用CONCAT_WS(separator, str1, str2)用分隔符连接忽略NULL或IFNULL()函数处理。SUBSTRING(str, pos, len)截取子串。数据库下标通常从1开始。REPLACE(str, from_str, to_str)替换字符串。LENGTH()和CHAR_LENGTH()前者返回字节数后者返回字符数。对于中文等多字节字符这两个结果不同这是初学者常混淆的点。数值函数数学计算。ROUND(x, d)四舍五入。注意银行家舍入法不MySQL的ROUND就是常见的“四舍五入”ROUND(2.5)结果是3。CEIL()向上取整FLOOR()向下取整。FORMAT(x, d)格式化数字为#######.##格式返回的是字符串。常用于金额显示。日期时间函数处理时间。NOW(),CURDATE(),CURTIME()获取当前时间。DATE_ADD(date, INTERVAL expr unit)日期加减。比如DATE_ADD(NOW(), INTERVAL 1 DAY)。DATEDIFF(date1, date2)计算两个日期相差的天数date1 - date2。DATE_FORMAT(date, format)格式化日期。SELECT DATE_FORMAT(NOW(), ‘%Y-%m-%d %H:%i:%s’)。流程控制函数实现条件逻辑。IF(condition, value_if_true, value_if_false)简单if-else。CASE WHEN ... THEN ... ELSE ... END强大的多条件分支是SQL中实现复杂逻辑的利器可以用于SELECT字段列表、WHERE条件、ORDER BY等几乎所有地方。实操心得很多函数有性能开销特别是在WHERE条件或JOIN的字段上使用函数如DATE(create_time)会导致索引失效。尽量对常量使用函数而不是对列使用函数。3.2 数据约束定义数据的“法律”约束定义了数据必须遵守的规则由数据库引擎强制执行。它是数据完整性的最后一道防线。非空约束NOT NULL最基本的约束。在设计表时一定要仔细思考每个字段是否真的允许为NULL。NULL值在查询、比较和索引中都有特殊行为会增加复杂度。对于核心业务字段如用户ID、订单号强烈建议设为NOT NULL。唯一约束UNIQUE保证一列或多列组合的值唯一。和主键的区别是唯一约束允许NULL值但通常只能有一个NULL取决于数据库实现MySQL的InnoDB允许多个NULL。它会自动创建一个唯一索引。主键约束PRIMARY KEY特殊的唯一约束不允许NULL且一张表只能有一个。它是表的物理存储顺序在InnoDB中表即索引数据存储在聚簇索引上主键就是聚簇索引的键。主键的选择至关重要推荐使用与业务无关的自增整数BIGINT AUTO_INCREMENT或全局唯一的分布式ID如雪花算法生成的ID。避免使用业务字段如身份证号、手机号或长字符串做主键这会导致聚簇索引频繁分裂影响插入性能。默认约束DEFAULT为列指定默认值。当插入数据未指定该列值时自动填充。对于NOT NULL的字段设置一个合理的默认值如DEFAULT ‘’对于字符串DEFAULT 0对于数字DEFAULT CURRENT_TIMESTAMP对于创建时间是好习惯。检查约束CHECK用于限制列值的范围如age 0。在MySQL 8.0.16之前CHECK约束会被解析但忽略对于InnoDB。从8.0.16开始MySQL才真正支持并强制执行CHECK约束。这是一个重要的版本差异点。外键约束FOREIGN KEY用于强制表与表之间的引用完整性。它要求子表从表中的某个字段值必须在主表主表的对应字段中存在。优点保证数据一致性避免“孤儿记录”。缺点会在每次DML操作时进行引用检查带来额外的性能开销在高并发写入场景可能引发死锁在分库分表或分布式架构中难以使用。个人建议在业务层代码逻辑简单、并发不高、且数据一致性要求极高的核心关联场景如交易流水关联订单可以使用。在互联网高并发业务中更倾向于在业务层通过事务和逻辑来保证一致性而不用数据库外键以获得更好的性能和扩展性。如果使用务必理解ON DELETE和ON UPDATE的级联规则RESTRICT,CASCADE,SET NULL,NO ACTION。4. 多表查询关联的艺术与性能陷阱单表操作是基础但真实业务数据分布在多张表中。多表查询的核心就是JOIN但JOIN用不好就是性能灾难的开始。4.1 JOIN的类型与语义首先要从逻辑上理解每种JOIN到底返回什么数据不要死记维恩图。INNER JOIN内连接返回两个表中连接条件匹配的所有行。这是最常用、最符合直觉的连接。如果A表有10条B表有5条匹配结果最多50条最少0条。LEFT JOIN左外连接返回左表的所有行即使右表中没有匹配。如果右表无匹配则结果集中右表部分全部为NULL。常用于“查询A并附带B的信息即使B可能没有”。RIGHT JOIN右外连接与LEFT JOIN相反返回右表所有行。但实践中通过调整表顺序总能用LEFT JOIN代替所以用得较少。FULL OUTER JOIN全外连接返回左右两表的所有行。当某一行在另一表中无匹配时另一表部分为NULL。MySQL原生不支持FULL JOIN但可以通过LEFT JOIN UNION RIGHT JOIN来模拟。CROSS JOIN交叉连接返回两表的笛卡尔积即左表每一行与右表所有行组合。结果行数 左表行数 * 右表行数。除非明确需要否则很少直接使用。4.2 JOIN的底层原理与性能优化知道怎么写JOIN只是第一步知道数据库怎么执行JOIN才是优化的关键。MySQL主要使用两种算法Nested-Loop Join嵌套循环连接这是最基础的算法。想象两个循环for each row a in table A { for each row b in table B { if (a and b satisfy the join condition) { output (a, b); } } }复杂度是O(M*N)当表很大时极慢。优化如果B表的连接字段有索引那么内层循环就可以从全表扫描变成索引查找复杂度降为O(M * log N)。这就是为什么JOIN条件字段必须建索引的原因。Block Nested-Loop Join块嵌套循环连接如果连接字段没有索引MySQL会使用BNL。它不再一行一行地比而是将外层表驱动表的数据读入一个缓存块join_buffer然后批量与内层表比较。这减少了内层表的扫描次数。性能取决于join_buffer_size的大小。如果驱动表很大仍然会很慢。这是一个明确的性能警告信号如果EXPLAIN结果中出现了Using join buffer (Block Nested Loop)说明连接没有用到索引必须考虑优化。Index Nested-Loop Join索引嵌套循环连接其实就是利用了索引的Nested-Loop Join是效率最高的常见方式。Hash JoinMySQL 8.0.18引入对于等值连接且没有索引可用的情况Hash Join通常比BNL快得多。它会将小表构建表的数据读入内存并为其连接字段建立一个哈希表然后扫描大表探测表用连接字段去哈希表中查找匹配。要利用Hash Join需要确保连接条件是等值比较并且join_buffer足够容纳构建表。优化实战要点永远为JOIN条件、WHERE条件字段建立索引。这是黄金法则。选择正确的驱动表EXPLAIN结果中排在第一行的表就是驱动表。通常应该将数据量小、过滤条件能筛选出更少结果集的表作为驱动表。MySQL优化器通常会帮你做出较好选择但有时也需要你通过STRAIGHT_JOIN来强制指定连接顺序。避免SELECT *只取需要的列减少网络传输和内存占用特别是JOIN时。小心多对多关系的爆炸三张表A JOIN B JOIN C如果关系都是多对多结果集行数可能会爆炸式增长。务必先用子查询或WHERE条件限制各表的结果集大小。4.3 子查询灵活但需谨慎子查询是把一个查询的结果作为另一个查询的条件或数据源。它很灵活但容易导致性能问题。标量子查询返回单个值的子查询可以放在SELECT列表、WHERE条件中。SELECT name, (SELECT dept_name FROM department WHERE id e.dept_id) as dept_name FROM employee e;问题如果外层表有N行这个子查询就会执行N次。当N很大时性能极差。通常可以改写成LEFT JOIN。IN 子查询WHERE id IN (SELECT ...)在MySQL 5.6之前这种查询性能很差。之后版本优化器可能会将其“物化”Materialization或转换为SEMI JOIN性能有所改善但仍需用EXPLAIN查看执行计划。EXISTS 子查询WHERE EXISTS (SELECT 1 FROM ... WHERE ...)它不关心子查询返回什么数据只关心是否存在。通常对于“存在性检查”EXISTS比IN效率更高因为一旦找到一条匹配记录就会返回。核心建议对于关联查询优先考虑使用JOIN。对于复杂的过滤或存在性检查再考虑子查询并一定要用EXPLAIN分析其执行计划。5. 事务数据库的“原子操作”单元事务是现代数据库的基石。它把一系列操作打包成一个不可分割的单元要么全部成功要么全部失败。最经典的例子就是银行转账A账户扣款和B账户加款必须同时成功或同时失败。5.1 事务的四大特性ACID原子性Atomicity事务是最小工作单元不可再分。由UNDO LOG保证用于回滚。一致性Consistency事务执行前后数据库都必须处于一致性状态。比如转账前后两个账户总额不变。这是由应用逻辑和数据库约束共同保证的最终目标。隔离性Isolation多个并发事务之间互不干扰。这是并发控制的重点也是下文要详细讨论的。持久性Durability事务一旦提交其对数据的修改就是永久性的即使系统故障也不会丢失。由REDO LOG保证。在MySQL中默认是自动提交模式autocommit1每条SQL语句都是一个独立的事务。要手动控制事务需要SET autocommit 0; -- 关闭自动提交 START TRANSACTION; -- 或 BEGIN -- 你的DML操作... COMMIT; -- 提交 -- 或 ROLLBACK; -- 回滚 SET autocommit 1; -- 恢复自动提交5.2 并发事务可能引发的四大问题当多个事务同时操作同一份数据时如果没有任何隔离措施就会产生问题。理解这些问题是理解隔离级别的前提。脏写Dirty Write一个事务修改了另一个未提交事务修改过的数据。场景事务A将行R的值从10改为20未提交。事务B又将同一行R的值从20改为30然后提交。随后事务A回滚将R的值恢复为10。这就导致了事务B的写入“丢失”了因为它基于了一个从未正式存在过的中间状态20。严重性这是最严重的问题所有数据库的隔离级别都必须防止脏写。通常通过行级锁写锁X锁来实现一个事务对某行加X锁后其他事务无法再对其加X锁。脏读Dirty Read一个事务读到了另一个未提交事务修改的数据。场景事务A将余额从100改为200未提交。事务B读取余额得到了200。然后事务A回滚余额变回100。事务B读到的就是一个根本不存在的“脏数据”。影响导致业务逻辑判断错误。比如基于脏数据做了后续操作。不可重复读Non-Repeatable Read在同一个事务内两次读取同一行数据结果不一样因为别的事务修改并提交了这行数据。场景事务A第一次读取余额为100。此时事务B将余额更新为200并提交。事务A再次读取余额发现变成了200。两次读取结果不一致。与脏读的区别脏读是读到了未提交的数据不可重复读是读到了其他事务已提交的修改。幻读Phantom Read在同一个事务内两次执行相同的查询返回的记录行数不一样因为别的事务插入或删除了数据并提交。场景事务A查询年龄小于30的用户有10人。此时事务B插入了一个年龄25的新用户并提交。事务A再次查询发现变成了11人。就像出现了“幻觉”一样。与不可重复读的区别不可重复读针对的是同一行数据的值被修改幻读针对的是结果集的行数发生变化新增或删除行。5.3 事务隔离级别在性能与正确性间的权衡为了解决上述并发问题SQL标准定义了4种隔离级别隔离级别越高数据一致性越强但并发性能越低。MySQL的InnoDB引擎支持全部四种级别。隔离级别脏读不可重复读幻读实现机制简述读未提交❌ 可能❌ 可能❌ 可能几乎不加锁性能最高但问题最多。读已提交✅ 避免❌ 可能❌ 可能每次SELECT都生成一个快照ReadView只能读到已提交的数据。可重复读✅ 避免✅ 避免❌ 可能InnoDB已解决在事务第一次SELECT时生成快照整个事务都使用这个快照。串行化✅ 避免✅ 避免✅ 避免所有操作加锁强制事务串行执行性能最低。读未提交READ UNCOMMITTED基本不用除非你能容忍所有数据问题只追求极致读取速度如某些实时性要求极高但准确性要求不高的监控场景。读已提交READ COMMITTED这是Oracle等数据库的默认级别。它解决了脏读问题。但在同一个事务中两次相同的查询可能得到不同的结果不可重复读和幻读。实现上它使用“语句级快照”每条SELECT语句执行时都会去看当前已提交的最新数据。可重复读REPEATABLE READ这是MySQL InnoDB引擎的默认隔离级别。它解决了脏读和不可重复读。并且InnoDB通过“间隙锁”Next-Key Lock的机制在这个级别下也解决了幻读问题这是MySQL对标准的增强。它使用“事务级快照”在事务开始后的第一次读操作时建立一致性视图之后都基于这个视图读取保证了可重复读。串行化SERIALIZABLE通过强制事务串行执行来解决所有问题。它会对所有读取的行也加锁共享锁导致大量的锁竞争和超时性能很差只在极端要求一致性的场景下使用。如何设置和查看隔离级别-- 查看当前会话隔离级别 SELECT transaction_isolation; -- 查看全局隔离级别 SELECT global.transaction_isolation; -- 设置当前会话隔离级别 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 设置全局隔离级别需重启或新会话生效 SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;5.4 InnoDB如何实现可重复读与防止幻读这是面试高频点也是理解InnoDB并发控制的核心。MVCC多版本并发控制这是实现“读已提交”和“可重复读”的基石。InnoDB每行数据都有两个隐藏字段trx_id最近修改它的事务ID和roll_pointer指向UNDO LOG中旧版本数据的指针。当一个事务开始时它会生成一个活跃事务ID列表ReadView。对于每行数据根据其trx_id与当前事务ReadView的对比规则来决定当前事务能看到哪个版本的数据可能是当前最新版本也可能是UNDO LOG里的一个历史版本。这就实现了“快照读”读操作不用加锁性能高。Next-Key Lock临键锁这是InnoDB防止幻读的武器。它不是一个新锁而是记录锁Lock on a Record和间隙锁Gap Lock的组合。记录锁锁住索引上的一条具体记录。间隙锁锁住索引记录之间的间隙防止在这个范围内插入新记录。临键锁 记录锁 该记录之前的间隙锁。它锁住一个左开右闭的区间。例如索引有值10 20 30。对20加临键锁会锁住(10 20]这个区间。这意味着其他事务无法在这个区间内插入新记录比如15从而防止了幻读。一个典型的幻读防止场景 事务ASELECT * FROM users WHERE age 20 FOR UPDATE;当前没有age20的记录 事务B尝试INSERT INTO users (age) VALUES (25);--这个操作会被阻塞因为事务A的SELECT ... FOR UPDATE会对age 20这个条件所涉及的最大值之后的间隙假设是正无穷加上间隙锁阻止了事务B的插入从而避免了幻读。重要提示普通的SELECT快照读在“可重复读”级别下不会加锁也不会阻塞其他事务的插入因此从“看到的数据”角度可能依然会因其他事务提交而看到新的行如果该行是在本事务开始后提交的且满足查询条件。但通过SELECT ... FOR UPDATE或SELECT ... LOCK IN SHARE MODE进行“当前读”时InnoDB就会通过Next-Key Lock来防止幻读。所以严格来说InnoDB的RR级别通过“当前读Next-Key Lock”解决了幻读而“快照读”本身由于MVCC的存在不会看到“幻影行”。6. 实战一个完整的事务与查询案例让我们通过一个模拟的电商场景把事务、隔离级别和多表查询串起来。假设有两张表orders订单和order_details订单详情。-- 创建表 CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(32) UNIQUE NOT NULL, user_id BIGINT NOT NULL, total_amount DECIMAL(10, 2) NOT NULL DEFAULT 0.00, status TINYINT NOT NULL DEFAULT 1 COMMENT 1待支付2已支付3已发货4已完成, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_user_id (user_id), INDEX idx_create_time (create_time) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE order_details ( id BIGINT PRIMARY KEY AUTO_INCREMENT, order_id BIGINT NOT NULL, product_id BIGINT NOT NULL, quantity INT NOT NULL, price DECIMAL(10, 2) NOT NULL, FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE, INDEX idx_order_id (order_id), INDEX idx_product_id (product_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;场景用户支付订单这个操作需要原子性地完成1. 检查订单状态2. 更新订单状态为“已支付”3. 记录支付流水假设在另一张表此处简化。我们必须用事务包裹。-- 会话 A支付事务 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 根据业务需要设置 START TRANSACTION; -- 1. 查询订单状态当前读加锁防止其他事务修改 SELECT status FROM orders WHERE order_no ‘202405200001’ FOR UPDATE; -- 假设查询到 status 1 (待支付) -- 2. 更新订单状态 UPDATE orders SET status 2 WHERE order_no ‘202405200001’; -- 3. 模拟插入支付流水略 -- 在提交前另一个会话...-- 会话 B同时尝试查询这个订单 START TRANSACTION; -- 在“读已提交”级别下这个SELECT会看到最新的已提交数据。 -- 因为会话A还未提交所以它看不到status2看到的还是status1避免了脏读。 -- 在“可重复读”级别下这个SELECT看到的是事务开始时的快照也是status1。 SELECT status FROM orders WHERE order_no ‘202405200001’; -- 会话B尝试修改这个订单比如取消订单 UPDATE orders SET status 5 WHERE order_no ‘202405200001’; -- 这个UPDATE会被阻塞因为会话A的 SELECT ... FOR UPDATE 已经对该行加了排他锁(X锁)。 -- 这就避免了脏写和更新丢失。-- 回到会话 A COMMIT; -- 提交事务 -- 会话A提交后锁释放。 -- 此时在“读已提交”级别下的会话B如果再次执行SELECT就会看到status2出现了不可重复读。 -- 而在“可重复读”级别下的会话B再次执行SELECT看到的仍然是status1保证了可重复读。结合多表查询的统计场景我们需要统计每个用户的订单总金额和订单数。-- 低效写法在应用程序里循环查询 -- 高效写法一条SQL完成 SELECT o.user_id, u.username, -- 假设有users表 COUNT(o.id) as order_count, SUM(o.total_amount) as total_spent, -- 使用子查询或JOIN获取最近一笔订单号演示CASE WHEN MAX(CASE WHEN o.create_time latest.latest_time THEN o.order_no ELSE NULL END) as latest_order_no FROM orders o INNER JOIN users u ON o.user_id u.id LEFT JOIN ( SELECT user_id, MAX(create_time) as latest_time FROM orders GROUP BY user_id ) latest ON o.user_id latest.user_id WHERE o.status 4 -- 只统计已完成的订单 GROUP BY o.user_id, u.username -- 确保GROUP BY的列是SELECT中非聚合列 HAVING total_spent 1000 -- 过滤消费总额大于1000的用户 ORDER BY total_spent DESC LIMIT 10;这个查询的优化点确保o.user_id,o.status,o.create_time,u.id上有索引。使用INNER JOIN确保只查询有订单的用户。使用派生表子查询latest先聚合出每个用户的最新订单时间避免在主查询中进行复杂的窗口函数计算如果MySQL版本支持窗口函数ROW_NUMBER()那会是更好的选择。WHERE在GROUP BY之前过滤减少聚合的数据量。HAVING在聚合后过滤。最后的ORDER BY和LIMIT利用了索引排序。7. 常见问题排查与经验总结在实际使用中你会遇到各种各样的问题。这里记录几个最典型的。7.1 慢查询如何定位和优化开启慢查询日志这是最直接的方法。在my.cnf中配置slow_query_log 1 slow_query_log_file /var/log/mysql/slow.log long_query_time 2 # 超过2秒的查询被记录 log_queries_not_using_indexes 1 # 记录未使用索引的查询使用EXPLAIN分析对任何慢查询第一反应就是EXPLAIN。关注以下列type访问类型从好到坏system const eq_ref ref range index ALL。至少要到range避免ALL全表扫描。key实际使用的索引。rows预估需要扫描的行数。Extra额外信息。出现Using filesort文件排序或Using temporary使用临时表通常需要优化。经典优化手段索引失效WHERE中对字段使用函数、表达式、类型转换、OR条件连接除非每个OR条件都有索引、LIKE ‘%xxx’前置百分号。最左前缀原则联合索引(a, b, c)查询条件必须包含a才能用到这个索引。WHERE b1 AND c2用不到这个索引。避免SELECT *。优化JOIN确保驱动表是小表被驱动表连接字段有索引。分页优化LIMIT 100000 10这种深度分页极慢。可以改用WHERE id 上一页最大ID LIMIT 10或者使用覆盖索引子查询。7.2 死锁成因与解决死锁就是两个或以上事务互相等待对方释放锁。InnoDB会自动检测死锁并回滚其中一个事务代价较小的事务。常见死锁场景不同顺序加锁事务A先锁行1再锁行2事务B先锁行2再锁行1。解决在业务代码中约定对多个资源的加锁顺序始终保持一致例如按ID升序加锁。间隙锁冲突两个事务同时向同一个间隙插入数据而该间隙被对方的间隙锁锁住。解决如果业务允许可以降低隔离级别到“读已提交”它不会加间隙锁但会引入幻读风险。或者尽量使用唯一索引减少间隙锁的范围。排查死锁查看SHOW ENGINE INNODB STATUS\G命令输出中的LATEST DETECTED DEADLOCK部分里面有导致死锁的最后一个事务的详细信息、执行的SQL和持有的锁。7.3 事务未提交导致连接池耗尽这是一个常见的线上事故模式。一个业务逻辑开启了事务或关闭了自动提交进行了查询但因为逻辑复杂或异常没有及时COMMIT或ROLLBACK。这个数据库连接就一直持有锁和资源不归还到连接池。当这样的请求多起来连接池很快被占满新的请求无法获取连接服务雪崩。预防使用框架的事务管理如Spring的Transactional并设置合适的超时时间timeout。在代码中确保事务在try-catch-finally块的finally中执行回滚或提交。监控数据库的SHOW PROCESSLIST关注长时间处于Sleep或Locked状态的事务。7.4 大字段更新导致Binlog暴增当你更新一个包含TEXT或BLOB大字段的表时即使只修改了一小部分在“行格式”为ROW的情况下整个行的新镜像都会被写入Binlog用于主从复制和数据恢复。如果这个字段很大比如几MB的文本频繁更新会导致Binlog文件飞速增长占满磁盘。解决将大字段拆分到单独的扩展表中主表只存引用ID。使用binlog_row_image MINIMALMySQL 5.6配置这样Binlog只记录被修改的列而不是整行。但需要注意这可能会在某些特殊的数据恢复场景下带来复杂性。数据库的学习是一个持续的过程从会写SQL到写好SQL从知道事务到精通并发控制中间隔着无数个坑。最好的学习方法就是结合理论去实践在真实的业务场景中遇到问题、分析问题、解决问题。每次慢查询优化每次死锁分析都会让你对MySQL的理解更深一层。记住数据库设计没有银弹所有的选择都是在一致性、性能、复杂度之间做权衡。理解这些底层原理就是为了让你能做出更明智的权衡。