TiDB 亿万级别复杂查询优化

🪐 TiDB 技术问题
, ,
1 
CREATE TABLE `waybill` (
  `col_1` bigint NOT NULL,
  `col_2` timestamp NULL DEFAULT NULL,
  `col_3` timestamp NULL DEFAULT NULL,
  `col_4` varchar(32) DEFAULT NULL,
  `col_5` varchar(32) DEFAULT NULL,
  `col_6` bigint DEFAULT NULL,
  `col_7` bigint DEFAULT NULL,
  `col_8` bigint DEFAULT '-1',
  `col_9` bigint DEFAULT '-1',
  `col_10` varchar(64) DEFAULT NULL,
  `col_11` varchar(255) DEFAULT NULL,
  `col_12` varchar(255) DEFAULT NULL,
  `col_13` varchar(16) DEFAULT NULL,
  `col_14` varchar(1024) DEFAULT NULL,
  `col_15` varchar(1024) DEFAULT NULL,
  `col_16` varchar(50) DEFAULT NULL,
  `col_17` varchar(50) DEFAULT NULL,
  `col_18` varchar(50) DEFAULT NULL,
  `col_19` varchar(50) DEFAULT NULL,
  `col_20` varchar(50) DEFAULT NULL,
  `col_21` varchar(50) DEFAULT NULL,
  `col_22` timestamp NULL DEFAULT NULL,
  `col_23` timestamp NULL DEFAULT NULL,
  `col_24` timestamp NULL DEFAULT NULL,
  `col_25` timestamp NULL DEFAULT NULL,
  `col_26` timestamp NULL DEFAULT NULL,
  `col_27` int DEFAULT NULL,
  `col_28` int DEFAULT NULL,
  `col_29` int DEFAULT NULL,
  `col_30` int DEFAULT NULL,
  `col_31` tinyint(1) DEFAULT '1',
  `col_32` timestamp NULL DEFAULT NULL,
  `col_33` varchar(100) DEFAULT NULL,
  `col_34` int DEFAULT '0',
  `col_35` varchar(1024) DEFAULT NULL,
  `col_36` varchar(255) DEFAULT NULL,
  `col_37` varchar(64) DEFAULT NULL,
  `col_38` varchar(64) DEFAULT NULL,
  `col_39` varchar(255) DEFAULT NULL,
  `col_40` bigint DEFAULT NULL,
  `col_41` int DEFAULT NULL,
  `col_42` timestamp NULL DEFAULT NULL,
  `col_43` varchar(255) DEFAULT NULL,
  `col_44` bigint DEFAULT NULL,
  `col_45` varchar(255) DEFAULT NULL,
  `col_46` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`col_1`)
  /*T![clustered_index] CLUSTERED */,
  KEY `index_col_10` (`col_10`) COMMENT 'index_col_10索引',
  KEY `index_col_6_col_2` (`col_6`, `col_2`) COMMENT 'index_col_6_col_2索引',
  KEY `index_col_11` (`col_11`(20)) COMMENT 'index_col_11索引',
  KEY `index_col_14` (`col_14`(30)) COMMENT 'index_col_14索引',
  KEY `index_col_12` (`col_12`(30)) COMMENT index_col_12索引',
  KEY `index_col_47` (`col_47`) COMMENT 'index_col_47索引',
  KEY `index_col_26` (`col_26`) COMMENT 'index_col_26索引'
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

我有如上的表数据量大概在2亿 其中类型为timestamp的字段都有可能进行排序 而且其中有十五个字段左右可以参与范围查询或者点查 现在发现这么复杂的条件查询就算加上很多索引 tidb有时候也根本查不动 有什么更好的办法吗 难道对于这种复杂查询最终只能把数据放到ES进行查吗

2

试试 tiflash

1 个赞
3

【资源配置】进入到 TiDB Dashboard -集群信息 (Cluster Info) -主机(Hosts) 截图此页面

截图看看你的资源如何~

4

建议先在测试环境对你的模型和SQL进行验证、分析，复杂SQL的查询任务可以用TiFLash 来查询，这个组件就是专门用来处理AP负载的

5

我们其他项目组之前有对tiflash进行验证 上线后发现tiflash的QPS才几十就把服务器搞崩了 我们的系统就是需要实时分析的 所以不太敢用

6

image
image824×384 7.73 KB

7

请问您有在生产使用过tiflash吗 经过其他项目组踩坑我对他的性能有点持怀疑态度

8

Tiflash应对的场景是大量数据的实时分析能力，一般场景下都没问题。

复杂大SQL的实时分析+高并发，这个能力要求其实是很高的，业界能支撑的数据库如果有，我们也想学习了解下。如果仅仅考虑能执行SQL，你可以考虑试用doris、CK、HBASE等，但是这些库你需要先把实时数据迁移过来，迁移的管理成本就会比较高。

TiDB（TiKV+TiFlash）是一个折中的方案，支持实时OLTP+OLAP，即HTAP，集群内部包含行存和列存两种引擎。如果打算采用这个方案，建议先测试验证，然后你可能需要针对性去修改SQL、引入中间表等，降低SQL复杂度，使得满足这个需求。

