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

男娘i 2022-10-08 11:11 110阅读 0赞

解决问题如下

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE0ODgwMDk_size_16_color_FFFFFF_t_70][]

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

# 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 ;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE0ODgwMDk_size_16_color_FFFFFF_t_70 1][]SQL单独查询：

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

主要得优化技巧：

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

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE0ODgwMDk_size_16_color_FFFFFF_t_70]: /images/20221005/bab36aaeefe34a919b35cfc62d4c8f36.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE0ODgwMDk_size_16_color_FFFFFF_t_70 1]: /images/20221005/bab2fffa11884d3480596763b2ff146f.png