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_info16473811 主表card_info11783047member_info17275533 2.优化思绪
1.分析索引：
https://img-blog.csdnimg.cn/direct/841f3f4abe484c739162ae3d209bfbc7.png
查抄查询条件join_time 是否是索引，发现join_time不是索引，但是winning_time是已经是索引
把查询条件更换为winning_time，发下查询时间大大优化，这里可以定位到慢查询的原因：未利用索引
解决方法：
[*] 给join_time 设置索引，怎么表的存储，空间换时间
[*] 利用winning_time作为查询条件，需要给业务沟通（这里查询统计数量一致，可能排序不一致，业务要求不严格可以利用）
颠末沟通，需要利用join_time 字段，所以选择利用方式1解决
可能这里你认为题目已经解决，但是还是存在题目，分页查询count查询依然特殊慢
https://img-blog.csdnimg.cn/direct/2908c7579fec458e8d5ed4c4b0986f17.png
https://img-blog.csdnimg.cn/direct/96f8a76b56d545218d655b69b7a9af1d.png
查询时间在20秒左右，所以也需要优化
再次分析sql：
https://img-blog.csdnimg.cn/direct/c0803764d89540a8bee3963c69388825.png
https://img-blog.csdnimg.cn/direct/9e6b8c79093f4717b13b949236505f50.png
导致慢sql原因：当你关联数据量大的表如 member 和 card_info 时，假如这些表的索引策略不合理大概毗连键上的索引未被有用利用，会导致查询性能显著降落。数据库查询优化器通常会根据统计信息来选择最佳的毗连次序，但是少数据优化器不知道你的数据量，所以需要手处理
解决方法：
[*] 毗连次序和策略：
[*] 数据库查询优化器通常会根据统计信息来选择最佳的毗连次序。假如统计信息过时，大概优化器的选择不是最优的，可能需要手动干预。
[*] 利用 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_JOINmember_info AS member ON info.member_id = member.id
STRAIGHT_JOIN member_level AS LEVEL ON LEVEL.id = member.LEVEL
STRAIGHT_JOINcard_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' )
https://img-blog.csdnimg.cn/direct/2df7d09e7af54fad817ca346f01db717.png
0.43 优化非常显着
[*] 针对业务：发现该查询是多个菜单利用，但是部分字段和表在此菜单不需要关联，针对这个菜单不需要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 JOINmember_info AS member ON info.member_id = member.id
LEFT JOIN member_level AS LEVEL ON LEVEL.id = member.LEVEL
LEFT JOINcard_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' )
https://img-blog.csdnimg.cn/direct/7bc410f65eee437cae038ef632664d23.png
0.362
以上算是完全解决全部题目
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]