常用的SQL优化！！！

常用的SQL优化

①以小表驱动大表：

意思就是就是首先将小表进行扫描，然后再根据小表的结果对大表进行连接或过滤，以减少大表的数据量，提高查询效率。

• join操作，小表驱动大表的前提是大表有建立关联字段的索引，如果选择A表作为驱动表，B表作为被驱动表，整个过程就是先从A表中拿一行数据，再拿去与B表匹配，总的时间复杂度就是A表的行数*B表走索引的时间logN，所以应选择A表作为小表

• In子查询：在使用In子查询时，MySql 的执行顺序会先执行子查询，再执行主查询。所以使用时，将小表作为主查询的子查询，即将小表的结果作为In关键字后面的子查询，而将大表作为外层主查询。这样数据库引擎会先执行小表的子查询，获取结果集后再去匹配外层主查询的结果，从而减少大表的扫描次数。

例如：select rate from B where id in(select id from A)
      这里的A应为小表

• Exists子查询：在使用Exists子查询时，会先对where 前的主查询询进行查询,然后用主查询的结果一个一个的代入exists的查询进行判断。所以使用Exists时，要将小表作为子查询。这样数据库引擎会先执行小表的子查询，获取结果集后再去判断外层主查询是否存在符合条件的记录，从而减少大表的扫描次数。

例如：select rate from B where exists (Select * from A where B.id=A.id and money >1000 );
      这里的A应为小表

小tips：

Exists和In都有过滤功能，它们最大的差别就是in引导的子句只能对一个字段进行限制，Exists可以对多个字段进行限制

②避免使用 select *

原因：
1、如果实际业务场景中，根本用不上那么多列的数据，那多查出的数据浪费了一定的内存和cpu资源
2、select * 不会走覆盖索引，会出现大量的回表操作【覆盖索引：所查的列数据，正好为索引的话，就不需要回表操作】

③Union 和 Union all ，如果需要用到，尽量选择Union all

原因：使用union all关键字，可以获取所有数据，包含重复的数据。
        使用union关键字后，可以获取排重后的数据，排重的过程需要遍历、排序和比较，它更耗时，更消耗cpu资源，所以如果没说需要排重的场景，可以就用union all。

④需要插入多个数据到表中时，使用批量插入，不要循环一个一个插入

原因：频繁请求数据库，是会消耗一定性能的，尽量减少请求数据库的次数。当然，一次性插入太多数据，数据库执行也会很慢，超过500条可以分批插入数据

可以使用ibatis或mybatis中的批量插入方法，例如：orderMapper.insertBatch(list):

⑤需要判断某个数据是否存在时，别再用count(*) 了，用limit代替！！！

原因：用count(*) 其实相当于全表扫描了，因为每条数据都要遍历到，计算有多少条数据时符合条件的而用LIMIT 1，数据库查询时遇到一条就会返回，而不会再继续查找还有多少条数据

一般做法：
####sql写法：
   select count(*) from user where status=1 and sex = 1
####java代码：
   int nums = xxDao.countXxxxByXxx(params);
   if ( nums > 0 ) {
          //当存在时，执行这里的代码
    } else {
          //当不存在时，执行这里的代码
    }


优化后：
####sql写法：
   select 1 from user where status=1 and sex = 1 limit 1
####java代码：
    Integer exist = xxDao.existXxxxByXxx(params);
    if ( exist != NULL ) {
        //当存在时，执行这里的代码
    } else {
        //当不存在时，执行这里的代码
    }

⑥使用group by的时候，先where过滤，再分组，避免数据量过大，分组很慢

⑦尽量不要使索引失效：

Ⅰ、查询语句中使用OR关键字，且 or前后两个条件 有一个不使用索引，那么查询将不走索引。
        查询语句只有OR关键字时，如果OR前后的两个条件的列都是索引，那么查询中将使用索引。如果OR前后有一个条件的列不是索引，那么查询中将不使用索引。可以选择用union 或in来代替or。

Ⅱ、使用了 LIKE 关键字，但匹配的第一个字符为 % ，那么查询将不走索引。
        在查询语句中使用 LIKE 关键字进行查询时，如果匹配字符串的第一个字符为“%”，索引不会被使用。如果“%”不是在第一个位置，索引就会被使用。【最左前缀匹配原则】

Ⅲ、使用复合索引，但查询语句中没有使用复合索引的第一个字段，那么查询将不走索引。
        复合索引是在表的多个字段上创建一个索引，只有查询条件中使用了这些字段中的第一个字段，索引才会被使用。【最左前缀匹配原则】

Ⅳ、当使用索引和不使用索引的效率差不多时，可能不会走索引

Ⅴ、对索引列使用列运算或函数时，也不会走索引

例如：假设rate是索引，那么以下这两种情况是不会走索引的

    select * from user where rate + 1 = 2 ;

    select * from user where SUBSTR(rate,1,3) = '100';

⑧使用“CASE WHEN 表达式 then ”时，不要在表达式中写select语句

当select查询里存在“CASE WHEN 表达式 then ” 时，访问路径类似for循环，所以不要在表达式中写select语句，不然每进行一次判断，都去查表，效率很低

示例：
    select  a.id,
            case when a.formid in (select id from b where b.salary >0) then 1
            else 2
            end test
    from test a

优化：
     select  a.id,
            case when a.formid = c.id then 1
            else 2
            end test
    from test a
    left join (select id from b where b.salary >0) c on c.id = a.formid