Mysql多张千万级数据量连表查询优化记录

1.背景

慢查询sql：

SELECT
	info.id,
	info.create_time,
	info.modify_time,
	info.prizes_info_id,
	info.prize_name,
	info.award_time,
	info.award_status,
	info.winning_time,
	info.join_time,
	info.check_status,
	info.award_type,
	info.award_type_name,
	member.mobile AS member_mobile,
	member.NAME AS member_real_name,
	card_info.card_no AS member_card_no,
	mall_info.NAME AS mall_name,
	mall_info.id AS mall_id,
	LEVEL.member_level AS member_level 
FROM
	mob_prizes_info.prizes_record_info AS info
	LEFT JOIN member_info AS member ON info.member_id = member.id
	LEFT JOIN card_info AS card_info ON info.member_id = card_info.vip_uid
	LEFT JOIN member_level AS LEVEL ON LEVEL.id = member.LEVEL 
	LEFT JOIN activity_base_info AS activity ON info.activity_id = activity.id
	LEFT JOIN event_promotion_award_config AS event_promotion ON info.activity_id = event_promotion.id
	LEFT JOIN mall_info AS mall_info ON mall_info.id = (
	CASE WHEN info.activity_mall_id IS NOT NULL THEN
			info.activity_mall_id 
			WHEN activity.mall_id IS NULL THEN
			event_promotion.mall_id ELSE activity.mall_id 
		END 
		) 
	WHERE
		info.join_time >= 1715961600 
		AND ( info.activity_mall_id = 'xx' OR activity.mall_id = 'xx' OR event_promotion.mall_id = 'BwvfX3ymNCddIUfG' ) 
		AND activity.CODE IN ( 'xx', 'xx' ) 
	ORDER BY
		info.join_time DESC 
	LIMIT 0,
	10

查询消耗：40s 左右

各表数据量：

表	数量级
mob_prizes_info	16473811 主表
card_info	11783047
member_info	17275533

2.优化思路

1.分析索引：

检查查询条件join_time 是否是索引，发现join_time不是索引，但是winning_time是已经是索引

​ 把查询条件更换为winning_time，发下查询时间大大优化，这里可以定位到慢查询的原因：未使用索引

​ 解决方法：

1. 给join_time 设置索引，怎么表的存储，空间换时间

2. 使用winning_time作为查询条件，需要给业务沟通（这里查询统计数量一致，可能排序不一致，业务要求不严格可以使用）

   经过沟通，需要使用join_time 字段，所以选择使用方式1解决

可能这里你认为问题已经解决，但是还是存在问题，分页查询count查询依然特别慢

查询时间在20秒左右，所以也需要优化

再次分析sql：

导致慢sql原因：当你关联数据量大的表如 member 和 card_info 时，如果这些表的索引策略不合理或者连接键上的索引未被有效利用，会导致查询性能显著下降。数据库查询优化器通常会根据统计信息来选择最佳的连接顺序，但是少数据优化器不知道你的数据量，所以需要手处理

解决方法：

1. 连接顺序和策略：

   • 数据库查询优化器通常会根据统计信息来选择最佳的连接顺序。如果统计信息过时，或者优化器的选择不是最优的，可能需要手动干预。

   • 使用 STRAIGHT_JOIN 强制查询按照你指定的顺序执行，有时可以改进性能，尤其是在处理大表时。

     SELECT
     COUNT(info.id)
     FROM
     	mob_prizes_info.prizes_record_info AS info
     	LEFT JOIN mob_prizes_info.activity_base_info AS activity ON info.activity_id = activity.id
     	LEFT JOIN mob_promotion.event_promotion_award_config AS event_promotion ON info.activity_id = event_promotion.id
     	LEFT JOIN mob_base_info.mall_info AS mall_info ON mall_info.id = (
     	CASE
     			WHEN info.activity_mall_id IS NOT NULL THEN
     			info.activity_mall_id 
     			WHEN activity.mall_id IS NULL THEN
     			event_promotion.mall_id ELSE activity.mall_id 
     		END 
     		) 
     		STRAIGHT_JOIN  member_info AS member ON info.member_id = member.id
     		STRAIGHT_JOIN member_level AS LEVEL ON LEVEL.id = member.LEVEL 
     		STRAIGHT_JOIN  card_info AS card_info ON info.member_id = card_info.vip_uid
     	WHERE
     		info.join_time >= 1715961600 
     		AND ( info.activity_mall_id = '1' OR activity.mall_id = '1' OR event_promotion.mall_id = '1' ) 
     		AND activity.CODE IN ( '1', '1' ) 
     

     

     0.43 优化非常明显

2. 针对业务：发现该查询是多个菜单使用，但是部分字段和表在此菜单不需要关联，针对这个菜单不需要member_level，所以这里只需要重写一个sql查询，去掉member_level关联信息即可

   SELECT
   COUNT(info.id)
   FROM
   	prizes_record_info AS info
   	LEFT JOIN activity_base_info AS activity ON info.activity_id = activity.id
   	LEFT JOIN event_promotion_award_config AS event_promotion ON info.activity_id = event_promotion.id
   	LEFT JOIN mall_info AS mall_info ON mall_info.id = (
   	CASE
   			WHEN info.activity_mall_id IS NOT NULL THEN
   			info.activity_mall_id 
   			WHEN activity.mall_id IS NULL THEN
   			event_promotion.mall_id ELSE activity.mall_id 
   		END 
   		) 
   		LEFT JOIN  member_info AS member ON info.member_id = member.id
   		LEFT JOIN member_level AS LEVEL ON LEVEL.id = member.LEVEL 
   		LEFT JOIN  card_info AS card_info ON info.member_id = card_info.vip_uid
   	WHERE
   		info.join_time >= 1715961600 
   		AND ( info.activity_mall_id = '1' OR activity.mall_id = '1' OR event_promotion.mall_id = '1' ) 
   		AND activity.CODE IN ( '1', '1' ) 
   

   

0.362

以上算是完全解决全部问题