SQL优化
简介
最近不经常写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的影响较大。
注意:
- in 语句,右边的表为驱动表
- exist语句,左边的表为驱动表
- 关联查询中, 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()()()...
原因:
- 一次性查询的数据过多,可能导致内存或者线程崩溃
建议:
- 可使用分批量查询,可以按照id,创建时间等等
八、高效的分页
分页很多时候能帮助我们解决数据量过多的问题,大多数场景下,我们会直接使用limit 起始值,取值范围 进行分页,不过当起始值过大时,会导致执行效率很低
反例:
select * from user limit 100000,10
原因:
- limit a,b的逻辑为 查询前a+b行数据,然后舍弃前a行,所以需要查询出前a行的数据, a过大时,就会导致性能大量消耗
建议:
- 可以进行分流查询,针对不同时间段的数据,进行查询,减少数据量
- 可以使用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操作时,会对索引进行重排序,会消耗性能,索引越多,消耗的性能越多。 如果索引本身是无序,消耗会更多。
- 索引太多,也会消耗很大存储空间
建议:
- 能使用联合索引就优先使用联合索引,删除单个索引
十三、选择合理的字段的类型
不同字段类型,空间,索引等都不同,选择合理的类型会减少存储空间和索引的比较
反例:
#使用char存储 类型,枚举类型
alter table order
add column type varchar(255) NOT NULL;
原因:
- 过长的字段类型会浪费存储空间,过多的字段类型会导致新增失败
- 较小的字段内搜索,查询效率会更高
建议:
- 能用数字,就不用字符串,字符处理比数字要慢
- 尽可能使用小的类型,如tinyint存储枚举数字
十四、提升group by having的效率
group by分组查询,是在已查询的基础上进行分组匹配,分组时使用having进行再处理。
反例:
select user_id from order goup by user_id having price > 200
原因:
- group by having的逻辑是针对已筛选的数据进行处理,且分组本身比较耗时,减少已筛选的数据可以增加分组效率
建议:
- 针对所有容易耗时的操作前,都应该减少数据范围。
十五、索引优化
SQL优化中,索引是非常重要的内容模块,合理的索引能极大的增加查询效率,本质是通过指定列建立索引树,一种特殊的数据结构,主要目的用于排序,便于快速定位,再通过主键定位原始数据行。
关键字:explain sql。用于分析是否走了sql
索引是一块比较大的内容,包含索引如何建立,索引的数据结构,索引的查询,索引的失效,聚集索引等等一系列
// todo 后续补充相关内容,或单独补充一篇文章
- 点赞
- 收藏
- 关注作者
评论(0)