【MYSQL】MYSQL 的学习教程(九)之 23 个 SQL 优化小技巧

发布时间:2023年12月26日

这篇文章从 15 个方面,分享了 sql 优化的一些小技巧,希望对你有所帮助

1. 避免使用 select *,务必指明字段名称

在实际业务场景中,可能我们真正需要使用的只有其中一两列。查了很多数据,但是不用,

  1. 白白浪费了数据库资源,比如:内存、cpu
  2. 多查出来的数据,通过网络 IO 传输的过程中,也会增加数据传输的时间
  3. 不会走覆盖索引,会出现大量的回表操作,而从导致查询 sql 的性能很低

【优化】:sql 语句查询时,只查需要用到的列,多余的列根本无需查出来

2. 用 union all 代替 union

  • sql 语句使用 union 关键字后,可以获取排序、去重后的数据
  • union all 关键字,可以获取所有数据,包含重复的数据

排序、去重的过程需要遍历、排序和比较,它更耗时,更消耗 cpu 资源

【优化】:如果能用 union all 的时候,尽量不用 union。除非是有些特殊的场景,比如业务场景中是不允许产生重复数据的

3. 小表驱动大表

小表驱动大表:用小表的数据集驱动大表的数据集

假如有 order 和 user 两张表,其中 order 表有 10000 条数据,而 user 表有 100 条数据
这时如果想查一下,所有有效的用户下过的订单列表

可以使用 in 关键字实现:

select * from order
where user_id in (select id from user where status=1)

也可以使用 exists 关键字实现:

select * from order
where exists (select 1 from user where order.user_id = user.id and status=1)

这种业务场景,使用 in关键字去实现业务需求,更加合适。

  • 因为如果 sql 语句中包含了 in 关键字,则它会优先执行 in 里面的子查询语句,然后再执行 in 外面的语句。如果 in 里面的数据量很少,作为条件查询速度更快
  • 而如果 sql 语句中包含了 exists 关键字,它优先执行 exists 左边的语句(即主查询语句)。然后把它作为条件,去跟右边的语句匹配。如果匹配上,则可以查询出数据。如果匹配不上,数据就被过滤掉了

【总结】:

  • in 适用于左边大表,右边小表
  • exists 适用于左边小表,右边大表

4. 批量操作

不建议一次批量操作太多的数据,如果数据太多数据库响应也会很慢。批量操作需要把握一个度,建议每批数据尽量控制在 500 以内。如果数据多于 500,则分多批次处理

5. 当只需要一条数据的时候,使用limit 1

有时候,我们需要查询某些数据中的第一条,使用 limit 1

6. IN 包含的值不应过多

sql 语句如下:

select id,name from category
where id in (1,2,3...100000000);

如果我们不做任何限制,该查询语句一次性可能会查询出非常多的数据,很容易导致接口超时。

①:使用 limit 做限制:

select id,name from category
where id in (1,2,3...100)
limit 500;

②:代码层面校验

public List<Category> getCategory(List<Long> ids) {
   if(CollectionUtils.isEmpty(ids)) {
      return null;
   }
   if(ids.size() > 500) {
      throw new BusinessException("一次最多允许查询500条记录")
   }
   return mapper.getCategoryList(ids);
}

③:分批查询
如果 ids 超过 500 条记录,可以分批用多线程去查询数据。每批只查 500 条记录,最后把查询到的数据汇总到一起返回(这只是一个临时方案,不适合于ids实在太多的场景。因为 ids 太多,即使能快速查出数据,但如果返回的数据量太大了,网络传输也是非常消耗性能的,接口性能始终好不到哪里去)

7. 增量查询

有时候,我们需要通过远程接口查询数据,然后同步到另外一个数据库

如果直接获取所有的数据,然后同步过去。这样虽说非常方便,但是带来了一个非常大的问题,就是如果数据很多的话,查询性能会非常差

【优化】:

select * from user 
where id> #{lastId} and create_time >= #{lastCreateTime} 
limit 100;

按 id 和时间升序,每次只同步一批数据,这一批数据只有 100 条记录。每次同步完成之后,保存这 100 条数据中最大的 id 和时间,给同步下一批数据的时候用

通过这种增量查询的方式,能够提升单次查询的效率

8. 高效的分页

在 mysql 中分页一般用的 limit 关键字:

select id,name,age 
from user limit 10,20;

如果表中数据量少,用limit关键字做分页,没啥问题。但如果表中数据量很多,用它就会出现性能问题

【优化】:

select id,name,age 
from user where id > 1000000 limit 20;

先找到上次分页最大的 id,然后利用 id 上的索引查询。不过该方案,要求id是连续的,并且有序的

9. 用连接查询代替子查询

mysql 中如果需要从两张以上的表中查询出数据的话,一般有两种实现方式:子查询 和 连接查询

子查询的例子如下:

select * from order
where user_id in (select id from user where status=1)

