为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
- 【TiDB 版本】:5.7.25-TiDB-v3.0.5
- 【前置条件】:数据量4kw
- 【问题描述】:进行分组查询速度超过1s
- 【表结构】:
CREATE TABLEsy_ads_bond_risk_detail
(
uniqueId
varchar(100) NOT NULL COMMENT ‘id’,
createDate
date DEFAULT NULL,
bondId
int(11) DEFAULT NULL COMMENT ‘id’,
secuCode
varchar(10) DEFAULT NULL,
sname
varchar(100) DEFAULT NULL,
cname
varchar(200) DEFAULT NULL,
issuer
varchar(200) DEFAULT NULL,
secuMarket
int(11) DEFAULT NULL,
underwriter
varchar(200) DEFAULT NULL,
startDate
datetime DEFAULT NULL,
endDate
datetime DEFAULT NULL,
subjectType
varchar(50) DEFAULT NULL,
websiteSnapshot
varchar(1000) DEFAULT NULL COMMENT ‘url’,
eventId
varchar(100) NOT NULL,
eventType
varchar(50) DEFAULT NULL,
eventSubject
varchar(100) DEFAULT NULL,
eventNum1
varchar(100) DEFAULT NULL COMMENT ‘1’,
eventNum2
varchar(100) DEFAULT NULL COMMENT ‘2’,
eventNum3
varchar(100) DEFAULT NULL COMMENT ‘3’,
party1
varchar(1000) DEFAULT NULL COMMENT ‘1’,
party2
varchar(1000) DEFAULT NULL COMMENT ‘2’,
party3
varchar(1000) DEFAULT NULL COMMENT ‘3’,
cash1
decimal(19,4) DEFAULT NULL COMMENT ‘1’,
cash2
decimal(19,4) DEFAULT NULL COMMENT ‘2’,
cash3
decimal(19,4) DEFAULT NULL COMMENT ‘3’,
label1
varchar(100) DEFAULT NULL COMMENT ‘1’,
label2
varchar(100) DEFAULT NULL COMMENT ‘2’,
label3
varchar(100) DEFAULT NULL COMMENT ‘3’,
priority
varchar(50) DEFAULT NULL,
eventDesc
text DEFAULT NULL,
detail
text DEFAULT NULL,
eventSrc
varchar(100) DEFAULT NULL,
eventUrl
varchar(1000) DEFAULT NULL,
eventTime
datetime DEFAULT NULL,
endTime
datetime DEFAULT NULL,
pubTime
datetime DEFAULT NULL,
subTime
datetime DEFAULT NULL,
isValid
tinyint(4) DEFAULT NULL COMMENT ’ 0- 1-',
modifyTime
timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
relationLink
text DEFAULT NULL,
secuMarket2
varchar(50) NOT NULL COMMENT ‘-zxjt’,
PRIMARY KEY (uniqueId
),
KEYisValid_index
(isValid
),
KEYeventType_index
(eventType
),
KEYeventSubject_index
(eventSubject
),
KEYbondId_index
(bondId
),
KEYcreateDate_index
(createDate
),
KEYstartDate_index
(startDate
),
KEYsub_endDate_underwrite_index
(subTime
,endDate
,underwriter
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; - 【查询语句】:
SELECT * FROM sy_ads_bond_risk_detail a
WHERE a.subTime BETWEEN ‘2019-11-10 00:00:00’ AND ‘2019-12-13 00:00:00’
AND a.endDate <‘2019-12-13 00:00:00’
AND underwriter = ‘中信建投证券股份有限公司’
AND a.isValid = 1
GROUP BY a.eventId,a.ISSUER
LIMIT 0,20;
执行计划: