SQL优化实战

优化实战

1、尽量全值匹配

#1、建立测试表
CREATE TABLE `staffs`( i
d 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 '入职时间' )charset utf8 comment '员工记录表'; 
#2、插入测试数据
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()); 
#3、建立name，age，pos列的复合索引
alter table staffs add index idx_staffs_nameAgePos(name,age,pos);

测试结果如下：
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

如果建立了索引列后，能在where条件中使用索引的尽量使用；

2、最佳左前缀法则

如果索引了多个列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
索引如下：
idx_staffs_nameAgePos(name,age,pos);
测试结果如下：
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、不在索引列上做任何操作

不在索引上做任何操作（计算、函数、自动或者手动类型转换），会导致索引失效转而扫描全表。
索引如下：
idx_staffs_nameAgePos(name,age,pos);
测试结果如下：
EXPLAIN SELECT * FROM staffs WHERE NAME = ‘July’;

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

4、范围条件放最后

中间有范围查询会导致范围字段后面的字段索引全部失效。
索引如下：
idx_staffs_nameAgePos(name,age,pos);
测试结果如下：
EXPLAIN SELECT * FROM staffs WHERE NAME = ‘July’ and age =22 and pos=‘manager’

EXPLAIN SELECT * FROM staffs WHERE NAME = ‘July’ and age >22 and pos=‘manager’

5、覆盖索引尽量用

尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致））。减少使用select *；
索引如下：
idx_staffs_nameAgePos(name,age,pos);
测试结果如下：
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在使用不等于（！= 或者 <>）的时候无法使用索引会导致全表扫描。
索引如下：
idx_staffs_nameAgePos(name,age,pos);
测试结果如下：
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有影响

Null、Not Null 对索引可能有影响。

（1）自定义为Null或者不定义

#新建表
CREATE TABLE `staffs2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `pos` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  `add_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (`id`),
  #新建列值索引
  KEY `idx_staffs_nameAgePos` (`name`,`age`,`pos`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

测试结果如下：
EXPLAIN select * from staffs2 where name is null；

EXPLAIN select * from staffs2 where name is not null；

（2）自定义为Not Null

#修改索引：
alter table staffs add index idx_staffxs_name(Name);
测试结果如下：
EXPLAIN select * from staffs where name is null

EXPLAIN select * from staffs where name is not null

在字段为not null的情况下，使用is null或者is not null都会导致索引失效。
解决方式：使用覆盖索引解决，尽量避免使用 null、not null

8、like查询要当心

Like以通配符开头（‘%abc…’）Mysql索引失效会变成全表扫描。
#索引如下：
idx_staffxs_name(Name);
测试结果如下：
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 from staffs where name like ‘%july%’

9、字符类型加引号

字符串不加单引号会导致索引失效。
#索引如下：
idx_staffxs_name(Name);
测试结果如下：
EXPLAIN select * from staffs where name = 917

解决方案：请加引号

10、OR改Union效率高

#索引如下：
idx_staffxs_name(Name);
测试结果如下：
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’

11、测试题

假设index（a，b，c）

第四个没有遵循最左原则
第五个使用到了a，但是c没有使用到，因为b中间断了
第六个使用到了a和b，c不能用在范围之后，b断了
第八个自用到了a，因为like是后面索引失效了
第九个和第八个一样
最后一个like索引没有失效