sql优化整理笔记

这是本人把网上搜到的优化方法和自己的一些感想整理总和而成的，如有疏漏错误，欢迎留言不错指正@TOC

程序方面:
1.尽量使用批量sql语句
2.尽量使用预编译sql语句
3.减少连接数据库的次数
4.sql语句尽量避免+号连接
5.尽量避免大事务操作，提高系统并发能力。
6.不要一次性返回大数据量，可以考虑分页返回，或者相应需求是否合理。

索引：
索引是最常用、最简单、很有效的数据库优化手段。
它对业务逻辑没有任何影响,只是一个标记,数据库把会把标记的字段,以B+tree(mysql)的方式组织起来，它可以有效的改善查询速度（B+tree这里就不解释了，有兴趣可以网上搜索）。但是这样也不是没有坏处，比如修改和添加数据的时候，B+tree结构也会相应改变，造成额外的消耗，所以索引也不是越多越好。
总之，查询较多的字段如 where 及 order by 涉及的列，加上索引，而修改，添加比较多的列不要加，具体情况具体衡量。一般一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

关于索引的优化

1.当索引列有大量数据重复时，SQL查询可能不会去利用索引，如sex，isnull等字段，几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用，这种情况，使用索引也不会改善效率，建议删除索引。

2.索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；

3.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致索引失效，而进行全表扫描，如：
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0

4.使用or的时候，确保用到的所有字段都有索引，否则将导致索引失效
or用到的字段，如果有一个字段没有索引，会进行全表扫描。很多时候使用union all或者是union的方式来代替“or”会得到更好的效果。
这里说明一下，尽量用union all代替union
union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。当然，union all的前提条件是两个结果集没有重复数据或不在意重复数据。

select id from t where num=10 or num=20
可以这样查询：
select id from t where num=10
union all
select id from t where num=20

5.尽量避免使用“like’%abc’”的句式,会在成索引失效
select id from t where name like ‘%abc’

6.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算

select id from t where num/2=100
应改为:
select id from t where num=100*2

select id from t where substr(name,1,3)=‘abc’
应改为:
select id from t where name like ‘abc%’

7.不要写一些没有意义的查询，如需要生成一个空表结构：
select col1,col2 into #t from t where 1=0
这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：
create table #t(…)

8.concat或||是mysql和oracle的字符串连接操作，如果对列进行该函数操作，那么也开会忽略索引的使用。比较下面的查询语句：
– 忽略索引
select … from … where first_name || ‘’ || last_name = ‘bill gates’ ;
– 使用索引
select … from … where first_name = ‘bill’ and last_name = ‘bill gates’ ;

9.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

10.避免隐式类型转换,特别是索引字段,会导致索引失效
where子句中出现column字段的类型和传入的参数类型不一致的时候发生的类型转换，建议先确定where中的参数类型

其他方面的优化

1.编写统一规范简洁的sql语句,不要写嵌套太多层次,太过复杂的sql
对于以下两句SQL语句，很多人认为是相同的，但是，数据库查询优化器认为是不同的。
select * from dual
select * From dual
虽然只是大小写不同，查询分析器就认为是两句不同的SQL语句，必须进行两次解析,生成2个执行计划。所以应该保证相同的查询语句在任何地方都一致，多一个空格都不行！

2.不要把SQL语句写得太长，太过冗余
一般，将一个Select语句的结果作为子集，然后从该子集中再进行查询，这种一层嵌套语句还是比较常见的,这样的语句很难看懂,容易出错,不易维护，而且根据经验，超过3层嵌套，查询优化器就很容易给出错误的执行计划
如果需要复杂的语句（嵌套多级子查询）,可以考虑适当拆成几步，先生成一些临时数据表，再进行关联操作。

3.减少不必要的表连接,有些数据操作的业务逻辑可以放到应用层进行实现

4.如果where后面有多个条件（or字句除外），优先应该选择过滤数据的操作,并且过滤数据多的优先
select … from … on … where … group by … having … order by … limit …，以上是sql语句的语法结构，其中on、where和having是有过滤行为的，过滤行为越能提前完成就越可以减少传递给下一个阶段的数据量，因此如果在having中的过滤行为能够在where中完成，则应该优先考虑where来实现。

5.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。
这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

6.使用>=代替>
– 直接定位到4的记录(推荐)
select … from … where SAL >= 4 ;
– 先定位到3，再向后找1个(不推荐)
select … from … where SAL > 3 ;

7.尽可能的使用 varchar 代替 char ，因为首先变长字段存储空间小，可以节省存储空间，
其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

8.不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

9.使用GROUP BY时,可以先将不需要的记录在GROUP BY的数据过滤掉.

10.优化update,insert
同一个表的修改在一个过程里出现好几十次，如：
update table1 set col1=… where col2=…; update table1 set col1=… where col2=… …
这类脚本其实可以很简单就整合在一个UPDATE语句来完成

11.合理利用in和exists、not in和not exists
in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。
not in和not exists同样，具体用哪个，看具体业务逻辑

12.LEFT JOIN 左表为驱动表，RIGHT JOIN 右表为驱动表，要以小表驱动大表。
JOIN（即INNER JOIN） MySQL会自动找出那个数据少的表作用驱动表。

13.对于多张大数据量的表JOIN，要先分页再JOIN，否则逻辑读会很高，性能很差。

14.必要时可以使用force index来强制查询走某个索引
有的时候MySQL优化器采取它认为合适的索引来检索SQL语句，但是可能它所采用的索引并不是我们想要的。这时就可以采用forceindex来强制优化器使用我们制定的索引。

15.使用『临时表』缓存中间结果,这样可以避免程序中多次扫描主表，也大大减少了阻塞，提高了并发性能,但要避免频繁创建和删除临时表，以减少系统表资源的消耗,对于一次性事件，最好使用导出表。

16.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

17.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，如数据比较大,先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

18.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
游标的一个常见用途就是保存查询结果，以便以后使用。游标的结果集是由SELECT语句产
生，如果处理过程需要重复使用一个记录集，那么创建一次游标而重复使用若干次，比重复
查询数据库要快的多。

19.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET
NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送DONE_IN_PROC消息。