MySQL InnoDB 核心机制：MVCC、索引、事务、锁与 SQL 优化

┌─────────────────────────────────────────┐
│              客户端连接                  │
└─────────────────┬───────────────────────┘
                  ▼
┌─────────────────────────────────────────┐
│            MySQL Server 层              │
│  连接器 → 分析器 → 优化器 → 执行器       │
└─────────────────┬───────────────────────┘
                  ▼
┌─────────────────────────────────────────┐
│            InnoDB 存储引擎              │
│  ┌──────────┐  ┌──────────┐            │
│  │Buffer Pool│  │  Redo Log│            │
│  └──────────┘  └──────────┘            │
│  ┌──────────┐  ┌──────────┐            │
│  │ Undo Log  │  │ 锁管理   │            │
│  └──────────┘  └──────────┘            │
└─────────────────────────────────────────┘

聚簇索引（主键索引）
┌─────────┐
│ 主键 ID  │ → [全部列数据]
└─────────┘

二级索引（普通索引）
┌─────────┐
│ index列  │ → [主键 ID] → 回表 → [全部列数据]
└─────────┘

-- name 列有索引，查询只取 id 和 name
SELECT id, name FROM users WHERE name = 'Tom';
-- 不回表，Extra: Using index

-- 索引 (province, city, district)

-- 提供所有层级 → 完全匹配
SELECT * FROM address WHERE province = '广东' AND city = '深圳' AND district = '南山';

-- 有省有区但跳过市 → 只用省定位，district 用不上
SELECT * FROM address WHERE province = '广东' AND district = '南山';

-- 只有市 → 不知道哪个省，索引失效
SELECT * FROM address WHERE city = '深圳';

-- 只有省 → 能定位，缩到省范围
SELECT * FROM address WHERE province = '广东';

-- 1. 函数/计算破坏索引
SELECT * FROM t WHERE DATE(create_time) = '2024-01-01';  -- ❌
SELECT * FROM t WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02';  -- ✅

-- 2. 隐式类型转换
SELECT * FROM t WHERE phone = 13800138000;  -- ❌ phone 是 varchar
SELECT * FROM t WHERE phone = '13800138000';  -- ✅

-- 3. LIKE 前置 %
SELECT * FROM t WHERE name LIKE '%Tom';  -- ❌
SELECT * FROM t WHERE name LIKE 'Tom%';  -- ✅

-- 4. OR 条件有非索引列
SELECT * FROM t WHERE name = 'Tom' OR age = 25;  -- age 无索引则全表扫描

-- 5. !=, <>, NOT IN, NOT EXISTS
SELECT * FROM t WHERE status != 1;  -- 通常不走索引

-- 查看当前隔离级别
SHOW VARIABLES LIKE 'transaction_isolation';

-- 设置
SET SESSION transaction_isolation = 'READ-COMMITTED';

脏读：读到别的事务还没提交的数据
T1: UPDATE age=20 WHERE id=1  （未提交）
T2: SELECT age FROM t WHERE id=1  → 读到 20
T1: ROLLBACK                     → age 实际还是 18

不可重复读：同一事务内两次读到不同值（update 导致）
T2: SELECT age FROM t WHERE id=1  → 18
T1: UPDATE age=20 WHERE id=1; COMMIT;
T2: SELECT age FROM t WHERE id=1  → 20 （不一致！）

幻读：同一事务内两次查询多出/少了行（insert/delete 导致）
T2: SELECT * FROM t WHERE age>18  → 3 行
T1: INSERT INTO t VALUES(4, 20); COMMIT;
T2: SELECT * FROM t WHERE age>18  → 4 行 （多了一行！）

当前行 (TRX_ID=100)
  ↑ DB_ROLL_PTR
旧版本 (TRX_ID=80)
  ↑ DB_ROLL_PTR
旧版本 (TRX_ID=50)
  ↑ DB_ROLL_PTR
旧版本 (TRX_ID=10)

如果 trx_id == creator_trx_id  → 可见（自己的修改）
如果 trx_id < min_trx_id        → 可见（已提交）
如果 trx_id >= max_trx_id       → 不可见（将来的事务）
如果 min_trx_id <= trx_id < max_trx_id:
    如果 trx_id 在 m_ids 中    → 不可见（未提交）
    如果 trx_id 不在 m_ids 中  → 可见（已提交）

-- 查看当前锁状态
SELECT * FROM performance_schema.data_locks;
SHOW ENGINE INNODB STATUS;

表 t：id 主键 (1, 5, 10, 15, 25)

SELECT * FROM t WHERE id = 10 FOR UPDATE;
→ 锁住 id=10（Record Lock，等值 + 唯一索引）

SELECT * FROM t WHERE id = 8 FOR UPDATE;
→ id=8 不存在，锁住 (5, 10] → Gap Lock (5, 10)
   防止其他事务在 5-10 之间插入

SELECT * FROM t WHERE id >= 10 AND id < 15 FOR UPDATE;
→ (5, 10] + (10, 15] → 实际锁住 [10, 15]

-- T1                    -- T2
BEGIN;                   BEGIN;
UPDATE t SET a=1
  WHERE id=10;  -- X 锁 id=10
                        UPDATE t SET a=2
                          WHERE id=20;  -- X 锁 id=20
UPDATE t SET a=1
  WHERE id=20;  -- 等待 T2 释放
                        UPDATE t SET a=2
                          WHERE id=10;  -- 等待 T1 释放
-- 死锁！

EXPLAIN SELECT * FROM orders WHERE user_id = 100 ORDER BY create_time DESC;

system   → 表只有一行
const    → 主键/唯一键等值查询，最多一行
eq_ref   → 关联查询，驱动表每行匹配被驱动表唯一索引
ref      → 非唯一索引等值查询，可能多行
range    → 索引范围扫描
index    → 全索引扫描（比 ALL 好一点）
ALL      → 全表扫描，最差

-- 慢：扫描 100010 行，丢掉前 100000 行
SELECT * FROM t ORDER BY id LIMIT 100000, 10;

-- 优化：记住上次的 id，从该位置接着取
SELECT * FROM t WHERE id > 100000 ORDER BY id LIMIT 10;

-- Extra: Using filesort
SELECT * FROM t WHERE name = 'Tom' ORDER BY age;

-- 建联合索引 (name, age)，Extra: Using index
CREATE INDEX idx_name_age ON t(name, age);

-- 小表驱动大表
SELECT * FROM small_table s
JOIN big_table b ON s.id = b.sid;

-- JOIN 字段建索引
CREATE INDEX idx_sid ON big_table(sid);

-- 外表小用 IN
SELECT * FROM t WHERE id IN (SELECT tid FROM small);

-- 外表大用 EXISTS
SELECT * FROM t WHERE EXISTS (SELECT 1 FROM small WHERE small.tid = t.id);

-- MyISAM 快，InnoDB 慢（需要扫索引）
SELECT COUNT(*) FROM t;

-- 估算（不精确但快）
SELECT TABLE_ROWS FROM information_schema.TABLES
WHERE TABLE_NAME = 't';

-- 真有大量统计需求用 Redis 计数器

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 超过 1 秒记录

-- 查看慢查询
SHOW VARIABLES LIKE 'slow_query%';