利用Explain来分析和优化你的mysql

时间:2017-04-24作者:klpeng分类:数据库浏览:1911评论:0

下面我用一个例子来演示如何使用explain来优化mysql查询:


需求是这样的,在一个有1300+万条的mysql表中查出一个时间段内的数据,联表查询:

select d2.name as '大区' ,d1.name as '事业部', et_station.name as '小区',et_device.direction as '方向',count(1) as '次数'
from et_exception
join et_station on et_station.id=et_exception.station
join et_device on et_device.id=et_exception.device
join et_department as d1 on d1.id=et_station.dept
join et_department as d2 on d1.parent=d2.id
where
`et_exception`.`time`>=UNIX_TIMESTAMP('2017/04/24 00:00:00')
and
`et_exception`.`time`<UNIX_TIMESTAMP('2017/04/25 00:00:00')
group by et_exception.station,et_device.direction

这条SQL语句直接执行,查询需要9秒完成,可想而知,在实际应用中,这条sql会有非常大的隐患,我们来用explain分析下:

利用Explain来分析和优化你的mysql

可以看到,主表采用了全表扫描(type:ALL),mysql查询了13149541行记录(rows),这样的效率非常之低,业务需求不能更改的情况下,在生产环境执行可能会跑跨数据库,下面我们简单做处理:

考虑到业务需求,经常用时间来作为条件,查询数据导出报表,我们来给time字段加上索引。

再来explain分析下:

explain(select d2.name as '大区' ,d1.name as '事业部', et_station.name as '小区',et_device.direction as '方向',count(1) as '次数'
from et_exception
join et_station on et_station.id=et_exception.station
join et_device on et_device.id=et_exception.device
join et_department as d1 on d1.id=et_station.dept
join et_department as d2 on d1.parent=d2.id
where
`et_exception`.`time`>=UNIX_TIMESTAMP('2017/04/24 00:00:00')
and
`et_exception`.`time`<UNIX_TIMESTAMP('2017/04/25 00:00:00')
group by et_exception.station,et_device.direction)

下面我们看结果:

利用Explain来分析和优化你的mysql

对比上一张图我们能看到,type由ALL变成了range(范围检索),因为我们给time字段上了索引,mysql可以利用索引来检查行。rows由1300多万变成1,表示这次只用查询1行就完成了查询;

然后我们执行sql查询,整个查询只需要0.3秒完成。


当然了,这只是个例子,并不是可以适用任何业务需求的场景,explain可以帮我们分析出很多问题,大家可以去多多了解。


原创文章如转载,请注明出处,本文首发于彭超的博客

打赏
文章版权声明:除非注明,否则均为彭超的博客原创文章,转载或复制请以超链接形式并注明出处。
相关推荐

发表评论:

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。

猜你喜欢