文章目录
索引的使用规则
建表
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) values('z3',22,'manager');
insert into staffs(name,age,pos) values('July',23,'dev');
insert into staffs(name,age,pos) values('2000',23,'dev');
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=23;
explain select * from staffs where name='july' and age=23 and pos='dev';
最左前缀法则
如果索引了多列,要遵守最左前缀法则。指查询从索引的最左列开始并且不跳过索引中的列
explain select * from staffs where age=23 and pos='dev';
explain select * from staffs where pos='dev';
不在索引列上做任何操作
不在索引列上做任何操作(计算、函数、(自动或手动)类型转换),会导致索引失效而转向全表扫描
explain select * from staffs where left(name,4)='july';
存储引擎不能使用索引中范围条件右边的列
explain select * from staffs where name='july' and age=23 and pos='dev';
explain select * from staffs where name='july' and age>20 and pos='dev';
使用覆盖索引
尽量使用覆盖索引(索引列和查询列一致),减少select *
使用不等于(!= 或者<>)时 索引失效
explain select * from staffs where name='july';
explain select * from staffs where name!='july';
explain select * from staffs where name <>'july';
like以通配符开头,索引失效
explain select * from staffs where name like 'z%';
explain select * from staffs where name like '%z%';
explain select * from staffs where name like 'z%3%';
如果业务中必须要使用到like %xx%的情况,不能使用索引,怎么办呢?
这个时候可以尝试使用到覆盖索引
explain select name,age from staffs where name like '%z%';
字符串不加单引号索引失效
## name 是varchar类型,这里2000会隐式类型转为字符
explain select * from staffs where name=2000;
少用or,用它连接是 索引会失效
explain select * from staffs where name='2000' or age =23;
使用索引时的建议
-
对于单键索引,尽量选择针对当前query过滤性(离散性好)的类做索引
-
在选择组合索引的时候,当前query中过滤性最好的字段放在索引字段的顺序中,位置越靠前越好。
-
在选择组合索引时,尽量选择可以能包含当前查询中的where子句更多字段的索引(全值索引)
-
尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。