数据库查询的内部实现
根据我的经验，我在 mysql、sql-server、oracle 等中使用过许多查询，例如 select、order by、where 子句等。 我想了一会儿，
1）内部是如何编写来实现上述查询的？
2）他们使用哪种语言？
3）那是编程语言吗？如果是，哪种语言？
4）实现这种复杂的数据库需要什么样的环境
In my experience I have used many queries like select, order by, where clause etc.. in mysql, sql-server, oracle etc
For a moment i have thought,
1)how is this internally written to implement the above queries
2) which language do they use?
3) is that programming language? if yes which language?
4)what kind of environment required to implement this kind of complex database
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
1) 在原理图形式中，您将按照以下步骤进行：
a) 将查询拆分为其组件并创建查询的抽象语法树 (AST)。有一些工具可以做到这一点，过去使用 lex 和 yacc 来完成此任务，现在有更多的选择。
b) 在第一步中，优化器将通过应用已知的相等转换来重新组织树，因此查询将通过使用索引、执行首先返回很少结果的查询来实现最高效的方式，这样您就不需要连接，等等...
c)您可以遍历这棵树来实现对数据库和返回的数据的小操作。通常，这会导致 AST 节点中出现“虚拟临时”表
d) 从顶部节点收集内容并将其返回给客户端
2-3) 我认为没有特殊语言。很多是用C语言的，但也有使用Java和其他语言
4）我认为最好的环境是这种工作的安静环境。 ;-)
真正困难的工作不在于 SQL 解释器/编译器，而在于详细的数据结构以及保持一切有效组织和动态调整以适应情况的实质内容，以保持数据库的性能。
1) In schematic form you would proceed as follows :
a) Split the query in its components and create a Abstract Syntax Tree (AST) of the query. There are tools to do this, in the olden days lex and yacc were used for this, now there is a lot more choice.
b) In a first step an optimizer will reorganize the tree by applying known equal transformations so the query will be most performant way by using indexes, doing queries which return little results first so you have less to join, etc....
c) You can walk this tree to implement the small operations on the database and the data returned. Typically this results in "virtual temporary" tables in the nodes of your AST
d) Collect the stuff from your top node and return it to the client
2-3) I do not think there are special languages. Many are in C, but there are Java and other languages used too
4) I think the best environment is a quiet environment for this kind of work. ;-)
The real hard work is not in the SQL interpreter/compiler but in the detailed datastructures and the nitty gritty of keeping everything efficiently organised and dynamically tuned to the situation in order to keep the database performant.
我相信 Oracle DBMS 最初都是用 C 编写的，而且可能仍然是。
I believe the Oracle DBMS was all originally written in C, and probably still is.
MySQL 是用 C、C++ 编写的，根据 MySQL Launchpad 页面
查看代码以了解更多信息关于MySQL是如何实现的。
查看 Postgresql 代码。
MySQL is written in C,C++ according to the MySQL Launchpad page
Check the code to find out more about how MySQL is implemented.
Have a look at the Postgresql code.
在许多（所有？）数据库中，您可以查看解释/执行计划。这将为您提供数据库内部发生的情况的粗略估计。在商业 RDBMS 中，这是您对优化如何工作最接近的理解。查询分析器中的算法是严格保密的。
Joe Chang 写了一篇关于 SQL Server 基于成本的优化器如何工作的优秀文章
同样，您可以找到类似的信息关于 PostgreSQL 和 MySQL
In many (all?) databases you can view an explain/execution plan. This will give you a rough approximation of what is going on inside the database. In commercial RDBMSes, that's the closest you will get to any understanding of how the optimizations work. The algorithms in the query analyzers are closely guarded secrets.
Joe Chang wrote an excellent article about how SQL Server's cost based optimizer works
Likewise you can find similar information about PostgreSQL and MySQL