1.查询执行计划的分析与优化（EXPLAIN）

1.1 EXPLAIN 核心字段详解

EXPLAIN SELECT * FROM users WHERE id = 100;

字段	含义	关键优化点
id	查询序列号，值越大越先执行	关注子查询和 UNION 的执行顺序
select_type	查询类型（SIMPLE/SUBQUERY/DERIVED）	DERIVED 临时表可能效率低，考虑优化
table	访问的表名	识别多表 JOIN 的执行顺序
type	访问方式（ALL/index/ref/range/const）	核心指标，至少达到 range 级别
possible_keys	可能使用的索引	判断索引是否生效
key	实际使用的索引	与 possible_keys 对比，避免索引失效
key_len	索引使用的字节数	判断联合索引使用列数
rows	预估扫描行数	值越大越慢，需优化索引或查询条件
Extra	额外信息（Using index/Using filesort/Using temporary）	Using filesort/temporary 需重点优化

1.2 关键 type 类型性能排序

const > eq_ref > ref > range > index > ALL
⭐️const：主键/唯一索引等值查询，最多返回 1 行（最佳）
⭐️ref：普通索引等值查询，返回匹配行（良好）
⭐️range：索引范围查询（BETWEEN/IN/>,<），需控制范围大小（可用）
⭐️index：全索引扫描（通常比 ALL 快，但需优化）
⭐️ALL：全表扫描（必须优化）

1.3 Extra 危险信号与优化

– 危险：Using filesort（额外排序）

EXPLAIN SELECT * FROM orders ORDER BY created_at LIMIT 10;
– 优化：created_at 加索引，覆盖索引避免回表
ALTER TABLE orders ADD INDEX idx_created_at (created_at, id, amount);

– 危险：Using temporary（创建临时表）
EXPLAIN SELECT DISTINCT user_id FROM orders WHERE amount > 100;
– 优化：user_id 加索引，避免临时表
ALTER TABLE orders ADD INDEX idx_user_amount (user_id, amount);

2.索引优化与索引失效的常见场景

2.1 索引设计黄金法则

最左前缀原则：联合索引 (a,b,c) 只能生效于 a / a,b / a,b,c 查询

-- 索引：INDEX idx_a_b_c (a, b, c)

-- ✅ 生效场景
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3
WHERE a > 1 AND b = 2  -- 范围查询后列失效

-- ❌ 失效场景
WHERE b = 2              -- 跳过 a
WHERE c = 3              -- 跳过 a,b
WHERE a = 1 AND c = 3    -- 跳过 b

2.2 索引失效的 8 大常见场景

场景	示例	原因	优化方案
1. 使用函数或运算	WHERE YEAR(created_at) = 2023	破坏索引有序性	WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31'
2. 隐式类型转换	WHERE phone = 13800138000	字符串转数字	WHERE phone = '13800138000'
3. LIKE 前导模糊	WHERE name LIKE '%张%'	无法定位起始位置	使用全文索引或 Elasticsearch
4. 使用 OR 条件	WHERE a = 1 OR b = 2	无法同时使用两个索引	拆分为 UNION 或创建联合索引
5. 联合索引范围查询	WHERE a > 1 AND b = 2	范围后列失效	调整索引顺序为 (b, a)
6. 使用 NOT/<>	WHERE status <> 1	无法利用索引树	转换为 IN (2,3,4) 或保留小范围扫描
7. IS NULL 优化	WHERE name IS NULL	统计信息不准	确保 nulls 分布均匀或设置 NOT NULL 默认值
8. 小表全表扫描	表行数 < 1000	优化器认为全表更快	无需优化，属于正常行为

2.3 覆盖索引优化回表

-- 原始查询（回表 10000 次）
SELECT id, name, age FROM users WHERE city = '北京' AND age > 20;

-- 优化：创建覆盖索引
ALTER TABLE users ADD INDEX idx_city_age_name (city, age, name);

-- 优化后 Extra 显示 Using index，无需回表

覆盖索引判断标准：Extra = Using index，且 key_len 包含所有查询字段

3.表结构设计与规范化/反规范化

3.1 规范化范式权衡

第三范式（3NF）优势
⭐️数据冗余小：节省存储空间，更新时只需修改一处
⭐️一致性保证：外键约束保证数据引用完整性
⭐️适合 OLTP：高频增删改操作效率高

