sql怎么优化
  • 建索引。目的是为了避免全表扫描,经常用到(where、groupby、orderby语句中)、很少修改的字段建索引,也不是越多越好,越多占用空间越大,DML操作性能越受损。

  • 尽量不用select *

  • 有连接操作时:

    • 尽量用小表驱动大表,如left join左边最好放小表
    • 尽量用内连接,因为内连接和外连接的区别:比如左外连接会保留左边全部,右边保留符合条件的,行数自然就比较多了,内连接要求更加严格,只会保留左右都符合的行。不过有时候优化器会自动优化成最优的连接方案
    • 有时候join连接比子查询要快
  • 条件允许可以调整临时表大小

    ​ 临时表一般放在内存中,查询速度较快,当查询结果过大,临时表装不下,就会将中间结果写入磁盘,降低查询速度。mysql默认最大临时表大小为16M,可以调大点:set tmp_table_size=100*1024*1024*1024; 设置为100M

  • 批量插入优化

    • 放在事务中,批量提交。尽量少写insert,比如都写在一条insert内insert into a(name, age) values('a1', 1), ('a2', 2);

    • 关闭唯一校验:若大量插入数据,能提前保证主键列唯一不会重复,可以关闭唯一校验来提升性能:set unique_checks=0,插入完毕后再开启

    • 禁用索引。

      -- 禁用索引,会删除索引,避免边插边建索引
      alter table test DISABLE keys;
      -- 批量插入大量数据。。。
      -- 恢复索引,当插入完毕后,再重新对已有的数据一次性建索引,这样比较快
      alter table test ENABLE keys;
      
  • 利用好索引。

    • 模糊查询like语句优化

      • like '%abc%'无法使用索引,不应该在开头用%,所以是abc%
      • 以常量结束%abc也无法使用索引,此时建立反向索引(oracle支持,mysql不),或者新建一列,存放的是反向字符串(code=>edoc),然后建立索引,查询的时候,用reverse函数like reverse('%abc')
      • 尽量不用模糊查询like,而是使用替代函数,如POSITION('keyword' IN filed)、INSTR(field, 'keyword' )>0、LOCATE('keyword', field)>0、FIND_IN_SET('keyword', field)
    • limit优化

      • 利用limit提前终止查询,比如有些时候只需要查看几条记录,like 'abc%' limit 2
      • limit偏移量越大越慢,要利用好索引,如SELECT id FROM A LIMIT 90000,10优化成select id from A order by id limit 90000,10;
    • where语句

      • 最佳左前缀法则:若有多列索引,查询索引字段应该从左到右,且不要跳过(中断)某一列

        如index(a,b,c)。where a = 3 and b = 5 and c = 4是OK的;where a = 3 and c = 5只用到了a;where a = 3 and b > 4 and c = 7没用到c,因为b范围查询;where b = 3 | where b = 3 and c = 4 | where c = 4没用到任何索引,因为or关系联结

      • 不要用 != 或 <>。会导致索引失效全表扫描,如id!=5优化成id>5 or id<5

      • 不要用is null 或 is not null。一般确保没有null值,然后用 id=0

      • or语句优化。替换成union all,select id from t where n=1 or n=2优化成select id from t where n=1 union all select id from t where n=2

      • 慎用in和not in。

        • 有范围的就用between。如n in (1,2,3)替换成n between 1 and 3
        • exist替换in。n in(select n from b)优化成n exists(select 1 from B where B.n = A.n)。适用于b表比较大的情况。因为直接In会把a、b都查出来,嵌套循环(for a循环里套for b循环),而exist则查出a,对a循环,循环里每次判断时查询数据库b是否有a的id,区别就是,exist不是b全表扫描,因此会快。口诀:in小,exist大
        • left join替换in。num in(select num from B)优化成FROM A LEFT JOIN B ON A.num = B.num
      • where子句中对字段进行表达式操作的优化

        不要在where子句中的“=”左边进行函数、算数运算或其他表达式运算,否则系统将可能无法正确使用索引。

           如SQL:SELECT id FROM A WHERE num/2 = 100 优化成:SELECT id FROM A WHERE num = 100*2

           如SQL:SELECT id FROM A WHERE substring(name,1,3) = 'abc' 优化成:SELECT id FROM A WHERE LIKE 'abc%'

           如SQL:SELECT id FROM A WHERE datediff(day,createdate,'2016-11-30')=0 优化成:SELECT id FROM A WHERE createdate>='2016-11-30' and createdate<'2016-12-1'

           如SQL:SELECT id FROM A WHERE year(addate) <2016 优化成:SELECT id FROM A where addate<'2016-01-01'

    • group by优化

      一个标准的 Group by 语句包含排序、分组、聚合函数,比如 select a,count() from t group by a ; 这个语句默认使用 a 进行排序。如果 a 列没有索引,那么就会创建临时表来统计 a和 count(),然后再通过 sort_buffer 按 a 进行排序。

      ​ 使用group by 分组查询时,默认分组后,还会排序,可能会降低速度,此时使用可以:group by m order by null;,取消排序提升速度。

    • order by优化

      ​ 尽量Using index,覆盖索引,遵守最左前缀原则,避免出现Using filesort。

    • distinct的实现与优化

      ​ 使用索引,不要在大表distinct,避免filesort。DISTINCT 实际上和 GROUP BY的操作非常相似。