1
-- 创建索引
2
CREATE INDEX idx_username ON users(username);
3
CREATE UNIQUE INDEX idx_email ON users(email);
4
CREATE INDEX idx_name_age ON users(name, age);  -- 联合索引
5

6
-- 查看索引
7
SHOW INDEX FROM users;
8

9
-- 删除索引
10
DROP INDEX idx_username ON users;

1
B 树：
2
        [30|60]
3
       /   |   \
4
   [10|20] [40|50] [70|80]
5
   (数据存储在内部节点)
6

7
B+ 树：
8
        [30|60]           ← 内部节点只存键值
9
       /   |   \
10
   [10|20] [40|50] [70|80]
11
      ↓       ↓       ↓
12
   [data]  [data]  [data]  ← 叶子节点存数据
13
      ↔       ↔       ↔    ← 叶子节点链表相连

1
聚簇索引（InnoDB 主键）：
2
┌─────────────────────────────────┐
3
│  B+ 树叶子节点存储完整行数据    │
4
│  数据即索引，索引即数据         │
5
└─────────────────────────────────┘
6

7
非聚簇索引（二级索引）：
8
┌─────────────────────────────────┐
9
│  B+ 树叶子节点存储主键值        │
10
│  需要"回表"查询完整数据         │
11
└─────────────────────────────────┘

1
-- 回表示例
2
SELECT * FROM users WHERE name = 'Tom';
3
-- 1. 在 name 索引树找到主键 id = 100
4
-- 2. 在主键索引树找到 id = 100 的完整行数据（回表）
5

6
-- 避免回表：覆盖索引
7
SELECT id, name FROM users WHERE name = 'Tom';
8
-- name 索引已包含 id 和 name，无需回表

1
-- ✅ 命中索引
2
WHERE a = 1                    -- 命中 a
3
WHERE a = 1 AND b = 2          -- 命中 a, b
4
WHERE a = 1 AND b = 2 AND c = 3 -- 命中 a, b, c
5

6
-- ❌ 不命中或部分命中
7
WHERE b = 2                    -- 不命中（缺少最左列 a）
8
WHERE c = 3                    -- 不命中
9
WHERE b = 2 AND c = 3          -- 不命中
10

11
-- ⚠️ 范围查询后停止
12
WHERE a = 1 AND b > 2 AND c = 3  -- 命中 a, b（c 不命中，范围后停止）
13

14
-- ✅ 索引下推（MySQL 5.6+）
15
WHERE a = 1 AND b LIKE '%test%' AND c = 3
16
-- ICP 优化：在索引层过滤 b，减少回表

1
-- 1. 对索引列使用函数
2
WHERE YEAR(create_time) = 2024        -- ❌ 失效
3
WHERE create_time >= '2024-01-01'     -- ✅ 命中
4

5
-- 2. 隐式类型转换
6
WHERE phone = 13800138000             -- ❌ phone 是 varchar，数字会转换
7
WHERE phone = '13800138000'           -- ✅ 命中
8

9
-- 3. LIKE 以 % 开头
10
WHERE name LIKE '%Tom'                -- ❌ 失效
11
WHERE name LIKE 'Tom%'                -- ✅ 命中
12

13
-- 4. OR 条件有非索引列
14
WHERE name = 'Tom' OR age = 20        -- ❌ age 无索引，全表扫描
15

16
-- 5. != 和 NOT IN
17
WHERE status != 1                     -- ❌ 可能失效
18
WHERE status NOT IN (1, 2)            -- ❌ 可能失效
19

20
-- 6. IS NULL / IS NOT NULL（视数据分布）
21
WHERE name IS NULL                    -- 可能失效
22

23
-- 7. 联合索引不满足最左前缀
24
-- 见上题

1
-- 事务示例
2
START TRANSACTION;
3

4
UPDATE account SET balance = balance - 100 WHERE id = 1;
5
UPDATE account SET balance = balance + 100 WHERE id = 2;
6

7
COMMIT;  -- 或 ROLLBACK;

1
-- 查看隔离级别
2
SELECT @@transaction_isolation;
3

4
-- 设置隔离级别
5
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

1
-- 脏读：读到未提交的数据
2
-- 事务A修改数据但未提交，事务B读到了修改后的值
3

