03-性能优化MySQL 性能优化 1. EXPLAIN 执行计划 1.1 EXPLAIN 基本使用 1.2 type

MySQL 性能优化

MySQL 性能优化和调优实战指南

1. EXPLAIN 执行计划

1.1 EXPLAIN 基本使用

EXPLAIN SELECT * FROM users WHERE age = 25;

-- 输出字段
id            -- 查询序列号
select_type   -- 查询类型
table         -- 表名
partitions    -- 分区信息
type          -- 访问类型（重要）
possible_keys -- 可能使用的索引
key           -- 实际使用的索引（重要）
key_len       -- 索引长度
ref           -- 索引比较的列
rows          -- 扫描行数（重要）
filtered      -- 过滤百分比
Extra         -- 额外信息（重要）

1.2 type 访问类型（从优到差）

-- system: 表中只有一行（系统表）
-- const: 主键或唯一索引查询，最多一行
EXPLAIN SELECT * FROM users WHERE id = 1;

-- eq_ref: 唯一索引扫描，JOIN 时常见
EXPLAIN SELECT * FROM orders o JOIN users u ON o.user_id = u.id;

-- ref: 非唯一索引扫描
EXPLAIN SELECT * FROM users WHERE city = 'Beijing';

-- range: 范围扫描（>, <, BETWEEN, IN）
EXPLAIN SELECT * FROM users WHERE age BETWEEN 18 AND 30;

-- index: 索引全扫描
EXPLAIN SELECT id FROM users;

-- ALL: 全表扫描（最差）
EXPLAIN SELECT * FROM users WHERE name LIKE '%alice%';

优化目标：type 至少达到 ref，避免 ALL。

1.3 Extra 额外信息

-- Using index: 覆盖索引（好）
EXPLAIN SELECT id, username FROM users WHERE username = 'alice';

-- Using where: 使用 WHERE 过滤（正常）

-- Using temporary: 使用临时表（差）
EXPLAIN SELECT DISTINCT city FROM users;

-- Using filesort: 文件排序（差）
EXPLAIN SELECT * FROM users ORDER BY age;

-- Using index condition: 索引下推（好）
EXPLAIN SELECT * FROM users WHERE age > 18 AND city = 'Beijing';

1.4 优化建议

-- ❌ 全表扫描
EXPLAIN SELECT * FROM users WHERE age + 1 = 26;
-- type: ALL, rows: 100000

-- ✅ 使用索引
EXPLAIN SELECT * FROM users WHERE age = 25;
-- type: ref, rows: 100

2. 索引优化

2.1 索引设计原则

1. 选择性高的列

-- ❌ 选择性低（不适合建索引）
CREATE INDEX idx_gender ON users(gender);  -- 只有 2-3 个值

-- ✅ 选择性高
CREATE INDEX idx_email ON users(email);    -- 几乎唯一

2. 最左前缀原则

-- 联合索引
CREATE INDEX idx_city_age_gender ON users(city, age, gender);

-- ✅ 会使用索引
WHERE city = 'Beijing'
WHERE city = 'Beijing' AND age = 25
WHERE city = 'Beijing' AND age = 25 AND gender = 'male'

-- ❌ 不会使用索引
WHERE age = 25
WHERE gender = 'male'

3. 覆盖索引

-- 创建索引
CREATE INDEX idx_username_email ON users(username, email);

-- ✅ 覆盖索引（无需回表）
SELECT username, email FROM users WHERE username = 'alice';

-- ❌ 需要回表
SELECT * FROM users WHERE username = 'alice';

4. 索引列不要太多

-- ❌ 索引过多
CREATE INDEX idx1 ON users(city);
CREATE INDEX idx2 ON users(age);
CREATE INDEX idx3 ON users(gender);
CREATE INDEX idx4 ON users(status);
-- 维护成本高，写入慢

-- ✅ 合理设计
CREATE INDEX idx_city_age ON users(city, age);  -- 联合索引

5. 前缀索引

-- 对于长字符串，使用前缀索引
CREATE INDEX idx_email ON users(email(20));  -- 只索引前 20 个字符

-- 计算选择性
SELECT 
    COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS sel10,
    COUNT(DISTINCT LEFT(email, 20)) / COUNT(*) AS sel20,
    COUNT(DISTINCT LEFT(email, 30)) / COUNT(*) AS sel30
FROM users;

2.2 索引失效场景总结

