SQL解析慢

Time: 2022-03-07T15:35:35.392744013+08:00

Txn_start_ts: 431656411988230188

User@Host: XXXX[x’x’x’x’x] @ xxx [xxx]

Conn_ID: 197179

Query_time: 0.14115512

Parse_time: 0.13990084

Compile_time: 0.000439323

Rewrite_time: 0.000168635

Optimize_time: 0.001164157

Wait_TS: 0.000010265

DB: tsmscp

Is_internal: false

Digest: 06f219f3b161b969ed2bdfb164fde235944e70aba66f2c854d378ee3f60fd8b1

Num_cop_tasks: 0

Mem_max: 5450

Prepared: false

Plan_from_cache: false

Plan_from_binding: false

Has_more_results: false

KV_total: 0.880693493

PD_total: 0.00000611

Backoff_total: 0

Write_sql_response_total: 0

Succ: true

Plan: tidb_decode_plan(‘pQHQMAkyN18xCTAJMAlOL0EJMAl0aW1lOjE0Mi4zwrVzLCBsb29wczoxLCBwcmVwYXJlOjU0LjcFGihpbnNlcnQ6ODcuNgUQLGxvY2tfa2V5czogewVFCDU3MA1FJHJlZ2lvbjozLCAFIAAzAVesY2tfcnBjOjEuMjU2NzY2bXMsIHJwY19jb3VudDozfQk1LjMyIEtCCU4vQQo=’)

      insert into table_name(                 SENT_MESSAGE_ID,        TEMPLATE_ID,        REQUEST_ID,        ARRIVE_DATE,        ARRANGE_DATE,        VSENDER_ID,        SMS_PRIORITY,        MOBILE_NUMBER,        SMS_CONTENT,        GATEWAY_CHANNEL,        MOSEQUENCE_ID,        SRC_ID,        EXPIRE_DATE,        VERSION_ID,        SEND_USER,        SENDER_SERIES_ID,        TEMPLATE_NAME,        NATIVE_SERIES_ID,        sms_task_id,        sms_task_num,        sms_benefit_serie,        CONFIRM_TEMPLATE_ID,        business_type_id,        party_no,        client_no,        group_task_no,        url,        sms_type,        site_no,        linkid,        receive_msg_id,        totalNumber,        forbid_start_date,        forbid_stop_date,           LOCALGATEWAY_DATE,           STATUS,           mobile_number_hash,           mobile_number_cipher)      values            (1045635510,       'test09299',       8535102095710506,       current_date,       '2022-03-07 15:36:32.857',       'CCDD',       2,       '13557530506',       'éa~Lèˉ~A紧?~@¥?~A~\?~O~Q?~Z~D?~C~E?~Fμ??~K??~L?~O~V?~U°?~@§è~C?.gg??~L?~J| ??~@??~K?~O~Xé~G~O?~Q~W??~L?~F~M?~J| ?~O~Xé~G~O',    8    null,       null,       null,       34,       'CCDD',       'PA011_S000033464',       '?§~Q?~J~@-?~V°??| é~A~S-232323-232323?~_-信',       'PA011_S000033464',       106989336438,       1,       'PA011_S000033464',       null,        '1',       null,       null,          '3',          null,       '1',       null,       null,       0,       1,       '2022-03-08 00:00:00.0',       '2022-03-08 02:59:00.0',          DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'),       '1',          'Fz1t2DrZNTig8uOifUS2T5tQlrZqk2WDRsIazy393pY=',          'zo0iH0wz01thyEPXPfQ58TH6LWW8ZhzA==')            ;
1 个赞

表的索引

PRIMARY KEY (ID,ARRIVE_DATE),
KEY IDX_DATE_STAT (STAT_DATE),
KEY IDX_PSCP_ARRANGE_DATE2 (ARRANGE_DATE),
KEY INX_PSCP_SMS_SENTID2 (SENT_MESSAGE_ID),
KEY INX_SEND_TASK_ID2 (SMS_TASK_ID),
KEY idx_pscp_sms_sent_hash (mobile_number_hash),
KEY INX_SENT_SERIES2 (SENDER_SERIES_ID),
KEY INX_SEQUENCE_ID2 (SEQUENCE_ID),
UNIQUE KEY INX_SMS_SENT_CHK2 (VSENDER_ID,REQUEST_ID,SMS_TASK_NUM,ARRIVE_DATE),
KEY INX_TEMPLATE_FK2 (TEMPLATE_ID,VERSION_ID)

可以看看 TiDB 监控 Executor - Compile duration,是一直比较慢,还是偶尔抖动,通常是 TiDB server 节点负载较高 Go runtime 调度延迟上涨引起的

一致慢啊。。而且就这个语句慢。。。

compile duration很低

debug1.zip (606.0 KB)

从火焰图上主要是 prepared 语句的解析和优化开销, useServerPrepStmts = truecachePrepStmts = true 设置了吗
https://docs.pingcap.com/zh/tidb/stable/java-app-best-practices#prepare-相关参数

该主题在最后一个回复创建后60天后自动关闭。不再允许新的回复。