Mysqls

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; 结果： ...

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的基本单位，与磁盘页一一对应。 ...

行格式概述 行格式（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'); 存储布局： ...

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 -- 独立表空间 优点： ...

存储引擎概述 存储引擎（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已废弃） 聚簇索引 主键索引直接存储数据： ...

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 发送数据给客户端 大结果集传输 长连接与短连接 长连接：连接保持，减少连接开销 ...

实战案例概览 案例 核心问题 解决方案 难点 转账业务 数据一致性、死锁 固定加锁顺序、悲观锁 多账户并发转账 秒杀抢购 超卖、高并发 乐观锁 + 限流 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); } } <!-- MyBatis Mapper --> <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件商品，要求： ...

乐观锁 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：转账业务 ...

什么是死锁？ 死锁（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的锁 │ │ │ -- 死锁！ │ └─────────────────┴─────────────────────────────────┘ 死锁检测与处理： ...

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（间隙锁） 定义 锁住两个索引记录之间的间隙，防止其他事务在间隙中插入数据。 ...