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

本贴最后更新于 1650 天前,其中的信息可能已经天翻地覆

索引分析

案例一 单表分析

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

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;

mysql01.png

查询 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;

mysql02.png

很显然,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);

建好索引后

mysql03.png

关于 Using index condition https://blog.csdn.net/z69183787/article/details/53393153 。 建立索引后,也可以明显看出来用到了索引,避免了全表扫描,但是还是存在文件排序:

mysql04.png

这是为什么呢?原因是我们 SQL 语句中的 >1, >1 是个范围,如果改成=1 呢?

mysql05.png

很显然,这是两个常量字段都是 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);

mysql06.png

mysql07.png

案例二 两表分析

如下两张表,分别插入 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 分析:

mysql08.png

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

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

mysql10.png

mysql09.png

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

mysql11.png

mysql12.png

通过实验一和实验二的对比,可以看到 **左连接 + 索引加在右表 ** 第二行的 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;

mysql13.png

现在对 phone 和 book 表的 card 字段建立索引:

mysql14.png

后 2 行的 type 都是 ref 且总 rows 优化很好效果不错。因此索引最好设置在需要经常查询的字段中。

mysql15.png

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

mysql16.png

2、最佳左前缀法则

但是请看下面这种情况

mysql17.png

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

那么这样呢?

explain select * from staffs where name = 'July' and pos = 'dev';

mysql18.png

上面这条 SQL 很显然用到了索引,但是 key_len 还是 74 没变,其实这条 SQL 只用到了 name 索引,并未用到 pos 索引。这违反了最佳左前缀法则,因为 age 索引还没用到就用 pos,肯定 pos 会无效的。拿上面的火车举例子,如果中间的车厢断开了,后面的车厢肯定也动不了了。所以最佳左前缀法则还是很重要的!

3、不在索引列上做任何操作

不在索引列上做任何操作(计算、函数、(自动 or 手动)类型转换),会导致索引失效而转向全表扫描

mysql19.png

接下来分别看看他们的执行计划:

mysql20.png

很明显,在索引列上做任何操作(计算、函数、(自动 or 手动)类型转换),会导致索引失效而转向全表扫描!所以这一点是非常需要注意的!

4、存储引擎不能使用索引中范围条件右边的列

mysql21.png

如上图所示,索引一旦出现范围条件,那么后面的索引会失效。出现范围的索引呢也是会用到,比如这里的 age 字段排序,但是级别从 ref 降到了 range 级别,在数据量非常大的情况下还是很损伤性能的!

5、尽量使用覆盖索引

尽量使用覆盖索引(只访问索引的查询,即索引列和查询列一致),减少使用 select*

mysql22.png

可以看出如果只是要检索字段,那就尽量明确写出需要查询的字段,不要写 select * ,只查询索引字段的话就会使用 Using index,而不是 Using where。

6、在使用不等于的时候无法使用索引

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

mysql23.png

7、is null、is not null 也无法使用索引

mysql24.png

关键字段尽量避免 null 值,最好设置默认值!

8、like 以通配符开头索引失效会变成全表扫描

like 以通配符开头('$abc...')mysql 索引失效会变成全表扫描操作

mysql25.png

通过上面的例子可以看出,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');

mysql26.png

create index idx_user_nameAge on tbl_user(name, age);

show index from tbl_user;

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

mysql27.png

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

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

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

mysql30.png

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

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

mysql31.png

优化总结

全值匹配我最爱,最左前缀要遵守;

带头大哥不能死,中间兄弟不能断;

索引列上少计算,范围之后全失效;

like 百分写最右,覆盖素引不写星;

不等空值还有 or,索引失效要少用;

varchar 引号不可丢,SQL 高级也不难!

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

  • MySQL

    MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 公司。MySQL 是最流行的关系型数据库管理系统之一。

    673 引用 • 535 回帖
  • 性能
    62 引用 • 180 回帖

相关帖子

欢迎来到这里!

我们正在构建一个小众社区,大家在这里相互信任,以平等 • 自由 • 奔放的价值观进行分享交流。最终,希望大家能够找到与自己志同道合的伙伴,共同成长。

注册 关于
请输入回帖内容 ...