代码之家 › 专栏 › 技术社区 › walnutmon

如何优化数据库中的查询-基础知识

query-optimization optimization database mysql sql

walnutmon · 技术社区 · 15 年前

似乎所有关于这个主题的问题都非常具体,虽然我重视具体的示例,但我对SQL优化的基础很感兴趣。我非常熟悉使用SQL,并且有硬件/低级软件的背景。

我想要的是工具,既有有形的软件,也有一种方法来查看我定期查看的MySQL数据库,并知道join语句的顺序和where语句之间的区别。

我想知道一个索引为什么有帮助,比如,确切的原因。我想知道具体发生了什么不同的事情,我想知道我如何真正地看待正在发生的事情。我不需要一个工具来分解我的SQL的每一个步骤,我只是想能够到处看看,如果有人不能告诉我要索引哪个列,我就可以拿出一张纸,在一段时间内,我就可以找到答案。

数据库是复杂的,但它们并不那么复杂,而且一定有一些很好的材料可以用来学习基础知识,这样你就知道如何找到你遇到的优化问题的答案,即使你可以在论坛上找到确切的答案。

请推荐一些简明扼要、直观的阅读方式,不要害怕接触到低级别的螺母和螺栓。我更喜欢网上免费的资源,但如果一本书的推荐书破坏了它的成功,我会考虑接受它。

5 回复 | 直到 8 年前

Jay 15 年前

您必须查找每个where条件和每个join…条件。两个工作原理相同。

假设我们写信

select name
from customer
where customerid=37;

不知为何,DBMS必须找到一个或多个customerid=37的记录。如果没有索引,那么执行此操作的唯一方法是读取表中的每个记录,将customerid与37进行比较。即使找到了一个,它也无法知道只有一个,所以它必须不断地寻找其他人。

如果在customerid上创建一个索引,DBMS可以很快地搜索该索引。这不是顺序搜索,但取决于数据库、二进制搜索或其他有效方法。确切地说,有什么不重要的,接受它比顺序要快得多。然后,索引将其直接带到适当的一个或多个记录中。此外,如果您指定索引是“唯一的”,那么数据库知道只能有一个索引,因此它不会浪费时间寻找第二个索引。(DBMS将阻止您添加一秒钟。)

现在考虑这个查询:

select name
from customer
where city='Albany' and state='NY';

现在我们有两个条件。如果只有一个字段上有索引,DBMS将使用该索引查找记录的一个子集,然后按顺序搜索这些记录。例如,如果您有状态索引,DBMS将快速找到NY的第一条记录,然后按顺序搜索查找city='albany',当它到达NY的最后一条记录时停止查找。

如果您有一个包含这两个字段的索引,即“在客户(州、市)上创建索引”,那么DBMS可以立即缩放到正确的记录。

如果您有两个单独的索引,每个字段一个,那么DBMS将应用各种规则来决定要使用哪个索引。同样,如何做到这一点取决于您所使用的特定DBMS,但基本上,它试图保持对记录总数、不同值的数量和值分布的统计。然后它将按顺序搜索这些记录以查找满足其他条件的记录。在这种情况下,DBMS可能会观察到有比州更多的城市,因此通过使用城市索引,它可以快速地缩放到“奥尔巴尼”记录。然后它将按顺序搜索这些内容,并对照“ny”检查每个内容的状态。如果你有加利福尼亚州奥尔巴尼的记录,这些将被跳过。

每个连接都需要某种查找。

说我们写

select customer.name
from transaction
join customer on transaction.customerid=customer.customerid
where transaction.transactiondate='2010-07-04' and customer.type='Q';

现在,DBMS必须决定首先读取哪个表,从中选择适当的记录,然后在另一个表中找到匹配的记录。

如果您对transaction.transactionDate和customer.customerID有索引,那么最好的计划可能是查找所有具有此日期的交易,然后为每个交易查找具有匹配customerID的客户,然后验证该客户的类型是否正确。

如果在customer.customerid上没有索引,那么DBMS可以快速找到事务,但是对于每个事务,它必须按顺序搜索customer表以查找匹配的customerid。(这可能非常慢。)

假设您拥有的唯一索引是transaction.customerid和customer.type。那么DBMS可能会使用完全不同的计划。它可能会扫描customer表中所有具有正确类型的客户,然后针对每个客户查找此客户的所有交易,并按顺序搜索这些交易以查找正确的日期。

