101- 一个小SQL,看看各个数据库都是啥反映

有网友请我帮忙诊断一个SQL性能问题, 说是mysql的库, 内层查询没能使用外层的谓词条件,导致sql执行较慢.

我找了一个mysql的库,建表建索引模拟,却发现根本无法执行, 于是就有了这篇文章.

sql比较简单:

--一个自关联update:
update T_DIAG006 a
set a.cnt=(select count(*) from T_DIAG006 b 
           where a.id=b.id and a.name=b.name 
           and b.data_State !=2
           )
where a.id=123456789;

网友给的执行计划截图显示, 外层查询走了id字段上的索引(选择性好,适合走索引), 但是内层使用的是全表扫描,问怎样能让内层查询也走索引?

下面是我用来模拟该SQL需要的建表和索引语句:

--这个建表语句我特意做成通用的,在oracle/mysql/postgresql 都适用
create table T_DIAG006
 (id int
 ,name varchar(10)
 ,data_State int
 ,cnt int
 );
create index idx_T_DIAG006 on T_DIAG006(id);
--只是用来验证是否报错和执行计划情况,不需要生成测试数据  

在mysql 8.x环境, 执行报错:

搜索了一下相关报错, 发现mysql确实不支持这种SQL的写法, 写法都不支持,性能也就没办法验证了.

问了一下网友,用的是mysql的哪个版本? 告知用的不是纯的mysql, 而是跟mysql同一个体系的国产数据库,看来国产数据库确实改进了一些东西, 这里给国产数据库点赞. 但是优化器还有点不足, 继续努力!

在这种情况下, 在这个国产数据库上的优化方法就是把a.id=123456789这个条件再写一遍到内层查询:

--优化改写:
update T_DIAG006 a
set a.cnt=(select count(*) from T_DIAG006 b 
           where a.id=b.id and a.name=b.name 
           and b.data_State !=2
           and b.id=123456789
           )
where a.id=123456789;

至于在原生mysql库上要怎么改才能正常执行,各位网友可以献计献策, 让我也学习一下.

我把SQL拿到另一个非常流行的开源数据库Postgresql(版本14.5)上测试,这个库的表现一开始也是报错:

根据提示, 我把上图中的a.去掉, 这次没问题了, 优化器给出的执行计划也是我们想要的,就这个SQL而言, postgresql比之前的mysql系国产数据库更胜一筹,比原生mysql胜了两筹:

最后再回到咱最熟悉的oracle数据库, 不出意外, 不需要原sql做任何改动, 就得到我们想要的执行计划:

我又特意找出20多年前的oracle 8i版本, 也是没问题的. oracle的优化器确实很强大, 国产数据库有这样的对手, 假以时日, 应该也会变大变强! 在目前的情况下, 通过写法上的弥补, 还是可以接受的. 但是对于更复杂的SQL, 可能就没那么容易了.

大家如果有兴趣, 可以在你负责的国产数据库中用上面的语句测试一下,看看是什么情况, 说不定能顺便看出它的"血统".

以上只是一个小SQL的简单测试, 不代表某个数据库就比另一个数据库强, 在以往的公众号文章中, 我还写过mysql胜过oracle的例子.