在进行数据库操作时，优化 SQL 语句是提升性能和效率的关键步骤之一。无论处理的是大规模数据还是简单的查询，优化 SQL 语句都可以明显改善系统的响应时间和资源利用率。

1. 使用内连接

   当涉及到多个表的连接查询时，通常使用 join 关键字。最常用的连接方式是左连接和内连接。以下使用 inner join 的示例：

   
           SELECT o.id, o.code, u.name
           FROM order o
           INNER JOIN user u
             ON o.user_id = u.id
           WHERE u.status = 1;
         

   如果两个表使用 inner join 关联，会自动选择两个表中的小表驱动大表，因此在性能上不会出现太多问题。以下使用 left join 的示例：

   
           SELECT o.id, o.code, u.name
           FROM order o
           LEFT JOIN user u
             ON o.user_id = u.id
           WHERE u.status = 1;
         

   如果两个表使用 left join 关联，MySQL 默认使用左连接关键字驱动右侧的表。如果左表中存在大量数据，则可能会出现性能问题。需要注意的是，在使用 left join 查询时，应该将小表放在左侧，将大表放在右侧。如果可以使用 inner join，应尽量避免使用 left join。

2. 限制索引的数量

   众所周知，索引可以大幅提高 SQL 查询的性能，但索引的数量并不是越多越好。因为当向表中添加新数据时，同时需要为其创建索引，而索引需要额外的存储空间和一定的性能消耗。单个表中的索引数量应尽量控制在 5 个以内，单个索引中的字段数量也不应超过 5 个。MySQL 使用的 B+ 树结构来保存索引，B+ 树索引在插入、更新和删除操作时需要进行更新。如果索引过多，将消耗大量的额外性能。

   那么，如果表中的索引过多，超过了 5 个怎么办呢？这个问题需要辩证地看待。如果你的系统并发性较低，表中的数据量也不是很大，实际上可以使用超过 5 个的索引，只要不过度即可。但对于一些高并发的系统，务必遵守单个表上不超过 5 个索引的限制。那么，高并发系统如何优化索引的数量呢？

   • 如果可以建立联合索引，就不要建立单个索引，可以删除一些无用的单个索引。
   • 将一些查询功能迁移到其他类型的数据库中，比如 ElasticSearch、HBase 等，只需在业务表中建立少量的关键索引即可。

3. 选择适当的字段类型

   char 表示固定长度的字符串类型，该类型的字段存储空间是固定的，会浪费存储空间。

   
           ALTER TABLE order
           ADD COLUMN code CHAR(20) NOT NULL;
         

   varchar 表示可变长度的字符串类型，该类型的字段存储空间会根据实际数据的长度进行调整，不会浪费存储空间。

   
           ALTER TABLE order
           ADD COLUMN code VARCHAR(20) NOT NULL;
         

   如果是固定长度的字段，比如用户的手机号码，一般是 11 位，可以定义为长度为 11 字节的 char 类型。但如果是企业名称字段，如果定义为 char 类型，会存在问题。如果长度定义得过长，例如定义为 200 字节，而实际企业名称只有 50 字节，将浪费 150 字节的存储空间。如果长度定义得过短，例如定义为 50 字节，而实际企业名称有 100 字节，将无法存储，并抛出异常。因此，建议将企业名称改为 varchar 类型。可变长度字段的存储空间较小，可以节省存储空间，对于查询来说，在相对较小的字段中搜索效率显然更高。

   选择字段类型时，应遵循以下原则：

   • 如果可以使用数字类型，就不要使用字符串，因为数字类型的存储空间更小，查询效率更高。
   • 尽量使用小型类型，例如使用 bit 类型存储布尔值，tinyint 类型存储枚举值等。
   • 对于固定长度的字段，可以使用 char 类型。
   • 对于可变长度的字段，可以使用 varchar 类型。
   • 对于金额字段，使用 decimal 类型，避免精度丢失的问题

如何提高sql语句的执行效率

