SQL性能优化

今天药忘吃喽~ 2023-02-15 12:53 18阅读 0赞

### 选择最有效率的表连接顺序 ###

SQL的语法顺序：

select 【distinct】 ....from ....【xxx join】【on】....where....group by ....having....【union】....order by......

SQL的执行顺序：

from ....【xxx join】【on】....where....group by ....avg()、sum()....having....select 【distinct】....order by......

from 子句--执行顺序为从后往前、从右到左  
表名(最后面的那个表名为驱动表，执行顺序为从后往前, 所以数据量较少的表尽量放后）

where子句--执行顺序为自下而上、从右到左

将可以过滤掉大量数据的条件写在where的子句的末尾性能最优

group by 和order by 子句执行顺序都为从左到右

select子句--少用\*号，尽量取字段名称。 使用列名意味着将减少消耗时间。

### 避免产生笛卡尔积 ###

含有多表的SQL，必须指明各表的连接条件，以避免产生笛卡尔积。N个表连接需要N-1个连接条件。

### 避免使用\* ###

把需要的字段列出来。

### 用where子句替换having子句 ###

having子句只会在检索出所有纪录之后才对结果集进行过滤，这个处理需要排序，总计等操作。如果能通过where子句限制记录的数目，那就能减少这方面的开销。where子句搜索条件在进行分组操作之前应用；而having自己条件在进行分组操作之后应用。

### 用exist, not exist, in, not in相互替代 ###

替代的原则是，哪个子查询产生的记录数小，就用哪个。

select * from t1 where x in (select y from t2)
    
    select * from t1 where exists (select null from t2 where y =x)

IN适合于外表大而内表小的情况；exists适合于外表小而内表大的情况。

### 使用exists替代distinct ###

当提交一个包含一对多表信息（比如部门表和雇员表）的查询时，避免在select子句中使用distinct，一般可以考虑使用exists代替，exists使查询更为迅速，因为子查询的条件一旦满足，立马返回结果。

低效写法：

select distinct dept\_no,dept\_name from dept d,emp e where d.dept\_no=e.dept\_no

高效写法：

select dept\_no,dept\_name from dept d where exists (select 'x' from emp e where e.dept\_no=d.dept\_no)

备注：其中x的意思是：因为exists只是看子查询是否有结果返回，而不关心返回的什么内容，因此建议写一个常量，性能较高！

用exists的确可以替代distinct，不过以上方案仅适用dept\_no为唯一主键的情况，如果要去掉重复记录，需要参照以下写法：

select \* from emp where dept\_no exists (select Max(dept\_no)) from dept d, emp e where e.dept\_no=d.dept\_no group by d.dept\_no)

### 避免隐式数据类型转换 ###

隐式数据类型转换不能适用索引，导致全表扫描！t\_tablename表的phonenumber字段为varchar类型

以下代码不符合规范：

select column1 into i\_l\_variable1 from t\_tablename where phonenumber=18519722169;

应编写如下：

select column1 into i\_lvariable1 from t\_tablename where phonenumber='18519722169';

### 使用索引来避免排序操作 ###

在执行频度高，又含有排序操作的sql语句，建议使用索引来避免排序。排序是一种昂贵的操作，在一秒钟执行成千上万次的sql语句中，如果带有排序操作，往往会消耗大量的系统资源，性能低下。索引是一种有序结果，如果order by后面的字段上建有索引，将会大大提升效率！

### 尽量使用前端匹配模糊查询 ###

例如，column1 like 'ABC%'方式，**可以对column1字段进行索引范围扫描**；而column1 like '%ABC%'方式，即使column1字段上存在索引，也无法使用该索引，只能走全表扫描。

### 不要在选择性较低的字段上建立索引 ###

在选择性较低的字段使用索引，不但不会降低逻辑I/O，相反，往往会增加大量逻辑I/O降低性能。比如，性别列，男和女！

### 避免对列的操作 ###

不要在where条件中对字段进行数学表达式运算，**任何对列的操作都可能导致全表扫描**，这里所谓的操作，包括数据库函数，计算表达式等等，查询时要尽可能将操作移到等式的右边，甚至去掉函数。

例如：下列sql条件语句中的列都建有恰当的索引，但几十万条数据下已经执行非常慢了：

select \* from record where amount/30<1000 (执行时间11s)

由于where子句中对列的任何操作结果都是在sql运行时逐行计算得到，因此它不得不进行全表扫描，而没有使用上面的索引；如果这些结果在查询编译时就能得到，那么就可以被sql优化器优化，使用索引，避免全表扫描，因此sql重写如下：

select \* from record where amount<1000\*30 (执行时间不到1秒)

### 尽量去掉"IN","OR" ###

含有"IN"、"OR"的where子句常会使用工作表，使索引失效，如果不产生大量重复值，可以考虑把子句拆开；拆开的子句中应该包含索引；

