mysql join 缺陷_无意中测试了下MySQL里面的join操作，发现还是存在理解偏差

这是学习笔记的第 2179 篇文章读完需要9分钟

速读仅需5分钟

在一个很偶然的场景下，我看到了一个关于数据库中间件的SQL测试，对比测试的内容大体是对于一条查询语句的输出。看到输出结果，虽然结果是客观的，但是我总是感觉缺少了些什么，于是做了下测试。

为了简化起见，我们把测试场景做到至简。创建两张表，就1个字段，4行记录，来说明下JOIN的一些问题和隐患。

但凡不是太懒的话，这个场景都可以很快实现的。mysql> create table test1(id int);

mysql> create table test2(id int)

mysql> insert into test2 values(1),(2),(2),(3)

mysql> insert into test1 values (1),(2),(2),(3);

‍

和我们预期的一样，这2张表的输出就是4行记录。mysql> select *from test1;

+------+

| id |

+------+

| 1 |

| 2 |

| 2 |

| 3 |

+------+

4 rows in set (0.00 sec)

数据情况是完全一样的。mysql> select *from test2;

+------+

| id |

+------+

| 1 |

| 2 |

| 2 |

| 3 |

+------+

4 rows in set (0.00 sec)

对于下面的SQL，你猜猜分别会有几行输出结果。select * from test1 m,test2 n where m.id=n.id and n.id=2;

select m.id from test1 m,test2 n where m.id=n.id and n.id=2;

可以先思考几秒钟，再往下看。

2

输出结果如下：

mysql> select * from test1 m,test2 n where m.id=n.id and n.id=2;

+------+------+

| id | id |

+------+------+

| 2 | 2 |

|  2 |    2 |

| 2 | 2 |

|     2 |    2 |

+------+------+

4 rows in set (0.00 sec)

第2条SQL的输出如下：mysql> select m.id from test1 m,test2 n where m.id=n.id and n.id=2;

+------+

| id |

+------+

| 2 |

| 2 |

| 2 |

| 2 |

+------+

4 rows in set (0.00 sec)

结果是不是很简单，当然我要表达的不是这一层含义，我想要说的是结果和我们的需求其实是存在一些偏差。

3

从我们的预期来看，输出既然是m(test1)的数据，那么m应该是作为驱动表，那么从我们的常规思路来看，应该是期望看到2条记录。

因为m(test1)表一共就4行记录，一共输出了4行，而且有2行还是完全一样的,对于需求来说实在是找不出有什么场景需要这样的预期结果。

如果要实现这种需求，显然使用distinct,group by是不等价的。mysql> select distinct m.id from test1 m join test2 n on m.id=n.id and m.id=2 ;

+------+

| id |

+------+

| 2 |

+------+

1 row in set (0.00 sec)

所以要实现这种需求，一种很自然的处理方式就浮出水面，那就是半连接。mysql> select m.id from test1 m where m.id in (select n.id from test2 n where m.id=n.id and n.id=2);

+------+

| id |

+------+

| 2 |

| 2 |

+------+

2 rows in set (0.00 sec)

还有一种是exists,在MySQL中其实是更偏爱exists的方式的。mysql> select m.id from test1 m where exists (select 1 from test2 n where m.id=n.id and n.id=2);

+------+

| id |

+------+

| 2 |

| 2 |

+------+

2 rows in set (0.00 sec)

可以看到在这种场景下，从SQL要表达的含义层面才是符合我们的需求出发点的。

我们来看看使用单纯的JOIN带来的一些副作用。

4

第一个是过滤数据的偏差，按照distinct，group by的处理方式是始终做唯一性处理的，也就意味着这种场景下只有1行记录输出。mysql> select distinct m.id from test1 m join test2 n on m.id=n.id where n.id=2;

+------+

| id |

+------+

| 2 |

+------+

1 row in set (0.00 sec)

第二个是带来的数据统计偏差

我们其实想看一下匹配的记录，预期是2行，但是输出了4行，如果数据量较大的情况下，这种查询导致的结果影响面就足够大。mysql> select count(*) from test1 m join test2 n on m.id=n.id where n.id=2;

+----------+

| count(*) |

+----------+

| 4 |

+----------+

1 row in set (0.00 sec)

所以很多不好的查询习惯就开始了，比如：mysql> select count(*) from (select m.id from test1 m join test2 n on m.id=n.id where n.id=2) t;

+----------+

| count(*) |

+----------+

| 4 |

+----------+

1 row in set (0.00 sec)

而这种逻辑方式就很容易适配了。mysql> select count(*) from test1 m where exists (select 1 from test2 n where m.id=n.id and n.id=2);

+----------+

| count(*) |

+----------+

| 2 |

+----------+

1 row in set (0.00 sec)

第三点影响最大，也是我们最容易忽略的。那就是去重过滤带来的副作用。

我们知道输出结果是4行，但是我们预期的是2行，所以如果处理得当，我们需要过滤的数据比例就是50%，而如果匹配记录数是3，则过滤的数据比例是1-3/3^2将近70%，所以一个很基本的公式 1- N/N^2=1-1/N，过滤比例是很高的，如果匹配的记录数是100，那么常规的SQL处理要过滤的就是99.99%的数据。这个过滤比例实在是太高了。

或者换一个问法，如何在1万条记录中如何有效的过滤掉99%以上的数据，可想而知这个复杂度和资源消耗。

第四点，如果是在分布式场景中，那么这个影响的面会被最大化，复杂度和消耗可能是和节点数成正比的。

5

到了这里，会发现我需求出发点的JOIN竟然会变得如此复杂。而换个角度来看，其实就容易理解在我们优化中经常看到的一些distinct和一些看起来蹩脚的组合查询了。

换句话来说，我们的需求是什么，从需求相关的驱动表来入手，可能也是我们需要明白的一个优化点。

转文至此。

以下为个人公众号“一森咖记”，欢迎关注。

近期热文

你可能也会对以下话题感兴趣。点击链接便可查看。