【MYSQL高级】索引优化

待我称王封你为后i 2021-10-03 02:04 490阅读 0赞

### 1.先来学习索引的一些语法： ###

*  展示索引
    
     *  show index from 表名;
 *  创建索引
    
     *  create index 索引名 on 表名(字段1,字段2,...);
 *  删除索引
    
     *  Drop index 索引名 on 表名;

### 2.索引在单表中的操作： ###

1.进行（单表）建表操作

create table if not exists article(
    id int(10) unsigned not null primary key AUTO_INCREMENT,
    auther_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(auther_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');

2.进行查询操作

查询 category\_id 为 1 且 comments大于1的情况下，views最多的 auther\_id。

explain select id,auther\_id from article where category\_id = 1 AND comments>1 order by views DESC LIMIT 1;

![20200408205340821.png][]

从图中可以看出：type类型是ALL，并且出现了Using filesort（内排序的字段） 。ALL说明是全表扫描，这在一定程度上降低了速度，所以需要进行优化。

3.进行建立索引的操作

*  建立索引的原则就是给where后面的字段都创建成索引。

create index idx\_article\_ccv on article(category\_id,comments,views);

![20200408205733423.png][]

可以看出：虽然由ALL变成了range，但是还是没有解决Using filesort（内排序）的问题，这是因为comments在进行查找的时候系统自己进行了排序，因为他不是常量导致了索引失效。

*  进行再一次的优化

create index idx\_article\_cv on article(category\_id,views);

![20200408210042982.png][]

这次就解决了所有问题，而且达到了ref的状态。

### 3.索引在两表中优化 ###

*  ### 建表 ###

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

*   查询book的card和class的card相等。(左连接)

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

![20200408210311675.png][]

可以看到type类型还是ALL

*  进行优化操作

alter table book ADD INDEX Y (card);

![20200408210420336.png][]

book作为右表加上优化之后，达到了ref水准。

删除索引：  
drop index Y on book;  
alter table class ADD INDEX Y (card);

![2020040821045394.png][]

结论：左连接加在右表，右连接相反。  
这是由于左连接的特性决定的。LEFT JOIN 条件用于确定如何从右表搜索行，左边一定都有。

### 4.三表联查优化 ###

*  进行建表

create table if not exists phone(
    	phoneid int(10) unsigned not null AUTO_INCREMENT,
    	card int(10) unsigned not null,
    	primary key(phoneid)
    	)engine = innodb;
    	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)));
    	沿用上面的book和class表进行操作

*  进行三表查询操作

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

![20200408210621156.png][] join buffer 表示使用了缓存

*  进行优化操作

alter table phone ADD index z(card);  
 alter table book ADD index y(card);

![20200408210728726.png][]

结论：尽可能减少join语句中的NestedLoop（嵌套循环）的循环总次数：‘永远用小的结果集驱动大的结果集’。  
        优先优化嵌套循环中的内层循环；  
        保证join语句中被驱动表上的join条件字段已经被索引。  
    当无法保证被驱动表的join条件字段被索引且内存充足的前提下，不要太吝啬JoinBuffer的设置。

[20200408205340821.png]: /images/20211003/0646bd3e1f1b4ba7a97c6d742dfe0f5d.png
[20200408205733423.png]: /images/20211003/dd659caca405479182daa9194497a01f.png
[20200408210042982.png]: /images/20211003/374da3f2c67b4dbf8f0f868f3c74c957.png
[20200408210311675.png]: /images/20211003/b9144aedf45a44b59d8e4f850844a110.png
[20200408210420336.png]: /images/20211003/a68492b6c1e0412ba836389b467d5a75.png
[2020040821045394.png]: /images/20211003/4b3dd49a09fe4bc98b30d20f18461ddc.png
[20200408210621156.png]: /images/20211003/be1f3b90bb2b4727b686c9c40a377f6e.png
[20200408210728726.png]: /images/20211003/c791201201a245b6847dc82075b7400c.png