sql效率低的几种原因及优化

为什么要进行sql的优化

项目开发初期，业务数据量相对较少，一些SQL的执行效率对程序运行效率影响不太明显，随着时间的积累，业务数据量越来越多，sql的执行效率对程序的运行效率影响越来越大，此时对sql的有效就很有必须，来提升整体程序的运行效率。

什么情况会导致sql执行效率低及优化方式

1、首先尽量避免全表查询，在sql中where及order by设计的列上建立索引
2、尽量避免where语句中对字段进行null判断，否则将放弃使用索引而进行全表扫描。
如：select id from where num is null
或可以建表时设置num列的默认值是0，确保表中num列没有null值，然后可以这么查询：
select id from t where num=0。这样不会放弃索引
3、尽量避免wherer语句后使用!=或者<>操作符，否则将导致放弃索引而进行全表扫描
4、应该尽量避免where子句中使用or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，比如以下情况：
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、in和not in也要慎用，否则会进行全表查找。比如：
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=1002
8、应尽量避免where字句中对字段进行函数操作，这样会导致引擎放弃索引而进行全表扫描。比如：
select id from t where substring(name,1,3)=‘abc’
可以改为：
select id from t where name like ‘abc%’
9、不要在where子句中“=”左边进行函数、算术运算或者表达式运算，否则系统安静无法正确使用所以。
10、在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引不会被使用，并且应该尽可能让字段顺序与索引顺序相一致。
11、不要写没有意义的查询，如果需要生成一个空表结构：
select col1，col2 into #t from t where 1=0;
这类代码不会返回任何结果集，但是会消耗系统资源，应该改为这样：
create table #t(…)
12、很多时候用exist是代替in是一个好的选择
select num frim a where num in (select num from b)
用下面语句进行替换：
select num frm a where exists(select 1 from b where num =a.num)
13、并不是所有索引都对查询都有效，SQL是根据表中数据来进行优化查询的，当索引列有大量数据重复时，SQL查询核能不会去利用索引，。比如一表中有字段sex时，男或女几乎各占一半，那么即使在sex上建立索引也不会对查询起到作用。
14、索引并不是越多越好，索引固然可以提升相应的select的效率，同时会降低insert及Updata的效率。
因为insert或者update是会重建索引，索引怎样建索引慎重考虑，视具体情况而定。一个表的索引最好不要超过6个。不要在不常用的列上面建索引。
15、尽量使用数字型字段，若只包含数值信息的字段尽量不要设计为字符型，这样会降低查询和连接性能，并会增加存储开销。
这是因为引擎在处理查询和连接时会逐个比较字符串中每个字符，而对数字型而言只需要比较一次就够了。
16、尽可能的使用varchar代替char，因为首先变长字段存储空间小，可以节省存储空间。
其次对于查询来说，相对于较小的字段内搜索效率明显提高。
17、任何地方不要使用select * from t,用到具体字段代替""，不要返回用不到的任何字段来提升效率
18、避免频繁创建和删除临时表，以减少系统表资源的消耗。
19、临时表并不是不可以使用，适当的使用他们可以使某些进程更加有效，例如：当需要重复引用到大型表或者常用表的某个数据集时。
20、在新建临时表时，如果一次性插入数据量很大，那么就可以使用select into代替create table，避免生成大量log，如果一次性插入数据量不大，为了缓和系统表资源，应该先create table，然后insert。
21、如果使用到临时表，在存储过程最后务必将所有临时表显示删除，先truncate table，然后再drop table，这样可以避免系统表的较长时间锁定。
22、尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
23、使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。
24、与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。
在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。
25、尽量避免大事务操作，提高系统并发能力。
26、尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。