一、ref - 索引引用 1.1 含义 显示索引查找使用的列或常量。 -- 常量查询 EXPLAIN SELECT * FROM users WHERE id = 10; -- ref: const -- JOIN查询 EXPLAIN SELECT * FROM orders o JOIN users u ON o.user_id = u.id; -- ref: database.o.user_id（orders表的user_id列） 二、rows - 扫描行数 2.1 预估行数 EXPLAIN SELECT * FROM users WHERE name = '张三'; -- rows: 100（预估扫描100行） 注意：是估算值，不是精确值。 2.2 优化目标 rows越小越好 - ✅ rows < 100：良好 - ⚠️ rows < 10000：可接受 - ❌ rows > 100000：需优化 三、filtered - 过滤百分比 3.1 含义 经过WHERE过滤后，剩余记录的百分比。 EXPLAIN SELECT * FROM users WHERE name = '张三' AND age = 20; -- rows: 1000 -- filtered: 10% -- 最终：1000 * 10% = 100行 3.2 计算 实际返回行数 ≈ rows * (filtered / 100) 四、Extra - 额外信息（重要） 4.1 Using index（覆盖索引） EXPLAIN SELECT id, name FROM users WHERE name = '张三'; -- Extra: Using index（覆盖索引，最优） 含义：只需扫描索引，无需回表。 ...

一、什么是慢查询日志 1.1 定义 记录执行时间超过阈值的SQL语句。 1.2 作用 定位慢SQL 分析性能瓶颈 优化数据库性能 二、配置慢查询日志 2.1 查看当前配置 -- 是否启用 SHOW VARIABLES LIKE 'slow_query_log'; -- OFF（未启用） -- 慢查询阈值 SHOW VARIABLES LIKE 'long_query_time'; -- 10（默认10秒） -- 日志文件路径 SHOW VARIABLES LIKE 'slow_query_log_file'; 2.2 启用慢查询日志 -- 方式1：临时启用（重启失效） SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 2; -- 2秒 -- 方式2：配置文件永久启用 -- my.cnf [mysqld] slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow.log long_query_time = 2 log_queries_not_using_indexes = 1 -- 记录未使用索引的查询 2.3 重启生效 systemctl restart mysqld 三、慢查询日志格式 3.1 日志示例 # Time: 2024-11-21T22:30:00.123456Z # User@Host: root[root] @ localhost [] # Query_time: 5.123456 Lock_time: 0.000123 Rows_sent: 100 Rows_examined: 1000000 SET timestamp=1700601000; SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at; 3.2 关键字段 Query_time：查询执行时间（秒） Lock_time：锁等待时间 Rows_sent：返回行数 Rows_examined：扫描行数 四、分析慢查询日志 4.1 查看慢查询日志 tail -f /var/log/mysql/slow.log 4.2 mysqldumpslow工具 # 查看出现次数最多的10条慢SQL mysqldumpslow -s c -t 10 /var/log/mysql/slow.log # 查看执行时间最长的10条慢SQL mysqldumpslow -s t -t 10 /var/log/mysql/slow.log # 查看返回记录最多的10条慢SQL mysqldumpslow -s r -t 10 /var/log/mysql/slow.log # 查看锁等待时间最长的10条慢SQL mysqldumpslow -s l -t 10 /var/log/mysql/slow.log 参数说明： ...

引言 本文通过5个真实案例，综合运用索引优化知识，展示完整的优化过程。 案例1：订单列表查询优化 1.1 问题 -- 慢SQL SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 20; -- 执行时间：5秒 1.2 分析 EXPLAIN SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 20; 结果： type: ref key: idx_user_id rows: 50000 Extra: Using filesort ← 文件排序 问题： 扫描5万行 需要额外排序（filesort） 1.3 优化 -- 创建联合索引 CREATE INDEX idx_user_created ON orders(user_id, created_at); -- 验证 EXPLAIN SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 20; 结果： type: ref key: idx_user_created rows: 20 Extra: Using index condition ← 无filesort 1.4 效果 执行时间：5秒 → 0.01秒 性能提升：500倍 扫描行数：50000 → 20 案例2：分页查询深分页优化 2.1 问题 -- 慢SQL（翻到第5000页） SELECT * FROM products ORDER BY id LIMIT 100000, 20; -- 执行时间：8秒 2.2 分析 EXPLAIN SELECT * FROM products ORDER BY id LIMIT 100000, 20; 问题： ...

