Sweet Candy

I will in life and deth

0%

Mysql进阶--索引两表优化

发表于 更新于 分类于 Valine：
本文字数： 3.6k 阅读时长 ≈ 3 分钟

单表优化已经完成，这里再进行两表优化，单表优化有所困惑的请参看上一篇文章:

Mysql进阶-索引单表优化

1.为了展示两张表的索引优化，再创建一张class表和book表，并插入部分数据，执行语句，

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

image-20201113133541475

image-20201113133557936

2.两张表数据已经没问题，业务需求：连表查询，条件为class.card = book.card，左表class表是驱动表，执行语句，

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

image-20201113133907035

3.照例对语句进行Explain分析，不错所料，type为ALL，也就是查询了每一条数据，rows为表中数据总行数，说明class和book进行了全表检索，也就是说，每一次class表对book表进行左外连接的时候，都会在book表中进行一次全表检索，

1
Explain SELECT * FROM class LEFT JOIN book ON class.card = book.card;

image-20201113134329715

4.给book表的card字段添加索引优化，

1
ALTER TABLE book ADD INDEX Y (card);

image-20201113135110795

5.再使用Explain分析，book表的type变为了ref

image-20201113135151469

6.再试试加class表，同样加card字段，先删除开始的book表的索引，再新建class表索引，

1
2
drop index Y on book;
ALTER TABLE class ADD INDEX X(card);

7.再次分析，注意，这次是class表的索引，查看两种情况，对sql执行的影响，可以看到class表的type变为index，并且rows也再一次达到了20+21=41，也就是两表都进行了全表检索，

注意两种情况，class表左连接book表，

当card索引加在book右表，type为ref；

当card索引加在class左表，type为index，别忘记了type好坏的比较：system>const>eq_ref>ref>range>index>ALL，

分析其原因：

  • 这是由左连接特性决定的。LEFT JOIN条件用于确定如何从右表搜索行，左边一定都有，所以右边是我们的关键点，一定需要在右表建立索引。
  • 左表连接右表，则需要拿着左表的数据去右表里面查，索引需要在右表中建立索引

image-20201113140155923

在这种情况下，也许索引就很不适合，但是DBA表示这索引也是为其他地方服务，不能修改删除，那么就可以试试右连接，可以看到class表变为了ref，并且rows总数也变为了21，

1
Explain SELECT * FROM class right JOIN book ON class.card = book.card;

image-20201113141636914

分析其原因：

  • 这是因为RIGHT JOIN条件用于确定如何从左表搜索行，右边一定都有，所以左边是我们的关键点，一定需要建立索引。
  • class RIGHT JOIN book ：book 里面的数据一定存在于结果集中，我们需要拿着 book 表中的数据，去 class 表中搜索，所以索引需要建立在 class 表中
----------本文结束感谢您的阅读----------