MYSQL避免全表扫描,首先考虑在where及order by涉及的列上建立索引。
导致引擎放弃使用索引而进行全表扫描的情况:
1、where字句对字段进行null判断。 可以设置默认值为0,确保列中无null值。
2、where字句使用!= 或<>操作符。
3、where子句使用OR来连接条件。 可以使用union all代替。
4、where字句中in 和not in 要慎用
5、where字句中like尽量以"常量"开头,不要以'%'开头。
like '%keyword%' 索引失效,无法使用反向索引。
like '%keyword' 索引失效, 使用全表扫描。可以使用反转函数,不走全表扫描,如where reverse(code) like reverse('%Code2')。
like 'keyword%' 索引有效,不会造成全表扫描
6、where字句中 =左边 对字段进行表达式操作,函数操作,算术表达式操作。
7、在使用索引字段作为条件时,复合索引,字段顺序与索引顺序一致。
8、exists代替in,是很好的选择。
9、索引不一定有效,当索引列有大量数据重复时,Sql查询可能不会利用索引。
10、索引并不是越多越好,最好不要超过6个,索引可以提高select效率,会影响update,insert效率。
11、尽量使用数字型字段,尽量varchar代替char。
12、尽量避免使用*。
13、避免频繁的创建和删除临时表,删除表时,先truncate table,后drop table,可以避免系统表长时间的锁定。
14、当重复使用一个数据集时可以建临时表。
新建临时表时,数据量很大,使用select into代替create table,避免造成大量的log,以提高速度。
如果数据量不大,为了缓和系统表资源,应先create table,然后insert。
15、尽量避免大事务操作,提高系统并发能力。
16、尽量避免向客户端返回大数据量,若数据量过大,应考虑需求是否合理。
其它一些优化
1、优化like查询:
a.使用其它函数来进行模糊查询,如果出现的位置大于0,表示包含该字符串,查询效率比like要高。
1、Oracle中,使用instr,这样查询效果很好,速度很快。
select count(*) from table t where instr(t.code,'Cod2%') > 0 ;
2、在mysql中,可以用locate和position函数,如table.field like '%AAA%'
可以改为locate('AAA', table.field) > 0或POSITION('AAA' IN table.field)>0。
LOCATE(substr,str)、POSITION(substr IN str):返回子串 substr 在字符串 str 中第一次出现的位置。
如果子串 substr 在 str 中不存在,返回值为 0。
3、在sql server中,可以给字段建立全文索引,用contains来检索数据,
CONTAINS用法,可以参考:http://bijian1013.iteye.com/blog/2232872
2、exist和in
如果主查询的数据集大,则使用In,效率高。
如果子查询的数据集大,则使用exist,效率高。
3、order by优化
提高order by查询的策略:
a.选择使用单路、双路 ;调整buffer的容量大小;
b.避免select * ...
c.复合索引 不要跨列使用 ,避免using filesort
d.保证全部的排序字段 排序的一致性(都是升序 或 降序)