A naukri.com initiative
Dev
2w
391
Image Credit: Dev
How is an SQL Query Statement executed
- MySQL can be divided into two parts: the server and the storage engine layer.
- The MySQL server layer includes the connector, query cache, parser, optimizer, executor, and most of the MySQL's core service functions.
- The storage engine layer, on the other hand, is responsible for data storage and retrieval; its architecture is plugin-based and supports multiple storage engines such as InnoDB, MyISAM, Memory.
- The first step in executing an SQL query statement is to connect the database, which requires the connector. The connector is responsible for establishing a connection with the client, obtaining permissions, and maintaining and managing the connection.
- When MySQL receives a query request, it first checks the query cache to see if this query has been executed before. Queries that have been executed before and their results are cached in memory as key-value pairs.
- If the query cache is not hit, the parser starts the statement execution process by performing lexical and syntax analysis.
- Next, the optimizer determines how to execute the query by deciding which indexes to use or the order of table joins.
- Finally, the executor starts executing the query by checking permission, interacting with the storage engine based on the table's engine definition, and accumulating the result set to return to the client.
- The number of rows scanned by the engine does not necessarily equal rows_examined.
- MySQL 8.0 completely removed the query cache feature because its disadvantages outweigh its advantages.
Read Full Article
23 Likes
For uninterrupted reading, download the app