Server ArchitectureΒΆ

At a high level, the MySQL Server is comprised of two distinctive parts: the server [1] and storage engines. Query optimization occurs at the server level, above the storage engine API, and is semantically broken up into four stages:

  • Logical Transformations
  • Preparation for Cost-based Optimization
  • Cost-based Optimization
  • Plan Refinement
_images/server-architecture.png

Dividing the optimizer into defined layers has been an evolutionary process. In the early days of MySQL, the optimizer had fewer query optimizations, and for performance reasons the stages of query optimization were not very well separated or defined.

As new features reduced maintainability, this created a need for refactoring, which has been implemented over several versions of MySQL - 5.6, 5.7, and 8.0. Breaking execution into stages also paves the way for newer features. For example, plan caching would be difficult to implement without clear separation since re-use between connections is made difficult.

[1]Be careful: the name “server” is ambiguous, and could also be used to describe the superset of server + storage engines.