1.创建索引:
为了尽量避免全表扫描,应该在”经常需要进行检索的字段上“、以及” where 和 order by 涉及的列上“建立索引,从而提高查询效率。
同时,在发现检索速度过慢的时候应该首先想到创建索引。
一个表的索引数最好不要超过6个,如果太多的话就要考虑一下某些不常使用的索引是否有必要,索引不是越多越好,太多也会降低insert、update的效率。
2.避免在索引列上使用计算:
在对索引列使用计算或函数操作时,(如加减乘除、不等于等),数据库的优化器不会使用索引,从而导致全表扫描。
效率低: select * from user where salary*22>11000(salary是索引列)
效率高: select * from user where salary>11000/22(salary是索引列)
3.尽量将多条SQL语句压缩到一句SQL中:
因为每次执行SQL的时候都要建立网络连接、权限校验、SQL的查询优化、发送执行结果等,这个过程非常耗时,所以尽量将多条SQL语句压缩到一句SQL中。
4.尽量用where子句替换Having子句:
因为HAVING子句只会在检索出所有数据之后才会对结果集进行过滤,而where子句则是在聚合前先过滤数据,所以where子句可以减少不必要的开销。
因此,Having子句中的条件一般用于聚合函数的过滤,除此之外,应该将条件写在where字句中。
5.使用表别名:
多表连接时要使用表别名,并且把别名前缀在每个列名上,这样可以减少解析的时间、并减
少因列名歧义引起的语法错误。
6.检索中不存在重复数据时,用union all 替换 union:
union all 不会对结果集去重,union 会对联合的结果集进行去重,即使检索结果中不会有重复的记录,union也会尝试进行合并,然后在输出最终结果前进行排序。所以,如果可以判断检索结果中不存在重复数据时,可以使用union all,这样效率会高很多。
7.尽可能使用varchar 代替 char
char是不可变长字段,varchar是可变长字段,可变长字段存储空间小,可以节省存储空间,并且在一个相对较小的字段内查询效率显然要高些。
(char(100)类型,在字段建立时空间就固定了,不管有没有值(NULL也包含在内),都是占用100个字符的空间。varchar中null不占用空间。)
8.针对Select查询语句的优化:
(1)避免使用 SELECT ,用具体的字段列表代替“”,按需求查找相关列。
(2)尽量避免在模糊查询时以%开头。
(3)尽量减少子查询,可以使用关联查询(left join,right join,inner join)替代子查询。
(4)大小表join时,小表在前大表在后。
(left join就小表在前大表在后,right join就大表在前小表在后)
(5)尽量避免使用or,因为两个字段中有一个没有索引,就会放弃索引进行全表扫描,可以使用union all代替。
原查询中用or:select id from t where num=10 or num=20
优化:用union all代替or:
select id from t where num=10 union all select id from t where num=20
(6)避免在 where 子句中使用空值判断,否则会放弃索引进行全表扫描,可以设置字段的默认值为 not null或0。
避免出现这种情况:select id from t where num is null
(7)对于where条件中连续的数值,能用between就不要用in了:
原查询中用in:select id from t where num in(1,2,3)
优化:用between替代in:select id from t where num between 1 and 3
(8)编写有效的表的顺序,from子句中,写在最后的表是基础表,基础表优先被执行,所以在包含多个表的语句中,把数据量最小的表放在最后作为基础表。