SQL优化

举报
object 发表于 2025/01/14 09:28:58 2025/01/14
【摘要】 简介最近不经常写sql了,但是作为CRUD的核心,作为一个开发的基础技能,SQL的优化还是很重的,在写sql的时候,能注意到这些细节,可能会极大的优化性能一、避免使用select *select * 说实话确实很多时候确实很要用,特别是针对项目初期,可能经常会涉及字段的变更。反例:select * from employ原因:过多的字段查询,IO传输会浪费大量资源select * 不会走覆盖...

简介

最近不经常写sql了,但是作为CRUD的核心,作为一个开发的基础技能,SQL的优化还是很重的,在写sql的时候,能注意到这些细节,可能会极大的优化性能

一、避免使用select *

select * 说实话确实很多时候确实很要用,特别是针对项目初期,可能经常会涉及字段的变更。

反例:

select * from employ

原因:

  • 过多的字段查询,IO传输会浪费大量资源
  • select * 不会走覆盖索引,会出现大量的回表操作,从而导致查询sql的性能很低

二、用union all代替union

union主要用于同类型表的竖向拼接场景,除非需要去重的场景,优先使用union

反例:

select name from user 
union
select name from employee

原因:

  • union拼接时,会进行排重,会涉及遍历、排序、比较等操作,会消耗资源

三、小表驱动大表

这个也会很重要的概念,这个跟sql的执行逻辑有关。sql执行过程中,一般是固定驱动表,遍历被驱动表。所以被驱动表越小,遍历的次数就越小。

反例:

# user表10个用  order表10000个订单
select order_id from user where user_id in (select user_id from order)

原因:

  • 前提是大表需要使用索引,否则时间是一致的。公式是:小表一趟for循环的代价 + 大表上使用B+树索引的代价 < 大表一趟for循环的代价 + 小表使用B+树索引的代价
  • 查询效率。 B+数索引的代价为 2Log2M,M为大表的数据行数。  加上小表的行数乘积N,总查询时间为: N*2Log2M。  可以看出,N的影响较大。

注意:

  1. in 语句,右边的表为驱动表
  2. exist语句,左边的表为驱动表
  3. 关联查询中, inner join 会自动优化,使用小表驱动。  left join左表为驱动表  right join右表为驱动表

四、批量插入操作

这个就比较简单了,就是批量操作不要使用for循环语句进行嵌套插入。

反例:

for (User user: List<User> users) {
  userMapper.insert(user);
}

原因:

  • 循环执行sql,每次sql的连接执行都会产生多余的资源消耗,影响性能
  • 使用 insert () values(),(),()代替for循环

五、使用limit提高性能

如果并不需要查询所有的数据,仅需要其中的一条数据即可,则可以使用limit进行阶段,优化性能

反例:

select * from user order by create_time asc

原因:

  • 过多的数据传输,可能导致IO传输过程的性能消耗
  • 执行sql,获取到数据后,即停止遍历查询,提高性能

六、in中值太多

这个逻辑与小表驱动大表类似理由差不多,但是这里更多的体现在sql拼接常量的场景

反例:

select * from user where id in (1,2,3,4,5,6,7,....,10000)

原因:

  • in右边为驱动表,过大容易导致查询时间过长
  • 查询的数据过多,可能会导致IO传输数据量过多,性能消耗较大

7.增量查询

对于同步数据场景,不建议一次性批量传输所有数据,可能会导致奔溃

反例:

# 从一个数据库的user表获取数据
select * from database1.user

#同步到另一个数据库
insert into database2.user(id,name) values()()()...

原因:

  • 一次性查询的数据过多,可能导致内存或者线程崩溃

建议:

  1. 可使用分批量查询,可以按照id,创建时间等等

八、高效的分页

分页很多时候能帮助我们解决数据量过多的问题,大多数场景下,我们会直接使用limit 起始值,取值范围 进行分页,不过当起始值过大时,会导致执行效率很低

反例:

select * from user limit 100000,10

