mysql left join limit_针对left join以及limit的两条优化小技巧

记两则亲身经历的sql优化技巧：

一、主表数据不到100万，以下查询结果集约200左右，第一条sql执行效率为40ms，第二条为200ms，使用上面方法，其效率明显优于left join：

sql1:

SELECT SQL_NO_CACHE

usersr_id

,businessunit_id

,ifnull((SELECT name FROM sync_businessunit WHERE id= obj.businessunit_id),'无名' ) businessunit_name

,ifnull((SELECT fullname FROM sync_usersys WHERE id= obj.usersr_id),'无名' ) sr_name

,SUM(price) price

,IFNULL((SELECT target FROM analysis_target WHERE year=2017 and month=4 and usersr_id = obj.usersr_id ),0) target

FROM analysis_cusorderobj

WHERE send_time >= '2017-02-01' AND send_time < '2017-03-01' #BETWEEN '2017-02-01' and '2017-02-28'

GROUP BY usersr_id

ORDER BY usersr_id;

sql2:

SELECT SQL_NO_CACHE

o.usersr_id,

o.businessunit_id,

u.`name` as businessunit_name,

us.fullname,

t.target as zongzhibiao,

SUM(o.price) as zongdacheng

FROM analysis_cusorder o

LEFT JOIN analysis_target t ON o.usersr_id = t.usersr_id and t.`year`=2017 and t.`month` = 4

LEFT JOIN sync_businessunit u ON o.businessunit_id = u.id

LEFT JOIN sync_usersys us ON o.usersr_id = us.id

WHERE o.send_time BETWEEN '2017-02-01' and '2017-02-28' GROUP BY o.usersr_id;

二、为针对limit的优化，一般表数据超过1000万，limit基本就废了，需采用sql1的方法进行优化，效率相关极为明显，以下语句为使用php框架后的写法：

sql1:

$query = $this->db->select('id,third_id,recommend_menus')->where("id > $maxid and recommend_menus != ''")->order_by("id asc")->limit($perpage)->get('crawler_merchant');

sql2:

$query = $this->db->select('id,third_id,recommend_menus')->limit($perpage, $offset)->get('crawler_merchant');