sql常见优化

为什么80%的码农都做不了架构师？>>>   

sql常见语句
1、此处设计的表格含有用户表，用户企业关系表，用户角色关系表，角色表，
角色功能关系表，企业表，功能权限表(目前只是存储具有权限的，无权限控制的不存储)
2、sql语句常见优化方式
    1：通过变量的方式来设置参数
      好："select * from people p where p.id=?";
      坏:"select * from people p where p.id="+id;
      数据库的sql文解析和执行计划会保存在缓存中，但sql只要变化，就需要重新解析。"where p.id="+id的方式在id值发生变化时需要重新解析，浪费时间。
    2：不要使用select*
    好:"select people_name ,people_id from people";
    坏:"select * from people"
    使用select*的话会增加解析的时间，另外还会把不需要的数据查询出来，数据传输也是需要消耗时间的。
    3：谨慎使用模糊查询
    好："select * from people p where p.id like 'param%'";
    坏："select * from people p where p.id like '%param%';
    当模糊匹配以%开头时，该列索引将失效。不以%开头，该列索引有效。
    4：不要使用列号：
    好："select people_name,people_id from people order by people_name";
    坏:"select people_name,people_id from people order by 1";
    5:优先使用union all 避免使用union
    好:"select name from student union all select name from teacher";
    坏："select name from student union select name from teacher";
    UNION 因为会将各查询子集的记录做比较，故比起UNION ALL ，通常速度都会慢上许多。一般来说，如果使用UNION ALL能满足要求的话，务必使用UNION ALL。还有一种情况，
    如果业务上能够确保不会出现重复记录
    6在where语句或者order by语句中避免对索引字段进行计算操作
    好："select people_name,pepole_age from people where create_date=date1 ";
    坏： "select people_name,pepole_age from people where trunc(create_date)=date1";
    当在索引列上进行操作之后，索引将会失效。正确做法应该是将值计算好再传入进来。
    7:使用not exist代替not in
    好："select * from orders where customer_name not exist (select customer_name from customer)";
    坏： "select * from orders where customer_name not in(select customer_name from customer)";
    如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。
    8:exist和in的区别
    in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。因此，in用到的是外表的索引， exists用到的是内表的索引。
    如果查询的两个表大小相当，那么用in和exists差别不大。
    如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：
    例如：表A（小表），表B（大表）
      1：select * from A where cc in (select cc from B)
      效率低，用到了A表上cc列的索引；
      select * from A where exists(select cc from B where cc=A.cc)
        效率高，用到了B表上cc列的索引。
        select * from B where cc in (select cc from A)
        效率高，用到了B表上cc列的索引；
        select * from B where exists(select cc from A where cc=B.cc)
        效率低，用到了A表上cc列的索引。
    9:避免在索引列上做如下操作：
    ◆避免在索引字段上使用<>，!=
    ◆避免在索引列上使用IS NULL和IS NOT NULL
    ◆避免在索引列上出现数据类型转换（比如某字段是String类型，参数传入时是int类型）
    当在索引列上使用如上操作时，索引将会失效，造成全表扫描。
    10:复杂操作可以考虑适当拆成几步
    有时候会有通过一个SQL语句来实现复杂业务的例子出现，为了实现复杂的业务，嵌套多级子查询。造成SQL性能问题
    对于这种情况可以考虑拆分SQL，
    通过多个SQL语句实现，或者把部分程序能完成的工作交给程序完成。

转载于:https://my.oschina.net/kxhome/blog/1608647