亿级表优化思路之 SQL 篇，值得收藏

20240403 日更新，亿级别第二篇更新了，大家看下面这篇文章哦

初六坐高铁回家，看着窗外铁轨我有一个疑问为啥铁轨旁边有一堆小石头呢？

看着铁轨要不写几篇性能优化吧（这几天我正愁写啥呢），刚好有丢丢经验可以总结下。

拿出电脑打开 XMID 构思中……

于是初八回成都，默默拿出电脑……

好了先介绍下想法吧，亿级表优化分 2 讲，第 1 讲分享 SQL 和索引优化；第 2 讲分享表分区和冷热表分离。

写作背景

为啥会写亿级表优化？网上谈的不少但是不知道大伙儿是否在自己 Owner 的业务中验证过。我也来凑凑热闹。

名词解释

某一个记录表

目前数据量 n 亿+。

我先介绍下模型，可以理解记录表。模型比较简单如下：

字段名	字段类型	字段解释
id	varchar(256)	主键id
user_id	varchar(256)	员工id
c_id	varchar(256)	联系人id
status	varchar(256)	状态（已完成、未完成...）
create_time	bigint(20)	创建时间
node_id	varchar(256)	节点id
sop_id	varchar(256)	sopid
task_id	varchar(256)	任务id
n_name	varchar(256)	节点名称
.....		.....

TIDB 关键字

分析 SQL 执行计划会用到一些 TIDB 知识，详细可以参考TIDB官方文档自己研究，文档贴在后面。TiDB 执行计划概览 | PingCAP 文档中心

EXPLAIN 和 EXPLAIN ANALYZE：我们要分析 SQL 执行计划肯定会用到这 2 个关键字，但是他们是有区别的， EXPLAIN 实际不会执行查询。EXPLAIN ANALYZE 可用于实际执行查询并显示执行计划。

算子简介

执行顺序

还有 JOIN 的执行计划，用 EXPLAIN 查看 JOIN 查询的执行计划 | PingCAP 文档中心

问题定位

定位方法

问题定位千万不要依赖客户反馈、不要依赖客户反馈、不要依赖客户反馈... 重要的事说 3 遍。如果是客户反馈大概率是全平台问题了，受影响的客户会非常多。项目上线后一定要做好监控、数据看板、告警能及时发现问题。

定位问题一般是结合多个工具。1、公司内部的”可观测平台“，它能及时给你告警和一些有用信息。2、你也可以利用腾讯平台的 cls 登录 - 腾讯云或者阿里云的 sls 阿里云登录 - 欢迎登录阿里云，安全稳定的云计算服务平台配置看板和告警，下图是我利用 cls 构造的 SQL 看板（写文章用）。

有了看板发现问题就比较简单了，SQL 耗时、reqid、对应的 SQL 明细被记录在以下列表，如下图：

问题验证

通过看板和监控搜集了一批耗时长 SQL，最终排查结果锁定 SQL 问题，模拟一个案例如下：

EXPLAIN ANALYZE
SELECT DISTINCT (ut.id),
                ut.create_time,
                ut.plan_id,
                ut.task_type,
                c.NAME
FROM sop_user_task AS ut
         LEFT JOIN sop_node AS c ON ut.node_id = c.id
    AND ut.cid = c.cid
         LEFT JOIN sop AS d ON ut.plan_id = d.id
    AND ut.cid = c.cid
         LEFT JOIN sop_user_task_item AS utd ON utd.task_id = ut.id
    AND utd.cid = ut.cid
WHERE (
            utd.user_id = '418'
        AND d.is_delete = 0
    )
  AND utd.STATUS IN ('1', '2')
  AND ut.cid = 'x00000003'
ORDER BY ut.create_time ASC
LIMIT 20 offset 0;

SQL 比较简单可以理解成查询 cid 为 x00000003，user_id 为 418 且 status 是 1 或 2 、并且是未删除的数据。

上段 SQL ，通过 explain analyze 分析 SQL，如下图：

