sql调优:优化响应时间(优化sql) ； 优化吞吐量

Sql性能调优的目的

1.优化响应时间>>优化sql

经过调优后，执行查询、更新等操作的时候，数据库的反应速度更快，花费的时间更少。

2.优化吞吐量

即“并发”, 就是“同时处理请求”的能力。

优化sql

尽量将多条SQL语句压缩到一句>>减少访问数据库的次数

SQL中每次执行SQL的时候都要建立网络连接、进行权限校验、进行SQL语句的查询优化、发送执行结果，这个过程是非常耗时的，因此应该尽量避免过多的执行SQL语句，能够压缩到一句SQL执行的语句就不要用多条来执行。

使用表的别名

>>当在SQL语句中连接多个表时, 尽量使用表的别名并把别名前缀于每个列上。这样一来,就可以减少解析的时间并减少那些由列歧义引起的语法错误。

合理使用游标

>>游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写

>>使用WHILE循环代替游标，性能通常更好

>>MERGE语句可以同时处理插入、更新和删除操作，适合替代游标中的复杂逻辑

选择记录条数最少的表作为基础表(在FROM 子句中包含多个表的情况下)

 

用TRUNCATE语句替代DELETE语句(清空表数据时)

>>当执行TRUNCATE命令时, 回滚段不会存放任何可被恢复的信息，所有数据不能被恢复。因此很少资源被调用，整个执行时间就会很短。

 

通过用索引提高效率, 且避免导致索引失效的情况

>>避免SQL中出现隐式类型转换

(索引字段在作为where条件)因为隐式类型转换也属于计算，所以此时DBMS会使用全表扫面。

>>避免在索引列上使用空值判断,即 IS NULL和IS NOT NULL。

可能造成优化器假设匹配的记录数太多,检索范围过宽,DBMS优化器将放弃索引查找而使用全表扫描。

>>避免在索引列上使用NOT。

>>避免在索引列上使用函数

>>避免在索引上进行数学计算(+-*/)

>>用UNION替换OR(适用于索引列):

>>联合索引遵循最左原则：

如果索引是建立在多个列上,只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引。

>>避免改变索引列的类型:

当比较不同数据类型的数据时, ORACLE自动对列进行简单的类型转换

假设 EMPNO是一个数值类型的索引列. SELECT … FROM EMP WHERE EMPNO = ‘123' 实际上,经过ORACLE类型转换, 语句转化为: SELECT … FROM EMP WHERE EMPNO = TO_NUMBER(‘123') 幸运的是,类型转换没有发生在索引列上,索引的用途没有被改变.

假设EMP_TYPE是一个字符类型的索引列. SELECT … FROM EMP WHERE EMP_TYPE = 123 这个语句被ORACLE转换为: SELECT … FROM EMP WHERE TO_NUMBER(EMP_TYPE)=123 因为内部发生的类型转换, 这个索引将不会被用到!

 

Union

>>用UNION ALL替换UNION，(union all不去重,性能更好)

>>用UNION替换WHERE子句中的OR, 可以避免索引的失效

当WHERE子句中使用OR连接多个条件时，数据库优化器可能无法有效利用索引，从而导致全表扫描

通过将OR条件重写为UNION，可以将查询拆分为多个子查询，每个子查询独立利用索引EXISTS

 

EXISTS

>>当SQL包含一对多表查询时,用EXISTS替换DISTINCT

DISTINCT关键字用于去除查询结果中的重复行。它的实现方式通常是： 先执行查询，生成一个中间结果集。 然后对中间结果集进行排序或哈希操作，以去除重复行

短路评估：EXISTS子句在找到第一个匹配的记录后会立即停止搜索，不会继续处理剩余的记录。

 布尔逻辑：EXISTS返回布尔值（TRUE或FALSE），而不是具体的行数据，因此它避免了不必要的数据处理和去重操作

>>用EXISTS替代IN、用NOT EXISTS替代 NOT IN：

  EXISTS和NOT EXISTS通过短路评估（找到第一个匹配项后停止搜索）提高了查询效率

用 >= 替换 >

WHERE age > 25如果数据中存在大量等于25的记录，>=可能会更快地定位到起始点

在select中,避免*的使用

oracle在解析的过程中，会将“* ”依次转换成列名, 这是通过查询数据字典完成的, 这将耗费更长的时间。

简单等值比较使用DECODE函数, 复杂判断条件case when

DECODE语法简洁, 可以减少SQL解析的复杂度

短路评估：DECODE函数在找到匹配条件后会立即返回结果，而不会继续评估后续条件

在使用oracle时，尽量多使用COMMIT命令。 该命令可以明显释放运行资源，因此程序的性能得到明显提高。（太基础，本来就应该这么做，不建议说）

SQL语句尽量用大写的

因为oracle总是先解析SQL语句，把小写的字母转换成大写的再执行。

尽量将HAVING中的条件放到where中.

减少数据处理量：将条件从HAVING移到WHERE可以尽早地过滤掉不满足条件的记录，从而减少后续数据处理的量。

避免不必要的聚合计算：如果条件可以放在WHERE子句中，可以避免对不满足条件的记录进行聚合计算，提高查询效率。

利用索引：WHERE子句中的条件可以利用索引进行快速过滤，而HAVING子句中的条件通常无法利用索引

使用参数化SQL,预编译查询

程序中通常是根据用户的输入来动态执行SQL，这时应该尽量使用参数化SQL,这样不仅可以避免SQL注入漏洞攻击，最重要数据库会对这些参数化SQL进行预编译，这样第一次执行的时候DBMS会为这个SQL语句进行查询优化并且执行预编译，这样以后再执行这个SQL的时候就直接使用预编译的结果，这样可以大大提高执行的速度。

提高"抗并发"能力的方法

降低事务隔离级别（一定程度地牺牲数据一致性等）>>数据分析系统(读多写少)

通过“集群”等方式，实现请求的“负载均衡”>>Hadoop

为什么降低隔离级别可以提高抗并发能力？

减少锁竞争：高隔离级别（如SERIALIZABLE）通常需要更严格的锁机制来保证数据一致性，这会导致事务之间频繁的锁竞争，降低并发性能。

减少锁等待时间：降低隔离级别可以减少锁的使用范围和持有时间，从而减少事务之间的等待时间，提高系统的吞吐量。

牺牲一致性换取性能：在某些业务场景中，数据一致性要求并不严格，可以通过适当降低隔离级别来换取更高的并发性能。

为什么集群可以提高抗并发能力？

负载均衡：通过将请求分散到多个数据库节点上，避免单个节点过载，从而提高系统的整体处理能力。

高可用性：集群中的节点可以相互备份，当某个节点出现故障时，其他节点可以接管其请求，保证系统的可用性。

扩展性：可以通过增加节点来水平扩展系统的处理能力，适应不断增长的业务需求。

 

适用场景:对并发性能和可用性要求较高的场景,例如

高流量的在线交易系统：如电商平台、金融系统等，需要处理大量并发请求。

分布式大数据系统：如Hadoop、Cassandra等，通过分布式架构处理海量数据。

 

数据库集群是指通过多台数据库服务器（DB Server）协同工作，共同承担业务请求的系统。