tinyint 类型字段查询条件=2时查询效率极慢

TiDBer_7OFQFtbY · 2022 年5 月 11 日 06:48

【 TiDB 使用环境】生产环境【 TiDB 版本】5.3 【遇到的问题】查询时候 tinyint类型过滤条件为2的时候，查询效率严重慢【复现路径】select* from xxx where a=1 limit 10,很快，a=2很慢
【问题现象及影响】

【附件】

请提供各个组件的 version 信息，如 cdc/tikv，可通过执行 cdc version/tikv-server --version 获取。

h5n1 · 2022 年5 月 11 日 07:06

表结构，2个SQL的explain analyze执行计划贴下，这个表中是否经常有数据更新或删除。

TiDBer_7OFQFtbY · 2022 年5 月 11 日 07:10

等于1的情况下

等于2的情况下耗时30s

没有删除，只有插入

h5n1 · 2022 年5 月 11 日 07:15

从actrows上看=2的数据(1199万)比=1的(8160条)明显要多很多。limit 没有下推到tikv导致=2时扫描大量数据返回后才应用limit，可以试试HINT ：/*+ LIMIT_TO_COP() */ 或重新收集表统计信息看看

TiDBer_7OFQFtbY · 2022 年5 月 11 日 07:34

那去掉clxl 条件也很快又是为什么？加上你发的这个也你看看

h5n1 · 2022 年5 月 11 日 07:42

这啥意思?能贴下SQL吗

TiDBer_7OFQFtbY · 2022 年5 月 11 日 07:44

就是上面的结果
SELECT
/*+ LIMIT_TO_COP() */
a.createtime xjsj,

a.createtime xjsjstr,

a.createtime,

a.xjjf,

a.dfyy,

a.inspectrecordid,

a.inspectrecordzzid,

a.ljlx,

a.tenantgroupid,

a.tenantuserid,

a.communityid,

a.sjly,

a.sbbh,

a.sblx,

a.bfb,

a.jlwybz,

a.bizid,

a.sblj

FROM

h_inspect_record a

WHERE

1 = 1

AND a.tenantid = ‘2503fce6-b923-42ab-9e2a-1d399fae712d’

– and cjlx=2

AND a.productid = ‘58fd4d48-cbe5-430c-8cca-8b5b39469bd5’

AND a.createtime >= ‘2021-05-01 00:00:00’

AND a.createtime <= ‘2022-05-11 23:59:59’

