sql优化

本文主要是介绍sql优化，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

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的操作非常相似。

这篇关于sql优化的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！