MySQL Advanced Learning Notes (Part 3): Introduction to MySQL Logical Architecture, Detailed Explanation of MySQL Storage Engine

Introduction to MySQL logical architecture Overview

Compared to other databases, MySQL is a bit different in that its architecture can be applied in a variety of different scenarios and work well. This is mainly reflected in the storage engine architecture. The plug-in storage engine architecture separates query processing from other system tasks and data storage and extraction. This architecture allows you to select the appropriate storage engine based on business needs and actual requirements.

Controller layer:
Connectors: connection layer, c, java, etc. connect to mysql

The business logic is processed into:
Connection Pool: connection layer c3p0 connection pool, etc.
Manager Service util: backup, disaster recovery, cluster, etc.
SQL interface: stored procedures, view triggers
Parser: If the query of the permissions of things and objects begins with select, it is considered a read operation, and if it begins with insert, it is a write operation.
The loading of sql statements starts from from and is converted into a form that mysql can understand
optimizer: MySQL optimizer. After the previous conversion, MySQL will optimize to the best method that MySQL considers, not our artificial optimization.
caches & buffers

Pluggable storage engine
plugin storage engine
Most of them use myism and innodb

File storage layer computer hardware
file system
file & logs

1. Connection layer

The top layer is a number of clients and connection services, including local sock communication and most of the client/server-based tools to achieve similar TCP/IP communication. It mainly completes some tasks such as connection processing, authorization authentication, and related security solutions. The concept of thread pool is introduced at this layer to provide threads for clients that have accessed securely through authentication. SSL-based secure links can also be implemented at this layer. The server will also verify the operation permissions of each client that securely accesses it.

2. Service Layer

2.1 Management Services & Utilities: System management and control tools

2.2 SQL Interface: The SQL interface accepts the user's SQL commands and returns the results that the user needs to query. For example, select from calls the SQL Interface

2.3 Parser: Parser
When the SQL command is passed to the parser, it will be validated and parsed by the parser.

2.4 Optimizer: Query optimizer.
The SQL statement will be optimized using the query optimizer before querying.
This can be understood with an example: select uid,name from user where gender= 1;
The optimizer decides whether to project or filter first.

2.5 Cache and Buffer: Query cache.
If there is a hit query result in the query cache, the query statement can directly retrieve data from the query cache.
This cache mechanism is composed of a series of small caches. For example, table cache, record cache, key cache, permission cache and other caches are responsible for reading, and buffer is responsible for writing.

3. Engine layer

Storage engine layer: The storage engine is actually responsible for the storage and extraction of data in MySQL. The server communicates with the storage engine through the API. Different storage engines have different functions, so we can choose according to our actual needs. MyISAM and InnoDB will be introduced later

4. Storage Layer

The data storage layer mainly stores data on the file system running on the raw device and completes the interaction with the storage engine.

General Overview

Query flow chart:

First of all, the query process of MySQL is roughly as follows:
•The MySQL client establishes a connection with the MySQL server through the protocol, sends a query statement, and checks the query cache first. If it hits (only the exact same SQL can hit), it returns the result directly, otherwise it parses the statement. That is to say, before parsing the query, the server will first access the query cache (query cache) - it stores SELECT statements and the corresponding query result sets. If a query result is already in the cache, the server will not parse, optimize, or execute the query again. It simply returns the results in the cache to the user, which will greatly improve the performance of the system.

Syntax parser and preprocessing: First, MySQL parses the SQL statement through keywords and generates a corresponding "parse tree". The MySQL parser will validate and parse the query using MySQL syntax rules; the preprocessor will further check whether the parsed number is legal according to some MySQL rules.
Query Optimizer When the parse tree is considered valid, it is converted into an execution plan by the optimizer. A query can be executed in many ways and still return the same result. The role of the optimizer is to find the best execution plan among them. .
Then, MySQL uses the BTREE index by default, and a general direction is: no matter how you mess with SQL, at least for now, MySQL will only use one index in the table at most.

mysql storage engine

View Commands

1 How to view with command

See what storage engine your MySQL currently provides:

mysql> show engines;

InnoDB default, supports transactions, foreign keys, and row locks

Check your mysql's current default storage engine:

mysql> show variables like '%storage_engine%';

The default is InnoDB
The current one is also InnoDB

Introduction to each engine

1. InnoDB storage engine

InnoDB is the default transaction engine for MySQL, which is designed to handle a large number of short-lived transactions. Unless there is a very special reason to use another storage engine, the InnoDB engine should be given priority. Row-level locks, suitable for high concurrency situations

2. MyISAM storage engine

MyISAM provides a large number of features, including full-text indexing, compression, spatial functions (GIS), etc., but MyISAM does not support transactions and row-level locks (MyISAM will lock the entire table when modifying the table). One undoubted flaw is that it cannot be safely recovered after a crash.

3. Archive Engine

The Archive storage engine only supports INSERT and SELECT operations and does not support indexes before MySQL 5.1.
Archive tables are suitable for log and data collection applications. Suitable for situations such as low-access big data.
According to the English test results, Archive tables are about 75% smaller than MyISAM tables and about 83% smaller than InnoDB tables that support transaction processing.

4. Blackhole Engine

The Blackhole engine does not implement any storage mechanism. It discards all inserted data without saving any data. However, the server will record the logs of the Blackhole table, so it can be used to copy data to the backup database, or simply record it in the log. However, this application method will encounter many problems and is therefore not recommended.

5. CSV Engine

The CSV engine can process ordinary CSV files as MySQL tables, but does not support indexes.
The CSV engine can be very useful as a data exchange mechanism.
The data stored in CSV can be read directly in the operating system using a text editor or Excel.

6. Memory Engine

If you need to access data quickly, and the data will not be modified and will not be lost after a restart, then using a Memory table is very useful. Memory tables are at least an order of magnitude faster than MyISAM tables. (It is faster to use a professional memory database, such as redis)

7. Federated Engine

The Federated engine is a proxy for accessing other MySQL servers. Although this engine appears to provide a good flexibility across servers, it also often causes problems, so it is disabled by default.

MyISAM and InnoDB (Key Points)

InnoDB index uses B+TREE MyISAM index uses b-tree
The primary key of innodb is a clustered index, and the efficiency of adding, deleting, modifying and querying based on the clustered index is very high.

Which one should I use for Alibaba or Taobao?

• Percona has made improvements to the MySQL database server, which has significant improvements in functionality and performance over MySQL. This version improves the performance of InnoDB under high load conditions, provides some very useful performance diagnostic tools for DBAs, and has more parameters and commands to control server behavior.

•The company has created a new storage engine called XtraDB, which can completely replace InnoDB and has better performance and concurrency.

•Most of Alibaba's MySQL databases are actually modified from the Percona prototype.
• AliSql+AliRedis

The above is the introduction of Mysql logical architecture and the detailed integration of MySQL storage engine introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!