性能优化

一、性能分析

1.1 EXPLAIN分析

1.1.1 EXPLAIN概述

EXPLAIN：分析SQL语句执行计划。

东巴文理解：

EXPLAIN = 体检报告
体检报告显示身体状况
EXPLAIN显示SQL执行情况
找出性能问题所在

1.1.2 基本用法

语法格式：

EXPLAIN SELECT语句;
EXPLAIN ANALYZE SELECT语句;  -- MySQL 8.0+

示例：

-- 创建测试表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    email VARCHAR(100),
    dept_id INT,
    INDEX idx_name (name),
    INDEX idx_dept (dept_id)
);

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

1.1.3 执行计划字段

重要字段：

1.1.4 访问类型

type字段值（从好到差）：

示例：

-- system：系统表
EXPLAIN SELECT * FROM mysql.proxies_priv LIMIT 1;

-- const：主键查询
EXPLAIN SELECT * FROM users WHERE id = 1;

-- eq_ref：JOIN时使用主键
EXPLAIN SELECT * FROM users u JOIN orders o ON u.id = o.user_id;

-- ref：非唯一索引
EXPLAIN SELECT * FROM users WHERE name = '张三';

-- range：范围查询
EXPLAIN SELECT * FROM users WHERE id BETWEEN 1 AND 100;

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

-- ALL：全表扫描
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

1.1.5 Extra字段

常见值：

示例：

-- Using index：覆盖索引
EXPLAIN SELECT id, name FROM users WHERE name = '张三';

-- Using temporary：临时表
EXPLAIN SELECT dept_id, COUNT(*) FROM users GROUP BY dept_id;

-- Using filesort：文件排序
EXPLAIN SELECT * FROM users ORDER BY email;

1.2 慢查询日志

1.2.1 开启慢查询日志

查看状态：

-- 查看慢查询日志状态
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

开启慢查询日志：

-- 临时开启
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';

-- 永久开启（配置文件）
[mysqld]
slow_query_log = ON
long_query_time = 2
slow_query_log_file = /var/log/mysql/mysql-slow.log

1.2.2 分析慢查询日志

使用mysqldumpslow：

# 查看慢查询统计
mysqldumpslow -s t /var/log/mysql/mysql-slow.log

# 查询最多的10条
mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log

# 平均查询时间最长的10条
mysqldumpslow -s at -t 10 /var/log/mysql/mysql-slow.log

使用pt-query-digest：

# 安装Percona Toolkit
apt-get install percona-toolkit

# 分析慢查询日志
pt-query-digest /var/log/mysql/mysql-slow.log > slow_report.txt

1.3 SHOW PROFILE

1.3.1 使用SHOW PROFILE

示例：

-- 开启profiling
SET profiling = ON;

-- 执行查询
SELECT * FROM users WHERE name = '张三';

-- 查看profile
SHOW PROFILES;

-- 查看详细profile
SHOW PROFILE FOR QUERY 1;

-- 查看特定资源
SHOW PROFILE CPU, BLOCK IO FOR QUERY 1;

1.3.2 profile字段

状态值：

1.4 Performance Schema

1.4.1 启用Performance Schema

查看状态：

-- 查看Performance Schema状态
SHOW VARIABLES LIKE 'performance_schema';

启用Performance Schema：

[mysqld]
performance_schema = ON

1.4.2 使用Performance Schema

示例：

-- 查看等待事件
SELECT * FROM performance_schema.events_waits_summary_global_by_event_name
WHERE COUNT_STAR > 0
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- 查看文件I/O
SELECT * FROM performance_schema.file_summary_by_instance
ORDER BY COUNT_READ DESC
LIMIT 10;

-- 查看表I/O
SELECT * FROM performance_schema.table_io_waits_summary_by_table
WHERE COUNT_READ > 0
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

二、索引优化

2.1 索引设计原则

2.1.1 选择合适的列

适合创建索引的列：

• WHERE子句中的列
• JOIN连接的列
• ORDER BY的列
• GROUP BY的列
• DISTINCT的列

不适合创建索引的列：

• 区分度低的列（如性别）
• 频繁更新的列
• 数据量小的表
• TEXT、BLOB类型的列

