图灵学院：SQL 优化

叁歲伎倆 2023-10-12 19:38 50阅读 0赞

#### 文章目录 ####

*  一、避免使用 select \*
 *  二、使用小表驱动大表
 *  三、用连接查询代替子查询
 *  四、提升 group by 的效率
 *  五、批量操作
 *  六、使用 limit
 *  七、union all 代替 union

--------------------

## 一、避免使用 select \* ##

阿里规范：在表查询中，一律不要使用 \* 作为查询的字段列表，需要哪些字段必须明确写明。说明：

> ① 增加查询分析器解析成本。  
> ② 增减字段容易与 resultMap 配置不一致。  
> ③ 无用字段增加网络消耗，尤其是 text 类型的字段。

查看执行计划，select \* 走全表扫描，没有用到任何索引，因此查询效率非常低。  
如果查询列都是索引列，那么这些列被称为覆盖索引。这种情况下查询的相关字段都能走索引，且不需要回表，索引查询的效率相对较高。

--------------------

## 二、使用小表驱动大表 ##

小表驱动大表就是指用数据量较小、索引比较完备的表，然后使用其索引和条件对大表进行数据筛选，从而减少数据计算量，提高查询效率。

比如：student 表有 30 条数据，scores 表有 80w 条数据。

select * from student left join scores on student.id = scores.student_id;

Join Buffer（连接缓冲区）是优化器用于处理连接查询操作时的临时缓冲区。简单来说当我们需要比较两个或多个表的数据进行 Join 操作时，Join Buffer 可以帮助 MySQL 临时存储结果，以减少磁盘读取和 CPU 负担，提高查询效率。需要注意的是每个 join 都有一个单独的缓冲区。  
  Block nested-loop join（BNL算法）会将驱动表数据加载到 join buffer 里面，然后再批量与非驱动表进行匹配；如果驱动表数据量较大，join buffer 无法一次性装载驱动表的结果集，将会分阶段与被驱动表进行批量数据匹配，会增加被驱动表的扫描次数，从而降低查询效率。**所以开发中要遵守小表驱动大表的原则。**

分阶段匹配过程如下：

> ① 先把 student 表前 15 条数据读到 join buffer 中。  
> ② 然后用 scores 表去匹配 join buffer 中的前15条。  
> ③ 记录下匹配结果。  
> ④ 清空 join buffer。  
> ⑤ 再把 student 表后 15 条读取 join buffer 中。  
> ⑥ 然后用 scores 表去匹配 join buffer 中的后15条。  
> ⑦ 记录下匹配结果。

--------------------

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

mysql 需要在两张表以上获取数据的方式有两种：第一种通过连表查询获取，第二种通过子查询获取。  
模拟一个真实场景，同样 student 表有30条数据，scores 表有 80w 条数据，我们想查看学员各科分数信息：

ALTER TABLE scores ADD index idx_student_id (student_id); -- 建立索引
    
    EXPLAIN
    SELECT
    	(SELECT student.NAME FROM student WHERE student.id = scores.student_id),
    	scores.course_name,
    	scores.score
    FROM
    	scores;

因为子查询需要执行两次数据库查询，一次是外部查询，一次是嵌套子查询。子查询的学生表只使用到主键索引，但对于学生表而言，数据条数较少，主键索引起不到很大的优化作用。

连接查询可以更好地利用数据库索引，提高查询的性能。子查询通常会使用临时表或内存表，而连接查询可以直接利用表上的索引。这意味着连接查询可以更快地访问表中的数据，减少查询的资源消耗。

对于大型数据集，使用连接查询通常比使用子查询更高效。子查询通常需要扫描整个表，而连接查询可以利用索引加速读取操作。

EXPLAIN
    SELECT
    	student.NAME,
    	scores.course_name,
    	scores.score
    FROM
    	student inner JOIN scores ON student.id = scores.student_id;

使用连接查询可以更快地执行查询操作，减少数据库的负载，提高查询的性能和效率。

--------------------

## 四、提升 group by 的效率 ##

创如果使用 group by 的列没有索引，那么查询可能会变得很慢。因此，可以为 group by 的字段创建一个或多个适当的索引来加速查询。

alter table scores add index idx_remarks (remarks); -- 建立索引
    
    select remarks from scores group by remarks;

*  调整查询：查询的写法也会影响 group by 的效率。可以尝试不使用子查询或临时表，或者可以使用 join 或 exits 来代替 in 子查询。
 *  限制结果集的数量：如果只需要查看一小部分结果，可以在查询中添加 limit 子句，以便只返回一定数量的结果。

--------------------

## 五、批量操作 ##

批量插入或批量删除数据，比如说现在需要将1w+数据插入到数据库时，建议使用批量操作，逐个处理会频繁的与数据库交互，损耗性能。

使用 mybatis 的 foreach 标签拼接 动态 sql。但需要注意的是，不建议一次批量操作太多的数据，如果数据太多数据库响应也会很慢。批量操作需要把握一个度，建议每批数据尽量控制在500以内。如果数据多于500，则分多批次处理。

--------------------

## 六、使用 limit ##

使用 Limit 的好处：

*  提高查询效率：一个查询返回成千上万的数据行，不仅占用了大量的系统资源，也会占用更多的网络带宽，影响查询效率。使用 Limit 可以限制返回的数据行数，减轻了系统负担，提高了查询效率。
 *  避免过度提取数据：对于大型数据库系统，从数据库中提取大量的数据可能会导致系统崩溃。使用 Limit 可以限制提取的数据量，避免过度提取数据，保护系统不受影响。
 *  优化分页查询：分页查询需要查询所有的数据才能进行分页处理，这会浪费大量的系统资源和时间。使用 Limit 优化分页查询可以只查询需要的数据行，缩短查询时间，减少资源的浪费。
 *  简化查询结果：有时我们只需要一小部分数据来得出决策，而不是整个数据集。使用 Limit 可以使结果集更加精简和易于阅读和理解。

限制行数非常有用，因为它可以提高查询性能、减少处理需要的时间，并且只返回我们关心的列。

**问题：百万级表 Limit 翻页越往后越慢怎么办？为什么 offset 偏大之后 limit 查找会变慢？**

这需要了解 limit 操作是如何运作的，以下面这句查询为例：

select * from table_name limit 10000,10

它首先做得是先查询前10000条，然后再查目标的 10 条数据。

**第一次优化**

根据数据库这种查找的特性，就有了一种想当然的方法，利用主键自增索引（假设为id）：

select * from table_name where (id > 10000) limit 10

通过 id 的索引直接定位到第 10000 条数据，再去向后面查 10 条数据。

**第二次优化**

提起数据库查询优化，第一时间想到的就是索引，所以便有了第二次优化：先查找出需要数据的索引列（假设为 id），再通过索引列查找出需要的数据。

select * from table_name where id in (select id from table_name) limit 10000, 10;