mysql从零单排（二）序幕：小林mysql之旅小林是一名准备面试后端工程师的应届生，第一幕：基础筑基篇 - 数据库

序幕：小林mysql之旅

小林是一名准备面试后端工程师的应届生，

第一幕：基础筑基篇 - 数据库的根基

1.1 数据库的三范式：从混乱到秩序

故事背景：小林设计了一个电商数据库，最初所有数据塞在一张表里，导致大量冗余...

-- ❌ 第一版：混沌初开（未规范化）
CREATE TABLE chaotic_orders (
    order_id INT,
    customer_name VARCHAR(50),      -- 重复存储
    customer_phone VARCHAR(20),      -- 重复存储
    product_name VARCHAR(100),       -- 重复存储
    product_price DECIMAL(10,2),     -- 重复存储
    supplier_name VARCHAR(50),       -- 重复存储
    quantity INT,
    total_amount DECIMAL(10,2)
);
-- 问题：客户改手机号要改100条记录，供应商改名要改1000条记录

第一范式（1NF）：原子性

-- ✅ 第二版：确保每列不可再分
CREATE TABLE orders_1nf (
    order_id INT PRIMARY KEY,
    customer_name VARCHAR(50),      -- 原子值
    customer_phone VARCHAR(20),     -- 不是"手机1,手机2"
    product_name VARCHAR(100),
    product_price DECIMAL(10,2),
    supplier_name VARCHAR(50),
    quantity INT,
    total_amount DECIMAL(10,2)
);
-- 所有字段都是原子值，无重复组

第二范式（2NF）：消除部分依赖

-- ✅ 第三版：消除非主属性对主键的部分依赖
-- 主键是(order_id, product_id)，但customer只依赖order_id

-- 订单表
CREATE TABLE orders_2nf (
    order_id INT PRIMARY KEY,
    customer_id INT,                -- 外键关联
    order_date DATETIME,
    total_amount DECIMAL(10,2)
);

-- 订单明细表（完全依赖联合主键）
CREATE TABLE order_items_2nf (
    order_id INT,
    product_id INT,
    quantity INT,
    unit_price DECIMAL(10,2),        -- 依赖product_id
    PRIMARY KEY (order_id, product_id)
);

第三范式（3NF）：消除传递依赖

-- ✅ 第四版：消除传递依赖（A→B→C，则A应直接关联C）

-- 客户表（独立存储，避免每个订单重复客户信息）
CREATE TABLE customers_3nf (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50),
    customer_phone VARCHAR(20),
    customer_email VARCHAR(100)
    -- 不再存储"客户等级描述"（等级→描述是传递依赖）
);

-- 产品表（供应商信息剥离）
CREATE TABLE products_3nf (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    product_price DECIMAL(10,2),
    supplier_id INT                 -- 外键，不直接存供应商名
);

-- 供应商表（独立存储）
CREATE TABLE suppliers_3nf (
    supplier_id INT PRIMARY KEY,
    supplier_name VARCHAR(50),
    supplier_address VARCHAR(200)
);

-- 订单表（最精简）
CREATE TABLE orders_3nf (
    order_id INT PRIMARY KEY,
    customer_id INT,                -- 只存ID，不存冗余信息
    order_date DATETIME,
    FOREIGN KEY (customer_id) REFERENCES customers_3nf(customer_id)
);

范式化流程图：

flowchart TD
    A[原始大表<br/>所有字段混杂] --> B{满足1NF?}
    B -->|否| C[拆分重复组<br/>确保原子性]
    C --> D{满足2NF?}
    D -->|否| E[消除部分依赖<br/>拆分联合主键表]
    E --> F{满足3NF?}
    F -->|否| G[消除传递依赖<br/>独立实体表]
    G --> H[BCNF/4NF/5NF<br/>更高级规范化]
    B -->|是| D
    D -->|是| F
    F -->|是| I[规范化完成]
    
    H --> I
    
    style A fill:#ff9999
    style I fill:#90EE90
    style C fill:#ffcc99
    style E fill:#ffcc99
    style G fill:#ffcc99

反范式化：实际生产中为了查询性能，会故意冗余（空间换时间）。

灵活把握
- 第一：不用完全按照范式设计，尽快完成功能投入市场验证产品更重要，产品有市场了，回过头来规范设计；
- 第二：公司有要求的就按照公司的范式设计，胳膊拗不过大腿；
- 第三：空间和时间是对等的，灵活调控。

1.2 MySQL存储引擎：选择合适的"心脏"

小林发现，同样的表结构，不同的"引擎"表现截然不同...

对比维度	InnoDB	MyISAM
存储架构	聚簇索引（Clustered Index）数据文件和索引文件在一起（.ibd）	非聚簇索引（Non-Clustered）数据（.MYD）和索引（.MYI）分离
事务支持	✅ 完全支持 ACID 支持 `BEGIN` / `COMMIT` / `ROLLBACK`	❌ 不支持事务每条语句自动提交，无法回滚
锁机制	行级锁（Row-Level Locking）只锁定冲突行，并发度高	表级锁（Table-Level Locking）读写互相阻塞，并发度低
并发性能	高并发读写优秀适合 OLTP（在线事务处理）	读密集型场景尚可写操作会锁全表，并发差
崩溃恢复	✅ 支持崩溃恢复通过 Redo Log 保证数据完整性	❌ 易损坏，难恢复崩溃后可能丢失数据，需修复工具
外键约束	✅ 支持外键（Foreign Keys）保证参照完整性	❌ 不支持外键需应用层保证数据一致性
MVCC	✅ 支持多版本并发控制实现非阻塞读（快照读）	❌ 不支持 MVCC 读操作也会加共享锁
索引特性	支持自适应哈希索引支持全文索引（5.6+，功能较弱）	支持全文索引（成熟强大）支持压缩索引
主键要求	必须有主键（无则自动生成 6 字节的隐藏主键 `DB_ROW_ID`）	可以没有主键
索引缓存	缓存数据和索引（Buffer Pool）内存利用率高	只缓存索引（Key Cache）数据依赖 OS 缓存
表空间管理	支持独立表空间（`innodb_file_per_table`）支持通用表空间	每个表三个文件：.frm/.MYD/.MYI
数据压缩	支持透明页压缩（Transparent Page Compression）	支持表压缩（myisampack），压缩后只读
统计信息	实时统计信息（基于采样），可能不准确需 `ANALYZE TABLE`	统计信息准确，保存于索引文件中
计数效率	`SELECT COUNT(*)` 需要全表扫描或索引扫描慢	`SELECT COUNT(*)` 直接从元数据读取极快
自增列	自增值持久化到内存，重启不丢失（8.0+）	自增值存储在数据文件中
热备份	✅ 支持热备份（XtraBackup、MySQL Enterprise Backup）	❌ 需锁表或冷备份（mysqldump + 锁表）
复制支持	支持行级复制（Row-Based Replication）精确安全	仅支持语句级复制（Statement-Based）可能有主从不一致
内存占用	较高（Buffer Pool、Redo Log Buffer、自适应哈希等）	较低（仅 Key Buffer）
磁盘空间	通常更大（聚簇索引、Undo 空间、双写缓冲）	通常更小（紧凑存储，可压缩）
插入顺序	按主键顺序插入最优随机主键导致页分裂	插入始终在文件末尾追加速度快
删除更新	删除标记为软删除，空间不立即释放需 `OPTIMIZE TABLE` 或后台 Purge	删除立即释放空间（标记删除，可复用）
查询缓存	8.0 已移除查询缓存	支持查询缓存（但 8.0 也已移除）
地理空间数据	支持空间索引（SPATIAL INDEX，5.7+ 功能增强）	原生支持空间索引（成熟）
临时表	内部临时表优先使用 MEMORY 引擎大数据量转 MyISAM（8.0 前）	常用于内部临时表（已逐渐被 InnoDB 替代）
默认引擎	✅ MySQL 5.5+ 默认引擎	MySQL 5.1 及之前默认引擎
社区支持	✅ 官方主推，持续优化	⚠️ 逐渐边缘化，功能不再增强
适用场景	金融交易、电商订单、用户系统需要事务、高并发、数据安全的场景	日志系统、数据仓库、只读报表读多写少、不需要事务的场景

