如约数科

死锁：产生原因与解决方案

什么是死锁？死锁（Deadlock）：两个或多个事务互相等待对方释放锁，形成循环等待，导致所有事务都无法继续执行。 -- 经典死锁场景事务A：持有锁1，等待锁2 事务B：持有锁2，等待锁1 → 互相等待，形成死锁死锁产生条件必须同时满足4个条件：互斥：资源不能被多个事务同时占用持有并等待：事务持有锁的同时，等待其他锁不可剥夺：已获得的锁不能被强制释放循环等待：事务形成循环等待链死锁示例示例1：经典死锁 -- 建表 CREATE TABLE account ( id INT PRIMARY KEY, balance INT ); INSERT INTO account VALUES (1, 1000), (2, 2000); -- 时间线 ┌─────────────────┬─────────────────────────────────┐ │ 事务A │ 事务B │ ├─────────────────┼─────────────────────────────────┤ │ START TRANSACTION│ │ │ UPDATE account │ │ │ SET balance=900 │ │ │ WHERE id=1; │ │ │ -- 持有id=1的锁 │ │ │ │ START TRANSACTION │ │ │ UPDATE account │ │ │ SET balance=1800 │ │ │ WHERE id=2; │ │ │ -- 持有id=2的锁 │ │ UPDATE account │ │ │ SET balance=800 │ │ │ WHERE id=2; │ │ │ -- 等待id=2的锁 │ │ │ │ UPDATE account │ │ │ SET balance=1100 │ │ │ WHERE id=1; │ │ │ -- 等待id=1的锁 │ │ │ -- 死锁！ │ └─────────────────┴─────────────────────────────────┘ 死锁检测与处理： ...

乐观锁与悲观锁：应用场景对比

乐观锁 vs 悲观锁核心思想类型核心思想锁机制冲突处理适用场景悲观锁先加锁，再操作（悲观：总会冲突）数据库锁（X锁、S锁）阻塞等待冲突频繁乐观锁先操作，提交时检查（乐观：很少冲突）版本号、时间戳重试或放弃冲突少 1. 悲观锁（Pessimistic Lock）定义假设冲突一定会发生，每次读取数据前先加锁，其他事务无法修改数据。实现方式方式1：排他锁（FOR UPDATE） -- 加排他锁 START TRANSACTION; SELECT * FROM account WHERE id = 1 FOR UPDATE; -- 加X锁，其他事务阻塞 -- 修改数据 UPDATE account SET balance = 900 WHERE id = 1; COMMIT; -- 释放锁方式2：共享锁（LOCK IN SHARE MODE） -- 加共享锁 START TRANSACTION; SELECT * FROM account WHERE id = 1 LOCK IN SHARE MODE; -- 加S锁，其他事务可读但不可写 -- 读取后再更新 UPDATE account SET balance = 900 WHERE id = 1; COMMIT; 应用场景场景1：库存扣减（防止超卖） -- 秒杀场景：10000个用户抢100件商品 START TRANSACTION; -- 1. 加锁查询库存 SELECT stock FROM product WHERE id = 1001 FOR UPDATE; -- 悲观锁 -- stock = 100 -- 2. 检查库存 IF stock >= 1 THEN -- 3. 扣减库存 UPDATE product SET stock = stock - 1 WHERE id = 1001; -- 4. 创建订单 INSERT INTO orders (user_id, product_id) VALUES (123, 1001); COMMIT; ELSE ROLLBACK; -- 库存不足 END IF; 场景2：转账业务 ...

事务实战：转账案例与并发控制

