mysql - 我如何在mysql中优化这个查询？

Question

我有一个这样的查询 -

    SELECT 
  CONCAT(u.name, u.deviceAlias),
  u.name,
  u.deviceAlias,
  j.description,
  u.description AS vlanDescription 
FROM
  vlaninterfaces u,
  interfacedescriptioninfo j 
WHERE u.interfacedescriptioninfoid = j.id
  AND u.deviceId = j.deviceId 
  AND u.interfaceNameAlias = j.name 
  AND j.toDevice NOT IN 
  (SELECT 
    deviceAlias 
  FROM
    devices 
  WHERE siteId = 12) 
  AND u.deviceAlias IN 
  (SELECT 
    deviceAlias 
  FROM
    devices 
  WHERE siteId = 12) 
  AND CONCAT(u.name, u.deviceAlias) NOT IN 
  (SELECT DISTINCT 
    CONCAT(v.name, fromDevice) 
  FROM
    vlaninterfaces v,
    interfacedescriptioninfo i 
  WHERE v.interfacedescriptioninfoid = i.id
    AND v.deviceId = i.deviceId 
    AND v.interfacenameAlias = i.name 
    AND v.deviceAlias IN 
    (SELECT 
      deviceAlias 
    FROM
      devices 
    WHERE siteId = 12) 
    AND v.interfacenameAlias LIKE '%ae%' 
    AND IF(
      i.toDevice IS NULL,
      i.interconnectedDevice,
      i.toDevice
    ) IN 
    (SELECT 
      deviceAlias 
    FROM
      devices 
    WHERE deviceClass = 'SWITCH' 
      AND siteId = 12)) 
GROUP BY CONCAT(u.name, u.deviceAlias) ;

我尝试使用 MINUS 但发现在 mysql 中我们不能使用这样的集合运算符。

在解释计划中，表 vlaninterfaces 不使用键并扫描所有行。

请让我知道 NOT IN 的替代示例(如果可能的话相同的查询)。

Answer 1

您的查询非常复杂，所以我只显示部分。可以 LEFT JOIN 到表上并检查 null 而不是使用 NOT IN。 IE。如果我们有这样的查询:

 SELECT 
  CONCAT(u.name, u.deviceAlias),
  u.name,
  u.deviceAlias,
  j.description,
  u.description AS vlanDescription 
FROM
  vlaninterfaces u,
  interfacedescriptioninfo j 
WHERE u.interfacedescriptioninfoid = j.id
  AND u.deviceId = j.deviceId 
  AND u.interfaceNameAlias = j.name 
  AND j.toDevice NOT IN 
  (SELECT 
    deviceAlias 
  FROM
    devices 
  WHERE siteId = 12) 

然后我们可以使用 siteId 12 连接到设备别名，并检查 where 子句中的连接是否成功(请注意，在下面的示例中，我还将 vlaninterfaces 和 interfacedescriptioninfo 上的连接更改为显式 - 我发现这更具可读性):

SELECT 
  CONCAT(u.name, u.deviceAlias),
  u.name,
  u.deviceAlias,
  j.description,
  u.description AS vlanDescription 
FROM
  vlaninterfaces u
  INNER JOIN interfacedescriptioninfo j ON (u.interfacedescriptioninfoid = j.id AND u.deviceId = j.deviceId AND u.interfaceNameAlias = j.name )
  LEFT JOIN devices da ON (j.toDevice = da.deviceAlias AND da.siteId = 12)
WHERE 
   da.deviceAlias IS NULL