最近看到历史项目里面的很多SQL，有点嗨皮， 一段SQL里面层层嵌套，无限连表，而且有的嵌套层还是一样的。只是稍稍一点点条件不同而已。我见到了一个让我分析了一两个小时的SQL。 因为不是很懂原来的业务，只能从SQL 去倒推，猜想业务是什么样的，比较头痛←_←
SELECT * FROM
(
SELECT FROM
IME_LOG_TRACK_BILL_DETAIL I2
LEFT JOIN ……（ SELECT FROM （SELECT FROM GROUP BY……）GROUP BY……）
当然我这个例子，不能真是代表具体的场景，里面还有很多left join 的东西，关联不同表处理的东西，所以上面这个伪代码并不能表现真实场景。
看这样嵌套很多层，而且有部分重复代码的SQL ，我一般都认为原来这个哥们没有把业务梳理清楚，导致表结构设计不合理。所以后来SQL 也就乌七八糟，东拼西凑能出结果就行，从来不考虑开发的难度与执行的效率。
其实想这样的东西，首先应该更改设计，表结构设计，是不是可以加入中间表，或者说加入某些冗余字段， 后面可以让查询变得很简单，性能也好。
另外，实在没办法的时候，也尽量将里面的嵌套SQL的重复代码，或者功能独立部分的SQL 抽取出来作为类似临时表的样子，这样也总比层层嵌套SQL 去看容易多了吧。
WITH AS
如果是ORACLE, 建议考虑下 WITH AS 语法，很多复杂的SQL 的时候，真的用得上，而且逻辑清晰，好理解。
with my_temp_1 as (
select ... 复杂的语句， 一部分清晰的逻辑
),
my_temp_2 as (
select .... 复杂的语句，一部分清晰的逻辑
)
select a.*, b.column1,b.column2 from my_temp_1 a left join my_temp_2 b on a.id=b.relid
where ......
对于复杂的SQL,这种写法，总比所有语句拼在一起好很多吧。反正我是这么认为的， 只能代表个人观点了。
除了简化复杂的嵌套业务，良好的SQL语句也能提高查询效率。
SQL语句优化
索引的使用规范
索引的创建要与应用结合考虑，建议大的OLTP表不要超过6个索引；尽可能的使用索引字段作为查询条件，尤其是聚簇索引，必要时可以通过index index_name来强制指定索引；避免对大表查询时进行table scan，必要时考虑新建索引；在使用索引字段作为条件时，如果该索引是联合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用；要注意索引的维护，周期性重建索引，重新编译存储过程。
IN 操作符
用IN 写出来的SQL的优点是比较容易写及清晰易懂，这比较适合现代软件开发的风格。
但是用IN 的SQL性能总是比较低的，从ORACLE执行的步骤来分析用IN 的SQL 与不用IN 的SQL 有以下区别：
ORACLE试图将其转换成多个表的连接，如果转换不成功则先执行IN 里面的子查询，再查询外层的表记录，如果转换成功则直接采用多个表的连接方式查询。由此可见用IN 的SQL 至少多了一个转换的过程。一般的SQL 都可以转换成功，但对于含有分组统计等方面的SQL 就不能转换了。
很多时候用 exists 代替 in 是一个好的选择：
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)
避免在索引列上使用计算和函数
WHERE 子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描
避免在索引列上使用NOT
索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的影响. 当ORACLE”遇到”NOT,他就会停止使用索引转而执行全表扫描
此操作是强列推荐不使用的，因为它不能应用表的索引。
推荐方案：用NOT EXISTS 或（外连接+判断为空）方案代替。
select * from table_a a
left join table_b b on a.id=b.table_a_id
where b.table_a_id is null
<> 操作符（不等于）
不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。
推荐方案：用其它相同功能的操作运算代替，如
a<>0 改为a>0 or a<0
a<>’’ 改为 a>’’ or a<’’
LIKE操作符
LIKE 操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题，如like '23%'这种查询不会引用索引，而like '120423%'则会引用范围索引。
一个实际例子：用toRec表中的任务号TaskNum like '23%'这个条件会产生全表扫描，如果改成TaskNum like '120423%' or TaskNum like '130423%'则会利用TaskNum的索引进行两个范围的查询，性能肯定大大提高
IS NULL 或IS NOT NULL 操作（判断字段是否为空）
判断字段是否为空一般是不会应用索引的，因为B树索引是不索引空值的。
推荐方案：
用其它相同功能的操作运算代替，如
a is not null 改为a>0 或a>’’等。
不允许字段为空，而用一个缺省值代替空值，如车辆在岗状态字段不允许为空，缺省为0。
建立位图索引（有分区的表不能建，位图索引比较难控制，如字段值太多索引会使性能下降，多人更新操作会增加数据块锁的现象）
优化 GROUP BY
提高 GROUP BY 语句的效率， 可以通过将不需要的记录在 GROUP BY 之前过滤掉，查询返回结果就会快了许多。
低效:
SELECT JOB , AVG(SAL)
FROM EMP
GROUP BY JOB
HAVING JOB =’PRESIDENT’
OR JOB =’MANAGER’
高效:
SELECT JOB , AVG(SAL)
FROM EMP
WHERE JOB =’PRESIDENT’
OR JOB =’MANAGER’
GROUP BY JOB
参考文章:
SELECT COUNT 小结
为什么索引可以让查询变快，你有思考过吗？
52条SQL语句，性能优化，干货必收藏
索引失效的场景有哪些？索引何时会失效？
posted on