-- 查看支持的引擎
SHOW ENGINES;

-- 经典对比实验
-- 场景1：论坛帖子表（读多写少，需要全文检索）
CREATE TABLE forum_posts_myisam (
    post_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT INDEX ft_content (title, content)  -- MyISAM全文检索强
) ENGINE=MyISAM;

-- 场景2：银行交易表（必须事务安全）
CREATE TABLE bank_transactions (
    transaction_id BIGINT PRIMARY KEY,
    account_id INT,
    amount DECIMAL(18,2),
    status TINYINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_account_time (account_id, created_at)
) ENGINE=InnoDB;  -- 默认且推荐

InnoDB vs MyISAM 深度对比：

-- 事务支持演示（InnoDB）
START TRANSACTION;
UPDATE bank_transactions SET amount = amount - 1000 WHERE account_id = 1;
UPDATE bank_transactions SET amount = amount + 1000 WHERE account_id = 2;
-- 模拟断电：ROLLBACK自动回滚，数据安全

-- MyISAM无事务，断电可能数据损坏

-- 行级锁 vs 表级锁
-- InnoDB：UPDATE时只锁一行，其他行可并发修改
UPDATE bank_transactions SET status = 1 WHERE transaction_id = 10001;
-- 并发：另一个事务可修改 transaction_id = 10002

-- MyISAM：UPDATE时锁整个表，其他查询阻塞

存储引擎选择流程图：

flowchart TD
    A[业务场景分析] --> B{需要事务?}
    B -->|是| C[InnoDB<br/>唯一选择]
    B -->|否| D{读多写少?}
    
    D -->|是| E{需要全文检索?}
    D -->|否| C
    
    E -->|是| F[MyISAM<br/>或Sphinx/ES]
    E -->|否| G{数据可重建?}
    
    G -->|是| H[Memory<br/>纯内存表]
    G -->|否| C
    
    C --> I[默认推荐<br/>99%场景适用]
    F --> J[日志/归档场景]
    H --> K[临时计算/缓存]
    
    style C fill:#90EE90
    style I fill:#90EE90

1.3 数据类型选择：varchar vs char & 时间类型

场景：小林设计用户表，纠结用VARCHAR还是CHAR...

-- CHAR vs VARCHAR 存储实验
CREATE TABLE char_test (
    -- CHAR(10)：固定10字节，不足补空格，查询快
    char_col CHAR(10),
    -- VARCHAR(10)：变长，1-2字节存长度+实际内容，省空间
    varchar_col VARCHAR(10)
) ENGINE=InnoDB;

-- 存储'hello'：
-- CHAR(10): 'hello     ' + 10字节 = 10字节（无长度前缀）
-- VARCHAR(10): 1字节长度前缀 + 5字节内容 = 6字节

-- 性能对比
-- 场景1：手机号/身份证号（定长）→ CHAR
CREATE TABLE users_phone (
    phone CHAR(11) PRIMARY KEY,  -- 定长，索引紧凑
    name VARCHAR(50)               -- 变长，省空间
);

-- 场景2：用户名/地址（变长）→ VARCHAR
CREATE TABLE users_profile (
    username VARCHAR(30),          -- 30是上限，实际平均10字符
    bio VARCHAR(500),              -- 简介，大部分很短
    address VARCHAR(200)
);

DATETIME vs TIMESTAMP：

-- 时间类型选择
CREATE TABLE events (
    -- DATETIME：8字节，'1000-01-01'到'9999-12-31'，无时区概念
    -- 适用：生日、历史日期、不需要时区转换的场景
    birthday DATETIME,
    scheduled_time DATETIME,
    
    -- TIMESTAMP：4字节，'1970-01-01'到'2038-01-19'，自动时区转换
    -- 适用：记录创建/更新时间，跨时区应用
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- 注意：2038年问题，未来项目建议DATETIME或BIGINT存毫秒
    future_proof BIGINT  -- 存Unix时间戳毫秒
);

-- 时区转换演示
SET time_zone = '+00:00';  -- UTC
INSERT INTO events (created_at) VALUES (NOW());  -- 存储UTC时间

SET time_zone = '+08:00';  -- 北京
SELECT created_at FROM events;  -- 自动显示为北京时间

1.4 IN vs EXISTS：半连接的抉择

故事：小林要查"有订单的用户"，两种写法结果相同，性能天差地别...

场景	推荐方案	关键原因
子查询结果集小	`IN`	物化后哈希查找，O(1) 定位
子查询结果集大	`EXISTS`	半连接优化，找到即返回，避免物化
`NOT IN` vs `NOT EXISTS`	NOT EXISTS	`NOT IN` 有 NULL 陷阱
关联子查询（依赖外表）	`EXISTS`	天然支持相关子查询
MySQL 5.6+ 自动优化	两者接近	优化器自动将 `IN` 转为 SEMI JOIN

-- 准备数据：100万用户，1000万订单
CREATE TABLE users_demo (
    user_id INT PRIMARY KEY,
    username VARCHAR(50)
);

CREATE TABLE orders_demo (
    order_id BIGINT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10,2),
    INDEX idx_user_id (user_id)
);

-- ❌ 低效：IN + 子查询（MySQL 5.6之前会物化子查询）
SELECT * FROM users_demo 
WHERE user_id IN (
    SELECT user_id FROM orders_demo WHERE amount > 1000
);
-- 旧版本：先执行子查询，生成临时表，再JOIN，内存消耗大

