SQL 优化

执行计划

EXPLAIN 使用

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

执行计划字段

字段	说明
id	查询序号
select_type	查询类型
table	表名
type	访问类型
possible_keys	可能使用的索引
key	实际使用的索引
key_len	索引长度
ref	索引比较的列
rows	预估扫描行数
Extra	额外信息

type 访问类型

性能从好到差：

类型	说明
system	表只有一行
const	主键或唯一索引等值查询
eq_ref	主键或唯一索引关联查询
ref	非唯一索引等值查询
range	索引范围查询
index	全索引扫描
ALL	全表扫描

提示

一般要求 type 至少达到 range 级别，最好是 ref。

Extra 常见值

值	说明
Using index	覆盖索引
Using where	使用 WHERE 过滤
Using temporary	使用临时表
Using filesort	使用文件排序
Using index condition	索引下推

慢查询优化

开启慢查询日志

-- 查看慢查询配置
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';

分析慢查询日志

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

# -s：排序方式（t-时间，c-次数，r-返回行数）
# -t：显示前N条

常见优化场景

1. 避免 SELECT *

-- 不推荐
SELECT * FROM users WHERE id = 1;

-- 推荐
SELECT id, name, email FROM users WHERE id = 1;

2. 分页优化

-- 深分页问题
SELECT * FROM users LIMIT 1000000, 10;  -- 慢

-- 优化方案1：使用主键
SELECT * FROM users WHERE id > 1000000 LIMIT 10;

-- 优化方案2：延迟关联
SELECT * FROM users u
INNER JOIN (SELECT id FROM users LIMIT 1000000, 10) t
ON u.id = t.id;

3. JOIN 优化

-- 小表驱动大表
SELECT * FROM small_table s
INNER JOIN big_table b ON s.id = b.s_id;

-- 确保关联字段有索引
CREATE INDEX idx_s_id ON big_table(s_id);

4. ORDER BY 优化

-- 利用索引排序
-- 索引 (a, b)
SELECT * FROM t WHERE a = 1 ORDER BY b;  -- 使用索引排序

-- 避免 filesort
SELECT * FROM t ORDER BY a, b;  -- 使用索引排序
SELECT * FROM t ORDER BY a DESC, b ASC;  -- filesort（排序方向不一致）

5. GROUP BY 优化

-- 利用索引分组
-- 索引 (a, b)
SELECT a, COUNT(*) FROM t GROUP BY a;  -- 使用索引

-- 避免临时表
SELECT a, b, COUNT(*) FROM t GROUP BY a, b;  -- 使用索引

6. COUNT 优化

-- COUNT(*) vs COUNT(1) vs COUNT(column)
-- COUNT(*) 和 COUNT(1) 性能相同
-- COUNT(column) 不统计 NULL 值

-- 大表 COUNT 优化
-- 方案1：使用近似值
SHOW TABLE STATUS LIKE 'users';  -- 查看 Rows 字段

-- 方案2：维护计数表
CREATE TABLE table_counts (
    table_name VARCHAR(64) PRIMARY KEY,
    row_count BIGINT
);

7. IN 优化

-- IN 列表不要太长
SELECT * FROM users WHERE id IN (1, 2, 3, ..., 1000);

-- 优化：使用临时表
CREATE TEMPORARY TABLE tmp_ids (id INT PRIMARY KEY);
INSERT INTO tmp_ids VALUES (1), (2), (3), ...;
SELECT * FROM users u INNER JOIN tmp_ids t ON u.id = t.id;

8. EXISTS vs IN

-- 子查询结果集小，用 IN
SELECT * FROM t1 WHERE id IN (SELECT id FROM t2);

-- 子查询结果集大，用 EXISTS
SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.id = t1.id);

9. UNION vs UNION ALL

-- UNION 会去重，有额外开销
SELECT * FROM t1 UNION SELECT * FROM t2;

-- 不需要去重时使用 UNION ALL
SELECT * FROM t1 UNION ALL SELECT * FROM t2;

索引优化

查看索引使用情况

-- 查看表的索引
SHOW INDEX FROM users;

-- 查看索引使用统计
SELECT * FROM sys.schema_index_statistics WHERE table_name = 'users';

-- 查看未使用的索引
SELECT * FROM sys.schema_unused_indexes;

强制使用索引

-- 强制使用指定索引
SELECT * FROM users FORCE INDEX(idx_name) WHERE name = '张三';

-- 忽略指定索引
SELECT * FROM users IGNORE INDEX(idx_name) WHERE name = '张三';

表结构优化

1. 选择合适的数据类型

-- 整数类型
TINYINT    -- 1字节
SMALLINT   -- 2字节
INT        -- 4字节
BIGINT     -- 8字节

-- 字符串类型
CHAR(10)      -- 定长，适合固定长度
VARCHAR(100)  -- 变长，适合可变长度

-- 时间类型
DATETIME   -- 8字节，范围大
TIMESTAMP  -- 4字节，范围小，自动时区转换

2. 避免 NULL

-- 不推荐
CREATE TABLE t (
    name VARCHAR(50)
);

-- 推荐
CREATE TABLE t (
    name VARCHAR(50) NOT NULL DEFAULT ''
);

3. 适当拆分大表

-- 垂直拆分：将不常用的列拆分到另一张表
-- 水平拆分：按某个字段分表（如按时间、用户ID）

配置优化

缓冲池

-- 查看缓冲池大小
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- 设置缓冲池大小（建议为物理内存的 60-80%）
SET GLOBAL innodb_buffer_pool_size = 4294967296;  -- 4GB

连接数

-- 查看最大连接数
SHOW VARIABLES LIKE 'max_connections';

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

-- 设置最大连接数
SET GLOBAL max_connections = 500;

查询缓存（MySQL 8.0 已移除）

-- MySQL 5.7
SHOW VARIABLES LIKE 'query_cache%';