小菜鸟

java菜鸟号正在起航

0%

优化建议

发表于 更新于 分类于 阅读次数： Valine：
本文字数： 4.5k 阅读时长 ≈ 4 分钟

MySQL 优化建议：从查询到存储引擎的全方位优化

数据库性能优化的核心是减少磁盘 IO 和 CPU 消耗，同时高效利用内存缓存。本文从查询优化、索引使用、存储引擎配置等维度，结合 MySQL 底层原理，提供可落地的优化建议。

JOIN 语句优化：减少循环与 IO 开销

JOIN 是多表关联查询的常用操作，但其性能易受表大小、索引、缓冲等因素影响，优化核心是减少嵌套循环次数和被驱动表的扫描代价。

1. 小表驱动大表（小结果集驱动大结果集）

JOIN 本质是 “嵌套循环”：外层表（驱动表）的每条记录都会触发内层表（被驱动表）的查询。驱动表越小，总循环次数越少。

反例：
1
2
3
4
-- 驱动表（admin）未过滤，数据量大
SELECT * FROM admin 
LEFT JOIN log ON log.admin_id = admin.id 
WHERE log.admin_id > 10;
优化：先过滤驱动表，缩小结果集
1
2
3
4
5
-- 子查询过滤驱动表，只保留必要数据
SELECT * FROM (
  SELECT * FROM admin WHERE id > 10  -- 驱动表结果集变小
) t1 
LEFT JOIN log ON log.admin_id = t1.id;

2. 优先优化内层循环

内层循环（被驱动表）的执行次数 = 驱动表的行数，因此被驱动表的查询效率对整体性能影响更大。

关键优化：确保被驱动表的 JOIN 条件有索引
1
2
-- 为被驱动表的关联字段创建索引
ALTER TABLE log ADD INDEX idx_admin_id (admin_id);

索引可将被驱动表的全表扫描（O (n)）转为索引查找（O (log n)），大幅减少耗时。

3. 合理设置 join_buffer_size

join_buffer_size 是 MySQL 为 JOIN 操作分配的内存缓冲区，用于存储驱动表的查询结果。若缓冲区不足，会使用磁盘临时文件，导致 IO 激增。

优化建议：
  • 对于频繁的小表 JOIN，可适当调大（如 4M-8M）：SET GLOBAL join_buffer_size = 8388608;（8M）。
  • 避免过大：多连接场景下，总内存占用 = 连接数 × join_buffer_size，可能导致 OOM。

4. 用 JOIN 替代低效子查询（MySQL 5.6 前）

MySQL 5.6 前，子查询常采用 “相关子查询”（外层每一行触发一次子查询），效率低下。JOIN 可避免重复执行，且优化器处理更高效。

反例（子查询）：
1
2
3
4
SELECT * FROM admin 
WHERE EXISTS (
  SELECT 1 FROM log WHERE log.admin_id = admin.id AND log.create_time > '2024-01-01'
);
优化（JOIN）：
1
2
3
SELECT DISTINCT a.* FROM admin a 
JOIN log l ON a.id = l.admin_id 
WHERE l.create_time > '2024-01-01';

注意：MySQL 5.6+ 引入 “子查询物化”（将子查询结果存入临时表，仅执行一次），子查询与 JOIN 性能差异缩小，可根据实际执行计划选择。

减少排序：降低 CPU 消耗

排序操作（ORDER BY）依赖 CPU 计算，尤其数据量大时，可能触发文件排序（Using filesort），增加磁盘 IO。优化核心是利用索引排序或减少排序数据量。

1. 利用索引排序（避免 filesort）

索引本身是有序的，若 ORDER BY 字段与索引顺序一致，可直接利用索引返回有序数据，无需额外排序。

示例：
1
2
3
4
5
6
7
-- 创建复合索引，匹配查询和排序条件
ALTER TABLE user ADD INDEX idx_age_register (age, register_time);

-- 查询可利用索引排序（无 Using filesort）
SELECT id, age, register_time FROM user 
WHERE age > 30 
ORDER BY register_time;

2. 减少参与排序的记录数

通过 WHERE 条件提前过滤数据，缩小排序范围：

反例：
1
2
-- 先排序全表，再取前 10 条（排序数据量大）
SELECT * FROM user ORDER BY register_time LIMIT 10;
优化：
1
2
3
4
-- 先过滤，再排序（仅排序近 1 个月数据）
SELECT * FROM user 
WHERE create_time > '2024-07-01' 
ORDER BY register_time LIMIT 10;

3. 非必要不排序

若业务允许无序返回（如数据展示不要求严格顺序），可去除 ORDER BY，避免排序开销。

减少 OR 的使用：避免索引失效

OR 条件可能导致 MySQL 放弃索引，改用全表扫描（尤其不同字段的 OR）。建议用 UNION ALL 替代，利用索引提升效率。

反例（OR）：

1
2
-- 可能导致全表扫描（即使 id 和 name 都有索引）
SELECT * FROM user WHERE id = 1 OR name = '张三';
优化（UNION ALL）：
1
2
3
4
-- 分两次查询，各自利用索引，再合并结果
SELECT * FROM user WHERE id = 1
UNION ALL
SELECT * FROM user WHERE name = '张三';

注意：UNION ALL 不去重，若需去重改用 UNION（但会增加排序去重开销，尽量避免）。

防止索引失效：确保索引被有效利用

索引失效是性能杀手，需避免以下常见场景：

