图灵学院：SQL 优化

2023-06-24 01:52| 来源: 网络整理| 查看: 265

文章目录一、避免使用 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;

注：子查询是覆盖索引，外面的查询 id in … 用到了主键索引。

相比较结果是（500w条数据）：第一条花费平均耗时约为第二条的 1/3 左右。

同样是较大的 offset，第一条的查询更为复杂，为什么性能反而得到了提升？

这涉及到 mysql 主索引的数据结构 b+Tree ，基本原理就是：

子查询只用到了索引列，没有取实际的数据，所以不涉及到磁盘IO，所以即使是比较大的 offset 查询速度也不会太差。利用子查询的方式，把原来的基于 user 的搜索转化为基于主键（id）的搜索，主查询因为已经获得了准确的索引值，所以查询过程也相对较快。

第三次优化

在数据量大的时候 in 操作的效率很低，因此可以使用 join 替换 in。

select * from table_name inner join ( select id from table_name ) b using (id) limit 10000, 10; 七、union all 代替 union

union all：获取所有数据但是数据不去重，包含重复数据； union：获取所有数据且数据去重，不包含重复数据；

select id,name,department from student union all select id,student_id,score from scores

那么 union all 与 union 如果当然它业务数据容许出现重复的记录，更推荐使用 union all，因为 union 去重数据需要遍历、排序和比较，它更耗时，更消耗 cpu 资源，但是数据结果最完整。

【本文地址】

公司简介

联系我们