索引优化示例详解

单表索引优化

创建一张article表

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 COMMENT '查看次数', 
`comments` INT(10) UNSIGNED NOT NULL COMMENT '备注次数',
`title` VARBINARY(255) NOT NULL,
`content` TEXT NOT NULL
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

随便插入一些数据:

INSERT INTO `article`(`author_id`, `category_id`,`views`,`comments`,`title`,`content`) VALUES(1,1,1,1,'1','AA'),(2,2,2,2,'2','BB'),(1,1,3,3,'3','CC'),(2,1,2,4,'4','CC'); 

需要查询的要求是：category_id为1且comments大于1的情况下，views最多的article_id。

这里直接给出正确的查询语句，使用explian查看SQL的执行情况。

explain select * from article where category_id=1 and comments>1 order by views desc limit 2;

我们发现这里type为all，并且出现了文件排序。这些都是需要我们优化的地方。

首先，根据查询语句条件判断涉及的字段创建索引。

#ALERT TABLE `article` ADD INDEX idx_article_ccv(`category_id`,`comments`,`views`);
create index idx_article_ccv on article(category_id, comments, views);
#查看索引
show index from article;

可以看到索引创建成功。

再次使用explain查看上面的查询语句。

此时type变成了range，但是文件排序还是存在的。这里产生文件排序的原因在于comments>1这个条件。如果改成等于1，再看看完全不一样了。当然我们不能修改查询要求，这里要知道的一点就是如果查询不是一个确定值而是范围，那么索引就会失效，即MySQL无法利用索引再对后面的views部分进行检索。所以，我们不能像上面那样创建索引，下面我继续优化。

首先删除原来的索引，再次新建索引，这次索引是关于category_id和views两个字段的索引。

DROP INDEX idx_article_ccv ON article;
#ALERT TABLE `article` ADD INDEX idx_article_cv(`category_id`,`views`);
create index idx_article_cv on article(category_id, views);
#查看索引
show index from article;

再次执行explain+SQL，输出如下信息：

可以看到，type变为了ref，Extra中的Using filesort也消失了，结果非常理想。

两表索引优化

创建两张表，一张类别表，一张书集表。

class表

CREATE TABLE IF NOT EXISTS `class`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL COMMENT '分类卡'
);

book表

CREATE TABLE IF NOT EXISTS `book`(
`bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL
);

插入数据，执行10次

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

下面使用explain来分析两边的左、右连结查询。

explain select * from class left join book on class.card = book.card;

可以看到type都为all，需添加索引优化。那么索引是添加在class表上还是添加在book表上呢？两种情况我们都尝试一下。

首先，我们为book表的card字段添加索引：

create index idx_book_card on book(`card`);

我们发现情况变好了一些。

接着，我们删除刚才建的索引，再将索引建在class表上。

DROP INDEX idx_book_card ON book;

create index idx_class_card on class(`card`);
#查看索引
show index from class;

我们很容易发现这次创建的索引没有上面的效果好。

所以，对于左连结我们应该将索引建在右表。这是由左连结特性决定的。left join条件用于确定如何从右表搜索行，左边一定都有，所以右表是我们的关键点，一定需要建立索引。对于右连结也是同样的道理。

总结：左连结索引建在右表，右连结索引建在左表。对于索引已经建立的表，我们可以灵活使用左连结还是右连结。

三表索引优化

在上面两表的基础上，创建一个phone表，这里不用关心表及字段的意义。

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

接着使用下面的语句也插入10条记录

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

三表连接的查询语句（注意怎么写）：

select * from class left join book on class.card=book.card left join phone on book.card = phone.card;

使用explain+上面的SQL得到下面的结果：

对应三表连接查询，我们可以看做两个两表查询。根据上面两表优化的例子可以知道，我们需要在book表上建立索引，然后class表和book表的join结果作为左表，那么需要在phone表上建立索引。

清除所有表上创建的索引，然后执行下面的语句创建索引。

create index idx_book_card on book(`card`);
create index idx_phone_card on phone(`card`);

可以看到后面两行type都是ref，优化效果不错。

关于join查询的一点建议：

• 尽可能减少join语句中NestedLoop的循环总次数；

• 优先优化NestedLoop的内层循环；

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

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

索引优化总结

• 不在索引列上做任何操作（计算、函数、自动或者手动类型转换），会导致索引失效而转向全表扫描。

• mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描。

• is null， is not null也无法使用索引。

• like以通配符开头（’%abc…’），mysql索引失效会变成全表扫描。

• 字符串不加单引号索引失效。

• 少于or，用它来连接时会索引失效。

优化建议：

• 对于单键索引，尽量选择针对当前query过滤性更好的索引

• 在组合索引时，尽量选择可以能够包含当前query中的where子句中更多字段的索引。当query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。