关于 MySQL bug 91418 一些看法

最新推荐文章于 2021-01-27 12:23:20 发布

转载最新推荐文章于 2021-01-27 12:23:20 发布 · 254 阅读

本内容遵循CC 4.0 BY-SA版权协议

原文链接：https://shop42509167-38.youzan.com/wscvis/knowledge/index?kdt_id=42316999\x26amp;p=columnshow\x26amp;alias=2froywfz558sn\x26amp;qr=paidcolumn_2froywfz558sn#/columnshow?kdt_id=42316999\x26amp;p=columnshow\x26amp;alias=2froywfz558sn\x26amp;qr=paidcolumn_2fr

导读：

郑松华，知数堂SQL 优化班老师

现任 CCmediaService DBA，主要负责数据库优化相关工作

擅长SQL优化，数据核对

想阅读更多内容请点击订阅专栏

大家好，我是知数堂SQL 优化班老师网名：骑龟的兔子

今天给大家发一下我的之前写的一篇文章，是关于 MySQL bug 91418

我们看bug 和学习处理方式的一个重要原因是以后碰到类似问题，不仅仅在MySQL。

在别的数据库或者语言中也可以触类旁通增加自己能力。

下面是bug链接

https://bugs.mysql.com/bug.php?id=91418

create table TEST_SUB_PROBLEM	
(	
UID integer PRIMARY KEY AUTO_INCREMENT	
,BID VARCHAR(10)	
,THING_NAME VARCHAR(100)	
,OTHER_IFO VARCHAR(100)	
);	

	
insert into TEST_SUB_PROBLEM(BID,THING_NAME,OTHER_IFO)	
values ('thing1','name1','look a chicken')	
,( 'thing1','name1','look an airplane')	
,('thing2','name2','look a mouse')	
,('thing3','name3','look a taperecorder')	
,('thing3','name3','look an explosion')	
,('thing4','name4','look at the stars')	
;	

	
 select    	
TST.UID	
,TST.BID	
,TST.THING_NAME	
,TST.OTHER_IFO	
,vw2.DIST_UID	
from TEST_SUB_PROBLEM TST	
join (	
    select uuid() as DIST_UID, vw.*	
    from (	
    select DISTINCT BID, THING_NAME from TEST_SUB_PROBLEM	
    ) vw	
    ) vw2 on vw2.BID = TST.BID	
    ;

640?wx_fmt=png

bug 的问题就是

select uuid() as DIST_UID, vw.*	
    from (	
    select DISTINCT BID, THING_NAME from TEST_SUB_PROBLEM	
    ) vw	
  	
  +--------------------------------------+--------+------------+	
| DIST_UID                             | BID    | THING_NAME |	
+--------------------------------------+--------+------------+	
| 00a993b0-cc29-11e8-9fff-080027158a34 | thing1 | name1      |	
| 00a993d6-cc29-11e8-9fff-080027158a34 | thing2 | name2      |	
| 00a993df-cc29-11e8-9fff-080027158a34 | thing3 | name3      |	
| 00a993e5-cc29-11e8-9fff-080027158a34 | thing4 | name4      |	
+--------------------------------------+--------+------------+  	

	
这里 DIST_UID 应该是4个 值 但是 原来的SQL 出现了6个不同的值	
我查看了下原因 如下 	
root@mysql3308.sock>[test]>show warnings\G	
*************************** 1. row ***************************	
  Level: Note	
   Code: 1003	
Message: /* select#1 */ select `test`.`TST`.`UID` AS `UID`,`test`.`TST`.`BID` AS `BID`,	
`test`.`TST`.`THING_NAME` AS `THING_NAME`,`test`.`TST`.`OTHER_IFO` AS `OTHER_IFO`,uuid() AS `DIST_UID` 	
from `test`.`TEST_SUB_PROBLEM` `TST` join 	
(/* select#3 */ select distinct `test`.`TEST_SUB_PROBLEM`.`BID` AS `BID`,	
`test`.`TEST_SUB_PROBLEM`.`THING_NAME` AS `THING_NAME` from `test`.`TEST_SUB_PROBLEM	
`) `vw` where (`vw`.`BID` = `test`.`TST`.`BID`)	
1 row in set (0.00 sec)