2.1.2 最左前缀原则

示例：

-- 创建复合索引
CREATE INDEX idx_name_dept_salary ON users(name, dept_id, salary);

-- 使用索引（符合最左前缀）
SELECT * FROM users WHERE name = '张三';
SELECT * FROM users WHERE name = '张三' AND dept_id = 1;
SELECT * FROM users WHERE name = '张三' AND dept_id = 1 AND salary > 10000;

-- 不使用索引（不符合最左前缀）
SELECT * FROM users WHERE dept_id = 1;
SELECT * FROM users WHERE salary > 10000;

2.2 避免索引失效

2.2.1 索引失效场景

1. 使用函数：

-- 索引失效
SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- 索引有效
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

2. 隐式类型转换：

-- 索引失效（字符串列使用数字）
SELECT * FROM users WHERE phone_number = 13800138000;

-- 索引有效（使用字符串）
SELECT * FROM users WHERE phone_number = '13800138000';

3. 使用OR：

-- 索引失效
SELECT * FROM users WHERE name = '张三' OR email = 'test@example.com';

-- 索引有效（使用UNION）
SELECT * FROM users WHERE name = '张三'
UNION
SELECT * FROM users WHERE email = 'test@example.com';

4. 使用NOT：

-- 索引失效
SELECT * FROM users WHERE name NOT LIKE '张%';

-- 索引有效
SELECT * FROM users WHERE name LIKE '李%';

5. 使用!=或<>：

-- 索引失效
SELECT * FROM users WHERE dept_id != 1;

-- 索引有效
SELECT * FROM users WHERE dept_id > 1;

6. 使用IS NULL或IS NOT NULL：

-- 索引失效
SELECT * FROM users WHERE name IS NULL;

-- 建议：设置默认值
ALTER TABLE users MODIFY name VARCHAR(50) DEFAULT '';

7. LIKE以通配符开头：

-- 索引失效
SELECT * FROM users WHERE name LIKE '%张%';

-- 索引有效
SELECT * FROM users WHERE name LIKE '张%';

2.3 覆盖索引

2.3.1 覆盖索引概念

覆盖索引：查询所需的所有字段都在索引中，无需回表。

示例：

-- 创建索引
CREATE INDEX idx_name_email ON users(name, email);

-- 使用覆盖索引
EXPLAIN SELECT name, email FROM users WHERE name = '张三';
-- Extra: Using index

2.3.2 覆盖索引优势

• 减少I/O操作
• 提高查询速度
• 减少锁竞争

2.4 索引下推

2.4.1 索引下推概念

索引下推（ICP）：在索引遍历过程中进行过滤，减少回表次数。

示例：

-- 创建索引
CREATE INDEX idx_name_dept ON users(name, dept_id);

-- 不使用索引下推（MySQL 5.6之前）
-- 1. 通过索引找到所有name='张三'的记录
-- 2. 回表查询完整记录
-- 3. 过滤dept_id=1的记录

-- 使用索引下推（MySQL 5.6+）
-- 1. 通过索引找到name='张三'且dept_id=1的记录
-- 2. 回表查询完整记录

EXPLAIN SELECT * FROM users WHERE name = '张三' AND dept_id = 1;
-- Extra: Using index condition

三、查询优化

3.1 查询重写

3.1.1 避免SELECT *

不推荐：

SELECT * FROM users WHERE id = 1;

推荐：

SELECT id, name, email FROM users WHERE id = 1;

3.1.2 使用LIMIT

示例：

-- 只需要一条记录
SELECT * FROM users WHERE name = '张三' LIMIT 1;

-- 分页查询
SELECT * FROM users LIMIT 0, 10;

3.1.3 使用JOIN代替子查询

不推荐：

SELECT * FROM users 
WHERE dept_id IN (SELECT id FROM departments WHERE dept_name = '技术部');

推荐：

SELECT u.* 
FROM users u
JOIN departments d ON u.dept_id = d.id
WHERE d.dept_name = '技术部';

3.1.4 使用UNION代替OR

不推荐：

SELECT * FROM users WHERE name = '张三' OR email = 'test@example.com';

推荐：