子查询语句可以通过 in 关键字实现,一个查询语句的条件落在另一个 select 语句的查询结果中。程序先运行在嵌套在最内层的语句,再运行外层的语句

子查询语句的优点是简单,结构化,如果涉及的表数量不多的话

但缺点是 mysql 执行子查询时,需要创建临时表,查询完毕后,需要再删除这些临时表,有一些额外的性能消耗

【优化】:
这时可以改成连接查询。具体例子如下:

select o.* from order o
inner join user u on o.user_id = u.id
where u.status=1

10. join 的表不宜过多

根据阿里巴巴开发者手册的规定,join 表的数量不应该超过 3 个

如果 join 太多,mysql 在选择索引的时候会非常复杂,很容易选错索引,并且如果没有命中中,nested loop join 就是分别从两个表读一行数据进行两两对比,复杂度是 n^2

所以我们应该尽量控制 join 表的数量

如果实现业务场景中需要查询出另外几张表中的数据,可以在 a、b、c 表中冗余专门的字段,比如:在表 a 中冗余 d_name 字段,保存需要查询出的数据

11. join 时要注意

join 使用最多的是 left joininner join

  • inner join:mysql 会自动选择两张表中的小表,去驱动大表,所以性能上不会有太大的问题
  • left join:mysql 会默认用 left join 关键字左边的表,去驱动它右边的表。如果左边的表数据很多时,就会出现性能问题

用 left join 关联查询时,左边要用小表,右边可以用大表。如果能用 inner join 的地方,尽量少用 left join

12. 控制索引的数量

阿里巴巴的开发者手册中规定,单表的索引数量应该尽量控制在 5 个以内,并且单个索引中的字段数不超过 5 个

mysql 使用的 B+ 树的结构来保存索引的,在 insert、update和delete 操作时,需要更新 B+ 树索引。如果索引过多,会消耗很多额外的性能

高并发系统如何优化索引数量?

能够建联合索引,就别建单个索引,可以删除无用的单个索引

将部分查询功能迁移到其他类型的数据库中,比如:ElasticSeach、HBase 等,在业务表中只需要建几个关键索引即可

13. 选择合理的字段类型

我们在选择字段类型时,应该遵循这样的原则:

  1. 能用数字类型,就不用字符串,因为字符的处理往往比数字要慢。
  2. 尽可能使用小的类型,比如:用 bit 存布尔值,用 tinyint 存枚举值等。
  3. 长度固定的字符串字段,用 char 类型。
  4. 长度可变的字符串字段,用 varchar 类型

14. 提升 group by 的效率

有很多业务场景需要使用 group by 关键字,它主要的功能是去重和分组。通常它会跟 having 一起配合使用,表示分组后再根据一定的条件过滤数据

select user_id,user_name from order
group by user_id
having user_id <= 200;

这种写法性能不好,它先把所有的订单根据用户 id 分组之后,再去过滤用户 id 大于等于 200 的用户

分组是一个相对耗时的操作,为什么我们不先缩小数据的范围之后,再分组呢?

【优化】:

select user_id,user_name from order
where user_id <= 200
group by user_id

使用 where 条件在分组前,就把多余的数据过滤掉了,这样分组时效率就会更高一些

WHERE 和 HAVING 区别

  1. WHERE 指定行对应得条件, HAVING 指定组对应的条件
  2. 先执行 WHERE 子句,后执行 HAVING 子句

SQL 的执行顺序:

在这里插入图片描述

15. 如果排序字段没有用到索引,就尽量少排序

16. 如果限制条件中其他字段没有索引,尽量少用 or

or 两边的字段中,如果有一个不是索引字段,而其他条件也不是索引字段,会造成该查询不走索引的情况。很多时候使用 union all 或者是 union (必要的时候)的方式来代替“or”会得到更好的效果

17. 避免在 where 子句中对字段进行 null 值判断

对于null的判断会导致引擎放弃使用索引而进行全表扫描。

18. 避免在 where 子句中对字段进行表达式操作

对字段就行了算术运算,这会造成引擎放弃使用索引

19. 不建议使用%前缀模糊查询

例如LIKE “%name”或者LIKE “%name%”,这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE “name%”。

那如何查询%name%?

答案:使用全文索引

20. 避免隐式类型转换

21. 对于联合索引来说,要遵守最左前缀法则

在创建联合索引的时候一定要注意索引字段顺序,常用的查询字段放在最前面

22. 索引优化

很多时候 sql 语句,走了索引,和没有走索引,执行效率差别很大。所以索引优化被作为 sql 优化的首选

  1. 查看是否建立索引
  2. 查看是否走了索引:EXPLAIN 查看执行计划:【MYSQL】MYSQL 的学习教程(七)之 慢 SQL 优化思路
  3. 如果建立索引但没有走索引,那么可能索引失效:【MYSQL】MYSQL 的学习教程(四)之索引失效场景

23. 必要时可以使用 force index 来强制查询走某个索引

文章来源:https://blog.csdn.net/sco5282/article/details/135216075
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。