实用的sql顶层优化方案

SQL调优
为什么要调优?
sql命令执行不当的情况下,本地会导致CPU和内存负荷非常大,在企业的生产环境中,硬件资源是非常昂贵的,那么更可怕的做法是远程调用全表扫描回传到本地,在剧烈消耗服务器资源的同时,还大量占用网络资源或导致网络阻塞,风险和成本都非常高.
怎么调优?
对于大数据的行业来说,顾名思义,数据量非常大,sql优化说穿了就是尽量避免一切全表扫描的情况发生,在相同达到目的的情况下使用使用更少的资源和更短的时间.下面是调优的方法和代替方案:
先从sql方法语句说起,现在列举一个烂的一塌糊涂的sql语句:
select *                                                                   //1
from table_name1 t1
where t1.column_name in (                                 //2
    select column_name4
    from table_name2 t2 
    where id in (2,10)                                             //3
    and 
    column_name1/10 = 20                                  //4
    and
    column_name2 is NULL                                 //5
    and
    SUBSTR(column_name3,1,6) = 'Jeremy'     //6
    and
    column_name4 like '%String%'                      //7
    or                                                                        //8
    column_name4 like '%char%'                         //9
    and
    1=1                                                                    //10
)
语句-语法-关键字:
1.避免使用select *,因为在大型数据库中,一张表中column的数量会达到十几个甚至几十个,使用select *会查到一堆用不到的数据而白白浪费硬件资源,所以在查询时用到那个字段就具体写那个字段.
2.一般情况下,带有子查询的sql语句中,子查询中的表数据量会大于主语句中表数据量,而in关键字的内外涉及两张表会跳过索引而进行全表遍历并加入内存,然后in内外的所有数据进行一一匹配,然后返回结果释放内存.很显然这个方法极其消耗硬件资源极不科学.此时,使用exists函数方法代替in方法,更为有效,exists方法只匹配表1数据量的次数,可以减少了硬件资源的压力.
详情方法解读参见:https://www.cnblogs.com/clarke157/p/7912871.html
3.in函数会使查询跳过索引进行全表遍历,我们使用between ... and ...代替in函数避免全表遍历情况的出现.
4.条件表达式左侧出现运算会使索引失效,从而导致全表扫描的结果,这里将表达式左侧的运算等价替换到右边来避免全表扫描.即改为column_name = 10*20
5.NULL判断会导致全表扫描,所以在设计表之初要设置为not NULL,将空值设置为default值来避免NULL的出现.
6.条件表达式左边函数方法同样会导致索引失效导致全表扫描,替换方案为等价移到表达式右侧避免全表扫描,即column_name like 'Jeremy%'
7.模糊查询中,开头使用%同样会使索引失效引起全表扫描,代替方案为'aString%'固定首字母来保持索引的有效性.
8.or关键字同样会引起全表扫描,可使用union关键字代替or关键字,具体参见下面优化后sql.
9.同7
10.1=1查询条件会引起全表扫描,应避免使用,将其删除.
那么优化后的sql查询语句为:
select column_name1,column_name2,...
from table_name1
where exists(
    select id,column_name1,column_name2,column_name3,column_name4 
    from (
        select column_name4
        from table_name2 
        where id between 2 and 10
        and 
        column_name1 = 20*10
        and
        column_name2 = 0
        and
        column_name3 like 'Jeremy%'
        and
        column_name4 like 'aString%'
        union all
        select column_name4
        from table_name2 
        where id between 2 and 10
        and 
        column_name1 = 20*10
        and
        column_name2 = 0
        and
        column_name3 like 'Jeremy%'
        and
        column_name4 like 'achar%'
    ) table_name3 
    where table_name1.column_name = table_name3.column_name4
)

虽然语句变长了,但是减少了资源的浪费.

索引:
1)合理利用索引:建立索引时应考虑where和group by使用到的字段.
2)根据业务实际需求适当创建复合索引以提高搜索效率
3)避免不合理利用索引:避免创建没必要或用不到的字段索引,并且每个表的索引不要超过6个.

分区:
创建合理的,数据大致平均分配的分区标准,例如使用时间或者机构.

PS.对于已投产的项目来说,sql顶层优化是成本最低,效果最好的优化方式,对于sql参数调整,系统参数调整,硬件优化的问题,并且可能不会变好,反而变差,对于已投产的项目来说,这几项优化需要做好充分的可行性分析和风险评估.