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

This article mainly shows you the "example analysis of MySQL logical architecture", which is easy to understand and well-organized. I hope it can help you solve your doubts. Let the editor lead you to study and study the "sample analysis of MySQL logical architecture".

Mysql logical architecture (roughly divided into three layers) layer 1: connection threading

Client-> connection threading (connection handling, authorization, security)

The services included are not unique to mysql, they all serve the Cmax S program or what these programs need (connection handling, authorization authentication, security, etc.)

Layer 2: contains most of the mysql core services

Query cache-- > parser-- > optimizer-- > execute query

Query caching, parsing, analysis, optimization, caching, all built-in functions (date, time, math and encryption functions) while all storage engines provide functions focused on this layer (stored procedures, triggers, views)

Process: before parsing a query, query the cache first. The cache can only store the information and result data of the query. If a query is requested to exist in the cache, there is no need to parse, optimize and execute the query. Directly return the results of the query stored in the cache

Layer 3: contains the storage engine

The storage engine is responsible for the storage and extraction of data in mysql (similar to file systems under Linux)

Each storage engine has its advantages and disadvantages. The middle service layer communicates with the storage engine through API. These API interfaces shield the differences between different storage engines and make the query layer as transparent as possible.

The storage engine API contains more than a dozen underlying functions, such as performing "start a transaction" or fetching a row with a specific primary key, but the storage engine generally does not parse SQL, (InnoDB parses the definition of foreign keys because it does not implement this function), and different storage engines do not communicate with each other, but simply respond to upper server requests.

Mysql logical Architecture-detailed introduction (eight steps)

1.Connectors

Refers to the interaction with SQL in different prophecies

Nactive C API,JDBC,ODBC,.NET,PHP,Python,Perl,Ruby,VB

2. Enterprise Management Services & Utilities

System management and control tools

Backup & Recovery,Security,Replication,Cluster,Partitioning,Instance Manager,INPORMATICN_SCHEMA,Administrator,Workbench,Query Browser,Migration Toolkit

3. Connection Pool (connection pool)

Manage caching requirements such as buffering user connections, threading, and so on.

Responsible for monitoring various requests for MySQL Server, accepting connection requests, and forwarding all connection requests to the thread management module.

The client request for MySQL Server on each connection is assigned (created) a connection thread to serve it separately, and the connection thread is cached, so there is no need to create and destroy each client connection separately. The main task of the connection thread is responsible for the communication between MySQL Server and the client, receiving command requests from the client, transmitting the result information of the server side, and the thread management module is responsible for managing and maintaining these connection threads. Including thread creation, thread cache and so on.

Authentication-Thread Reuse-Connection Limits-Check Memory-Caches

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.

DML,DDL,Stored Procedures,Views,Triggers,etc

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, which is a very long script. In MySQL, we are used to sending all the commands that the Client side sends to the server side as query. In MySQL Server, after receiving a Query from the client, the connection thread will directly pass the query to the various Query for classification and forward to each corresponding processing module.

Main functions:

a. Analyze the semantics and syntax of SQL statements, decompose them into data structures, then classify them according to + the same operation type, and forward them to the following steps. Later, the transmission and processing of SQL statements is based on this structure.

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

Query Translation,Object Privilege

6.Optimizer (query optimizer)

The SQL statement uses the query optimizer to optimize the query before the query, that is, to optimize the client request query. According to the query statement requested by the client and some statistical information in the database, an optimal strategy is obtained on the basis of a series of algorithms, which tells the later program how to get the result of the query statement, using the "select-projection-join" strategy to query. For example, the query statement select uid,name from user where gender=1; is first selected according to the following statements of where, instead of querying all the tables first and then filtering them with gender, and then projecting attributes according to uid and name, instead of filtering after all the attributes are taken out. Finally, the two query conditions are joined to generate the final query results.

Access Paths,Statistics

7.Cache and Buffer (query caching)

