SQL优化十大法则

小咪咪 2023-07-05 10:19 36阅读 0赞

一、SQL优化—优化实战

1、尽量全值匹配

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';  
当建立了索引列后，能在wherel条件中使用索引的尽量所用。

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';  
\-- 中间有范围查询会导致后面的索引列全部失效：  
EXPLAIN SELECT \* FROM staffs WHERE NAME = 'July' and age >22 and pos='manager';

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 有影响

注意null/not null对索引的可能影响

7.1、自定定义为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;

7.2、自定义为NULL或者不定义

创建staffs2表，不设置name属性得默认值，如下所示：

alter table staffs2 add index idx\_staffs\_nameAgePos(name,age,pos);  
alter table staffs2 add index idx\_staffs\_name(name);  
执行下面查询语句：