关联查询sql优化
- sql语句
-- sql语句 ,执行时间500ms
SELECT
i.id AS itemId,
i.item_name AS itemName,
i.sell_counts AS sellCounts,
ii.url AS imgUrl,
tempSpec.price_discount AS price
FROM
items i
LEFT JOIN items_img ii ON i.id = ii.item_id
LEFT JOIN ( SELECT item_id, MIN( price_discount ) AS price_discount FROM items_spec GROUP BY item_id ) tempSpec ON i.id = tempSpec.item_id
WHERE
ii.is_main = 1
AND i.item_name LIKE '%天天%'
ORDER BY
i.sell_counts DESC
LIMIT 10000;
-
explain分析
可以看出此sql语句导出都是问题,type=all,rows很大,extra=Using temporary; Using filesort,表示全表查询,并且检索行数很大,还存在临时表和发生了文件排序。
分析:可以看出id存在四条记录,最大的先执行,相等的依次执行,所以先处理id等于2的,表为items_spec的语句,sql中可以看出是子查询的语句 -
通过explain拆分分析
优化1
- 处理id=2的子查询语句,处理思路:1、这条语句是典型的group by语句,创建对应索引 2、创建index(item_id, price_discount)组合索引
SELECT item_id, MIN( price_discount ) AS price_discount FROM items_spec GROUP BY item_id
下图明显可以看出extra=Using index for group-by使用了松散索引扫描,大sql执行时间300ms
优化2
接下来是id等于1的,依次执行,就是查看表ii也就是items_img的关联表
ii表用到了is_main和item_id,创建组合索引index(is_main,item_id),执行sql,type=ref,但是row还是很高
优化3
在看i表,ii表因爱于i表,order by的i表sell_counts 排序,i的item_name是全模糊查询,sql的返回字段中i表只需要i表的id和item_name和sell_counts,由此可以创建组合索引index(sell_counts, item_name),但是当前i表中possible_keys=PRIMARY是主键关联,想要使用i表的组合索引,就需要将left join改成STRAIGHT_JOIN,强制使用i表为驱动表
使用到了组合索引,并且行数减少很多,extra=Using where; Using index,使用到了覆盖索引,执行时间100ms
优化4
上面的sql的排序方式是i.sell_counts DESC,如果更改成i.item_name DESC,时间就会多点了,差不多快200ms,只需要额外创建下组合索引index(item_name, sell_counts )
组合索引就切换了,执行时间80ms
优化5
如果排序改成tempSpec.price_discount DESC ,花费时间300ms,explain中extra 出现Using temporary; Using filesort,
就需要调大sort_buffer_size,减少临时表的创建
set sort_buffer_size = 4 * 1024 * 1024
再执行sql,花费时间150ms
优化6,反模式设计
将子查询的MIN( price_discount )引入到items表,也是引入冗余,去除子查询,想怎么排序怎么排序