腾讯云：线上千万级大表排序该如何优化？

小提示：您能找到这篇{腾讯云：线上千万级大表排序该如何优化？}绝对不是偶然，我们能帮您找到潜在客户，解决您的困扰。如果您对本页介绍的腾讯云：线上千万级大表排序该如何优化？内容感兴趣，有相关需求意向欢迎拨打我们的服务热线，或留言咨询，我们将第一时间联系您！

< ">前言

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

< ">应急问题

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

< ">线上数据量

< ">< background-color: rgb(242, 242, 242);">merchant_member_info 7000W条数据。

< ">< background-color: rgb(242, 242, 242);">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查询太慢耗时未知。

< ">为什么降序排序快和而升序慢呢？

< ">因为是对时间建立了索引，最近的时间一定在最后面，升序查询，需要查询更多的数据，才能过滤出相应的结果，所以慢。

< ">解决方案

< ">目前生产库的索引

< ">调整索引

< ">需要删除< background-color: rgb(242, 242, 242);">index_merchant_user_last_time索引，同时将< background-color: rgb(242, 242, 242);">index_merchant_user_merchant_ids单例索引，变为< background-color: rgb(242, 242, 242);">merchant_id,recently_consume_time组合索引。

< ">调整结果（准生产）

< ">调整前后结果对比（准生产）

< ">测试数据

< ">< background-color: rgb(242, 242, 242);">merchant_member_info有902606条记录。

< ">< background-color: rgb(242, 242, 242);">member_info表有775条记录。

< ">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_am精确搜索ount,

    (

        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