4
-- 不可重复读：同一事务内两次读取结果不同
5
-- 事务A读取数据，事务B修改并提交，事务A再次读取发现变了
6

7
-- 幻读：同一事务内两次查询行数不同
8
-- 事务A查询10行，事务B插入新行，事务A再查询变成11行

1
MySQL InnoDB 通过 MVCC 实现 REPEATABLE READ 下的非锁定读：
2

3
每行数据隐藏列：
4
- trx_id：最后修改的事务ID
5
- roll_pointer：指向 undo log 的指针
6

7
读取时根据 Read View 判断可见性：
8
- 事务开始时创建 Read View
9
- 读取时沿着 roll_pointer 找到可见版本

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

5
-- 2. EXPLAIN 分析执行计划
6
EXPLAIN SELECT * FROM users WHERE name = 'Tom';
7

8
-- 关键字段
9
-- id: 查询序号
10
-- select_type: 查询类型（SIMPLE/PRIMARY/SUBQUERY）
11
-- table: 表名
12
-- type: 访问类型（性能从好到差）
13
--   system > const > eq_ref > ref > range > index > ALL
14
-- possible_keys: 可能使用的索引
15
-- key: 实际使用的索引
16
-- rows: 预估扫描行数
17
-- Extra: 额外信息
18
--   Using index: 覆盖索引（好）
19
--   Using where: 需要过滤
20
--   Using filesort: 需要额外排序（差）
21
--   Using temporary: 使用临时表（差）

1
-- 1. 避免 SELECT *
2
SELECT id, name, email FROM users;  -- ✅ 只查需要的列
3

4
-- 2. 使用 LIMIT 分页
5
SELECT * FROM orders ORDER BY id LIMIT 100000, 10;  -- ❌ 深分页慢
6
-- 优化：延迟关联
7
SELECT o.* FROM orders o
8
INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 10) t
9
ON o.id = t.id;  -- ✅
10

11
-- 3. 小表驱动大表
12
-- IN 适合子查询结果集小
13
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);
14
-- EXISTS 适合外表小
15
SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
16

17
-- 4. 批量操作
18
INSERT INTO users (name, age) VALUES ('A', 20), ('B', 21), ('C', 22);  -- ✅ 批量
19
-- 而不是循环单条插入
20

21
-- 5. 使用连接查询替代子查询
22
SELECT u.*, o.amount FROM users u
23
JOIN orders o ON u.id = o.user_id;  -- ✅ 通常比子查询快
24

25
-- 6. 合理使用索引
26
-- WHERE、ORDER BY、GROUP BY 的列加索引
27
-- 联合索引把区分度高的列放前面
28

29
-- 7. 避免大事务
30
-- 大事务占用锁时间长，影响并发

1
-- 共享锁（读锁）
2
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;
3

4
-- 排他锁（写锁）
5
SELECT * FROM users WHERE id = 1 FOR UPDATE;
6

7
-- 死锁示例
8
-- 事务A：UPDATE users SET ... WHERE id = 1;
9
-- 事务B：UPDATE users SET ... WHERE id = 2;
10
-- 事务A：UPDATE users SET ... WHERE id = 2;  -- 等待B
11
-- 事务B：UPDATE users SET ... WHERE id = 1;  -- 等待A，死锁！
12

13
-- InnoDB 自动检测死锁，回滚代价小的事务

1
垂直分库：按业务拆分
2
┌─────────────────┐    ┌──────┐ ┌──────┐ ┌──────┐
3
│   单一数据库    │ →  │用户库│ │订单库│ │商品库│
4
└─────────────────┘    └──────┘ └──────┘ └──────┘
5

6
水平分表：按规则分散数据
7
┌─────────────────┐    ┌────────┐ ┌────────┐ ┌────────┐
8
│   orders 表     │ →  │orders_0│ │orders_1│ │orders_2│
9
│   (1亿条)       │    │(hash)  │ │(hash)  │ │(hash)  │
10
└─────────────────┘    └────────┘ └────────┘ └────────┘
11

12
读写分离：
13
写 → 主库 → 同步 → 从库1（读）
14
                  → 从库2（读）