MySQL索引优化(一)

MySQL索引优化（案例）

索引单表优化

案例：
（1）首先建立一个article表：

往其中插入三条数据：

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

 select id,author_id from article where category_id=1 and comments>1 order by views desc limit 1;

查询结果如下：

使用explain语句来分析这条sql语句写得好不好

explain select id,author_id from article where category_id=1 and comments>1 order by views desc limit 1;

结果如下：

type为ALL：说明是全表扫描，不好
Extra显示Using filesort：说明MySQL中无法用索引来排序，所以MySQL会用外部排序——即文件内排序来排序数据并返回查询结果，这样子也不好

（3）我们来查询一下article表的索引（其实是没有的，因为建表时只是将id设为了主键，没有建立除主键索引以外的索引）

SHOW INDEX FROM article;

由图可见，索引是主键索引，下面开始优化

优化：
(1)新建索引，发现该索引不合适，删除该索引
由于上述查询语句中用到了category_id,comments,views字段作为查询条件，所以尝试将索引应该建在这三个条件上面

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

建好索引后再查询一下全部索引：

现在再来分析一下查询语句的性能

explain select id,author_id from article where category_id=1 and comments>1 order by views desc limit 1;

发现type字段下的ALL变成了range，用到了idx_article_ccv索引，但是Using filesort还有。

如果sql语句中comments>1条件改为了comments=3，那么性能分析会呈现以下情况：

可以看到，Using filesort没有了，type字段下的range变成了ref。
这就是范围查询带来的索引失效问题，因为按照BTree索引的工作原理，先排序category_id，若遇到相同category_id再排序comments，若遇到相同的comments再排序views，当comments字段在联合索引里处于中间位置时，comments>1条件是一个范围值（range），MySQL无法利用索引再对后面的views部分进行检索，即range类型查询字段后面的索引失效。这个问题会在本文后面得到解决。
现在删除idx_article_ccv索引，重新建立新的索引

（2）重建索引
绕过comments字段，在categroy_id和views字段上建索引：

CREATE INDEX idx_article_cv ON article(category_id,views);

再次使用explain来分析该查询语句的性能

explain select id,author_id from article where category_id=1 and comments>1 order by views desc limit 1;

由图可见，使用了这个索引已经将该sql语句优化到最佳效果！

索引两表优化

案例
（1）建立一个class表和一个book表
class表（id为主键自增）：

book表(bookid为主键自增)：

（2）使用explain语句进行性能分析

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

此时，type为ALL，显然需要优化
（3）对右表book表添加索引

CREATE INDEX idx_book_card ON book(card);

再用explain语句分析（2）中的查询语句，结果如下：

由此可见，性能有明显的提升
现在删去索引idx_book_card
（4）对左表class表添加索引

CREATE INDEX idx_class_card ON class(card);

再用explain语句分析（2）中的查询语句，结果如下：

与（3）中的结果对比可见，将索引建立在右表上性能好。
（5）结论
通过分析可得出结论，如果SQL语句是LEFT JOIN，则在右表建立索引；如果SQL语句是RIGHT JOIN，则在左表建立索引

索引三表优化

(1)再建立一个phone表（phoneid为主键自增），并把这三个表中的索引全部删去

（2）来用explain语句分析以下语句

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

结果为：

（3）在book表和phone表上的card字段建索引

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

继续用explain语句分析（2）中的SQL语句：

从type属性和rows属性以及Extra属性中看到，建立索引后效率提升了很多

（4）结论：
尽可能减少join语句中的Nested Loop的循环总次数，“永远用小结果集驱动大的结果集；
优化要先优化Nested Loop的内层循环；
保证Join语句中被驱动表上Join条件字段已经被索引；
当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝啬JoinBuffer的设置