点击上方“IT那活儿”公众号--专注于企业全栈运维技术分享,不管IT什么活儿,干就完了!!!
问题描述
某数据库系统历史账单查询sql,由于涉及的表列属性由decimal类型改变为int类型,导致执行计划发生改变。
问题案例:
当该表字段类型为decimal时:
语句执行时间仅为0.014s。语句执行计划为:当字段类型为bigint时:语句执行时间仅为54s。语句执行计划为:
由上可见在decimal字段类型下,优化器自动使用了index_merge算法,虽然都是数字类型,但在bigint字段类型下,优化器却没有选择使用该优化算法。
解决方案
影响优化器执行计划的选择是方方面面的,从官方文档的描述中可以看出在使用INDEX_MERGE优化算法时具有局限性,在AND/OR语句嵌套的情况下,MySQL可能不会选择使用最优方案。
即:
原sql为
SELECT * FROM t WHERE billing_cycle_id = 202203
AND (
( product_instance_id = 65489045484533 )
OR
( acct_id = 65489045484533 AND product_instance_id acct_id ) );
可改写为
SELECT * FROM t WHERE (billing_cycle_id = 202203
AND product_instance_id = 65489045484533)
OR
( billing_cycle_id = 202203
AND
( acct_id = 65489045484533 AND product_instance_id acct_id ) );
或者
SELECT * FROM t
WHERE billing_cycle_id = 202109
AND product_instance_id = 65489045484533
UNION ALL
SELECT * FROM t
WHERE billing_cycle_id = 202109
AND acct_id = 65489045484533
AND product_instance_id acct_id;
参考:https://dev.mysql.com/doc/refman/5.7/en/index-merge-optimization.html%22%20/t%20%22_blank/
也可以采用在sql中添加hint的方式指导优化器选择正确的索引及算法:参考:https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html#optimizer-hints-index-level
END