一、实战准备
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表
- 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条件字段已经被索引
网友评论