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

This article shares with you the content of a sample analysis of the MySQL architecture. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

MySQL architecture

MySQL architecture is mainly divided into two parts: client and server.

Client

The client connector is mainly responsible for some client connections, providing connection services for different programming language drivers.

Server side

The service layer includes: system management and control tools, connection pool, SQL interface, parser, query optimizer, cache.

Connection pool

Responsible for managing the connection between the client and the service layer data processing, as well as the verification of the user's operation rights.

System management and control tools

Responsible for backup security, security management, cluster management services and tools.

SQL interface

Receive client-side sql commands and return the results required by the user. Such as: dml,ddl, stored procedures, views, triggers and other commands.

Parser

Word (keyword) parsing, syntax parsing, receiving sql parsing, parsing tree generation, and syntax checking.

Query optimizer: after the parse tree is generated and the parser syntax is passed, the optimizer selects the appropriate index, generates an execution plan, and then interacts with the execution engine.

Caching

The caching mechanism is composed of a series of caches.

For example: table cache, record cache (sql query results are cached, and the next time the same sql query is returned from the cache)

Permission cache, engine cache, etc. If the cache hits, the data will be fetched directly from the cache.

Storage engine layer

Responsible for reading and accessing Mysql data, such as InnoDB,MyISAM, etc. Pluggable, different storage engines can be replaced.

System file layer

Such as logs, data files, etc.

An execution process of SQL

First, the client connects.

Secondly, verify user rights and other operations in the connection manager of the server layer.

Then, if it is a non-8.0 version, it will check the cache of the current sql in the cache pool, and if there is a direct return

If not in the cache, the parsing tree will be generated by the parser syntax semantics.

The optimizer parses and generates the execution plan and selects the appropriate index

The storage engine calls the API API to query the data, and updates the queried data to cache cache

Finally, return to the client.

Storage engine layer

Storage engine is a subsystem dealing with files in MySQL. It is a system abstracted from the file system for dealing with mysql and disk files.

Commonly used storage engine, MyISAM, InnoDB that supports transactions

Prior to MySQL version 5.6, the default storage engine was MyISAM

The default storage engine after version 5.6 is InnoDB

Functional comparison

InnoDB supports four transaction features of ACID, while MyISAM does not

InnoDB supports four transaction isolation levels. Repeatable Read can be read repeatedly by default, and MyISAM does not support it.

InnoDB supports crash secure recovery, but MyISAM does not

InnoDB supports foreign keys, but MyISAM does not.

InnoDB supports row-level lock granularity, but MyISAM does not support it. Only table-level lock granularity is supported.

InnoDB supports MVCC,MyISAM does not support

Thank you for reading! This is the end of this article on "sample Analysis of MySQL Architecture". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.