1. SQL优化的原则是：将一次操作需要读取的BLOCK数减到最低,即在最短的时间达到最大的数据吞吐量。 调整不良SQL通常可以从以下几点切入： ? 检查不良的SQL，考虑其写法是否还有可优化内容 ? 检查子查询 考虑SQL子查询是否可以用简单连接的方式进行重新书写 ? 检查优化索引的使用 ? 考虑数据库的优化器 2. 避免出现SELECT * FROM table 语句，要明确查出的字段。 3. 在一个SQL语句中，如果一个where条件过滤的数据库记录越多，定位越准确，则该where条件越应该前移。 4. 查询时尽可能使用索引覆盖。 即对SELECT的字段建立复合索引，这样查询时只进行索引扫描，不读取数据块。 5. 在判断有无符合条件的记录时建议不要用SELECT COUNT （*）和select top 1 语句。 6. 使用内层限定原则，在拼写SQL语句时，将查询条件分解、分类，并尽量在SQL语句的最里层进行限定，以减少数据的处理量。 7. 应绝对避免在order by子句中使用表达式。 8. 如果需要从关联表读数据，关联的表一般不要超过7个。 9. 小心使用 IN 和 OR，需要注意In集合中的数据量。 建议集合中的数据不超过200个。 10. <> 用 < 、 > 代替，>用>=代替，<用<=代替，这样可以有效的利用索引。 11. 在查询时尽量减少对多余数据的读取包括多余的列与多余的行。 12. 对于复合索引要注意，例如在建立复合索引时列的顺序是F1，F2，F3，则在where或order by子句中这些字段出现的顺序要与建立索引时的字段顺序一致，且必须包含第一列。 只能是F1或F1，F2或F1，F2，F3。 否则不会用到该索引。 13. 多表关联查询时，写法必须遵循以下原则，这样做有利于建立索引，提高查询效率。 格式如下select sum（） from table1 table1, table2 table2, table3 table3 where (table1的等值条件（=）) and (table1的非等值条件) and (table2与table1的关联条件) and (table2的等值条件) and (table2的非等值条件) and (table3与table2的关联条件) and (table3的等值条件) and (table3的非等值条件)。 注:关于多表查询时from 后面表的出现顺序对效率的影响还有待研究。 14. 子查询问题。 对于能用连接方式或者视图方式实现的功能，不要用子查询。 例如：select name from customer where customer_id in ( select customer_id from order where money>1000)。 应该用如下语句代替：select name from customer inner join order on _id=_id where >100。 15. 在WHERE 子句中，避免对列的四则运算，特别是where 条件的左边，严禁使用运算与函数对列进行处理。 比如有些地方 substring 可以用like代替。 16. 如果在语句中有not in（in）操作，应考虑用not exists（exists）来重写,最好的办法是使用外连接实现。 17. 对一个业务过程的处理，应该使事物的开始与结束之间的时间间隔越短越好，原则上做到数据库的读操作在前面完成，数据库写操作在后面完成，避免交叉。 18. 请小心不要对过多的列使用列函数和order by,group by等，谨慎使用disti软件开发t。 19. 用union all 代替 union，数据库执行union操作，首先先分别执行union两端的查询，将其放在临时表中，然后在对其进行排序，过滤重复的记录。 当已知的业务逻辑决定query A和query B中不会有重复记录时，应该用union all代替union，以提高查询效率。

ORACLE SQL语句优化技术分析

为了让更多的新手受益，我抽空把SQL语句优化部分进行了整理，希望大家一起进步。

一、操作符优化

1、IN 操作符

用IN写出来的SQL的优点是比较容易写及清晰易懂，这比较适合现代软件开发的风格。但是用IN的SQL性能总是比较低的，从Oracle执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别：

ORACLE试图将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录，如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功，但对于含有分组统计等方面的SQL就不能转换了。

推荐方案：在业务密集的SQL当中尽量不采用IN操作符，用EXISTS 方案代替。

2、NOT IN操作符

此操作是强列不推荐使用的，因为它不能应用表的索引。

推荐方案：用NOT EXISTS 方案代替

3、IS NULL 或IS NOT NULL操作（判断字段是否为空）

判断字段是否为空一般是不会应用索引的，因为索引是不索引空值的。

推荐方案：用其它相同功能的操作运算代替，如：a is not null 改为 a0 或a’’等。不允许字段为空，而用一个缺省值代替空值，如申请中状态字段不允许为空，缺省为申请。

4、 及操作符（大于或小于操作符）

大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，但有的情况下可以对它进行优化，如一个表有100万记录，一个数值型字段A，30万记录的A=0，30万记录的A=1，39万记录的A=2，1万记录的A=3。那么执行A2与A=3的效果就有很大的区别了，因为A2时ORACLE会先找出为2的记录索引再进行比较，而A=3时ORACLE则直接找到=3的记录索引。

5、LIKE操作符

