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 ;

这里写图片描述

结论：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;

这里写图片描述

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;

这里写图片描述

改完之后发现没有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;

这里写图片描述

结论:可以看到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;

这里写图片描述

结论type有all

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

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

alter table book add index Y(card);

这里写图片描述

之后我把这个索引删除掉

drop index Y on book;

之后加在card表上

alter table class add index Y(card);

这里写图片描述

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

结论：左连接的时候左边全有，加索引加到右表上。这是由左连接特性决定的。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;

这里写图片描述

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

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

这里写图片描述

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

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

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

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

优先优化NestedLoop的内层循环。

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

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

MySQL如何建立索引

两表

三表

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 MySQL索引优化：为何建立、如何维护

相关 MySQL建立索引

相关 MySQL如何建立索引

相关 MYSQL 如何建立索引及如何使用索引

相关如何为mysql建立索引

相关 mysql 长字符串如何建立索引前缀索引

相关【mysql】索引、建立索引

相关搜索引擎索引之如何建立索引

相关 MySQL如何建立索引

相关 MySQL如何建立索引

随便看看

Apache Kafka消息格式的演变(0.7.x~0.10.x)

Windows微信刷朋友圈

4种方案详解如何实现准时的setTimeout

el-backtop简单实现功能

轻量级流程编排引擎-模型&设计

linux启动时有文件错误,解决由于文件系统错误而出现 Linux VM 启动 - Virtual Machines | Microsoft Docs...

教程文章

热评文章

1江湖小白之一起学Python （二）爬取数据的保存

2Java Shiro：简化身份验证和授权的安全框架

3Java中try()catch{}的使用方法

4Swagger注解-@ApiModel 和 @ApiModelProperty

5windows下强制杀死tomcat进程

6uni-app 条形码(一维码)/二维码生成实现

标签列表