实战案例概览案例核心问题解决方案难点转账业务数据一致性、死锁固定加锁顺序、悲观锁多账户并发转账秒杀抢购超卖、高并发乐观锁 + 限流 10000人抢100件商品订单支付重复支付、幂等性悲观锁 + 唯一约束防止重复扣款红包发放余额不足、公平性悲观锁 + 事务隔离 1个红包被多人抢积分扣减负数积分乐观锁 + 余额检查并发扣减积分案例1：转账业务需求用户A向用户B转账100元，要求：余额不能为负数转账过程中不能被打断防止死锁方案1：基础实现（有死锁风险） -- ❌ 可能死锁 -- 事务A：A向B转100 START TRANSACTION; UPDATE account SET balance = balance - 100 WHERE user_id = 'A'; -- 锁A UPDATE account SET balance = balance + 100 WHERE user_id = 'B'; -- 等待锁B COMMIT; -- 事务B：B向A转50（并发执行） START TRANSACTION; UPDATE account SET balance = balance - 50 WHERE user_id = 'B'; -- 锁B UPDATE account SET balance = balance + 50 WHERE user_id = 'A'; -- 等待锁A（死锁！） COMMIT; 方案2：固定加锁顺序（推荐） -- ✅ 避免死锁：按user_id升序加锁 -- 转账函数（伪代码） FUNCTION transfer(from_user, to_user, amount): -- 1. 固定加锁顺序（按user_id升序） first_user = MIN(from_user, to_user) second_user = MAX(from_user, to_user) START TRANSACTION; -- 2. 按顺序锁定账户 SELECT balance FROM account WHERE user_id = first_user FOR UPDATE; SELECT balance FROM account WHERE user_id = second_user FOR UPDATE; -- 3. 检查余额 IF from_user.balance < amount THEN ROLLBACK; RETURN "余额不足"; END IF; -- 4. 扣款和到账 UPDATE account SET balance = balance - amount WHERE user_id = from_user; UPDATE account SET balance = balance + amount WHERE user_id = to_user; COMMIT; RETURN "转账成功"; END FUNCTION; 方案3：Java实现（完整代码） @Service public class TransferService { @Autowired private AccountMapper accountMapper; @Transactional(rollbackFor = Exception.class) public void transfer(String fromUser, String toUser, BigDecimal amount) { // 1. 固定加锁顺序（避免死锁） String firstUser = fromUser.compareTo(toUser) < 0 ? fromUser : toUser; String secondUser = fromUser.compareTo(toUser) < 0 ? toUser : fromUser; // 2. 按顺序锁定账户（悲观锁） Account first = accountMapper.selectForUpdate(firstUser); Account second = accountMapper.selectForUpdate(secondUser); // 3. 检查余额 Account fromAccount = fromUser.equals(firstUser) ? first : second; if (fromAccount.getBalance().compareTo(amount) < 0) { throw new BusinessException("余额不足"); } // 4. 扣款和到账 accountMapper.updateBalance(fromUser, amount.negate()); // 扣款 accountMapper.updateBalance(toUser, amount); // 到账 // 5. 记录流水（可选） recordTransferLog(fromUser, toUser, amount); } }  <select id="selectForUpdate" resultType="Account"> SELECT * FROM account WHERE user_id = #{userId} FOR UPDATE </select> <update id="updateBalance"> UPDATE account SET balance = balance + #{amount} WHERE user_id = #{userId} </update> 案例2：秒杀抢购需求 10000个用户抢购100件商品，要求： ...

MySQL架构总览：从连接到执行的完整流程

MySQL架构总览 MySQL采用分层架构，从上到下分为4层： ┌────────────────────────────────────────┐ │ 第1层：连接层（Connection Layer） │ │ - 连接器：管理客户端连接 │ │ - 线程池：处理并发连接 │ ├────────────────────────────────────────┤ │ 第2层：SQL层（SQL Layer / Server层） │ │ - 查询缓存（已废弃） │ │ - 分析器：词法分析 + 语法分析 │ │ - 优化器：生成执行计划 │ │ - 执行器：调用存储引擎接口 │ ├────────────────────────────────────────┤ │ 第3层：存储引擎层（Storage Engine） │ │ - InnoDB（默认） │ │ - MyISAM、Memory等 │ ├────────────────────────────────────────┤ │ 第4层：文件系统层（File System） │ │ - 数据文件、日志文件、配置文件 │ └────────────────────────────────────────┘ 1. 连接器（Connector）作用管理客户端连接，进行身份验证和权限验证。连接过程 -- 客户端连接命令 mysql -h127.0.0.1 -P3306 -uroot -p -- 连接流程 1. TCP握手建立连接 2. 验证用户名密码（authentication_string） 3. 获取用户权限列表（grant tables） 4. 返回连接ID（connection_id）查看连接信息 -- 查看当前连接 SHOW PROCESSLIST; -- 输出示例 +----+------+-----------+------+---------+------+----------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+----------+------------------+ | 1 | root | localhost | test | Query | 0 | starting | SHOW PROCESSLIST | | 2 | root | localhost | NULL | Sleep | 600 | NULL | NULL | +----+------+-----------+------+---------+------+----------+------------------+ 连接状态状态含义典型场景 Sleep 空闲，等待客户端发送请求连接池中的空闲连接 Query 正在执行SQL 查询、更新、删除等 Locked 等待锁释放表锁、行锁冲突 Sorting 正在排序 ORDER BY、GROUP BY Sending 发送数据给客户端大结果集传输长连接与短连接长连接：连接保持，减少连接开销 ...

存储引擎对比：InnoDB vs MyISAM

存储引擎概述存储引擎（Storage Engine）是MySQL的核心组件，负责数据的存储和读取。 -- 查看支持的存储引擎 SHOW ENGINES; -- 输出示例 +--------------------+---------+ | Engine | Support | +--------------------+---------+ | InnoDB | DEFAULT | -- 默认引擎 | MyISAM | YES | | MEMORY | YES | | CSV | YES | | ARCHIVE | YES | +--------------------+---------+ InnoDB vs MyISAM对比特性 InnoDB MyISAM 事务支持 ✅ 支持ACID ❌ 不支持锁粒度 ✅ 行锁 ❌ 表锁外键 ✅ 支持 ❌ 不支持崩溃恢复 ✅ 自动恢复（redo log） ❌ 需要手动修复 MVCC ✅ 支持 ❌ 不支持全文索引 ✅ 支持（5.6+） ✅ 支持存储结构聚簇索引非聚簇索引 COUNT(*) 慢（需要扫描）快（存储行数）空间占用较大较小适用场景 OLTP（事务处理） OLAP（统计分析） InnoDB存储引擎核心特性支持事务：ACID保障行级锁：高并发性能 MVCC：读写不冲突外键约束：数据完整性文件结构 # InnoDB文件（MySQL 5.7+） /var/lib/mysql/ ├─ ibdata1 # 系统表空间（共享） ├─ ib_logfile0 # redo log文件 ├─ ib_logfile1 ├─ test/ # 数据库目录 │ ├─ users.ibd # 独立表空间（数据+索引） │ └─ users.frm # 表结构定义（MySQL 8.0已废弃）聚簇索引主键索引直接存储数据： ...

InnoDB存储结构：表空间、段、区、页

InnoDB存储层次表空间（Tablespace） └─ 段（Segment） └─ 区（Extent，1MB = 64页） └─ 页（Page，16KB，InnoDB的基本IO单位） └─ 行（Row） 1. 表空间（Tablespace）定义表空间是InnoDB存储数据的逻辑单位，包含多个段。分类类型文件名内容配置参数系统表空间 ibdata1 数据字典、undo log、双写缓冲 innodb_data_file_path 独立表空间 table_name.ibd 表数据+索引 innodb_file_per_table 通用表空间 tablespace.ibd 多个表共享 CREATE TABLESPACE 临时表空间 ibtmp1 临时表、排序缓冲 innodb_temp_data_file_path Undo表空间 undo_001、undo_002 undo log（MySQL 8.0+） innodb_undo_tablespaces 系统表空间 vs 独立表空间 -- 查看配置 SHOW VARIABLES LIKE 'innodb_file_per_table'; -- ON：每个表独立.ibd文件（推荐） -- OFF：所有表共享ibdata1（不推荐） -- 查看表空间文件 # ls -lh /var/lib/mysql/test/ -rw-r----- 1 mysql mysql 16M users.ibd -- 独立表空间优点： ...

InnoDB行格式：Compact、Redundant、Dynamic、Compressed

行格式概述行格式（Row Format）定义了一行数据在磁盘上的存储方式。 -- 查看表的行格式 SHOW TABLE STATUS LIKE 'users'\G -- Row_format: Dynamic -- 创建表时指定行格式 CREATE TABLE test ( id INT PRIMARY KEY, name VARCHAR(100) ) ROW_FORMAT=COMPACT; -- 修改行格式 ALTER TABLE test ROW_FORMAT=DYNAMIC; 四种行格式行格式 MySQL版本特点适用场景 Redundant 5.0前默认旧格式，占用空间大兼容性 Compact 5.0-5.6默认紧凑格式，节省20%空间通用（已过时） Dynamic 5.7+默认动态格式，处理行溢出推荐（默认） Compressed 5.5+ 压缩格式，节省50%+空间只读表、归档数据 1. Compact行格式（紧凑格式）结构 ┌──────────────────────────────────────────────────┐ │ 变长字段长度列表（逆序） │ VARCHAR、TEXT字段的长度 ├──────────────────────────────────────────────────┤ │ NULL值列表（位图，逆序） │ 标记哪些字段为NULL ├──────────────────────────────────────────────────┤ │ 记录头信息（5字节） │ deleted_flag、min_rec_flag等 ├──────────────────────────────────────────────────┤ │ 隐藏列 │ DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID ├──────────────────────────────────────────────────┤ │ 列1数据 │ 实际数据 ├──────────────────────────────────────────────────┤ │ 列2数据 │ ├──────────────────────────────────────────────────┤ │ ... │ └──────────────────────────────────────────────────┘ 示例 CREATE TABLE user_compact ( id INT PRIMARY KEY, name VARCHAR(20), age INT, email VARCHAR(50) ) ROW_FORMAT=COMPACT; INSERT INTO user_compact VALUES (1, 'Alice', 25, 'alice@example.com'); 存储布局： ...

Buffer Pool：MySQL的内存管理核心

Buffer Pool概述 Buffer Pool（缓冲池）是InnoDB最重要的内存结构，用于缓存数据页和索引页。 ┌──────────────────────────────────────┐ │ MySQL内存结构 │ ├──────────────────────────────────────┤ │ Buffer Pool（最大，默认128MB） │ 缓存数据页 ├──────────────────────────────────────┤ │ Change Buffer │ 缓存写操作 ├──────────────────────────────────────┤ │ Adaptive Hash Index │ 自适应哈希索引 ├──────────────────────────────────────┤ │ Log Buffer │ 缓存redo log └──────────────────────────────────────┘ Buffer Pool的作用 1. 减少磁盘IO -- 无Buffer Pool（每次查询都访问磁盘） SELECT * FROM users WHERE id = 1; -- 磁盘IO：10ms -- 有Buffer Pool（缓存命中，直接读内存） SELECT * FROM users WHERE id = 1; -- 内存读取：0.01ms（快1000倍） 2. 提升并发性能 -- 多个事务并发读取同一数据页事务A: SELECT * FROM users WHERE id = 1; -- 加载到Buffer Pool 事务B: SELECT * FROM users WHERE id = 1; -- 直接从Buffer Pool读取（无磁盘IO）事务C: SELECT * FROM users WHERE id = 1; -- 直接从Buffer Pool读取 Buffer Pool的结构 1. 缓冲页（Buffer Page）缓冲页是Buffer Pool的基本单位，与磁盘页一一对应。 ...

Change Buffer：提升非唯一索引写入性能

Change Buffer概述 Change Buffer 是InnoDB的写优化机制，用于缓存非唯一二级索引的修改操作。传统方式（每次修改立即更新索引）： INSERT → 更新数据页 → 更新索引页（磁盘IO） Change Buffer方式（延迟更新索引）： INSERT → 更新数据页 → 缓存到Change Buffer → 后台合并（merge）为什么需要Change Buffer？问题：二级索引随机IO CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50), email VARCHAR(100), INDEX idx_email (email) -- 非唯一二级索引 ); -- 插入数据 INSERT INTO users VALUES (1, 'Alice', 'alice@a.com'); INSERT INTO users VALUES (100, 'Bob', 'bob@b.com'); INSERT INTO users VALUES (50, 'Charlie', 'charlie@c.com'); 问题：主键索引：顺序插入（1 → 100 → 50，但按主键排序） email索引：随机插入（alice → bob → charlie，按email排序）随机IO：email索引页分散在磁盘各处，需要多次磁盘寻址 Change Buffer解决方案：缓存email索引的修改到内存批量合并（merge），减少随机IO Change Buffer的工作原理 1. 插入流程 INSERT INTO users VALUES (101, 'David', 'david@d.com'); -- 流程 1. 更新主键索引（聚簇索引） - 直接写入Buffer Pool（缓存命中）或磁盘 2. 更新email索引（二级索引） - 检查索引页是否在Buffer Pool - ✅ 在：直接更新 - ❌ 不在：缓存到Change Buffer（不读磁盘） 3. 后台合并（merge） - 当索引页加载到Buffer Pool时 - 或系统空闲时 - 或Change Buffer满时 2. 合并（Merge）流程触发条件： 1. 查询需要访问该索引页 2. 后台线程定期合并 3. Change Buffer空间不足 4. MySQL正常关闭合并流程： 1. 加载索引页到Buffer Pool 2. 应用Change Buffer中的修改 3. 写回磁盘（或标记为脏页） 4. 清空Change Buffer对应条目 Change Buffer的限制只适用于非唯一二级索引 -- ✅ 适用：非唯一索引 CREATE INDEX idx_name ON users(name); -- 可以使用Change Buffer -- ❌ 不适用：唯一索引 CREATE UNIQUE INDEX uk_email ON users(email); -- 无法使用Change Buffer -- 原因：需要读取索引页检查唯一性，无法延迟合并 -- ❌ 不适用：主键索引 -- 原因：主键是聚簇索引，数据直接存储在索引中为什么唯一索引不能用？ -- 插入数据 INSERT INTO users VALUES (102, 'Eve', 'eve@e.com'); -- 唯一索引检查流程 1. 检查email='eve@e.com'是否已存在 2. 必须读取email索引页（磁盘IO） 3. 如果不存在，才能插入 -- 无法使用Change Buffer 因为必须立即读取索引页，无法延迟合并 Change Buffer配置 1. 查看配置 -- 查看Change Buffer大小 SHOW VARIABLES LIKE 'innodb_change_buffer_max_size'; -- 25（默认，占Buffer Pool的25%） -- 查看Change Buffer模式 SHOW VARIABLES LIKE 'innodb_change_buffering'; -- all（默认） 2. 模式配置模式缓存操作适用场景 all INSERT、DELETE、UPDATE（默认）通用 none 禁用唯一索引多的表 inserts 只缓存INSERT 只有插入场景 deletes 只缓存DELETE 批量删除场景 changes 缓存INSERT、UPDATE 插入更新场景 purges 缓存DELETE、PURGE 清理场景 -- 配置示例 # my.cnf [mysqld] innodb_change_buffering = all innodb_change_buffer_max_size = 25 -- 25% 性能对比测试场景 -- 测试表（500万行，非唯一索引） CREATE TABLE test_change_buffer ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), email VARCHAR(100), phone VARCHAR(20), INDEX idx_email (email), INDEX idx_phone (phone) ); -- 批量插入100万行 INSERT INTO test_change_buffer (name, email, phone) SELECT CONCAT('User', seq), CONCAT('user', seq, '@example.com'), CONCAT('1380000', seq) FROM seq_1_to_1000000; 结果： ...

