如何避免MySQL索引失效与三个优化实例
最好是查找的值都是建立索引的字段，要遵守最佳左前缀匹配法则，第一个索引没用上其他的都用不上，中间的索引没用上后面的也用不上。索引字段不要函数计算、自动或手动的类型转换。凡是在范围条件之后的索引全部失效，like的百分号写在最后边，实在是需要两边都是百分号那么建立索引，并且别查找其他非索引字段，也就是尽量别写select * 。尽量不使用不等于、大于、小于等条件，尽量不要使用or进行连接，否则会导致索引失效。对于varchar类型的字段不要忘记写引号，避免发生隐式类型转换。
索引分析
案例一 单表分析
假设这样一张表，下面是建表语句
1CREATE TABLE `article` (
2 `id` int(10) NOT NULL,
3 `author_id` int(10) NOT NULL,
4 `category_id` int(10) NOT NULL,
5 `views` int(10) NOT NULL,
6 `comments` int(10) NOT NULL,
7 `title` varchar(255) NOT NULL,
8 `content` text NOT NULL,
9 PRIMARY KEY (`id`)
10) ENGINE=InnoDB DEFAULT CHARSET=utf8;
11
12-- ----------------------------
13-- Records of article
14-- ----------------------------
15BEGIN;
16INSERT INTO `article` VALUES (1, 1, 1, 1, 1, '1', '1');
17INSERT INTO `article` VALUES (2, 2, 2, 2, 2, '2', '2');
18INSERT INTO `article` VALUES (3, 1, 1, 3, 3, '3', '3');
19COMMIT;
查询 category_id为1且 comments大于1的情况下，views最多的 article_id：
1explain select id, author_id from article where category_id = 1 and comments > 1 order by views desc limit 1;
很显然，type是ALL，即最坏的情况。Exta里还出现了 Using filesort 也是最坏的情况。优化是必须的，先来看看这张表的索引：
1mysql> show index from article;
2+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
4+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5| article | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | |
6+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
71 row in set (0.00 sec)
很显然，只有主键索引。那么接下来开始建索引吧，有两种方式：
1alter table article add index idx_article_ccv(category_id, comments, views);
2
3create index idx_article_ccv on article(category_id, comments, views);
建好索引后
关于Using index condition https://blog.csdn.net/z69183787/article/details/53393153 。 建立索引后，也可以明显看出来用到了索引，避免了全表扫描，但是还是存在文件排序：
这是为什么呢？原因是我们SQL语句中的>1， >1是个范围，如果改成=1呢？
很显然，这是两个常量字段都是const，也就不需要文件内排序，但是这样是属于更改题目。所以这也说明了一个问题，就是范围后的索引会失效，也就是把索引放到一个范围后面，这个索引成了失效索引！所以尽量给定具体值，不要给范围。
所以如果非要在给定范围内查找，这个索引是不合适的，我们需要重建索引，先把之前的索引删除：
1drop index idx_article_ccv on article;
当我们建立索引的后，type变成了 range，这是可以忍受的。但是 extra里使用 Using files仍是无法接受的
但是我们已经建立了索引，为啥没用呢？这是因为按照 BTree索引的工作原理，先排序 category_id，如果遇到相同的 category_id则再排序 comments，如果遇到相同的 comments则再排序views。当 comments字段在联合索引里处于中间位置时，因 comments > 1条件是一个范围值(所谓 range)，MySQL无法利用索引再对后面的views部分进行检索，即 range类型查询字段后面的索引无效。
好了，接下来开始重新建立索引：
1alter table article add index idx_article_cv(category_id, views);
2
3create index idx_article_cv on article(category_id, views);
案例二 两表分析
如下两张表，分别插入20条数据：
1create table if not exists class(
2 id int(10) unsigned not null auto_increment,
3 card int(10) unsigned not null,
4 primary key (id)
5);
6
7create table if not exists book(
8 bookid int(10) unsigned not null auto_increment,
9 card int(10) unsigned not null,
10 primary key (bookid)
11);
12
13insert into class(card)values (FLOOR(1 + RAND() * 20));
14insert into book(card)values (FLOOR(1 + RAND() * 20));
下面开始explain分析：
两个type都是ALL，那么究竟是在class加索引还是book加索引呢？
实验一：左连接 + 索引加在右表
实验二：接下来删除book的card索引，对右表建立索引。也就是：左连接 + 索引加在左表
通过实验一和实验二的对比，可以看到 **左连接 + 索引加在右表 ** 第二行的type变为了 ref ，rows也变成了优化比较明显。这是由左连接特性决定的。 left join 条件用于确定如何从右表搜索行，左表一定都有，所以右表是我们的关键点，一定需要建立索引。
所以说有时对于DBA建好的索引也无需修改，我们需要修改SQL语句中表的位置即可！比如对于上述例子：
1select * from book right join class on class.card = book.card;
同样的道理， right join 条件用于确定如何从左表搜索行，右表一定都有，所以左表是我们的关键点，一定需要建立索引。
案例三 三表分析
还是依照上面的两张表，再新建一张表，并插入20条数据：
1create table if not exists phone(
2 phoneid int(10) unsigned not null auto_increment,
3 card int(10) unsigned not null,
4 primary key (phoneid)
5);
6
7insert into phone(card)values (FLOOR(1 + RAND() * 20));
1explain select * from class left join book on class.card = book.card left join phone on book.card = phone.card;
现在对phone和book表的card字段建立索引：
后2行的type都是ref且总rows优化很好效果不错。因此索引最好设置在需要经常查询的字段中。
JOIN语句的优化：
尽可能减少JOIN语句中的NestedLoop的循环总次数：“永远用小结果集驱动大的结果集”
优先优化NestedLoop的循环的内层
保证JOIN语句中被驱动表上Join条件字段已经被索引
当无法保证被驱动表的JOIN条件字段被索引且内存资源充足的前提下，不要太吝惜 JoinBuffer的设置
索引失效
先建示例表与该表的索引：
1create table staffs(
2 id int primary key auto_increment,
3 name varchar(24) not null default '' comment '姓名',
4 age int not null default 0 comment '年龄',
5 pos varchar(20) not null default '' comment '职位',
6 add_time timestamp not null default current_timestamp comment '入职时间'
7)charset utf8 comment '员工记录表';
8
9INSERT INTO staffs(NAME, age, pos, add_time)VALUES('z3', 22, 'manager', NOW());
10INSERT INTO staffs(NAME, age, pos, add_time) VALUES('July', 23, 'dev', NOW());
11INSERT INTO staffs(NAME, age, pos, add_time) VALUES('2000', 23, 'dev', NOW());
12SELECT * FROM staffs;
13
14alter table staffs add index idx_staffs_nameAgePos(name, age, pos);
15
16show index from staffs;
1、全值匹配我最爱
2、最佳左前缀法则
3、不在索引列上做任何操作(计算、函数、(自动or手动)类型转换)，会导致索引失效而转向全表扫描
4、存储引擎不能使用索引中范围条件右边的列
5、尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)，减少 select
6、mysql在使用不等于(=或者<>)的时候无法使用索引会导致全表扫描
7、is null, is not null也无法使用索引
8、like以通配符开头('%abc…')mysq索引失效会变成全表扫描的操作
9、字符串不加单引号索引失效
10、少用or，用它来连接时会索引失效
1、全值匹配我最爱
1explain select * from staffs where name = 'July';
2explain select * from staffs where name = 'July' and age = 23;
3explain select * from staffs where name = 'July' and age = 23 and pos = 'dev';
2、最佳左前缀法则
但是请看下面这种情况
很明显建的索引没用到，但是如果只是根据name字段来查询却又可以用到索引。如果索引了多列，要遵守最佳左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。否则会引起索引失效！这也是最常用的法则！很形象的一个例子：带头大哥不能死，name索引相当于火车头，age、pos是车厢，所以没有name火车肯定动不了，但是如果只有火车头，那么也是能动的，单独的车厢不能运动而已！
那么这样呢？
1explain select * from staffs where name = 'July' and pos = 'dev';
上面这条SQL很显然用到了索引，但是key_len还是74没变，其实这条SQL只用到了name索引，并未用到pos索引。这违反了最佳左前缀法则，因为age索引还没用到就用pos，肯定pos会无效的。拿上面的火车举例子，如果中间的车厢断开了，后面的车厢肯定也动不了了。所以最佳左前缀法则还是很重要的！
3、不在索引列上做任何操作
不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
接下来分别看看他们的执行计划：
很明显，在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描！所以这一点是非常需要注意的！
4、存储引擎不能使用索引中范围条件右边的列
如上图所示，索引一旦出现范围条件，那么后面的索引会失效。出现范围的索引呢也是会用到，比如这里的age字段排序，但是级别从ref降到了range级别，在数据量非常大的情况下还是很损伤性能的！
5、尽量使用覆盖索引
尽量使用覆盖索引（只访问索引的查询，即索引列和查询列一致），减少使用select*
可以看出如果只是要检索字段，那就尽量明确写出需要查询的字段，不要写select * ，只查询索引字段的话就会使用Using index，而不是Using where。
6、在使用不等于的时候无法使用索引
mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描
7、is null、is not null 也无法使用索引
关键字段尽量避免null值，最好设置默认值！
8、like以通配符开头索引失效会变成全表扫描
like以通配符开头（'$abc…'）mysql索引失效会变成全表扫描操作
通过上面的例子可以看出，like的%尽量加在右边。而且like查询是一个范围查询！
解决like'%字符串%‘索引不被使用的方法？？看看下面的示例：
1create table tbl_user(
2 id int(11) not null auto_increment,
3 name varchar(20) default null,
4 age int(11) default null,
5 email varchar(20) default null,
6 primary key(id)
7)engine=INNODB auto_increment=1 default charset=utf8;
8
9insert into tbl_user (name, age, email) values ('1aa1', 21, 'b@163.com');
10insert into tbl_user (name, age, email) values ('2aa2', 222, 'a@163.com');
11insert into tbl_user (name, age, email) values ('3aa3', 265, 'c@163.com');
12insert into tbl_user (name, age, email) values ('4aa4', 21, 'd@163.com');
通过建立覆盖索引来避免全表扫描，从下图可以看出类型为index：
所以说：like百分写右边，复合索引解决两边都是百分号的问题！需要时建立了索引，查询的字段是建立了索引的字段，那就没问题。如果包含了其他字段，就会造成索引失效！
9、字符串不加单引号索引失效
varchar类型必须加单引号，下面的示例可以说明问题：
这是为什么呢？因为MySQL自动识别name是一个varchar类型，所以如果没有在SQL语句中没加单引号，MySQL会自动发生隐式类型转换，所以参考第三条，这必然导致索引失效，在开中应该尽量避免
10、少用or，用or来连接时索引失效
优化总结
全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
like 百分写最右，覆盖素引不写星；
不等空值还有or，索引失效要少用；
varchar引号不可丢，SQL高级也不难！
打油诗解读：通俗来讲，长话短说，最好是查找的值都是建立索引的字段，要遵守最佳左前缀匹配法则，第一个索引没用上其他的都用不上，中间的索引没用上后面的也用不上。索引字段不要函数计算、自动或手动的类型转换。凡是在范围条件之后的索引全部失效，like的百分号写在最后边，实在是需要两边都是百分号那么建立索引，并且别查找其他非索引字段，也就是尽量别写select * 。尽量不使用不等于、大于、小于等条件，尽量不要使用or进行连接，否则会导致索引失效。对于varchar类型的字段不要忘记写引号，避免发生隐式类型转换。