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;
从图中可以看出：type类型是ALL，并且出现了Using filesort（内排序的字段） 。ALL说明是全表扫描，这在一定程度上降低了速度，所以需要进行优化。
3.进行建立索引的操作
- 建立索引的原则就是给where后面的字段都创建成索引。
create index idx_article_ccv on article(category_id,comments,views);
可以看出：虽然由ALL变成了range，但是还是没有解决Using filesort（内排序）的问题，这是因为comments在进行查找的时候系统自己进行了排序，因为他不是常量导致了索引失效。
- 进行再一次的优化
create index idx_article_cv on article(category_id,views);
这次就解决了所有问题，而且达到了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;
可以看到type类型还是ALL
- 进行优化操作
alter table book ADD INDEX Y (card);
book作为右表加上优化之后，达到了ref水准。
删除索引：
drop index Y on book;
alter table class ADD INDEX Y (card);
结论：左连接加在右表，右连接相反。
这是由于左连接的特性决定的。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;
join buffer 表示使用了缓存
- 进行优化操作
alter table phone ADD index z(card);
alter table book ADD index y(card);
结论：尽可能减少join语句中的NestedLoop（嵌套循环）的循环总次数：‘永远用小的结果集驱动大的结果集’。
优先优化嵌套循环中的内层循环；
保证join语句中被驱动表上的join条件字段已经被索引。
当无法保证被驱动表的join条件字段被索引且内存充足的前提下，不要太吝啬JoinBuffer的设置。
转载：https://blog.csdn.net/weixin_43271086/article/details/105396715