MySQL如何建立索引

た入场券 2022-05-28 07:18 223阅读 0赞

# 单表 #

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'), (1,1,1,2,'4','4');
    
    mysql> select * from article;
    +----+-----------+-------------+-------+----------+-------+---------+
    | id | author_id | category_id | views | comments | title | content |
    +----+-----------+-------------+-------+----------+-------+---------+
    |  1 |         1 |           1 |     1 |        1 | 1     | 1       |
    |  2 |         2 |           2 |     2 |        2 | 2     | 2       |
    |  3 |         1 |           1 |     3 |        3 | 3     | 3       |
    |  4 |         1 |           1 |     1 |        2 | 4     | 4       |
    +----+-----------+-------------+-------+----------+-------+---------+

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

![这里写图片描述][70]

结论：type是all，即最坏的情况，extra里边还出现了using filesort，也是最坏的情况，优化是必须的。

查看索引

show index from article;

发现只有一个主键索引

下面开始优化

针对category\_id，comment，views建立一个复合索引

alter table article add index idx_article_ccv(category_id,comments,views);
    或者
    create index idx_article_ccv on article(category_id,comments,views);
    
    drop index idx_article_ccv on article;

![这里写图片描述][70 1]

![这里写图片描述][70 2]

type的值变成range了，通过key看到用了索引。但还是有using filesort。

type变成了range，这是可以接受的，但是extra里使用using filesort仍是无法接受的。

但是我们已经建立的索引，为啥没用呢？

这是因为按照BTree索引的工作原理，先排序category\_id,如果遇到相同的category\_id则再排序comments，如果遇到相同的comments则再排序views。当comments字段在联合索引里处于中间位置时，因comments>1条件是一个范围值（所谓range），MySQL无法利用索引再对后面的views部分进行检索，即range类型查询字段后面的索引无效。

因为这里comments的条件为>1会让索引失效。那我可不可以绕过comments建立索引，而建立category\_id和views的索引呢。

这里我先把comments>1改成=1

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

![这里写图片描述][70 3]

改完之后发现没有using filesort了，但是不符合需求了。这里注意，以后工作的时候可以和产品经理商量看需求可不可以这样弄，性能会更好。

当前这个索引解决了全表扫描的问题，但还有using filesort的问题，我需要更改索引。

我删除之前的索引,然后创建category\_id和views的索引，之后再执行SQL语句

drop index idx_article_ccv on article;
    create index idx_article_ccv on article(category_id,views);
    show index from article;
    explain select id,author_id from article where category_id=1 and comments>1 order by views desc limit 1;

![这里写图片描述][70 4]

