前段时间紧急群有客服反馈，会员管理功能无法按到达时间、访问次数、消费金额排序。排查后发现SQL执行效率低，索引效率低。图片来自Pexels紧急问题。商家反映会员管理功能无法按到店时间、到店次数、消费金额排序。一直转，或者转完之后没有变化。商家需要使用这些数据来做活动。它更着急。请尽快处理。谢谢。线上数据量merchant_member_info：7000W条数据。member_info:3000W.别问我为什么不分表，变化太大，我也无能为力。提问SQL提问SQL如下：SELECTmui.id,mui.merchant_id,mui.member_id,DATE_FORMAT(mui.recently_consume_time,'%Y%m%d%H%i%s')recently_consume_time,IFNULL(mui.total_consume_num,0)total_consume_num,IFNULL(mui.total_consume_amount,0)total_consume_amount,(CASEWHENu.ni??ck_nameISNULLTHEN'Member'WHENu.ni??ck_name=''THEN'Member'ELSEu.nick_nameEND)AS'昵称',u.sex,u.head_image_url,u.province,u.city,u.countryFROMmerchant_member_infomuiLEFTJOINmember_infouONmui.member_id=u.idWHERE1=1ANDmui.merchant_id='MerchantID'ORDERBYmui.recently_consume_timeDESC/ASCLIMIT0,100,10出现的原因已经验证，可以按降序排列“到达时间”，但是不能升序排序主要是查询太慢了。主要原因是查询虽然使用了recently_consume_time索引，但是该索引效率低，需要查询整棵索引树，导致查询时间较长。DESC查询需要4s左右，ASC查询太慢，耗时未知。为什么降序快升序慢？如下图所示：因为时间是有索引的，所以最后的时间肯定是最晚的。升序查询需要更多的数据才能过滤出对应的结果，所以比较慢。解决方案当前产库索引如下图：①调整索引，需要删除index_merchant_user_last_time索引，将index_merchant_user_merchant_ids单例索引改为merchant_id,recently_consume_time复合索引。②调整结果（准产）如下：③调整前后结果对比（准产）测试数据：merchant_member_info有902606条记录。member_info表有775条记录。④SQL执行效率优化前，如下图：优化后，如下图：typefromindex→ref,reffromnull→const：调整索引时需要执行的SQL执行注意事项：由于表数据量较大，请执行，需单独执行。#删除近期消费时间索引ALTERTABLEmerchant_member_infoDROPINDEXindex_merchant_user_last_time;#删除商户编号索引ALTERTABLEmerchant_member_infoDROPINDEXindex_merchant_user_merchant_ids;#建立商户编号和近期消费时间组合索引ALTERTABLEmerchant_member_infoADDINDEXidx_merchant_id_recently_time(`merchant_id`,`recently_consume_time`);经询问，重建索引花了30分钟。Finalpagingqueryoptimization上述SQL虽然在调整索引后可以达到很高的执行效率，但是随着分页数据的不断增加，性能会急剧下降。SQL优化的最终思路：先用覆盖索引定位到需要的数据行的主键值，然后INNERJOIN回原表获取其他数据。SELECTmui.id,mui.merchant_id,mui.member_id,DATE_FORMAT(mui.recently_consume_time,'%Y%m%d%H%i%s')recently_consume_time,IFNULL(mui.total_consume_num,0)total_consume_num,IFNULL(mui.total_consume_amount,0)total_consume_amount,(CASEWHENu.ni??ck_nameISNULLTHEN'Member'WHENu.ni??ck_name=''THEN'Member'ELSEu.nick_nameEND)AS'昵称',u.sex,u.head_image_url,u.province,u.city,u.countryFROMmerchant_member_infomuiINNERJOIN(SELECTidFROMmerchant_member_infoWHEREmerchant_id='商户ID'ORDERBYrecently_consume_timeDESCLIMIT9000,10)AStmpONtmp.id=mui.idLEFTJOINmember_infoONmui.member_id=u.id作者：不同科技之家编辑：陶家龙来源：juejin.7post9/32
