【Mysql优化】SQL优化最佳实践分析与总结(速食版)_优化sql查询的gorm最佳实践-优快云博客

文章目录

- SQL优化最佳实践分析与总结

SQL优化最佳实践分析与总结

SQL查询性能的优化是数据库设计与使用中的核心问题之一，通过以下六大实践，我们可以显著提升查询效率，降低资源消耗：

一、避免使用 `SELECT *`

原因：

查询解析成本增加：SELECT * 会将字段解析为所有列，这一过程增加了解析器的负担。
覆盖索引失效：SELECT * 通常无法利用覆盖索引，会导致大量回表查询，效率低下。
网络传输负担：无用字段的传输，特别是大字段 (如 TEXT) 会浪费带宽。
浪费CPU、内存资源：不需要使用的字段也查出来浪费CPU、内存资源

优化建议：

明确列出所需字段，确保查询列与索引列匹配。
使用工具（如 SHOW WARNINGS）检查字段映射与优化建议。

在这里插入图片描述

二、小表驱动大表

原理：
在 JOIN 操作中，优化器优先使用驱动表的索引匹配被驱动表的数据。小表作为驱动表能减少扫描次数，降低磁盘读取量，提高效率。

简单的说，驱动表的每一条数据都要对应着去扫另一个表，数据越多，扫的次数越多。

名词解析：

1. Join Buffer（连接缓冲区）

作用：在进行多个表的连接操作时，Join Buffer 用作 MySQL 临时存储数据的缓冲区。它主要用于优化连接查询，减少磁盘访问和 CPU 负担，从而提高查询效率。
每个 Join 都有独立的 Join Buffer：每当有连接操作发生时，MySQL 会为每个连接创建一个独立的缓冲区来存储数据，直到查询完成。

在这里插入图片描述

2. Block Nested-Loop Join（BNL算法）

算法解释：BNL 是一种常见的连接算法，它会将驱动表的数据一次性加载到 Join Buffer 中，然后通过批量方式与被驱动表进行匹配。
基本流程是：
1. 从驱动表（例如，student 表）读取数据并加载到 Join Buffer 中。
2. 使用 Join Buffer 中的数据批量扫描并与非驱动表（例如，scores 表）进行匹配。
3. 将匹配结果记录下来，然后清空缓冲区，继续加载驱动表中剩余的数据，重复这个过程。

优化示例：

不推荐：SELECT * FROM scores LEFT JOIN student ON ... （大表驱动小表）
推荐：SELECT * FROM student LEFT JOIN scores ON ... （小表驱动大表）

三、用连接查询代替子查询

子查询问题：

子查询需要多次数据库查询，常依赖临时表或内存表，效率低。
子查询通常无法充分利用索引。

-- 子查询方式 	这里要找到每个id，然后再去找对应的数据
SELECT name, department 
FROM student 
WHERE id IN (SELECT student_id FROM scores WHERE grade > 90);

优化方法：

优先使用 JOIN 查询。
JOIN 查询直接利用索引，加速读取并降低资源消耗。

-- JOIN 查询方式（更高效）
SELECT s.name, s.department 
FROM student s 
JOIN scores sc ON s.id = sc.student_id 
WHERE sc.grade > 90;

优点：

减少查询次数： 子查询会导致嵌套查询，执行时需要先查询子表，再返回结果到外部查询。连接查询直接在一次查询中完成数据检索。
充分利用索引： 连接查询可以直接使用表上的索引，而子查询往往会使用临时表或内存表，导致性能下降。
减少资源消耗： 对于大型数据集，连接查询避免了重复扫描表的情况，从而降低了 I/O 和内存开销。

使用JOIN还能更直观的看出2表的关系，也利于后续的拓展。

四. 提升 `GROUP BY` 的效率

问题：

无索引时，GROUP BY 会对全表进行排序与分组，耗时较长。

优化方法：

创建索引：在 GROUP BY 的列上添加适当索引。
优化查询：避免子查询，尝试使用 JOIN 或 EXISTS。
限制结果集：通过 LIMIT 缩小返回范围。

核心其实就是走索引、减少Group By的数据量（条件筛选）

ps: order by 也可以参考这个

五. 批量操作

问题：

单条插入导致多次数据库交互，性能低下。

优化建议：

使用批量操作，减少数据库交互次数。
每批次数据量建议控制在 500 条以内，避免单次操作过大导致数据库响应缓慢。

本文主要讲Sql优化，批量插入这里可以看这篇文章：[MybatisPlus实现真正批量插入](MybatisPlus实现真正批量插入_mybatisplus 批量插入-优快云博客)

六. 使用 `LIMIT` 优化查询

优势：

提高查询效率：当查询返回成千上万的数据行时，会占用大量系统资源和网络带宽，影响查询效率。使用 LIMIT 限制返回的数据行数，可以减轻系统负担，显著提高查询效率。
避免过度提取数据：在大型数据库中提取过多数据可能导致系统崩溃或性能下降。LIMIT 限制数据量，避免一次性提取过多数据，保护系统不受影响。
优化分页查询：分页查询通常需要查询所有数据来进行分页处理，浪费大量系统资源和时间。使用 LIMIT 可以直接查询所需的数据，减少不必要的计算，提高查询效率。
简化查询结果：有时只需要部分数据来做决策，而不是整个数据集。通过使用 LIMIT，可以返回精简的结果集，避免信息冗余，简化决策过程。

