数据库优化作为每个程序猿必掌握之技能,在开发过程中时常遇到,特别对于互联网公司而言,一个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)FROMt_cs_recharge_record tLEFT JOIN(SELECT AES_DECRYPT(FROM_BASE64(Mobile), 'xxxxx秘钥xxxxx==') AS Mobile, NickName AS NickNameFROMt_customlogin) tc ON t.account = tc.MobileWHERE 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', '%') ORJSON_EXTRACT(t.a_mode_detail, '$.shopList[*].shopName') LIKE CONCAT('%', '123155539', '%')) AND t.status_codeIN ('D02', 'D04') ;

执行结果:

由执行结果可知:

  • 此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 tLEFT JOINt_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', '%') ORJSON_EXTRACT(t.a_mode_detail, '$.shopList[*].shopName') LIKE CONCAT('%', '123155539', '%')) AND t.status_codeIN ('D02', 'D04') ;

对比一下执行时间:

  • 优化前
  • 优化后

至此,大功告成!!!