Day53-56 - MySQL 进阶

本部分涵盖 MySQL 进阶知识，包括索引原理、索引优化、执行计划分析和慢查询优化。

1. 索引原理

1.1 为什么需要索引

索引是数据库中用于加速数据查找的数据结构，就像书籍的目录。合理的索引可以大幅提升查询性能。

据《MySQL 索引底层彻底吃透：B+树原理、聚簇索引机制与全场景优化指南》（阿里云开发者社区），索引的核心目标是减少磁盘 IO 次数。

1.2 常见数据结构对比

数据结构	特点	问题
哈希表	等值查询 O(1)	无法范围查询
二叉搜索树	有序查找	极端情况退化成链表
平衡二叉树	平衡查找	树高过高，IO 次数多
B 树	多路平衡	非叶子节点存储数据
B+ 树	最优选择	专为磁盘设计

1.3 B+ 树核心原理

mermaid
graph TB
    subgraph B+树结构
        R["根节点<br/>[20, 40, 60]"]
        R --> N1["非叶子节点1<br/>[20, 40]"]
        R --> N2["非叶子节点2<br/>[40, 60]"]
        R --> N3["非叶子节点3<br/>[60]"]
        
        N1 --> L1["叶子节点1<br/>[1][5][9]"]
        N1 --> L2["叶子节点2<br/>[15][18]"]
        N2 --> L3["叶子节点3<br/>[22][35]"]
        N3 --> L4["叶子节点4<br/>[42][55]"]
        N3 --> L5["叶子节点5<br/>[65][78][90]"]
        
        L1 <--> L2 <--> L3 <--> L4 <--> L5
    end

据《MySQL InnoDB 索引底层：B+树深度解析》（CSDN），B+ 树的核心特性：

非叶子节点只存索引键：不存储数据，能容纳更多键，树高更低
叶子节点双向链表：所有叶子节点有序连接，范围查询高效
所有查询落到叶子节点：查询路径稳定，性能一致

1.4 MySQL InnoDB B+ 树计算

-- InnoDB 页大小默认 16KB
-- 假设主键为 BIGINT (8字节)，指针为 6字节

-- 每个内部节点可存储键值数
16KB / (8 + 6) ≈ 1170 个

-- B+ 树存储能力
高度=2: 1170 × 1170 × 16KB/行 ≈ 21GB
高度=3: 1170³ × 16KB/行 ≈ 25TB

1.5 聚簇索引 vs 二级索引

类型	定义	特点
聚簇索引	主键索引	叶子节点存储完整数据
二级索引	非主键索引	叶子节点存储索引值+主键

-- 聚簇索引查询
SELECT * FROM users WHERE id = 1;
-- 直接在主键索引 B+ 树查找，无需回表

-- 二级索引查询
SELECT * FROM users WHERE username = '张三';
-- 先在 username 索引查找主键
-- 再回表到主键索引获取完整数据

1.6 回表查询与覆盖索引

-- 回表查询（需要2次索引查找）
SELECT * FROM users WHERE username = '张三';
-- 1. 在 username 索引找到主键 id=5
-- 2. 回表到主键索引获取完整行数据

-- 覆盖索引（只需1次索引查找）
SELECT username, id FROM users WHERE username = '张三';
-- 索引已包含查询所需字段，无需回表

2. 索引类型

2.1 主键索引

-- 创建时指定
CREATE TABLE t1 (
    id INT PRIMARY KEY
);

-- 单独创建
CREATE TABLE t2 (
    id INT,
    PRIMARY KEY (id)
);

2.2 唯一索引

CREATE UNIQUE INDEX idx_email ON users(email);

2.3 普通索引

CREATE INDEX idx_name ON users(name);

2.4 联合索引

-- 创建联合索引
CREATE INDEX idx_name_age_status ON users(name, age, status);

-- 索引顺序：name -> age -> status
-- 遵循最左前缀原则

2.5 前缀索引

-- 对字符串前N个字符创建索引
CREATE INDEX idx_email ON users(email(10));

3. 索引优化与最左前缀原则

3.1 最左前缀原则

-- 联合索引：INDEX idx(name, age, status)

