如何避免 MySQL 索引失效与三个优化实例

索引分析

案例一 单表分析

假设这样一张表，下面是建表语句

CREATE TABLE `article` (
  `id` int(10) NOT NULL,
  `author_id` int(10) NOT NULL,
  `category_id` int(10) NOT NULL,
  `views` int(10) NOT NULL,
  `comments` int(10) NOT NULL,
  `title` varchar(255) NOT NULL,
  `content` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of article
-- ----------------------------
BEGIN;
INSERT INTO `article` VALUES (1, 1, 1, 1, 1, '1', '1');
INSERT INTO `article` VALUES (2, 2, 2, 2, 2, '2', '2');
INSERT INTO `article` VALUES (3, 1, 1, 3, 3, '3', '3');
COMMIT;

查询 category_id 为 1 且 comments 大于 1 的情况下，views 最多的 article_id：

explain select id, author_id from article where category_id = 1 and comments > 1 order by views desc limit 1;

很显然，type 是 ALL，即最坏的情况。Exta 里还出现了 Using filesort 也是最坏的情况。优化是必须的，先来看看这张表的索引：

mysql> show index from article;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| article |          0 | PRIMARY  |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

很显然，只有主键索引。那么接下来开始建索引吧，有两种方式：

alter table article add index idx_article_ccv(category_id, comments, views);

create 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，也就不需要文件内排序，但是这样是属于更改题目。所以这也说明了一个问题，就是范围后的索引会失效，也就是把索引放到一个范围后面，这个索引成了失效索引！所以尽量给定具体值，不要给范围。

所以如果非要在给定范围内查找，这个索引是不合适的，我们需要重建索引，先把之前的索引删除：

drop 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 类型查询字段后面的索引无效。

好了，接下来开始重新建立索引：

alter table article add index idx_article_cv(category_id, views);

create index idx_article_cv on article(category_id, views);

案例二 两表分析

如下两张表，分别插入 20 条数据：

create table if not exists class(
    id int(10) unsigned not null auto_increment,
    card int(10) unsigned not null,
    primary key (id)
);

create table if not exists book(
    bookid int(10) unsigned not null auto_increment,
    card int(10) unsigned not null,
    primary key (bookid)
);

insert into class(card)values (FLOOR(1 + RAND() * 20));
insert into book(card)values (FLOOR(1 + RAND() * 20));

下面开始 explain 分析：

两个 type 都是 ALL，那么究竟是在 class 加索引还是 book 加索引呢？

实验一：左连接 + 索引加在右表

实验二：接下来删除 book 的 card 索引，对右表建立索引。也就是：左连接 + 索引加在左表

通过实验一和实验二的对比，可以看到 **左连接 + 索引加在右表 ** 第二行的 type 变为了 ref ，rows 也变成了优化比较明显。这是由左连接特性决定的。 left join 条件用于确定如何从右表搜索行，左表一定都有，所以右表是我们的关键点，一定需要建立索引。

所以说有时对于 DBA 建好的索引也无需修改，我们需要修改 SQL 语句中表的位置即可！比如对于上述例子：

select * from book right join class on class.card = book.card;

同样的道理， right join 条件用于确定如何从左表搜索行，右表一定都有，所以左表是我们的关键点，一定需要建立索引。

案例三 三表分析

还是依照上面的两张表，再新建一张表，并插入 20 条数据：

create table if not exists phone(
    phoneid int(10) unsigned not null auto_increment,
    card int(10) unsigned not null,
    primary key (phoneid)
);

insert into phone(card)values (FLOOR(1 + RAND() * 20));

explain 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 的设置

索引失效

先建示例表与该表的索引：

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, '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());
SELECT * FROM staffs;

alter table staffs add index idx_staffs_nameAgePos(name, age, pos);

show 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、全值匹配我最爱

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

2、最佳左前缀法则

但是请看下面这种情况

很明显建的索引没用到，但是如果只是根据 name 字段来查询却又可以用到索引。如果索引了多列，要遵守最佳左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。否则会引起索引失效！这也是最常用的法则！很形象的一个例子：带头大哥不能死，name 索引相当于火车头，age、pos 是车厢，所以没有 name 火车肯定动不了，但是如果只有火车头，那么也是能动的，单独的车厢不能运动而已！

那么这样呢？

explain 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'% 字符串 %'索引不被使用的方法？？看看下面的示例：

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;

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

create index idx_user_nameAge on tbl_user(name, age);

show index from tbl_user;

通过建立覆盖索引来避免全表扫描，从下图可以看出类型为 index：

所以说：like 百分写右边，复合索引解决两边都是百分号的问题！需要时建立了索引，查询的字段是建立了索引的字段，那就没问题。如果包含了其他字段，就会造成索引失效！

9、字符串不加单引号索引失效

varchar 类型必须加单引号，下面的示例可以说明问题：

这是为什么呢？因为 MySQL 自动识别 name 是一个 varchar 类型，所以如果没有在 SQL 语句中没加单引号，MySQL 会自动发生隐式类型转换，所以参考第三条，这必然导致索引失效，在开中应该尽量避免

10、少用 or，用 or 来连接时索引失效

优化总结

全值匹配我最爱，最左前缀要遵守；

带头大哥不能死，中间兄弟不能断；

索引列上少计算，范围之后全失效；

like 百分写最右，覆盖素引不写星；

不等空值还有 or，索引失效要少用；

varchar 引号不可丢，SQL 高级也不难！

打油诗解读：通俗来讲，长话短说，最好是查找的值都是建立索引的字段，要遵守最佳左前缀匹配法则，第一个索引没用上其他的都用不上，中间的索引没用上后面的也用不上。索引字段不要函数计算、自动或手动的类型转换。凡是在范围条件之后的索引全部失效，like 的百分号写在最后边，实在是需要两边都是百分号那么建立索引，并且别查找其他非索引字段，也就是尽量别写 select * 。尽量不使用不等于、大于、小于等条件，尽量不要使用 or 进行连接，否则会导致索引失效。对于 varchar 类型的字段不要忘记写引号，避免发生隐式类型转换。