SQL性能优化

1 概述

sql是应用程序与数据库之间交互的语言，sql的好坏直接影响了应用程序与数据库交互的效率，在应用程序实际运行阶段，80%的性能问题都和应用程序与数据库交互的效率相挂钩，所以说sql的好坏直接影响的是应用程序的稳定性以及用户的体验度。

2 优化指南

1. 操作系统优化：对于cpu，I/O，内存的添加往往直接影响着sql执行的效率。
2. 数据库优化：根据业务场景优化数据库配置，选择对应的策略。如果是mysql数据库可以在建表的时候选择更合适业务需求的存储引擎（mysql5.5.5开始默认的存储引擎是InnoDB）。

     3、分库分表：当单表数据量过于庞大的时候，就需要考虑进行分库分表了，对于分库可以选用分布式数据库，一般是针对大型的电商网站，而对于分表则是我们经常解决单表数据量过于庞大的手段，分表主要有两种方式，一种是创建历史表，将过早且使用率很低的数据迁移到历史表来减少查询表的数据量；第二种是使用类别来进行分表，这个类别可以是区划、年度、月份，具体根据实际情况而定，这种分表策略可以有效减少查询的数据量，但要考虑创建对应视图以及查询sql的复杂度。

    4、数据结构调整：当查询的sql存在大量的关联查询，而且因此导致查询缓慢的时候，就要考虑数据结构的合理性，评估是否需要调整表结构，通过添加表字段将需要关联查询的字段直接放到查询表中，减少关联查询。

    5、创建索引：索引相当于数据的目录页，对于查询效率的提升是“不可同日而语”的，但索引也并不是百利而无一害的，索引的创建同时会伴随增删改的效率降低，一般单表的索引数量不超过6个。 

    6、分析报告：oracle数据库会生成分析报告，可以通过分析报告中慢SQL TOP10定位查询慢的sql进行处理。

3 sql具体优化方法

3.1 使用union all替换union

相对于union all，union过滤掉重复的数据，其中就包含了排序的操作，执行效率会很慢，所以在明确两个查询语句结果没有重复数据的情况，尽量使用union all。

3.2 将数据量少的表放在右边，将过滤数据量多的条件放在最右边

oracle数据查询的顺序是从由到左，所以多表查询，应尽量将数据量少的表放在右边作为主表，where查询条件也是从右到左所以要把过滤掉数据量比较多的条件放在右边。

3.3 使用group by替换distinct

distinct会先进行排序再进行去重，相对于group by效率很低。

3.4 使用exists替换in

在查询的时候，当查询的内容是多个常量的时候，比如in (A, B, C)这种情况可以直接使用in,但当in中是一条子查询的时候则尽量选择使用exists。

3.5 用union all替换or

相对union all,or会造成多次全表查询，而且不会使用索引，所以建议使用union all替换or。

3.6 使用instr替换like ‘%%’

当like使用前匹配%的时候，会导致索引失效，这里尽量使用instr替换like ‘%%’，instr同样可以建函数索引。

3.7 通过创建全文索引替换like ‘%%’

对于like ‘%%’执行效率低下的情况可以通过创建全文索引解决。

3.8 避免隐式类型转换

在查询的时候，比如查询的字段是varchar2类型的，但是查询sql的时候，传递的是数值类型的，则会导致一个隐式的类型转换，导致索引失效。

3.9 使用预编译语句

oracle在执行sql之前会包括对sql的词法解析，语法解析，以及生成执行计划，然后会放到共享池中，使用预编译可以在遇到相同sql的时候（查询条件值可以不一致），会到共享池中直接拿到执行计划，避免了oracle一系列的工作量，提高了效率，而且，预编译语句可以防止sql注入。