mysql - MySQL 中执行一个 JOIN + 一个 LIKE 语句更快还是执行两个 JOIN 更快？

Question

我必须创建一个 cron 作业，它本身很简单，但因为它每分钟都会运行，所以我担心性能。我有两张表，一张有用户名，另一张有有关其网络的详细信息。大多数时候，一个用户只属于一个网络，但理论上他们可能属于更多网络，但即使如此，也很少，可能是两个或三个。因此，为了减少 JOIN 的数量，我将用 | 分隔的网络 id 保存在用户表的一个字段中，例如

|1|3|9|

(这个问题的简化)用户表结构是

TABLE `users` (
  `u_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
  `userid` VARCHAR(500) NOT NULL UNIQUE,
  `net_ids` VARCHAR(500) NOT NULL DEFAULT '',
  PRIMARY KEY (`u_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

(也简化的)网络表结构是

CREATE TABLE `network` (
  `n_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
  `netname` VARCHAR(500) NOT NULL UNIQUE,
  `login_time` DATETIME DEFAULT NULL,
  `timeout_mins` TINYINT UNSIGNED NOT NULL DEFAULT 10,
  PRIMARY KEY (`n_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

发生超时时我必须发送警告，我的查询是

SELECT N.netname, N.timeout_mins, N.n_id, U.userid FROM
(SELECT netname, timeout_mins, n_id FROM network
 WHERE is_open = 1 AND notify = 1
 AND TIMESTAMPDIFF(SECOND, TIMESTAMPADD(MINUTE, timeout_mins, login_time), NOW()) < 60) AS N
INNER JOIN users AS U ON U.net_ids LIKE CONCAT('%|', N.n_id, '|%');

我将 N 设置为子查询以减少连接的行数。但我想知道添加第三个表(以 u_id 和 n_id 作为列)，从用户中删除 net_ids 列，然后对所有三个表进行联接是否会更快？因为我读到使用 LIKE 会减慢速度。

在这种情况下使用哪个查询最有效？一个 JOIN 和一个 LIKE 还是两个 JOINS？

附注我做了一些实验，使用两个 JOINS 的初始值高于使用 JOIN 和 LIKE 的初始值。然而，重复运行相同的查询似乎会加快速度很多，我怀疑某些东西缓存在某个地方，无论是在我的应用程序还是数据库中，并且两者都具有可比性，所以我没有发现这些数据令人满意。这也与我根据所读内容的预期相矛盾。

我使用了这张表:

TABLE `user_net` (
`u_id` BIGINT UNSIGNED NOT NULL,
`n_id` BIGINT UNSIGNED NOT NULL,
INDEX `u_id` (`u_id`),
FOREIGN KEY (`u_id`) REFERENCES `users`(`u_id`),
INDEX `n_id` (`n_id`),
FOREIGN KEY (`n_id`) REFERENCES `network`(`n_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

这个查询:

SELECT N.netname, N.timeout_mins, N.n_id, U.userid FROM
(SELECT netname, timeout_mins, n_id FROM network
 WHERE is_open = 1 AND notify = 1
 AND TIMESTAMPDIFF(SECOND, TIMESTAMPADD(MINUTE, timeout_mins, login_time), NOW()) < 60) AS N
INNER JOIN user_net AS UN ON N.n_id = UN.n_id
INNER JOIN users AS U ON UN.u_id = U.u_id;

Answer 1

您应该定义composite indexes对于 user_net 表。其中之一可以(并且应该)作为主键。

TABLE `user_net` (
    `u_id` BIGINT UNSIGNED NOT NULL,
    `n_id` BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (`u_id`, `n_id`),
    INDEX `uid_nid` (`n_id`, `u_id`),
    FOREIGN KEY (`u_id`) REFERENCES `users`(`u_id`),
    FOREIGN KEY (`n_id`) REFERENCES `network`(`n_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

我还将您的查询重写为:

SELECT N.netname, N.timeout_mins, N.n_id, U.userid
FROM network N
INNER JOIN user_net AS UN ON N.n_id = UN.n_id
INNER JOIN users AS U  ON UN.u_id   = U.u_id
WHERE N.is_open = 1 
  AND N.notify = 1
  AND TIMESTAMPDIFF(SECOND, TIMESTAMPADD(MINUTE, N.timeout_mins, N.login_time), NOW()) < 60

虽然您的子查询可能不会造成太大影响，但没有必要。

请注意，最后一个条件不能使用索引，因为您必须组合两列。如果您的 MySQL 版本至少为 5.7.6，您可以定义 indexed virtual (calculated) column .

CREATE TABLE `network` (
  `n_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
  `netname` VARCHAR(500) NOT NULL UNIQUE,
  `login_time` DATETIME DEFAULT NULL,
  `timeout_mins` TINYINT UNSIGNED NOT NULL DEFAULT 10,
  `is_open` TINYINT UNSIGNED,
  `notify`  TINYINT UNSIGNED,
  `timeout_dt` DATETIME AS (`login_time` + INTERVAL `timeout_mins` MINUTE),
  PRIMARY KEY (`n_id`),
  INDEX (`timeout_dt`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

现在将查询更改为:

SELECT N.netname, N.timeout_mins, N.n_id, U.userid
FROM network N
INNER JOIN user_net AS UN ON N.n_id = UN.n_id
INNER JOIN users AS U  ON UN.u_id   = U.u_id
WHERE N.is_open = 1 
  AND N.notify  = 1
  AND N.timeout_dt < NOW() + INTERVAL 60 SECOND

并且它将能够使用索引。

您也可以尝试更换

INDEX (`timeout_dt`)

与

INDEX (`is_open`, `notify`, `timeout_dt`)

看看是否有帮助。