SQL优化之Using join buffer (Block Nested Loop)

数据库优化作为每个程序猿必掌握之技能,在开发过程中时常遇到,特别对于互联网公司而言,一个sql的执行速度,直接影响到用户的体验。而DB攻城狮对一般企业来说又是可望不可即的存在,只能寄托给咱们苦逼的程序猿……

问题描述

今天处理个left join涉及的嵌套循环问题(不懂的可以百度),先领导给的SQL执行图片(打码部分为字段加密的秘钥,谅解一下):
在这里插入图片描述

  1. sql中共涉及两个表t_customlogint_cs_recharge_recordt_customlogin表是用户的登录表,即用户表;t_cs_recharge_record为用户的充值表。此SQL查询的业务为:根据时间、店铺维度查询充值记录表部分字段,并显示用户充值记录的用户信息(上面的字段忽略了,统一使用count(0))
  2. t_customlogin为用户登录表,字段mobile为加密字段,与t_cs_recharge_record表中account字段关联,此字段未加密

问题分析

遇事不决,量子力学!Sql优化,那就explain走起!

EXPLAIN SELECT COUNT(0)
FROM  t_cs_recharge_record t
LEFT JOIN
        (SELECT AES_DECRYPT(FROM_BASE64(Mobile), 'xxxxx秘钥xxxxx==') AS Mobile, NickName AS NickName
        FROM    t_customlogin) tc ON t.account = tc.Mobile
WHERE   1 = 1 AND t.query_date BETWEEN '2022-08-01' AND '2022-08-31' AND t.k_code = 'K270121610' AND (t.shop_Id =
        '123155539' OR JSON_EXTRACT(t.a_mode_detail, '$.shopList[*].shopId') LIKE CONCAT('%', '123155539', '%') OR
        JSON_EXTRACT(t.a_mode_detail, '$.shopList[*].shopName') LIKE CONCAT('%', '123155539', '%')) AND t.status_code
        IN ('D02', 'D04') ;

执行结果:
explain执行结果
由执行结果可知:

  • 此sql中两条查询均为simple类型,两表均无分区,索引和索引长度均无问题
    • t_cs_recharge_record表其实可以采取分区,建议过,领导拒绝了,GG
    • t_cs_recharge_record表中存在多个索引:部分字段联合索引和单字段索引,mysql中没有找到从where后的联合索引,故选择部分字段的联合索引,如图选择了index_kcode_shopId索引,放弃了时间索引:index_query_date
  • Extra中显示Using where; Using index; Using join buffer (Block Nested Loop), 且被关联表t_customlogin表中涉及的行数为711384行,由此可知sql执行中进行了嵌套循环,关联表t_cs_recharge_record获取一条数据后,会去被关联表t_customlogin中查询
    • 由于被关联表是加密字段,故关联表在查询被关联表的时候,会先对7w多条数据进行解密,解密之后再会关联,查找出符合条件的数据,这样极大的拖延了查询时间,至此,问题找到!!!
    • 顺便说一嘴,如果是mysql5.7以前,mysql采用临时表

解决问题

直接说处理方式吧:
先进行左连接,后对字段加解密on关联(对连接表字段加密,而非被关联表)!!!
先进行左连接,后对字段加解密on关联(对连接表字段加密,而非被关联表)!!!
先进行左连接,后对字段加解密on关联(对连接表字段加密,而非被关联表)!!!

SELECT COUNT(0)
FROM t_cs_recharge_record t
LEFT JOIN
        t_customlogin tc on tc.Mobile = TO_BASE64(aes_encrypt(t.account,'KzN7e115RTBCZw=='))
WHERE   1 = 1 AND t.query_date BETWEEN '2022-08-01' AND '2022-08-31' AND t.k_code = 'K270121610' AND (t.shop_Id =
        '123155539' OR JSON_EXTRACT(t.a_mode_detail, '$.shopList[*].shopId') LIKE CONCAT('%', '123155539', '%') OR
        JSON_EXTRACT(t.a_mode_detail, '$.shopList[*].shopName') LIKE CONCAT('%', '123155539', '%')) AND t.status_code
        IN ('D02', 'D04') ;

对比一下执行时间:

  • 优化前
    在这里插入图片描述
  • 优化后
    在这里插入图片描述

至此,大功告成!!!