（1）使用索引

（1.1）建表sql（索引失效测试）

CREATE TABLE staffs (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR (24)  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,'manager',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());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES(null,23,'dev',NOW());
SELECT * FROM staffs;
 
ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name, age, pos);

（1.2）索引失效的案例

（1.2.1）全值匹配我最爱

索引 idx_staffs_nameAgePos 建立索引时 以 name ， age ，pos 的顺序建立的。全值匹配表示按顺序匹配的

• 如下图所示，先找name，再找age，最后找pos
  
• 执行下面的explain，看看索引会不会失效

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

• 执行结果
  可以看到key_len越来越长，意味着精度越来越高，当然消耗也越来越大。ref由1个变2个，再变3个

（1.2.2）大头大哥不能死，中间兄弟不能断：最佳左前缀原则

• （1）什么是最左前缀原则
  如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列 and 忽略左右关系。既即使没有没有按顺序 由于优化器的存在，会自动优化。
  经过试验结论 建立了 idx_nameAge 索引 id 为主键
  1.当使用覆盖索引的方式时，(select name/age/id from staffs where age=10 (后面没有其他没有索引的字段条件))，即使不是以 name 开头，也会使用 idx_nameAge 索引。
  既 select 后的字段 有索引，where 后的字段也有索引，则无关执行顺序。
  2.除开上述条件 才满足最左前缀法则。

• （2）执行下面explain，看看会不会失效

EXPLAIN SELECT * FROM staffs WHERE age = 25 AND pos = 'dev';
 
EXPLAIN SELECT * FROM staffs WHERE pos = 'dev';

EXPLAIN SELECT * FROM staffs WHERE name='July' AND pos = 'dev';

• （3）执行结果
  带头大哥不能死
  带头大哥不能死
  中间兄弟不能断
  
• （4）结论（1-单独有火车头可以跑，没有火车头不能跑；2-带头大哥不能死，中间兄弟不能断，中间一断后面跑散）
  多个列创建的索引，name+age+pose，如果没有最左的name列，那么索引就会失效。所以如果索引了多列，就要遵守最佳左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列。

（1.2.3）索引列上不计算：不再索引列上做任何操作（计算、函数、自动或者手动的类型转换），会导致索引失效而转向全表扫描

• 执行下面的explain，看看索引会不会失效

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

• 执行结果
  

（1.2.4）范围之后全失效：存储引擎不能使用索引中范围条件右边的列

范围若有索引则能使用到索引，范围条件右边的索引会失效(范围条件右边与范围条件使用的同一个组合索引，右边的才会失效。若是不同索引则不会失效)

• 执行效果
  
• 结论
  如果age用了范围查找<>，那么后面的pos索引就会失效

（1.2.5）尽量使用覆盖索引（值访问索引的查询（索引列和查询列一致））。减少select *

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

索引：idx_nameAgeJob，idx_name
使用 != 和 <> 的字段索引失效( != 针对数值类型。 <> 针对字符类型
前提 where and 后的字段在混合索引中的位置比比当前字段靠后 where age != 10 and name=‘xxx’ ,这种情况下，mysql自动优化，将 name=‘xxx’ 放在 age ！=10 之前，name 依然能使用索引。只是 age 的索引失效)

（1.2.7）is not null 也无法使用索引，但是is null 是可以使用索引的

（1.2.8）百分like加右边：like 以通配符开头（’%abc…’）mysql索引失效会变成全表扫描的操作

• 注意
  like ‘%abc%’ type 类型会变成 all
  like ‘abc%’ type 类型为 range ，算是范围，可以使用索引
• 执行效果
  
• 问题：如果必须左边加%，怎么解决like '%字符串%'时索引不被使用的方法

CREATE TABLE `tbl_user` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `NAME` VARCHAR(20) DEFAULT NULL,
 `age` INT(11) DEFAULT NULL,
 email VARCHAR(20) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 
#drop table tbl_user
 
INSERT INTO tbl_user(NAME,age,email) VALUES('1aa1',21,'b@163.com');
INSERT INTO tbl_user(NAME,age,email) VALUES('2aa2',222,'a@163.com');
INSERT INTO tbl_user(NAME,age,email) VALUES('3aa3',265,'c@163.com');
INSERT INTO tbl_user(NAME,age,email) VALUES('4aa4',21,'d@163.com');
INSERT INTO tbl_user(NAME,age,email) VALUES('aa',121,'e@163.com');
 
#before index
 
EXPLAIN SELECT NAME,age    FROM tbl_user WHERE NAME LIKE '%aa%';
 
EXPLAIN SELECT id    FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT NAME     FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT age   FROM tbl_user WHERE NAME LIKE '%aa%';
 