优化的最重要的关键是找出哪些索引真正有帮助,并创建这些索引。另外,未使用的索引对数据库来说是一个负担,因为维护它们需要工作,如果它们从未被使用过,这是浪费的工作。

您可以使用explain命令来告诉DBMS将用于任何给定查询的索引。我一直使用这个方法来确定我的查询是否得到了很好的优化,或者是否应该创建额外的索引。(有关该命令输出的说明,请阅读该命令的文档。)

注意:请记住,我说过DBMS会对每个表中的记录数量和不同值的数量等进行统计。如果数据发生变化,“解释”可能会给你一个与昨天完全不同的计划。例如,如果您有一个连接两个表的查询,其中一个表非常小,而另一个表很大,那么它将倾向于先读取小表,然后在大表中查找匹配的记录。向表中添加记录可能会改变较大的值,从而导致DBMS更改其计划。因此,您应该尝试对具有实际数据的数据库进行解释。在每个表中有5条记录的测试数据库上运行比在活动数据库上运行要小得多。

嗯,还有很多话可以说,但我不想在这里写一本书。

Amadan 15 年前

假设你在另一个城市找朋友。一种方法是挨家挨户地问这是否是你要找的房子。另一种方法是看地图。

索引是到表的映射。它可以准确地告诉数据库引擎你要找的东西在哪里。因此,您为您认为必须搜索的每一列编制索引,并删除您刚刚从中读取数据的列,而不搜索这些列。

良好的技术阅读能力 about indices 和 about ORDER BY optimization . 如果你想知道到底发生了什么,你需要 EXPLAIN 语句。

harpo Binary Worrier 15 年前

不要考虑优化数据库。考虑优化查询。

通常,您会以牺牲其他案例为代价优化一个案例。你只需决定你感兴趣的案件。

Erwin Smout 15 年前

“我对索引将如何影响联接特别感兴趣。”

作为一个例子,我将以equijoin为例(从a,b中选择,其中a.x=b.y)。

如果根本没有索引(理论上是可能的,但我认为不是在SQL中),那么基本上计算联接的唯一方法是取整个表A并将其分区到X上,取整个表Y并将其分区到Y上,然后匹配分区,最后对每对匹配分区计算结果行。对于除了最小的表以外的所有表来说,这都是昂贵的(甚至完全不可能,因为内存限制)。

如果A和/或B上确实存在索引,但没有任何索引具有x resp,那么情况也是一样的。Y是它的第一个属性。

如果X上确实存在索引,但Y上不存在索引(或相反),则会打开另一种可能性:扫描表B,对于每行选择值Y,在索引中查找该值并获取相应的A行以计算联接。请注意,如果没有其他限制(和z=……)的话,这仍然不会给您带来多少好处-除非在x和y值之间只有很少的匹配。

如果X和Y上都存在有序索引(基于哈希的索引没有排序),那么第三种可能性就出现了:对索引本身进行匹配扫描(索引本身可能比表本身小,因此扫描索引本身需要更短的时间),对于匹配的X/Y值,计算相应的行。

这就是底线。X>Y等连接出现变化。

Makis 15 年前

我不知道MySQL工具,但在MS SQLServer中,有一个工具可以显示查询所需的所有操作,以及整个查询的处理时间。

使用这个工具帮助我了解查询优化器是如何优化查询的,这比我认为任何一本书都能帮助的多,因为优化器所做的通常不容易理解。通过调整查询和可能的下划线数据库,我可以看到每个更改是如何影响查询计划的。在编写查询时有一些关键点,但对我来说,似乎您已经了解了这些关键点,因此在您的案例中,优化比任何一般规则都更重要。经过几年的数据库开发,我确实看了几本专门针对SQL Server上数据库优化的书,发现很少有用的信息。

快速谷歌发现了这个: http://www.mysql.com/products/enterprise/query.html 这听起来像是一个类似的工具。

当然,在查询级别上,数据库级别的优化又是另一回事,但在这里,您将看到一些参数,例如数据库在硬盘上的划分方式等。至少在sqlserver中,您可以选择将表划分到不同的HDD,甚至磁盘,这会产生很大的影响,因为驱动器驱动头可以并联工作。另一个问题是如何构建查询,以便数据库可以并行地在多个线程和处理器中运行它们,但这两个问题再次取决于数据库引擎,甚至是您使用的版本。