SELECT * FROM users WHERE name = '张三'
UNION
SELECT * FROM users WHERE email = 'test@example.com';

3.2 分页优化

3.2.1 传统分页问题

问题：

-- 偏移量大时性能差
SELECT * FROM users LIMIT 1000000, 10;

3.2.2 覆盖索引分页

优化方案：

-- 使用覆盖索引
SELECT u.* FROM users u
JOIN (SELECT id FROM users LIMIT 1000000, 10) t
ON u.id = t.id;

3.2.3 游标分页

优化方案：

-- 第一页
SELECT * FROM users WHERE id > 0 ORDER BY id LIMIT 10;

-- 下一页（假设上一页最后一条id=10）
SELECT * FROM users WHERE id > 10 ORDER BY id LIMIT 10;

3.3 ORDER BY优化

3.3.1 使用索引排序

示例：

-- 创建索引
CREATE INDEX idx_salary ON users(salary);

-- 使用索引排序
EXPLAIN SELECT * FROM users ORDER BY salary;
-- Extra: Using index

3.3.2 避免文件排序

不推荐：

-- 使用文件排序
SELECT * FROM users ORDER BY RAND();

推荐：

-- 使用索引排序
SELECT * FROM users ORDER BY id;

3.4 GROUP BY优化

3.4.1 使用索引分组

示例：

-- 创建索引
CREATE INDEX idx_dept ON users(dept_id);

-- 使用索引分组
EXPLAIN SELECT dept_id, COUNT(*) FROM users GROUP BY dept_id;
-- Extra: Using index

3.4.2 避免临时表

不推荐：

-- 使用临时表
SELECT dept_id, COUNT(*) FROM users GROUP BY dept_id ORDER BY COUNT(*) DESC;

推荐：

-- 使用索引
SELECT dept_id, COUNT(*) as cnt FROM users GROUP BY dept_id ORDER BY dept_id;

四、表结构优化

4.1 字段类型优化

4.1.1 选择合适的类型

整数类型：

-- 不推荐
CREATE TABLE users (
    id INT(11),
    age INT(11)
);

-- 推荐
CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    age TINYINT UNSIGNED
);

字符串类型：

-- 不推荐
CREATE TABLE users (
    name VARCHAR(255),
    phone VARCHAR(255)
);

-- 推荐
CREATE TABLE users (
    name VARCHAR(50),
    phone CHAR(11)
);

4.1.2 避免NULL

不推荐：

CREATE TABLE users (
    id INT,
    name VARCHAR(50) NULL
);

推荐：

CREATE TABLE users (
    id INT NOT NULL,
    name VARCHAR(50) NOT NULL DEFAULT ''
);

4.2 表分区

4.2.1 分区类型

RANGE分区：

CREATE TABLE orders (
    id INT,
    order_date DATE,
    amount DECIMAL(10, 2)
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

LIST分区：

CREATE TABLE users (
    id INT,
    region_id INT
)
PARTITION BY LIST (region_id) (
    PARTITION p_north VALUES IN (1, 2, 3),
    PARTITION p_south VALUES IN (4, 5, 6),
    PARTITION p_east VALUES IN (7, 8, 9),
    PARTITION p_west VALUES IN (10, 11, 12)
);

HASH分区：

CREATE TABLE users (
    id INT,
    name VARCHAR(50)
)
PARTITION BY HASH (id)
PARTITIONS 4;

4.2.2 分区管理

添加分区：

ALTER TABLE orders ADD PARTITION (
    PARTITION p2023 VALUES LESS THAN (2024)
);

删除分区：

ALTER TABLE orders DROP PARTITION p2020;

查看分区：

SELECT * FROM information_schema.PARTITIONS 
WHERE TABLE_NAME = 'orders';

4.3 表拆分

4.3.1 垂直拆分

拆分前：

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100),
    address TEXT,
    profile TEXT,
    preferences TEXT
);

拆分后：

-- 基础信息表
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);

-- 详细信息表
CREATE TABLE user_profiles (
    user_id INT PRIMARY KEY,
    address TEXT,
    profile TEXT,
    preferences TEXT
);

4.3.2 水平拆分

拆分前：

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    amount DECIMAL(10, 2)
);

