MySQL核心机制
第6章 存储引擎
6.1 MySQL存储引擎概述
存储引擎架构
MySQL采用插件式存储引擎架构，允许根据应用需求选择最适合的存储引擎。
-- 查看支持的存储引擎
SHOW ENGINES;
SHOW ENGINES\G
-- 查看默认存储引擎
SHOW VARIABLES LIKE 'storage_engine';
SHOW VARIABLES LIKE 'default_storage_engine';
-- 查看表使用的存储引擎
SHOW TABLE STATUS WHERE Name = 'users';
SHOW CREATE TABLE users;
-- 查看所有表的存储引擎
SHOW TABLE STATUS;
存储引擎对比
| 引擎 | 事务支持 | 锁机制 | 外键 | 索引类型 | 适用场景 |
|---|---|---|---|---|---|
| InnoDB | ✓ | 行锁 | ✓ | B+树、全文 | 高并发、事务处理 |
| MyISAM | ✗ | 表锁 | ✗ | B+树、全文 | 读密集、Web应用 |
| Memory | ✗ | 表锁 | ✗ | 哈希 | 临时表、缓存 |
| CSV | ✗ | 表锁 | ✗ | - | 数据交换、日志 |
| Archive | ✗ | 行锁 | ✗ | - | 归档、历史数据 |
| Blackhole | ✗ | - | ✗ | - | 日志记录、测试 |
6.2 InnoDB存储引擎
InnoDB架构
┌─────────────────────────────────────────────┐
│ InnoDB架构层次 │
├─────────────────────────────────────────────┤
│ 连接线程：处理用户请求，SQL接口 │
├─────────────────────────────────────────────┤
│ InnoDB主缓冲池（Buffer Pool）： │
│ - 数据页缓存 │
│ - 索引页缓存 │
│ - 插入缓冲、自适应哈希索引 │
├─────────────────────────────────────────────┤
│ 日志缓冲区： │
│ - Redo Log Buffer │
│ - Undo Log Buffer │
├─────────────────────────────────────────────┤
│ 后台线程： │
│ - Master Thread：purge、刷新脏页 │
│ - IO Thread：异步IO操作 │
│ - Purge Thread：回收undo页 │
│ - Page Cleaner Thread：刷脏页 │
├─────────────────────────────────────────────┤
│ 存储文件： │
│ - ibdata1: 共享表空间（系统数据、undo） │
│ - ibd: 独立表空间（数据、索引） │
│ - ib_logfile0/1: Redo日志文件 │
└─────────────────────────────────────────────┘
InnoDB特性
1. 事务支持（ACID）
-- InnoDB完全支持ACID特性
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT; -- 持久化到磁盘
-- ROLLBACK; -- 回滚事务
2. 行级锁
-- InnoDB使用行级锁，并发度高
-- 共享锁（S锁）：允许读，不允许写
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;
-- 排他锁（X锁）：不允许读也不允许写
SELECT * FROM users WHERE id = 1 FOR UPDATE;
-- 意向锁：表级锁，自动添加
-- IS（意向共享锁）、IX（意向排他锁）
3. 外键约束
-- InnoDB支持外键
CREATE TABLE departments (
dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(50)
) ENGINE=InnoDB;
CREATE TABLE employees (
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(50),
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
ON DELETE CASCADE
ON UPDATE CASCADE
) ENGINE=InnoDB;
4. 崩溃恢复
-- Redo Log：重做日志，保证持久性
-- Undo Log：回滚日志，保证原子性和MVCC
-- 查看Redo日志配置
SHOW VARIABLES LIKE 'innodb_log%';
SHOW VARIABLES LIKE 'innodb_flush_log%';
-- innodb_flush_log_at_trx_commit:
-- 0: 每秒刷新到磁盘
-- 1: 每次事务提交刷新（默认，最安全）
-- 2: 每次事务提交写到缓存，每秒刷新
InnoDB行格式
-- 查看行格式
SHOW TABLE STATUS LIKE 'users'\G
SHOW VARIABLES LIKE 'innodb_default_row_format';
-- 设置行格式
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50)
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
ALTER TABLE users ROW_FORMAT=DYNAMIC;
行格式对比：
| 格式 | 特点 | 存储效率 | 适用场景 |
|---|---|---|---|
| REDUNDANT | 旧格式，兼容性好 | 低 | 旧版本兼容 |
| COMPACT | 紧凑格式，比REDUNDANT节省20%空间 | 中 | 通用场景 |
| DYNAMIC | 动态格式，支持大字段外存 | 高 | 大字段多（默认） |
| COMPRESSED | 压缩格式，节省磁盘和内存 | 最高 | 归档表、IO密集 |
-- DYNAMIC vs COMPRESSED 示例
CREATE TABLE articles (
id INT PRIMARY KEY,
title VARCHAR(200),
content LONGTEXT, -- 大字段
created_at TIMESTAMP
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
-- content超过768字节后存储到外部页
CREATE TABLE logs (
id INT PRIMARY KEY,
log_data TEXT,
created_at TIMESTAMP
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
-- 压缩存储，节省空间，但CPU开销大
InnoDB缓冲池
-- 查看缓冲池配置
SHOW VARIABLES LIKE 'innodb_buffer_pool%';
SHOW STATUS LIKE 'Innodb_buffer_pool%';
-- 缓冲池大小（建议设置为物理内存的50-80%）
SET GLOBAL innodb_buffer_pool_size = 2147483648; -- 2GB
-- 多缓冲池实例（减少竞争）
SET GLOBAL innodb_buffer_pool_instances = 4;
-- 缓冲池状态查询
SELECT
pool_size,
pool_instances,
ROUND(buffer_pool_size / 1024 / 1024 / 1024, 2) AS size_gb
FROM (
SELECT
@@innodb_buffer_pool_size AS buffer_pool_size,
@@innodb_buffer_pool_instances AS pool_instances,
@@innodb_buffer_pool_size / @@innodb_buffer_pool_instances AS pool_size
) t;
6.3 MyISAM存储引擎
MyISAM特性
-- 创建MyISAM表
CREATE TABLE logs (
id INT PRIMARY KEY AUTO_INCREMENT,
log_message TEXT,
created_at TIMESTAMP
) ENGINE=MyISAM;
-- MyISAM特点：
-- 1. 不支持事务
-- 2. 表级锁（读锁和写锁）
-- 3. 不支持外键
-- 4. 支持全文索引（MySQL 5.6之前）
-- 5. 崩溃后无法安全恢复
-- 6. 支持压缩表（只读）
MyISAM文件结构
# MyISAM表存储文件
# table_name.MYD：数据文件（MYData）
# table_name.MYI：索引文件（MYIndex）
# table_name.frm：表结构文件
-- 查看表文件
SHOW TABLE STATUS LIKE 'logs'\G;
MyISAM锁机制
-- MyISAM表级锁
-- 读锁（共享锁）：支持并发读，阻塞写
LOCK TABLE logs READ;
SELECT COUNT(*) FROM logs; -- 可以执行
-- INSERT INTO logs ...; -- 阻塞
UNLOCK TABLES;
-- 写锁（排他锁）：独占访问
LOCK TABLE logs WRITE;
INSERT INTO logs VALUES (NULL, 'test', NOW()); -- 可以执行
-- SELECT * FROM logs; -- 阻塞
UNLOCK TABLES;
-- 查看锁状态
SHOW OPEN TABLES WHERE In_use > 0;
SHOW STATUS LIKE 'Table_locks%';
MyISAM适用场景
-- 1. 读密集型应用
CREATE TABLE articles (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(200),
content TEXT,
created_at TIMESTAMP,
FULLTEXT INDEX ft_content (title, content)
) ENGINE=MyISAM;
-- 2. 只读或读多写少的配置表
CREATE TABLE configs (
config_key VARCHAR(100) PRIMARY KEY,
config_value TEXT,
updated_at TIMESTAMP
) ENGINE=MyISAM;
-- 3. 临时表或会话表
CREATE TEMPORARY TABLE temp_results (
id INT PRIMARY KEY AUTO_INCREMENT,
result_data TEXT
) ENGINE=MyISAM;
6.4 Memory存储引擎
Memory特性
-- 创建Memory表
CREATE TABLE cache (
cache_key VARCHAR(100) PRIMARY KEY,
cache_value TEXT,
created_at TIMESTAMP,
expires_at TIMESTAMP
) ENGINE=MEMORY;
-- Memory特点：
-- 1. 数据存储在内存中，访问速度快
-- 2. 表级锁
-- 3. 不支持事务、外键
-- 4. 崩溃后数据丢失
-- 5. 支持哈希索引（默认）和B+树索引
-- 6. 固定宽度字段（VARCHAR会转为CHAR）
Memory索引
-- 哈希索引（默认）
CREATE TABLE hash_cache (
id INT PRIMARY KEY, -- 哈希索引
name VARCHAR(50),
KEY idx_name (name) USING HASH -- 显式指定哈希
) ENGINE=MEMORY;
-- B+树索引
CREATE TABLE btree_cache (
id INT PRIMARY KEY,
name VARCHAR(50),
KEY idx_name (name) USING BTREE -- B+树索引，支持范围查询
) ENGINE=MEMORY;
-- 哈希索引 vs B+树索引
-- 哈希索引：等值查询快，不支持范围查询
-- B+树索引：支持范围查询、排序，但稍慢
Memory使用场景
-- 1. 会话缓存
CREATE TABLE session_cache (
session_id VARCHAR(128) PRIMARY KEY,
user_id INT,
session_data TEXT,
expires_at DATETIME,
INDEX idx_expires (expires_at)
) ENGINE=MEMORY;
-- 2. 查询结果缓存
CREATE TABLE popular_items (
item_id INT PRIMARY KEY,
view_count INT,
rank_score INT
) ENGINE=MEMORY;
-- 3. 临时聚合结果
CREATE TEMPORARY TABLE temp_stats (
stat_date DATE,
stat_value INT,
INDEX idx_date (stat_date)
) ENGINE=MEMORY;
-- 定期刷新缓存
-- 可以通过定时任务或应用层逻辑实现
6.5 存储引擎选择策略
选择决策树
是否需要事务？
│
┌───────────┴───────────┐
是 否
│ │
选择InnoDB 是否大量写操作？
│
┌───────────┴───────────┐
是 否
│ │
仍建议InnoDB 数据是否可丢失？
(除非特殊需求) │
┌────────┴────────┐
是 否
│ │
选择Memory 选择MyISAM
(临时缓存) (只读/归档)
性能对比
| 操作 | InnoDB | MyISAM | Memory |
|---|---|---|---|
| 读性能 | 中 | 高 | 极高 |
| 写性能 | 中 | 低 | 高 |
| 并发读 | 优秀 | 差 | 差 |
| 并发写 | 优秀 | 差 | 差 |
| 事务安全 | ✓ | ✗ | ✗ |
| 崩溃恢复 | ✓ | ✗ | ✗ |
| 外键支持 | ✓ | ✗ | ✗ |
实际应用建议
-- 1. 核心业务表：InnoDB
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) UNIQUE,
email VARCHAR(100),
balance DECIMAL(10, 2),
created_at TIMESTAMP
) ENGINE=InnoDB;
-- 2. 统计报表表：MyISAM（只读，定期生成）
CREATE TABLE monthly_reports (
report_id INT PRIMARY KEY,
report_month DATE,
total_sales DECIMAL(15, 2),
total_orders INT
) ENGINE=MyISAM;
-- 3. 缓存表：Memory
CREATE TABLE user_cache (
user_id INT PRIMARY KEY,
user_data JSON,
last_update TIMESTAMP
) ENGINE=MEMORY;
-- 4. 日志表：根据需求选择
-- 高并发写：InnoDB（可靠性优先）
-- 只读归档：MyISAM（空间优先）
CREATE TABLE access_logs (
log_id BIGINT PRIMARY KEY AUTO_INCREMENT,
user_id INT,
action VARCHAR(50),
ip VARCHAR(45),
created_at TIMESTAMP
) ENGINE=InnoDB; -- 或 MyISAM
第7章 索引机制
7.1 B+树索引原理
B+树结构
┌─────────┐
│ 50 │ 根节点（内部节点）
├─────────┤
┌───────┴───────┐
↓ ↓
┌─────────┐ ┌─────────┐
│ 20 40 │ │ 70 90 │ 内部节点
├─────────┤ ├─────────┤
↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓
┌──┬─┬─┬──┐ ┌──┬─┬─┬──┐
│10│25│35│45│ │60│75│85│95│ 叶子节点（数据页）
└──┴─┴─┴──┘ └──┴─┴─┴──┘
↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑
数据链表（双向指针）
B+树特点：
- 所有数据存储在叶子节点
- 叶子节点形成有序链表
- 非叶子节点只存储索引
- 查询时间复杂度：O(log n)
- 范围查询高效
InnoDB B+树实现
-- B+树高度（通常为3层）
-- 第1层：根节点（1个页）
-- 第2层：内部节点（多个页）
-- 第3层：叶子节点（大量页，存储实际数据）
-- 查看页大小
SHOW VARIABLES LIKE 'innodb_page_size';
-- 默认16KB
-- 计算B+树容量
-- 假设主键为BIGINT（8字节），指针6字节
-- 一个页可存储约 16384 / (8 + 6) ≈ 1170 个索引项
-- 3层B+树可存储约 1170 × 1170 × 16 ≈ 2200万条记录
-- 查看索引统计信息
SHOW INDEX FROM users;
SHOW STATUS LIKE 'Innodb_rows%';
7.2 聚簇索引与二级索引
聚簇索引（主键索引）
-- 聚簇索引：数据行的物理存储顺序与索引顺序一致
CREATE TABLE users (
id INT PRIMARY KEY, -- 聚簇索引
username VARCHAR(50),
email VARCHAR(100),
age INT
) ENGINE=InnoDB;
-- 查询聚簇索引
SELECT * FROM users WHERE id = 1; -- 直接定位到数据页
-- 聚簇索引特点：
-- 1. 一个表只能有一个聚簇索引（主键）
-- 2. 叶子节点存储完整数据行
-- 3. 查询效率最高（一次索引查找）
-- 4. 范围查询高效（叶子节点有序链表）
二级索引（辅助索引）
-- 二级索引：叶子节点存储索引列 + 主键值
CREATE TABLE users (
id INT PRIMARY KEY, -- 聚簇索引
username VARCHAR(50),
email VARCHAR(100),
age INT,
INDEX idx_username (username), -- 二级索引
INDEX idx_age (age) -- 二级索引
) ENGINE=InnoDB;
-- 查询二级索引（回表查询）
SELECT * FROM users WHERE username = 'zhangsan';
-- 1. 在 idx_username 索引中找到 username='zhangsan' 的记录
-- 2. 获取主键 id
-- 3. 回表到聚簇索引，根据 id 查找完整数据行
-- 覆盖索引（无需回表）
SELECT id FROM users WHERE username = 'zhangsan';
-- 查询的列都在索引中，无需回表
SELECT id, username FROM users WHERE username = 'zhangsan';
-- 如果是联合索引 (username, id)，也可以覆盖
回表查询与覆盖索引
-- 创建示例表
CREATE TABLE orders (
order_id INT PRIMARY KEY,
user_id INT,
order_date DATE,
amount DECIMAL(10, 2),
status VARCHAR(20),
INDEX idx_user_date (user_id, order_date)
) ENGINE=InnoDB;
-- 回表查询
SELECT * FROM orders WHERE user_id = 1 AND order_date = '2024-01-01';
-- 1. 在二级索引 idx_user_date 中定位记录
-- 2. 获取 order_id
-- 3. 回表到聚簇索引，获取完整数据行
-- 覆盖索引优化
CREATE INDEX idx_user_date_amount ON orders(user_id, order_date, amount);
SELECT user_id, order_date, amount
FROM orders
WHERE user_id = 1 AND order_date = '2024-01-01';
-- 所有查询列都在索引中，无需回表
7.3 索引类型
普通索引
-- 基础索引
CREATE TABLE users (
id INT PRIMARY KEY,
username VARCHAR(50),
email VARCHAR(100),
INDEX idx_username (username)
) ENGINE=InnoDB;
-- 添加普通索引
CREATE INDEX idx_email ON users(email);
ALTER TABLE users ADD INDEX idx_email (email);
-- 删除索引
DROP INDEX idx_email ON users;
ALTER TABLE users DROP INDEX idx_email;
唯一索引
-- 唯一索引：保证列值唯一
CREATE TABLE users (
id INT PRIMARY KEY,
username VARCHAR(50) UNIQUE, -- 唯一索引
email VARCHAR(100),
UNIQUE KEY uk_email (email) -- 唯一索引
) ENGINE=InnoDB;
-- 添加唯一索引
CREATE UNIQUE INDEX uk_phone ON users(phone);
-- 唯一索引特点：
-- 1. 保证数据唯一性
-- 2. 允许NULL（多个NULL不冲突）
-- 3. 查询效率高（可以使用索引）
主键索引
-- 主键索引：特殊的唯一索引
CREATE TABLE users (
id INT PRIMARY KEY, -- 主键索引
username VARCHAR(50)
) ENGINE=InnoDB;
-- 复合主键
CREATE TABLE order_items (
order_id INT,
product_id INT,
quantity INT,
PRIMARY KEY (order_id, product_id) -- 复合主键
) ENGINE=InnoDB;
-- 主键索引特点：
-- 1. 唯一标识每一行
-- 2. 自动创建（聚簇索引）
-- 3. 只能有一个
-- 4. 不允许NULL
联合索引
-- 联合索引：多个列组成的索引
CREATE TABLE orders (
order_id INT PRIMARY KEY,
user_id INT,
order_date DATE,
amount DECIMAL(10, 2),
INDEX idx_user_date (user_id, order_date) -- 联合索引
) ENGINE=InnoDB;
-- 最左前缀原则
-- 可以使用索引的查询：
SELECT * FROM orders WHERE user_id = 1; -- ✓
SELECT * FROM orders WHERE user_id = 1 AND order_date = '2024-01-01'; -- ✓
SELECT * FROM orders WHERE user_id = 1 AND order_date > '2024-01-01'; -- ✓
-- 不能使用索引的查询：
SELECT * FROM orders WHERE order_date = '2024-01-01'; -- ✗ 违反最左前缀
SELECT * FROM orders WHERE amount = 100; -- ✗ 索引中不包含amount
-- 部分使用索引
SELECT * FROM orders WHERE user_id = 1 AND amount > 100; -- user_id使用索引
全文索引
-- 全文索引：用于全文搜索（中文需要ngram插件）
-- MySQL 5.6+ InnoDB支持全文索引
CREATE TABLE articles (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(200),
content TEXT,
FULLTEXT INDEX ft_title_content (title, content) -- 全文索引
) ENGINE=InnoDB;
-- 全文搜索
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('MySQL database' IN NATURAL LANGUAGE MODE);
-- 布尔模式
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE);
-- +：必须包含 -：必须不包含
-- 查询扩展模式
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('MySQL' WITH QUERY EXPANSION);
-- 全文索引配置
SHOW VARIABLES LIKE 'ft%';
SET GLOBAL ft_min_word_len = 2; -- 最小词长度
空间索引
-- 空间索引：用于地理空间数据
CREATE TABLE locations (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
position GEOMETRY, -- 空间数据类型
SPATIAL INDEX idx_position (position) -- 空间索引
) ENGINE=InnoDB;
-- 插入空间数据
INSERT INTO locations (name, position) VALUES
('Beijing', ST_GeomFromText('POINT(116.4074 39.9042)')),
('Shanghai', ST_GeomFromText('POINT(121.4737 31.2304)'));
-- 空间查询
SELECT name, ST_AsText(position) AS position
FROM locations
WHERE ST_Distance_Sphere(
position,
ST_GeomFromText('POINT(116.4074 39.9042)')
) < 100000; -- 100km范围内
-- 空间函数
SELECT
name,
ST_AsText(position) AS position,
ST_Distance_Sphere(
position,
ST_GeomFromText('POINT(116.4074 39.9042)')
) AS distance
FROM locations;
7.4 索引优化策略
覆盖索引优化
-- 创建联合索引实现覆盖
CREATE TABLE orders (
order_id INT PRIMARY KEY,
user_id INT,
order_date DATE,
amount DECIMAL(10, 2),
status VARCHAR(20)
) ENGINE=InnoDB;
-- 优化前
SELECT user_id, order_date, amount
FROM orders
WHERE user_id = 1 AND order_date = '2024-01-01';
-- 需要回表查询
-- 优化：创建覆盖索引
CREATE INDEX idx_user_date_amount ON orders(user_id, order_date, amount);
-- 查询计划验证
EXPLAIN SELECT user_id, order_date, amount
FROM orders
WHERE user_id = 1 AND order_date = '2024-01-01';
-- Extra: Using index（使用了覆盖索引）
最左前缀原则
-- 联合索引设计
CREATE TABLE users (
id INT PRIMARY KEY,
username VARCHAR(50),
age INT,
gender VARCHAR(10),
city VARCHAR(50),
-- 复合索引：按查询频率和区分度排序
INDEX idx_city_age (city, age, gender)
) ENGINE=InnoDB;
-- 索引设计原则：
-- 1. 区分度高的列放前面
-- 2. 经常查询的列放前面
-- 3. 范围查询的列放后面
-- 可以使用索引的查询
SELECT * FROM users WHERE city = 'Beijing'; -- ✓
SELECT * FROM users WHERE city = 'Beijing' AND age = 25; -- ✓
SELECT * FROM users WHERE city = 'Beijing' AND age > 20; -- ✓
-- 不能使用索引的查询
SELECT * FROM users WHERE age = 25; -- ✗
SELECT * FROM users WHERE gender = 'male'; -- ✗
SELECT * FROM users WHERE age = 25 AND city = 'Beijing'; -- ✗ 顺序不对
索引下推（ICP）
-- 索引下推：MySQL 5.6+ 特性，在索引层过滤数据
CREATE TABLE users (
id INT PRIMARY KEY,
username VARCHAR(50),
age INT,
gender VARCHAR(10),
city VARCHAR(50),
INDEX idx_city_age (city, age)
) ENGINE=InnoDB;
-- 使用索引下推
SELECT * FROM users
WHERE city = 'Beijing' AND age > 20 AND age < 30;
-- age 的条件在索引层过滤，减少回表次数
-- 查看执行计划
EXPLAIN SELECT * FROM users
WHERE city = 'Beijing' AND age > 20;
-- Extra: Using index condition（使用了索引下推）
-- 索引下推优化：
-- 1. 减少回表次数
-- 2. 减少IO操作
-- 3. 提高查询性能
索引选择性
-- 索引选择性：不重复的值比例
-- 计算选择性
SELECT
COUNT(DISTINCT city) / COUNT(*) AS city_selectivity,
COUNT(DISTINCT gender) / COUNT(*) AS gender_selectivity,
COUNT(DISTINCT age) / COUNT(*) AS age_selectivity
FROM users;
-- 越接近1，选择性越高，适合建索引
-- 选择性示例
-- gender: male, female（选择性低，不适合单独建索引）
-- city: Beijing, Shanghai, ...（选择性高，适合建索引）
-- age: 1-100（选择性中，可以建索引）
-- 低选择性列的使用策略
CREATE TABLE users (
id INT PRIMARY KEY,
city VARCHAR(50),
gender VARCHAR(10),
age INT,
-- 单独索引：选择性低，不推荐
INDEX idx_gender (gender),
-- 联合索引：提高选择性
INDEX idx_city_gender (city, gender)
) ENGINE=InnoDB;
7.5 索引失效场景
索引失效情况分析
-- 创建示例表
CREATE TABLE users (
id INT PRIMARY KEY,
username VARCHAR(50),
age INT,
email VARCHAR(100),
created_at TIMESTAMP,
INDEX idx_age (age),
INDEX idx_username (username),
INDEX idx_created (created_at)
) ENGINE=InnoDB;
-- 1. 使用函数或表达式
-- 失效
SELECT * FROM users WHERE YEAR(created_at) = 2024;
SELECT * FROM users WHERE age + 1 = 26;
-- 优化
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
SELECT * FROM users WHERE age = 25;
-- 2. 隐式类型转换
-- 失效
SELECT * FROM users WHERE username = 123; -- 字符串列，数字比较
-- 优化
SELECT * FROM users WHERE username = '123';
-- 3. 前缀模糊查询
-- 失效
SELECT * FROM users WHERE username LIKE '%zhang%';
SELECT * FROM users WHERE username LIKE '%zhang';
-- 优化
SELECT * FROM users WHERE username LIKE 'zhang%'; -- 前缀匹配可以使用索引
-- 4. OR连接的条件
-- 失效（如果不都使用索引）
SELECT * FROM users WHERE age = 25 OR email = 'test@example.com';
-- 如果 age 和 email 都有索引，可以使用 index merge
-- 优化：使用UNION
SELECT * FROM users WHERE age = 25
UNION
SELECT * FROM users WHERE email = 'test@example.com';
-- 5. != 或 <> 操作符
-- 可能失效（取决于数据分布）
SELECT * FROM users WHERE age != 25;
-- 优化：根据数据分布选择
-- 如果大部分age != 25，全表扫描更快
-- 如果少部分age != 25，索引扫描更快
-- 6. IS NULL 或 IS NOT NULL
-- 可以使用索引
SELECT * FROM users WHERE username IS NULL;
-- 7. 负向查询
-- 可能失效
SELECT * FROM users WHERE age NOT IN (20, 21, 22);
-- 优化：使用正向查询
SELECT * FROM users WHERE age < 20 OR age > 22;
-- 8. 违反最左前缀原则
-- 失效
CREATE INDEX idx_age_username ON users(age, username);
SELECT * FROM users WHERE username = 'zhangsan'; -- ✗
-- 优化
SELECT * FROM users WHERE age = 25 AND username = 'zhangsan'; -- ✓
索引失效检测
-- 使用EXPLAIN分析索引使用情况
EXPLAIN SELECT * FROM users WHERE age = 25;
-- 关键字段说明
-- type：访问类型
-- - ALL：全表扫描（最差）
-- - index：索引扫描
-- - range：范围扫描
-- - ref：索引查找
-- - eq_ref：唯一索引查找
-- - const：主键查找（最好）
-- key：实际使用的索引
-- key_len：使用的索引长度
-- rows：预估扫描行数
-- Extra：
-- - Using index：覆盖索引（好）
-- - Using index condition：索引下推
-- - Using filesort：文件排序（差）
-- - Using temporary：临时表（差）
-- 示例分析
EXPLAIN SELECT * FROM users WHERE age = 25;
-- type: ref
-- key: idx_age
-- Extra: NULL
EXPLAIN SELECT * FROM users WHERE age > 20;
-- type: range
-- key: idx_age
EXPLAIN SELECT * FROM users WHERE YEAR(created_at) = 2024;
-- type: ALL
-- key: NULL
-- Extra: Using where
第8章 事务与锁
8.1 ACID特性
原子性（Atomicity）
-- 原子性：事务中的操作要么全部成功，要么全部失败
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
-- 如果中间出错，执行ROLLBACK
-- ROLLBACK;
-- 如果全部成功，执行COMMIT
COMMIT;
-- 原子性实现：Undo Log
-- 修改数据前先记录Undo Log
-- 回滚时根据Undo Log恢复数据
一致性（Consistency）
-- 一致性：事务前后数据满足业务约束
-- 示例：转账业务
CREATE TABLE account (
id INT PRIMARY KEY,
name VARCHAR(50),
balance DECIMAL(10, 2) CHECK (balance >= 0)
) ENGINE=InnoDB;
START TRANSACTION;
-- 检查余额
SELECT balance FROM account WHERE id = 1 FOR UPDATE;
-- 扣款
UPDATE account SET balance = balance - 100 WHERE id = 1;
-- 入账
UPDATE account SET balance = balance + 100 WHERE id = 2;
-- 业务规则验证：余额不能为负
-- 如果违反，ROLLBACK
COMMIT;
-- 一致性实现：
-- 1. 数据库约束（主键、外键、CHECK）
-- 2. 业务逻辑验证
-- 3. 原子性、隔离性、持久性保证
隔离性（Isolation）
-- 隔离性：并发事务之间相互隔离
-- 查看隔离级别
SELECT @@transaction_isolation;
SELECT @@tx_isolation; -- MySQL 5.7-
-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- 全局设置
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
持久性（Durability）
-- 持久性：事务提交后，数据永久保存
-- 持久性实现：Redo Log
-- 1. 修改数据前先写Redo Log
-- 2. 提交事务时将Redo Log持久化到磁盘
-- 3. 根据Redo Log恢复数据
-- Redo Log配置
SHOW VARIABLES LIKE 'innodb_log%';
SHOW VARIABLES LIKE 'innodb_flush_log%';
-- innodb_flush_log_at_trx_commit：
-- 0：每秒刷新（可能丢失1秒数据）
-- 1：每次提交刷新（默认，最安全）
-- 2：每次提交写到缓存，每秒刷新（可能丢失1秒数据）
-- 持久性保证
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 1;
COMMIT; -- 提交后，即使MySQL崩溃，数据也不会丢失
8.2 事务隔离级别
读未提交（READ UNCOMMITTED）
-- 隔离级别：读未提交
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- 事务A
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 1;
-- 未提交
-- 事务B
START TRANSACTION;
SELECT balance FROM account WHERE id = 1;
-- 读到了未提交的数据（脏读）
-- 事务A
ROLLBACK;
-- 事务B
SELECT balance FROM account WHERE id = 1;
-- 数据恢复了，出现了不一致
-- 问题：
-- 1. 脏读：读到未提交的数据
-- 2. 不可重复读：同一条记录两次读取结果不同
-- 3. 幻读：范围查询结果集变化
-- 实际应用：几乎不使用
读已提交（READ COMMITTED）
-- 隔离级别：读已提交（Oracle、PostgreSQL默认）
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 事务A
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 1;
COMMIT;
-- 事务B
START TRANSACTION;
SELECT balance FROM account WHERE id = 1; -- 读取到已提交的数据
-- 事务A
UPDATE account SET balance = balance + 100 WHERE id = 1;
COMMIT;
-- 事务B
SELECT balance FROM account WHERE id = 1; -- 读到新数据，不可重复读
-- 问题：
-- 1. 不可重复读：同一条记录两次读取结果不同
-- 2. 幻读：范围查询结果集变化
-- 实际应用：适用于对一致性要求不高的场景
可重复读（REPEATABLE READ）
-- 隔离级别：可重复读（MySQL默认）
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- 事务A
START TRANSACTION;
SELECT balance FROM account WHERE id = 1; -- 第一次读取
-- 事务B
START TRANSACTION;
UPDATE account SET balance = balance + 100 WHERE id = 1;
COMMIT;
-- 事务A
SELECT balance FROM account WHERE id = 1; -- 第二次读取，结果相同（可重复读）
-- 事务A
COMMIT;
-- 可重复读实现：MVCC（多版本并发控制）
-- 1. 每个事务看到自己的数据快照
-- 2. Read View：事务开始时的数据视图
-- 问题：
-- 1. 幻读：在特殊情况下可能出现
-- 实际应用：大多数OLTP应用（默认）
串行化（SERIALIZABLE）
-- 隔离级别：串行化
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- 事务A
START TRANSACTION;
SELECT * FROM account WHERE balance > 1000 FOR UPDATE; -- 范围查询加锁
-- 事务B
START TRANSACTION;
INSERT INTO account VALUES (100, 'user100', 2000);
-- 阻塞，等待事务A释放锁
-- 事务A
COMMIT;
-- 事务B
-- 继续执行
-- 特点：
-- 1. 最高隔离级别
-- 2. 避免脏读、不可重复读、幻读
-- 3. 隐式锁定范围查询
-- 4. 性能最差
-- 实际应用：极少使用
隔离级别对比
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 性能 | 应用场景 |
|---|---|---|---|---|---|
| READ UNCOMMITTED | ✓ | ✓ | ✓ | 高 | 几乎不用 |
| READ COMMITTED | ✗ | ✓ | ✓ | 中高 | Oracle默认 |
| REPEATABLE READ | ✗ | ✗ | 可能 | 中 | MySQL默认 |
| SERIALIZABLE | ✗ | ✗ | ✗ | 低 | 极少使用 |
8.3 MVCC多版本并发控制
MVCC原理
MVCC（Multi-Version Concurrency Control）：多版本并发控制
核心思想：
1. 每行数据有多个版本
2. 读写不冲突（读不加锁）
3. 通过Undo Log实现版本链
版本链示例：
┌─────┬────────┬──────────────┬────────────┐
│ ID │ Name │ Create Time │ Roll Ptr │
├─────┼────────┼──────────────┼────────────┤
│ 1 │ Alice │ 2024-01-01 │ NULL │ ← 最新版本
│ 1 │ Alice │ 2023-12-01 │ → ↑ │ ← 旧版本
│ 1 │ Alice │ 2023-11-01 │ → ↑ │ ← 更旧版本
└─────┴────────┴──────────────┴────────────┘
Read View
-- Read View：事务开始时的数据视图
-- Read View包含的信息：
-- 1. m_ids：活跃事务ID列表（未提交的事务）
-- 2. min_trx_id：最小活跃事务ID
-- 3. max_trx_id：预分配事务ID（当前最大事务ID+1）
-- 4. creator_trx_id：创建Read View的事务ID
-- 版本可见性判断：
-- 1. 如果被访问版本的 trx_id < min_trx_id：可见（已提交）
-- 2. 如果被访问版本的 trx_id >= max_trx_id：不可见（未来事务）
-- 3. 如果被访问版本的 trx_id 在 m_ids 中：不可见（未提交）
-- 4. 如果被访问版本的 trx_id 不在 m_ids 中：可见（已提交）
-- RC和RR的区别：
-- RC：每次SELECT都生成新的Read View
-- RR：第一次SELECT生成Read View，后续复用
MVCC示例
-- 创建示例表
CREATE TABLE mvcc_test (
id INT PRIMARY KEY,
name VARCHAR(50)
) ENGINE=InnoDB;
INSERT INTO mvcc_test VALUES (1, 'Alice');
-- 事务A
START TRANSACTION;
SELECT * FROM mvcc_test WHERE id = 1; -- 读取到 Alice
-- 事务B
START TRANSACTION;
UPDATE mvcc_test SET name = 'Bob' WHERE id = 1;
COMMIT;
-- 事务A
SELECT * FROM mvcc_test WHERE id = 1; -- 仍然读取到 Alice（可重复读）
-- 事务A看到的是自己开始时的数据快照
-- 事务A
COMMIT;
-- 新事务C
START TRANSACTION;
SELECT * FROM mvcc_test WHERE id = 1; -- 读取到 Bob
8.4 锁机制
锁类型
1. 共享锁（S锁）与排他锁（X锁）
-- 共享锁（Shared Lock，读锁）
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;
-- 允许其他事务加共享锁
-- 不允许其他事务加排他锁
-- 排他锁（Exclusive Lock，写锁）
SELECT * FROM users WHERE id = 1 FOR UPDATE;
-- 不允许其他事务加共享锁或排他锁
-- 示例
-- 事务A
START TRANSACTION;
SELECT * FROM users WHERE id = 1 FOR UPDATE; -- 加X锁
UPDATE users SET age = 26 WHERE id = 1;
-- COMMIT;
-- 事务B
START TRANSACTION;
SELECT * FROM users WHERE id = 1; -- ✓ 可以读取（MVCC快照读）
SELECT * FROM users WHERE id = 1 FOR UPDATE; -- ✗ 阻塞，等待A释放X锁
-- SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE; -- ✗ 阻塞
2. 意向锁
-- 意向锁：表级锁，自动添加
-- IS（意向共享锁）：事务打算在某些行加共享锁
-- IX（意向排他锁）：事务打算在某些行加排他锁
-- 意向锁兼容性
-- IS IX S X
-- IS ✓ ✓ ✓ ✗
-- IX ✓ ✓ ✗ ✗
-- S ✓ ✗ ✓ ✗
-- X ✗ ✗ ✗ ✗
-- 意向锁作用：
-- 1. 快速判断表级锁冲突
-- 2. 不需要遍历所有行锁
-- 查看意向锁
SHOW ENGINE INNODB STATUS;
行锁、间隙锁、临键锁
1. 行锁（Record Lock）
-- 行锁：锁定索引记录
-- 事务A
START TRANSACTION;
UPDATE users SET age = 26 WHERE id = 1; -- 对id=1的记录加X锁
-- COMMIT;
-- 事务B
START TRANSACTION;
UPDATE users SET age = 27 WHERE id = 1; -- 阻塞，等待A释放锁
UPDATE users SET age = 27 WHERE id = 2; -- ✓ 不冲突
-- 行锁特点：
-- 1. 锁定索引记录
-- 2. 如果没有索引，退化为表锁
-- 3. InnoDB支持，MyISAM不支持
2. 间隙锁（Gap Lock）
-- 间隙锁：锁定索引记录之间的间隙（不包括记录本身）
-- 示例数据：id = 1, 5, 10
-- 间隙：(−∞, 1), (1, 5), (5, 10), (10, +∞)
-- 事务A
START TRANSACTION;
SELECT * FROM users WHERE id > 5 AND id < 10 FOR UPDATE;
-- 加间隙锁：(5, 10)
-- 事务B
INSERT INTO users VALUES (6, 'test'); -- ✗ 阻塞，间隙被锁定
INSERT INTO users VALUES (7, 'test'); -- ✗ 阻塞
INSERT INTO users VALUES (15, 'test'); -- ✓ 不在间隙内
-- 间隙锁特点：
-- 1. 只存在于RR隔离级别
-- 2. 防止幻读
-- 3. 只阻塞INSERT操作
3. 临键锁（Next-Key Lock）
-- 临键锁：行锁 + 间隙锁
-- 锁定索引记录及其前面的间隙
-- 示例数据：id = 1, 5, 10
-- 事务A
START TRANSACTION;
SELECT * FROM users WHERE id = 5 FOR UPDATE;
-- 加临键锁：(1, 5] (行锁 + 间隙锁)
-- 事务B
INSERT INTO users VALUES (3, 'test'); -- ✗ 阻塞，在间隙内
INSERT INTO users VALUES (5, 'test'); -- ✗ 阻塞，记录被锁定
INSERT INTO users VALUES (7, 'test'); -- ✓ 不在范围内
-- 临键锁特点：
-- 1. 默认的锁类型（RR级别）
-- 2. 解决幻读问题
-- 3. 左开右闭区间：(prev, current]
4. 锁的优化
-- 1. 减少锁的范围
-- 不推荐
SELECT * FROM users WHERE age > 20 FOR UPDATE; -- 范围大，锁定多行
-- 推荐
SELECT * FROM users WHERE id = 1 FOR UPDATE; -- 精确锁定
-- 2. 缩短锁持有时间
-- 不推荐
START TRANSACTION;
SELECT * FROM users WHERE id = 1 FOR UPDATE;
-- 执行耗时操作（如HTTP请求）
UPDATE users SET age = 26 WHERE id = 1;
COMMIT;
-- 推荐
SELECT * FROM users WHERE id = 1; -- 不加锁
-- 执行耗时操作
START TRANSACTION;
SELECT * FROM users WHERE id = 1 FOR UPDATE;
UPDATE users SET age = 26 WHERE id = 1;
COMMIT;
-- 3. 使用乐观锁
CREATE TABLE products (
id INT PRIMARY KEY,
stock INT,
version INT
) ENGINE=InnoDB;
-- 不使用悲观锁
UPDATE products
SET stock = stock - 1
WHERE id = 1 AND stock >= 1;
-- 使用乐观锁
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 10;
-- 检查affected rows，如果为0说明版本冲突
8.5 死锁检测与处理
死锁示例
-- 死锁：两个事务互相等待对方释放锁
-- 事务A
START TRANSACTION;
UPDATE users SET age = 26 WHERE id = 1; -- 锁定id=1
-- 等待id=2的锁
UPDATE users SET age = 27 WHERE id = 2;
-- 事务B
START TRANSACTION;
UPDATE users SET age = 28 WHERE id = 2; -- 锁定id=2
-- 等待id=1的锁
UPDATE users SET age = 29 WHERE id = 1;
-- 结果：死锁
-- MySQL自动检测并回滚其中一个事务
死锁检测
-- 查看死锁信息
SHOW ENGINE INNODB STATUS\G
-- LATEST DETECTED DEADLOCK 部分：
-- ***************************
-- DEADLOCK DETECTED
-- ***************************
-- (1) TRANSACTION:
-- TRANSACTION 1234, ACTIVE 10 sec starting index read
-- mysql tables in use 1, locked 1
-- LOCK WAIT 2 lock struct(s), heap size 1136
-- ...
-- (2) TRANSACTION:
-- TRANSACTION 1235, ACTIVE 8 sec starting index read
-- ...
-- 查看锁等待
SELECT * FROM information_schema.INNODB_TRX;
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
-- 查看锁状态
SHOW STATUS LIKE 'Innodb_row_lock%';
-- Innodb_row_lock_current_waits：当前等待的锁数量
-- Innodb_row_lock_time：锁总等待时间
-- Innodb_row_lock_time_avg：平均等待时间
-- Innodb_row_lock_time_max：最大等待时间
死锁避免策略
-- 1. 固定加锁顺序
-- 不推荐（容易死锁）
-- 事务A：UPDATE ... WHERE id = 1; UPDATE ... WHERE id = 2;
-- 事务B：UPDATE ... WHERE id = 2; UPDATE ... WHERE id = 1;
-- 推荐（按顺序加锁）
-- 事务A：UPDATE ... WHERE id = 1; UPDATE ... WHERE id = 2;
-- 事务B：UPDATE ... WHERE id = 1; UPDATE ... WHERE id = 2;
-- 2. 一次性加锁
-- 不推荐
SELECT * FROM users WHERE id = 1 FOR UPDATE;
SELECT * FROM users WHERE id = 2 FOR UPDATE;
-- 推荐
SELECT * FROM users WHERE id IN (1, 2) FOR UPDATE;
-- 3. 使用乐观锁
CREATE TABLE products (
id INT PRIMARY KEY,
stock INT,
version INT
) ENGINE=InnoDB;
-- 乐观锁更新
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 10;
-- 应用层检查affected_rows
-- if affected_rows == 0:
-- 版本冲突，重试或提示用户
-- 4. 设置锁超时
SET SESSION innodb_lock_wait_timeout = 5; -- 5秒超时
-- 超时后返回错误：ERROR 1205 (HY000): Lock wait timeout exceeded
-- 5. 使用较低隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- RC级别减少间隙锁，降低死锁概率
死锁处理
-- 死锁处理流程：
-- 1. MySQL自动检测死锁
-- 2. 回滚代价较小的事务
-- 3. 另一个事务继续执行
-- 4. 应用捕获死锁错误并重试
-- 应用层处理（示例）
-- Python伪代码
try:
execute_sql("UPDATE users SET age = 26 WHERE id = 1");
execute_sql("UPDATE users SET age = 27 WHERE id = 2");
commit();
except DeadlockError:
rollback();
sleep(0.1);
retry(); -- 重试
小结
本章深入讲解了MySQL核心机制的三个重要方面：
第6章 - 存储引擎重点：
- 理解InnoDB、MyISAM、Memory引擎的特点和适用场景
- 掌握InnoDB的架构、行格式、缓冲池机制
- 了解存储引擎的选择策略和性能对比
- 能够根据业务需求选择合适的存储引擎
第7章 - 索引机制重点：
- 理解B+树索引的底层原理和结构
- 掌握聚簇索引和二级索引的区别与联系
- 熟练使用各种索引类型（普通、唯一、联合、全文、空间）
- 掌握索引优化策略（覆盖索引、最左前缀、索引下推）
- 了解索引失效的场景和优化方法
第8章 - 事务与锁重点：
- 深入理解ACID特性和实现机制
- 掌握四种事务隔离级别的特点和区别
- 理解MVCC多版本并发控制原理
- 熟练使用各种锁类型（共享锁、排他锁、行锁、间隙锁、临键锁）
- 掌握死锁检测、避免和处理策略
核心要点：
- InnoDB是MySQL默认且最常用的存储引擎，支持事务、行锁、外键
- 索引是提升查询性能的关键，但需要合理设计和使用
- MVCC和锁机制保证了并发场景下的数据一致性和隔离性
- 实际应用中需要权衡性能、一致性、并发性
下章预告： 下一章将学习MySQL性能优化与高可用架构，包括SQL优化、表结构设计、主从复制、分库分表等内容。