欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

MySQL索引优化(一)查询索引

程序员文章站 2022-05-03 14:41:52
...

一、单表

1、初步查询

create table if not exists article
(
    id          int(10) unsigned not null primary key auto_increment,
    author_id   int(10) unsigned not null,
    category_id int(10) unsigned not null,
    views       int(10) unsigned not null,
    comments    int(10) unsigned not null,
    title       varbinary(255)   not null,
    content     text             not null
);

insert into article(author_id, category_id, views, comments, title, content)
VALUES (1, 1, 1, 1, '1', '1'), (2, 2, 2, 2, '2', '2'), (1, 1, 3, 3, '3', '3');

执行查询

select * from article;

结果如下
MySQL索引优化(一)查询索引
现有以下要求
查询category_id为1且comments大于1的情况下,views最多的article_id。
查询语句如下

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

结果如下
MySQL索引优化(一)查询索引
sql语句加上explain,执行如下

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

MySQL索引优化(一)查询索引
很显然,type是all,即最坏的情况。Extra里还出现了Using filesort,也是最坏的情况。优化是必须的。

2、优化查询

创建索引

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

查看索引
MySQL索引优化(一)查询索引
执行之前的explain

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

结果如下
MySQL索引优化(一)查询索引
可见type变成了range,这个是可以忍受的。但是extra里使用Using filesort仍是无法接受的。

但是我们修改查询语句,把comments > 1改为comments = 1

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

结果如下
MySQL索引优化(一)查询索引
会发现,range进一步优化成为了ref,Extra中的Using filesort也不见了。不过这条sql就违背了需求。(不过也可以得出结论,一般sql以等于为主,可以达到更好的优化。)

根据BTree索引的工作原理,先排序category_id,如果遇到相同的category_id则再排序comments,如果遇到相同的comments再排序views。
当comments字段在联合索引里处于中间位置时,因comments > 1条件是一个范围值(所谓range),MySQL无法利用索引再对后面的views部分进行检索,即range类型查询字段后面的索引无效,这也是为什么还有Using filesort的原因。

3、进一步优化查询

删除之前的索引,创建新的索引

drop index idx_article_ccv on article;
create index idx_article_cv on article(category_id, views);
show index from article;

查看新创建索引
MySQL索引优化(一)查询索引
执行

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

结果如下
MySQL索引优化(一)查询索引
可以看到,type变味了ref,Extra中的Using filesort也消失了,结果非常理想。

二、双表

创建数据表

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 (
    book_id int(10) unsigned not null auto_increment,
    card int(10) unsigned not null,
    primary key (book_id)
);
# 执行20次
insert into class(card) values (floor(1 + (rand() * 20)));
# 执行20次
insert into class(book) values (floor(1 + (rand() * 20)));

1、左连接(left join)

1)初步查询

执行,class相当于book的左表。

explain select * from class left join book on class.card = book.card;

结果如下
MySQL索引优化(一)查询索引
看到了type中的all就需要优化了。

1)优化查询

删除book表索引

drop index Y on book;

给class的card添加索引(给左表添加索引)

alter table class add index Y (card);

执行

explain select * from class left join book on class.card = book.card;

结果如下
MySQL索引优化(一)查询索引
可见第一行type变成了index。

2)进一步优化查询

给book的card添加索引(给右表添加索引)

alter table book add index Y (card);

执行

explain select * from class left join book on class.card = book.card;

结果如下
MySQL索引优化(一)查询索引
可以看见第二行的type变为了ref,rows、Extra也被优化了,整体优化比较明显。

这个是由左连接特性决定的(左表全都有),left join条件用于确定如何从右表搜索行,左表一定是都有的。所以右表是关键点,一定要建立索引。

3)细节

对于给class的card添加索引优化不如给book的card添加索引优化效果明显。如何不更改索引,就能达到右表优化呢?
其实查询sql语句中两表位置互换下即可。

explain select * from book left join class on class.card = book.card;

结果如下
MySQL索引优化(一)查询索引
同样可以达到效果。

2、右连接(right join)

思路和左连接一样
right join条件用于确定如何从左表查询,右表一定都有,所以左表是我们的关键点,一定要建立索引。

三、三表

新增新表

create table if not exists phone (
    phone_id int(10) unsigned not null auto_increment,
    card int(10) unsigned not null,
    primary key (phone_id)
);
# 执行20次
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;

结果如下
MySQL索引优化(一)查询索引
给phone和book创建索引

alter table phone add index Z (card);
alter table book add index Y (card);

执行

explain select * from class left join book on class.card = book.card left join phone on book.card = phone.card;

结果如下
MySQL索引优化(一)查询索引
后两行的type都是ref且总rows优化很好,效果不错。同样的道理,这里的phone相当于book的右表,而book相当于class的右表。

四、结论

Join语句优化
1)尽可能减少Join语句中的嵌套循环总次数,永远用小结果集驱动大的结果集(例如书的类别是小结果集,书的名称是大结果集)。
2)优先优化嵌套循环的内层循环。
3)保证Join语句中被驱动表上Join条件字段已经被索引。
4)当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下,不要太吝啬JoinBuffer的设置。