tidb查询慢

  • 【TiDB 版本】:v4.0.5
  • 【问题描述】:查询慢
    表大小:1500w
    执行语句时间:20秒
    执行语句如下:
    SELECT
    order_no,
    tenant_code,
    member_id,
    member_type,
    member_name,
    series_tag,
    play_class_tag,
    lottery_id,
    unit_amount,
    amount,
    times,
    odds,
    issue,
    bet_num,
    numbers,
    deduct_batch_number,
    STATUS,
    status_remark,
    version,
    lottery_no,
    lottery_time,
    win_num,
    win_amount,
    win_remark,
    award_batch_number,
    cancel_batch_number,
    profit,
    rebate,
    rebate_money,
    idempotent,
    create_time,
    update_time,
    source,
    bets_point,
    ext,
    effective_amount
    FROM
    bets
    WHERE
    tenant_code = ‘xxxx’
    AND STATUS IN ( 2, 5, 6, 7, 10 )
    AND create_time > ‘2020-09-10 00:00:00’
    AND create_time < ‘2020-09-17 00:00:00’
    AND bets_point NOT IN ( ‘推单’ )
    ORDER BY order_no DESC
    LIMIT 20,20;

执行计划如下:

表结构如下:
CREATE TABLE bets (
order_no bigint(20) NOT NULL ,
tenant_code varchar(32) NOT NULL ,
member_id bigint(20) NOT NULL ,
member_type tinyint(4) DEFAULT NULL ,
member_name varchar(255) DEFAULT ,
series_tag varchar(32) DEFAULT NULL,
play_class_tag varchar(32) NOT NULL’,
lottery_id bigint(20) NOT NULL ,
unit_amount decimal(14,3) DEFAULT 0.000,
amount decimal(14,3) DEFAULT 0.000,
times int(4) DEFAULT NULL ,
odds decimal(14,3) DEFAULT 0.000,
issue varchar(16) NOT NULL ,
bet_num int(11) DEFAULT 1 ,
numbers text NOT NULL ,
deduct_batch_number bigint(20) NOT NULL ,
status int(11) DEFAULT NULL ,
status_remark varchar(255) DEFAULT NULL,
version int(11) DEFAULT NULL COMMENT,
lottery_no varchar(128) DEFAULT ‘NULL,
lottery_time datetime DEFAULT NULL’,
win_num smallint(6) NOT NULL DEFAULT 0,
win_amount decimal(14,3) DEFAULT 0.000,
win_remark varchar(255) DEFAULT NULL ,
award_batch_number bigint(20) DEFAULT NULL,
cancel_batch_number varchar(32) DEFAULT NULL,
profit decimal(14,3) DEFAULT 0.000,
rebate decimal(14,3) DEFAULT 0.000,
rebate_money decimal(14,3) DEFAULT 0.000,
idempotent varchar(128) DEFAULT NULL ,
create_time datetime DEFAULT CURRENT_TIMESTAMP ,
update_time datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
source varchar(32) NOT NULL,
bets_point varchar(32) NOT NULL ,
ext varchar(255) DEFAULT NULL ,
effective_amount decimal(14,3) DEFAULT 0 ,
PRIMARY KEY (order_no),
UNIQUE KEY uk_ide (idempotent),
KEY idx_tc_li_issue (tenant_code,lottery_id,issue),
KEY idx_tcode_status_orderno (tenant_code,status,order_no),
KEY idx_tc_mid_ctime (tenant_code,member_id,create_time),
KEY idx_tc_ct_sta (tenant_code,create_time,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ;

麻烦反馈下 explain analyze sql 实际执行的时间,多谢。

时间: 24.558s

我的意思是麻烦反馈下 explain analyze sql 的结果信息,多谢。

TopN_9 20 20 time:24.463342084s, loops:2 we.bets.order_no:desc, offset:20, count:20 746.0859375 KB root
└─IndexLookUp_28 30366 40 time:24.440296082s, loops:32, rpc num: 27, rpc max:494.801373ms, min:721.572µs, avg:25.027375ms, p80:15.992571ms, p95:31.503835ms, proc keys max:523695, p95:0 84.84614562988281 MB root
├─Selection_25(Build) 10876245 7548309.59 proc max:757ms, min:200ms, p80:558ms, p95:701ms, iters:10755, tasks:27 in(we.bets.status, 2, 5, 6, 7, 10) N/A cop[tikv]
│ └─IndexRangeScan_23 10889225 10888687 proc max:700ms, min:188ms, p80:528ms, p95:648ms, iters:10755, tasks:27 range:(“xxxx” 2020-09-10 00:00:00,“xxxx” 2020-09-17 00:00:00), keep order:false N/A cop[tikv] table:bets, index:idx_tc_ct_sta(tenant_code, create_time, status)
└─TopN_27(Probe) 30366 40 proc max:454ms, min:0s, p80:189ms, p95:252ms, iters:11092, tasks:762 we.bets.order_no:desc, offset:0, count:40 N/A cop[tikv]
└─Selection_26 10876236 7548309.59 proc max:440ms, min:0s, p80:183ms, p95:241ms, iters:11092, tasks:762 ne(we.bets.bets_point, “推单”) N/A cop[tikv]
└─TableRowIDScan_24 10876245 7548309.59 proc max:432ms, min:0s, p80:181ms, p95:239ms, iters:11092, tasks:762 keep order:false N/A cop[tikv] table:bets
  1. 从执行计划看应该没有问题, 在tikv层分别计算了bets_point NOT IN ( ‘推单’ ) 和 STATUS IN ( 2, 5, 6, 7, 10 )AND create_time > ‘2020-09-10 00:00:00’AND create_time < ‘2020-09-17 00:00:00’ . 之后在 tidb-server 层合并。not in 在 desc 后取了 40 条记录,但是另一个是 750万的数据, indexlookup 消耗了24s多。

  2. 单纯针对这一条 sql ,可以试试 调大参数是否有效
    https://docs.pingcap.com/zh/tidb/stable/system-variables#tidb_index_lookup_concurrency