上面这个执行计划比较复杂，我简介绍下有一个 indexRangeScan 带有范围的索引数据扫描操作，扫了 140W 数据。大概应该都知道扫描行数越多，数据库内存占用会越大，执行计划没有截全看不到内存消耗了。

执行计划是有内存、磁盘消耗的，用 EXPLAIN ANALYZE 关键字自行测试。

解决方案

分析完执行计划，我们聊聊优化方案，线上紧急问题肯定是先止血，再设计短期方案，最后才是长期方案。

1、  第一步先止血，利用执行计划分析我们先增加索引，暂时提高 SQL 性能，但“SOP”数据量增加非常快，上面的 SQL 依然存在问题（增加索引只能晚上进行，高峰期是禁止执行的）。

2、  优化 SQL，SQL 优化思路大概是把多表 Join 查询改成单表仅保留 sop_user_task_item 表。

a)  技术测先冗余字段，sop 表 isDelete 字段 冗余到 sop_user_task_item status字段，减少 sop 表 join。

b)  去掉 join sop_user_task 表查询，从 sop_user_task_item 出结果，唯一缺点需要 group by task_id。

c)  a、b 步骤后剩下只 join sop_node 表了，员工任务列表有需求是通过节点名称模糊搜索，另外还需要回显示。回显示问题不大，ByIDs 查询一次就好，但是模糊搜索砍掉客户不接受，经过对线上分析，我们发现客户改节点名字的概率基本没有，最后决定损失一部分客户体验，保留模糊搜索，把节点名称冗余到 sop_user_task_item （我还有一种方案，利用子查询，也验证了性能是很好的，本次不讲，主要是优化上线后客户没提）。

经过上面一顿操作，优化后的 SQL 如下：

EXPLAIN ANALYZE
SELECT node_id,
       task_id,
       user_id
FROM sop_user_task_item
WHERE cid = 'x00000003'
  AND user_id = '418'
  AND STATUS IN ('1', '2')
GROUP BY node_id,
         task_id
ORDER BY node_id DESC
LIMIT 100 OFFSET 0;

SQL 优化后是不是简单多了？单条 SQL 查询一部分数据，其它回显数据 ByIDs 补充数据就好了。

优化后的执行计划如下：

简单解释下执行计划：

第一步：通过 IndexRangeScan，带有范围的索引数据扫描操作，命中索引 cid_uid_ctime 并且行数是 1665。

第二步：因为索引不带 status，还会在 tikv 上过滤 status，过滤后数据量为 32 条。

第三步：SQL 是带 group by 的，通过 HashAgg 聚合算子聚合后，数据还有21 条。

第四步：limit 限制 20 条返回，返回数据量为 20，SQL 执行完毕。

总结

1、日常开发中，监控、看板、告警很重要，大家在做系分设计一定要把这个纳入你的设计中，保障你的数据、行为是可以量化和追踪的，另外你可以通过数据看板持续跟踪功能的使用情况。

2、假设有疑似问题千万别放过（只要 SQL 存在性能问题生产环境一定会出现的，不要抱有侥幸心理。），先排查（找到问题根源，避免胡乱抓）、再验证（确保改造有效，避免做无用功，上线后问题没有解决还浪费资源）、再改造、最后再上线（建议先灰度不要一把梭哈，尤其是改造范围广的，先灰度发现问题减少对客户的影响）。

3、不要死扣技术，相信技术能解决一切，要具备一些产品思维（要适当损失产品体验降低设计复杂度）。

4、并不是所有数据表设计都要满足数据库三范式（尤其是大表），合理冗余字段来优化跨库、跨表的 JOIN，提高查询性能。

5、大表在你用多表 JOIN 做业务前一定要导入大量数据验证清楚；我建议不要 JOIN 尽量采用字段冗余、单表出结果+ByIDs 查询这套组合拳。

问题讨论

1、你们平时定位问题的思路和步骤是啥呢？

2、你们开发中遇到的大表是如何解决的呢？

欢迎大家一起讨论。

写在最后

本文封面来源于网图，侵删。

公众号原文地址

mp.weixin.qq.com/s/GhM2FlnYd…