SQL：常用优化方式

　　在SQL查询中为了提高SQL执行效率，我们通常会采取一些措施对查询语句进行SQL优化。

一.常用的优化方式：

　　1、尽量避免全表扫描，首先硬考虑在 where 及 order by 涉及的列上简历索引。
　　2、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。
　　3、应尽量避免在 where 子句中使用 ！= 或者 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。
　　4、应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描。
　　5、in 和 not in 应避免使用，在某些特定的情况下（如，使用 in ，且该列中存在null值）会导致全表扫描，如：
　　select id from t where num in (1,2,3)
　　对于连续的数值，能用 between 就尽量少使用 in
　　select id from t where num between 1 and 3
　　6、like + 双通配符% 通常也会导致全表扫描，如
　　select id from t where name like '%abc%'
　　7、不要在 where 子句中的 = 左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引，如：
　　select id from t where num/2 = 100
　　应改为：
　　select id from t where num = 100 * 2
　　8、应尽量避免在 where 子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描，如：
　　select id from t where substr(name,1,3) = 'abc'
　　应该为：
　　select id from t where name like 'abc%'
　　9、在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
　　10、避免一些没有意义的查询，如需生成一个空表接口：
　　select col1,col2 into #t from t where 1 = 0
　　这类代码不会反悔任何结果集，但是会消耗系统资源的，应该为：
　　create table #t(...)
　　11、使用 exists 代替 in 有时（比如：B表数据量大于A表时）会是一个好的选择
　　select num from a where num in(select num from b)
　　用下面的语句替换：
　　select num from a where exists(select 1 from b where num = a.num)
　　12、并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引。
　　13、索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率。在执行 insert 或 update 时有可能会对索引进行重建。
　　14、尽量使用数字型字段（int，bigint等），若只含数值信息的字段尽量不要涉及为字符型，这会降低查询和连接的性能，并会增加存储开销。引擎在处理查询和连接是会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
　　15、尽量合理设置的字段长度，避免运行时占用过多资源
　　16、不要使用select * from t的形式进行查询，使用具体的字段列表代替 * ， 一来避免读取非使用的字段列，而来防止表结构变更导致后续的错列问题。

二.SQL通用书写规范(基于vertica数据库-列存储数据库)

1.通用规范

　　1、进行 delete 、 update 操作后及时进行 commit 或 rollback 操作
　　vertica锁机制不同于关系型数据库，在进行 delete 或 update 操作时会进行 X lock table ， 所以进行上述操作后需要及时释放锁资源
　　2、避免使用子查询、or，将子查询转化为表连接方式，or 转化为 in
　　or 操作很难优化，or 可转换为 union all 或 in 操作

2.select语句

　　1、明确查询字段，禁止使用 select *
　　避免因表结构变更导致程序异常；明确字段，避免读取所有字段列
　　2、避免出现隐式转换，保持SQL中变量类型与字段类型一直
　　条件语句中数据类型以传入参数为准。id 定义为int型，在程序中使用 id = '1'，就会发生隐式转换，将 id 转换为varchar类型，影响数据库性能，因此应使用id = 1。
　　3、where 条件所涉及的过滤字段尽量选择基数低的列放在前面
　　过滤字段选用高选择性的字段。
　　4、避免对 = 左侧字段使用函数、运算，避免在 join 、group by 中使用函数，这样可以让优化器能更好的发挥作用
　　5、避免使用not、<>、is null、is not null条件
　　6、like子句尽量前置匹配，前置不加%
　　“%”前缀模糊匹配效率低
　　7、order by 字段顺序尽量与 projection 中的 order by 顺序保持一致
　　projection 中已经排序过的数据
　　8、多用 = 操作；>,<操作转换为>=，<=
　　“=”号操作可直接定位到相应的位置，>，<操作需要再移一位才能定位
　　9、尽可能使用where条件替代having子句
　　SQL语句中先执行 where 后执行 having ，过滤的结果越多，group by 的代价越小。

3.insert语句

　　1、insert时建议指定字段名，避免字段顺序变动后数据插入错误
　　2、当需要插入大量数据时，一客使用copy方式装载，避免执行大量 insert 语句
　　3、creat table as select 时，可以使用copy_table 函数提高数据复制效率

4.update语句

　　1、避免 update 语句无 where 条件

5.dalete语句

　　1、避免 dalete 语句无 where 条件
　　2、删除大量数据时，使用trancate语句

6.其他注意事项

　　1、使用 strcat 函数时，必须检验strcat函数中字段数据类型是否为字符型，若为非字符型需强制在使用 strcat 函数前加 to_char 转换
　　当前 strcat 函数在运行字段类型为非字符型时，若程序出错，则SQL无法中止运行，会持续占用系统资源，且不返回任何报错信息。