SQL 查询是如何工作的？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2468202/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how does a SQL query work?
提问by Anand Sunderraman
How does a SQL query work?
How does it get compiled?
Is the fromclause compiled first to see if the table exists?
How does it actually retrieve data from the database?
How and in what format are the tables stored in a database?
SQL 查询如何工作？它是如何编译的？是from先编译子句看表是否存在？它实际上是如何从数据库中检索数据的？表如何以及以什么格式存储在数据库中？
I am using phpmyadmin, is there any way I can peek into the files where data is stored? I am using MySQL
我正在使用 phpmyadmin，有什么方法可以查看存储数据的文件吗？我正在使用 MySQL
采纳答案by TomTom
Well...
好...
- First you have a syntax check, followed by the generation of an expression tree - at this stage you can also test whether elements exist and "line up" (i.e. fields do exist WITHIN the table). This is the first step - any error here any you just tell the submitter to get real.
- Then yo have.... analysis. A SQL query is different from a program in that it does not say HOW to do something, just WHAT THE RESULT IS. Set based logic. So you get a query analyzer in (depending on product bad to good - oracle long time hat crappy ones, DB2 the most sensitive ones even measuring disc speed) to decide how best to approach this result. This is a really complicated beast - it may try dozens or hundreds of approaches to find one he believes to be fastest (cost based, basically some statistics).
- Then that gets executed.
- 首先进行语法检查，然后生成表达式树 - 在此阶段，您还可以测试元素是否存在并“对齐”（即字段确实存在于表中）。这是第一步 - 这里的任何错误，你都只是告诉提交者变得真实。
- 然后你有....分析。SQL 查询与程序的不同之处在于它不说明如何做某事，而只说明结果是什么。基于集合的逻辑。所以你得到一个查询分析器（取决于产品从坏到好——oracle 很长一段时间都是蹩脚的，DB2 是最敏感的，甚至测量磁盘速度）来决定如何最好地处理这个结果。这是一个非常复杂的野兽 - 它可能会尝试数十或数百种方法来找到他认为最快的方法（基于成本，基本上是一些统计数据）。
- 然后就被执行了。
The query analyzer, by the way, is where you see huge differences. Not sure about MySQL - SQL Server (Microsoft) shines in that it does not have the best one (but one of the good ones), but that it really has nice visual tools to SHOW the query plan, compare the estimates the the analyzer to the real needs (if they differ too much table statistics may be off so the analyzer THINKS a large table is small). They present that nicely visually.
顺便说一下，查询分析器是您看到巨大差异的地方。不确定 MySQL - SQL Server（微软）的亮点在于它没有最好的（但最好的之一），但它确实有很好的可视化工具来显示查询计划，比较分析器的估计真正的需求（如果它们差异太大，表统计信息可能会关闭，因此分析器认为大表很小）。他们在视觉上很好地呈现了这一点。
DB2 had a great optimizer for some time, measuring - i already said - disc speed to put it into it's estimates. Oracle went "left to right" (no real analysis) for a long time, and took user provided query hints (crap approach). I think MySQL was VERY primitive too in the start - not sure where it is now.
一段时间以来，DB2 有一个很棒的优化器，它测量 - 我已经说过 - 磁盘速度以将其纳入估计。Oracle“从左到右”（没有真正的分析）很长一段时间，并接受用户提供的查询提示（废话）。我认为 MySQL 在开始时也非常原始 - 不确定它现在在哪里。
Table format in database etc. - that is really something you should not care for. This is documented (clearly, especially for an open source database), but why should you care? I have done SQL work for nearly 15 years or so and never had that need. And that includes doing quite high end work in some areas. Unless you try building a database file repair tool.... it makes no sense to bother.
数据库等中的表格格式 - 这真的是你不应该关心的。这是记录在案的（很明显，特别是对于开源数据库），但你为什么要关心？我已经做了将近 15 年的 SQL 工作，但从未有过这种需求。这包括在某些领域做相当高端的工作。除非您尝试构建数据库文件修复工具.... 打扰是没有意义的。
回答by MAC
The order of SQL statement clause execution-
SQL语句子句执行顺序-
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY
From -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY
My answer is specific to Oracle database, which provides tutorials pertaining to your queries. Well, when SQL database engine processes any SQL query/statement, It first starts parsingand within parsing it performs three checks Syntax, Semanticand Shared Pool. To know how do these checks work? Follow the link below.
我的回答是针对 Oracle 数据库的，它提供了与您的查询相关的教程。好吧，当 SQL 数据库引擎处理任何 SQL 查询/语句时，它首先开始解析，并在解析中执行三项检查Syntax、Semantic和Shared Pool。想知道这些检查是如何工作的？按照下面的链接。
Once query parsing is done, it triggers the Executionplan. But hey Database Engine! you are smart enough. You do check if this SQL query has already been parsed (Soft Parse), if so then you directly jump on execution plan or else you deep dive and optimize the query (Hard Parse). While performing hard parse, you also use a software called Row Source Generationwhich provides Iterative Execution Planreceived from optimizer. Enough! see the SQL query processing stages below.
一旦查询解析完成，它就会触发执行计划。但是，嘿数据库引擎！你够聪明。您确实会检查此 SQL 查询是否已被解析（Soft Parse），如果是，则直接跳转到执行计划，否则您将深入研究并优化查询（Hard Parse）。在执行硬解析时，您还可以使用名为Row Source Generation的软件，该软件提供从优化器接收的迭代执行计划。足够的！请参阅下面的 SQL 查询处理阶段。
Note -Before execution plan, it also performs Bindoperations for variable's values and once the query is executed It performs Fetchto obtain the records and finally store into result set. So in short, the order is-
注意 -在执行计划之前，它还对变量的值执行绑定操作，一旦执行查询，它执行Fetch以获取记录并最终存储到结果集中。简而言之，顺序是——
PASRE -> BIND -> EXECUTE -> FETCH
PASRE -> BIND -> EXECUTE -> FETCH
And for in depth details, this tutorialis waiting for you. This may be helpful to someone.
对于更深入的细节，本教程正等着您。这可能对某人有帮助。
