当前位置:网站首页>[go to big factory series] two optimization tips for left join and limit

[go to big factory series] two optimization tips for left join and limit

2021-10-14 06:10:56 Old fellow iron has dried up the code.

Remember two personal experiences sql Optimization techniques :

One 、 The data in the main table cannot be found 100 ten thousand , The following query results are intensive 200 about , Article 1 with a sql The efficiency of execution is 40ms, The second is 200ms, Use the above method , Its efficiency is obviously better than left join:

sql1:

SELECT SQL_NO_CACHE 
	usersr_id 
	,businessunit_id
	,ifnull((SELECT name FROM sync_businessunit WHERE id= obj.businessunit_id),' unknown ' ) businessunit_name
	,ifnull((SELECT fullname FROM sync_usersys WHERE id= obj.usersr_id),' unknown ' ) 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_cusorder	obj
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;

Two 、 For limit The optimization of the , General table data exceeds 1000 ten thousand ,limit Basically abandoned , To be used sql1 The method is optimized , The efficiency correlation is very obvious , The following statement is to use php Writing after frame :

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');

版权声明
本文为[Old fellow iron has dried up the code.]所创,转载请带上原文链接,感谢
https://chowdera.com/2021/10/20211002145845295f.html

随机推荐