-- ✅ 高效：EXISTS（半连接，找到即返回，不生成临时表）
SELECT * FROM users_demo u
WHERE EXISTS (
    SELECT 1 FROM orders_demo o 
    WHERE o.user_id = u.user_id AND o.amount > 1000
);
-- 原理：对外表每行，检查子查询是否有匹配，有则立即返回true

-- MySQL 5.6+优化：IN会自动转为SEMI JOIN，性能接近EXISTS
-- 但NOT IN和NOT EXISTS仍有区别

IN vs EXISTS 选择流程图：

flowchart TD
    A[子查询场景] --> B{子查询表大小?}
    B -->|子查询小| C[IN<br/>适合小表驱动]
    B -->|子查询大| D{是否NOT?}
    
    D -->|NOT IN| E[可能全NULL问题<br/>慎用]
    D -->|NOT EXISTS| F[NOT EXISTS<br/>安全可靠]
    D -->|普通EXISTS| G[EXISTS<br/>大表驱动优化]
    
    C --> H[MySQL5.6+自动优化为SEMI JOIN]
    F --> I[关联子查询<br/>逐行验证]
    G --> I
    
    style C fill:#90EE90
    style F fill:#90EE90
    style E fill:#ff9999

1.5 DROP vs DELETE vs TRUNCATE：删除的三种命运

-- 准备测试表
CREATE TABLE test_data (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
) ENGINE=InnoDB;

INSERT INTO test_data (name) VALUES ('A'), ('B'), ('C');

-- 1. DELETE：逐行删除，可回滚，慢，保留自增值
START TRANSACTION;
DELETE FROM test_data WHERE id = 1;
-- 或 DELETE FROM test_data;  -- 逐行删100万行
ROLLBACK;  -- 可以恢复
-- 特点：产生大量undo log，自增值不重置，触发器触发

-- 2. TRUNCATE：重置表，快，不可回滚（DDL）
TRUNCATE TABLE test_data;
-- 特点：删除所有行，重置自增值为1，不触发触发器，立即释放空间（MyISAM）
-- InnoDB：如果表被外键引用或启用了innodb_file_per_table，行为略有不同

-- 3. DROP：删除整个表结构+数据+索引（DDL）
DROP TABLE test_data;
-- 特点：最彻底，表对象消失，所有依赖报错

删除操作对比流程图：

flowchart TD
    A[需要删除数据] --> B{删除范围?}
    B -->|部分行| C[DELETE + WHERE<br/>事务安全可回滚]
    B -->|全部行| D{保留表结构?}
    
    D -->|是| E{需要回滚?}
    D -->|否| F[DROP TABLE<br/>彻底删除]
    
    E -->|是| G[DELETE全表<br/>慢但可回滚]
    E -->|否| H[TRUNCATE<br/>快不可回滚]
    
    C --> I[产生Undo Log<br/>触发器执行]
    G --> J[产生大量Undo<br/>自增值保留]
    H --> K[重置自增值<br/>不触发触发器]
    F --> L[删除元数据<br/>释放文件]
    
    style C fill:#90EE90
    style H fill:#90EE90
    style F fill:#ff9999

1.6 存储过程：封装与争议

-- 存储过程：将业务逻辑封装在数据库层
DELIMITER //

CREATE PROCEDURE TransferMoney(
    IN from_account INT,
    IN to_account INT,
    IN amount DECIMAL(18,2),
    OUT result_message VARCHAR(100)
)
BEGIN
    DECLARE from_balance DECIMAL(18,2);
    
    -- 开启事务
    START TRANSACTION;
    
    -- 检查余额
    SELECT balance INTO from_balance 
    FROM accounts WHERE account_id = from_account FOR UPDATE;
    
    IF from_balance < amount THEN
        SET result_message = '余额不足';
        ROLLBACK;
    ELSE
        -- 扣款
        UPDATE accounts SET balance = balance - amount 
        WHERE account_id = from_account;
        
        -- 入账
        UPDATE accounts SET balance = balance + amount 
        WHERE account_id = to_account;
        
        COMMIT;
        SET result_message = '转账成功';
    END IF;
END //

DELIMITER ;

-- 调用
CALL TransferMoney(1001, 1002, 500.00, @msg);
SELECT @msg;

优缺点分析：

flowchart TD
    A[存储过程决策] --> B{团队技术栈?}
    B -->|DBA强| C[优点集中<br/>适合复杂事务]
    B -->|开发强| D[缺点明显<br/>推荐应用层处理]
    
    C --> E[减少网络往返<br/>封装复杂逻辑<br/>预编译性能<br/>权限控制细粒度]
    D --> F[调试困难<br/>版本控制难<br/>迁移性差<br/>扩展性受限]
    
    E --> G[金融核心交易<br/>复杂报表计算]
    F --> H[互联网高并发<br/>微服务架构]
    
    style C fill:#90EE90
    style D fill:#ffcc99

1.7 并发问题：从混乱到可控

-- 并发问题演示
CREATE TABLE inventory (
    product_id INT PRIMARY KEY,
    stock INT
);
INSERT INTO inventory VALUES (1, 100);

-- 场景：两个会话同时扣减库存
-- 会话A：读取stock=100 → 计算100-1=99 → 更新（此时会话B已改为90）
-- 会话B：读取stock=100 → 计算100-10=90 → 更新
-- 结果：A的更新被覆盖（丢失更新）

-- 解决方案1：悲观锁（先锁后读）
BEGIN;
SELECT stock FROM inventory WHERE product_id = 1 FOR UPDATE;  -- 加X锁
-- 其他会话阻塞等待
UPDATE inventory SET stock = stock - 1 WHERE product_id = 1;
COMMIT;

-- 解决方案2：乐观锁（版本号）
ALTER TABLE inventory ADD COLUMN version INT DEFAULT 0;

UPDATE inventory 
SET stock = stock - 1, version = version + 1 
WHERE product_id = 1 AND version = 0;  -- 假设读取时version=0

-- 影响行数为0则说明被修改，需重试

1.8 B-Tree索引实现：MyISAM vs InnoDB

-- 聚簇索引 vs 非聚簇索引
-- InnoDB：主键是聚簇索引，叶子节点存整行数据
CREATE TABLE clustered_index_demo (
    id INT PRIMARY KEY,  -- 聚簇索引：数据按id顺序物理存储
    name VARCHAR(50),
    INDEX idx_name (name)  -- 二级索引：叶子存id值
) ENGINE=InnoDB;

-- 查询：SELECT * WHERE name = 'Alice'
-- 1. 查idx_name得id=5
-- 2. 查聚簇索引得完整数据（回表）

-- MyISAM：所有索引都是非聚簇，叶子存行指针（文件偏移）
CREATE TABLE non_clustered_demo (
    id INT PRIMARY KEY,
    name VARCHAR(50)
) ENGINE=MyISAM;
-- 主键索引和数据分离，.MYD存数据，.MYI存索引

索引结构对比流程图：

