在编写高效的SQL时，你能遇到的影响最大的就是索引。然而，一个很重要的事实是，很多SQL客户端要求数据库做很多“不必要的强制性工作”。跟我重复一遍：不必要的强制性工作什么是“不必要的强制性工作”？此含义有两个方面：不必要地假设您的客户端应用程序需要此信息：这没什么特别的。我们运行一个电影数据库（例如Sakila数据库），我们希望向用户显示每部电影的标题和评分。这是产生上述结果的查询：SELECTtitle,ratingFROMfilm然而，我们的应用程序（或我们的ORM（LCTT注释：对象关系映射））运行的查询是：SELECT*FROMfilm我们得到了什么？猜一下。我们得到了很多无用的信息：甚至一些复杂的JSON数据也被一路加载：从磁盘到缓存通过总线进入客户端内存然后丢弃是的，我们丢弃了大部分。检索它所做的工作是完全没有必要的。正确的？确切地。强制性这是最糟糕的部分。随着优化器变得越来越智能，这些天对于数据库来说是强制性的。数据库无法知道客户端应用程序实际上不需要95%的数据。这只是一个简单的例子。想象一下，如果我们连接更多的表……你想想会发生什么？数据库快吗？再来看一些你之前可能没有想到的地方：内存消耗当然单次执行时间不会有很大的变化。也许慢1.5倍，但我们可以接受，对吧？为了方便，有时是这样。但是，如果您每次都为了方便而牺牲性能，那就很重要了。我们不谈性能问题（单个查询的速度），但是当我们关注吞吐量（系统响应时间）时，事情就变得困难和难以解决了。你坚持缩放。我们看一下执行计划，是Oracle的：----------------------------------------------|Id|Operation|Name|Rows|Bytes|-------------------------------------------------|0|SELECTSTATEMENT||1000|166K||1|TABLEACCESSFULL|FILM|1000|166K|-----------------------------比较：---------------------------------------------|Id|Operation|Name|行|字节|-------------------------------------------------|0|SELECTSTATEMENT||1000|20000||1|TABLEACCESSFULL|FILM|1000|20000|------------------------------------------------当执行SELECT*而不是SELECTfilm,rating时，我们在数据库中使用了8倍的内存。不足为奇，对吧？我们早就知道了。我们在许多不需要其中所有数据的查询中这样做。我们为数据库创建了不必要的强制性工作，累积的结果是我们使用了高达8倍的内存（当然，数值可能会有所不同）。现在，所有其他步骤（例如，磁盘I/O、总线传输、客户端内存消耗）都以同样的方式受到影响，我在这里跳过它们。另外，我还想看看...索引的使用现在大多数数据库都有一个覆盖索引（LCTT译注：覆盖索引，包括你需要查询的列的索引，甚至更多的列，你可以得到所有需要的列直接从索引数据而不访问物理表）。覆盖索引不是特殊索引。但是对于一个特定的查询，它可以“不小心”或人为地变成一个“特殊索引”。看看这个查询：SELECT*FROMactorWHERElast_nameLIKE'A%'执行计划没有什么特别的。这只是一个简单的查询。索引范围扫描，表访问，结束：---------------------------------------------------------------------|Id|Operation|Name|Rows|------------------------------------------------------------------|0|SELECTSTATEMENT||8||1|TABLEACCESSBYINDEXROWID|ACTOR|8||*2|INDEXRANGESCAN|IDX_ACTOR_LAST_NAME|8|---------------------------------------------这是一个好计划？如果这就是我们想要的，那就不是：当然，我们正在浪费内存等等。我们再看看这个查询：SELECTfirst_name,last_nameFROMactorWHERElast_nameLIKE'A%'它的计划是：------------------------------------------------|Id|Operation|Name|Rows|--------------------------------------------------|0|SELECTSTATEMENT||8||*1|INDEXRANGESCAN|IDX_ACTOR_NAMES|8|------------------------------------------------现在我们可以完全消除表访问，因为有一个索引涵盖了我们查询所需的一切……覆盖索引。有关系吗？当然！这种方法可以将你的一些查询加速一个数量级（如果在某个更改后你的索引不再覆盖，则可能会慢一个数量级）。您不能总是从覆盖索引中获利。索引也有自己的成本，你不应该添加太多的索引，这样是不明智的。让我们来做个测试：SETSERVEROUTPUTONDECLAREv_tsTIMESTAMP;v_repeatCONSTANTNUMBER:=100000;BEGINv_ts:=SYSTIMESTAMP;FORiIN1..v_repeatLOOPFORrecIN(--Worstquery:MemoryoverheadANDtableaccessSELECT*FROMactorWHERElast_nameLIKE'A%')LOOPNULL;ENDLOOP;ENDLOOP;dbms_output.put_line('Statement1:'||(SYSTIMESTAMP-v_ts));v_ts:=SYSTIMESTAMP;FORiIN1..v_repeatLOOPFORrecIN(--Betterquery:StilltableaccessSELECT/*+INDEX(actor(last_name))*/first_name,last_nameFROMactorWHERElast_nameLIKE'A%')LOOPNULL;ENDLOOP;ENDLOOPdbms_output.put_line('Statement2:'||(SYSTIMESTAMP-v_ts));v_ts:=SYSTIMESTAMP;FORiIN1..v_repeatLOOPFORrecIN(--Bestquery:CoveringindexSELECT/*+INDEX(actor(last_name,first_name))*/first_name,last_nameFROMactorLIWHERElast_nameA%')LOOPNULL;ENDLOOP;ENDLOOP;dbms_output.put_line('Statement3:'||(SYSTIMESTAMP-v_ts));END;/结果为：Statement1:+00000000000:00:02.479000000Statement2:+00000000000:00:02.2610030000:+00000000000:00:01.857000000注意表actor只有4列，所以状态之间的区别ments1和2不太令人印象深刻意义深远，但仍然需要注意的是，我使用了Oracle的提示来强制优化器为查询选择一个或另一个索引。在这种情况下，语句3显然胜出。这是一个更好的查询，而且非常简单。当我们写SELECT*语句时，给数据库带来了不必要的强制性工作，无法优化。它不会使用覆盖索引，因为覆盖索引比它使用的LAST_NAME索引要贵一点，而且无论如何它都必须访问表以获取无用的LAST_UPDATE列。使用SELECT*会变得更糟。想一想……SQL转换优化器工作得非常好，因为它们会转换您的SQL查询（请参阅我最近在VoxxedDaysZurich上就此发表的演讲）。例如，有一个叫做“表连接消除”的转换非常强大。看看这个辅助视图，我们写这个视图是因为我们真的很讨厌总是连接所有这些表：JOINcountrycoUSING(country_id)视图简单地连接了CUSTOMER和它们不同的ADDRESS部分之间的所有“一对一”关系。谢天谢地，它很整洁。现在，在使用这个视图一段时间后，想象一下我们已经习惯了这个视图，以至于我们忘记了它的所有底层表。然后，我们运行这个查询：SELECT*FROMv_customer并且我们得到了一个非常令人印象深刻的计划：----------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost|------------------------------------------------------------|0|SELECTSTATEMENT||599|47920|14||*1|HASHJOIN||599|47920|14||2|TABLEACCESSFULL|国家|109|1526|2||*3|HASHJOIN||599|39534|11||4|TABLEACCESSFULL|城市|600|10800|3||*5|HASHJOIN||599|28752|8||6|TABLEACCESSFULL|客户|599|11381|4||7|TABLEACCESSFULL|地址|603|17487|3|------------------------------------------------当然如此。我们运行了所有这些表连接和全表扫描，因为这就是我们要求数据库做的事情：获取所有数据。现在，再想想，对于一个特定的场景，我们真正想要什么：是的，对吧？现在你应该明白我的意思了。但是想象一下，我们确实从以前的错误中吸取了教训，现在我们实际上运行了一个更好的查询：SELECTfirst_name,last_nameFROMv_customer并再次查看结果！---------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost|----------------------------------------------------------------|0|SELECTSTATEMENT||599|16173|4||1|NESTEDLOOPS||599|16173|4||2|TABLEACCESSFULL|CUSTOMER|599|11381|4||*3|INDEXUNIQUESCAN|SYS_C007120|1|8|0|----------------------------------------------------------------这是执行计划A巨大的进步。我们的表连接被消除了，因为优化器可以证明它们是不必要的，所以一旦它可以证明这一点（并且你没有通过使用select*使其成为强制性工作），它可以删除这些不执行它的工作。为什么会这样？每个CUSTOMER.ADDRESS_ID外键保证有且只有一个ADDRESS.ADDRESS_ID主键值，所以保证JOIN操作是一对一的连接，不会产生或删除行。如果我们甚至不选择行或查询行，我们当然不需要实际加载行。可证明删除JOIN不会更改查询的结果。数据库一直在做这些事情。您可以在大多数数据库上尝试此操作：--OracleSELECTCASEWHENEXISTS(SELECT1/0FROMdual)THEN1ELSE0ENDFROMdual--更合理的SQL语句，例如PostgreSQLSELECTEXISTS(SELECT1/0)在这种情况下，当您运行此查询时，您可能会抛出算术异常：SELECT1/0FROMdualproduced:ORA-01476:divisorisequaltozero但它没有发生。优化器（甚至解析器）可以证明EXISTS(SELECT..)谓词内部的任何SELECT列表达式都不会改变查询的结果，因此无需评估其值。哼！另外……大多数ORM最不幸的问题是它们非常随意地编写SELECT*查询。其实比如HQL/JPQL，默认是设置使用的。您甚至可以完全放弃SELECT子句，因为毕竟您想要获取声明的所有实体，对吧？例如：FROMv_customerVladMihalcea（Hibernate专家和Hibernate开发倡导者）建议在每次确定不想在获取后进行任何更改时使用查询。ORM可以轻松解决对象图持久性问题。注：坚持。实际修改对象图并持久化修改的想法是与生俱来的。但如果您不想这样做，为什么要获取实体？为什么不写一个查询？让我们明确一点：从性能的角度来看，为您正在解决的用例编写查询将始终胜过其他选项。你可能不在乎，因为你的数据集很小，所以没关系。能。但最后，您需要扩展和重新设计您的应用程序以在强制实体图遍历之上支持查询语言，这变得困难。您还需要做其他事情。当您只想检查是否存在时运行COUNT(*)查询时，计算出现次数是最浪费的。例如：这个用户有订单吗？我们会运行：SELECTcount(*)FROMordersWHEREuser_id=:user_id很简单。如果COUNT=0：没有订单。否则：是的，有命令。性能可能不会那么差，因为我们可能在ORDERS.USER_ID列上有一个索引。但是你觉得上面的性能和下面的相比如何：--OracleSELECTCASEWHENEXISTS(SELECT*FROMordersWHEREuser_id=:user_id)THEN1ELSE0ENDFROMdual--更合理的SQL语句，比如不需要火箭科学家的PostgreSQLSELECTEXISTS(SELECT*FROMordersWHEREuser_id=:user_id)以确保实际存在谓词一旦找到就可以停止寻找其他行。因此，如果答案是“无订单”，则速度将大致相同。但是如果结果是“有，有订单”，那么这个结果就大大加快了，不用我们去统计具体的次数。因为我们不关心确切的次数。我们告诉数据库去计算它（非必要），数据库不知道我们会丢弃所有大于1的结果（强制）。当然，如果您在JPA支持的集合上调用list.size()来做同样的事情，事情会变得更糟！我最近写了一篇关于这个的博客，并在不同的数据库上对其进行了测试。去看看吧。总结文章的立场是“显而易见的”。不要让数据库做不必要的强制性工作。这不是必需的，因为对于您给定的要求，您知道某些工作不需要完成。但是，您告诉数据库去做。这是强制性的，因为数据库无法证明它是不必要的。此信息仅包含在客户端中，服务器无法访问。所以需要数据库来做。本文的大部分内容都是关于SELECT*的，因为它是一个如此简单的目标。但它不仅限于数据库。这涉及客户端要求服务器执行不必要的强制性工作的任何分布式算法。您的AngularJS应用程序平均有多少个N+1问题，其中UI在服务结果A上循环并多次调用服务B，而不是将对B的所有调用打包到一个调用中？这是一个反复出现的模式。解决方案总是一样的。您向执行命令的实体提供的信息越多，它（理论上）执行此类命令的速度就越快。每次都写一个好的查询。您的整个系统都会为此感谢您。如果您喜欢这篇文章...请查看我最近在苏黎世VoxxedDays上的演讲，在那里我展示了一些夸张的例子，说明了为什么SQL在数据处理算法上总是胜过Java。（标题图片：Pixabay，CC0）
