如何优化MySQL查询，即使它有索引

Question

这是我的查询，需要 17.9397 秒 的时间才能得到响应：

SELECT allbar.iBarID AS iBarID,
       allbar.vName AS vName,
       allbar.tAddress AS tAddress,
       allbar.tDescription AS tDescription,

  (SELECT COUNT(*)
   FROM tbl_post p
   WHERE p.vBarIDs = allbar.iBarID) AS `total_post`,
       allbar.bar_usbg AS bar_usbg,
       allbar.bar_enhance AS bar_enhance,

  (SELECT count(*)
   FROM tbl_user
   WHERE FIND_IN_SET(allbar.iBarID,vBarIDs)
     AND (eType = 'Bartender'
          OR eType = 'Bar Manager'
          OR eType = 'Bar Owner')) AS countAss,
       allbar.eStatus AS eStatus
FROM
  (SELECT DISTINCT b.iBarID AS iBarID,
                   b.vName AS vName,
                   b.tAddress AS tAddress,
                   (CASE LENGTH(b.tDescription) WHEN 0 THEN '' WHEN LENGTH(b.tDescription) > 0
                    AND LENGTH(b.tDescription) < 50 THEN CONCAT(LEFT(b.tDescription, 50),'...') ELSE b.tDescription END) AS tDescription,
                   b.usbg AS bar_usbg,
                   b.enhance AS bar_enhance,
                   b.eStatus AS eStatus
   FROM tbl_bar b,
        tbl_user u
   WHERE b.iBarID <> '-10') AS allbar

我已经尝试了EXPLAIN，以下是结果:

有人能解释一下这个EXPLAIN的结果吗？

- Pathik Vejani

Answer 1

你应该彻底重写那个查询语句，它完全没有意义。

在这部分中

  (SELECT DISTINCT b.<whatever>
   FROM tbl_bar b,
        tbl_user u
   WHERE b.iBarID <> '-10') AS allbar

您基本上正在将tbl_bar表中的每一行与tbl_user表中的每一行连接起来。然后过滤tbl_bar表，在选择了所有内容之后（也许MySQL在执行此操作之前必须先将所有内容写入临时表），返回没有重复项的结果集。您绝不希望这样做，特别是当您甚至没有从tbl_user表中选择任何内容时。如果有连接，请指定连接；如果没有连接，请不要连接这些表或创建连接。我不知道您的表是否连接，但应该像这样：

  (SELECT DISTINCT b.<whatever>
   FROM tbl_bar b
   JOIN tbl_user u ON b.user_id = u.id /*or whatever the connection is*/
   WHERE b.iBarID <> '-10') AS allbar

然后你有这个丑陋的子查询。

  (SELECT COUNT(*)
   FROM tbl_post p
   WHERE p.vBarIDs = allbar.iBarID) AS `total_post`,
       allbar.bar_usbg AS bar_usbg,
       allbar.bar_enhance AS bar_enhance,

顺便提一下，这是一个依赖关系的子查询（请参见您的解释输出）。这意味着，该子查询将为您外部查询的每一行执行一次（是的，就是我们以上讨论的那个带有交叉连接的查询）。不要使用此子查询，而是在外部查询中加入表格，并使用 GROUP BY 进行操作。

到目前为止，查询应该看起来像这样：

SELECT 
b.iBarID AS iBarID,
b.vName AS vName,
b.tAddress AS tAddress,
b.tDescription AS tDescription,

COUNT(*) AS `total_post`,
allbar.bar_usbg AS bar_usbg,
allbar.bar_enhance AS bar_enhance

FROM
tbl_bar b
JOIN tbl_user u ON b.user_id = u.id
JOIN tbl_post p ON p.vBarIDs = b.iBarID
WHERE b.iBarID <> '-10'
GROUP BY b.iBarID 

< p > < em >（实际上，这并不完全正确。规则是，在SELECT子句中的每一列都应该在GROUP BY子句中或者对它应用聚合函数（如count()或max()）。否则，每个组将显示一个随机行。但这只是一个例子，你需要进一步工作出细节。）

现在最糟糕的部分来了。

  (SELECT count(*)
   FROM tbl_user
   WHERE FIND_IN_SET(allbar.iBarID,vBarIDs)
     AND (eType = 'Bartender'
          OR eType = 'Bar Manager'
          OR eType = 'Bar Owner')) AS countAss,
       allbar.eStatus AS eStatus

使用FIND_IN_SET()表明您正在一个列中存储多个值。再次强调，您永远不应该这样做。请阅读此答案：在数据库列中存储分隔列表真的很糟糕吗？，然后重新设计您的数据库。我不会帮助您解决这个问题，因为这显然是另一个问题。

所有这些并没有真正解释EXPLAIN结果。对于这个问题，我需要写一篇完整的教程，但我不会这样做，因为所有内容都在手册中，如常所述。

- fancyPants

---

谢谢您的回复。但是我通过使用“JOIN”使它更加顺畅。 :) 您的答案是可以接受的，我刚刚看到了。 - Pathik Vejani