索引优化案例
1. 查询条件有范围的情况,单表
此时这个表没有建索引,所以执行计划分析sql后得出的结论是,性能辣鸡,注意type是All,全表遍历,extra有using filesort,文件排序,太辣鸡了。
按照最简单的想法,那我给categroy_id,comments,views三个列加个联合索引不就行了吗?那么来试试。
可以看到索引建好了,继续用执行计划分析。
发现索引确实被使用了(key),全表遍历也避免了(type=range),但是文件排序还没有解决。
也就是说,这个复合索引虽然解决了查询问题,但是没有解决排序问题,因为这个复合索引排序时是先按category_id排序,一样时再按comments排序,再一样时按views排序,而当comments处于索引的中间位置,且又是范围筛选条件comments>1时(type=range),会令索引后面的排序失效(views的排序)。
删掉这个索引,重新创建新的索引,因为是comments字段是范围筛选条件,干脆不对它做索引,只做另外两个字段,避免失效
执行计划验证下
解决问题!
2.连接查询,两表
有两个表,书籍book和书籍分类class,其中book表有主键bookid和卡号card字段,class表有主键id和卡号card字段,book.card和class.card是关联的,
即可以 book.card join class.card这么查,大概这意思。
左连接
2个表都是全表搜索,辣鸡!
应该加索引,那么问题来了,是加在book表还是class表呢?
先加book表试试,注意book表是右表。
很明显,比两个都不加强多了,book表的type从all变成了ref,rows也从20变成了1,extra变成了覆盖索引。
删除book.card的索引,换成class.card建索引。
发现class表的type变成了index,遍历索引,比之前的all强,但是不如之前book表的ref,并且rows还是20,不如之前book的rows=1,效率不如之前的索引。
为什么会这样?因为左连接的特性是,左表全都包括,我们需要注重的是如何优化右表,因此索引建立在右表效率更高,右连接则反之,但是实际工作中遇到这种情况,通常改一下sql里表的顺序就好了,可别去改表的索引啊!
另外在join的时候,注意要小表驱动大表,即需要遍历的表一定是小表比较好,因为数据量少,效率更高。
所以在这个例子中,class类别表的数据一定比book书本表的数据少,所以查询时以class为主,同时索引建在book表上效率更高。