1) 对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。
2,应尽量避免在 where 子句中对字段进行 null 值判断,创建表时NULL是默认值,但大多数时候应该使用NOT NULL,或者使用一个特殊的值,如0,-1作为默 认值。
3,应尽量避免在 where 子句中使用!=或<>操作符, MySQL只有对以下操作符才使用索引:<,<=,=,>,>=,BETWEEN,IN,以及某些时候的LIKE。
4,应尽量避免在 where 子句中使用 or 来连接条件, 否则将导致引擎放弃使用索引而进行全表扫描, 可以 使用UNION合并查询: select id from t where num=10 union all select id from t where num=20
5,in 和 not in 也要慎用,否则会导致全表扫描,对于连续的数值,能用 between 就不要用 in 了:Select id from t where num between 1 and 3
6,下面的查询也将导致全表扫描:select id from t where name like ‘%abc%’ 或者select id from t where name like ‘%abc’若要提高效率,可以考虑全文检索。而select id from t where name like ‘abc%’ 才用到索引
7, 如果在 where 子句中使用参数,也会导致全表扫描。
8,应尽量避免在 where 子句中对字段进行表达式操作,尽量避免在where子句中对字段进行函数操作
9,很多时候用 exists 代替 in 是一个好的选择: select num from a where num in(select num from b).用下面的语句替换: select num from a where exists(select 1 from b where num=a.num)
10,尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。
11,尽可能的使用 varchar/nvarchar 代替 char/nchar , 因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。
12,最好不要使用*返回所有: select * from t ,用具体的字段列表代替“*”。
13,使用表的别名(Alias):当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误。
14,常见的简化规则如下:不要有超过5个以上的表连接(JOIN),考虑使用临时表或表变量存放中间结果。少用子查询,视图嵌套不要过深,一般视图嵌套不要超过2个为宜。
15,在IN后面值的列表中,将出现最频繁的值放在最前面,出现得最少的放在最后面,减少判断的次数。
16,尽量使用exists代替select count(1)来判断是否存在记录,count函数只有在统计表中所有行数时使用,而且count(1)比count(*)更有效率。
17,下列SQL条件语句中的列都建有恰当的索引,但执行速度却非常慢:
SELECT * FROM record WHERE substrINg(card_no,1,4)=’5378’ (13秒)
SELECT * FROM record WHERE amount/30< 1000 (11秒)
SELECT * FROM record WHERE convert(char(10),date,112)=’19991201’ (10秒)
分析:
WHERE子句中对列的任何操作结果都是在SQL运行时逐列计算得到的,因此它不得不进行表搜索,而没有使用该列上面的索引;如果这些结果在查询编译时就能得到,那么就可以被SQL优化器优化,使用索引,避免表搜索,因此将SQL重写成下面这样:
SELECT * FROM record WHERE card_no like ‘5378%’ (< 1秒)
SELECT * FROM record WHERE amount< 1000*30 (< 1秒)
SELECT * FROM record WHERE date= ‘1999/12/01’ (< 1秒)
18,选择最有效率的表名顺序(只在基于规则的优化器中有效):
oracle 的解析器按照从右到左的顺序处理FROM子句中的表名,FROM子句中写在最后的表(基础表 driving table)将被最先处理,在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.
19,提高GROUP BY语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉.下面两个查询返回相同结果,但第二个明显就快了许多.
低效:
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
20,mysql查询优化总结:使用慢查询日志去发现慢查询,使用执行计划去判断查询是否正常运行,总是去测试你的查询看看是否他们运行在最佳状态下。久而久之性能总会变化,避免在整个表上使用count(*),它可能锁住整张表,使查询保持一致以便后续相似的查询可以使用查询缓存
,在适当的情形下使用GROUP BY而不是DISTINCT,在WHERE, GROUP BY和ORDER BY子句中使用有索引的列,保持索引简单,不在多个索引中包含同一个列,有时候MySQL会使用错误的索引,对于这种情况使用USE INDEX,检查使用SQL_MODE=STRICT的问题,对于记录数小于5的索引字段,在UNION的时候使用LIMIT不是是用OR。