MySQL如何建立索引_建立索引 mysql

单表

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       |
+----+-----------+-------------+-------+----------+-------+---------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

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

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

查看索引

show index from article;
1

发现只有一个主键索引

下面开始优化

针对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;
1
2
3
4
5

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;
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;
1
2
3
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)));
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

下面开始explain分析

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

结论type有all

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

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

alter table book add index Y(card);
1

之后我把这个索引删除掉

drop index Y on book;
1

之后加在card表上

alter table class add index Y(card);
1

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

结论：左连接的时候左边全有，加索引加到右表上。这是由左连接特性决定的。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)));
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

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

EXPLAIN SELECT
    *
FROM
    class
LEFT JOIN book ON class.card = book.card
LEFT JOIN phone ON book.card = phone.card;
1
2
3
4
5
6

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

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

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

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

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

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

优先优化NestedLoop的内层循环。

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

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