0%

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

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

索引分析

案例一 单表分析

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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;

mark

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

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

mark

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

1
2
3
4
5
6
7
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)

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

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

create index idx_article_ccv on article(category_id, comments, views);

建好索引后

mark

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

mark

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

mark

很显然,这是两个常量字段都是const,也就不需要文件内排序,但是这样是属于更改题目。所以这也说明了一个问题,就是范围后的索引会失效,也就是把索引放到一个范围后面,这个索引成了失效索引!所以尽量给定具体值,不要给范围。

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

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

好了,接下来开始重新建立索引:

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

create index idx_article_cv on article(category_id, views);

mark

mark

案例二 两表分析

如下两张表,分别插入20条数据:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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分析:

mark

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

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

mark

mark

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

mark

mark

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

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

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

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

案例三 三表分析

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

1
2
3
4
5
6
7
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));
1
explain select * from class left join book on class.card = book.card left join phone on book.card = phone.card;

mark

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

mark

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

mark

JOIN语句的优化:

尽可能减少JOIN语句中的NestedLoop的循环总次数:”永远用小结果集驱动大的结果集”

优先优化NestedLoop的循环的内层

保证JOIN语句中被驱动表上Join条件字段已经被索引

当无法保证被驱动表的JOIN条件字段被索引且内存资源充足的前提下,不要太吝惜 JoinBuffer的设置

索引失效

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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、全值匹配我最爱

1
2
3
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';

mark

2、最佳左前缀法则

但是请看下面这种情况

mark

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

那么这样呢?

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

mark

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

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

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

mark

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

mark

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

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

mark

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

5、尽量使用覆盖索引

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

mark

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

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

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

mark

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

mark

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

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

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

mark

通过上面的例子可以看出,like的%尽量加在右边。而且like查询是一个范围查询!

解决like’%字符串%’索引不被使用的方法??看看下面的示例:

1
2
3
4
5
6
7
8
9
10
11
12
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');

mark

1
2
3
create index idx_user_nameAge on tbl_user(name, age);

show index from tbl_user;

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

mark

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

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

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

mark

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

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

mark

优化总结

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

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

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

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

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

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

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

  • 本文作者: Tim
  • 本文链接: https://zouchanglin.cn/1649611076.html
  • 版权声明: 本博客所有文章除特别声明外,均采用 BY-NC-SA 许可协议。转载请注明出处!