从上面的 show warnings 可以发现

uuid() AS `DIST_UID` 这个部分经过MySQL 转换之后挪到了最上层所以导致最终有六个不同的值

我的环境是8.0 可以使用如下hint 就可以解决 	

	
select   /*+ set_var(optimizer_switch = 'derived_merge=off' ) */	
TST.UID	
,TST.BID	
,TST.THING_NAME	
,TST.OTHER_IFO	
,vw2.DIST_UID	
from TEST_SUB_PROBLEM TST	
join (	
    select uuid() as DIST_UID, vw.*	
    from (	
    select DISTINCT BID, THING_NAME from TEST_SUB_PROBLEM	
    ) vw	
    ) vw2 on vw2.BID = TST.BID	
    ;	
   	
+-----+--------+------------+---------------------+--------------------------------------+	
| UID | BID    | THING_NAME | OTHER_IFO           | DIST_UID                             |	
+-----+--------+------------+---------------------+--------------------------------------+	
|   1 | thing1 | name1      | look a chicken      | 3d65599a-cc26-11e8-9fff-080027158a34 |	
|   2 | thing1 | name1      | look an airplane    | 3d65599a-cc26-11e8-9fff-080027158a34 |	
|   3 | thing2 | name2      | look a mouse        | 3d655a0b-cc26-11e8-9fff-080027158a34 |	
|   4 | thing3 | name3      | look a taperecorder | 3d655a1a-cc26-11e8-9fff-080027158a34 |	
|   5 | thing3 | name3      | look an explosion   | 3d655a1a-cc26-11e8-9fff-080027158a34 |	
|   6 | thing4 | name4      | look at the stars   | 3d655a25-cc26-11e8-9fff-080027158a34 |	
+-----+--------+------------+---------------------+--------------------------------------+	
6 rows in set (0.00 sec)	

	
+----+-------------+------------------+------------+------+---------------+-------------+---------+--------------+------+----------+-----------------+	
| id | select_type | table            | partitions | type | possible_keys | key         | key_len | ref          | rows | filtered | Extra           |	
+----+-------------+------------------+------------+------+---------------+-------------+---------+--------------+------+----------+-----------------+	
|  1 | PRIMARY     | TST              | NULL       | ALL  | NULL          | NULL        | NULL    | NULL         |    6 |   100.00 | Using where     |	
|  1 | PRIMARY     | <derived2>       | NULL       | ref  | <auto_key0>   | <auto_key0> | 33      | test.TST.BID |    2 |   100.00 | NULL            |	
|  2 | DERIVED     | <derived3>       | NULL       | ALL  | NULL          | NULL        | NULL    | NULL         |    6 |   100.00 | NULL            |	
|  3 | DERIVED     | TEST_SUB_PROBLEM | NULL       | ALL  | NULL          | NULL        | NULL    | NULL         |    6 |   100.00 | Using temporary |	
+----+-------------+------------------+------------+------+---------------+-------------+---------+--------------+------+----------+-----------------+	
 	
 从 show warnings 部分可以看出  uuid() AS `DIST_UID 在id =2 部分 运行出结果了 所以没有结果有4个值！	

	
root@mysql3308.sock>[test]>show warnings\G	
*************************** 1. row ***************************	
  Level: Note	
   Code: 1003	
Message: /* select#1 */ select /*+ SET_VAR(optimizer_switch='derived_merge=off') */ `test`.`TST`.`UID` AS `UID`,	
`test`.`TST`.`BID` AS `BID`,`test`.`TST`.`THING_NAME` AS `THING_NAME`,`test`.`TST`.`OTHER_IFO` AS `OTHER_IFO`,	
`vw2`.`DIST_UID` AS `DIST_UID` from `test`.`TEST_SUB_PROBLEM` `TST` 	
join (/* select#2 */ select uuid() AS `DIST_UID`,`vw`.`BID` AS `BID`,`vw`.`THING_NAME` AS `THING_NAME` 	
from (/* select#3 */ select distinct `test`.`TEST_SUB_PROBLEM`.`BID` AS `BID`,	
`test`.`TEST_SUB_PROBLEM`.`THING_NAME` AS `THING_NAME` from `test`.`TEST_SUB_PROBLEM`)	
 `vw`) `vw2` where (`vw2`.`BID` = `test`.`TST`.`BID`)	
1 row in set (0.00 sec) s/82991850

上面的问题是因为MySQL5.7 开始的视图合并功能引起的，如果是MySQL5.6 角度看的话确实是bug，因为跟5.6 结果是不同的。

但是从另一个角度上说，这个也是无法解决的Bug。

因为这个问题的根本原因有一方面是视图合并，但更重要的是类似 uuid() 这样的随着时间或者次数不停变化的函数特性，导致这种问题必然存在。

类似的函数还有sysdate()

root@mysql3308.sock>[test]>select    	
    -> TST.UID	
    -> ,TST.BID	
    -> ,TST.THING_NAME	
    -> ,TST.OTHER_IFO	
    -> ,vw2.DIST_UID 	
    -> from TEST_SUB_PROBLEM TST	
    -> join (	
    ->     select sysdate() as DIST_UID, vw.*  ,sleep(1) s	
    ->     from (	
    ->     select DISTINCT BID, THING_NAME from TEST_SUB_PROBLEM	
    ->     ) vw	
    ->     ) vw2 on vw2.BID = TST.BID	
    ->     ;	
+-----+--------+------------+---------------------+---------------------+	
| UID | BID    | THING_NAME | OTHER_IFO           | DIST_UID            |	
+-----+--------+------------+---------------------+---------------------+	
|   1 | thing1 | name1      | look a chicken      | 2018-10-10 10:21:42 |	
|   2 | thing1 | name1      | look an airplane    | 2018-10-10 10:21:42 |	
|   3 | thing2 | name2      | look a mouse        | 2018-10-10 10:21:42 |	
|   4 | thing3 | name3      | look a taperecorder | 2018-10-10 10:21:42 |	
|   5 | thing3 | name3      | look an explosion   | 2018-10-10 10:21:42 |	
|   6 | thing4 | name4      | look at the stars   | 2018-10-10 10:21:42 |	
+-----+--------+------------+---------------------+---------------------+	
6 rows in set (0.00 sec)	

	
root@mysql3308.sock>[test]>select   /*+ set_var(optimizer_switch = 'derived_merge=off' ) */	
    -> TST.UID	
    -> ,TST.BID	
    -> ,TST.THING_NAME	
    -> ,TST.OTHER_IFO	
    -> ,vw2.DIST_UID 	
    -> from TEST_SUB_PROBLEM TST	
    -> join (	
    ->     select sysdate() as DIST_UID, vw.* ,sleep(1) s	
    ->     from (	
    ->     select DISTINCT BID, THING_NAME from TEST_SUB_PROBLEM	
    ->     ) vw	
    ->     ) vw2 on vw2.BID = TST.BID	
    ->     ;	

	
+-----+--------+------------+---------------------+---------------------+	
| UID | BID    | THING_NAME | OTHER_IFO           | DIST_UID            |	
+-----+--------+------------+---------------------+---------------------+	
|   1 | thing1 | name1      | look a chicken      | 2018-10-10 10:22:04 |	
|   2 | thing1 | name1      | look an airplane    | 2018-10-10 10:22:04 |	
|   3 | thing2 | name2      | look a mouse        | 2018-10-10 10:22:05 |	
|   4 | thing3 | name3      | look a taperecorder | 2018-10-10 10:22:06 |	
|   5 | thing3 | name3      | look an explosion   | 2018-10-10 10:22:06 |	
|   6 | thing4 | name4      | look at the stars   | 2018-10-10 10:22:07 |	
+-----+--------+------------+---------------------+---------------------+	
6 rows in set (4.00 sec)

谢谢大家~ 欢迎转发

如有关于SQL优化方面疑问需要交流的，请加入QQ群（579036588），并@骑兔子的龟 就可与我联系

END

点击下图小程序订阅

《SQL优化专栏》

get更多优化技能