线上千万级大表排序:优化攻略揭秘,轻松应对海量数据!

简介: 前段时间应急群有客服反馈,会员管理功能无法按到店时间、到店次数、消费金额 进行排序。经过排查发现是Sql执行效率低,并且索引效率低下。遇到这样的情况我们该如何处理呢?今天我们聊一聊Mysql大表查询优化。

前言

  前段时间应急群有客服反馈,会员管理功能无法按到店时间、到店次数、消费金额 进行排序。经过排查发现是Sql执行效率低,并且索引效率低下。遇到这样的情况我们该如何处理呢?今天我们聊一聊Mysql大表查询优化。

应急问题

  商户反馈会员管理功能无法按到店时间、到店次数、消费金额 进行排序,一直转圈圈或转完无变化,商户要以此数据来做活动,比较着急,请尽快处理,谢谢。

线上数据量

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

出现的原因

  经过验证可以按照“到店时间”进行降序排序,但是无法按照升序进行排序主要是查询太慢了。主要原因是:虽然该查询使用建立了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

TOP 优化前 优化后
到店时间-降序 0.274s 0.003s
到店时间-升序 11.245s 0.003s

调整索引需要执行的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虽然经过调整索引,虽然能达到较高的执行效率,但是随着分页数据的不断增加,性能会急剧下降。

分页数据 查询时间 优化后
limit 0,10 0.003s 0.002s
limit 10,10 0.005s 0.002s
limit 100,10 0.009s 0.002s
limit 1000,10 0.044s 0.004s
limit 9000,10 0.247s 0.016s

最终的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

结尾

  如果觉得对你有帮助,可以多多评论,多多点赞哦,也可以到我的主页看看,说不定有你喜欢的文章,也可以随手点个关注哦,谢谢。

  我是不一样的科技宅,每天进步一点点,体验不一样的生活。我们下期见!

相关文章
|
8月前
|
缓存 关系型数据库 Java
哈啰一面:如何优化大表的查询速度?
哈啰一面:如何优化大表的查询速度?
164 1
哈啰一面:如何优化大表的查询速度?
|
存储 测试技术 C++
实践:几十亿条数据分布在几十个节点上的毫秒级实时排序方法
#引子 先简单的问一下, 你如何解决这样的需求: ``` 对一堆数据按某字段排序,获取第100-10条的数据。 ``` 假设你面对的数据是个单节点,简单来说,就是一个mysql数据库, 很自然地用 select a from tb order by a limit 100, 10; ![imag
4178 0
|
19天前
|
存储 监控 Java
近亿级用户体量高并发实战:大促前压测干崩近百个服务引起的深度反思!
几年前,数百个服务,将堆内存从28GB升配到36GB,引发系统全面OOM的事件。
53 12
|
2月前
|
存储 关系型数据库 分布式数据库
突破大表瓶颈|小鹏汽车使用PolarDB实现百亿级表高频更新和实时分析
PolarDB已经成为小鹏汽车应对TB级别大表标注、分析查询的"利器"。
突破大表瓶颈|小鹏汽车使用PolarDB实现百亿级表高频更新和实时分析
|
2月前
|
消息中间件 监控 druid
思源:秒级体验百亿级数据量监控钻取
思源:秒级体验百亿级数据量监控钻取
|
3月前
|
存储 新零售 监控
挖掘业务场景的存储更优解
挖掘业务场景的存储更优解
|
9月前
|
存储 关系型数据库 MySQL
MySQL索引探秘:加速数据检索的必备利器
MySQL索引探秘:加速数据检索的必备利器
250 0
|
9月前
|
存储 监控 关系型数据库
传统库分表麻烦查询慢?TDengine 如何解决“搜狐基金”的应用难题
搜狐基金团队使用的 MySQL 数据库在面对海量数据时存在能力瓶颈,在此背景下,其决定基于 TDengine 尝试一下全新的方案。
120 0
|
9月前
|
存储 关系型数据库 MySQL
太强了!三种方案优化 2000w 数据大表!
太强了!三种方案优化 2000w 数据大表!
112 0
|
10月前
|
存储 SQL 运维
单表 1000 万条数据,TDengine 助力麦当劳中国实现 PERCENTILE 秒级查询优化
今天我们为大家分享一个关于 TDengine 在 PERCENTILE 函数性能优化上的真实案例。
119 0