TIDB SQL优化进阶

1.理解执行计划

通过观察 EXPLAIN 的结果，你可以知道如何给数据表添加索引使得执行计划使用索引从而加速 SQL 语句的执行速度；你也可以使用 EXPLAIN 来检查优化器是否选择了最优的顺序来 JOIN 数据表

详见：https://pingcap.com/docs-cn/v3.0/query-execution-plan/

（1）EXPLAIN 输出格式

目前 TiDB 的 EXPLAIN 会输出 4 列，分别是：id，count，task，operator info。执行计划中每个算子都由这 4 列属性来描述，EXPLAIN 结果中每一行描述一个算子。每个属性的具体含义如下：

示例：

explain select count(*)from zhiyoubao2_mq_dw.order_infowhere corp_code = 'sdyfx009'explain select count(*)from zhiyoubao2_mq_dw.order_infowhere create_time >= '2020-06-01 00:00:00'

（2）EXPLAIN执行计划解读

示例：

SELECT oncr.service_code,sp.project_name     as scenic_name,sum(oncr.check_num) as check_num
FROM ods_tesb.online_check_record oncrjoin ods_tesb.sys_project_info sp on oncr.service_code = sp.service_code
WHERE oncr.check_time >= '2020-06-02 00:00:00'AND oncr.check_time <= '2020-06-02 23:59:59'AND oncr.service_code = 'SE2020042718200173951'and oncr.deleted = 'F'and oncr.check_type not like '%换票%'
group by oncr.service_code,sp.project_name

sql的执行计划需要转换为查询树后，才能看到比较清楚

2.join方式选择

(1)Hint语法(参考Optimizer Hints)：

TiDB 在 MySQL 的 Optimizer Hint 语法上，增加了一些 TiDB 专有的 Hint 语法, 使用这些 Hint 的时候，TiDB 优化器会尽量使用指定的算法，在某些场景下会比默认算法更优。TiDB使用的hint语法和Oracle的语法类似。

TiDB 支持三种 Join 算法：Hash Join，Sort Merge Join 和 Index Look up Join。Hash Join 的原理是将参与连接的小表预先装载到内存中，读取大表的所有数据进行连接。Sort Merge Join 会利用输入数据的有序信息，同时读取两张表的数据并依次进行比较。Index Look Up Join 会读取外表的数据，并对内表进行主键或索引键查询。

(2)示例如下：

a.TIDB_SMJ(t1, t2) / MERGE_JOIN(t1, t2)

SELECT /*+ TIDB_SMJ(t1, t2) */ * from t1，t2 where t1.id = t2.id

提示优化器使用 Sort Merge Join 算法，这个算法通常会占用更少的内存，但执行时间会更久。 当数据量太大，或系统内存不足时，建议尝试使用。

b.TIDB_INLJ(t1, t2) / INL_JOIN(t1, t2)

SELECT /*+ TIDB_INLJ(t1, t2) */ * from t1，t2 where t1.id = t2.id

提示优化器使用 Index Lookup Join 算法，这个算法可能会在某些场景更快，消耗更少系统资源，有的场景会更慢，消耗更多系统资源。对于外表经过 WHERE 条件过滤后结果集较小（小于 1 万行）的场景，可以尝试使用。TIDB_INLJ()中的参数是建立查询计划时，驱动表（外表）的候选表。即TIDB_INLJ(t1)只会考虑使用t1作为驱动表构建查询计划。

c.TIDB_HJ(t1, t2) / Hash_JOIN(t1, t2)

SELECT /*+ TIDB_HJ(t1, t2) */ * from t1，t2 where t1.id = t2.id 

提示优化器使用 Hash Join 算法，这个算法多线程并发执行，执行速度较快，但会消耗较多内存。

d.USE_INDEX(t1_name, idx1_name [, idx2_name …])/等价于在表名后面加use index(idx)

select /*+ USE_INDEX(t1, idx1, idx2) */ * from t t1 