结论:可以看到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( 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 class(card) values(floor(1+(rand()*20)));
    insert into class(card) values(floor(1+(rand()*20)));
    insert into class(card) values(floor(1+(rand()*20)));
    insert into class(card) values(floor(1+(rand()*20)));
    insert into class(card) values(floor(1+(rand()*20)));
    insert into class(card) values(floor(1+(rand()*20)));
    insert into class(card) values(floor(1+(rand()*20)));
    insert into class(card) values(floor(1+(rand()*20)));
    insert into class(card) values(floor(1+(rand()*20)));
    insert into class(card) values(floor(1+(rand()*20)));
    insert into class(card) values(floor(1+(rand()*20)));
    insert into class(card) values(floor(1+(rand()*20)));
    insert into class(card) values(floor(1+(rand()*20)));
    insert into class(card) values(floor(1+(rand()*20)));
    insert into class(card) values(floor(1+(rand()*20)));
    insert into class(card) values(floor(1+(rand()*20)));
    insert into class(card) values(floor(1+(rand()*20)));
    insert into class(card) values(floor(1+(rand()*20)));
    insert into class(card) values(floor(1+(rand()*20)));
    
    insert into book(card) values(floor(1+(rand()*20)));
    insert into book(card) values(floor(1+(rand()*20)));
    insert into book(card) values(floor(1+(rand()*20)));
    insert into book(card) values(floor(1+(rand()*20)));
    insert into book(card) values(floor(1+(rand()*20)));
    insert into book(card) values(floor(1+(rand()*20)));
    insert into book(card) values(floor(1+(rand()*20)));
    insert into book(card) values(floor(1+(rand()*20)));
    insert into book(card) values(floor(1+(rand()*20)));
    insert into book(card) values(floor(1+(rand()*20)));
    insert into book(card) values(floor(1+(rand()*20)));
    insert into book(card) values(floor(1+(rand()*20)));
    insert into book(card) values(floor(1+(rand()*20)));
    insert into book(card) values(floor(1+(rand()*20)));
    insert into book(card) values(floor(1+(rand()*20)));
    insert into book(card) values(floor(1+(rand()*20)));
    insert into book(card) values(floor(1+(rand()*20)));
    insert into book(card) values(floor(1+(rand()*20)));
    insert into book(card) values(floor(1+(rand()*20)));
    insert into book(card) values(floor(1+(rand()*20)));

下面开始explain分析

explain SELECT * from class LEFT JOIN book on class.card=book.card;

![这里写图片描述][70 5]

结论type有all

我索引应该建立到左表还是右表呢，不知道，那怎么办？都试一遍呗

先在book表上添加索引，这里book表是右表，左连接加在右表上

alter table book add index Y(card);

![这里写图片描述][70 6]

之后我把这个索引删除掉

drop index Y on book;

之后加在card表上

alter table class add index Y(card);

![这里写图片描述][70 7]

这次把索引加到左表上，但效果没有加在右表上好。

**结论：左连接的时候左边全有，加索引加到右表上。这是由左连接特性决定的。left join条件用于确定如何从右表搜索行，左边一定都有，所以右边是我们的关键点，一定需要建立索引**

DBA建立索引的时候是为全局考虑的，不可能因为个人而更改索引。

但我们可以自己更换一下表的位置啊，以前在左边的表放到右边。

右连接的话，右边一定都有，所以左边是我们的关键点，一定需要建立索引。

# 三表 #

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)));
    insert into phone(card) values(floor(1+(rand()*20)));
    insert into phone(card) values(floor(1+(rand()*20)));
    insert into phone(card) values(floor(1+(rand()*20)));
    insert into phone(card) values(floor(1+(rand()*20)));
    insert into phone(card) values(floor(1+(rand()*20)));
    insert into phone(card) values(floor(1+(rand()*20)));
    insert into phone(card) values(floor(1+(rand()*20)));
    insert into phone(card) values(floor(1+(rand()*20)));
    insert into phone(card) values(floor(1+(rand()*20)));
    insert into phone(card) values(floor(1+(rand()*20)));
    insert into phone(card) values(floor(1+(rand()*20)));
    insert into phone(card) values(floor(1+(rand()*20)));
    insert into phone(card) values(floor(1+(rand()*20)));
    insert into phone(card) values(floor(1+(rand()*20)));
    insert into phone(card) values(floor(1+(rand()*20)));
    insert into phone(card) values(floor(1+(rand()*20)));
    insert into phone(card) values(floor(1+(rand()*20)));
    insert into phone(card) values(floor(1+(rand()*20)));
    insert into phone(card) values(floor(1+(rand()*20)));

把之前建立的索引都删除掉，现在看class，book，phone这三张表。

EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card;

![这里写图片描述][70 8]

之后我在book和phone上建立索引

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

![这里写图片描述][70 9]

**尽可能减少Join语句中的NestedLoop（嵌套循环）的循环总次数；“永远用小结果集驱动大的结果集”**

书的数目肯定比书的类别要多，所以SQL语句要这样写

SELECT * from class LEFT JOIN book on class.card=book.card;

如果用左连接的话，把类别表放到左边，因为类别少，左连接的左表肯定会全扫描，把数据少的放到左边。永远是小结果集驱动大的结果集

**优先优化NestedLoop的内层循环。**

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

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

[70]: /images/20220528/e4634f93440b4a6da4b1871b10387228.png
[70 1]: /images/20220528/667d1ed8a0b44f2cb12c61bccc3a84dd.png
[70 2]: /images/20220528/24d2e89838424819b03f534c282ebb0b.png
[70 3]: /images/20220528/e8c6293b64e84b4fa8fa9f96c06a9010.png
[70 4]: /images/20220528/2259cf03fee947a0adb71e0df95074f3.png
[70 5]: /images/20220528/124d70a6f7314bc89b28e95a436e247e.png
[70 6]: /images/20220528/2c50aa7f5c4b421e9d94df30c9df42f6.png
[70 7]: /images/20220528/8684d61233c04d32803585714e96cc64.png
[70 8]: /images/20220528/2893f6309f1e49608814ef4bf61e6606.png
[70 9]: /images/20220528/592836b28b4144cbae634b9973c9269e.png