1. 避免查询全局扫描,首先考虑在where及order by 涉及的列上建立索引。
2. 尽量避免在where字句中使用!=或者<>操作符,否则引擎会放弃使用索引而进行全表扫描。
3. 尽量避免在where字句中对字段进行null值判断。否则会导致全表扫描.
```SQL
# 例如
select id from t where num is null;
# 可以将null设置一个默认值,如果是int类型的比如设置为0,然后如下查询
select id from t were num = 0;
```
4. 尽量避免在where字句中使用or来连接条件,否则会导致全表扫描
```sql
select id from t where num = 10 or num = 20;
# 可以换成如下语句
select id from t where num = 10
union all
select id from t where num = 20
```
5. 【?】模糊查询在最前面加上%会导致全表扫描,若要考虑效率,建议全文检索
```
select id from t where name like "%abc%";
```
6. in与not in 要慎用,否则会导致全局扫描
```
select id from t where num in(1,2,3);
# 对于连续的数值,建议使用between ... and ...
select id from t where num between 1 and 3;
```
7. 如果在where字句中使用参数,也会导致全表扫描。因为
sql只有在编译时建立访问计划,变量的值还是未知的,因而无法
作为所以选择的输入项。
```
select id from t where num=@num
# 可以改为强制查询使用索引
select id from t with(index(索引名)) where num =@num
```
8. 避免在where字句的“=”左边进行函数、算数运算或者其他表达式运算,否则系统无法正常使用索引
```
select id from t where num/2 = 100;
# 改为
select id from t where num = 100 * 2;
```
9. 尽量避免在where字句中对字段进行函数操作,否则会导致全局扫描
```sql
select id from t where substring(name,1,3) = "abc"; # name以abc开头
# 改为
select id from t where name like "abc%";
select id from t where datediff(day,createdate,"2005-11-30") = 0; # "2005-11-30"生成的id
# 改为
select id from t where createdate >= "205-11-30" and createdate < "2005-12-01";
```
- 新建临时表时,如果一次性插入的数据量很大,可以使用insert into 代替 create table ,避免造成大量log,以提高速度;如果数据量不大,为了缓和系统表的资源,应先create table,然后insert into
- 尽量使用表变量来代替临时表。如果表变量包含大量数据,请注意索引非常有限(只有主键索引)
- 很多时候用exists代替in是一个好的选择:
```
select num from a where num in (selet num from b);
# 使用下面的语句替换
select num from a where exists(select 1 from b where num=a.num)
```