flowchart TD
    A[索引查询] --> B{存储引擎?}
    B -->|InnoDB| C[聚簇索引结构]
    B -->|MyISAM| D[非聚簇索引结构]
    
    C --> E[主键索引叶子<br/>存整行数据]
    E --> F[二级索引叶子<br/>存主键值]
    F --> G[可能需要回表<br/>查聚簇索引]
    
    D --> H[所有索引叶子<br/>存行指针ROWID]
    H --> I[直接定位数据文件<br/>无需二次查找]
    
    C --> J[范围查询快<br/>插入需维护顺序]
    D --> K[插入快<br/>主键非单调也高效]
    
    style E fill:#90EE90
    style F fill:#ffcc99
    style H fill:#ffcc99

第二幕：事务篇 - 并发的艺术

2.1 事务与ACID：银行转账的可靠性

-- 事务演示：ACID特性
BEGIN;  -- 或 START TRANSACTION

-- A：原子性（Atomicity）- 要么全成功，要么全失败
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
-- 如果第二步失败，第一步自动回滚

-- C：一致性（Consistency）- 转账前后总金额不变
-- 数据库约束保证：balance >= 0 检查

-- I：隔离性（Isolation）- 并发事务互不干扰
-- 见下文隔离级别

-- D：持久性（Durability）- 提交后即使断电也不丢失
-- redo log保证

COMMIT;  -- 或 ROLLBACK

2.2 四种隔离级别：从"脏"到"净"

-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;  -- 最低
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;     -- Oracle默认
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;    -- MySQL默认
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;       -- 最高

-- 脏读（Dirty Read）- READ UNCOMMITTED特有
-- 事务A修改未提交，事务B就读到了（可能A会回滚）

-- 不可重复读（Non-repeatable Read）- READ COMMITTED问题
-- 事务A两次读同一行，中间事务B修改并提交，A两次结果不同

-- 幻读（Phantom Read）- REPEATABLE READ部分解决
-- 事务A两次范围查询，中间事务B插入新行，A第二次多出行
-- InnoDB通过MVCC+间隙锁解决

隔离级别选择流程图：

flowchart TD
    A[选择隔离级别] --> B{一致性要求?}
    B -->|极高| C[SERIALIZABLE<br/>完全串行化]
    B -->|高| D[REPEATABLE READ<br/>MySQL默认<br/>解决脏读/不可重复读]
    B -->|中| E[READ COMMITTED<br/>Oracle默认<br/>解决脏读]
    B -->|低| F[READ UNCOMMITTED<br/>基本不用]
    
    D --> G{是否有范围查询?}
    G -->|是| H[间隙锁防幻读<br/>可能性能下降]
    G -->|否| I[行锁足够<br/>性能较好]
    
    E --> J[每次读最新快照<br/>不可重复读可接受]
    
    C --> K[所有SELECT加锁<br/>性能最差]
    
    style D fill:#90EE90
    style E fill:#90EE90
    style C fill:#ff9999
    style F fill:#ff9999

2.3 MVCC：多版本并发控制的魔法

-- MVCC原理：每行数据有隐藏列
-- DB_TRX_ID：最后修改的事务ID
-- DB_ROLL_PTR：回滚指针，指向undo log
-- DB_ROW_ID：行ID（无主键时自动生成）

-- 创建Read View（快照）决定可见性
-- READ COMMITTED：每次SELECT生成新Read View
-- REPEATABLE READ：事务开始时生成Read View，全程不变

-- 可见性规则（简化）：
-- 1. 数据版本DB_TRX_ID = 当前事务ID → 可见（自己改的）
-- 2. DB_TRX_ID < min_trx_id（已提交）→ 可见
-- 3. DB_TRX_ID > max_trx_id（将来事务）→ 不可见
-- 4. min_trx_id <= DB_TRX_ID <= max_trx_id → 检查m_ids列表

MVCC工作流程图：

flowchart TD
    A[事务开始] --> B[生成Read View<br/>记录活跃事务ID列表]
    B --> C[SELECT查询]
    C --> D{数据版本<br/>DB_TRX_ID?}
    
    D -->|当前事务ID| E[可见<br/>自己修改的]
    D -->|< min_trx_id| F[可见<br/>已提交事务]
    D -->|> max_trx_id| G[不可见<br/>将来事务]
    D -->|在范围内| H{在m_ids列表?}
    
    H -->|是<br/>未提交| G
    H -->|否<br/>已提交| F
    
    G --> I[沿ROLL_PTR<br/>查Undo Log旧版本]
    I --> D
    
    E --> J[返回数据]
    F --> J
    
    style E fill:#90EE90
    style F fill:#90EE90
    style G fill:#ffcc99

2.4 快照读 vs 当前读

-- 快照读（Snapshot Read）：普通SELECT，不加锁，读历史版本
SELECT * FROM accounts WHERE id = 1;  -- MVCC快照读，无锁

-- 当前读（Current Read）：读最新版本，加锁
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;  -- X锁
SELECT * FROM accounts WHERE id = 1 LOCK IN SHARE MODE;  -- S锁
-- INSERT/UPDATE/DELETE也是当前读（先读再写）

-- 幻读问题演示
BEGIN;  -- 事务A，RR隔离级别
SELECT * FROM accounts WHERE id > 10;  -- 快照读，假设返回0行（MVCC）

-- 事务B插入id=15并提交

-- 事务A当前读
SELECT * FROM accounts WHERE id > 10 FOR UPDATE;  -- 返回1行！幻读
-- 因为FOR UPDATE是当前读，读最新数据

-- 解决：RR级别下FOR UPDATE会加间隙锁，阻止B插入，但MySQL优化后可能不加
-- 彻底解决：SERIALIZABLE或应用程序加锁

第三幕：索引篇 - 性能的基石

3.1 索引的代价与收益

-- 索引不是越多越好！
-- 成本1：空间（索引文件可能比数据大）
-- 成本2：写入慢（每次INSERT/UPDATE/DELETE要维护所有索引）
-- 成本3：优化器选择困难（索引多可能选错）

-- 适合建索引的列：
CREATE TABLE indexing_guide (
    -- 1. 高频WHERE条件
    user_id INT,  -- 经常 WHERE user_id = ?
    INDEX idx_user_id (user_id),
    
    -- 2. 外键列（自动建索引，但MySQL不会自动建，需手动）
    order_id INT,
    INDEX idx_order_id (order_id),
    
    -- 3. 排序/分组列
    created_at DATETIME,
    INDEX idx_created_at (created_at),  -- ORDER BY created_at
    
    -- 4. 覆盖查询（查询列全在索引中）
    status TINYINT,
    INDEX idx_status_created (status, created_at)  -- 覆盖 SELECT status, created_at
);

-- 不适合建索引：
-- 1. 选择性低的列（性别：男/女，各占50%）
-- 2. 很少查询的列
-- 3. 频繁更新的列（每次更新重建索引树）
-- 4. 大文本/大对象列（用前缀索引或全文索引）

