MySQL优化策略

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);

策略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’;

image-20210103162616652

策略2 最佳左前缀法则

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

image-20210103162838915

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

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

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

image-20210103163001636

策略4 范围条件放最后

存储引擎不能使用索引中范围条件右边的列。

image-20210103163056611

策略5 覆盖索引尽量用

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

image-20210103163142048

策略6 不等于要甚用

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

image-20210103163230379

策略7 Null/Not有影响

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

image-20210103163323591

image-20210103163334062

image-20210103163348367

image-20210103163401574

策略8 Like查询要当心

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

image-20210103163546781

策略9 字符类型加引号

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

image-20210103163628719

策略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’;

image-20210103163755352

测试题（假设index(a,b,c)）：

Where语句 索引是否被使用
where a = 3 Y,使用到a
where a = 3 and b = 5 Y,使用到a，b
where a = 3 and b = 5 and c = 4 Y,使用到a,b,c
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4 N
where a = 3 and c = 5 使用到a， 但是c不可以，b中间断了
where a = 3 and b > 4 and c = 5 使用到a和b， c不能用在范围之后，b断了
where a = 3 and b like ‘kk%’ and c = 4 Y,使用到a,b,c
where a = 3 and b like ‘%kk’ and c = 4 Y,只用到a
where a = 3 and b like ‘%kk%’ and c = 4 Y,只用到a
where a = 3 and b like ‘k%kk%’ and c = 4 Y,使用到a,b,c

葵花宝典

  • 全职匹配我最爱，最左前缀要遵守；
  • 带头大哥不能死，中间兄弟不能断；
  • 索引列上少计算，范围之后全失效；
  • LIKE百分写最右，覆盖索引不写*；
  • 不等空值还有OR，索引影响要注意；
  • VAR引号不可丢， SQL优化有诀窍。