74-这类SQL优化，oracle输给了mysql，如何补救？

MySQL和Oracle都是Oracle公司旗下的关系型数据库，在最近几年全球关系型数据库使用排行榜上，一直占据头两把交椅（下图是2020年2月的一个排名）。

oracle数据库是Oracle公司主打产品，是一款接近于完美的数据库产品，当然其使用授权费用也是比较昂贵的；Mysql虽然没有oracle那么强大，但是因为其开源免费的优点，被广泛使用。

我主要以研究Oracle数据库（尤其是性能方面）为主，也会关注一些Mysql的技术，今天列举的一个案例，就是Mysql优化器比它的“老大哥”oracle做的还要好的一个例子。

首先说一个区别，mysql的索引保存null值，而oracle不保存全是null值的索引记录，比如下面的查询：

select * from t1 where object_id is null;

这样的sql，在object_id 定义可为null，而且存在单字段索引的时候，mysql是可以使用索引的，但oracle不可以，需要创建(object_id,0)这样的联合索引，上面sql才能使用索引。在oracle中，如果要使用索引，上面的写法是不建议的，索引列应尽量定义为not null。

我们今天要比较的sql如下：

select owner,max(object_id) as max_object_id from tbig group by owner;

业务逻辑：选出每个owner对应的最大（最小也是一样）的object_id，已知owner的唯一值比较少。

Mysql：

mysql的优化器有一个loose index scan的技术，类似oracle 的index skip scan，可以高效的执行上面sql，官方文档相关链接：

https://dev.mysql.com/doc/refman/8.0/en/group-by-optimization.html

在8.0 版本，mysql增加了一个非常好用的新特性，可以使用explain anzlyze来查看执行计划和cost。这个特性类似oracle的 gather_plan_statistics + dbms_xplan.display_cursor( format=>'all stats,last')。

表结构及索引情况：

CREATE TABLE tbig (

id smallint(5) unsigned NOT NULL ,

owner varchar(30) null,

object_id int(10) unsigned NULL,

create_date datetime NULL,

PRIMARY KEY (id),

KEY idx_tbig_owner_object_id (owner,object_id)

) ;

插入一下数据，省略。

explain analyze select owner,max(object_id) as max_object_id from tbig group by owner;

执行计划：

因为owner和object_id两个字段定义都是null，这里是没有使用loose index scan。

将其中任意一个字段定义改成not null：

alter table tbig modify owner varchar(30) not null;

再看看执行计划：

explain analyze select owner,max(object_id) as max_object_id from tbig group by owner;

这个时候执行计划使用的就是高效的loose index scan（cost 比上面低很多），关键字为index_for_group_by。

疑问：如果这时候把owner字段定义再改回null，alter table tbig modify owner varchar(30) null; 还是会使用loose index scan的执行计划，我对Mysql的这个特性有点不太理解，如有mysql专家可以帮忙解惑，万分感谢。

Oracle：

oracle虽然很早的版本就有了index skip scan技术，但是却无法实现与mysql 的loose index scan 相同的优化。

相同的表结构，创建 owner + object_id 联合索引，如果两个字段定义为可为null，上面sql使用的是全表扫描的执行计划：

如果owner和object_id两个字段其中任意一个定义为not null，那么上面sql可以使用index fast full scan的执行计划。或者我们在sql中加入一个where owner is not null 的条件，也可以达到相同目的。这种情况虽然性能有一定的提升，但也只是利用了索引比表小的原理，没有很多的改善:

不用where owner is not null,使用下面命令也会得到相同执行计划：

alter table tbig modify owner not null;

补救方法：

这种情况，如果我们设计的关系模型是符合范式规范的，那么应该还有一张表是用来只保存owner信息的，根据owner唯一值少的先决条件，这个表一定是一个小表（比如叫t_user)，这时，我们可以用标量子查询的写法，来优化这个sql（注：在owner对应的object_id有空值的时候，两个sql在逻辑上不完全等价），标量子查询在这里起到了积极意义（题外话：很多事情都不是非黑即白，都有适用的场景，标量子查询也有其正面的意义）：

select * from

(

select username as owner,(select max(object_id) from tbig s where s.owner=m.username) as max_object_id

from t_user m

) where max_object_id is not null;

经过上面的改写，sql执行效率会有大幅提升（tbig表越大，效率提升越明显），与mysql的loose index scan的实现效果相差无几了。有兴趣的朋友可以自己动手测试一下。

总结：

没有。

春天来了，疫情也早点去吧。