【 使用环境 】生产环境
【 OB or 其他组件 】OB
【 使用版本 】4.2.2.0
【问题描述】SQL执行计划 在明明有单列索引的情况下 执行全盘扫描操作 sql执行时间过长
【附件及日志】
mysql耗时0.2s ob耗时90s
原始sql:
SELECT
a.INFOCODE,
a.NOTICETITLE,
DATE_FORMAT( a.NOTICEDATE, ‘%Y-%m-%d’ ) AS NOTICEDATE,
( SELECT INFOBODYCONTENT FROM info_an_content WHERE INFOCODE = a.INFOCODE LIMIT 1 ) AS INFOBODYCONTENT
FROM
( SELECT INFOCODE, NOTICETITLE, NOTICEDATE FROM info_an_notice_title_special WHERE SECURITYCODE = ‘300209’ AND NOTICETYPE = ‘50008’ ORDER BY NOTICEDATE DESC LIMIT 1 ) a
info_an_notice_title_special 的索引为 SECURITYCODE,NOTICETYPE
info_an_content 的索引为 INFOCODE
info_an_content 的 INFOBODYCONTENT 字段 为longtext
执行计划如下:
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
|0 |NESTED-LOOP OUTER JOIN | |2 |1166918 |
|1 |├─PX COORDINATOR | |1 |14 |
|2 |│ └─EXCHANGE OUT DISTR |:EX10000 |1 |13 |
|3 |│ └─SUBPLAN SCAN |VIEW2 |1 |9 |
|4 |│ └─TABLE RANGE SCAN|info_an_notice_title_special(unique_key)|1 |9 |
|5 |└─SUBPLAN SCAN |VIEW1 |77362 |1163794 |
|6 | └─HASH GROUP BY | |77362 |1163589 |
|7 | └─TABLE FULL SCAN |info_an_content |7736135 |363196 |
Outputs & filters:
0 - output([VIEW2.info_an_notice_title_special.INFOCODE], [VIEW2.info_an_notice_title_special.NOTICETITLE], [DATE_FORMAT(VIEW2.info_an_notice_title_special.NOTICEDATE,
‘%Y-%m-%d’)], [VIEW1.INFOBODYCONTENT]), filter(nil)
conds([VIEW1.cast(info_an_content.INFOCODE, VARCHAR(1048576)) = VIEW2.info_an_notice_title_special.INFOCODE]), nl_params_(nil), use_batch=false
1 - output([VIEW2.info_an_notice_title_special.INFOCODE], [VIEW2.info_an_notice_title_special.NOTICETITLE], [VIEW2.info_an_notice_title_special.NOTICEDATE]), filter(nil)
2 - output([VIEW2.info_an_notice_title_special.INFOCODE], [VIEW2.info_an_notice_title_special.NOTICETITLE], [VIEW2.info_an_notice_title_special.NOTICEDATE]), filter(nil)
is_single, dop=1
3 - output([VIEW2.info_an_notice_title_special.INFOCODE], [VIEW2.info_an_notice_title_special.NOTICEDATE], [VIEW2.info_an_notice_title_special.NOTICETITLE]), filter(nil)
access([VIEW2.info_an_notice_title_special.INFOCODE], [VIEW2.info_an_notice_title_special.NOTICEDATE], [VIEW2.info_an_notice_title_special.NOTICETITLE])
4 - output([info_an_notice_title_special.INFOCODE], [info_an_notice_title_special.NOTICEDATE], [info_an_notice_title_special.NOTICETITLE]), filter(nil)
access([info_an_notice_title_special.TZT_ID], [info_an_notice_title_special.INFOCODE], [info_an_notice_title_special.NOTICETITLE], [info_an_notice_title_special.NOTICEDATE]), partitions(p0)
limit(1), offset(nil), is_index_back=true, is_global_index=false,
range_key([info_an_notice_title_special.SECURITYCODE], [info_an_notice_title_special.NOTICETYPE], [info_an_notice_title_special.shadow_pk_0]), range(300209,
50008,MIN ; 300209,50008,MAX),
range_cond([info_an_notice_title_special.SECURITYCODE = cast(‘300209’, VARCHAR(1048576))], [info_an_notice_title_special.NOTICETYPE = cast(‘50008’,
VARCHAR(1048576))])
5 - output([VIEW1.INFOBODYCONTENT], [VIEW1.cast(info_an_content.INFOCODE, VARCHAR(1048576))]), filter(nil)
access([VIEW1.INFOBODYCONTENT], [VIEW1.cast(info_an_content.INFOCODE, VARCHAR(1048576))])
6 - output([T_FUN_MIN(info_an_content.INFOBODYCONTENT)], [cast(info_an_content.INFOCODE, VARCHAR(1048576))]), filter(nil)
group([cast(info_an_content.INFOCODE, VARCHAR(1048576))]), agg_func([T_FUN_MIN(info_an_content.INFOBODYCONTENT)])
7 - output([info_an_content.INFOCODE], [info_an_content.INFOBODYCONTENT]), filter(nil)
access([info_an_content.INFOCODE], [info_an_content.INFOBODYCONTENT]), partitions(p0)
is_index_back=false, is_global_index=false,
range_key([info_an_content.EID]), range(MIN ; MAX)always true
在mysql8.0.6中执行计划如下：
| 1 | PRIMARY | system | 1 | 100.00 | |||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| 3 | DERIVED | info_an_notice_title_special | const | unique_key,idx_info_an_noticetitle_f10_isn | unique_key | 636 | const,const | 1 | 100.00 | ||
| 2 | DEPENDENT SUBQUERY | info_an_content | range | idx_INFOCODE | idx_INFOCODE | 603 | 1 | 100.00 | Using index condition |