先说下背景:
广告表ad 城市表city_area_code 按城市显示广告关系表 ad_r_area_Code(索引是ad_id 、area_code)
管理平台有个页面显示列表,如图:
之前用的一个大的SQL去写
SELECT a.id, a.title, GROUP_CONCAT(c.`name` SEPARATOR '、') AS area_code_name
FROM ad a
LEFT JOIN ad_r_area_Code b ON a.id = b.target_id AND b.target_type = 2
LEFT JOIN city_area_code c ON b.area_code = c.id
GROUP BY a.id
ORDER BY a.create_time DESC limit 20
增加“GROUP BY a.id”和“GROUP_CONCAT(c.name
SEPARATOR ‘、’) ”是要实现第二个红框的内容显示。
ad表有25万条数据,有索引的情况下执行了25秒多。通过在SQL前加 EXPLAIN
EXPLAIN SELECT a.id, a.title, GROUP_CONCAT(c.`name` SEPARATOR '、') AS area_code_name
FROM ad a
LEFT JOIN ad_r_area_Code b ON a.id = b.target_id AND b.target_type = 2
LEFT JOIN city_area_code c ON b.area_code = c.id
GROUP BY a.id
ORDER BY a.create_time DESC limit 20
结果:
每列的含义,如图:
Using temporary使用了临时表,官网指出使用group by 或者order by 会出现这个值
经尝试去掉“GROUP BY a.id”后果然没有了Using temporary值,执行时间190毫秒。
因为使用了 group by 导致的SQL执行时间过长。
那不用group by如何实现第一图的第二个红框的内容呢?
使用了 在列上加子查询
SELECT a.id, a.title,
(SELECT GROUP_CONCAT(c.`name` SEPARATOR '、')
FROM ad_r_area_Code bb
, city_area_code c WHERE bb.area_code = c.id AND a.id = bb.target_id AND bb.target_type = 2 )
FROM ad a
ORDER BY a.create_time DESC limit 20
执行时间100毫秒,但是查看explain发现 ad没有使用索引
假如数据量继续增加,执行时间也会变长,哪还能不能继续优化呢?是不是可以让表ad走索引,执行时间还会变短呢?
是的,对ORDER BY的列增加索引即可,经测验大于1列的排序是不走索引的;这是因为btree索引是排好序的;hash索引不知道了。innodb建不了hash索引