Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you an example analysis of the MySQL architecture. The article is rich in content and analyzed and described from a professional point of view. I hope you can get something after reading this article.

MySQL is composed of SQL interface, parser, optimizer, cache, and storage engine.

The architecture diagram is as follows:

1.Connectors refers to interaction with SQL in different languages

2.Management Serveices & Utilities: a system management and control tool

3.Connection Pool: connection pooling

Manage caching requirements such as buffering user connections, threading, etc.

4.SQL Interface: SQL interface

Accept the user's SQL command and return the results that the user needs to query. For example, select from calls SQL Interface.

5.Parser: parser

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

Main functions:

A. Decompose the SQL statement into a data structure and pass this structure to the next step, based on which the delivery and processing of the SQL statement is based

b. If an error is encountered in the decomposition composition, then the sql statement is unreasonable.

6.Optimizer: query optimizer

The SQL statement uses the query optimizer to optimize the query before the query. He uses the "select-project-join" strategy to query.

It can be understood by an example: select uid,name from user where gender = 1

This select query is selected according to the where statement first, rather than querying all the tables first and then gender filtering.

This select query first projects attributes based on uid and name, rather than taking out all the attributes and then filtering them.

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

7.Cache and Buffer: query caching

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

This caching mechanism consists of a series of small caches. Such as table cache, record cache, key cache, permission cache, etc.

8.Engine: storage engine

Storage engine is a specific subsystem dealing with files in MySql. It is also one of the most distinctive features 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 storage engine).

Now there are many kinds of storage engines, each of which has different advantages, the most commonly used is MyISAM,InnoDB,BDB

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

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

Mysql also supports its own customized storage engine, and even different tables in a library use different storage engines, which are allowed.

The above is the example analysis of the MySQL architecture shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.