- 【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 ;