2021-06-21解决列表查询很慢的优化SQL定位查询慢原因优化

解决问题如下

主要是耗时查询很久的问题！

1.查询定位下SQL是不是有慢查询：

EXPLAIN  SELECT
	p.id,
	p.news_id,
	parent_news_id,
	title,
	app_ids,
	news_url,
	media_source,
	media_source_code,
	ds_source_type,
	ds_source_name,
	ds_news_columns,
	publish_status,
	publish_date,
	publish_user,
	handle_status,
	handle_user,
	handle_time,
	audit_user,
	audit_time,
	src_publish_date,
	remark,
	`level`,
	author,
	trading_market,
	range_base,
	range_plus,
	financial,
	financial_plus,
	form,
	author_org_code,
	update_time,
	create_time,
	author_org,
	layout_info,
	layout_number,
	del_reason,
	b.model_total_score_news,
	b.model_total_score_yq
FROM
	tbm_news_processing p
LEFT JOIN (
	SELECT
		id,
		model_total_score_news,
		model_total_score_yq
	FROM
		tbm_news_factor_score
	WHERE
		1 = 1
	AND create_time BETWEEN '2021-06-21 00:00:00'
	AND '2021-06-21 23:59:59'
) b ON p.news_id = b.id
INNER JOIN (
	SELECT
		register_tag_id,
		news_id
	FROM
		tbm_news_tags
	WHERE
		register_tag_id IN ('101111529683333872')
	GROUP BY
		news_id
) t1 ON t1.news_id = p.news_id
WHERE
	1 = 1
AND app_ids LIKE CONCAT('%' ,5, '%')
AND handle_status IN (0)
AND create_time BETWEEN '2021-06-21 00:00:00'
AND '2021-06-21 23:59:59'
AND news_type IN ('1')
ORDER BY
	create_time DESC
LIMIT 0,10 ;
   

SQL单独查询：

受影响的行: 0
时间: 0.150s  还算是面前凑乎，

主要得优化技巧：

1、大表嵌套小表，最好都限定下范围，控制粒度越小越好！

2、关联表的对接字段最好是添加索引，那样就能够最好的达到预期效果！