EXPLAIN SELECT id,NAME    FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT id,NAME,age FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT NAME,age FROM tbl_user WHERE NAME LIKE '%aa%';
 
EXPLAIN SELECT *     FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT id,NAME,age,email  FROM tbl_user WHERE NAME LIKE '%aa%';
 
#create index
CREATE INDEX idx_user_nameAge ON tbl_user(NAME,age);
 
#DROP INDEX idx_user_nameAge ON tbl_user
 
#after index
 
EXPLAIN SELECT * FROM tbl_user WHERE NAME =800 AND age = 33;

（1.2.9）字符串里有引号：字符串不加单引号索引失效

如果不加单引号，也可以正常查询，但是索引会失效。
底层进行转换使索引失效，使用了函数造成索引失效。

（1.2.10）少用or：连接时会导致索引失效

（1.2.11）小总结

假设复合索引：index(a,b,c)

Where语句	索引是否被使用
where a = 3	Yes，使用到a
where a = 3 and b = 5	Yes，使用到a，b
where a = 3 and b = 5 and c = 4	Yes，使用到a,b,c
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4	No
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	Yes，使用到a,b,c
where a = 3 and b like ‘%kk’ and c = 4	Yes，只用到a
where a = 3 and b like ‘%kk%’ and c = 4	Yes，只用到a
where a = 3 and b like ‘k%kk%’ and c = 4	Yes，使用到a,b,c

（1.3）面试题分析：实例case

（1）建表语句

create table test03(
 id int primary key not null auto_increment,
 c1 char(10),
 c2 char(10),
 c3 char(10),
 c4 char(10),
 c5 char(10)
);
 
insert into test03(c1,c2,c3,c4,c5) values('a1','a2','a3','a4','a5');
insert into test03(c1,c2,c3,c4,c5) values('b1','b2','b3','b4','b5');
insert into test03(c1,c2,c3,c4,c5) values('c1','c2','c3','c4','c5');
insert into test03(c1,c2,c3,c4,c5) values('d1','d2','d3','d4','d5');
insert into test03(c1,c2,c3,c4,c5) values('e1','e2','e3','e4','e5');
 
select * from test03;

（2）建索引

create index idx_test03_c1234 on test03(c1,c2,c3,c4);
show index from test03;

（3）问题：我们创建了复合索引idx_test03_c1234 ,根据以下SQL分析下索引使用情况？

explain select * from test03 where c1='a1';
explain select * from test03 where c1='a1' and c2='a2';
explain select * from test03 where c1='a1' and c2='a2' and c3='a3';
explain select * from test03 where c1='a1' and c2='a2' and c3='a3' and c4='a4';
 
 
1）
 explain select * from test03 where c1='a1' and c2='a2' and c3='a3' and c4='a4'; 
2） 
 explain select * from test03 where c1='a1' and c2='a2' and c4='a4' and c3='a3'; 
3） 
 explain select * from test03 where c1='a1' and c2='a2' and c3>'a3' and c4='a4';
4） 
 explain select * from test03 where c1='a1' and c2='a2' and c4>'a4' and c3='a3';
5） 
 explain select * from test03 where c1='a1' and c2='a2' and c4='a4' order by c3;
 c3作用在排序而不是查找
6） 
 explain select * from test03 where c1='a1' and c2='a2' order by c3;
7） 
 explain select * from test03 where c1='a1' and c2='a2' order by c4; 
出现了filesort
8） 
8.1
 explain select * from test03 where c1='a1' and c5='a5' order by c2,c3; 
 
 只用c1一个字段索引，但是c2、c3用于排序,无filesort
8.2
 explain select * from test03 where c1='a1' and c5='a5' order by c3,c2;
 
 出现了filesort，我们建的索引是1234，它没有按照顺序来，3 2 颠倒了
9） 
 explain select * from test03 where c1='a1' and c2='a2' order by c2,c3;
10）
 explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c2,c3;       
 用c1、c2两个字段索引，但是c2、c3用于排序,无filesort
 
 explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c3,c2;             
 
 本例有常量c2的情况，和8.2对比
 
 explain select * from test03 where c1='a1' and c5='a5' order by c3,c2;                                filesort
11）
 explain select * from test03 where c1='a1' and c4='a4' group by c2,c3;
12）
 explain select * from test03 where c1='a1' and c4='a4' group by c3,c2;
 
 Using where; Using temporary; Using filesort

（4）一般性建议
1-对于单键索引，尽量选择针对当前query过滤性更好的索引
2-在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。（避免索引过滤性好的索引失效）
3-在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引
4-尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的