The main function is to cache the returned result set of the Select-like request submitted by the client to Mysql into memory, corresponding to a hash value of the query. After any data changes in the base table of the query, MySQL will automatically invalidate the Cache of the query. In the application system with a very high read-write ratio, the performance improvement of Query Cache is very significant, of course, its memory consumption is also very large. If there is a valid hit to the query result in the query cache, the query statement can directly fetch data from the query cache. This cache mechanism is composed of a series of small caches, such as table cache, record cache, Key cache, permission cache and so on.

Global and Engine Specific Caches & Buffers

8.pluggable storage Engines (plug-in storage engine)

Storage engine interface: the most important feature that distinguishes MySQL from other databases is its plug-in table storage engine.

The MySQL plug-in storage engine architecture provides a series of standard management and service support, which are independent of the storage engine itself and may be necessary for every database system, such as SQL analyzer and optimizer, while the storage engine is the implementation of the underlying physical structure. Each storage engine developer can develop according to his or her own wishes.

9.file system

File system, data, log (redo,undo) index, error log, query record, slow query, etc.

Note: the storage engine is based on tables, not databases

Workflow of database

Set up TCP connection-- > authenticate user-- > create thread parse SQL-- > generate execution plan-- > Open table-- > search buffer to see if the required data pages are cached-- > scan data from disk-- > get data and write buffer pool-- > return data to client-- > close table-- > close thread-- > close connection.

Top layer: client connection

1. Connection processing: the client establishes a TCP connection with the database service layer, and the connection management module establishes the connection and requests a connection thread. If there is an idle connection thread in the connection pool, it is assigned to the connection. If not, create a new connection thread to take charge of the client if the maximum number of thread connections is not exceeded.

two。 Authorization and authentication: before the query operation, the user module needs to be called for authorization check to verify whether the user has permission. After passing, the service is provided, and the connection thread begins to accept and process the SQL statement from the client.

Layer 2: core services

1. After receiving the SQL statement, the connection thread hands the statement to the SQL statement parsing module for syntax and semantic analysis.

two。 If it is a query statement, you can first see if there are any results in the query cache, and if so, return them directly to the client.

3. If there is no result in the query cache, you need to query the database engine layer and send the SQL statement to the optimizer to optimize the query. If it is a table change, the classification is handed over to the insert,update,delete,create,alert processing module for processing.

Tier 3: database engine layer

1. Open the table and obtain the appropriate lock if necessary.

two。 First query whether there is any corresponding data in the cache page, if so, you can return it directly, and if not, read it from the disk.

3. When the corresponding data is found in the disk, it will be loaded into the cache, which makes the following query more efficient. Because of the limited cache, the flexible LRU table is used to manage the cache page to ensure that the cache is frequently accessed data.

4. Finally, get the data and return it to the client, close the connection, and release the connection thread.

FAQ analysis

1. What is a plug-in storage engine

The storage engine is to read and write data to the disk. Different storage engines, different read and write operations, transactions, locks and so on are different because we have different business requirements, so there will be many storage engines. In a database, because the storage engine is table-based, different tables can have different storage engines, allowing the storage engine to be loaded into the running MySQL server. This is the plug-in storage engine.

two。 What is LRU caching

A cache elimination mechanism strategy algorithm, because the cache memory is always limited, so when the cache is full, it is necessary to delete some content to make room for the new content. Under the LRU mechanism, the eliminated data is called useless data, and the full name of LRU is Least Recently Used, that is, we think that the recently used data is useful, and the data that has not been used for a long time should be useless. When the memory is full, priority is given to deleting data that has not been used for a long time recently.

The caching policy of Ehcache in JVM includes

LRU-least recently used (least recently used)

LFU-least frequently used (least frequently used)

FIFO-first in first out, the oldest element by creation time (clear the earliest cached data, do not care whether it is used frequently)

The above is all the content of the article "sample Analysis of MySQL logical Architecture". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, 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.