-- 规范化设计
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    product_id INT,
    quantity INT,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

反规范化场景与收益
⭐️查询性能优先：将关联查询转换为单表查询，减少 JOIN 开销
⭐️读多写少：报表、BI 分析场景，数据更新频率低

-- 反规范化设计：冗余用户姓名到订单表
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    user_name VARCHAR(50),  -- 冗余字段
    product_id INT,
    quantity INT,
    INDEX idx_user_name (user_name)  -- 加速按用户姓名查询
);

-- 触发器或应用层保证一致性

混合策略最佳实践

场景	策略	实现方式
高频查询字段	反规范化冗余	用户昵称、商品标题冗余到订单表
低频变更字典	反规范化冗余	地区码表、分类信息冗余
关联统计	预计算汇总表	每日订单统计、用户积分汇总
核心业务	保持规范化	资金流水、库存扣减必须规范

3.2 字段类型优化

-- ❌ 不推荐：使用大类型
user_id BIGINT,  -- 20 亿数据内使用 INT 足够
status VARCHAR(20),  -- 用 TINYINT 枚举代替

-- ✅ 推荐：最小够用原则
user_id INT UNSIGNED,  -- 无符号 0-42 亿
status TINYINT,  -- 0:待支付 1:已支付 2:已发货
price DECIMAL(10,2),  -- 精确金额避免浮点误差
created_at DATETIME,  -- TIMESTAMP 仅支持 1970-2038

-- 字符集选择
-- utf8mb4（支持 emoji，MySQL 8.0 默认） vs utf8（仅 3 字节）

3.3 分区表策略

-- 按时间范围分区，加速历史数据清理
CREATE TABLE orders (
    order_id INT,
    created_at DATETIME
) PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

-- 优势：DROP PARTITION 比 DELETE 快 1000 倍

4.服务器参数调优与缓存策略

4.1 核心内存参数调优

参数	推荐值	作用	优化效果
innodb_buffer_pool_size	物理内存 50%-70%	InnoDB 数据页缓存	命中率 > 99%，减少磁盘 I/O
innodb_buffer_pool_instances	8-16	减少锁竞争	提升并发性能 10-30%
key_buffer_size	128M-256M	MyISAM 索引缓存	MyISAM 表性能提升
query_cache_size	0（MySQL 8.0 已移除）	查询结果缓存	高并发下反而降低性能
tmp_table_size/max_heap_table_size	64M-256M	内存临时表大小	避免磁盘临时表
innodb_log_file_size	2G-4G	Redo Log 大小	减少 Checkpoint 频率
innodb_flush_log_at_trx_commit	2（安全）/ 0（性能）	日志刷盘策略	平衡性能与持久性

4.2 InnoDB 核心调优公式

# 通用服务器（16G 内存）
innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 8
innodb_log_file_size = 2G
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT  # 绕过文件系统缓存

# 高性能写入场景（SSD）
innodb_flush_log_at_trx_commit = 0  # 每秒刷盘，性能最高但可能丢 1 秒数据
sync_binlog = 0  # 关闭 binlog 同步刷盘

4.3 慢查询优化四步法

步骤 1：开启慢查询日志

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 记录超过 1 秒的查询
SET GLOBAL log_queries_not_using_indexes = ON;

步骤 2：使用 pt-query-digest 分析

pt-query-digest /var/log/mysql/slow.log > slow_report.txt
# 输出 TOP 10 慢查询及执行占比

步骤 3：优化 SQL 或索引
⭐️对 rows_examined 远大于 rows_sent 的查询加索引
⭐️对 Using filesort 查询创建复合索引

步骤 4：验证效果

-- 优化前
SELECT * FROM orders WHERE user_id = 12345;  -- 扫描 100000 行

-- 优化后
ALTER TABLE orders ADD INDEX idx_user_id (user_id);
-- 扫描 50 行，性能提升 2000 倍

4.4 应用层缓存策略

多级缓存架构

用户请求 → [本地缓存 Caffeine] → [Redis 集群] → [MySQL]
          (10s TTL)        (1h TTL)      (持久化)

缓存更新策略
Cache Aside（旁路缓存） —— 最常用

