Mysql structure hierarchy

Mysql structure hierarchy

To understand MySql, you must keep in mind its architecture. Mysql is composed of SQL interfaces, Resolvers, optimizers, caches, and storage engines.
Www.2cto.com


1. Connectors refers to the interaction with SQL in different languages

2. Management Serveices & Utilities: system Management and control tools

3. Connection Pool: Connection Pool

Manage the cache user connection and thread processing requirements.

4. SQL Interface: SQL Interface

Accept your SQL commands and return the query results. For example, select from calls SQL Interface.


5. Parser: Parser.

When SQL commands are passed to the parser, they are verified and parsed by the parser. The parser is implemented by Lex and YACC and is a very long script.

Main functions:

A. Break down the SQL statement into a data structure and pass the structure to the subsequent steps. In the future, the transmission and processing of SQL statements are based on this structure.

B. if an error occurs in the decomposition structure, it indicates that the SQL statement is unreasonable.

6. Optimizer: Query Optimizer

Before querying, SQL statements use the query optimizer to optimize the query. It uses the "select-projection-join" policy for query.

For example, select uid, name from user where gender = 1;

This select query selects the table based on the where statement, instead of querying all the tables before performing gender filtering.

This select query first projects Attributes Based on the uid and name, instead of filtering all attributes.

Join the two query conditions to generate the final query result.

7. Cache and Buffer: Query Cache

If the query results are saved in the cache, the query statement can directly query the data in the cache.

This cache mechanism is composed of a series of small caches. Such as table cache, record cache, key cache, and permission cache.

8. Engine: storage Engine

The storage engine is a specific subsystem in MySql that deals with files. It is also the most distinctive feature of Mysql.

Mysql's storage engine is plug-in-type. It customizes a file access mechanism (called the storage engine) based on an abstract interface in the file access layer provided by MySql AB ).

Currently, there are many storage engines with different advantages. The most common storage engines are MyISAM, InnoDB, and BDB.

By default, MySql uses the MyISAM engine, which provides fast query speed and better index optimization and data compression technologies. However, it does not support transactions.

InnoDB supports transactions and provides row-level locking, which is also widely used.

Mysql also supports custom storage engines, and even different tables in a database use different storage engines, which are allowed.