时间戳

首页 > 解决方案 > MySQL LEFT JOIN 与 ON 子句中的一列

mysql - MySQL LEFT JOIN 与 ON 子句中的一列

问题描述

有一个需要优化的查询，查询如下所示：

SELECT *
FROM TableA
LEFT JOIN TableB ON TableA.column_1 = TableB.column_1
  AND TableB.column_2 IS NOT NULL
  AND TableB.column_3 IS NULL
  AND TableB.column_4 IS NULL
  AND TableB.column_5 IS NULL
LEFT JOIN TableC ON TableA.column_1 = TableC.column_1
  AND TableC.column_3 IS NULL
  AND TableC.column_4 IS NULL
  AND TableC.column_5 IS NULL

查询中缓慢的部分是很多IS NULLinON子句。执行时间约为 4+ 秒。在使用查询一段时间后，我注意到IS NULL可以从ON子句中的字段中省略它。像这样：

SELECT *
FROM TableA
LEFT JOIN TableB ON TableA.column_1 = TableB.column_1
  AND TableB.column_2 IS NOT NULL
  AND TableB.column_3
  AND TableB.column_4
  AND TableB.column_5
LEFT JOIN TableC ON TableA.column_1 = TableC.column_1
  AND TableC.column_3
  AND TableC.column_4
  AND TableC.column_5

该查询的执行时间为53ms!，记录集也与我们从第一个查询中得到的相同。试图谷歌它并没有找到任何东西。

有谁知道它是如何工作的？

MySQL版本是5.7.25

更新

CREATE TABLE TableA (
    `column_1` INT(2) NOT NULL,
    `column_2` datetime  NULL,
    `column_3` datetime  NULL,
    `column_4` datetime  NULL,
    `column_5` datetime  NULL,
)

CREATE TABLE TableB (
    `column_1` INT(2) NOT NULL,
    `column_2` datetime  NULL,
    `column_3` datetime  NULL,
    `column_4` datetime  NULL,
    `column_5` datetime  NULL,
)

CREATE TABLE TableC (
    `column_1` INT(2) NOT NULL,
    `column_2` datetime  NULL,
    `column_3` datetime  NULL,
    `column_4` datetime  NULL,
    `column_5` datetime  NULL,
)

INSERT INTO `TableA` (`column_1`, `column_2`, `column_3`, `column_4`, `column_5`)
VALUES
    (1, '2019-08-13 00:00:00', NULL, NULL, NULL);

不包括索引，但ON子句中使用的每个字段都有索引

标签： mysql

解决方案

考虑下表：

create table tablename (id int, col datetime);
insert into tablename (id, col) values
(5,   null),
(6,   '2019-01-06 11:38:41'),
(7,   '2019-01-06 11:39:40'),
(8,   '2019-01-06 11:39:49');

查询：

select * 
from tablename
where col

返回：

| id  | col                 |
| --- | ------------------- |
| 6   | 2019-01-06 11:38:41 |
| 7   | 2019-01-06 11:39:40 |
| 8   | 2019-01-06 11:39:49 |

请参阅演示。
这意味着为了评估col为0(False) 或1(True) 的值，由于评估发生在WHERE子句中，MySql 将任何non nulldatetime 值转换为正数>0，评估为 True 并且任何null值都将评估为非 True（所以视为 False) 。

你所描述的恰恰相反。
因为当评估为orTableB.column_3 IS NULL在or子句中时返回相反的结果。TableB.column_3>00WHEREON

推荐阅读