1. 索引列上的计算或函数操作

1
2
3
4
5
-- 反例：索引列 age 被计算，索引失效
SELECT * FROM user WHERE age + 1 = 30;

-- 优化：移去计算，条件改写
SELECT * FROM user WHERE age = 29;

2. 使用 NOT、<>、!= 等否定操作符

1
2
3
4
5
-- 反例：!= 可能导致索引失效
SELECT * FROM user WHERE age != 30;

-- 优化：用范围查询替代（若业务允许）
SELECT * FROM user WHERE age < 30 OR age > 30;

3. IS NULL 或 IS NOT NULL

1
2
3
4
5
-- 反例：IS NOT NULL 可能导致索引失效
SELECT * FROM user WHERE name IS NOT NULL;

-- 优化：业务层面避免 NULL，用默认值（如空字符串）替代
ALTER TABLE user MODIFY name VARCHAR(50) DEFAULT '' NOT NULL;

4. 隐式类型转换

1
2
3
4
5
-- 反例：字符串字段与数字比较，触发类型转换，索引失效
SELECT * FROM user WHERE name = 123;  -- name 是 VARCHAR 类型

-- 优化：保持类型一致
SELECT * FROM user WHERE name = '123';

5. 索引列包含 NULL 值

InnoDB 索引不存储 NULL 值，若字段含大量 NULL，索引过滤效果差，建议用默认值替代 NULL。

GROUP BY 优化：减少分组数据量

GROUP BY 用于分组统计，优化核心是在分组前过滤数据，减少参与分组的行数。

1. 用 WHERE 替代 HAVING 过滤条件

WHERE 在分组前过滤，HAVING 在分组后过滤。优先用 WHERE 减少分组数据量：

反例：
1
2
3
4
-- HAVING 在分组后过滤，分组数据量大
SELECT age, COUNT(*) FROM user 
GROUP BY age 
HAVING age > 30;
优化：
1
2
3
4
-- WHERE 在分组前过滤，减少分组行数
SELECT age, COUNT(*) FROM user 
WHERE age > 30 
GROUP BY age;

UNION 优化：避免不必要的去重

UNION 会对结果集去重（通过临时表 + DISTINCT），开销较大；若无需去重，用 UNION ALL 更高效。

反例：
1
2
3
4
-- UNION 去重，额外开销
SELECT id FROM user WHERE age < 20
UNION
SELECT id FROM user WHERE age > 40;
优化：
1
2
3
4
-- 无需去重，用 UNION ALL
SELECT id FROM user WHERE age < 20
UNION ALL
SELECT id FROM user WHERE age > 40;

存储引擎配置优化

不同存储引擎（MyISAM、InnoDB）的优化重点不同，需针对性调整。

1. MyISAM 存储引擎（适用于读多写少场景）

（1）减少表碎片

删除或更新数据后，MyISAM 表可能产生碎片（未释放的空闲空间），导致扫描效率下降。

  • 查看碎片：SHOW TABLE STATUS FROM db_name LIKE 'table_name';（Data_free 字段为碎片大小）。

  • 整理碎片：

    1
    2
    3
    4
    5
    6
    -- 分析表（更新索引统计信息）
    ANALYZE TABLE table_name;

    -- 优化表（重组数据和索引，释放碎片）
    OPTIMIZE TABLE table_name;  -- 5.7+ 可能提示用 ALTER TABLE 替代
    ALTER TABLE table_name ENGINE = MyISAM;  -- 等效于 OPTIMIZE
（2）优化 Key Cache

MyISAM 依赖 Key Cache 缓存索引，提升读效率。

  • 查看配置：SHOW VARIABLES LIKE 'key_buffer_size';（默认 8M）。

  • 优化建议：设为可用内存的 20%-30%（如 4GB 内存设为 1GB）：

    1
    SET GLOBAL key_buffer_size = 1073741824;  -- 1GB
（3）使用多个 Key Cache

避免单缓存争用，为高频访问表创建独立缓存：

1
2
3
4
5
-- 创建自定义缓存（128MB）
SET GLOBAL custom_cache.key_buffer_size = 134217728;

-- 将表索引加载到自定义缓存
CACHE INDEX table_name IN custom_cache;

2. InnoDB 存储引擎（适用于事务和写操作频繁场景）

（1）优化缓冲池（InnoDB Buffer Pool）

InnoDB 依赖 Buffer Pool 缓存数据页和索引页，是最重要的优化项。

  • 配置建议：设为物理内存的 60%-70%（仅 InnoDB 引擎服务器）：

    1
    2
    [mysqld]
    innodb_buffer_pool_size = 12G  # 16GB 内存服务器示例

  • 验证效果：SHOW STATUS LIKE 'Innodb_buffer_pool_read_ratio';（命中率 > 95% 为佳）。

（2）日志相关优化

  • innodb_log_buffer_size：事务日志缓冲区，建议 16M-64M（减少日志刷盘次数）：

    1
    innodb_log_buffer_size = 64M

  • innodb_flush_log_at_trx_commit：

    • 1（默认）：事务提交时立即刷盘（最安全，性能略低）。
    • 2：事务提交时写入 OS 缓存，每秒刷盘一次（兼顾安全与性能）。
（3）减少锁冲突
  • 用 InnoDB 行锁（而非 MyISAM 表锁），降低并发阻塞。
  • 避免长事务：及时提交，减少锁持有时间
相关文章

欢迎关注我的其它发布渠道