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

posted @ 2021-04-23 23:21 i%2 阅读(80) 评论(0) 收藏举报

刷新页面返回顶部

brightdagger

sql优化

sql怎么优化

公告