线上MySQL千万级大表，如何优化？

线上MySQL千万级大表，如何优化？

前段时间应急群有客服反馈，会员管理功能无法按到店时间、到店次数、消费金额进行排序。经过排查发现是 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 组合索引。

②调整结果(准生产)

如下图：

③调整前后结果对比(准生产)

测试数据：

merchant_member_info 有 902606 条记录。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_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

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。