总之，要看你具体的业务需求和访问负载、资源支撑等情况，新业务几乎不可能不作调整就可以上一个新数据库的。

1 个赞
9

就这么点资源去做超过2亿表的复杂大SQL，肯定会有问题的，目前来看是面向OLTP业务部署的集群，如果要上大SQL业务，建议要根据业务的实际情况去合理分配资源才行。

10

你这个资源，跑2亿级别复杂sql确实有点难，你每个tidb都只有16G的内存，如果只是点查或者小范围查询还好，如果是大范围查询或者汇总类sql，不管你的sql在哪个tidb上，都不好跑吧。。。。
可以先把你最常用的sql的真实执行计划发出来，看下到底是慢在哪一块。

11

TiFlash 是针对 AP 场景的，所以 QPS 确实不会很高。一般都是结合 TiKV 使用，TiKV 用来应对高频的 TP 查询， TiFlash 用来应对低频的 AP 查询。如果既是 AP 查询，又很高频，其实也可以通过堆 TiFlash 机器个数来应对。

12

TiKV 和 TiFlash 并不是割裂必须2选1的，其实可以选择高频高优先级的查询场景，对相应的字段正常加索引，针对低频低优先级的查询场景， 完全可以让走 TiFlash 来硬抗。

13

把具体的 SQL 拿出来，拿几个出来看看

14 
SELECT *
from waybill
where col_6 = 263630223654780928
and col_2<= '2025-03-11 23:59:59' 
and col_2>= '2025-01-11 00:00:00'
and col_47 in (171,289847013007171584)
and col_18 in ('US')
and col_19 in ('GB')
order by col_2
limit 100;

您好 确实发现普通的点查和范围查速度都是在毫秒级别 如上 只有col_6 以及col_2参与查询 并且两个字段有联合索引情况下是很快的 增加查询条件col_47其实也挺快
但只要再加上一个col_18(col_18是国家简码字段 全世界国家只有300个不到 区分度不高) 这个时候感觉查询就突然要达到30s以上 col_18区分度太低 所以加不加索引没啥区别 现在主要有以下问题

问题1: 我们主要是单表的复杂查询 其他还有十个左右字段都可以用于查询和排序的 只要加上区分度比较低的数据进入查询条件 查询速度会骤降 就算给这字段加上索引也是没用 所以比较疑惑tiflash如果把配置再升高能否解决这种区分度低字段的筛选问题

问题2: 点查、小范围查询字段加上limit确实很快 如果只是把*改成count(*) 查询速度也从毫秒级别增加到30s左右 考虑到tiflash是用来做AP数据分析的 如果是目前单表2亿数据的分析需要什么配置能够抗住呢 如果吃的配置太高 我这边还不如采取将[复杂查询+分析]放到ES进行查询

15

explain analyze 看下语句的执行计划。

16

CREATE GLOBAL BINDING for SELECT * FROM t2 as 别名HERE a > 1 AND b = 1
USING SELECT /*+ use_index(‘别名’, ‘索引名称’) */ * FROM t2 as 别名 WHERE a > 1 AND b = 1;

试试绑定吧，强制走某个索引

17 
SELECT *
from test
where col_6 = 263630223654780928
and col_2<= '2025-03-11 23:59:59' 
and col_2>= '2025-01-11 00:00:00'
and col_47 in (171,289847013007171584)
and col_18 in ('US')
order by col_2
limit 100;

Limit_12	100.00	root		offset:0, count:100
└─IndexLookUp_25	100.00	root		
  ├─IndexRangeScan_22(Build)	11765.00	cop[tikv]	table:test, index:index_col_6_col_2(col_6, col_2)	range:[263630223654780928 2025-01-11 00:00:00,263630223654780928 2025-03-11 23:59:59], keep order:true
  └─Selection_24(Probe)	100.00	cop[tikv]		eq(t3_general.test.col_18, "US"), in(t3_general.test.col_47, 171, 289847013007171584)
    └─TableRowIDScan_23	11765.00	cop[tikv]	table:test	keep order:false
查询耗时: 48.055598 sec

强行指定索引没什么用 直接超过2分钟了 还是tidb自己选择的最快

18

首页有个根据查询条件查询出数据 然后根据其中某个状态枚举进行分组求count的功能 根据查询条件查询出数据偏向于TP 分组求count偏向于AP 但目前发现TP查询在区分度比较低的字段加入查询条件会很慢 区分度太低加了索引也没有用 由于在首页这个AP查询也会很高频 所以害怕Tiflash抗不住 就算堆了机器抗住怕成本太高

19

explain analyze这样看真实的执行计划，不过看你这个sql，一个是返回字段必须是*吗？不能确定到返回需要的字段？另外这4个字段上面没有联合索引吗？有的话，应该好一点

20

返回不是*也是很慢 毕竟就算只返回部分字段很难用上覆盖索引的 查询的时候条件是一直在变 查询的字段也是一直在变化 全由用户选择 就算给这四个字段建上联合索引实现覆盖 但是用户一变化查询条件就查询不出了 只能给区分度高的字段加上单独的索引 但是当区分度低的字段加入查询条件这次查询就很慢了 不知道这种情况能怎么解决