1、应尽量避免全表扫描,首先考虑在where、order by、group by、between and等涉及的列上建立索引
2、应尽量避免在where子句中对字段进行null值判断。在创建表时null是默认值,大多数时候应该使用not null关键字标注字段非空,或者使用一个特殊的值作为默认值,如0、-1
3、应尽量避免在where子句中使用!=或<>操作符, 在MySQL的where子句中只对<、<=、=、>、>=、between and等操作符以及某些时候的like才使用索引
4、应尽量避免在where子句中使用or作为连接条件,否则将导致引擎放弃使用索引而进行全表扫描, 可以使用union进行合并查询
5、in和not in要谨慎使用,否则会导致全表扫描,对于连续的数值,能用between and就不要用in了6、like查询在下述情况下会导致全表扫描:select id from emp where name like ‘%a%’ 或者select id from emp where name like ’%a’,若要提高效率,可以考虑全文检索。而select id from emp where name like ‘a%’ 查询语句才用到索引
7、如果在 where 子句中使用参数,也会导致全表扫描
8,应尽量避免在 where 子句中对字段进行表达式操作,尽量避免在where子句中对字段进行函数操作,因为在where子句中对列的任何操作结果都是在SQL运行时逐列计算得到的,因此它不得不进行表搜索,而没有使用该列上面的索引;如果这些结果在查询编译时就能得到,那么就可以被SQL优化器优化,使用索引,避免表搜索
9、很多时候可以用exists代替in
10、尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销
11、尽可能的使用 varchar/nvarchar 代替 char/nchar,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些
12、最好不要使用“*”返回所有,而是用具体的字段列表来代替“*”
13、当在SQL语句中连接多个表时,请使用表的别名并在每个列上加上别名的前缀,这样一来,就可以减少解析的时间并减少那些由列歧义引起的语法错误
14、不要有超过5个以上的表连接,考虑使用临时表或表变量存放中间结果。少用子查询,视图嵌套不要过深,一般视图嵌套不要超过2个为宜
15、在IN后面值的列表中,将出现最频繁的值放在最前面,出现得最少的放在最后面,减少判断的次数
16、尽量使用exists代替select count(1)来判断是否存在记录,count函数只有在统计表中所有行数时使用,而且count(1)比count(*)更有效率
17、可以通过将不需要的记录在group by之前用where子句过滤掉
MySQL查询优化总结:总是去使用慢查询日志去发现慢查询,总是去使用执行计划去判断查询是否正常运行,总是去测试你的查询看看是否他们运行在最佳状态下,久而久之性能总会变化;避免在整个表上使用count(*),它可能锁住整张表;使查询保持一致以便后续相似的查询可以使用查询缓存;在适当的情形下使用group by而不是distinct;在where, group by和order by子句中使用有索引的列;保持索引简单,不在多个索引中包含同一个列;有时候MySQL会使用错误的索引,对于这种情况使用use index,检查使用sql_mode=strict的问题