同一个表的索引和主键，执行的时间差异巨大

🪐 TiDB 技术问题 性能调优
1

【 TiDB 使用环境】
4台tikv 64G
三台tidb 96G SSD+SAS超融合
【概述】 下面SQL没有命中索引，执行需要十几分钟
select c.* from tb_card c
where (c.c_org_id = ‘8’ and c.c_cardno>‘210007040764’ and c.c_cardno<‘210008913257’ )
and c.c_cardno like repeat(’_’,length(‘210008913257’));
【背景】使用use index可以使用索引
KEY IX_tb_card_org (c_org_id,c_cardno)，这个索引本来是要删除的
PRIMARY KEY (c_org_id,c_cardno)
【现象】
use index (IX_tb_card_org) 执行需要几秒钟

use index (PRIMARY ) 执行需要十几分钟
tidb_decode_plan(‘wQJMMAkzMF84CTAJMTEzMjkwOTMuNjAJASQxCQoxCTFfNwkxThwAfGVxKDEyLCBsZW5ndGgoZW5qb3ljcm0udGJfY2FyZC5jBQcUbm8pKSwgoisAGAoyCTEzXzUBcaA0MTYxMzY3CXRhYmxlOmMsIGluZGV4OlBSSU1BUlkoY19vcmdfaWQsIBVpbCwgcmFuZ2U6KCI4IiAiMjEwMDA3MDQwNzY0IiwZE2g4OTEzMjU3IiksIGtlZXAgb3JkZXI6ZmFsc2UB7ggwXzYBfkYLAQB0EYhCNgA=’)
【问题】 使用两个索引为何差异这么大？
【业务影响】
业务系统性能抖动明显
【TiDB 版本】
V4.0.0
【应用软件及版本】

【附件】 相关日志及配置信息

  • TiUP Cluster Display 信息
  • TiUP CLuster Edit config 信息

监控（https://metricstool.pingcap.com/)

  • TiDB-Overview Grafana监控
  • TiDB Grafana 监控
  • TiKV Grafana 监控
  • PD Grafana 监控
  • 对应模块日志（包含问题前后 1 小时日志）

若提问为性能优化、故障排查类问题，请下载脚本运行。终端输出的打印结果，请务必全选并复制粘贴上传。

1 个赞
2

表设计 以及 索引 信息能放出来么？

另外 explain 和 explain analyze 的执行计划，可以放出来么？

这个表的数据规模最好也提供一下

1 个赞
3

– EnjoyCRM.tb_card definition