3.2 索引类型全景

-- 1. 主键索引（聚簇索引）
PRIMARY KEY (id)

-- 2. 唯一索引（值唯一，允许NULL）
UNIQUE KEY uk_email (email)

-- 3. 普通索引（非唯一）
KEY idx_name (name)

-- 4. 组合索引（最左前缀）
KEY idx_a_b_c (a, b, c)  -- 支持(a), (a,b), (a,b,c)，不支持(b), (c), (b,c)

-- 5. 前缀索引（长字符串）
KEY idx_content_prefix (content(20))  -- 只索引前20字符

-- 6. 全文索引（MyISAM原生，InnoDB 5.6+支持）
FULLTEXT INDEX ft_title_content (title, content) WITH PARSER ngram

-- 7. 空间索引（GIS数据）
SPATIAL INDEX idx_location (location)

-- 8. 函数索引（MySQL 8.0.13+）
KEY idx_func ((LOWER(email)))  -- 索引小写转换后的值

3.3 聚簇索引 vs 非聚簇索引

-- 聚簇索引：数据行和索引在一起，InnoDB主键
-- 优点：范围查询极快（数据物理连续），主键查找直接得数据
-- 缺点：插入非顺序主键需页分裂，二级索引需回表

-- 非聚簇索引：索引和数据分离，MyISAM所有索引
-- 优点：插入快，主键修改不影响数据位置
-- 缺点：任何查询都要二次查找（索引→数据文件）

-- InnoDB二级索引"回表"问题
SELECT * FROM orders WHERE user_id = 10086;
-- 1. 查idx_user_id得主键集合[1,5,9,12...]
-- 2. 对每个主键查聚簇索引得完整行（回表，随机IO）

-- 覆盖索引避免回表
SELECT user_id, order_id, status FROM orders WHERE user_id = 10086;
-- 如果索引是idx_user_id_status (user_id, status, order_id) 或包含这些列
-- Extra: Using index，无需回表

3.4 为什么用B+树？

flowchart TD
    A[索引结构选择] --> B{需求分析}
    B --> C[磁盘IO优化<br/>减少树高度]
    B --> D[范围查询高效<br/>顺序访问]
    B --> E[插入删除平衡<br/>不频繁重组]
    
    C --> F[B+树<br/>多叉树，矮胖结构]
    D --> F
    E --> F
    
    F --> G[特点1：非叶子节点<br/>只存键值，不存数据<br/>一页存更多键]
    F --> H[特点2：叶子节点<br/>链表连接<br/>范围扫描快]
    F --> I[特点3：所有查询<br/>都到叶子层<br/>稳定O(logN)]
    
    G --> J[相比B树<br/>B树非叶子也存数据<br/>扇出少，树更高]
    H --> K[相比Hash<br/>Hash范围查询需全扫<br/>B+树顺序IO]
    I --> L[相比二叉树<br/>红黑树高度大<br/>磁盘IO多]
    
    style F fill:#90EE90
    style G fill:#90EE90
    style H fill:#90EE90

3.5 联合索引与最左前缀

-- 联合索引结构：按列顺序排序的多级索引
ALTER TABLE orders ADD KEY idx_user_status_created (user_id, status, created_at);

-- 索引排序逻辑：先按user_id，user_id相同按status，都相同按created_at

-- ✅ 能用上索引：
WHERE user_id = 1;  -- 用第1列
WHERE user_id = 1 AND status = 2;  -- 用第1,2列
WHERE user_id = 1 AND status = 2 AND created_at > '2024-01-01';  -- 全用
WHERE user_id = 1 AND created_at > '2024-01-01';  -- 用第1列，第3列跳过第2列，只能第1列

-- ❌ 不能用：
WHERE status = 2;  -- 跳过第1列
WHERE created_at > '2024-01-01';  -- 跳过前2列
WHERE user_id > 1 AND status = 2;  -- 第1列范围，第2列无法精确匹配（可能部分用）

-- 优化：调整列顺序，让等值查询在前，范围查询在后
-- 如果查询是 WHERE user_id = ? AND created_at BETWEEN ? AND ?
-- 建 (user_id, created_at) 比 (user_id, status, created_at) 更好

3.6 覆盖索引与回表

-- 回表：通过二级索引找到主键，再查聚簇索引得数据
-- 覆盖索引：查询列全在二级索引中，无需回表

-- 示例表
CREATE TABLE cover_index_demo (
    id INT PRIMARY KEY,
    user_id INT,
    status TINYINT,
    amount DECIMAL(10,2),
    created_at DATETIME,
    INDEX idx_user_status (user_id, status)  -- 不包含amount, created_at
);

-- ❌ 需要回表：SELECT * 或包含不在索引中的列
EXPLAIN SELECT * FROM cover_index_demo WHERE user_id = 1 AND status = 1;
-- Extra: NULL（需要回表查amount, created_at）

-- ✅ 覆盖索引：只选索引中的列
EXPLAIN SELECT user_id, status FROM cover_index_demo WHERE user_id = 1;
-- Extra: Using index（覆盖索引）

-- 优化技巧：添加包含列（MySQL 8.0.13+）
ALTER TABLE cover_index_demo 
DROP INDEX idx_user_status,
ADD INDEX idx_user_status_cover (user_id, status, amount, created_at);
-- 现在SELECT user_id, status, amount, created_at都无需回表
-- 代价：索引变大，维护成本增加

3.7 索引失效场景

-- 1. 函数/运算导致失效
WHERE YEAR(created_at) = 2024  -- 失效
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'  -- 有效

-- 2. 类型隐式转换
WHERE phone = 13800138000  -- phone是VARCHAR，转数字，失效
WHERE phone = '13800138000'  -- 有效

-- 3. 前导模糊查询
WHERE name LIKE '%张%'  -- 失效（无法定位起点）
WHERE name LIKE '张%'   -- 有效（可以用索引定位'张'）

-- 4. OR条件部分无索引
WHERE user_id = 1 OR status = 2  
-- 如果status无索引，可能全表扫描（MySQL 5.7+索引合并优化可能改善）

-- 5. 不等于/非空判断
WHERE status != 1       -- 可能失效（选择性高时可能用）
WHERE status IS NOT NULL -- 可能失效

-- 6. 索引列参与计算
WHERE id + 1 = 100  -- 失效
WHERE id = 99       -- 有效

3.8 自增主键的奥秘

-- 为什么推荐自增主键？

-- 1. 插入性能：顺序插入，减少页分裂
-- UUID主键：随机插入，频繁页分裂，碎片多
CREATE TABLE uuid_pk (
    id CHAR(36) PRIMARY KEY DEFAULT (UUID()),  -- 随机
    data VARCHAR(100)
);

CREATE TABLE auto_pk (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,  -- 顺序
    data VARCHAR(100)
);

-- 2. 空间效率：二级索引叶子存主键值，主键越短越好
-- BIGINT 8字节 vs UUID 36字节，二级索引节省大量空间

