엑셈

In this article, we look at what happens inside MySQL after a client sends a query.

Query Cache

First, before analyzing the query, MySQL Server checks the Query Cache. The Query Cache stores the full result set of SELECT statements.

This is the first step a client query goes through: before analyzing the query, MySQL Server looks up the Query Cache. If the client runs a query identical to one already in the cache, the server skips the SQL Parser and Optimizer steps and returns the result directly.

📢 When data in a table changes, the contents of the Query Cache change too, so all information related to the changed table had to be deleted. This caused severe concurrency performance degradation and many bugs, so in MySQL 8.0 the Query Cache was removed from MySQL Server entirely.

Parser

When the Query Cache cannot return a result for the query, the first step is parsing. MySQL checks whether the SQL statement written by the client follows syntax MySQL can use, and splits the statement so the database can process it internally, generating a Parse Tree.

Preprocessor

If the statement passes parsing with no syntax errors, MySQL then performs semantic analysis.

It checks whether the SQL statement can be executed against the target tables.

(e.g. it checks that the tables and columns exist, and checks names and aliases to make sure column references are not ambiguous.)

It checks whether the user has the privileges to run the SQL statement.

Query Optimizer

The Query Optimizer turns the query into the database's internal form so it can be executed efficiently. Once the Preprocessor step is done, the generated Parse Tree is considered valid and MySQL prepares to build the query execution plan. A query can be executed in many ways; finding the best option is the job of the Query Optimizer.

A typical RDBMS builds an efficient execution plan with either CBO, which plans based on performance (cost), or RBO, which follows fixed rules. MySQL's Query Optimizer works on CBO by default, and the base unit of the cost it calculates is 4 kb (the cost of reading 1 page of data). Caching effects are not included in the estimate; the cost is calculated on the assumption that every step causes disk I/O. (It is based on many factors, such as the number of pages per table or index, index cardinality, row and key length, and key distribution.)

📢 After running a query, check the status variable Last_query_cost to see the cost the Optimizer estimated for that query.

Query Execution Engine

The Query Execution Engine executes the query according to the execution plan generated by the Query Optimizer, and repeats until there are no more rows to check under that plan. In this step in particular, it continuously exchanges Handler API calls with the storage engine that holds the actual data.

Storage Engines

The storage engine communicates with the Query Execution Engine through the Handler API, and stores data on disk or reads it from disk as the Query Execution Engine requests. (For MyISAM tables this is the MyISAM storage engine; for InnoDB tables it is the InnoDB storage engine.)