# 读取逻辑
def get_user(user_id):
    # 1. 读缓存
    user = redis.get(f"user:{user_id}")
    if user:
        return json.loads(user)
    
    # 2. 缓存未命中，读数据库
    user = mysql.query("SELECT * FROM users WHERE id = %s", user_id)
    
    # 3. 回填缓存
    if user:
        redis.setex(f"user:{user_id}", 3600, json.dumps(user))
    return user

# 更新逻辑
def update_user(user_id, data):
    # 1. 先更新数据库
    mysql.execute("UPDATE users SET name = %s WHERE id = %s", data['name'], user_id)
    
    # 2. 删除缓存（而非更新）
    redis.delete(f"user:{user_id}")

Write Through（穿透缓存）

# 同时写缓存和数据库，保证强一致
def update_user(user_id, data):
    redis.set(f"user:{user_id}", json.dumps(data))
    mysql.execute("UPDATE users SET name = %s WHERE id = %s", data['name'], user_id)

4.5 连接池优化

// HikariCP 推荐配置
HikariConfig config = new HikariConfig();
config.setMaximumPoolSize(20);  // 核心数 * 2 + 有效磁盘数
config.setMinimumIdle(10);
config.setConnectionTimeout(30000);
config.setIdleTimeout(600000);
config.setMaxLifetime(1800000);
config.setLeakDetectionThreshold(60000);

公式：连接池大小 = (CPU 核心数 * 2) + 有效磁盘数，通常 20-50 足够

5.优化检查清单

SQL 层
⭐️所有查询 type 至少为 range 级别
⭐️Extra 无 Using filesort 和 Using temporary
⭐️使用覆盖索引避免回表
⭐️避免在 WHERE 条件中使用函数和运算
⭐️大表必须包含分区或分片策略
索引层
⭐️联合索引遵循最左前缀原则
⭐️区分度低的字段（如性别）不单独建索引
⭐️索引数量不超过 6 个/表，避免写入性能下降
⭐️定期使用 pt-index-usage 清理无用索引
配置层
⭐️innodb_buffer_pool_size 设置合理（>70% 物理内存）
⭐️ innodb_log_file_size > 2G，避免频繁刷盘
⭐️慢查询日志开启，long_query_time ≤ 1 秒
⭐️连接池大小符合 (CPU 核心数 * 2) + 磁盘数
架构层
⭐️读多写少场景实现读写分离
⭐️热点数据使用 Redis 缓存，命中率 > 90%
⭐️大数据量（>5000 万）考虑水平分片或 TiDB 迁移
⭐️历史数据定期归档，保持热表行数 < 1000 万

6.真实案例：千万级订单表优化

问题 SQL

-- 原始查询：每天定时统计各城市订单数，执行时间 8.2 秒
SELECT city, COUNT(*) 
FROM orders 
WHERE created_at >= '2024-01-01' 
GROUP BY city;

EXPLAIN 分析

type: ALL  (全表扫描)
rows: 15,000,000
Extra: Using where; Using temporary; Using filesort

四步优化
1. 索引优化

-- 创建分区 + 复合索引
ALTER TABLE orders PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

ALTER TABLE orders ADD INDEX idx_created_city (created_at, city);

2. 查询改写

-- 改为覆盖索引查询，避免回表
SELECT city, COUNT(*) 
FROM orders 
WHERE created_at >= '2024-01-01' 
  AND created_at < '2024-01-02'  -- 缩小范围
GROUP BY city;

3. 缓存策略

# 统计结果缓存 5 分钟
result = redis.get("stats:orders:20240101")
if not result:
    result = mysql.query("SELECT city, COUNT(*) FROM orders ...")
    redis.setex("stats:orders:20240101", 300, result)

4. 参数调优

innodb_buffer_pool_size = 32G  # 原 8G
innodb_buffer_pool_instances = 16  # 避免锁竞争

优化效果
⭐️执行时间：8.2 秒 → 0.12 秒（提升 68 倍）
⭐️扫描行数：1500 万 → 50 万（减少 97%）
⭐️缓存命中率：92%（减少 90% 数据库请求）
通过系统性优化，该查询从无法接受的慢查询变为毫秒级响应，系统整体吞吐量提升 5 倍以上