TiDB模糊查询速度过慢

TiDB小萌新 · 2023 年7 月 17 日 06:44

【 TiDB 使用环境】生产环境
【 TiDB 版本】5.4.0
【 TiDB 配置】8台数据库8c16G集群
TiDB模糊查询时间过慢数据量一亿，请教各位大神，这种查询还有优化空间吗？

有猫万事足 · 2023 年7 月 17 日 06:55

需要执行计划啊，兄弟。
不然看结果是不多，不知道原因在哪里。

TiDB小萌新 · 2023 年7 月 17 日 07:01

这个是加强制走tiflash的

这个是没有加强制走Tiflash的

tidb菜鸟一只 · 2023 年7 月 17 日 07:17

上次好像看到过了，我的建议是，还想提速，只有在所有你查询的字段，过滤的字段，包括排序的字段上建一个联合索引吧。。。这样不用回表，才能更快

TiDB小萌新 · 2023 年7 月 17 日 07:18

上次按照您提的建议已经加上去了组合索引，现在列表不加查询条件很快。
但是，我这个sql里面可能会有很多的like查询条件，而且like是不走索引的吧

有猫万事足 · 2023 年7 月 17 日 07:22

tiflash查询你要继续优化，如果不止一台tiflash可以试试MPP模式。
你的执行计划里面看到了ExchangeSender ，可已经已经用上MPP模式了。

https://docs.pingcap.com/zh/tidb/stable/use-tiflash-mpp-mode

其实我感觉你这个查询，tikv可能更适合，特别是id_card这个字段应该是一个特别适合索引的字段。
但查询并没有走，原因可能是没有索引，或者%idcard%，前面这个%造成的。
如果可能，idcard有索引+idcard%这样添加条件，应该能利用到索引。
其他的条件如果能快速的锁定一个小范围值的，都可以加索引试试看。

TiDB小萌新 · 2023 年7 月 17 日 07:23

CREATE TABLE license (
id varchar(20) NOT NULL,
create_time bigint(20) DEFAULT NULL,
update_time bigint(20) DEFAULT NULL,
is_deleted int(11) DEFAULT ‘0’,
user_id varchar(100) DEFAULT NULL COMMENT ‘用户id’,
zlb_user_id varchar(100) DEFAULT NULL COMMENT ‘浙里办id’,
ins_id varchar(20) DEFAULT NULL,
org_id varchar(20) DEFAULT NULL COMMENT ‘成果目录id’,
license_type varchar(255) DEFAULT NULL COMMENT ‘成果类型’,
license_data json DEFAULT NULL COMMENT ‘成果字段详情’,
org_license_type varchar(30) DEFAULT NULL COMMENT ‘成果org_license细分’,
course_id varchar(20) DEFAULT NULL COMMENT ‘课程id’,
course_name varchar(100) DEFAULT NULL COMMENT ‘课程名称’,
tran_time bigint(0) DEFAULT ‘0’ COMMENT ‘转化时间’,
apply_file json DEFAULT NULL COMMENT ‘申请文件’,
mec_id varchar(50) DEFAULT NULL COMMENT ‘机构id’,
status varchar(20) DEFAULT NULL COMMENT ‘审核状态’,
tran_status varchar(30) DEFAULT NULL COMMENT ‘转换状态’,
first_mec_id varchar(30) DEFAULT NULL COMMENT ‘一级机构id’,
second_mec_id varchar(30) DEFAULT NULL COMMENT ‘二级机构id’,
license_code varchar(50) DEFAULT NULL COMMENT ‘成果代码’,
hash_value varchar(256) DEFAULT NULL COMMENT ‘区块链hash’,
source varchar(30) DEFAULT NULL,
id_card varchar(50) DEFAULT NULL COMMENT ‘身份证’,
credit_hour varchar(30) DEFAULT NULL,
user_name varchar(50) DEFAULT NULL,
user_code varchar(30) DEFAULT NULL,
org_code varchar(50) DEFAULT NULL,
license_name varchar(100) DEFAULT NULL,
career_qualification_data json DEFAULT NULL COMMENT ‘证书，课程，专业信息’,
operation_phone varchar(50) DEFAULT NULL COMMENT ‘操作人手机号’,
operation_name varchar(50) DEFAULT NULL COMMENT ‘操作人姓名’,
mec_name varchar(50) DEFAULT NULL COMMENT ‘机构名称’,
is_old tinyint(3) DEFAULT ‘0’ COMMENT ‘是否老系统’,
old_id varchar(50) DEFAULT NULL COMMENT ‘老系统id’,
KEY zlbuserid_idx (zlb_user_id),
PRIMARY KEY (id) /*T![clustered_index] NONCLUSTERED */,
KEY id_card_idx (id_card),
KEY create_time_idx (create_time),
KEY idx_delete_type_mec_create (is_deleted,org_license_type,mec_id,create_time),
KEY idx_delete_type_first_mec_create (is_deleted,org_license_type,first_mec_id,create_time),
KEY idx_deleted_orgId (is_deleted,org_id),
KEY idx_delete_type_second_mec_create (is_deleted,org_license_type,second_mec_id,create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

TiDB小萌新 · 2023 年7 月 17 日 07:24

我这个表是创建的索引的，因为%idCard%这样他不走索引，检索数据量大，所以才hint用tiflash的，但是这样分页查询也要十几秒。我有两个tiflash的节点。

已经开启mmp了

redgame · 2023 年7 月 17 日 07:34

在被模糊查询的字段上创建前缀索引

TiDB小萌新 · 2023 年7 月 17 日 07:41

前缀索引对%idCard%模糊查询有用吗？

tidb菜鸟一只 · 2023 年7 月 17 日 07:47

like '%…%'这种确实是走不了索引的，like '…%'这种还行，tidb是不支持全文索引的， id_card这个字段确定需要like '%…%'这种查询条件吗？

TiDB小萌新 · 2023 年7 月 17 日 07:48

是的，业务上要求的需要，还有另外类似user_name的也需要 '%…%'这种查询

zhanggame1 · 2023 年7 月 17 日 08:10

%…%这种like无法优化，可以看看表达式索引，有没有可能把查询条件转换成某个表达式，然后创建索引
CREATE INDEX | PingCAP 文档中心

啦啦啦啦啦 · 2023 年7 月 17 日 08:14

我们这种like %%的都是走es的

tidb菜鸟一只 · 2023 年7 月 17 日 08:27

实在不行，把数据同步到Elasticsearch上试试吧。。。tidb本身对这种sql没啥优化的空间了

TiDB小萌新 · 2023 年7 月 17 日 08:27

好的吧，我跟我们业务部门沟通下，不行了只能上es了

Kongdom · 2023 年7 月 17 日 09:24

看你这个查询好像不需要用like吧

cassblanca · 2023 年7 月 17 日 09:45

Tidb单纯使用 Like 会导致查询到有歧义的结果或满足搜索条件的结果无法返回，从Tidb索引角度无法更加优化了。更好的方式是通过其他方式，使用全文检索，比如ES