select count(\*) from stuff where id\_no in('0','1')

可以拆开为：

select count(\*) from stuff where id\_no='0'

select count(\*) from stuff where id\_no='1'

然后在做一个简单的加法

### 尽量去掉"<>" ###

尽量去掉"<>"，避免全表扫描，如果数据是枚举值，且取值范围固定，可以使用"or"方式

update serviceinfo set state=0 where state<>0;

以上语句由于其中包含了"<>"，执行计划中用了全表扫描（Table access full），没有用到state字段上的索引，实际应用中，由于业务逻辑的限制，字段state智能是枚举值，例如0,1或2，因此可以去掉"<>" 利用索引来提高效率。

update serviceinfo set state=0 where state =1 or state =2

### 避免在索引列上使用IS NULL或者NOT ###

避免在索引中使用任何可以为空的列，导致无法使用索引。

### 批量提交sql ###

如果你需要在一个在线的网站上去执行一个大的DELETE或INSERT查询，你需要非常小心，要避免你的操作让你的整个网站停止相应。因为这两个操作是会锁表的，表一锁住了，别的操作都进不来了。

Apache会有很多的子进程或线程。所以，其工作起来相当有效率，而我们的服务器也不希望有太多的子进程，线程和数据库链接，这是极大的占服务器资源的事情，尤其是内存。

如果你把你的表锁上一段时间，比如30秒钟，那么对于一个有很高访问量的站点来说，这30秒所积累的访问进程或线程，数据库链接，打开的文件数，可能不仅仅会让你的WEB服务崩溃，还可能会让你的整台服务器马上挂了。所以，如果你有一个大的处理，你一定把其拆分。

发表评论取消回复

表情：

评论列表（有 0 条评论，18人围观）

还没有评论，来说两句吧...

相关阅读

相关 SQL性能优化

选择最有效率的表连接顺序 SQL的语法顺序： select 【distinct】 ....from ....【xxx join】【on】....where....gro

今天药忘吃喽~/ 2023年02月15日 12:53/ 0 赞/ 19 阅读

相关 Oracle SQL性能优化

> 最近看到有人私信我问了一些oracle sql的优化问题,分享一篇关于sql优化的文章,后面会多分享一些关于sql优化的文章 > （1）选择最有效率的表

蔚落/ 2022年09月23日 03:51/ 0 赞/ 397 阅读

相关 oracle SQL性能优化

oracle SQL性能优化我们要做到不但会写SQL,还要做到写出性能优良的SQL,以下为笔者学习、摘录、并汇总部分资料与大家分享！（1）选择最

待我称王封你为后i/ 2022年09月18日 07:49/ 0 赞/ 618 阅读

相关 Oracle SQL性能优化

[Oracle SQL性能优化][Oracle SQL] （1）选择最有效率的表名顺序(只在基于规则的优化器中有效)： ORACLE的解析器按照从右到左的

雨点打透心脏的1/2处/ 2022年08月13日 00:43/ 0 赞/ 433 阅读

相关 Oracle SQL性能优化

（1）选择最有效率的表名顺序(只在基于规则的优化器中有效)： ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表

谁践踏了优雅/ 2022年08月03日 13:36/ 0 赞/ 399 阅读

相关性能优化和SQL优化

一、性能调优手段 1、配置参数调优 2、应用算法优化 3、GC内存调优二、集群调优核心：以数据位中心，均衡并发，高效计算三、调优工具 Web UI、nMon

旧城等待，/ 2022年06月03日 01:36/ 0 赞/ 536 阅读

相关 sql性能优化总结

最近在帮一同学做数据库优化，查了一下资料，觉得很不错。一．目的数据库参数进行优化所获得的性能提升全部加起来只占数据库应用系统性能提升的40%左右，

我不是女神ヾ/ 2022年04月17日 04:22/ 0 赞/ 495 阅读

相关 sql server 性能优化

1、用程序中，保证在实现功能的基础上，尽量减少对数据库的访问次数；通过搜索参数，尽量减少对表的访问行数,最小化结果集，从而减轻[网络][Link 1]负担；能够分开的操作尽量

小咪咪/ 2022年02月19日 03:53/ 0 赞/ 575 阅读

相关 SQL性能优化

你在项目中碰到过什么问题你是怎么解决的我的个人回答：之前在做货品管理项目的时候，涉及到进销存单据的查询，会遇到查询很慢，甚至查询失败的情况，我一般都会查阅自己写的SQ

爱被打了一巴掌/ 2021年09月22日 19:38/ 0 赞/ 1033 阅读

相关 SQL 性能优化梳理

点击上方 [Java后端][Java]，选择设为星标优质文章，及时送达 -------------------- 来源：juejin.im/post/59b11ba1

本是古典何须时尚/ 2021年09月02日 04:46/ 0 赞/ 891 阅读