-- 1. 使用函数
WHERE YEAR(created_at) = 2024  -- ❌

-- 2. 类型转换
WHERE phone = 13800138000      -- ❌ (phone 是 VARCHAR)

-- 3. LIKE 左模糊
WHERE name LIKE '%alice'       -- ❌

-- 4. OR 条件（索引不完整）
WHERE age = 25 OR city = 'Beijing'  -- ❌

-- 5. 不等于
WHERE age != 25                -- ❌

-- 6. IS NOT NULL
WHERE email IS NOT NULL        -- ❌

-- 7. NOT IN、NOT EXISTS
WHERE id NOT IN (1,2,3)        -- ❌

3. SQL 优化

3.1 SELECT 优化

避免 SELECT *

-- ❌ 查询所有字段
SELECT * FROM users WHERE id = 1;

-- ✅ 只查询需要的字段
SELECT id, username, email FROM users WHERE id = 1;

-- 好处
1. 减少网络传输
2. 可能使用覆盖索引
3. 减少内存占用

使用 LIMIT

-- ❌ 查询所有
SELECT * FROM users;

-- ✅ 限制返回数量
SELECT * FROM users LIMIT 100;

避免子查询

-- ❌ 子查询
SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

-- ✅ JOIN
SELECT DISTINCT u.* 
FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE o.amount > 1000;

3.2 INSERT 优化

批量插入

-- ❌ 逐条插入
INSERT INTO users (username) VALUES ('user1');
INSERT INTO users (username) VALUES ('user2');
-- ...

-- ✅ 批量插入
INSERT INTO users (username) VALUES 
    ('user1'), ('user2'), ('user3'), ... ('user1000');

-- 建议：每批 1000-5000 条

禁用索引

-- 大量数据导入时
ALTER TABLE users DISABLE KEYS;
-- 导入数据...
ALTER TABLE users ENABLE KEYS;

使用 LOAD DATA

-- 从文件导入（最快）
LOAD DATA INFILE '/path/to/data.csv' 
INTO TABLE users 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n';

3.3 UPDATE 优化

使用索引

-- ❌ 全表扫描
UPDATE users SET age = 26 WHERE name = 'alice';

-- ✅ 使用索引
UPDATE users SET age = 26 WHERE id = 1;

批量更新

-- ❌ 逐条更新
UPDATE users SET status = 1 WHERE id = 1;
UPDATE users SET status = 1 WHERE id = 2;

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

3.4 DELETE 优化

分批删除

-- ❌ 一次性删除大量数据
DELETE FROM logs WHERE created_at < '2023-01-01';  -- 可能锁表

-- ✅ 分批删除
DELETE FROM logs WHERE created_at < '2023-01-01' LIMIT 1000;
-- 循环执行直到删除完

4. 慢查询优化

4.1 开启慢查询日志

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

-- 开启慢查询
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;  -- 2 秒
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

4.2 分析慢查询

# 使用 mysqldumpslow 分析
mysqldumpslow -s t -t 10 slow.log  # 按时间排序，显示前 10 条

# 参数
-s: 排序方式（t-时间，c-次数，l-锁时间）
-t: 显示条数
-g: 正则过滤

4.3 优化案例

案例 1：未使用索引

-- 问题
SELECT * FROM users WHERE YEAR(created_at) = 2024;
-- 扫描行数：100万，耗时：2秒

-- 优化
SELECT * FROM users 
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
-- 扫描行数：1万，耗时：0.01秒

案例 2：JOIN 未使用索引

-- 问题
SELECT * FROM orders o JOIN users u ON o.user_id = u.id;
-- users.id 有索引，但 orders.user_id 没有

-- 优化
CREATE INDEX idx_user_id ON orders(user_id);
-- 性能提升 100 倍

案例 3：深分页慢

-- 问题
SELECT * FROM users ORDER BY id LIMIT 1000000, 10;
-- 需要扫描 100 万行

-- 优化：使用子查询
SELECT * FROM users 
WHERE id >= (SELECT id FROM users ORDER BY id LIMIT 1000000, 1) 
LIMIT 10;

5. 表结构优化

5.1 选择合适的数据类型

原则

更小的更好：TINYINT vs INT
简单更好：INT vs VARCHAR
避免 NULL：NULL 需要额外存储

-- ❌ 类型过大
CREATE TABLE users (
    id BIGINT,           -- 实际只需 INT
    age INT,             -- 实际只需 TINYINT
    status VARCHAR(20)   -- 实际只需 TINYINT (0/1)
);

