SQL优化的十条原则

首先创建如下数据表供测试使用

CREATE TABLE  staffs(
id int primary key auto_increment,
name varchar(24) not null default "" comment'姓名',
age int not null default 0 comment '年龄',
pos varchar(20) not null default ""  comment'职位',
add_time timestamp not null default current_timestamp comment '入职时间'
)CHARACTER SET utf8 comment '员工记录表';

插入几条数据，如下：

insert into staffs(name,age,pos,add_time) values('z3',22,'manage',now());

insert into staffs(name,age,pos,add_time) values('july',23,'dev',now());

insert into staffs(name,age,pos,add_time) values('2000',23,'dev',now());

添加复合索引，包括三列：

alter table staffs add index idx_staffs_nameAgePos(name,age,pos);

1、尽量全值匹配

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25;

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'dev'

上面使用执行计划来看索引是否生效，如图：

Key_len表示索引中使用的字节数,从上面三个数能够看出，每一条语句使用的索引的个数是不相同的，另外从ref中也能够看出。

当建立了索引列后，能在where条件中使用索引的尽量所用。

2、最佳左前缀原则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

EXPLAIN SELECT * FROM staffs WHERE  age = 25 AND pos = 'dev'（不会使用索引）

EXPLAIN SELECT * FROM staffs WHERE pos = 'dev'（不会使用索引）

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' （会使用索引）

3、不在索引列上做任何操作

不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';

EXPLAIN SELECT * FROM staffs WHERE left(NAME,4) = 'July';

4、范围条件放最后

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' ;

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July'  and age >22;

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July'  and age >22 and pos='manager';

中间有范围查询会导致后面的索引列全部失效，age本身索引并不会失效。

5、覆盖索引尽量用

尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July'  and age =22 and pos='manager'
EXPLAIN SELECT name,age,pos FROM staffs WHERE NAME = 'July'  and age =22 and pos='manager'

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July'  and age >22 and pos='manager'
EXPLAIN SELECT name,age,pos FROM staffs WHERE NAME = 'July'  and  age >22 and pos='manager'

6、不等于要慎用

mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';（使用了索引）

EXPLAIN SELECT * FROM staffs WHERE NAME != 'July';（索引失效）

EXPLAIN SELECT * FROM staffs WHERE NAME <> 'July';（索引失效）

如果定要需要使用不等于,请用覆盖索引

EXPLAIN SELECT name,age,pos FROM staffs WHERE NAME != 'July';

EXPLAIN SELECT name,age,pos FROM staffs WHERE NAME <> 'July';

7、Null/Not有影响

如果创建表的时候name字段自定定义为NOT NULL

EXPLAIN select * from staffs where name is null（索引失效，根本不会扫描）

EXPLAIN select * from staffs where name is not null（索引失效）

在字段为not null的情况下，使用is null 或 is not null 会导致索引失效

解决方式：覆盖索引

EXPLAIN select  name,age,pos from staffs where name is not null

如果创建表的时候name字段自定义为NULL或者不定义

EXPLAIN select * from staffs2 where name is null（索引有效）

EXPLAIN select * from staffs2 where name is not null （索引失效）

Is not null 的情况会导致索引失效

解决方式：覆盖索引

EXPLAIN select name,age,pos from staffs where name is not null

8、Like查询要当心

like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作

EXPLAIN select * from staffs where name ='july'（索引有效）

EXPLAIN select * from staffs where name like '%july%'（索引失效）

EXPLAIN select * from staffs where name like '%july'（索引失效） 

EXPLAIN select * from staffs where name like 'july%'（索引有效）

解决方式：覆盖索引

EXPLAIN select name,age,pos from staffs where name like ‘%july%’

9、字符类型加引号

字符串不加单引号索引失效

EXPLAIN select * from staffs where name = 917 （索引失效）

解决方式：请加引号

10、OR改union效率高

EXPLAIN
select * from staffs where name='July' or name = 'z3' （索引失效）

EXPLAIN
select * from staffs where name='July' 
UNION
select * from staffs where  name = 'z3' （索引有效）

解决方式：覆盖索引

EXPLAIN select name,age from staffs where name='July' or name = 'z3'

记忆总结：

• 全值匹配我最爱，最左前缀要遵守；

• 带头大哥不能死，中间兄弟不能断；

• 索引列上少计算，范围之后全失效；

• LIKE百分写最右，覆盖索引不写*；

• 不等空值还有OR，索引影响要注意；

• varchar引号不可丢， SQL优化有诀窍。