什么是事务？ 事务（Transaction） 是数据库操作的最小工作单元，是一组不可分割的SQL语句集合。 -- 转账场景：A向B转账100元 START TRANSACTION; UPDATE account SET balance = balance - 100 WHERE user_id = 'A'; UPDATE account SET balance = balance + 100 WHERE user_id = 'B'; COMMIT; ACID四大特性 1. 原子性（Atomicity） 定义：事务是不可分割的最小单元，要么全部成功，要么全部失败回滚。 实现机制：undo log（回滚日志） 每次修改前，记录原始值到undo log 回滚时，读取undo log恢复数据 -- 示例：转账失败自动回滚 START TRANSACTION; UPDATE account SET balance = balance - 100 WHERE user_id = 'A'; -- 成功 UPDATE account SET balance = balance + 100 WHERE user_id = 'B'; -- 失败（余额不足） ROLLBACK; -- 自动回滚，A的余额恢复 应用场景： 订单支付（扣库存 + 创建订单 + 扣款） 批量数据导入（全部成功或全部失败） 2. 一致性（Consistency） 定义：事务执行前后，数据库从一个一致性状态转换到另一个一致性状态。 一致性约束： 数据完整性约束：主键、外键、唯一索引 业务规则约束：账户余额>=0，库存>=0 应用层约束：总金额守恒 -- 转账前：A有1000，B有500，总额1500 -- 转账后：A有900，B有600，总额仍是1500 -- 违反一致性的例子 UPDATE account SET balance = balance - 100 WHERE user_id = 'A'; -- 如果这里系统崩溃，没有执行下一条，总额变成1400，违反一致性 UPDATE account SET balance = balance + 100 WHERE user_id = 'B'; 如何保证一致性？ ...

为什么需要隔离级别？ 并发事务可能产生三大问题： 脏读（Dirty Read）：读到未提交的数据 不可重复读（Non-Repeatable Read）：同一查询两次结果不同 幻读（Phantom Read）：范围查询两次结果不同 隔离级别就是用来控制在多大程度上解决这些问题。 四种隔离级别 级别对比表 隔离级别 脏读 不可重复读 幻读 性能 应用场景 READ UNCOMMITTED（读未提交） ❌ 会 ❌ 会 ❌ 会 ⭐⭐⭐⭐ 几乎不用 READ COMMITTED（读已提交） ✅ 避免 ❌ 会 ❌ 会 ⭐⭐⭐ Oracle/PostgreSQL默认 REPEATABLE READ（可重复读） ✅ 避免 ✅ 避免 ⚠️ 部分避免 ⭐⭐ MySQL默认（推荐） SERIALIZABLE（串行化） ✅ 避免 ✅ 避免 ✅ 避免 ⭐ 严格一致性要求 1. READ UNCOMMITTED（读未提交） 特点：事务可以读取其他事务未提交的数据（脏读）。 演示：脏读问题 -- 设置隔离级别 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- 事务A START TRANSACTION; SELECT balance FROM account WHERE user_id = 'A'; -- 读到1000 -- 事务B（并发执行） START TRANSACTION; UPDATE account SET balance = 500 WHERE user_id = 'A'; -- 未提交 -- 此时事务A再次查询 -- 事务A SELECT balance FROM account WHERE user_id = 'A'; -- 读到500（脏读！） COMMIT; -- 事务B ROLLBACK; -- 回滚，余额恢复到1000 问题：事务A读到了事务B未提交的数据（500），但事务B最终回滚了，导致数据不一致。 ...