-- ✅ 合适的类型
CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT,
    age TINYINT UNSIGNED,
    status TINYINT DEFAULT 1
);

5.2 范式设计

第一范式（1NF）：字段不可分

-- ❌ 违反 1NF
CREATE TABLE users (
    id INT,
    name VARCHAR(100),
    address VARCHAR(200)  -- "北京市朝阳区xx路xx号" 可分
);

-- ✅ 符合 1NF
CREATE TABLE users (
    id INT,
    name VARCHAR(100),
    province VARCHAR(50),
    city VARCHAR(50),
    district VARCHAR(50),
    street VARCHAR(100)
);

第二范式（2NF）：非主属性完全依赖主键

-- ❌ 违反 2NF
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    product_name VARCHAR(100),  -- 依赖 product_id，不依赖主键
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

-- ✅ 符合 2NF
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

第三范式（3NF）：非主属性不依赖其他非主属性

-- ❌ 违反 3NF
CREATE TABLE users (
    id INT PRIMARY KEY,
    city VARCHAR(50),
    province VARCHAR(50)  -- 依赖 city
);

-- ✅ 符合 3NF
CREATE TABLE users (
    id INT PRIMARY KEY,
    city_id INT
);

CREATE TABLE cities (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    province VARCHAR(50)
);

5.3 反范式设计

何时反范式：

查询性能要求高
读多写少
可接受数据冗余

-- 订单表存储用户名（反范式）
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    username VARCHAR(50),  -- 冗余字段，避免 JOIN
    amount DECIMAL(10,2)
);

-- 好处：查询订单时无需 JOIN users 表
-- 坏处：用户改名需要同步更新所有订单

6. 分库分表

6.1 垂直拆分

垂直分库

-- 按业务拆分数据库
user_db:      users, user_profiles
order_db:     orders, order_items
product_db:   products, categories

垂直分表

-- 按字段拆分表（冷热分离）
-- 热数据表
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    status TINYINT
);

-- 冷数据表
CREATE TABLE user_profiles (
    user_id INT PRIMARY KEY,
    bio TEXT,
    avatar BLOB,
    settings JSON
);

6.2 水平拆分

水平分表

-- 按范围分表
users_2023
users_2024

-- 按哈希分表
users_0  -- user_id % 10 = 0
users_1  -- user_id % 10 = 1
...
users_9

-- 按地区分表
users_beijing
users_shanghai

分表策略

// 按 user_id 哈希
$tableIndex = $userId % 10;
$tableName = "users_{$tableIndex}";

// 按时间范围
$year = date('Y');
$tableName = "orders_{$year}";

6.3 分库分表中间件

ShardingSphere：Apache 开源
MyCat：国内开源
Vitess：YouTube 开源

7. 读写分离

7.1 架构

应用层
    ↓
  中间件（代理）
    ↓
┌───────┴────────┐
│                │
Master (写)    Slave (读)

7.2 实现方式

方式 1：应用层

class Database {
    private $master;  // 主库连接
    private $slaves;  // 从库连接池
    
    public function query($sql) {
        if ($this->isWriteSQL($sql)) {
            return $this->master->query($sql);
        } else {
            $slave = $this->getRandomSlave();
            return $slave->query($sql);
        }
    }
    
    private function isWriteSQL($sql) {
        return preg_match('/^\s*(INSERT|UPDATE|DELETE)/i', $sql);
    }
}

方式 2：中间件

ProxySQL
MySQL Router
MaxScale

7.3 读写分离问题

主从延迟

问题：刚写入数据，立即从从库读取，可能读不到

解决方案：
1. 强制主库读
2. 延迟读取
3. 缓存
4. 半同步复制

8. 查询优化技巧

8.1 避免回表

-- ❌ 需要回表
SELECT * FROM users WHERE username = 'alice';

-- ✅ 覆盖索引
SELECT id, username, email FROM users WHERE username = 'alice';
-- 索引：idx_username_email(username, email)

8.2 使用索引下推（ICP）

-- MySQL 5.6+ 支持索引下推

-- 索引：idx_city_age
SELECT * FROM users WHERE city = 'Beijing' AND age > 18;

-- 没有 ICP：
-- 1. 使用 city 索引查找所有 Beijing 的记录
-- 2. 回表获取完整行
-- 3. 过滤 age > 18

