慢SQL 优化

or条件

使用union all替换

select * from tablea A,tableb B where A.id = B.sourceId and A.tran_code = B.tran_code
and A.name = B.name
or A.name != ''
or A.name is null
or A.name = 'TEST'
or A.name exists (select 'X' from tablec C where A.name = C.name)

修改为

select * from tablea A,tableb B where A.id = B.sourceId and A.tran_code = B.tran_code
and A.name = B.name

union all

select * from tablea A,tableb B where A.id = B.sourceId and A.tran_code = B.tran_code
and A.name != ''

union all

select * from tablea A,tableb B where A.id = B.sourceId and A.tran_code = B.tran_code
and A.name is null

union all

select * from tablea A,tableb B where A.id = B.sourceId and A.tran_code = B.tran_code
and A.name = 'TEST'

union all

select * from tablea A,tableb B where A.id = B.sourceId and A.tran_code = B.tran_code
and A.name exists (select 'X' from tablec C where A.name = C.name)

or不满足最左索引匹配，需要对tablea增加联合索引id,tran_code,name;tableb增加sourceId,tran_code,name和name两个索引

贡献者

xiaofei731016

更新日志

2026/6/25 15:15

查看所有更新日志

• 6226c-更新侧边栏配置和图标于 2026/6/25
• fb8bc-更新为vuepress于 2026/4/5

编辑此页

上一页数据库死锁解决

下一页SVN