sql优化案例步骤

关联查询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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

• 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
1
2

下图明显可以看出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表，也是引入冗余，去除子查询，想怎么排序怎么排序