-- 有 ICP：
-- 1. 使用 city 索引查找
-- 2. 在索引中过滤 age > 18（减少回表）
-- 3. 回表获取完整行

8.3 优化 JOIN

小表驱动大表

-- ❌ 大表驱动小表
SELECT * FROM users u 
JOIN orders o ON u.id = o.user_id;
-- users: 100 万，orders: 1000

-- ✅ 小表驱动大表
SELECT * FROM orders o 
JOIN users u ON o.user_id = u.id;

确保 JOIN 字段有索引

-- ✅ 两个表的 JOIN 字段都要有索引
CREATE INDEX idx_user_id ON orders(user_id);
-- users.id 已有主键索引

8.4 优化 GROUP BY

-- ❌ 未使用索引
SELECT city, COUNT(*) FROM users GROUP BY city;

-- ✅ 创建索引
CREATE INDEX idx_city ON users(city);
SELECT city, COUNT(*) FROM users GROUP BY city;

-- ✅ 使用 ORDER BY NULL 禁用排序
SELECT city, COUNT(*) FROM users GROUP BY city ORDER BY NULL;

8.5 优化 ORDER BY

-- ❌ 未使用索引（Using filesort）
SELECT * FROM users ORDER BY age;

-- ✅ 使用索引
CREATE INDEX idx_age ON users(age);
SELECT * FROM users ORDER BY age;

-- ✅ 覆盖索引
CREATE INDEX idx_age_username ON users(age, username);
SELECT id, age, username FROM users ORDER BY age;

8.6 优化分页

-- ❌ 深分页慢
SELECT * FROM users ORDER BY id LIMIT 1000000, 10;
-- 需要扫描 100 万行

-- ✅ 使用子查询
SELECT * FROM users 
WHERE id >= (SELECT id FROM users ORDER BY id LIMIT 1000000, 1) 
LIMIT 10;

-- ✅ 记录上次查询的最大 ID
SELECT * FROM users WHERE id > 1000000 ORDER BY id LIMIT 10;

9. 配置优化

9.1 连接配置

# my.cnf

# 最大连接数
max_connections = 1000

# 连接超时
wait_timeout = 28800
interactive_timeout = 28800

9.2 缓存配置

# InnoDB 缓冲池（最重要）
innodb_buffer_pool_size = 8G  # 建议设置为物理内存的 50-70%

# 每个缓冲池实例大小
innodb_buffer_pool_instances = 8

# 查询缓存（MySQL 8.0 已移除）
# query_cache_size = 0

9.3 日志配置

# redo log
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 1  # 1-最安全，2-性能好

# binlog
log-bin = mysql-bin
binlog_format = ROW
max_binlog_size = 1G
expire_logs_days = 7

9.4 InnoDB 配置

# 每表一个文件
innodb_file_per_table = ON

# IO 线程数
innodb_read_io_threads = 4
innodb_write_io_threads = 4

# 刷新邻接页
innodb_flush_neighbors = 0  # SSD 设为 0

# 自适应哈希索引
innodb_adaptive_hash_index = ON

10. 监控和诊断

10.1 查看状态

-- 查看连接数
SHOW STATUS LIKE 'Threads_connected';
SHOW PROCESSLIST;

-- 查看 InnoDB 状态
SHOW ENGINE INNODB STATUS;

-- 查看表状态
SHOW TABLE STATUS LIKE 'users';

-- 查看索引使用情况
SELECT * FROM sys.schema_unused_indexes;

10.2 分析表

-- 分析表（更新索引统计信息）
ANALYZE TABLE users;

-- 优化表（重组表数据）
OPTIMIZE TABLE users;

-- 检查表
CHECK TABLE users;

-- 修复表
REPAIR TABLE users;

11. 总结

MySQL 性能优化核心：

✅ EXPLAIN 分析：type、key、rows、Extra
✅ 索引优化：选择性、最左前缀、覆盖索引
✅ SQL 优化：避免全表扫描、减少回表
✅ 慢查询优化：开启日志、分析、优化
✅ 表结构优化：合适的数据类型、范式设计
✅ 分库分表：垂直拆分、水平拆分
✅ 读写分离：主写从读
✅ 配置优化：缓冲池、连接数、日志

优化步骤：

使用 EXPLAIN 分析
查看慢查询日志
添加索引或优化 SQL
调整配置参数
考虑分库分表

性能指标：

type: 至少 ref
rows: 越小越好
Extra: 避免 Using temporary、Using filesort

下一步：实战案例和题！