LIKE操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题，如LIKE ‘%5400%’ 这种查询不会引用索引，而LIKE ‘X5400%’则会引用范围索引。

一个实际例子：用YW_YHJBQK表中营业编号后面的户标识号可来查询营业编号 YY_BH LIKE ‘%5400%’ 这个条件会产生全表扫描，如果改成YY_BH LIKE ’X5400%’ OR YY_BH LIKE ’B5400%’ 则会利用YY_BH的索引进行两个范围的查询，性能肯定大大提高。

6、UNION操作符

UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。如： select * from gc_dfys union select * from ls_jg_dfys 这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。

推荐方案：采用UNION ALL操作符替代UNION，因为UNION ALL操作只是简单的将两个结果合并后就返回。

select * from gc_dfys union all select * from ls_jg_dfys

二、SQL书写的影响

1、同一功能同一性能不同写法SQL的影响。

如一个SQL在A程序员写的为 Select * from zl_yhjbqk

B程序员写的为 Select * from _yhjbqk（带表所有者的前缀）

C程序员写的为 Select * from （大写表名）

D程序员写的为 Select * from （中间多了空格）

以上四个SQL在ORACLE分析整理之后产生的结果及执行的时间是一样的，但是从ORACLE共享内存SGA的原理，可以得出ORACLE对每个SQL 都会对其进行一次分析，并且占用共享内存，如果将SQL的字符串及格式写得完全相同，则ORACLE只会分析一次，共享内存也只会留下一次的分析结果，这不仅可以减少分析SQL的时间，而且可以减少共享内存重复的信息，ORACLE也可以准确统计SQL的执行频率。

2、WHERE后面的条件顺序影响

WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响。如： Select * from zl_yhjbqk where dy_dj = 1KV以下 and xh_bz=1 Select * from zl_yhjbqk where xh_bz=1 and dy_dj = 1KV以下 以上两个SQL中dy_dj（电压等级）及xh_bz（销户标志）两个字段都没进行索引，所以执行的时候都是全表扫描，第一条SQL的dy_dj = 1KV以下条件在记录集内比率为99%，而xh_bz=1的比率只为0.5%，在进行第一条SQL的时候99%条记录都进行dy_dj及xh_bz的比较，而在进行第二条SQL的时候0.5%条记录都进行dy_dj及xh_bz的比较，以此可以得出第二条SQL的CPU占用率明显比第一条低。

3、查询表顺序的影响

在FROM后面的表中的列表顺序会对SQL执行性能影响，在没有索引及ORACLE没有对表进行统计分析的情况下，ORACLE会按表出现的顺序进行链接，由此可见表的顺序不对时会产生十分耗服物器资源的数据交叉。（注：如果对表进行了统计分析，ORACLE会自动先进小表的链接，再进行大表的链接）

三、SQL语句索引的利用

1、操作符优化（同上）

2、对条件字段的一些优化

采用函数处理的字段不能利用索引，如：

substr(hbs_bh,1,4)=’5400’，优化处理：hbs_bh like ‘5400%’

trunc(sk_rq)=trunc(sysdate)， 优化处理：sk_rq=trunc(sysdate) and sk_rqtrunc(sysdate+1)

进行了显式或隐式的运算的字段不能进行索引，如：ss_df+2050，优化处理：ss_df30

‘X’ || hbs_bh’X’，优化处理：hbs_bh’’

sk_rq+5=sysdate，优化处理：sk_rq=sysdate-5

hbs_bh=，优化处理：hbs_bh=’ ’，注：此条件对hbs_bh 进行隐式的to_number转换，因为hbs_bh字段是字符型。

条件内包括了多个本表的字段运算时不能进行索引，如：ys_dfcx_df，无法进行优化 qc_bh || kh_bh=’’，优化处理：qc_bh=’5400’ and kh_bh=’’

四、其他

ORACLE的提示功能是比较强的功能，也是比较复杂的应用，并且提示只是给ORACLE执行的一个建议，有时如果出于成本方面的考虑ORACLE也可能不会按提示进行。根据实践应用，一般不建议开发人员应用ORACLE提示，因为各个数据库及服务器性能情况不一样，很可能一个地方性能提升了，但另一个地方却下降了，ORACLE在SQL执行分析方面已经比较成熟，如果分析执行的路径不对首先应在数据库结构（主要是索引）、服务器当前性能（共享内存、磁盘文件碎片）、数据库对象（表、索引）统计信息是否正确这几方面分析。