sql优化总结

  • 时间:
  • 来源:互联网
  • 文章标签:

1、合理的创建索引:
(1)一般在查询条件中的字段可以使用索引:比如where、order by等语句后的字段。
(2)IS NULL在字段允许为空的会走索引,在字段不允许为空的情况下不走索引。
(3)IS NOT NULL不管字段允不允许为空都不走索引。
(4)在’<>’、’ != ‘、‘NOT IN’前的字段不走索引。
(5)‘NOT IN’ 会对内外表都进行全表扫描,没有用到索引;而’NOT EXISTS’ 的子查询依然能用到表上的索引。所以无论那个表大,用not exists都比not in要快。
(6)‘IN’和’EXISTS’都走索引,如果子查询得出的结果集记录较少,主查询中的表较大时应该用IN, 反之如果外层的主查询记录较少,子查询中的表大时使用EXISTS。IN是把外表和内表作hash 连接,而EXISTS是对外表作loop循环,每次loop循环再对内表进行查询。
(7)所有的or条件都必须是独立索引,那么这条语句才会走索引,有一个条件没有索引都会全表扫描。该类sql最好用IN替代。
(8)经过普通运算或函数运算后的字段不走索引。例如:select id from t where num/2 = 100;应该为:select id from t where num = 100*2;
(9)‘LIKE’模糊查询时,开头带有通配符时不走索引(如:’%abc%’、’_abc’),开头不到通配符时会走索引(如:‘abc%’)。
(10)查询过程中很少使用或参考的列,不应该创建索引。
(11)有些时候,我们只是访问表中的几个字段,并且字段内容较少,我们可以为这几个字段单独建立一个联合索引,这样就可以直接只通过访问索引就能得到数据,一般索引占用的磁盘空间比表小很多,所以这种方式可以大大减少磁盘IO开销。如:select id,name from company where type=‘2’;如果这个SQL经常使用,我们可以在type,id,name上创建组合索引create index my_comb_index on company(type,id,name);有了这个组合索引后,SQL就可以直接通过my_comb_index索引返回数据,不需要访问company表。
2、返回更少的数据:
(1)一般采用分页方式,控制每次查询的结果集。
(2)尽可能只返回需要的字段。通过去除不必要的返回字段可以提高性能,可以减少数据在网络上传输开销、减少服务器数据处理开销、减少客户端内存占用、字段变更时提前发现问题,减少程序BUG、如果访问的所有字段刚好在一个索引里面,则可以使用纯索引访问提高性能。
3、减少交互次数
(1)采用jdbc支持batch的提交处理方法:addBatch(String sql) 将给定的 SQL 命令添加到此 Statement 对象的当前命令列表中,executeBatch() 将一批命令提交给数据库来执行,如果全部命令执行成功,则返回更新计数组成的数组,clearBatch() 清空此 Statement 对象的当前 SQL 命令列表。
(2)采用in(list)方法:通过这样处理可以大大减少SQL请求的数量,从而提高性能。但IN里面的值个数超过20个以后性能基本没什么太大变化,也特别说明不要超过100,超过后可能会引起执行计划的不稳定性及增加数据库CPU及内存成本。
(3)设置Fetch Size:当我们采用select从数据库查询数据时,数据默认并不是一条一条返回给客户端的,也不是一次全部返回客户端的,而是根据客户端fetch_size参数处理,每次只返回fetch_size条记录,当客户端游标遍历到尾部时再从服务端取数据,直到最后全部传送完成。但是fetchsize不能设置太大,如果一次取出的数据大于JVM的内存会导致内存溢出,所以建议不要超过1000,太大了也没什么性能提高,反而可能会增加内存溢出的危险。
(4)使用存储过程:大型数据库一般都支持存储过程,合理的利用存储过程也可以提高系统性能。如果采用存储过程,你可以将整个业务逻辑封装在存储过程里,然后在客户端直接调用存储过程处理,这样可以减少网络交互的成本。当然存储过程有以下缺点:
a、不可移植性,每种数据库的内部编程语法都不太相同,当你的系统需要兼容多种数据库时最好不要用存储过程。
b、业务逻辑多处存在,采用存储过程后也就意味着你的系统有一些业务逻辑不是在应用程序里处理,这种架构会增加一些系统维护和调试成本。
c、存储过程和常用应用程序语言不一样,它支持的函数及语法有可能不能满足需求,有些逻辑就只能通过应用程序处理。
d、如果存储过程中有复杂运算的话,会增加一些数据库服务端的处理成本,对于集中式数据库可能会导致系统可扩展性问题。
e、为了提高性能,数据库会把存储过程代码编译成中间运行代码(类似于java的class文件),所以更像静态语言。当存储过程引用的对像(表、视图等等)结构改变后,存储过程需要重新编译才能生效,这可能会导致数据库瞬间压力上升引起故障(Oracle数据库就存在这样的问题)。
4、减少数据库服务器CPU的运算:
(1)尽可能的使用 varchar/nvarchar 代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。
(2)使用绑定变量:绑定变量是指SQL中对变化的值采用变量参数的形式提交,而不是在SQL中直接拼写对应的值。可以防止SQL注入、提高SQL可读性、提高SQL解析性能,不使用绑定变更我们一般称为硬解析,使用绑定变量我们称为软解析。
(3)合理使用排序。
(4)对于一些比较复杂的sql查询,可以适当的使用 WITH AS将其中一部分sql数据先查出来当作其中的临时表,然后再对这些临时表进行操作。使用WITH AS生成的临时表依然会延用原表的索引字段。
5、采用分库分表技术:
(1)可以根据不同的业务,将这些业务的数据分布存放到不同的数据库,其实也就相当于微服务一样,经不同的业务分离成独立的项目,每个独立的项目拥有自己独立的数据库,互不影响。
(2)对于单表数据量很大的表,可以采用垂直分割或者水平分割的方法,将一张大表跟个成多个小表。

本文链接http://www.taodudu.cc/news/show-1782085.html