-- 命中索引的情况
SELECT * FROM users WHERE name = '张三';           -- ✅ 命中
SELECT * FROM users WHERE name = '张三' AND age = 25;    -- ✅ 命中
SELECT * FROM users WHERE name = '张三' AND age = 25 AND status = 1;  -- ✅ 命中

-- 不命中索引的情况
SELECT * FROM users WHERE age = 25;               -- ❌ 不命中
SELECT * FROM users WHERE status = 1;             -- ❌ 不命中
SELECT * FROM users WHERE age = 25 AND status = 1;  -- ❌ 不命中

3.2 索引失效场景

场景	示例	说明
LIKE %开头	`LIKE '%三'`	%在左边无法利用索引
OR 连接	`WHERE name='A' OR age=25`	OR 前后未都用索引
类型转换	`WHERE phone='123'` vs `WHERE phone=123`	隐式类型转换
使用函数	`WHERE YEAR(create_time) = 2024`	索引列参与运算
范围冲突	`WHERE age > 5 AND age < 3`	范围不合法
NOT 操作	`WHERE status != 1`	部分场景失效

3.3 索引优化技巧

-- 1. 离散度高的列放前面
-- 假设有 sex(2种) 和 name(1000种)
CREATE INDEX idx_name_sex ON users(name, sex);  -- ✅ 推荐

-- 2. 覆盖索引减少回表
-- 查询尽量使用覆盖索引
SELECT id, name, age FROM users WHERE name = '张三';

-- 3. 字符串索引长度选择
-- 统计离散度选择合适长度
SELECT COUNT(DISTINCT LEFT(name, 5)) / COUNT(*) FROM users;

4. EXPLAIN 执行计划

4.1 执行计划字段

EXPLAIN SELECT * FROM users WHERE name = '张三';

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type| table | type | key           | rows | filtered| Extra|
+----+-------------+-------+------+---------------+------+---------+------+-------------+
|  1 | SIMPLE      | users | ref  | idx_name      |   10 |   100.00| NULL |
+----+-------------+-------+------+---------------+------+---------+------+-------------+

4.2 关键字段说明

字段	说明	优化目标
type	访问类型	最好达到 ref/const
key	实际使用索引	非空表示使用了索引
rows	预估扫描行数	越少越好
Extra	附加信息	避免 Using filesort, Using temporary

4.3 type 访问类型（从好到差）

类型	说明	性能
const	主键或唯一索引等值查询	最优
eq_ref	唯一索引关联查询	优
ref	非唯一索引等值查询	良
range	索引范围查询	中
index	全索引扫描	差
ALL	全表扫描	最差

4.4 Extra 重要信息

值	说明
Using index	使用覆盖索引
Using where	需要回表过滤
Using index condition	使用索引下推
Using filesort	需要额外排序
Using temporary	需要临时表

4.5 实战分析

-- 全表扫描（需优化）
EXPLAIN SELECT * FROM users WHERE age > 20;

-- 使用索引（良好）
EXPLAIN SELECT * FROM users WHERE name = '张三';

-- 索引失效（需优化）
EXPLAIN SELECT * FROM users WHERE name LIKE '%三%';

-- Using filesort（需优化）
EXPLAIN SELECT * FROM users ORDER BY age DESC;

-- Using index（最优）
EXPLAIN SELECT id, name FROM users WHERE name = '张三';

5. 慢查询日志

5.1 开启慢查询日志

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

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 超过1秒记录
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

5.2 配置文件永久开启

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

5.3 分析慢查询日志

# 使用 mysqldumpslow 分析
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 参数说明
# -s: 排序方式 (c/t/l/r)
# -t: 显示前 N 条

5.4 使用 EXPLAIN 分析

-- 开启 EXPLAIN 分析
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 25;

-- 查看查询成本
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE name = '张三';

6. 视图

6.1 创建视图

-- 创建视图
CREATE VIEW v_user_orders AS
SELECT 
    u.id,
    u.username,
    o.order_no,
    o.total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- 查询视图
SELECT * FROM v_user_orders;

6.2 视图特点

