前段时间应急群有客服反馈,会员管理功能无法按到店时间、到店次数、消费金额进行排序。经过排查发现是 SQL 执行效率低,并且索引效率低下。
图片来自 Pexels
应急问题
商户反馈会员管理功能无法按到店时间、到店次数、消费金额进行排序,一直转圈圈或转完无变化,商户要以此数据来做活动,比较着急,请尽快处理,谢谢。
线上数据量
merchant_member_info:7000W 条数据。
member_info:3000W。
不要问我为什么不分表,改动太大,无能为力。
问题 SQL
问题 SQL 如下:
- SELECT
- mui.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,
- (
- CASE
- WHEN u.nick_name IS NULL THEN
- '会员'
- WHEN u.nick_name = '' THEN
- '会员'
- ELSE
- u.nick_name
- END
- ) AS 'nickname',
- u.sex,
- u.head_image_url,
- u.province,
- u.city,
- u.country
- FROM
- merchant_member_info mui
- LEFT JOIN member_info u ON mui.member_id = u.id
- WHERE
- 1 = 1
- AND mui.merchant_id = '商户编号'
- ORDER BY
- mui.recently_consume_time DESC / ASC
- LIMIT 0,
- 10
出现的原因
经过验证可以按照“到店时间”进行降序排序,但是无法按照升序进行排序主要是查询太慢了。
主要原因是:虽然该查询使用建立了 recently_consume_time 索引,但是索引效率低下,需要查询整个索引树,导致查询时间过长。DESC 查询大概需要 4s,ASC 查询太慢耗时未知。
为什么降序排序快和而升序慢呢?
如下图:
因为是对时间建立了索引,最近的时间一定在最后面,升序查询,需要查询更多的数据,才能过滤出相应的结果,所以慢。
解决方案
目前生产库的索引,如下图:
①调整索引
需要删除 index_merchant_user_last_time 索引,同时将 index_merchant_user_merchant_ids 单例索引,变为 merchant_id,recently_consume_time 组合索引。
②调整结果(准生产)
如下图:
③调整前后结果对比(准生产)
测试数据:
④SQL 执行效率
优化前,如下图:
优化后,如下图:
type 由 index→ref,ref 由 null→const:
调整索引需要执行的 SQL
执行的注意事项:由于表中的数据量太大,请在晚上进行执行,并且需要分开执行。
- # 删除近期消费时间索引
- ALTER TABLE merchant_member_info DROP INDEX index_merchant_user_last_time;
- # 删除商户编号索引
- ALTER TABLE merchant_member_info DROP INDEX index_merchant_user_merchant_ids;
- # 建立商户编号和近期消费时间组合索引
- ALTER TABLE merchant_member_info ADD INDEX idx_merchant_id_recently_time (`merchant_id`,`recently_consume_time`);
经询问,重建索引花了 30 分钟。
最终的分页查询优化
上面的 SQL 虽然经过调整索引,虽然能达到较高的执行效率,但是随着分页数据的不断增加,性能会急剧下降。
最终的 SQL
优化思路:先走覆盖索引定位到,需要的数据行的主键值,然后 INNER JOIN 回原表,取到其他数据。
- SELECT
- mui.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,
- (
- CASE
- WHEN u.nick_name IS NULL THEN
- '会员'
- WHEN u.nick_name = '' THEN
- '会员'
- ELSE
- u.nick_name
- END
- ) AS 'nickname',
- u.sex,
- u.head_image_url,
- u.province,
- u.city,
- u.country
- FROM
- merchant_member_info mui
- INNER JOIN (
- SELECT
- id
- FROM
- merchant_member_info
- WHERE
- merchant_id = '商户ID'
- ORDER BY
- recently_consume_time DESC
- LIMIT 9000,
- 10
- ) AS tmp ON tmp.id = mui.id
- LEFT JOIN member_info u ON mui.member_id = u.id
作者:不一样的科技宅
编辑:陶家龙
出处:juejin.cn/post/6844904053239971854
微软确认, 将会在Win10 Build 19043.899(21H1)更新中,彻底从系统中删除经典...
前言: 今天这篇文章给大家介绍关于ajax的content-download时间过慢问题的解决与...
来自:机器之心 最近在 GitHub 上最火的项目是一个对视力友好的十六进制编辑器,...
本文实例为大家分享了JSP+Servlet实现文件上传到服务器功能的具体代码,供大家参...
JSP开发中Apache-HTTPClient 用户验证的实例详解 前言: 在微服务框架之外的系统...
有时候我么您需要获取网址,端口、路径文件名、参数等,这里就为大家分享一下这...
OCR光学字符识别OPTICAL CHARACTER RECOGNITION作为计算机视觉领域的经典问题之...
如往常一样, 客户发给我一个xml文件, 用来更新数码课堂日程安排——是一个js读...
HTTP/1.1 协议规定的 HTTP 请求方法有 OPTIONS、GET、HEAD、POST、PUT、DELETE、...
为什么我们需要它 不得不说,在知道这个命令的时,以及之后的使用中,我都超级热...