AND a.regionid IN (

'153e50d3-f092-4277-bab9-98d207a380d1',

'd646b145-3179-486a-96e8-24c7f74381a4',

'5b69d6ad-cdc7-4f4b-aae0-65d1061b6ba5',

'a6ce914c-98ef-48f1-85e1-8874518bd875',

'953f1bf3-b347-4aa9-9663-1df85375e3c7',

'69caaa7f-b953-489a-81fd-60804800a87f',

'a1f196cc-32fc-4aa7-b2e6-d085ae01fe33',

'1935d859-ac28-4636-8804-b040f9f67a07',

'eb96102d-d413-4b52-862b-e9c334c68a4d',

'eef47690-3c7e-431e-bb1a-e67290bab873',

'2e5f8791-b262-40b4-bb97-856547fae761',

'545abb71-0d28-4650-a4ed-0752d5b0f814',

'13d1364e-fedf-4128-8678-b1ad9f0d6b4e',

'a8e0fd48-e167-417b-bd38-4617fe64519a',

'29c63c47-ff26-4108-9be4-3ec8aa4a90d8',

'c2d11eef-aa07-4abd-b044-9f84d0b24187',

'cd3c7fa5-903b-4c9f-8123-bd751801a4aa',

'79853d1a-53d5-4f70-a8a6-fa07ca148f5f',

'7afdfb7c-4849-405f-bbb8-455ef12d4d13',

'cf42c0d9-63cd-41b2-869c-4942d190a09d',

'60c77476-05b7-431f-a5b8-c0398c6dd980',

'68e785f4-e12b-4d98-9460-abb7cf020b59',

'6580a63f-32fe-42c2-8673-58e119c7f3d2',

'7b6410bd-efc0-4d77-8bdb-cc2d95457164',

'42b5fb23-6939-4425-a1da-ace5fbfd5a2f',

'ce8ff8cc-2ea0-43b0-98d7-08524bfd3a91',

'4ec63a07-3ac1-4606-a02b-372006eebb1d',

'8a1ceed8-4d2f-4be2-b42d-c4589ddd9c87',

'ca7f046d-8ccb-4fa4-a72a-62135b4ad78f',

'b87153e4-b729-4ee3-b1e0-8cf1008c1859',

'6890342c-eab3-401a-bf95-ab6672a72915',

'cea4bfce-a94f-460f-a463-5cbedea99de2',

'f4a8fbf4-3f5c-4eb7-8387-407c3196cd38',

'84cb1a17-3299-479b-aa53-0b84cd161271',

'c9841ad1-4c77-4563-8bfc-6a1ae040854c',

'c0018222-136d-400b-bf76-f9242a9fc390',

'b77074ec-649c-410c-b741-e264836566e4',

'011bd793-8e1e-42f0-b8ee-09bc141865c8',

'0c00607e-1f94-4bf8-ba85-1afc552eb546',

'23bd0bdf-853f-4fe4-8424-7deac8d9866a',

'e09c7c98-c517-4e88-9133-f2e4dbd00fd2',

'12cbc5aa-e595-4528-b756-a10d136cde4a',

'19e22c47-0fae-483b-a7d2-6151df7ad956',

'b5b02a7d-fbf6-4726-a4a6-b571b1a57d93',

'54e7aefe-1025-419d-9bfb-d3dc8b0522af',

'd5203eba-6ff3-4853-b99a-f1610fcb3933',

'574b1676-da61-404e-9fa1-e8852e413936',

 'd59d5af9-2b4a-47b1-8845-270153ff4a19',

'4009984c-0b07-4157-9e7e-cfea020e6d05',

'f77c387e-2d8b-4f42-a5d4-243eda5c05c5',

'30cdcee6-7286-4342-8878-cf6c25a9ab91',

'44f5efa4-7945-4ba1-becd-479511adcada',

'0b6adb4b-3dcf-4dca-9b0d-17de777d16bc',

'b494aaf2-1689-4c37-9526-d7743400d82c',

'd3e0720f-7cb1-4dda-ae50-78e944b51b77',

'fe3da29e-b6a2-4cbf-80ab-9b5062333231',

'bb7e8c2d-bd21-4cd6-97f1-4e54a831a7b4',

'fce3adbb-a9d7-425e-96e7-41be9407f8bf',

'f8d8957c-73a0-48c2-a95f-2b81d84a6a7f',

'd1daf7e2-a49c-4374-93c5-2c4b034cc5c9',

'6148515a-2af9-4302-a715-02ff5eb2605a',

'c77748c9-6c3f-4029-befc-8ba51cdf2034',

'b3772797-8f3f-48a6-9e0c-e27f168df4e6',

'1ecb2dc6-4c26-41d7-b96a-f2c52837e832',

'bd66b1eb-cf41-4651-ac4b-06c730b99e80',

'e0da0474-6127-44a7-995f-600e49bb9128',

'26f6a726-1643-4cee-a782-765963c67089',

'0ecc1391-de95-4098-9d05-d99547b55dfb',

'f0853709-dbcb-4fdb-943d-701d1586cd5f',

'b298ad08-2377-4554-9854-993ef9425bcf',

'f32a6633-4d95-47fe-8207-38f71bd8b411',

'43d485d5-91ad-4c6f-bd96-255c6fe60f64',

'b8d1f287-32e4-44b5-9475-fcd7de91e81a',

'647c3ba6-58ea-4064-bb32-1af691e5192f',

'cb439d6e-ca34-4423-828c-7a74b157a2c3',

'bc1d58b8-ef99-42b6-aa9f-2223f00b4b29',

'8908923c-60b9-4081-91e5-f9820f7f38a7',

'0d2bc2b1-861b-488c-8ef7-6135365afe1c',

'aa04c521-b4fa-4235-a5c8-de7fce19f3f1',

'd3ef291e-43ba-40aa-9e03-33b12003f1e8',

'cea8ee45-e2fa-4d8e-a29b-fb6570473215',

'ccd6c013-be22-4f34-8644-01d6fd0a37fc',

'557eadfa-caf6-427f-b03d-d27a7f93c726',

'df91cb10-db33-4e04-bb3b-fa210862bd9c',

'26fad545-bbe9-4046-b2eb-f1c5ee4eb5d3',

'e47be83b-1e48-4168-b368-dd640a7777f2',

'3dc4b553-3ef4-473d-bdac-5cd66e9afdaa',

'515c5798-1f23-44f7-ba2d-fcb18448533f',

'3c22dee1-d526-4f39-9317-939688c2f00a',

'16077cfb-6967-4a37-800d-6c8648932d43',

'a0c47d08-cf78-4b0e-9c1b-71a779c3510b',

'40ef54fd-e278-4aa0-8b18-f9079f5f9088',

'de8c2b1d-3c34-431e-b977-150ac16c4e22',

'fc6b7730-9379-4fd9-88ba-c76d3e1293c2',

'cfdca494-331c-44d2-92d3-c2197fa305be',

'482d2ab4-b759-445f-8dfe-a411ee4d2d73',

'107f641b-6b63-42a9-afc4-de54edeb1a8a',

'fbb61b85-4c71-4f6d-bd59-a198c8d2b6cb',

'772130ab-4d7f-4b61-a62d-c97ce704096c',

'9d540de7-d973-43ea-b97b-b53f5f219f21',

'106ef29f-d92c-421a-a936-f10d918e6544',

'97ad4e20-d262-4cee-9213-ac82d6724466',

'7336a1b2-2fec-4c20-a0ca-0b4648d5d8e8',

'd01dcd25-58aa-460a-9f15-f796ad457c98',

'164553e9-4d74-469e-8357-089e0ce4c897',

'f3c32f2a-c9a1-401d-ba0c-b20b13a05a40',

'4e3197b8-cbea-4787-9649-c34a0c1b1f14',

'34650c65-cc56-4447-9603-927c08c58fea',

'9bb7b2d0-cede-4770-bded-5a92883aede6',

'c421f72b-af87-4cf7-a006-21d4d1bd295b',

'facdb4f5-fd6d-441d-a345-04ebf924bbec',

'c461b0ad-329b-46c8-9d5a-66475c4b334f',

'9840f914-ab16-48be-b00e-2947612fe77c',

'22f5a924-befd-4778-8e3a-2df2313ee7ea',

'e681ae3b-047b-4ae5-abfc-02e3e4e7866a',

'a319edb2-5654-47ce-a56d-b211bffcee77',

'1fd9f71d-3334-4b60-9820-0b8a3d1d8759',

'5378792a-91e5-4572-b68d-7d3f12ce3fd7',

'66b3baf2-52ef-4b3d-a966-7041b61c8af2',

'e2497f3e-e78c-4fef-85a7-89cdc72aeb62',

'55a8c2ea-80f7-48de-a95f-761cb1e8531e',

'9860819c-4823-4092-ad5e-881c5170bc45',

'3a235d60-7ee8-4f7d-b34c-33754254f180',

'e623cf9d-edcc-49b3-8d71-82496ea8e6c3',

'5d8da93c-8c56-435e-bf9b-d4a94ab1f7b4',

'9c5c823f-fe7e-4802-a1cc-4bf385648dc9',

'f2630778-395c-4566-a146-ec9a432d2878',

'757284ae-eae8-4a72-9df5-63d907a890ba',

'c44eedfb-a07b-4e67-b0ae-a866384ff662',

'04d3d1e6-b4e2-4c11-bcc5-487ff52f931f',

'2048c7ec-6c11-4d6d-9ad6-8fc4e88529be',

'44d9399e-af1b-4498-96e8-e9655e4b80c7',

'f353e076-2027-4ba4-80a5-0a4771fd4e18',

'5e6012fd-5db9-4b89-95f2-5e6cb8c05e40',

'dcf4033e-f822-46e0-832f-31856c41f7e4',

'9c151202-c3c3-40e3-9733-4ca8a8b1fa71',

'4ee778dd-18f1-41b0-9950-4849db8960ac',

'bf34179c-85db-4c57-ba06-1ef4fe265591',

'b12c5e8e-5015-431e-b23d-0d781ee13db0',

'f5b90a55-22f2-4d92-bb68-e3573bc91ebf',

'9cdc9673-3195-4298-8d4c-d50183fd2638',

'e29fd5e7-5894-46e5-b015-574b2cd611c8',

'94bd4539-9cfe-438f-8a7b-1805549e9a04',

'7d42d99d-cac0-461c-b122-1de3b483deb1',

'519e521b-ae72-4d12-9b3b-7cbf68634c9d',

'92e14d5f-528c-49bb-925a-5a0bb79a0e15',

'4217c1d3-3854-49de-ac34-204896a2e604',

'b5836679-f2f0-4e18-86e3-ef28225a0490',

'1a110450-b856-4dfd-b2f3-9c9719834651',

'726967eb-ef64-4d70-85a3-70527939433f',

'dcb1417e-117d-4194-b553-34cc755fbde4',

'7f2b5db2-0e45-4464-8593-90c1e3acedfb',

'995d68f2-6078-413f-b273-bc789b79da54',

'24827e5f-9b89-4e03-a909-de3052570038',

'ce4e2cbb-86cc-4718-9c86-a7c7d5823ddd',

'51f05e83-61c7-49a1-90e0-9909fa5ef69a',

'af62e3d6-6132-48c6-ab44-1f23560c6235',

'0c1f6e5a-1c38-44ff-88ec-35ec5403f2ca',

'1f9d5699-9afc-456f-8422-d4c9f334661a',

'532bde24-61d6-4ffc-8bcf-778774a0c429',

'f60f7b32-2248-42ed-9a35-2c4f225850f5',

'3643485f-0a37-4a63-8a3d-fce1386bf714',

'b21ceafe-8a65-4849-af16-7dfb2729abe3'

)

