亿级表优化思路之SQL篇
初六坐高铁回家，看着窗外铁轨我有一个疑问为啥铁轨旁边有一堆小石头呢？
看着铁轨要不写几篇性能优化吧（这几天我正愁写啥呢），刚好有丢丢经验可以总结下。
拿出电脑打开 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、你们开发中遇到的大表是如何解决的呢？
欢迎大家一起讨论。
写在最后
本文封面来源于网图，侵删。