【sql|达梦sql优化实践7】Gjbwg项目sql语句优化
原sql如下：
select
table1.table1_ID as ZDBH ,
table1.RELICCODE as CPBH ,
table1.RELICNAME as CPMC ,
table1.COLLECTIONCODE as ZJBH ,
table1.ORIGINALNAME as YMC ,
table1.ACTUALQUANTITY as SJSL ,
table1.GRADE as JB ,
table1.MATERIAL as PCZDLB2,
table1.YEARID as SD ,
table1.MATERIALTYPE as ZD ,
table1.SIZEDESCRIPTION as CC ,
table1.SOURCE as LY ,
table1.SOURCETYPE as LYLX ,
table1.INJURY as WCQK ,
table1.INGRADLIST ,
table1.ISGRAD ,
table1.CONFIRMGRADE ,
table1.ADDRESS as CTDD ,
table1.INSTORE_DATE as RKRQ ,
table1.DJ_USERSURNAME as USERSURNAME ,
table1.DJ_USERNAME as USERNAME ,
table1.RELICTYPEID as PCWWLB ,
GB_WORKGROUP.WORKGROUPNAME as GROUPNAME ,
table1.GROUPID
from
table1
LEFT JOIN GB_WORKGROUP
ON
GB_WORKGROUP.ID = table1.GROUPID
where
/* 1=1
--and
and*/
(
(
table1.STOREID= 23
AND table1.RELICTYPEID = 204
)
OR
(
table1.STOREID= 23
AND table1.RELICTYPEID = 205
)
OR
(
table1.STOREID = 19
AND table1.CLASSID = 41
)
OR
(
table1.STOREID = 19
AND table1.CLASSID = 42
)
OR
(
table1.STOREID = 19
AND table1.CLASSID = 43
)
OR
(
table1.STOREID = 75
AND table1.CLASSID = 76
)
)
and table1.STATUS='RZ' limit 10;
其中加粗部分，等号后的数值与STOREID 、CLASSID 字段类型不匹配，发生隐式类型转换导致相关索引不能被使用。
修改为如下：
select
table1.table1_ID as ZDBH ,
table1.RELICCODE as CPBH ,
table1.RELICNAME as CPMC ,
table1.COLLECTIONCODE as ZJBH ,
table1.ORIGINALNAME as YMC ,
table1.ACTUALQUANTITY as SJSL ,
table1.GRADE as JB ,
table1.MATERIAL as PCZDLB2,
table1.YEARID as SD ,
table1.MATERIALTYPE as ZD ,
table1.SIZEDESCRIPTION as CC ,
table1.SOURCE as LY ,
table1.SOURCETYPE as LYLX ,
table1.INJURY as WCQK ,
table1.INGRADLIST ,
table1.ISGRAD ,
table1.CONFIRMGRADE ,
table1.ADDRESS as CTDD ,
table1.INSTORE_DATE as RKRQ ,
table1.DJ_USERSURNAME as USERSURNAME ,
table1.DJ_USERNAME as USERNAME ,
table1.RELICTYPEID as PCWWLB ,
GB_WORKGROUP.WORKGROUPNAME as GROUPNAME ,
table1.GROUPID
from
table1
LEFT JOIN GB_WORKGROUP
ON
GB_WORKGROUP.ID = table1.GROUPID
where
/* 1=1
--and
and*/
(
(
table1.STOREID= '23'
AND table1.RELICTYPEID = '204'
)
OR
(
table1.STOREID= '23'
AND table1.RELICTYPEID = '205'
)
OR
(
table1.STOREID = '19'
AND table1.CLASSID = '41'
)
OR
(
table1.STOREID = '19'
AND table1.CLASSID = '42'
)
OR
(
table1.STOREID = '19'
AND table1.CLASSID = '43'
)
OR
(
table1.STOREID = '75'
AND table1.CLASSID = '76'
)
) and table1.STATUS='RZ' limit 10;
优化后，执行时间从5秒，下降到0.09秒。
达梦云适配技术社区的网址。https://eco.dameng.com
推荐阅读
- DM8|达梦（DM8）SQL优化技巧
- mysql|面了个腾讯出来的00后，我见识到了什么叫“精通MySQL调优”
- 数据库|MySQL性能优化的21个最佳实践（转载）
- 后端|SpringBoot整合Redis集群
- 酷开科技 × StarRocks（统一 OLAP 分析引擎，全面打造数字化的 OTT 模式）
- Java|ruoyi使用AOP控制数据权限案例
- 若依源码学习笔记|若依源码学习4（AOP实现数据权限控制）
- 区块链|什么是区块链（超级账本 Brian Behlendorf 从五个方面教你认识）
- 开源日报|Snowflake 获 2021 年度数据库称号；Linus 成为 Linux 项目中最强 committer；OpenHarmony 3.1 Beta 版发布 | 开源日报