Mysql索引优化分析（3）｜ 8月更文挑战这是我参与8月更文挑战的第3天，活动详情查看：8月更文挑战索引优化之单表

这是我参与8月更文挑战的第3天，活动详情查看：8月更文挑战

索引优化之单表

SQL

CREATE TABLE `article` (
  `id` int(10) unsigned NOT NULL 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,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

问题：查询 category_id 为 1，且comments > 1 并且 view 最多的 article_id

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

结果：

分析：

type 是 All 即最坏的情况。Extra 是 Using filesort 也是最坏的情况。所以必须优化。

解决：

建立一个复合索引CREATE INDEX idx_article_ccv ON article(category_id,comments,views)

再次查询结果

可以发现 type 从 ALL 变为 range 了，也使用上了自己创建的索引，但是 Using filesort 还未解决，因为该查询存在一个范围查找，即range类型查询字段后面的索引无效。

改进后：

创建索引时避免把范围查找的字段加入索引，删除之前的索引DROP INDEX idx_article_ccv ON article，再重写创建索引CREATE INDEX idx_article_cv ON article(category_id,views)

执行结果：

这时再执行查询语句，就可以同时使用到检索和排序的索引了。

索引优化之双表

SQL

CREATE TABLE IF NOT EXISTS `class`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
CREATE TABLE IF NOT EXISTS `book`(
`bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
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)));
......

问题：有一个左连接查询EXPLAIN SELECT * FROM book b LEFT JOIN class c ON b.card = c.card;

没有创建任何索引的情况

创建右表索引 CREATE INDEX idx_class_card ON class(card)

结果

删除右表索引并创建左表索引 DROP INDEX idx_class_card ON class; CREATE INDEX idx_book_card ON book(card);

结果

左右表不同索引的分析

可以看出在左连接时，在右表创建索引的优势比在左表好。因为在左连接时，右表是关键的地方，左表无论如何都会全部查询，而条件是针对右表的，所以把索引建立在右表比较好。

左连接加右表索引。

索引优化之三表

SQL，在两表基础上添加一个表

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

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

三表关联的查询

EXPLAIN SELECT
	*
FROM
	class c
LEFT JOIN book b ON c.card = b.card
LEFT JOIN phone p ON b.card = p.card;

没有索引的结果

为两个右表添加索引

CREATE INDEX idx_phone_card ON phone(card);
CREATE INDEX idx_book_card ON book(card);

结果

总结：

在使用 join 语句时，永远使用小结果驱动大的结果集。
优先优化嵌套循环的内层循环
保证 Join 语句中被驱动表上 Join 字段已被索引