第16章：MySQL 性能优化基础（提升效率）

MySQL 性能优化是数据库管理的重要组成部分，本章将介绍如何通过合理的优化手段提高 MySQL 数据库的性能，包括索引优化、SQL 语句优化和数据表优化。

16.1 索引优化（重点）

16.1.1 索引的作用

索引是提高查询效率的关键，它可以：

加速数据查询速度
减少数据库服务器的IO操作
加速排序和分组操作
提高数据检索效率

16.1.2 合理创建索引

1. 适合创建索引的字段

频繁查询的字段：WHERE 子句中经常使用的字段
排序字段：ORDER BY 子句中的字段
分组字段：GROUP BY 子句中的字段
连接字段：JOIN 操作中的关联字段
唯一性字段：如身份证号、用户名等

2. 不适合创建索引的字段

频繁更新的字段：索引需要频繁维护，影响性能
基数低的字段：如性别、状态等（值的多样性低）
文本字段：全文索引除外
小表：数据量小的表，全表扫描可能更快

16.1.3 索引类型选择

索引类型	适用场景	特点
B-Tree 索引	范围查询、排序	最常用，默认索引类型
Hash 索引	等值查询	只支持等值比较，不支持范围查询
全文索引	文本搜索	支持全文搜索
空间索引	地理数据	用于地理空间数据

16.1.4 复合索引的使用

最左前缀原则：

复合索引 (a, b, c) 会自动包含 (a)、(a,b) 索引
查询条件必须从最左边开始使用

示例：

sql

-- 创建复合索引
CREATE INDEX idx_name_age ON users(name, age);

-- 会使用索引的查询
SELECT * FROM users WHERE name = '张三';
SELECT * FROM users WHERE name = '张三' AND age = 18;

-- 不会使用索引的查询
SELECT * FROM users WHERE age = 18;  -- 违反最左前缀原则

16.1.5 索引使用注意事项

1. 避免索引失效

不要在索引字段上使用函数：WHERE YEAR(create_time) = 2023
不要对索引字段进行计算：WHERE price * 0.8 < 100
避免使用 !=、<> 操作符：WHERE status != 1
避免使用 IS NULL、IS NOT NULL：WHERE email IS NULL
避免使用 LIKE '%xxx'：WHERE name LIKE '%张%'
避免使用 OR：WHERE name = '张三' OR age = 18

2. 优化索引使用

使用覆盖索引：查询的字段都在索引中，避免回表
使用索引提示：SELECT * FROM users FORCE INDEX(idx_name) WHERE name = '张三'
定期重建索引：ALTER TABLE users ENGINE=InnoDB
分析索引使用情况：SHOW INDEX FROM users

16.2 SQL语句优化

16.2.1 优化查询语句

1. 简化查询

**避免 SELECT ***：只查询需要的字段
使用 LIMIT：限制返回记录数
合理使用子查询：避免多层嵌套
使用 JOIN 代替子查询：某些情况下 JOIN 更高效

2. 优化 WHERE 子句

使用索引字段：WHERE 条件优先使用索引字段
避免函数操作：不要在索引字段上使用函数
使用范围查询：合理使用 BETWEEN、IN 等
避免全表扫描：确保查询条件能使用索引

3. 优化 JOIN 操作

小表驱动大表：将小表作为驱动表
使用等值连接：避免复杂的连接条件
添加连接字段索引：确保连接字段有索引
避免笛卡尔积：确保连接条件正确

4. 优化 GROUP BY 和 ORDER BY

使用索引：GROUP BY 和 ORDER BY 的字段最好有索引
避免使用临时表：复杂的 GROUP BY 可能使用临时表
合理排序：避免不必要的排序

16.2.2 示例优化

优化前：

sql

-- 全表扫描
SELECT * FROM users WHERE age > 18;

-- 使用函数，索引失效
SELECT * FROM users WHERE YEAR(create_time) = 2023;

-- 不使用索引
SELECT * FROM users ORDER BY name;

优化后：

sql

-- 添加索引
CREATE INDEX idx_age ON users(age);
SELECT * FROM users WHERE age > 18;

-- 避免函数
SELECT * FROM users WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';

-- 添加索引
CREATE INDEX idx_name ON users(name);
SELECT * FROM users ORDER BY name;

16.3 数据表优化

16.3.1 合理选择数据类型

1. 数值类型

类型	大小	范围	适用场景
`TINYINT`	1字节	-128~127	小整数，如状态
`SMALLINT`	2字节	-32768~32767	小整数，如年龄
`INT`	4字节	-2147483648~2147483647	常用整数
`BIGINT`	8字节	很大范围	大整数，如ID
`DECIMAL`	可变	精确小数	金额、价格

2. 字符串类型

类型	特点	适用场景
`CHAR`	固定长度	长度固定的字符串，如身份证号
`VARCHAR`	可变长度	长度可变的字符串，如姓名
`TEXT`	大文本	长文本，如文章内容

3. 日期时间类型

类型	大小	范围	适用场景
`DATE`	3字节	1000-01-01~9999-12-31	日期
`TIME`	3字节	-838:59:59~838:59:59	时间
`DATETIME`	8字节	1000-01-01 00:00:00~9999-12-31 23:59:59	日期时间
`TIMESTAMP`	4字节	1970-01-01 00:00:00~2038-01-19 03:14:07	时间戳