-- 3. 查询效率：整型比较比字符串快

-- 例外场景用业务主键：
-- 1. 分库分表需要全局唯一（用雪花ID等）
-- 2. 数据归档，自增ID可能冲突（用时间+序列）

第四幕：锁机制篇 - 并发的守护者

4.1 锁的分类体系

-- 按粒度分：
-- 表锁：LOCK TABLES t READ/WRITE，粒度大，并发低，MyISAM用
-- 页锁：BDB引擎用，已淘汰
-- 行锁：InnoDB用，粒度小，并发高

-- 按模式分：
-- 共享锁（S锁）：SELECT ... LOCK IN SHARE MODE，读共享
-- 排他锁（X锁）：SELECT ... FOR UPDATE / INSERT/UPDATE/DELETE，写独占

-- 按使用方式分：
-- 乐观锁：版本号/CAS，应用层实现
-- 悲观锁：数据库锁，FOR UPDATE

4.2 表锁、页锁、行锁对比

flowchart TD
    A[锁粒度选择] --> B{并发需求?}
    B -->|极高| C[行锁<br/>InnoDB默认<br/>冲突少]
    B -->|中| D[页锁<br/>已淘汰]
    B -->|低| E[表锁<br/>MyISAM<br/>开销小]
    
    C --> F[优点：并发高<br/>只锁冲突行]
    C --> G[缺点：内存大<br/>可能死锁]
    
    E --> H[优点：实现简单<br/>无死锁检测]
    E --> I[缺点：并发低<br/>写阻塞读]
    
    F --> J[OLTP系统<br/>高并发交易]
    G --> K[需死锁检测<br/>超时处理]
    
    style C fill:#90EE90
    style J fill:#90EE90

4.3 InnoDB行锁的三种形态

-- 1. 记录锁（Record Lock）：锁定单行
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- 锁id=1的记录

-- 2. 间隙锁（Gap Lock）：锁定范围，防止插入
SELECT * FROM accounts WHERE id > 5 AND id < 10 FOR UPDATE;
-- 锁住(5,10)间隙，阻止其他事务插入id=6,7,8,9

-- 3. 临键锁（Next-Key Lock）：记录锁+间隙锁，左开右闭
-- 锁住(5,10]，包含10的记录和(5,10)间隙
-- RR级别默认，解决幻读

-- 锁降级：唯一索引等值查询且记录存在时，Next-Key Lock退化为Record Lock
SELECT * FROM accounts WHERE id = 5 FOR UPDATE;  
-- id是唯一索引且存在，只锁记录，不锁间隙（优化）

4.4 共享锁与排他锁

-- 兼容性矩阵：
--       S锁   X锁
-- S锁   兼容   冲突
-- X锁   冲突   冲突

-- 演示：读锁阻塞写
-- 会话A
BEGIN;
SELECT * FROM inventory WHERE product_id = 1 LOCK IN SHARE MODE;  -- S锁

-- 会话B（阻塞直到A提交）
UPDATE inventory SET stock = 100 WHERE product_id = 1;  -- 需X锁，等待

-- 演示：写锁阻塞读写
-- 会话A
BEGIN;
SELECT * FROM inventory WHERE product_id = 1 FOR UPDATE;  -- X锁

-- 会话B（阻塞）
SELECT * FROM inventory WHERE product_id = 1 LOCK IN SHARE MODE;  -- 等S锁
-- 或 UPDATE ... 等X锁

4.5 乐观锁与悲观锁

-- 悲观锁：先锁后操作，适合写多读少，冲突严重
BEGIN;
SELECT stock, version FROM inventory WHERE product_id = 1 FOR UPDATE;
-- 应用层计算新库存
UPDATE inventory SET stock = 95, version = version + 1 
WHERE product_id = 1 AND version = 1;
COMMIT;

-- 乐观锁：先操作后检查，适合读多写少，冲突少
-- 应用层读取version=1
UPDATE inventory SET stock = stock - 5, version = version + 1 
WHERE product_id = 1 AND version = 1;  -- 假设读取时version=1

-- 检查影响行数：1行则成功，0行则冲突需重试

4.6 死锁分析与解决

-- 死锁产生：循环等待
-- 事务A：锁记录1，请求记录2
-- 事务B：锁记录2，请求记录1

-- 查看死锁日志
SHOW ENGINE INNODB STATUS\G
-- 找到LATEST DETECTED DEADLOCK部分

-- 线上死锁处理流程：
-- 1. 自动：InnoDB检测死锁，回滚代价小的事务（undo log少的）
-- 2. 手动：分析日志，调整业务逻辑

-- 预防策略：
-- 1. 固定加锁顺序（都先锁id小的）
-- 2. 降低隔离级别（RC减少间隙锁，降低死锁概率）
-- 3. 缩短事务长度，快速提交
-- 4. 批量操作分批处理

死锁检测流程图：

flowchart TD
    A[事务请求锁] --> B{锁可用?}
    B -->|是| C[获取锁<br/>继续执行]
    B -->|否| D{是否会形成循环等待?}
    
    D -->|是| E[死锁检测触发]
    D -->|否| F[进入锁等待队列]
    
    E --> G[选择牺牲者<br/>Undo Log少的]
    G --> H[回滚牺牲者事务]
    H --> I[应用层捕获错误<br/>重试事务]
    
    F --> J[等待锁释放<br/>或超时]
    C --> K[事务继续]
    
    style E fill:#ff9999
    style H fill:#ffcc99
    style I fill:#90EE90

第五幕：日志篇 - 数据的守护者

5.1 MySQL日志体系全景

flowchart TD
    A[MySQL日志系统] --> B[错误日志<br/>Error Log]
    A --> C[查询日志<br/>General Log]
    A --> D[慢查询日志<br/>Slow Log]
    A --> E[二进制日志<br/>Binlog<br/>主从复制/恢复]
    A --> F[重做日志<br/>Redo Log<br/>崩溃恢复]
    A --> G[回滚日志<br/>Undo Log<br/>事务回滚/MVCC]
    A --> H[中继日志<br/>Relay Log<br/>从库复制]
    
    B --> I[启动/运行错误]
    C --> J[所有SQL语句<br/>调试用]
    D --> K[执行时间>阈值]
    E --> L[数据变更记录<br/>逻辑日志]
    F --> M[物理页修改<br/>WAL机制]
    G --> N[数据旧版本<br/>链式存储]
    
    style E fill:#90EE90
    style F fill:#90EE90
    style G fill:#90EE90

5.2 Redo Log：崩溃恢复的基石

-- Redo Log：物理日志，记录"在某个数据页上做了什么修改"
-- 目的：崩溃恢复，保证已提交事务的持久性（D特性）

-- 配置
SHOW VARIABLES LIKE 'innodb_log_file_size';  -- 单个日志文件大小
SHOW VARIABLES LIKE 'innodb_log_files_in_group';  -- 文件个数

