最近看《高性能mysql》,遇到一些不同版本引起的困惑,准备长时间更新。
1 第三版第六章第6节 编写偷懒的union
作者在这里举了一个例子,主要是想说明如果需要从热点表和非热点表中查数据时,如果热点表中找到数据,非热点表应该如何找。作者这里举了一个例子,大体是使用一个中间变量来操作。
简表的sql如下:
CREATE TABLE `a1` (
`id` int(11) NOT NULL,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
CREATE TABLE `a2` (
`id` int(11) NOT NULL,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
CREATE TABLE `a3` (
`id` int(11) NOT NULL,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
作者的意思是,a1表中为热点数据,a2中为冷数据;检索时先在a1中找,正常解决这个问题可能得在应用层解决;如果要使用mysql本身功能,使用以下方案:
select id from a1 where id=1 union all select id from a2 where id=1;
但是这个sql有个问题,就是它会把两个表都查询一遍,这就失去了分表的意义。为此作者提出以下的方案:
set @found=null;
select greatest(@found := -1,id) as id ,'a1' as tbn from a1 where id = 1
union all
select id,'a2' from a2 where @found is null and id = 1
union all
select 1 ,'no exec' from a3 where (@found := null) is not null;
这个方案中使用一个临时变量,标记在热点表中是否找到数据,如果没有找到才去冷数据中查找,同时使用表3重置临时变量。但这个方案在不同版本的mysql上表现并不相同,它在5.7上是有效的,但在8上却比较诡异。
以下是5.7的情况:
能看到5.7上有一个正常的查询结果,数据来自a1;当然有个问题是最后重置found的语句没有生效,从explain看,mysql认为这条语句永远无效,所以根本不执行。
但是在8.0上的结果就比较诡异了。
found标记似乎并没有生效,mysql依然去表2中查找了一遍,它的explain如下:
同样没有执行第三个表的查询,而且两个版本的explain看不出任何区别。可能是新版本中执行的逻辑不同,似乎直接忽略了found的值,查询时只使用了index却没有使用where;
但是如果查看其它语句又觉得更蹊跷:
从exlpain看,这里使用了where,所以执行了对found的测试;然而为什么直接使用索引时没有呢?