查询结果在200K数据库上花费的时间太长，是否加快了提示？

Question

我有一条sql语句，其中我连接了大约4个表，每个表有200K行。查询运行，但一直处于冻结状态。当我在3个表上进行连接时，它会返回行(大约需要10秒)。有什么建议吗？提速的建议？

谢谢!

代码

SELECT *
FROM equipment, tiremap, workreference, tirework
WHERE equipment.tiremap = tiremap.`TireID` AND 
      tiremap.`WorkMap` = workreference.`aMap` AND
      workreference.`bMap` = tirework.workmap
LIMIT 5

p.s

如果有用的话，我使用sql alchemy来生成此代码，其中的sqlalchemy代码是

query = session.query(equipment, tiremap, workreference, tirework)
query = query.filter(equipment.c.tiremap == tiremap.c.TireID)
query = query.filter(tiremap.c.WorkMap==workreference.c.aMap)
query = query.filter(workreference.c.bMap == tirework.c.workmap)
query = query.limit(5)
query.all()

Answer 1

确保您的索引位于以下位置：

• equipment (tiremap)
• tiremap (TireID)
• tiremap (WorkMap)
• workreference (aMap)
• workreference (bMap)
• tirework (
  • equipment(tiremap)
  • tiremap(TireID)
  • tiremap(WorkMap)
  • workreference(aMap)
  • workreference(bMap)
  • tirework (workmap)

  )

编辑：我想我应该为它提供一些完整的上下文。

SQL优化器查看语句，对其进行解析，然后根据查询、引用的表和可用的索引确定语句的执行计划。如果使用SELECT * FROM tab1，那么它将对tab1执行全表扫描，因为没有其他方法可以执行该操作。

如果您使用SELECT * FROM person WHERE lastname LIKE 'V%'，并且您有一百万条记录，那么查询每一行都会很慢，但是如果对lastname进行索引，效率会高得多。

对于像您这样的查询，其中一个表将是驱动表，无论索引如何，都可以简单地作为全表扫描来完成。这没什么不对的。必须有一个表来驱动查询。如果存在WHERE子句(用于连接条件以外的其他条件)，则可能会发生变化，但在其他情况下通常是正确的。

然后，MySQL将从该驱动表开始将联接追加到执行计划中。这些连接需要另一端的索引才能有效地工作。

因此，对于三个表，您可能有一个没有索引的表，但这并不重要，因为它驱动查询。对于第四个表，可能有两个未索引的表，这现在是一个问题，因为对于一个MySQL中的每一行，必须对另一个进行全表扫描。

因此，基本上是在每个外键和连接列上创建一个索引，这样MySQL就可以使用可用的内容为您提供的查询制定最佳的执行计划。

最后，大多数工具将告诉您有关数据库模式的信息。PHPMyAdmin是用于托管数据库的一种流行的方法。就我个人而言，我实际上喜欢桌面应用程序来做这类事情。在这方面，Navicat Lite是一个不错的免费工具。

Answer 2

您正在进行4个表的自然连接。此外，在您的"WHERE“语句中，没有特殊条件。

数据库引擎将执行以下操作：

它将首先对每个表中的所有数据进行递归乘积。

考虑表A、B和C中的以下行：

A = rowA1
    rowA2
    rowA3;
B = rowB1
    rowB2
    rowB3;
C = rowC1
    rowC2
    rowC3;

基本上，如果你对这3个表进行自然连接，引擎将在内存中：

rowA1 - rowB1 - rowC1
rowA1 - rowB1 - rowC2
rowA1 - rowB1 - rowC3
rowA1 - rowB2 - rowC1
rowA1 - rowB2 - rowC2
rowA1 - rowB2 - rowC3
rowA1 - rowB3 - rowC1
rowA1 - rowB3 - rowC2
rowA1 - rowB3 - rowC3
...
...
...
rowA3 - rowB3 - rowC1
rowA3 - rowB3 - rowC2
rowA3 - rowB3 - rowC3

总共有27行被放入内存。但是，我们只需要3行：

rowA1 - rowB1 - rowC1
rowA2 - rowB2 - rowC2
rowA3 - rowB3 - rowC3

如果您的数据库引擎本身不进行优化，那么3个表的自然连接是非常昂贵的。对于4个表，即使是有限数量的行，这也是不可想象的。

现在，我们怎样才能得到更好的东西呢？

首先，通过查看代码，我们知道我们只需要5个值。另外，在数据库优化中，据说应该使选择尽可能早。

这里有一些未经测试的代码，它们应该会对您有所帮助。您可能需要修改它，具体取决于您使用的数据库引擎：

SELECT *
FROM (SELECT * FROM equipment LIMIT 5) e, tiremap, workreference, tirework
WHERE e.tiremap = tiremap.TireID AND
      tiremap.WorkMap = workreference.`aMap` AND
      workreference.`bMap` = tirework.workmap

这样做会让人感觉我们只有3张表，而不是4张，但这并不是你想要的。如果在其他表中没有引用一行"equipment“，那么最终得到的行数将少于5行。然而，这是一个向您展示我们可能并不真正需要所有表中的所有行的示例。

现在，我想你想要的可能是：

SELECT * FROM equipment 
INNER JOIN tiremap ON equipment.tiremap = tiremap.TireID
INNER JOIN workreference ON tiremap.WorkMap = workreference.aMap
INNER JOIN tirework ON workreference.bMap = tirework.workmap
LIMIT 5

您可能会遇到一个问题:如果您的引擎不是很好(mySQL，对不起)，那么它可能需要很长时间。

如果你真的想自己做优化：

SELECT * FROM tirework, 
   (SELECT * FROM workreference, 
       (SELECT * FROM tiremap,
           (SELECT * FROM equipment) e
        WHERE e.tiremap = tiremap.TireID) t
    WHERE t.WorkMap = workreference.aMap) w
WHERE w.bMap = tirework.workmap
LIMIT 5

就是这样！即使您的引擎优化器不存在，该查询也不会花费太长时间。你的引擎不会把所有东西都做成一个大的产品，而是一次只做一个产品，在把它和一个新的表连接起来之前，把不好的行去掉。

试试看。

Answer 3

这可能是因为您要加入的第四个表比其他表大得多。也可能是您要联接的列上没有索引。