Analysis based on MySQL architecture_PHP tutorial

To understand MySql, you must keep in mind its architecture diagram. Mysql is composed of SQL interface, parser, optimizer, cache, and storage engine

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 buffering user connections, thread processing and other requirements that require caching

4 SQL Interface: SQL interface.

Accepts the user's SQL command and returns the results that the user needs to query. For example, select from is to call SQL Interface

5 Parser: Parser.

When the SQL command is passed to the parser, it will be verified and parsed by the parser. The parser is implemented by Lex and YACC and is a very long script.

Main functions:

a. Decompose the SQL statement into a data structure and pass this structure to subsequent steps. The subsequent delivery and processing of SQL statements is based on this structure

b. If an error is encountered during decomposition, it means that the sql statement is unreasonable

6 Optimizer: Query optimizer.

The SQL statement will use the query optimizer to optimize the query before querying. He uses the "select-projection-join" strategy for querying.

You can understand it with an example: select uid,name from user where gender = 1;

This select query first selects based on the where statement, instead of querying all the tables first and then filtering by gender

This select query first performs attribute projection based on uid and name, instead of taking out all attributes and then filtering

Join these two query conditions to generate the final query result

7 Cache and Buffer: Query cache.

If the query cache has a hit query result, the query statement can directly fetch data from the query cache.

This caching mechanism is composed of a series of small caches. For example, table cache, record cache, key cache, permission cache, etc.

8 Engine: Storage engine.

The storage engine is the specific subsystem in MySql that deals with files. It is also the most unique place of Mysql.

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

There are many storage engines now, and each storage engine has different advantages. The most commonly used ones are MyISAM, InnoDB, and BDB

By default, MySql uses the MyISAM engine, which has fast query speed and good index optimization and data compression technology. But it doesn't support transactions.

InnoDB supports transactions and provides row-level locking, which is also widely used.
Mysql also supports customizing its own storage engine. Even different tables in a library use different storage engines. These are all allowed.