特点	说明
不存储数据	视图是虚拟表，查询时动态生成
简化复杂查询	将复杂 SQL 封装为视图
安全性	控制用户访问特定数据
可更新	简单视图可更新数据

6.3 管理视图

-- 查看视图
SHOW CREATE VIEW v_user_orders;

-- 修改视图
CREATE OR REPLACE VIEW v_user_orders AS
SELECT * FROM users WHERE status = 1;

-- 删除视图
DROP VIEW v_user_orders;

7. 存储过程

7.1 创建存储过程

DELIMITER //

CREATE PROCEDURE get_user_orders(IN user_id INT)
BEGIN
    SELECT 
        o.order_no,
        o.total_amount,
        o.created_at
    FROM orders o
    WHERE o.user_id = user_id
    ORDER BY o.created_at DESC;
END //

DELIMITER ;

-- 调用存储过程
CALL get_user_orders(1);

7.2 存储过程参数

DELIMITER //

CREATE PROCEDURE update_user_status(
    IN p_id INT,
    IN p_status INT,
    OUT p_result VARCHAR(50)
)
BEGIN
    UPDATE users SET status = p_status WHERE id = p_id;
    SET p_result = CONCAT('Updated ', ROW_COUNT(), ' rows');
END //

DELIMITER ;

-- 调用
CALL update_user_status(1, 0, @result);
SELECT @result;

7.3 存储函数

DELIMITER //

CREATE FUNCTION get_user_count(status INT)
RETURNS INT
BEGIN
    RETURN (SELECT COUNT(*) FROM users WHERE status = status);
END //

DELIMITER ;

-- 使用
SELECT get_user_count(1);

8. 触发器

8.1 创建触发器

-- 创建 INSERT 触发器
CREATE TRIGGER tr_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    -- 记录日志
    INSERT INTO order_logs (order_id, action, created_at)
    VALUES (NEW.id, 'INSERT', NOW());
END;

-- 创建 UPDATE 触发器
CREATE TRIGGER tr_order_update
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    INSERT INTO order_logs (order_id, action, old_status, new_status)
    VALUES (OLD.id, 'UPDATE', OLD.status, NEW.status);
END;

8.2 NEW 和 OLD 关键字

触发时机	NEW	OLD
INSERT	新插入的数据	NULL
UPDATE	更新后的数据	更新前的数据
DELETE	NULL	删除前的数据

9. 索引优化实战

9.1 优化案例：分页查询

-- 低效分页（深度分页）
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;
-- 问题：MySQL 先扫描前1000010条，再返回10条

-- 优化：使用延迟关联
SELECT * FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 1000000, 10
) t ON o.id = t.id;

-- 优化：记录游标
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 10;

9.2 优化案例：COUNT 查询

-- 低效：COUNT(*)
SELECT COUNT(*) FROM orders WHERE status = 1;

-- 优化：使用覆盖索引
SELECT COUNT(*) FROM orders WHERE status = 1;
-- 确保 status 字段有索引

-- 维护计数表
CREATE TABLE order_stats (
    status INT PRIMARY KEY,
    count INT DEFAULT 0
);

9.3 优化案例：JOIN 查询

-- 小表驱动大表原则
-- orders(1000万) 驱动 products(10万)

-- 低效写法
SELECT * FROM orders o
INNER JOIN products p ON o.product_id = p.id
WHERE p.category = '电子产品';

-- 优化写法
SELECT * FROM products p
INNER JOIN orders o ON o.product_id = p.id
WHERE p.category = '电子产品';

10. SQL 优化最佳实践

10.1 基础规范

规范	说明
避免 SELECT *	只查询需要的字段
批量操作	使用 INSERT 批量插入
合理使用索引	避免过多索引
分页优化	深度分页使用延迟关联

10.2 常见优化场景

-- 批量插入优化
INSERT INTO users (name, email) VALUES
    ('A', 'a@test.com'),
    ('B', 'b@test.com'),
    ('C', 'c@test.com');

-- 批量更新优化
UPDATE users SET status = 0 WHERE id IN (1, 2, 3);

-- 批量删除优化
DELETE FROM orders WHERE status = 0 AND created_at < '2024-01-01';