LIMIT 翻页性能问题：偏大 OFFSET 导致查询变慢

在处理百万级表时，LIMIT 翻页查询会随着 OFFSET 的增大变慢，原因在于 SQL 查询的执行逻辑：

假设查询如下：

select * from table_name limit 10000, 10

执行逻辑：

从数据表中读取第 N 条数据并添加到结果集中。
重复执行，直到读取到 N = 10000 + 10 的位置。
跳过前 10000 条数据。
返回剩余的 10 条数据。

随着 OFFSET 增大，数据库需要跳过更多的数据记录，这会导致查询变慢，尤其是在数据量大的情况下。为了解决这个问题，有几种优化方法。

第一次优化：利用自增索引

问题：当使用 OFFSET 时，数据库需要跳过大量的记录，导致查询变慢。
优化思路：通过查询主键（如自增 id）的范围，避免跳过大量数据，直接定位到目

select * from table_name where id >= 10000 limit 10

优点：使用自增主键作为查询条件，利用主键的索引结构（如 B+ 树）加速查询。
适用场景：当表中的数据是有序的，自增主键可以有效缩短查询时间。

第二次优化：使用子查询与索引列

问题：大 OFFSET 查询时，LIMIT 往往会导致数据库全表扫描，影响性能。
优化思路：通过先使用子查询查询符合条件的 id，然后使用这些 id 去查询实际数据，避免了大 OFFSET 的性能瓶颈。例如：

select * from table_name 
where id in (select id from table_name where user = 'xxx' limit 10000, 10)

优点：
- 子查询只会检索索引列（如 id），不涉及磁盘 I/O，减少了数据读取量。
- 主查询通过准确的 id 索引值查询，避免了全表扫描。
适用场景：当需要根据某些条件筛选数据，并且数据量较大时，子查询优化能够显著提高查询性能。

第三次优化：替换 IN 操作为 JOIN

问题：在数据量较大的情况下，IN 操作可能导致性能瓶颈，因为它需要扫描整个子查询结果集。
优化思路：将 IN 替换为 JOIN，通过连接操作来提高查询效率，避免 IN 操作中的性能问题。例如：

select * from table_name
inner join (select id from table_name where user = 'xxx' limit 10000, 10) b
using (id)

优点：JOIN 操作比 IN 更高效，尤其是当查询的 id 列已经有索引时。
适用场景：数据量较大时，JOIN 可以加速查询，避免了 IN 操作的性能瓶颈。

注意：

对百万级表，LIMIT 翻页会导致性能下降，推荐使用基于索引的优化方式：

-- 基于索引的分页
SELECT * FROM scores WHERE id > (SELECT id FROM scores ORDER BY id LIMIT 1 OFFSET 1000) LIMIT 10;

七. 用union all代替union

这里就是区分应用场景使用了

UNION ALL:

不去重：直接返回所有数据，包括重复的记录。
性能较好：因为它不进行去重操作，不需要对结果集进行排序和去比较，所以执行效率较高，尤其是在数据量较大的情况下。
适用场景：当业务允许存在重复记录时，使用 UNION ALL 可以提高查询效率，尤其是对于需要合并大量数据的场景。

UNION:

去重：合并结果后会自动去掉重复的记录，只保留唯一的数据。
性能较差：由于需要进行去重操作，通常会有更多的开销，系统需要对所有的记录进行排序和比较，这会消耗更多的时间和CPU资源。
适用场景：当业务要求去重或确保结果中没有重复记录时，使用 UNION。

八. join的表不宜过多

缺点:

查询效率下降：
- 多表 JOIN 会增加查询的计算量，随着连接的表增多，查询时间呈指数级增长，导致效率显著下降。
系统负载增加：
- JOIN 操作涉及大量数据的计算和存取。如果没有合适的索引，系统资源（如 CPU、内存）会大量消耗，导致系统负载增加。
维护难度加大：
- 多表 JOIN 查询通常较复杂，修改一个表的结构或数据时，可能需要同时调整其他表，增加了维护的难度。

优化建议:

减少 JOIN 表的数量：
- 尽量减少连接的表数，避免使用过多表进行 JOIN 操作。可以考虑拆分查询，或者通过合并表结构来简化查询。
保持 JOIN 字段的数据类型一致：
- 确保参与 JOIN 的字段在不同表中的数据类型一致，避免因数据类型不匹配导致的性能下降或转换错误。
为 JOIN 字段创建索引：
- 确保参与 JOIN 操作的字段上有索引，避免全表扫描，提高查询性能。
合理控制索引数量：
- 尽量避免为每个字段都创建索引。重点为常用的 JOIN 字段、查询条件字段等创建索引，避免过多的索引影响写入性能。

总结

优化 SQL 查询的核心思想是减少不必要的资源消耗，提高数据库的利用效率。通过避免全表扫描、使用合适的索引、小表驱动大表、批量操作和分页优化，可以显著提升系统性能。

博客主页: 总是学不会.

【Mysql优化】SQL优化最佳实践分析与总结(速食版)

文章目录

SQL优化最佳实践分析与总结

一、 避免使用 SELECT *

二、 小表驱动大表

三、 用连接查询代替子查询

四. 提升 GROUP BY 的效率