MySQL Forums
Forum List  »  Chinese

ICP中explain key-lenth和OPTIMIZER_TRACE中refine_plan展示信息不对称问题
Posted by: yijie fu
Date: September 07, 2022 04:45AM

情况1:
索引:ALTER TABLE t_app_recommended_info ADD INDEX idx_uid_create_time_type (uid,create_time,type);
查询条件:select * from t_app_recommended_info where uid = 1252057754179211264 and create_time >'2022-09-01 00:00:00' and type =1 and params like '%"couponId":12582268875%' and params like '%"hmsr":"saomaqudao"%';
explain:key-len=12

OPTIMIZER_TRACE:refine_plan: "pushed_index_condition": "((`t_app_recommended_info`.`type` = 1) and (`t_app_recommended_info`.`uid` = 1252057754179211264) and (`t_app_recommended_info`.`create_time` > '2022-09-01 00:00:00'))",
情况2:
索引:ALTER TABLE t_app_recommended_info ADD INDEX idx_uid_type_createtime (uid,type,create_time);
查询条件:select * from t_app_recommended_info where uid = 1252057754179211264 and create_time >'2022-09-01 00:00:00' and type =1 and params like '%"couponId":12582268875%' and params like '%"hmsr":"saomaqudao"%';
explain:key-len=16

OPTIMIZER_TRACE:refine_plan: "pushed_index_condition": "((`t_app_recommended_info`.`type` = 1) and (`t_app_recommended_info`.`uid` = 1252057754179211264) and (`t_app_recommended_info`.`create_time` > '2022-09-01 00:00:00'))",
根据情况1和情况2的explain的key-len发现type(4字节)未使用到,但是根据OPTIMIZER_TRACE:refine_plan: 又发现使用到了,求解惑,怀疑是key-len的解释又问题,求大佬解释

Options: ReplyQuote


Subject
Views
Written By
Posted
ICP中explain key-lenth和OPTIMIZER_TRACE中refine_plan展示信息不对称问题
101
September 07, 2022 04:45AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.