一条SQL优化之路

为什么80%的码农都做不了架构师？>>>   

优化之前的SQL

SELECT ftime, biz_id, biz_name, device_active, device_total, shakeuv_active, shakeuv_total,
	FROM biz_static_portal_d
	WHERE DateDiff(20150825, ftime)<8 AND DateDiff(20150825, ftime)>0 AND biz_id=10961;

查询时间为2.6S。

可以进行下列优化

• ftime是primary key放在函数中会失效，应该将它拿出来。
• biz_id也是primary key，可以放到最前面。先查询出一部分数据。
• 建立ftime_biz_id联合索引，DBA不让建。Orz

优化后的SQL

SELECT ftime, biz_id, biz_name, device_active, device_total, shakeuv_active, shakeuv_total, 
	shakepv_active, shakepv_total, focus_active, focus_total, ticket_active, ticket_total 
	FROM biz_static_portal_d
	WHERE biz_id=10961 AND ftime>DATE_SUB(20150825,INTERVAL 8 DAY) AND ftime<20150825;

查询时间为0.43S

参考

http://blog.youkuaiyun.com/leshami/article/details/6851973

http://blog.youkuaiyun.com/xtdhqdhq/article/details/17582779

转载于:https://my.oschina.net/lvyi/blog/496914