Mysql之排序分组后取第一条 慢SQL分析

1, 遇到的场景
需要先根据创建时间倒排, 再根据业务id分组, 取每个分组的第一条数据

2.第一个坑
mysql 5.7之后, group by 和 order by 后的字段不一样, order by 不生效
解决办法 在order by 后 加limit 破坏mysql的自动优化条件

3.慢sql

SELECT
	id,
	c_id,
	feature_log_key,
	feature_log_value,
	feature_log_year
FROM
	cs_customer_feature_log
WHERE
	id IN (
	SELECT
		MAX( t.id ) AS id 
	FROM
		(
		SELECT
			id AS id,
			c_id AS memberId 
		FROM
			cs_customer_feature_log 
		WHERE
			del_flag = 0 
			AND c_id IN ( '1468760830240878592', '1468760823018287104' ) 
		ORDER BY
			id DESC 
			LIMIT 1000000 
		) AS t 
	GROUP BY
	t.memberId 
	)

乍一看没什么问题, 但是看执行计划, 会发现最外面一层的select没有走索引, 而是全表扫描, 这样随着数据量的增大, 必将成为一个慢sql

4.解决办法
需要将上面的sql 拆成两个sql, 虽然会多链接一次数据库, 但是可以避免慢sql

-- 先将id查出来
SELECT
	MAX( t.id ) AS id 
FROM
	(
	SELECT
		id AS id,
		c_id AS memberId 
	FROM
		cs_customer_feature_log 
	WHERE
		del_flag = 0 
		AND c_id IN ( '1468760830240878592', '1468760823018287104' ) 
	ORDER BY
		id DESC 
		LIMIT 1000000 
	) AS t 
GROUP BY
	t.memberId;
	
-- 再利用主键索引 快速检索需要数据
SELECT
	id,
	c_id,
	feature_log_key,
	feature_log_value,
	feature_log_year 
FROM
	cs_customer_feature_log 
WHERE
	id IN ( 798346, 798343 );

效果是立竿见影,看执行计划就能看出区别