目录
3、8mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描
1、前提准备
首先我们创建一个测试的员工表
create table employees
(
id int auto_increment
primary key,
name varchar(20) null,
dep_id int null,
age int null,
salary decimal(10, 2) null,
cus_id int null
);
其次我们创建一个复合索引
create index idx_name_age_sal on employees(name,age,salary);
效果图:
2、1全值匹配(最好)
- 使用到了1个
explain select * from employees where name = '李白';
- 使用到了2个
explain select * from employees where name = '李白1' and age = 10;
- 使用用到了3个
explain select * from employees where name = '李白1' and age = 10 and salary = 1000;
3、最佳左前缀法则
3、1要求
如果索引的多列,要遵守最左前缀法则,指的就是从索引的最左列开始 并且不跳过索引中的列,如果左边的值未确定,那么无法使用此索引。
注:判断哪个索引生效,查看key_len的具体值来确定
3、2跳过第一个,索引失效
explain
select * from employees where age = 10 and salary = 1000;
3、3跳过前两个, 索引失效
explain
select * from employees where salary = 1000;
3、4跳过中间一个 ,只有第一个生效
explain
select * from employees where name = '李白1' and salary = 1000;
3、5顺序可以乱
explain
select * from employees where name = '李白1' and salary = 1000 and age = 10;
3、6不在索引列上做任何操作
计算,函数,类型转换,会导致索引失效而转向全表扫描
explain
select * from employees where trim(name) = '李白1' and age = 10 and salary = 1000;
3、7范围条件右边的索引失效
explain
select * from employees where name = '李白1' and age > 10 and salary = 1000;
3、8mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描
注:前提是欲查询的where条件没有与之相匹配的
explain
select * from employees where name != '李白';
3、9 is not null 无法使用索引
explain
select * from employees where age is not null ;
3、10少用or 用or连接时, 会导致索引失效
explain
select * from employees where name = '李白1' or age >10;
3、11like以通配符开头(%qw)索引失效变成全表扫描
explain
select * from employees where name like '%李';
3、12字符串不加引号索引失效
explain
select * from employees where name = 200;
3、13尽量使用覆盖索引
explain
select * from employees where name = '李白1' and age = 10 and salary = 1000;