Running mechanism and architecture of MySQL or SQL Server database

First, MySQL is mainly divided into the following components:

• Connection Pool Components
• Manage services and tools components
• SQL Interface Components
• Parser component
• Optimizer components
• Buffer components
• Plug-in storage engine
• Physical files

Two, MySQL composition: MySQL is composed of SQL interface, parser, optimizer, cache, storage engine.

Third, MySQL system structure:

1 connectors refers to the interaction of SQL in different languages

2 Management serveices & Utilities: System Management and control tools

3 Connection Pool: Connection pooling.

Manage buffered user connections, threading, and other needs for caching

4 SQL Interface:sql interface.

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

5 Parser: Parser.

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

Main functions:

A. The SQL statement is decomposed into a data structure, and the structure is passed to the next step, after which the SQL statements are passed and processed based on this structure

B. If an error is encountered in the decomposition composition, then it is not reasonable to indicate that the SQL statement

6 Optimizer: Query optimizer.

SQL statements use the query optimizer to optimize queries before they are queried. He uses the Select-drop-join strategy for querying.

As an example, you can understand: Select Uid,name from user where gender = 1;

This select query is selected based on the where statement instead of querying all of the tables before gender filtering

This select query first attributes the property based on the UID and name, instead of removing the attributes from the filter

Join these two query criteria to generate the final query result.

7 Cache and Buffer: query caching.

If the query cache has hit query results, the query can go directly to the query cache to fetch the data.

This caching mechanism is made up of a series of small caches. such as 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 one of MySQL's most distinctive places.

The MySQL 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 (the access mechanism is called the storage engine)

Now there are many kinds of storage engines, the advantages of each storage engine are different, the most common myisam,innodb,bdb

By default MySQL is using the MyISAM engine, it queries fast, has better index optimization and data compression technology. However, it does not support transactions.

InnoDB supports transactions and provides row-level locking, and the application is quite extensive.

Running mechanism and architecture of MySQL or SQL Server database