一、实战准备

• 创建SQL

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,
`context` TEXT NOT NULL
)

• 添加数据

INSERT INTO
`article`(`author_id`, `category_id`, `views`, `comments`, `title`, `context`)
VALUES
(1, 1,  1,  1,  '1',    '1'),
(2, 2,  2,  2,  '2',    '2'),
(1, 1,  3,  3,  '3',    '3')

二、使用Explain分析

1、查询 category_id 为1 且 comments 大于 1 的情况下，views 最多的 article_id

EXPLAIN
SELECT 
id,author_id, views, comments
FROM
article
WHERE
category_id = 1
AND
comments > 1 
ORDER BY
views DESC
LIMIT 1

Explain分析结果.png

• type：ALL，是最坏的情况
• Extra中出现了Using filesort，也是最好的情况
• 出现上面两种状况，优化势在必行

查看索引

SHOW INDEX  FROM    article

创建索引

CREATE INDEX idx_article_ccv ON article(category_id, comments, views)

删除索引

DROP INDEX idx_article_ccv  ON  article

索引.png

使用索引再次查询 使用索引再次查询.png

• type：range，得到很大改善
• key：idx_article_ccv，索引被使用
• Extra：Using filesort依然存在

修改搜索方式

EXPLAIN
SELECT 
id,author_id, views
FROM
article
WHERE
category_id = 1
AND
comments = 3
ORDER BY
views DESC
LIMIT 1

修改搜索条件.png

• type：ref，级别更高了
• key：idx_article_ccv，使用索引
• Extra：没有Using filesort

小结

• type变为range这是可以接受的，但是Extra里使用Using filesort是无法接受的
• 但是key中是有索引的，为什么Extra里还会存在Using filesort，感觉索引没有用
• 这是因为按照BTree索引的工作原理
  • 1、先排序category_id
  • 2、如果遇到相同的 category_id 则再排序 comments，如果遇到相同的comments则再排序views
  • 3、comments字段在联合索引里处于中间位置时，因为comments > 1条件是一个范围值（range）
  • 4、MySQL无法利用索引再对后面的views部分进行检索，即range类型查询字段后面的索引无效

2、改进1出现的问题

2.1、删除第一次创建的索引

DROP INDEX idx_article_ccv  ON  article

2.2、重新创建索引

CREATE INDEX idx_article_cv ON article(category_id, views)

idx_article_cv.png

2.3、再次查询

EXPLAIN
SELECT 
id,author_id, views
FROM
article
WHERE
category_id = 1
AND
comments > 1
ORDER BY
views DESC
LIMIT 1

image.png

• type：ref
• key：idx_article_cv
• 现在的状态非常理想

3、两表实战

3.1、SQL

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

3.2、SQL添加数据

INSERT INTO class(card) VALUES(FLOOR(1+(RAND() * 20)))

3.3、Explain分析

EXPLAIN
SELECT
* 
FROM
class
LEFT JOIN
book
ON
class.card = book.card

两表查询EXPLAIN分析结果.png

3.4、给book创建索引

ALTER TABLE book ADD INDEX idx_book_card (`card`)

3.5、查看索引 book索引.png

3.6、book创建索引情况下再次执行

EXPLAIN
SELECT
* 
FROM
class
LEFT JOIN
book
ON
class.card = book.card

book加索引的情况下.png

• book表
  • type：ref
  • key：也有索引
  • rows：1，有明显优化
• class表没有任何变化

3.7、删除book索引

DROP INDEX idx_book_card    ON  book

3.8、给class添加索引

ALTER TABLE class ADD INDEX idx_class_card (`card`)

class添加索引.png

3.9、class添加索引情况下再次查询

EXPLAIN
SELECT
* 
FROM
class
LEFT JOIN
book
ON
class.card = book.card

class添加索引.png

• book表
  • type：ALL
• class表
  • type：index，有优化
  • key：使用索引
  • rows：8，没有任何优化

小结

• 只给book添加索引的情况下，可以看到type变为ref，rows也变成了1，有明显的优化
• 只给class添加索引的情况下，可以看到type变为index，rows没有明显的优化
• 出现这样的问题是由左连接特性决定的。LEFT JOIN 条件用于确定如何从右表搜索行，左边一定都有，所以右边是我们的关键点，一定需要建立索引
• 同理，如果是RIGHT JOIN 那么条件用于确定如何从左表搜索行，右边一定都有，所以左边是我们的关键点，一定需要建立索引

4、三表实战

4.1、SQL

CREATE TABLE IF NOT EXISTS `phone`(
`phoneid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY(`phoneid`)
)

4.2、添加数据

INSERT INTO phone(card) VALUES(FLOOR(1+(RAND() * 20)))

4.3、Explain分析

EXPLAIN
SELECT
* 
FROM
class
LEFT JOIN
book
ON
class.card = book.card

LEFT JOIN
phone
ON
book.card = phone.card

3表Explain分析.png

4.4、phone表添加索引

ALTER TABLE phone ADD INDEX idx_phone_card (`card`)

4.5、phone表查看索引

SHOW INDEX  FROM    phone

phone表索引.png

4.6、phone表有索引的情况下再次查询

EXPLAIN
SELECT
* 
FROM
class
LEFT JOIN
book
ON
class.card = book.card

LEFT JOIN
phone
ON
book.card = phone.card

phone表有索引的情况下查询.png

• phone表
  • type：ref，得以优化
  • key：idx_phone_card
  • rows：1，优化明显

4.7、单独给book表加索引

ALTER TABLE book ADD INDEX idx_book_card (`card`)
SHOW INDEX  FROM    book

book表加索引.png

4.8、单独book表加索引情况下搜索

EXPLAIN
SELECT
* 
FROM
class
LEFT JOIN
book
ON
class.card = book.card

LEFT JOIN
phone
ON
book.card = phone.card

单独book表加索引情况下搜索.png

• book表
  • type：ref，得以优化
  • key：idx_book_card
  • rows：1，优化明显

4.9、给book表和phone表加索引情况下搜索

EXPLAIN
SELECT
* 
FROM
class
LEFT JOIN
book
ON
class.card = book.card

LEFT JOIN
phone
ON
book.card = phone.card

phone表和book表加索引.png

• book表
  • type：ref，得以优化
  • key：idx_book_card
  • rows：1，优化明显
• phone表
  • type：ref，得以优化
  • key：idx_phone_card
  • rows：1，优化明显

4.10、给class表和book表和phone表加索引情况下搜索

EXPLAIN
SELECT
* 
FROM
class
LEFT JOIN
book
ON
class.card = book.card

LEFT JOIN
phone
ON
book.card = phone.card

三张表全都加索引.png

• class表
  • type：index，得以优化
  • key：idx_class_card
  • rows：8，没有优化
• book表
  • type：ref，得以优化
  • key：idx_book_card
  • rows：1，优化明显
• phone表
  • type：ref，得以优化
  • key：idx_phone_card
  • rows：1，优化明显

小结：join语句的优化

• 尽可能减少Join语句中的NestedLoop的循环总次数：“永远用小结果集驱动大结果集”优先优化NestedLoop的内层循环
• 保证Join语句中被驱动表上Join条件字段已经被索引