SQL性能优化技巧-优快云博客

1.选取最合适的字段属性

1).长度尽可能小，避免给数据库增加不必要的空间
2).尽量把字段设置为NOT NULL，查询时不用比较null值

2.使用索引

3.有索引但未被用到的情况(不建议)

1).尽量避免like的参数以通配符开头,否则数据库引擎会放弃使用索引而进行全表扫描
如 :
select * from table_name a where a.col like "%0"
这里写图片描述

但是可以以通配符结束
这里写图片描述

2). 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
如：
select id from t where num is null
可以先对表的字段预处理或者建表的时候设置默认值为0

update t set num=0 where num = 0;

此例可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0

3).应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。

这里写图片描述

4).避免在where子句中使用or来连接条件,否则将导致引擎放弃使用索引而进行全表扫
合并.所有查询中的列数和列的顺序必须相同,数据类型必须兼容
从效率上说，union all要比union快很多.
union all:两张表和在一起，字段要相同
union :两张表和在一起，字段要相同,排重

某些复杂语句会用全表扫描,查询写的不够合理，让mysql放弃索引而进行全表扫描

索引列:
低效：

select * from t_credit_detail where Flistid = ‘2000000608201108010831508721’ or Flistid = ‘10000200001’;

高效：

'select * from t_credit_detail where Flistid = ‘2000000608201108010831508721’ union all select * from t_credit_detail where Flistid = ‘10000200001’

非索引列.则都是采取全表扫描,union扫描两次, 最好看执行计划写sql

4.避免select *

在解析的过程中,依次转换成所有的列名,这个工作是通过查询数据字典完成的,这意味着将消耗更多的时间,应该养成一个需要什么就取什么的好习惯

5.order by 语句优化

任何在order by语句的非索引项或者有计算表达式都将降低查询速度
method: 重写order by 语句以使用索引
为所使用的列建立另一索引
绝对避免在order by

6.group by 语句优化

提高group by 语句的效率,可以通过将不需要的记录在group by 之前过滤掉
低效: having 集合后赛选数据

select job,avg(sal) from emp 
group by job
having job = 'president' or job = 'manager'

高效: 集合前赛选数据

select job,avg(sal) from emp
where job = 'president' or job = 'manager'
group by job

7.用exists 代替 in (看情况)

很多时候用exists代替in是一个好的选择
A:10000 B:1000000
in:遍历B表(如果数据B表大于A表则不适合,数据大,查询慢)
查询效率:10000*1000000 (缓存在内存中比较)

select num from t a where a.num in ( select num from t2 b)
以上查询使用了in语句,in()只执行一次,它查出B表中的所有num字段并缓存起来.之后,检查A表的num是否与B表中的num相等,如果相等则将A表的记录加入结果集中,直到遍历完A表的所有记录.

替换
exists:
查询效率:只查询10000次,(不断查询数据库)
select num from t a where a.num exists( select 1 from t2 b where b.num=a.num)
以上查询使用了exists语句,exists()会执行A.length次,它并不缓存exists()结果集,因为exists()结果集的内容并不重要,重要的是结果集中是否有记录,如果有则返回true,没有则返回false.

结论:
in()适合B表比A表数据小的情况
exists()适合B表比A表数据大的情况
in()是在内存里遍历比较,而exists()需要查询数据库,我们都知道查询数据库所消耗的性能更高,而内存比较很快.

8.能用UNION ALL就不要用UNION

UNION ALL不执行SELECT DISTINCT函数，这样就会减少很多不必要的资源。

9、在Join表的时候使用相当类型的例，并将其索引(建表的时候尽量字段类型一致)

如果应用程序有很多JOIN 查询，你应该确认两个表中Join的字段是被建过索引的。这样，MySQL内部会启动为你优化Join的SQL语句的机制。
而且，这些被用来Join的字段，应该是相同的类型的。例如：如果你要把 DECIMAL 字段和一个 INT 字段Join在一起，MySQL就无法使用它们的索引。对于那些STRING类型，还需要有相同的字符集才行。（两个表的字符集有可能不一样）