CREATE TABLE tb_card (
c_cardno varchar(20) COLLATE utf8_bin NOT NULL COMMENT ‘卡号’,
c_grade varchar(10) COLLATE utf8_bin NOT NULL DEFAULT ‘’ COMMENT ‘卡类别（主卡、副卡）’,
c_cardname varchar(20) COLLATE utf8_bin NOT NULL DEFAULT ‘’ COMMENT ‘卡名称，对应于tb_card_def’,
c_status varchar(20) COLLATE utf8_bin NOT NULL DEFAULT ‘’ COMMENT ‘卡状态（初始、正常、挂失、作废） => tb_syscfg[卡状态]’,
c_mid varchar(42) COLLATE utf8_bin NOT NULL DEFAULT ‘’ COMMENT ‘介质内容’,
c_s_date datetime NOT NULL COMMENT ‘生效日期’,
c_e_date datetime NOT NULL COMMENT ‘过期日期’,
c_customer varchar(20) COLLATE utf8_bin NOT NULL DEFAULT ‘’ COMMENT ‘顾客编码’,
c_psword varchar(100) COLLATE utf8_bin NOT NULL DEFAULT ‘’ COMMENT ‘密码’,
c_mk_store_id varchar(10) COLLATE utf8_bin NOT NULL DEFAULT ‘’ COMMENT ‘发卡机构’,
c_mk_store_name varchar(40) COLLATE utf8_bin NOT NULL DEFAULT ‘’ COMMENT ‘发卡机构名称’,
c_modify_userno varchar(10) COLLATE utf8_bin NOT NULL DEFAULT ‘’ COMMENT ‘最后修改人员’,
c_modify_username varchar(50) COLLATE utf8_bin NOT NULL DEFAULT ‘’ COMMENT ‘最后修改人员名称’,
c_modify_dt datetime NOT NULL COMMENT ‘最后修改时间’,
c_card_note varchar(120) COLLATE utf8_bin NOT NULL COMMENT ‘卡备注’,
c_is_entity varchar(10) COLLATE utf8_bin NOT NULL DEFAULT ‘’ COMMENT ‘是否已办理实体卡’,
c_bind_psword varchar(100) COLLATE utf8_bin NOT NULL DEFAULT ‘’ COMMENT ‘绑定密码，用于和电商账户绑定’,
c_org_id varchar(10) COLLATE utf8_bin NOT NULL DEFAULT ‘’ COMMENT ‘账号定义组织’,
c_org_name varchar(50) COLLATE utf8_bin NOT NULL DEFAULT ‘’ COMMENT ‘账号定义组织名称’,
c_store_id text COLLATE utf8_bin NOT NULL COMMENT ‘账号定义机构’,
c_store_name text COLLATE utf8_bin NOT NULL COMMENT ‘账号定义机构名称’,
c_check_status varchar(20) COLLATE utf8_bin NOT NULL DEFAULT ‘尚未复核’ COMMENT ‘复核状态’,
c_check_userno varchar(10) COLLATE utf8_bin NOT NULL DEFAULT ‘’ COMMENT ‘复核人’,
c_check_username varchar(50) COLLATE utf8_bin NOT NULL DEFAULT ‘’ COMMENT ‘复核人姓名’,
c_check_dt datetime DEFAULT NULL COMMENT ‘复核时间’,
c_extend varchar(100) COLLATE utf8_bin NOT NULL DEFAULT ‘’ COMMENT ‘卡扩展1’,
c_extend2 varchar(100) COLLATE utf8_bin NOT NULL DEFAULT ‘’ COMMENT ‘卡扩展2’,
c_extend3 varchar(100) COLLATE utf8_bin NOT NULL DEFAULT ‘’ COMMENT ‘卡扩展3’,
c_extend4 varchar(100) COLLATE utf8_bin NOT NULL DEFAULT ‘’ COMMENT ‘卡扩展4’,
c_extend5 varchar(100) COLLATE utf8_bin NOT NULL DEFAULT ‘’ COMMENT ‘卡扩展5’,
KEY index_c_customer (c_customer),
KEY index_c_mid (c_mid),
KEY index_c_cardno (c_cardno),
KEY index_c_cardname (c_cardname),
KEY IX_tb_card_customer (c_org_id,c_customer,c_status,c_grade),
KEY IX_tb_card_org (c_org_id,c_cardno),
KEY index_status (c_status,c_grade),
KEY inx_modify (c_modify_dt),
PRIMARY KEY (c_org_id,c_cardno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT=‘卡档案表’;

1 个赞
4

慢的：
explain analyze
select c.* from tb_card c use index (PRIMARY )
where (c.c_org_id = ‘8’ and c.c_cardno>‘210007040764’ and c.c_cardno<‘210008913257’ )
and c.c_cardno like repeat(’’,length(‘210008913257’));
id |estRows |actRows|task |access object |execution info |operator info |memory |disk|
-------------------------±----------±------±--------±-----------------------------------------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------±--------------------------------------------------------------±------------------±—+
IndexLookUp_8 |11331358.40|1382934|root | |time:2.531820843s, loops:1352, rpc num: 4, rpc max:361.981307ms, min:166.707005ms, avg:241.492225ms, p80:361.981307ms, p95:361.981307ms, proc keys max:502465, p95:502465| |65.9934778213501 MB|N/A |
├─Selection_7(Build) |11331358.40|1382934|cop[tikv]| |proc max:318ms, min:148ms, p80:318ms, p95:318ms, iters:1369, tasks:4 |like(enjoycrm.tb_card.c_cardno, "__________", 92) |N/A |N/A |
│ └─IndexRangeScan_5 |11378714.35|1382934|cop[tikv]|table:c, index:PRIMARY(c_org_id, c_cardno)|proc max:269ms, min:128ms, p80:269ms, p95:269ms, iters:1369, tasks:4 |range:(“8” “210007040764”,“8” “210008913257”), keep order:false|N/A |N/A |
└─TableRowIDScan_6(Probe)|11331358.40|1382934|cop[tikv]|table:c |proc max:223ms, min:0s, p80:43ms, p95:83ms, iters:5029, tasks:994 |keep order:false |N/A |N/A |
快的：
explain analyze
select c.* from tb_card c use index (IX_tb_card_org )
where (c.c_org_id = ‘8’ and c.c_cardno>‘210007040764’ and c.c_cardno<‘210008913257’ )
and c.c_cardno like repeat(’’,length(‘210008913257’));
id |estRows |actRows|task |access object |execution info |operator info |memory |disk|
-------------------------±----------±------±--------±------------------------------------------------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------±--------------------------------------------------------------±-------------------±—+
IndexLookUp_8 |11331358.40|1382934|root | |time:3.063540656s, loops:1352, rpc num: 3, rpc max:508.369772ms, min:2.940506ms, avg:293.591546ms, p80:508.369772ms, p95:508.369772ms, proc keys max:795138, p95:795138| |64.17386627197266 MB|N/A |
├─Selection_7(Build) |11331358.40|1382934|cop[tikv]| |proc max:440ms, min:0s, p80:440ms, p95:440ms, iters:1364, tasks:3 |like(enjoycrm.tb_card.c_cardno, “____________”, 92) |N/A |N/A |
│ └─IndexRangeScan_5 |14164198.00|1382934|cop[tikv]|table:c, index:IX_tb_card_org(c_org_id, c_cardno)|proc max:373ms, min:0s, p80:373ms, p95:373ms, iters:1364, tasks:3 |range:(“8” “210007040764”,“8” “210008913257”), keep order:false|N/A |N/A |
└─TableRowIDScan_6(Probe)|11331358.40|1382934|cop[tikv]|table:c |proc max:279ms, min:0s, p80:48ms, p95:92ms, iters:4921, tasks:968 |keep order:false |N/A |N/A |

1 个赞
5

执行计划可以截图 这 看的眼花

6

image
image1566×374 34 KB
image
image1586×353 34.4 KB

1 个赞
7

不加提示的查询计划：

image
image1593×331 28.9 KB

1 个赞
8

fullscan…没用到任何索引 你确认俩表 结构都一样吗

1 个赞
9

看上去索引不是最佳, index lookup了

1 个赞
10

默认是不走任何索引？ 那索引的设定，符合你查询的需求么？

然后这个表数据变动大不大？
你上个 explain 的执行计划看看

1 个赞
11

不是两表，是同一个表

1 个赞
12

走主键索引的是一直都慢还是第一次慢

1 个赞
13

请看标题，问题点是主键慢，普通索引快。主键昨天晚上删除重建了，还是一样慢

1 个赞
14

为什么会创建一个和复合主键一样的复合索引 ？ 仅限于测试性能吗？从执行计划看 ，差异并不大，在 200ms 左右。这部分可能会和主键、索引 的数据分布有一定关系

image
image6158×1742 922 KB

15

因为主键慢，所以才建了一个和主键一样的复合索引

16

走主键一直都慢，不只是第一次慢，感觉和全表扫描差不多

17

因为不是聚簇索引，也不是整形主键，然后这样的联合索引效果不明显？ 囧

@qq24681430 你这个表设计有可以调整的空间么？

18

可以把走主键时慢SQL日志信息贴下

19

这是会员表，会有一些变动的

20

调整空间您指的哪个方面？