欢迎大家来到IT世界,在知识的湖畔探索吧!
今天我们聊一聊Mysql大表查询优化,前段时间应急群有客服反馈,会员管理功能无法按到店时间、到店次数、消费金额 进行排序。
经过排查发现是Sql执行效率低,并且索引效率低下。
应急问题
商户反馈会员管理功能无法按到店时间、到店次数、消费金额 进行排序,一直转圈圈或转完无变化,商户要以此数据来做活动,比较着急,请尽快处理,谢谢。
线上数据量
merchant_member_info 7000W条数据。
member_info 3000W。
“
不要问我为什么不分表,改动太大,无能为力。
问题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
欢迎大家来到IT世界,在知识的湖畔探索吧!
出现的原因
经过验证可以按照“到店时间”进行降序排序,但是无法按照升序进行排序主要是查询太慢了。
主要原因是:虽然该查询使用建立了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执行效率
优化前
优化后
type由index -> ref
ref由 null -> const
调整索引需要执行的SQL
执行的注意事项:
由于表中的数据量太大,请在晚上进行执行,并且需要分开执行。
欢迎大家来到IT世界,在知识的湖畔探索吧!# 删除近期消费时间索引
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分钟。关注公众号Java技术栈可以获取系列索引教程。
最终的分页查询优化
上面的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
结尾
如果觉得对你有帮助,可以多多评论,多多点赞哦,谢谢。
免责声明:本站所有文章内容,图片,视频等均是来源于用户投稿和互联网及文摘转载整编而成,不代表本站观点,不承担相关法律责任。其著作权各归其原作者或其出版社所有。如发现本站有涉嫌抄袭侵权/违法违规的内容,侵犯到您的权益,请在线联系站长,一经查实,本站将立刻删除。 本文来自网络,若有侵权,请联系删除,如若转载,请注明出处:https://itzsg.com/33394.html