SQL查询优化30条经验
- 对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引.
- 应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:select id from t where num is null可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:select id from t where num=0
注:经本人测试,is not null没有索引,is null使用了索引
- 应尽量避免在 where 子句中使用!=或<>操作符,否则引擎将放弃使用索引而进行全表扫描。
- 应尽量避免在 where 子句中使用or 来连接不同条件,否则将导致引擎放弃使用索引而进行全表扫描。如phone设置了索引,name未设置索引。
SELECT * FROMz_enterprise
WHERE phone = “13362581088” OR phone = “18967576870” 使用了索引。
SELECT * FROMz_enterprise
WHERE phone = “13362581088” OR name = “ss” 未使用索引、
如果将name也设置索引,则上面sql也会使用索引。
如果name和phone是联合索引,则上面sql不会使用索引。
- not in 也要慎用,否则会导致全表扫描,如:select id from t where num not in(1,2,3) 对于连续的数值,能用 between 就不要用 in 了:select id from t where num between 1 and 3
- 下面的查询也将导致全表扫描:select id from t where name like ‘李%’若要提高效率,可以考虑全文检索。
- 应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:select id from t where num/2=100应改为:select id from t where num=100*2
- 应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:select id from t where substring(name,1,3)=’abc’ ,name以abc开头的id
应改为:select id from t where name like ‘abc%’
- 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。
- 在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。
将name和code建立联合索引,name在左:
1.SELECT * FROMz_enterprise
WHERE name = “惜木斋” and code = “112”
2.SELECT * FROMz_enterprise
WHERE code = “112” and name = “惜木斋”
3.SELECT * FROMz_enterprise
WHERE code = “112”
4.SELECT * FROMz_enterprise
WHERE name = “112”
1,2,4都使用了索引,3未使用索引。 建议使用1,因为使用2的时候,mysql优化器也会优化成1那样。
- 很多时候用 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)
- 并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会去利用索引,如一表中有字段sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。
- 索引并不是越多越好,索引固然可 以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有 必要。
- 尽量避免大事务操作,提高系统并发能力。
- .尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。