-- 写入流程：
-- 1. 修改Buffer Pool中的页（脏页）
-- 2. 生成Redo Log，写入Log Buffer（内存）
-- 3. 事务提交时，fsync刷盘（innodb_flush_log_at_trx_commit=1）
-- 4. 脏页异步刷盘（checkpoint）

-- 崩溃恢复：重启时扫描Redo Log，重放已提交但未刷盘的操作

WAL（Write-Ahead Logging）机制：

flowchart TD
    A[事务提交] --> B[生成Redo Log]
    B --> C{innodb_flush_log_at_trx_commit?}
    
    C -->|0| D[每秒刷盘<br/>性能最高<br/>可能丢1秒数据]
    C -->|1| E[立即fsync<br/>最安全<br/>默认推荐]
    C -->|2| F[写入OS缓存<br/>每秒刷盘<br/>平衡方案]
    
    E --> G[Redo Log持久化]
    G --> H[返回客户端成功]
    H --> I[脏页异步刷盘<br/>后台线程]
    
    J[崩溃恢复] --> K[检查Redo Log]
    K --> L[重放已提交事务]
    K --> M[撤销未提交事务<br/>Undo Log]
    
    style E fill:#90EE90
    style G fill:#90EE90
    style L fill:#90EE90

5.3 Binlog：复制的纽带

-- Binlog：逻辑日志，记录SQL语句或行变更
-- 用途：主从复制、数据恢复、审计

-- 三种格式：
-- 1. STATEMENT：记录SQL语句，体积小，可能有主从不一致（如NOW()）
-- 2. ROW：记录行变更（前像+后像），体积大，精确安全，推荐
-- 3. MIXED：混合，默认STATEMENT，不安全时用ROW

SET GLOBAL binlog_format = 'ROW';

-- 查看Binlog
SHOW BINARY LOGS;
SHOW MASTER STATUS;

-- 解析Binlog
mysqlbinlog --base64-output=DECODE-ROWS -v mysql-bin.000001

5.4 Redo Log vs Binlog

flowchart TD
    A[日志对比] --> B[Redo Log]
    A --> C[Binlog]
    
    B --> D[InnoDB特有<br/>物理日志<br/>页级别修改]
    B --> E[循环写<br/>固定大小文件<br/>覆盖旧日志]
    B --> F[崩溃恢复用<br/>保证持久性]
    
    C --> G[Server层<br/>逻辑日志<br/>SQL或行数据]
    C --> H[追加写<br/>无限增长<br/>可清理]
    C --> I[主从复制用<br/>数据恢复]
    
    D --> J[先写Redo<br/>事务提交]
    G --> K[后写Binlog<br/>两阶段提交]
    
    J --> L[保证ACID<br/>不丢数据]
    K --> M[主从一致<br/>可恢复]
    
    style B fill:#90EE90
    style C fill:#90EE90
    style L fill:#90EE90
    style M fill:#90EE90

5.5 两阶段提交（2PC）

-- 为什么需要两阶段提交？
-- 如果先写Redo，后写Binlog，中间崩溃：
-- Redo有记录，Binlog无记录 → 主库恢复数据，从库无记录，主从不一致

-- 如果先写Binlog，后写Redo：
-- Binlog有记录，Redo无记录 → 从库有数据，主库无记录，主从不一致

-- 两阶段提交流程：
BEGIN;
-- 执行操作
-- Prepare阶段：写Redo Log，标记为Prepare状态
-- 写Binlog
-- Commit阶段：写Redo Log，标记为Commit状态
COMMIT;

-- 崩溃恢复检查：
-- 1. Redo Prepare + Binlog完整 → 提交
-- 2. Redo Prepare + Binlog不完整 → 回滚
-- 3. Redo Commit → 已提交，无需处理

2PC流程图：

flowchart TD
    A[事务执行] --> B[Prepare阶段]
    B --> C[写Redo Log<br/>状态=Prepare]
    C --> D{崩溃?}
    D -->|是| E[恢复时无Binlog<br/>回滚事务]
    D -->|否| F[写Binlog]
    
    F --> G[Commit阶段]
    G --> H[写Redo Log<br/>状态=Commit]
    H --> I{崩溃?}
    I -->|是| J[恢复时有Binlog<br/>提交事务]
    I -->|否| K[事务完成]
    
    E --> L[数据一致<br/>主从无记录]
    J --> M[数据一致<br/>主从都有]
    K --> M
    
    style B fill:#ffcc99
    style G fill:#90EE90
    style M fill:#90EE90

5.6 Undo Log：回滚与MVCC的支柱

-- Undo Log：逻辑日志，记录"如何撤销修改"
-- 用途：1. 事务回滚  2. MVCC读历史版本  3. 崩溃恢复时撤销未提交事务

-- 结构：多个版本链
-- 行数据 → DB_ROLL_PTR → Undo Log 1 → Undo Log 2 → ...

-- 插入Undo：记录PK，回滚时删除
-- 更新Undo：记录旧值，回滚时恢复
-- 删除Undo：记录整行，回滚时插入

-- Purge线程：清理不再需要的Undo Log（比最小活跃事务ID旧的）

第六幕：主从同步篇 - 扩展的艺术

6.1 主从同步原理

flowchart TD
    A[主库Master] -->|1. 写Binlog| B[Binlog Dump线程]
    B -->|2. 发送Binlog| C[从库Slave]
    
    C -->|3. IO线程接收| D[Relay Log中继日志]
    D -->|4. SQL线程重放| E[从库数据]
    
    F[同步方式] --> G[异步复制<br/>主库不等待<br/>性能高可能丢数据]
    F --> H[半同步复制<br/>等至少一个从库确认<br/>平衡方案]
    F --> I[同步复制<br/>等所有从库确认<br/>安全低性能]
    
    style A fill:#90EE90
    style C fill:#90EE90
    style H fill:#90EE90

6.2 同步延迟原因与解决

-- 延迟原因：
-- 1. 从库单线程重放（5.6前），主库并发写，从库串行
-- 2. 从库性能差，硬件不如主库
-- 3. 大事务，主库很快，从库重放慢
-- 4. 长查询阻塞SQL线程

-- 解决方案：
-- 1. 并行复制（MySQL 5.6+）
SET GLOBAL slave_parallel_workers = 4;  -- 多线程重放
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';  -- 基于组提交并行

-- 2. 读写分离，从库只读，减轻压力

-- 3. 大事务拆小，避免一次性删除100万行

-- 4. 监控延迟
SHOW SLAVE STATUS\G  -- 看Seconds_Behind_Master

第七幕：性能优化篇 - 调优的实战

7.1 EXPLAIN分析执行计划

-- EXPLAIN关键列解读
EXPLAIN SELECT * FROM orders 
WHERE user_id = 10086 AND status = 1 
ORDER BY created_at DESC LIMIT 10\G

