索引单表、双表、三表优化

一、索引单表优化

1.建表、插入、查询数据

先建立一张表

我们在里面插入数据

先不建立索引，当我们使用以下语句进行查询时，可以先分析一下性能

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

可以看到，type 的类型是 null，进行了全表扫描，同时 extra 还使用了 using filesort，因此效率更加低

2.使用索引

我们对要查询的几个字段，category_id、comments 和 views 建立索引

create index index_ccv on article(category_id, comments, views);

然后我们再次测试之前的语句，并进行性能分析

可以看到，第一个虽然 key 中表明已经用到了索引，但是在 extra 中还是出现了 using filesort，说明 mysql 自己建立了一个排序，并没有使用索引的排序。说明，因为使用 >，导致索引失效了

这是因为， comments > 1 的结果是一个范围值，mysql 无法利用索引对后面的 views 部分进行检索，即对于 view 字段，索引已经失效了

但是第二个语句，因为没有使用 >，范围更加精确，因此也用上了索引，此时 extra 中没有 using filesort

3.优化索引

如果我们直接跳过 comments，对 category_id 和 views 建立索引，此时虽然范围变小了，但是精度却提升了

create index index_cv on article(category_id, views); 

还是使用上面的语句进行性能优化

可以看到，type 变成了 ref，ref 变成了 const，同时 extra 没有了 using filesort，可见性能大大提升

二、索引双表优化

1.建表，插入数据

建立两张表，class 和 book 表，其中通过 card 字段进行关联

然后插入数据

先不建立索引，使用左外连接，然后进行性能分析，看看结果如何

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

可以看到，两个表的 type 都是 all，说明进行了全表扫描。效率比较低

2.使用索引

不像单表建立索引，双表因为是利用两个表进行连接查询，那么，问题就来了，到底在连接查询的左表建立索引，还是在右表建立索引？

①

对于上面的例子，我们在先在右表建立索引

create index index_r_b on book(card);

在使用上面的查询语句进行测试

可以看到，对右表(book 表)建立索引然后再次查询，发现 type 变成了 ref，rows 也变小了，说明索引起作用了，且比较明显

这是因为，left join 是先对左表(class)进行查询，然后用右表(book)在左表中进行搜索，所以，右表是关键点，必须建立索引

如果此时将左表和右表交换位置，看到 type 变回了 All

此时左表变成了 book，右表变成了 class，即会先查询 book，然后用右表进行匹配，重点变成了 class 表，因为此时的索引只有左表有，因此查询没有被优化。可见左连接如果对右表建立索引，并不能优化查询

所以我们得出了一个结论：左连接中对右表建立索引可以优化查询

②

我们在对右连接进测试，首先对左表建立索引

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

此时左表是 class，右表是 book

对左表(class)建立索引

alter table class add index index_l_c(card);

还是用上面的查询语句进行测试

可以看到，对左表建立索引，整个查询语句也进行了优化。原理和左外连接一致

我们试着将两张表的位置进行调换

可以看到，查询语句并没有被优化，因此，右连接中，对右表建立索引不能优化查询

得出结论，在右脸杰中对左表建立索引可以优化查询

三、索引三表优化

首先建立一个 phone 表，用来和前面的两个表进行联合查询

我们使用以下的语句对 class、book 和 phone 表进行三表联合查询

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

结果是

接下来，我们对左连接右边的两张表(book,phone)建立索引，然后使用 explain 来分析

-- 对 book 表的 card 字段建立索引 --
alter table book add index index_book_r(card);

-- 对 phone 表的 card 字段建立索引 --
alter table phone add index index_phone_r(card);

之后再用 explain 指令对三表联合查询的语句进行性能分析

通过标记，无论是 ref，rows 还是 extra，都看出建立的索引对联合查询进行了优化

四、总结

1. 对于单表查询，一定要根据 where 后面的字段建立索引，遇到有 <、>、!= 这样的关系运算符，会使已经建完的索引失效
2. 对于双表查询，左外连接只会因为右表的索引而被优化，右外连接只会因为左表的索引而被优化，内连接则因为两个表的索引都可以被优化
3. 对于三表查询，则根据是左连接对右边的连接建立索引，根据右连接对左边的连接建立索引