16.3.2 表结构优化

1. 表拆分

垂直拆分：将大表拆分为多个小表，按列拆分
水平拆分：将大表按行拆分，按时间或ID范围拆分

2. 反范式设计

适当冗余：为了提高查询效率，适当冗余数据
缓存字段：添加缓存字段，避免复杂计算

3. 分区表

范围分区：按时间范围分区
列表分区：按特定值分区
哈希分区：按哈希值分区

16.3.3 示例优化

优化前：

sql

-- 表结构不合理
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(255),  -- 过长
    age INT,            -- 可以使用 TINYINT
    bio TEXT,           -- 大文本
    created_at DATETIME
);

优化后：

sql

-- 优化表结构
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),   -- 合理长度
    age TINYINT,        -- 节省空间
    created_at DATETIME
);

-- 拆分大文本到单独表
CREATE TABLE user_profiles (
    user_id INT PRIMARY KEY,
    bio TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

16.4 查看SQL执行效率

16.4.1 使用 EXPLAIN 分析

EXPLAIN 语法：

sql

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

EXPLAIN 输出解读：

字段	含义	说明
`id`	查询ID	多个查询时的顺序
`select_type`	查询类型	SIMPLE、PRIMARY、SUBQUERY等
`table`	表名	正在访问的表
`type`	访问类型	ALL、index、range、ref、eq_ref、const
`possible_keys`	可能的索引	可能使用的索引
`key`	实际使用的索引	实际使用的索引
`key_len`	索引长度	使用的索引长度
`ref`	引用	索引引用的列
`rows`	估计行数	MySQL 估计需要扫描的行数
`Extra`	额外信息	Using index、Using where、Using temporary等

访问类型优先级（从优到差）：system > const > eq_ref > ref > range > index > ALL

16.4.2 分析执行计划

示例分析：

sql

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

输出：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	users	ref	idx_name	idx_name	152	const	1	Using index condition

解读：

type: ref：使用了索引查找
key: idx_name：使用了名为 idx_name 的索引
rows: 1：估计只需要扫描1行
Extra: Using index condition：使用了索引条件

16.4.3 慢查询日志

启用慢查询日志：

sql

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

-- 启用慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 1;  -- 1秒以上的查询

分析慢查询日志：

使用 mysqldumpslow 工具分析
使用 pt-query-digest 工具分析
找出执行时间长的SQL语句并优化

16.5 性能优化实战

16.5.1 案例1：优化查询速度

问题： 查询用户列表速度慢

分析：

表数据量：100万行
查询语句：`SELECT * FROM users WHERE age > 18 ORDER BY created_at DESC LIMIT 10;
执行时间：5秒

优化步骤：

添加索引：

sql

CREATE INDEX idx_age_created_at ON users(age, created_at);

修改查询语句：

sql

SELECT id, name, age, created_at FROM users WHERE age > 18 ORDER BY created_at DESC LIMIT 10;

执行时间：0.01秒

16.5.2 案例2：优化JOIN查询

问题： 多表连接查询速度慢

分析：

表结构：orders 和 products
查询语句：`SELECT o.*, p.name FROM orders o JOIN products p ON o.product_id = p.id WHERE o.status = 'completed';
执行时间：3秒

优化步骤：

添加索引：

sql

CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_product_id ON orders(product_id);

修改查询语句：

sql

SELECT o.id, o.order_no, o.amount, p.name 
FROM orders o 
JOIN products p ON o.product_id = p.id 
WHERE o.status = 'completed';

执行时间：0.05秒

16.6 本章小结

优化方向	具体措施	效果
索引优化	合理创建索引，避免索引失效	提高查询速度
SQL优化	简化查询，优化WHERE子句	减少执行时间
表结构优化	合理选择数据类型，表拆分	提高存储效率
执行计划分析	使用EXPLAIN分析	找出性能瓶颈
慢查询优化	分析慢查询日志	优化热点SQL

性能优化原则：

先分析，后优化：使用 EXPLAIN 分析执行计划
从瓶颈入手：优先解决最慢的查询
综合考虑：索引、SQL、表结构等多方面优化
持续监控：定期检查性能，发现问题及时优化
适度优化：避免过度优化，保持代码可读性

性能优化是一个持续的过程，需要根据实际情况不断调整和改进。

第16章：MySQL 性能优化基础（提升效率） ​

16.1 索引优化（重点） ​

16.1.1 索引的作用 ​

16.1.2 合理创建索引 ​

16.1.3 索引类型选择 ​

16.1.4 复合索引的使用 ​

16.1.5 索引使用注意事项 ​

16.2 SQL语句优化 ​

16.2.1 优化查询语句 ​

16.2.2 示例优化 ​

16.3 数据表优化 ​

16.3.1 合理选择数据类型 ​

16.3.2 表结构优化 ​

16.3.3 示例优化 ​

16.4 查看SQL执行效率 ​

16.4.1 使用 EXPLAIN 分析 ​

16.4.2 分析执行计划 ​

16.4.3 慢查询日志 ​

16.5 性能优化实战 ​

16.5.1 案例1：优化查询速度 ​

16.5.2 案例2：优化JOIN查询 ​

16.6 本章小结 ​