Adaptive Hash Index：自适应哈希索引

Adaptive Hash Index概述 Adaptive Hash Index（AHI，自适应哈希索引）是InnoDB的自动优化机制，在Buffer Pool上层构建哈希索引，加速等值查询。查询流程对比：传统B+树查询： SELECT * FROM users WHERE id = 100; → B+树查找（3-4次页访问） AHI查询： SELECT * FROM users WHERE id = 100; → Hash查找（1次内存访问） AHI的工作原理 1. 自动构建 -- InnoDB监控查询模式 SELECT * FROM users WHERE id = 1; -- 第1次 SELECT * FROM users WHERE id = 2; -- 第2次 SELECT * FROM users WHERE id = 3; -- 第3次 ... SELECT * FROM users WHERE id = 100; -- 第N次 -- 当检测到等值查询频繁访问同一索引前缀 -- InnoDB自动构建哈希索引 AHI[100] → Buffer Pool页地址 2. 哈希索引结构 B+树索引（磁盘） 10 / \ 5 15 / \ / \ 1 7 12 17 → 数据页 AHI（内存） Hash表： id=1 → 页地址0x1000 id=5 → 页地址0x1001 id=7 → 页地址0x1002 id=10 → 页地址0x1003 ... 3. 查询流程 -- 查询 SELECT * FROM users WHERE id = 100; -- 流程 1. 检查AHI是否有id=100的条目 2. ✅ 有：直接通过Hash查找，获取页地址（O(1)） 3. ❌ 没有：走B+树查找（O(log N)） AHI的优势 1. 加速等值查询 -- 等值查询（适合AHI） SELECT * FROM users WHERE id = 100; -- ✅ AHI加速 SELECT * FROM orders WHERE order_id = 1001; -- ✅ AHI加速 -- B+树查询：3-4次页访问（假设树高3） -- AHI查询：1次Hash查找（快3-4倍） 2. 减少CPU开销 -- B+树查找需要： 1. 二分查找（多次比较） 2. 页间跳转（多次指针访问） 3. 最终定位数据行 -- AHI查找需要： 1. Hash计算（一次） 2. 直接获取页地址（一次） AHI的限制 1. 只适用于等值查询 -- ✅ 适用 SELECT * FROM users WHERE id = 100; SELECT * FROM users WHERE id IN (1, 2, 3); -- ❌ 不适用 SELECT * FROM users WHERE id > 100; -- 范围查询 SELECT * FROM users WHERE id BETWEEN 1 AND 100; -- 范围查询 SELECT * FROM users WHERE name LIKE 'A%'; -- 模糊查询 2. 只针对热点数据 -- AHI只缓存频繁访问的索引前缀 -- 例如：id=100访问了1000次 → 自动加入AHI -- 而：id=999999只访问1次 → 不会加入AHI 3. 有维护开销 -- 更新/删除数据时，需要维护AHI UPDATE users SET name = 'Alice' WHERE id = 100; -- 流程： 1. 更新B+树索引 2. 更新Buffer Pool 3. 更新AHI（如果id=100在AHI中） AHI配置 1. 查看状态 -- 查看AHI开关 SHOW VARIABLES LIKE 'innodb_adaptive_hash_index'; -- ON（默认开启） -- 查看AHI分区数（MySQL 5.7+） SHOW VARIABLES LIKE 'innodb_adaptive_hash_index_parts'; -- 8（默认，减少锁竞争） 2. 开关配置 -- 关闭AHI（不推荐） SET GLOBAL innodb_adaptive_hash_index = OFF; -- 开启AHI（默认） SET GLOBAL innodb_adaptive_hash_index = ON; 注意：动态修改需要重建AHI，会短暂影响性能。 ...