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