单/多表索引分析

索引分析

单表索引优化分析

查询category_id为1且comments大于1的情况下，views最多的arcticle_id

未建立索引前

type：ALL对全表进行了扫描，效率最低

possible_keys和key都为null，没有使用索引

rows为3，表示找到所需记录所要读取的行数

Extra有Using filesort未使用内部索引，需要优化

由于用到了category_id,comments,views三个列，先尝试对这三个列建立复合索引

create index idx_article_ccv on article(category_id,comments,views)

再次查找

type:range变成了建索给定范围的行，效率提高

possible_key：理论上用到了ccv索引，实际上用到了ccv索引

ref:NULL用到了索引的哪一行？null？索引失效

Extra:Using filesort没有用到索引，索引失效

为什么？明明建了索引，key也实际用到了索引，索引还会失效？

因为按照BTree索引的工作原理，先排序categort_id,如果遇到相同的category_id则再排序comments，如果遇到相同的comments则再排序views，当comments字段在复合索引中间时，因comments>1条件是一个范围值（所谓range），MySQL无法利用索引再对后面的views部分进行建索，即range类型查询字段后面的索引无效

删除刚才三个字段的索引，再对字段category_id和views建立索引

drop index idx_article_ccv on article
create index idx_article_cv on article(category_id,views)

type：ref 非唯一性索引扫描，返回匹配某个单独值的所有行,进一步优化

使用到了idx_article_cv索引

ref：索引使用到的列为常量const

Extra中没有了Using filesort

索引优化完成

两表索引优化分析

EXPLAIN SELECT * from class left join book on class.card = book.card

通过sql语句可知，class表和book表都有card字段，那么索引应该建在class表的card字段还是book表的card字段？

未建立索引前

type都是ALL，说明两表都是全表扫描，没有引用一个索引，

rows都是20，都查询了所有行,显然是一种效率十分低下的查询

先尝试给book表的card字段添加索引

这是在左连接的右边字段添加索引，可以看出，book表使用了索引，type类型变成了ref类型（非唯一性索引扫描，返回匹配某个单独值的所有行），ref为db0629数据库的class表的card字段（显示索引哪一列被使用了）,Extra变成了Using index，效率较好的覆盖索引类型

删掉上面的索引再给class表的card字段添加索引

type为index和ALL，rows两个都为20，查找效率并没有上一个好

说明了什么？

给左连接的右边字段添加索引效果要好于给左连接的左边添加索引

为什么会这样？

这是因为左连接会把左边的表当成主表，主表中所有内容都会保存下来，最终结果中右表与左表不匹配的行会变为NULL，右表的数量要少于坐标，这样对右表建立索引就会减少索引长度，提高索引效率

同理，给右连接的左表字段添加索引要好于右连接的右表字段的索引

两表都加索引性能几乎没变，只不过其中一个ALL变成了index

三表索引优化分析

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

未建立索引前

type为ALL,rows都是20，使用了连接缓存(Using join buffer)

对book和phone表的card字段添加索引

book表和phone表的type都是ref类型，且rows变成了20,1,1查询效率提升

因此索引最好设置在需要经常查询的字段中

结论：

• 尽可能减少join语句中NestedLoop的循环总次数；小表驱动大表
• 优先优化NestedLoop的内层循环
• 保证join语句中被驱动的表上的join字段都是索引字段，
• 当无法保证被驱动表的join条件字段被索引且内存充足的情况下，可以把JoinBuffer的容量调高一些(JoinBuffer在my.ini中配置)