拆分后：

-- 订单表1
CREATE TABLE orders_2023 (
    id INT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    amount DECIMAL(10, 2)
);

-- 订单表2
CREATE TABLE orders_2024 (
    id INT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    amount DECIMAL(10, 2)
);

五、服务器优化

5.1 内存优化

5.1.1 InnoDB缓冲池

查看配置：

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

设置建议：

[mysqld]
# 设置为物理内存的70-80%
innodb_buffer_pool_size = 4G

5.1.2 查询缓存

MySQL 8.0已移除查询缓存。

MySQL 5.7及以下：

[mysqld]
query_cache_type = 1
query_cache_size = 64M

5.2 连接优化

5.2.1 最大连接数

查看配置：

SHOW VARIABLES LIKE 'max_connections';

设置建议：

[mysqld]
max_connections = 1000

5.2.2 连接超时

查看配置：

SHOW VARIABLES LIKE 'wait_timeout';
SHOW VARIABLES LIKE 'interactive_timeout';

设置建议：

[mysqld]
wait_timeout = 28800
interactive_timeout = 28800

5.3 日志优化

5.3.1 二进制日志

查看配置：

SHOW VARIABLES LIKE 'binlog%';

设置建议：

[mysqld]
log-bin = mysql-bin
binlog_format = ROW
binlog_cache_size = 1M
max_binlog_size = 100M
expire_logs_days = 7

5.3.2 慢查询日志

设置建议：

[mysqld]
slow_query_log = ON
long_query_time = 2
slow_query_log_file = /var/log/mysql/mysql-slow.log

5.4 其他优化

5.4.1 临时表

查看配置：

SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';

设置建议：

[mysqld]
tmp_table_size = 64M
max_heap_table_size = 64M

5.4.2 排序缓冲

查看配置：

SHOW VARIABLES LIKE 'sort_buffer_size';

设置建议：

[mysqld]
sort_buffer_size = 2M

六、实战案例

6.1 慢查询优化案例

问题SQL：

SELECT * FROM orders WHERE YEAR(order_date) = 2024;

分析：

EXPLAIN SELECT * FROM orders WHERE YEAR(order_date) = 2024;
-- type: ALL
-- Extra: Using where

优化方案：

-- 方案1：修改查询条件
SELECT * FROM orders 
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';

-- 方案2：创建函数索引（MySQL 8.0+）
CREATE INDEX idx_year ON orders((YEAR(order_date)));

6.2 分页查询优化案例

问题SQL：

SELECT * FROM users ORDER BY created_at LIMIT 1000000, 10;

分析：

EXPLAIN SELECT * FROM users ORDER BY created_at LIMIT 1000000, 10;
-- type: ALL
-- Extra: Using filesort

优化方案：

-- 方案1：覆盖索引
SELECT u.* FROM users u
JOIN (SELECT id FROM users ORDER BY created_at LIMIT 1000000, 10) t
ON u.id = t.id;

-- 方案2：游标分页
SELECT * FROM users WHERE id > 1000000 ORDER BY id LIMIT 10;

6.3 JOIN查询优化案例

问题SQL：

SELECT u.name, o.order_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.dept_id = 1;

分析：

EXPLAIN SELECT u.name, o.order_id FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE u.dept_id = 1;

优化方案：

-- 创建索引
CREATE INDEX idx_dept ON users(dept_id);
CREATE INDEX idx_user ON orders(user_id);

-- 优化查询
SELECT u.name, o.order_id
FROM users u FORCE INDEX (idx_dept)
LEFT JOIN orders o FORCE INDEX (idx_user) ON u.id = o.user_id
WHERE u.dept_id = 1;

七、本章小结

7.1 核心要点

✅ EXPLAIN分析SQL执行计划 ✅ 慢查询日志定位性能问题 ✅ 索引优化是关键 ✅ 查询重写提高性能 ✅ 表结构优化减少冗余 ✅ 服务器优化提升整体性能

7.2 验证清单

完成本章学习后，请确认您能够：

• 使用EXPLAIN分析SQL
• 分析慢查询日志
• 优化索引设计
• 重写低效查询
• 优化表结构
• 调整服务器参数