数据库优化作为每个程序猿必掌握之技能,在开发过程中时常遇到,特别对于互联网公司而言,一个sql的执行速度,直接影响到用户的体验。而DB攻城狮对一般企业来说又是可望不可即的存在,只能寄托给咱们苦逼的程序猿……
问题描述
今天处理个left join涉及的嵌套循环问题(不懂的可以百度),先领导给的SQL执行图片(打码部分为字段加密的秘钥,谅解一下):
- sql中共涉及两个表
t_customlogin
和t_cs_recharge_record
,t_customlogin
表是用户的登录表,即用户表;t_cs_recharge_record
为用户的充值表。此SQL查询的业务为:根据时间、店铺维度查询充值记录表部分字段,并显示用户充值记录的用户信息(上面的字段忽略了,统一使用count(0)) 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
表其实可以采取分区,建议过,领导拒绝了,GGt_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') ;
对比一下执行时间:
- 优化前
- 优化后
至此,大功告成!!!