三大并发问题概述 问题 定义 影响范围 解决隔离级别 脏读 读到未提交的数据 单行数据 READ COMMITTED 不可重复读 同一查询两次结果不同（UPDATE） 单行数据 REPEATABLE READ 幻读 范围查询两次结果不同（INSERT） 多行数据 SERIALIZABLE 1. 脏读（Dirty Read） 定义 读取到其他事务未提交的数据，如果该事务回滚，就会读到"脏"数据。 场景演示 -- 设置为最低隔离级别 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- 时间线：事务A和事务B并发执行 ┌─────────────────┬─────────────────────────────────┐ │ 事务A │ 事务B │ ├─────────────────┼─────────────────────────────────┤ │ START TRANSACTION│ │ │ │ START TRANSACTION │ │ SELECT balance │ │ │ FROM account │ │ │ WHERE id=1; │ │ │ -- 读到1000 │ │ │ │ UPDATE account │ │ │ SET balance=500 │ │ │ WHERE id=1; │ │ │ -- 未提交 │ │ SELECT balance │ │ │ FROM account │ │ │ WHERE id=1; │ │ │ -- 读到500（脏读）│ │ │ │ ROLLBACK; │ │ │ -- 余额回滚到1000 │ │ -- 但事务A已经基于 │ │ │ -- 500做决策，错误！│ │ │ COMMIT; │ │ └─────────────────┴─────────────────────────────────┘ 真实案例 -- 场景：电商库存扣减 -- 事务A：查询库存并下单 START TRANSACTION; SELECT stock FROM product WHERE id = 1001; -- 读到库存50 -- 事务B：库存更正（发现统计错误） START TRANSACTION; UPDATE product SET stock = 10 WHERE id = 1001; -- 实际只有10件 -- 未提交 -- 事务A继续 -- 基于库存50的判断，允许用户下单40件 INSERT INTO orders (product_id, quantity) VALUES (1001, 40); COMMIT; -- 事务B回滚 ROLLBACK; -- 库存恢复到50 -- 结果：用户下单40件，但实际库存只有10件，超卖！ 解决方案 提高隔离级别到READ COMMITTED或更高： ...

什么是MVCC？ MVCC（Multi-Version Concurrency Control，多版本并发控制） 是InnoDB实现高并发的核心机制。 核心思想： 每行数据有多个版本 读操作读取快照版本（不加锁） 写操作创建新版本（加锁） 读写不冲突，提高并发性能 适用隔离级别： ✅ READ COMMITTED ✅ REPEATABLE READ ❌ READ UNCOMMITTED（无需MVCC） ❌ SERIALIZABLE（完全加锁） MVCC的实现机制 1. 隐藏字段 InnoDB为每行数据添加三个隐藏字段： 字段名 长度 说明 DB_TRX_ID 6字节 最后修改该行的事务ID DB_ROLL_PTR 7字节 回滚指针，指向undo log DB_ROW_ID 6字节 隐藏主键（无主键时自动生成） -- 实际存储的行数据（用户不可见） ┌────┬──────┬─────────┬────────────┬─────────────┬────────────┐ │ id │ name │ balance │ DB_TRX_ID │ DB_ROLL_PTR │ DB_ROW_ID │ ├────┼──────┼─────────┼────────────┼─────────────┼────────────┤ │ 1 │ A │ 1000 │ 100 │ 0x7FA8... │ 1 │ └────┴──────┴─────────┴────────────┴─────────────┴────────────┘ 2. undo log版本链 每次修改数据，旧版本保存在undo log，形成版本链。 -- 初始数据 INSERT INTO account (id, name, balance) VALUES (1, 'A', 1000); -- DB_TRX_ID = 100 -- 事务101：修改余额 UPDATE account SET balance = 900 WHERE id = 1; -- DB_TRX_ID = 101，旧版本保存到undo log -- 事务102：再次修改 UPDATE account SET balance = 800 WHERE id = 1; -- DB_TRX_ID = 102，旧版本保存到undo log 版本链结构： ...