-- id: 执行顺序，id越大越先执行，相同从上到下
-- select_type: SIMPLE简单查询，PRIMARY最外层，SUBQUERY子查询
-- table: 访问的表
-- type: 访问类型（重要）
--   system > const > eq_ref > ref > range > index > ALL
--   目标：至少range，最好ref以上
-- possible_keys: 可能用的索引
-- key: 实际用的索引
-- key_len: 索引使用长度（判断用了多少列）
-- rows: 预估扫描行数
-- Extra: 额外信息
--   Using index: 覆盖索引
--   Using where: 用WHERE过滤
--   Using filesort: 需要排序，可能慢
--   Using temporary: 需要临时表，可能慢

7.2 索引失效场景（完整版）

-- 1. 不满足最左前缀
WHERE b = 1 AND c = 2  -- 索引(a,b,c)，a没出现，失效

-- 2. 索引列函数/运算
WHERE LEFT(name, 2) = '张'  -- 失效
WHERE age + 1 = 20  -- 失效

-- 3. 类型转换
WHERE id = '123'  -- id是INT，转字符串比较，可能失效

-- 4. 范围查询后列失效
WHERE a = 1 AND b > 2 AND c = 3  -- 索引(a,b,c)，c无法使用

-- 5. !=, <> 可能失效（看选择性）
-- 6. IS NULL/IS NOT NULL（看列是否允许NULL，统计信息）
-- 7. LIKE '%张%'（前导模糊）
-- 8. OR条件部分无索引
-- 9. 字符串不加引号
WHERE phone = 13800138000  -- phone是VARCHAR，失效

7.3 慢SQL优化步骤

flowchart TD
    A[发现慢查询] --> B[开启Slow Log<br/>pt-query-digest分析]
    B --> C[EXPLAIN分析执行计划]
    C --> D{type=ALL?}
    
    D -->|是| E[检查WHERE条件<br/>添加/优化索引]
    D -->|否| F{Extra有filesort?}
    
    F -->|是| G[利用索引排序<br/>或降低排序需求]
    F -->|否| H{Extra有temporary?}
    
    H -->|是| I[优化GROUP BY<br/>添加索引]
    H -->|否| J{rows过大?}
    
    J -->|是| K[限制返回行数<br/>分页优化]
    J -->|否| L[检查网络/锁等待]
    
    E --> M[验证优化效果]
    G --> M
    I --> M
    K --> M
    L --> M
    
    M --> N{性能达标?}
    N -->|否| O[SQL改写<br/>业务逻辑调整]
    N -->|是| P[优化完成]
    
    O --> C
    
    style P fill:#90EE90
    style E fill:#ffcc99

7.4 深分页优化

-- ❌ 深分页问题
SELECT * FROM orders 
ORDER BY created_at DESC 
LIMIT 1000000, 10;
-- 扫描1000010行，丢弃前1000000，极慢

-- ✅ 优化1：延迟关联
SELECT o.* FROM orders o
JOIN (
    SELECT order_id FROM orders 
    ORDER BY created_at DESC 
    LIMIT 1000000, 10
) tmp ON o.order_id = tmp.order_id;
-- 子查询只扫描索引，快10倍+

-- ✅ 优化2：书签记录
SELECT * FROM orders 
WHERE created_at < '2024-03-15 10:30:00'  -- 上一页最后时间
ORDER BY created_at DESC 
LIMIT 10;
-- 利用索引范围扫描，毫秒级

-- ✅ 优化3：覆盖索引+延迟关联
-- 确保有idx_created_id (created_at, order_id)
SELECT o.* FROM orders o
JOIN (
    SELECT order_id FROM orders 
    WHERE created_at < '2024-03-15 10:30:00'
    ORDER BY created_at DESC 
    LIMIT 10
) tmp ON o.order_id = tmp.order_id;

第八幕：分库分表篇 - 架构的演进

8.1 分库分表时机

flowchart TD
    A[数据库压力] --> B{单表数据量?}
    B -->|> 500万行| C[考虑分表]
    B -->|< 500万行| D{并发连接?}
    
    D -->|> 1000| E[考虑分库]
    D -->|< 1000| F{磁盘IO?}
    
    F -->|> 80%| G[考虑分库/读写分离]
    F -->|< 80%| H[当前架构够用]
    
    C --> I{访问模式?}
    I -->|按ID查询| J[Hash分表<br/>均匀分布]
    I -->|按时间查询| K[Range分表<br/>便于归档]
    I -->|按地区查询| L[List分表<br/>地理分布]
    
    E --> M[垂直分库<br/>业务解耦]
    G --> N[水平分库<br/>数据分片]
    
    style J fill:#90EE90
    style K fill:#90EE90
    style M fill:#90EE90
    style N fill:#90EE90

8.2 分库分表实现方式

-- 1. 客户端Sharding（Sharding-JDBC等）
-- 应用层配置，透明路由

-- 2. 代理层Sharding（MyCat, ShardingSphere-Proxy）
-- 独立服务，应用无感知

-- 3. 分布式数据库（TiDB, OceanBase）
-- 原生分布式，自动分片

-- 分片策略示例：用户表按user_id % 8分8张表
-- user_0: user_id % 8 = 0
-- user_1: user_id % 8 = 1
-- ...

-- 分库分表后问题：
-- 1. 分布式ID（雪花算法）
-- 2. 跨库JOIN（禁止，应用层组装）
-- 3. 跨库事务（最终一致性，Seata等）
-- 4. 分页查询（全局排序，内存归并）

8.3 跨库查询与事务解决

-- 跨库JOIN禁止，解决方案：

-- 方案1：字段冗余
-- 订单表冗余用户名，避免JOIN用户表
ALTER TABLE orders ADD COLUMN user_name VARCHAR(50);

-- 方案2：数据组装
-- 先查订单列表得user_ids，再批量查用户信息，应用层Map组装

-- 方案3：全局表（小表广播）
-- 每个库都有一份完整的地区表、配置表

-- 跨库事务：
-- 方案1：最终一致性（消息队列补偿）
-- 方案2：TCC（Try-Confirm-Cancel）
-- 方案3：Seata AT模式（自动补偿）

8.4 分表后分页查询

-- 问题：8张表，每页10条，全局第2页（11-20条）怎么查？

-- 方案1：全局排序，内存归并（页码小可用）
-- 查8张表各取20条（保证覆盖），内存排序，取11-20
-- 缺点：页码深时，每表需取大量数据，内存爆炸

-- 方案2：限制分页深度（业务妥协）
-- 只允许查前100页，或提供"下一页"游标

-- 方案3：二次查询法
-- 1. 各表查ORDER BY id LIMIT 100, 10（假设目标在这范围）
-- 2. 找到全局最小id和最大id
-- 3. 各表查WHERE id BETWEEN min AND max，内存排序

-- 方案4：搜索引擎（ES）
-- 数据同步到Elasticsearch，复杂查询走ES，精确查询走MySQL