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

1.背景

慢查询sql：

1. SELECT
2.         info.id,
3.         info.create_time,
4.         info.modify_time,
5.         info.prizes_info_id,
6.         info.prize_name,
7.         info.award_time,
8.         info.award_status,
9.         info.winning_time,
10.         info.join_time,
11.         info.check_status,
12.         info.award_type,
13.         info.award_type_name,
14.         member.mobile AS member_mobile,
15.         member.NAME AS member_real_name,
16.         card_info.card_no AS member_card_no,
17.         mall_info.NAME AS mall_name,
18.         mall_info.id AS mall_id,
19.         LEVEL.member_level AS member_level
20. FROM
21.         mob_prizes_info.prizes_record_info AS info
22.         LEFT JOIN member_info AS member ON info.member_id = member.id
23.         LEFT JOIN card_info AS card_info ON info.member_id = card_info.vip_uid
24.         LEFT JOIN member_level AS LEVEL ON LEVEL.id = member.LEVEL
25.         LEFT JOIN activity_base_info AS activity ON info.activity_id = activity.id
26.         LEFT JOIN event_promotion_award_config AS event_promotion ON info.activity_id = event_promotion.id
27.         LEFT JOIN mall_info AS mall_info ON mall_info.id = (
28.         CASE WHEN info.activity_mall_id IS NOT NULL THEN
29.                         info.activity_mall_id
30.                         WHEN activity.mall_id IS NULL THEN
31.                         event_promotion.mall_id ELSE activity.mall_id
32.                 END
33.                 )
34.         WHERE
35.                 info.join_time >= 1715961600
36.                 AND ( info.activity_mall_id = 'xx' OR activity.mall_id = 'xx' OR event_promotion.mall_id = 'BwvfX3ymNCddIUfG' )
37.                 AND activity.CODE IN ( 'xx', 'xx' )
38.         ORDER BY
39.                 info.join_time DESC
40.         LIMIT 0,
41.         10

复制代码

  查询消耗：40s 左右
  各表数据量：
表数量级mob_prizes_info16473811 主表card_info11783047member_info17275533 2.优化思绪

1.分析索引：

查抄查询条件join_time 是否是索引，发现join_time不是索引，但是winning_time是已经是索引
​ 把查询条件更换为winning_time，发下查询时间大大优化，这里可以定位到慢查询的原因：未利用索引
​ 解决方法：

• 给join_time 设置索引，怎么表的存储，空间换时间
  
• 利用winning_time作为查询条件，需要给业务沟通（这里查询统计数量一致，可能排序不一致，业务要求不严格可以利用）
  颠末沟通，需要利用join_time 字段，所以选择利用方式1解决
  

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

查询时间在20秒左右，所以也需要优化
再次分析sql：

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

• 毗连次序和策略：
  
  
  
  • 数据库查询优化器通常会根据统计信息来选择最佳的毗连次序。假如统计信息过时，大概优化器的选择不是最优的，可能需要手动干预。
    
  • 利用 STRAIGHT_JOIN 强制查询按照你指定的次序执行，偶然可以改进性能，尤其是在处理大表时。
    
    1. SELECT
    2. COUNT(info.id)
    3. FROM
    4.         mob_prizes_info.prizes_record_info AS info
    5.         LEFT JOIN mob_prizes_info.activity_base_info AS activity ON info.activity_id = activity.id
    6.         LEFT JOIN mob_promotion.event_promotion_award_config AS event_promotion ON info.activity_id = event_promotion.id
    7.         LEFT JOIN mob_base_info.mall_info AS mall_info ON mall_info.id = (
    8.         CASE
    9.                         WHEN info.activity_mall_id IS NOT NULL THEN
    10.                         info.activity_mall_id
    11.                         WHEN activity.mall_id IS NULL THEN
    12.                         event_promotion.mall_id ELSE activity.mall_id
    13.                 END
    14.                 )
    15.                 STRAIGHT_JOIN  member_info AS member ON info.member_id = member.id
    16.                 STRAIGHT_JOIN member_level AS LEVEL ON LEVEL.id = member.LEVEL
    17.                 STRAIGHT_JOIN  card_info AS card_info ON info.member_id = card_info.vip_uid
    18.         WHERE
    19.                 info.join_time >= 1715961600
    20.                 AND ( info.activity_mall_id = '1' OR activity.mall_id = '1' OR event_promotion.mall_id = '1' )
    21.                 AND activity.CODE IN ( '1', '1' )

    复制代码
    
    
    0.43 优化非常显着
    
  
  
• 针对业务：发现该查询是多个菜单利用，但是部分字段和表在此菜单不需要关联，针对这个菜单不需要member_level，所以这里只需要重写一个sql查询，去掉member_level关联信息即可
  
  1. SELECT
  2. COUNT(info.id)
  3. FROM
  4.         prizes_record_info AS info
  5.         LEFT JOIN activity_base_info AS activity ON info.activity_id = activity.id
  6.         LEFT JOIN event_promotion_award_config AS event_promotion ON info.activity_id = event_promotion.id
  7.         LEFT JOIN mall_info AS mall_info ON mall_info.id = (
  8.         CASE
  9.                         WHEN info.activity_mall_id IS NOT NULL THEN
  10.                         info.activity_mall_id
  11.                         WHEN activity.mall_id IS NULL THEN
  12.                         event_promotion.mall_id ELSE activity.mall_id
  13.                 END
  14.                 )
  15.                 LEFT JOIN  member_info AS member ON info.member_id = member.id
  16.                 LEFT JOIN member_level AS LEVEL ON LEVEL.id = member.LEVEL
  17.                 LEFT JOIN  card_info AS card_info ON info.member_id = card_info.vip_uid
  18.         WHERE
  19.                 info.join_time >= 1715961600
  20.                 AND ( info.activity_mall_id = '1' OR activity.mall_id = '1' OR event_promotion.mall_id = '1' )
  21.                 AND activity.CODE IN ( '1', '1' )

  复制代码
  
  
  

   0.362
  以上算是完全解决全部题目

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。