原因:

  • limit a,b的逻辑为 查询前a+b行数据,然后舍弃前a行,所以需要查询出前a行的数据, a过大时,就会导致性能大量消耗

建议:

  1. 可以进行分流查询,针对不同时间段的数据,进行查询,减少数据量
  2. 可以使用where代替,不过仅限一些简单的场景, 例如where id > 100000,前提id连续,且需要的是id为100001后的10条数据

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

当where语句后接select语句会造成子查询,子查询会增加额外的性能消耗,主要体现在临时表的创建和删除。优先使用连接查询进行代替

反例:

select * from user where id in (select id from order)

原因:

  • 子查询执行时会创建临时表,使用完后会销毁这些临时表,会造成额外的性能消耗

十、join的表不宜过多

根据阿里巴巴开发者手册规定,join表的数量不应该超过3个。当然实际的业务场景可能比较复杂,我们只能尽量设计,当然一张表过多的字段也不是一件好事,这是一个综合性的考量

反例:

select a.name,b.name,c.name,d.name,f.name,g.name
from a
inner join b on a.id = b.id
inner join c on c.id = b.id
inner join d on d.id = c.id
inner join f on f.id = d.id
inner join g on g.id = f.id

原因:

  • 连表过多,笛卡尔乘积会很大,遍历次数会较多
  • 连表过多,索引的简历会较复杂,容易导致索引失效

十一、join的使用

主要核心还是小表驱动大表,left join 、 inner join 、 right join几种使用方式不同

反例:

# user表10条数据 order 表10000条数据
select * from order r
left join user u on u.user_id = r.user_id

select * from user u
right join order r on u.user_id = r.user_id

原因:

  • left join、right join需要区分驱动表,left join左表为驱动表,right join右表为驱动表。根据上面第三条的小表驱动大表规则,需要使用不同的连接方式
  • inner join 会自动匹配小的为驱动表,不需要做区分

十二、控制索引的数量

控制索引的数量主要在于每次新增时,索引都需要进行重排序,虽然索引多能极大的提高查询效率,不过索引过多,会导致索引的存储空间占用,以及新增/编辑时会导致索引重建消耗过大。阿里巴巴建议的索引为不超过五个

原因:

  • 索引的insert,update,delete操作时,会对索引进行重排序,会消耗性能,索引越多,消耗的性能越多。 如果索引本身是无序,消耗会更多。
  • 索引太多,也会消耗很大存储空间

建议:

  1. 能使用联合索引就优先使用联合索引,删除单个索引

十三、选择合理的字段的类型

不同字段类型,空间,索引等都不同,选择合理的类型会减少存储空间和索引的比较

反例:

#使用char存储 类型,枚举类型
alter table order 
add column type varchar(255) NOT NULL;

原因:

  • 过长的字段类型会浪费存储空间,过多的字段类型会导致新增失败
  • 较小的字段内搜索,查询效率会更高

建议:

  1. 能用数字,就不用字符串,字符处理比数字要慢
  2. 尽可能使用小的类型,如tinyint存储枚举数字

十四、提升group by having的效率

group by分组查询,是在已查询的基础上进行分组匹配,分组时使用having进行再处理。

反例:

select user_id from order goup by user_id having price > 200

原因:

  • group by having的逻辑是针对已筛选的数据进行处理,且分组本身比较耗时,减少已筛选的数据可以增加分组效率

建议:

  1. 针对所有容易耗时的操作前,都应该减少数据范围。

十五、索引优化

SQL优化中,索引是非常重要的内容模块,合理的索引能极大的增加查询效率,本质是通过指定列建立索引树,一种特殊的数据结构,主要目的用于排序,便于快速定位,再通过主键定位原始数据行。

关键字:explain sql。用于分析是否走了sql

索引是一块比较大的内容,包含索引如何建立,索引的数据结构,索引的查询,索引的失效,聚集索引等等一系列

// todo 后续补充相关内容,或单独补充一篇文章

【声明】本内容来自华为云开发者社区博主,不代表华为云及华为云开发者社区的观点和立场。转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息,否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。