ORDER BY

a.createtime DESC

LIMIT

0, 100;

h5n1 · 2022 年5 月 11 日 08:10

show create talbe看下，你这个应该适合加个索引

TiDBer_7OFQFtbY · 2022 年5 月 11 日 08:11

CREATE TABLE h_inspect_record (
inspectrecordid varchar(36) COLLATE utf8mb4_general_ci NOT NULL,
tenantid varchar(36) COLLATE utf8mb4_general_ci NOT NULL,
productid varchar(36) COLLATE utf8mb4_general_ci NOT NULL,
regionid varchar(36) COLLATE utf8mb4_general_ci NOT NULL,
communityid varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
tenantuserid varchar(36) COLLATE utf8mb4_general_ci NOT NULL,
wybz varchar(25) COLLATE utf8mb4_general_ci NOT NULL,
tenantgroupid varchar(36) COLLATE utf8mb4_general_ci DEFAULT NULL,
ljlx varchar(4) COLLATE utf8mb4_general_ci NOT NULL,
xjjf int(11) NOT NULL DEFAULT ‘0’,
growthvalue int(11) NOT NULL DEFAULT ‘0’,
dfyy varchar(64) COLLATE utf8mb4_general_ci NOT NULL,
createuser varchar(36) COLLATE utf8mb4_general_ci NOT NULL,
createtime datetime NOT NULL,
active tinyint(4) NOT NULL DEFAULT ‘1’,
qy_projectid varchar(36) COLLATE utf8mb4_general_ci DEFAULT NULL,
inspectrecordzzid bigint(20) NOT NULL,
cjlx tinyint(4) DEFAULT NULL,
sjly varchar(2) COLLATE utf8mb4_general_ci DEFAULT NULL,
sbbh varchar(36) COLLATE utf8mb4_general_ci DEFAULT NULL,
sblx varchar(36) COLLATE utf8mb4_general_ci DEFAULT NULL,
xjbz tinyint(4) DEFAULT NULL,
sjlx tinyint(4) DEFAULT ‘0’,
isysk tinyint(4) DEFAULT ‘0’,
bfb varchar(36) COLLATE utf8mb4_general_ci DEFAULT NULL,
precent int(4) DEFAULT NULL COMMENT ‘巡检打分百分比’,
dsddjf int(11) DEFAULT NULL,
ydgfjf int(11) DEFAULT NULL,
equipmentid varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL,
jlwybz varchar(128) COLLATE utf8mb4_general_ci DEFAULT NULL,
bizid bigint(20) DEFAULT NULL,
sblj varchar(16) COLLATE utf8mb4_general_ci DEFAULT NULL,
PRIMARY KEY (inspectrecordid) /*T![clustered_index] NONCLUSTERED /,
UNIQUE KEY idx_jlwybz (jlwybz),
KEY idx_inspectrecord_product (tenantid,productid) /!80000 INVISIBLE /,
KEY idx_inspectrecord_region (regionid,communityid),
KEY idx_inspectrecord_time (createtime),
KEY idx_inspectrecord_group (tenantgroupid) /!80000 INVISIBLE /,
KEY inspectrecordzzid (inspectrecordzzid),
KEY idx_inspectrecord_tenant_product_communityid_createtime (tenantid,productid,communityid,createtime),
KEY idx_inspectrecord_tenant_product_regionid_createtime (tenantid,productid,createtime,regionid),
KEY idx_inspectrecord_tenant_product_regionid_createtime_xjjf (tenantid,productid,createtime,regionid,xjjf),
KEY idx_tenantid_productid_regionid (tenantid,productid,regionid) /!80000 INVISIBLE /,
KEY idx_inspectrecord_tenantgroupid (tenantgroupid) /!80000 INVISIBLE */,
KEY idx_tenantgroupid_createtime (tenantgroupid,createtime),
KEY idx_createuser (createuser,createtime),
KEY idx_tenantid_zzid (tenantid,inspectrecordzzid),
KEY idx_tenantuserid (tenantuserid),
KEY idx_bizid (bizid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

那不查询这个cjlx字段为啥会很快呢？

h5n1 · 2022 年5 月 11 日 08:15

执行计划内看不到具体的索引名，但是看表中这么多索引，估计是索引走的不对，而且有很多重复索引，比如下面这几个，要评估业务具体使用哪些字段再去建索引，建索引时选择性好的放在首列

TiDBer_7OFQFtbY · 2022 年5 月 11 日 08:16

不要看这些索引，没有这些条件也很快，只有cjlx=2的时候会慢，其他的条件可以全删除了

h5n1 · 2022 年5 月 11 日 08:18

你只用=2的条件查过吗？这个查时用HINT试过吗。执行计划贴下，别截图

system · 2022 年10 月 31 日 19:12

此话题已在最后回复的 1 分钟后被自动关闭。不再允许新回复。

tinyint 类型字段 查询 条件=2时查询效率极慢

tinyint 类型字段查询条件=2时查询效率极慢