MySQL的三种日志 日志类型 作用 实现层 记录内容 刷盘时机 undo log 保证原子性（回滚） InnoDB 修改前的旧值 事务执行时 redo log 保证持久性（崩溃恢复） InnoDB 修改后的新值 事务提交时 binlog 主从复制、数据恢复 Server层 逻辑SQL或行变更 事务提交时 1. undo log（回滚日志） 作用 事务回滚：保证原子性（ROLLBACK时恢复数据） MVCC实现：提供历史版本数据（快照读） 记录内容 记录数据修改前的旧值，用于回滚。 -- 执行UPDATE前，记录旧值到undo log UPDATE account SET balance = 900 WHERE id = 1; -- undo log记录： INSERT INTO undo_log VALUES ( trx_id = 101, table_id = account, row_id = 1, old_balance = 1000 -- 修改前的旧值 ); undo log类型 类型 操作类型 回滚方式 INSERT undo INSERT 删除插入的行 UPDATE undo UPDATE/DELETE 恢复修改前的值 -- INSERT undo log INSERT INTO account VALUES (2, 'B', 500); -- undo log：DELETE FROM account WHERE id = 2; -- UPDATE undo log UPDATE account SET balance = 900 WHERE id = 1; -- undo log：UPDATE account SET balance = 1000 WHERE id = 1; -- DELETE undo log DELETE FROM account WHERE id = 1; -- undo log：INSERT INTO account VALUES (1, 'A', 1000); 版本链 undo log通过DB_ROLL_PTR形成版本链，用于MVCC。 ...

MySQL锁分类 按锁粒度分类 全局锁（Global Lock） └─ FTWRL（Flush Tables With Read Lock） 表锁（Table Lock） ├─ 表级锁 ├─ 元数据锁（MDL Lock） └─ 意向锁（Intention Lock） 行锁（Row Lock） ├─ 记录锁（Record Lock） ├─ 间隙锁（Gap Lock） └─ Next-Key Lock（Record + Gap） 按锁模式分类 锁模式 英文名 兼容性 应用场景 共享锁（S锁） Shared Lock 读读兼容，读写互斥 SELECT … LOCK IN SHARE MODE 排他锁（X锁） Exclusive Lock 完全互斥 UPDATE、DELETE、SELECT … FOR UPDATE 1. 全局锁（Global Lock） 定义 锁住整个数据库实例，只读不可写。 命令 -- 加全局读锁 FLUSH TABLES WITH READ LOCK; -- 简称FTWRL -- 此时其他会话： SELECT * FROM account WHERE id = 1; -- ✅ 可以读 UPDATE account SET balance = 900 WHERE id = 1; -- ❌ 阻塞 INSERT INTO account VALUES (2, 'B', 500); -- ❌ 阻塞 -- 释放锁 UNLOCK TABLES; 应用场景 全库逻辑备份（保证数据一致性）： ...

InnoDB行锁分类 InnoDB行锁 ├─ Record Lock（记录锁）：锁住单行记录 ├─ Gap Lock（间隙锁）：锁住记录间的间隙 └─ Next-Key Lock（临键锁）：Record Lock + Gap Lock 适用场景： REPEATABLE READ隔离级别（默认） SERIALIZABLE隔离级别 1. Record Lock（记录锁） 定义 锁住索引记录，不锁间隙。 加锁条件 唯一索引等值查询（命中记录）： -- 建表 CREATE TABLE account ( id INT PRIMARY KEY, name VARCHAR(50), balance INT, KEY idx_balance (balance) ); INSERT INTO account VALUES (1, 'A', 1000), (5, 'B', 1500), (10, 'C', 2000), (15, 'D', 2500); -- 加记录锁 START TRANSACTION; SELECT * FROM account WHERE id = 5 FOR UPDATE; -- 只锁id=5这一行（Record Lock） -- 其他事务 UPDATE account SET balance = 900 WHERE id = 5; -- ❌ 阻塞（锁冲突） UPDATE account SET balance = 900 WHERE id = 10; -- ✅ 不阻塞（不同行） INSERT INTO account VALUES (7, 'E', 800); -- ✅ 不阻塞（无间隙锁） 锁范围示意 id索引： 1 ─── 5 ─── 10 ─── 15 │ ▼ │ │ │ [锁定] │ │ │ │ │ │ └─────┴─────┴─────┘ 不锁间隙 2. Gap Lock（间隙锁） 定义 锁住两个索引记录之间的间隙，防止其他事务在间隙中插入数据。 ...