MySQL 核心技术详解：从索引到底层架构

    5
   /
  4
 /
3

              [10 | 30]
             /    |    \
    [1|3|5|7] [15|20|25] [35|40|45]
       ↕️         ↕️          ↕️
    叶子节点双向链表连接

-- 通过主键查询最快，直接命中聚集索引
SELECT * FROM users WHERE id = 100;

-- 假设有 INDEX idx_name (name)
SELECT * FROM users WHERE name = 'sirius';
-- 1. 在辅助索引找到 name='sirius' 对应的主键 id
-- 2. 用 id 去聚集索引回表查询完整数据

CREATE INDEX idx_name_age ON users(name, age);

索引定义：CREATE INDEX idx_name_age ON users(name, age);
                                    ↑     ↑
                                  最左列  第二列
 
B+ 树物理存储顺序（先 name，后 age）：
[name:A, age:18]
[name:A, age:25]  ← name 相同，按 age 排序
[name:B, age:20]
[name:C, age:30]

-- ✅ 能用索引：包含最左列 name
WHERE name = 'sirius'
WHERE name = 'sirius' AND age = 24
 
-- ✅ 也能用索引：MySQL 优化器会自动调整顺序
WHERE age = 24 AND name = 'sirius'  -- 优化器重写成 name 在前
 
-- ❌ 用不上索引：缺少最左列 name
WHERE age = 24
 
-- ❌ 用不上 age 部分：name 用了范围查询后，age 无法使用索引
WHERE name LIKE 's%' AND age = 24
-- 原因：name LIKE 's%' 匹配多个值，每个值下的 age 是有序的，
-- 但跨不同 name 值，age 不是全局有序的

-- 索引：idx_name_age (name, age)
SELECT * FROM users WHERE name LIKE 's%' AND age = 24;
 
-- 没有 ICP：先回表获取所有 name LIKE 's%' 的数据，再过滤 age
-- 有 ICP：直接在索引中过滤 age，只回表符合条件的数据

-- 索引：CREATE INDEX idx_name_age ON users(name, age);
 
-- ❌ 不是覆盖索引：需要回表查 email
SELECT name, age, email FROM users WHERE name = 'sirius';
 
-- ✅ 是覆盖索引：name 和 age 都在索引里
SELECT name, age FROM users WHERE name = 'sirius';

┌─────────────────────────────────────────────────────────────┐
│                  普通查询（需要回表）                          │
├─────────────────────────────────────────────────────────────┤
│  SELECT * FROM users WHERE name = 'sirius';                 │
│                                                             │
│  1. 辅助索引找到 name='sirius' → 拿到 id=50                   │
│  2. 用 id=50 去聚集索引回表 → 获取完整行数据                   │
│                                                             │
│  结果：访问 2 颗 B+ 树，两次索引查找                           │
└─────────────────────────────────────────────────────────────┘
 
┌─────────────────────────────────────────────────────────────┐
│                  覆盖索引查询（无需回表）                       │
├─────────────────────────────────────────────────────────────┤
│  SELECT name, age FROM users WHERE name = 'sirius';         │
│                                                             │
│  1. 联合索引 idx_name_age 中找到 name='sirius'                │
│  2. 索引中直接包含 name 和 age，直接返回                       │
│                                                             │
│  结果：只访问 1 颗 B+ 树，减少一次回表 I/O                     │
└─────────────────────────────────────────────────────────────┘

EXPLAIN SELECT name, age FROM users WHERE name = 'sirius';
-- Extra 列显示 "Using index" 表示使用了覆盖索引
 
### 4. 利用索引有序性
 
字符串查询时，优先使用前缀匹配：
 
```sql
-- ✅ 能用索引
WHERE name LIKE 'sirius%'
 
-- ❌ 不能用索引（% 在开头）
WHERE name LIKE '%sirius'

-- 查看慢查询日志
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
 
-- 使用 EXPLAIN 分析执行计划
EXPLAIN SELECT * FROM users WHERE name = 'sirius';

┌─────────┐     binlog     ┌─────────┐
│  Master │ ─────────────→ │  Slave  │
│  (主库)  │                │  (从库)  │
└─────────┘                └─────────┘
   ↓                          ↓
Dump Thread               I/O Thread → SQL Thread
(发送日志)                (接收日志 → 重放执行)

阶段1：写入 Redo Log，标记为 prepare
阶段2：写入 Binlog
阶段3：Redo Log 标记为 commit

-- 事务A                    -- 事务B
UPDATE A SET ...           UPDATE B SET ...
UPDATE B SET ...           UPDATE A SET ...
-- 循环等待，形成死锁

-- 乐观锁示例：使用版本号
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 5;

1. 选举数据最新的从库（对比 Relay_Log_File 和 Relay_Log_Pos）
2. 从旧主库 binlog 中提取未同步日志
3. 应用缺失的 binlog 到新主库
4. 提升从库为新主库
5. 其他从库 CHANGE MASTER TO 指向新主库
6. VIP 漂移到新主库

□ 是否命中索引？（EXPLAIN 看 key 列）
□ 是否使用最左前缀？
□ 是否能使用覆盖索引减少回表？
□ 范围查询是否尽量缩小范围？
□ 字符串查询是否使用前缀匹配（LIKE 'prefix%'）？
□ 索引列是否避免函数运算和隐式类型转换？

异步复制：性能好，可能丢数据
半同步复制：等从库确认，平衡性能与可靠性
两阶段提交：Redo Log prepare → Binlog → Redo Log commit

1. Manager 检测到主库故障
2. 从各从库选举数据最新的作为新主库
3. 从旧主库 binlog 补全新主库数据
4. 其他从库切换主库指向
5. VIP 漂移到新主库