mysql区分度过低

最新推荐文章于 2024-10-13 17:08:52 发布

ZYP起飞

最新推荐文章于 2024-10-13 17:08:52 发布

阅读量585

点赞数

文章标签：数据库 sql

本文链接：https://blog.csdn.net/WATXZDN/article/details/126604695

版权

今天运维说官网加载过慢，然后我看了一下，有个接口响应竟然达到5s，之后去用explan查看，发现因为价格好多一样的，然后导致区分度过低，也就是索引失效了

----原sql----
SELECT
	od.order_id AS orderId,
	s.signal_no AS signalNo,
	s.full_name AS fullName,
	s.picture_seq AS pictureSeq,
	om.eval_method AS evalMethod,
	oi.collection_stamp_id AS collectionStampId 
FROM
	digitization.`order` od
	JOIN digitization.order_main om ON od.order_main_id = om.order_main_id
	JOIN digitization.order_item oi ON od.order_id = oi.order_id
	JOIN appraisal.stamp s ON oi.stamp_id = s.stamp_id
	JOIN appraisal.evaluation_order eo ON eo.order_id = od.order_id 
WHERE
	od.order_status = 6 
	AND om.eval_method != 2 
	AND eo.evaluation_result = '真品/优良' 
GROUP BY
	od.order_id 
ORDER BY
	s.refer_price DESC,
	od.order_id DESC 
	LIMIT 9

解决办法，改用子查询，先把符合条件的查询出来，在符合条件的结果里进行排序

----修改之后的sql，采用子查询----
SELECT
	s1.*,
	s.signal_no AS signalNo,
	s.full_name AS fullName,
	s.picture_seq AS pictureSeq 
FROM
	(
	SELECT
		od.order_id AS orderId,
		om.eval_method AS evalMethod,
		oi.stamp_id stamp_id,
		oi.collection_stamp_id AS collectionStampId 
	FROM
		digitization.order_main om
		JOIN digitization.`order` od ON od.order_main_id = om.order_main_id
		JOIN digitization.order_item oi ON od.order_id = oi.order_id 
	WHERE
		od.order_status = 6 
		AND om.eval_method != 2 
	GROUP BY
		od.order_id 
	ORDER BY
		od.order_id DESC 
	) s1
	JOIN appraisal.stamp s ON s1.stamp_id = s.stamp_id
	JOIN appraisal.evaluation_order eo ON eo.order_id = s1.orderId 
WHERE
	eo.evaluation_result = '真品/优良' 
GROUP BY
	s1.orderId 
ORDER BY
	s.refer_price DESC,
	s1.orderId DESC 
	LIMIT 9