提示优化器对指定表仅使用给出的索引

(3)拓展：

a._tidb_rowid

这个是一个 TiDB 的隐藏列，代表隐式的 ROW ID 的列名，只存在于 PK 非整数或没有 PK 的表上，可以进行增减改查的操作。

SELECT 语句示例：SELECT *, _tidb_rowid from t;

INSERT 语句示例：INSERT t (c, _tidb_rowid) VALUES (1, 1);

UPDATE 语句示例：UPDATE t SET c = c + 1 WHERE _tidb_rowid = 1;

DELETE 语句示例：DELETE FROM t WHERE _tidb_rowid = 1;

b.SHARD_ROW_ID_BITS

这个 TABLE OPTION 是用来设置隐式 _tidb_rowid 的分片数量的 bit 位数。

对于 PK 非整数或没有 PK 的表，TiDB 会使用一个隐式的自增 rowid，大量 INSERT 时会把数据集中写入单个 region，造成写入热点。 通过设置 SHARD_ROW_ID_BITS 可以把 rowid 打散写入多个不同的 region，缓解写入热点问题。 但是设置的过大会造成 RPC 请求数放大，增加 CPU 和网络开销。

SHARD_ROW_ID_BITS = 4 代表 16 个分片， SHARD_ROW_ID_BITS = 6 表示 64 个分片，SHARD_ROW_ID_BITS = 0 就是默认值 1 个分片 。

CREATE TABLE 语句示例: CREATE TABLE t (c int) SHARD_ROW_ID_BITS = 4;

ALTER TABLE 语句示例： ALTER TABLE t SHARD_ROW_ID_BITS = 4;

(4)join方式介绍：

a.Hash Join：

理论概述：会读取2个表全部数据，两表中的小表（称S）作为hash表，然后去扫描另一个表(称M)的每一行数据，用得出来的行数据根据连接条件去映射建立的hash表，hash表是放在内存中的，

这样可以很快的得到对应的S表与M表相匹配的行。

使用前提：无条件。

特点：消耗大量内存和计算时间。

b.Sort Merge Join：

理论概述：分别从两个表中取出一行数据进行匹配，如果合适放入结果集，不匹配将较小的那行丢掉继续匹配另一个表的下一行，依次处理直到将两表的数据取完。

使用前提：首先需要对两个表按照关联的字段进行排序，排序顺序一致，而且关联条件都被索引。

特点：内存消耗少，时间较快，但是创建索引时间较慢。

c.Index Look up Join：

理论概述：首先读取左表的所有行，然后根据右边的索引，左表中的数据去与右表的数据逐行匹配。

使用前提：满足右表的关联值被索引。

特点：内存消耗少，时间较快，通用。

(5)join方式选择：

(6)SQL示例：

select /*+ TIDB_SMJ(oi, od) */pi.name,pt.model_name,sum(od.pay_sum) as pay_sum
from xxsc_xxsc.order_info oijoin xxsc_xxsc.order_detail od on oi.id = od.order_idjoin xxsc_xxsc.park_ticket pt on od.goods_code = pt.model_codejoin xxsc_xxsc.park_info pi on pt.park_id = pi.id
where oi.create_time >= '2020-06-01 00:00:00'and oi.create_time <= '2020-06-04 23:59:59'and oi.bus_type in ('B', 'C')and oi.pay_status = '1'and oi.deleted = 'F'
group by pi.name,pt.model_name

如图，oi，od都是大表，但是对oi有过滤条件，使oi大表变小表，oi与od的关联条件order_id有索引，所以此处可以使用Index Look up Join

如果使用Sort Merge Join，反而导致oi表无法走idx_create_time索引，全表扫描，无法查出

如果使用Hash Join，会将oi与od放入内存中，本身是为了两张小表关联提升查询效率的，这样做反而会消耗大量内存并且无法查出想要的数据

smj:

hj:

INLJ: