MySQL高级知识点 | 七、SQL优化

SQL优化

优化策略

一、尽量全值匹配

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

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 '入职时间'
    )charset 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);

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'

二、最佳左前缀法则

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

EXPLAIN SELECT * FROM staffs WHERE age = 25 AND pos = 'dev'
EXPLAIN SELECT * FROM staffs WHERE pos = 'dev'
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' 
#前两个SQL语句由于跳过了NAME索引所以，所以会引起索引失效

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

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

四、范围条件放到最后

中间有范围查询会导致后面的索引列全部失效

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

五、尽量用覆盖索引

尽量使用覆盖索引即只访问索引的查询（索引列和查询列一致），减少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'

六、不等于要慎用

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

解决方法：使用覆盖索引

七、字段的NULL和NOT NULL

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

EXPLAIN select * from staffs where name is null

EXPLAIN select * from staffs where name is not null

当字段可以为null时，使用 is not null 会导致索引失效

解决方法：使用覆盖索引

八、Like查询要当心

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

解决方式：覆盖索引

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

九、字符类型加引号

字符类型一定要加引号

十、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'