MySQL architecture

MySQL architecture

Article directory

• Preface
• MySQL Server layer
• MySQL storage engine
• • 1. InnoDB storage engine
• 2. MyISAM storage engine
• 3. Memory storage engine

Preface

Before learning a transaction, we need to first understand the basic structure of things. Only after we understand the basic structure of things can we have a deeper understanding of related operations. So today I will introduce the MySQL architecture to you.

The server side of the MySQL database is mainly divided into the Server layer and the storage engine layer. Next, I will focus on these two layers to introduce the MySQL architecture.

MySQL Server layer

MySQL's Server layer takes care of seven components:

1. Interactive interfaces (Connectors) provided by MySQL
2. Connection Pool component (Connection Pool)
3. Management Service & Utilities
4. SQL Interface component (SQL Interface)
5. Query analyzer component (Parser)
6. Optimizer component (Optimizer)
7. Query Caches & Buffers

1) The interactive interface (Connectors) provided by MySQL to the outside world.
The Connectors component is the interactive component provided by MySQL to the outside world. Languages ​​such as Java, .NET, and PHP can use this component to operate MySQL statements and realize interaction with MySQL. After establishing a connection, you can show processlistview the established connections through the statement.

If the client has no active activity for a period of time, the connector will actively disconnect after the default 8 hours. If the client sends a request again after the connection is disconnected, it will receive an error reminder: Lost connection to MySQL server during query.

When the client connects to the MySQL database, it can be divided into short connection and long connection according to the length of the connection time . A short connection is relatively simple. It means that it will be disconnected after each query. Querying again requires re-establishing the connection. Therefore, the cost of using a short connection is higher. A long connection refers to connecting to the MySOL database for a long time and performing database operations. Therefore, a long connection will cause A memory overflow problem occurs, causing MySQL to restart abnormally.

When using a long connection, you can use client functions mysql_reset_connection()to reinitialize connection resources. This process does not require reconnection and permission verification, but will restore the connection to the state when it was just created.

2) Connection Pool component (Connection Pool)

Responsible for monitoring various requests from clients to the MySQL server, receiving requests, and forwarding requests to the target module. Each client request that successfully connects to the MySQL server will create or assign a thread. This thread is responsible for communication between the client and the MySQL server, receiving commands sent by the client, and transmitting result information from the server.

3) Management Service & Utilities

Provides integrated management of MySOL, such as backup, recovery, security, etc.

4) SQL interface component (SQL Interface)

Receive user SQL commands, such as DML, DDL and stored procedures, and return the final results to the user.

5) Query analyzer component (Parser)

Before the system executes the input statement, it must analyze what the statement wants to do. For example: firstly, through the select keyword, we know that this is a query command, which also includes analyzing which table is to be queried and what the query conditions are. At the same time, the parser must analyze the input statement for grammatical correctness. If there is a syntax error in the SQL, the query analyzer component will return the prompt message "You have an error in your SQL syntax".

6) Optimizer component (Optimizer)

The optimizer is the final step that MySQL uses to optimize the input statement before executing it. Optimization content includes: whether to select an index, which index to select, the joint order of multi-table queries, etc. The logical results of each execution method are the same, but the execution efficiency will be different, and the role of the optimizer is to decide which solution to use.

7) Query Caches & Buffers
This query cache is relatively easy to understand. During each query, MySQL first checks to see if the cache is hit, and if it hits the cache, it is returned directly, which improves the system's response speed. However, this function has a considerable drawback, that is, once the data in this table changes, all caches corresponding to this table will become invalid.

For databases with heavy update pressure, the hit rate of the query cache will be very low. Unless the business system only has one static table, it will only be updated once a long time. For example, if it is a system configuration table, then the query on this table is suitable for query cache. Therefore, in production systems, it is recommended to turn off this function.

Before MySQL 8.0, you could turn off the query cache function by setting the parameter "query_cachetype" to OFF. But after MySQL version 8.0, this part of the function was directly deleted.

 
    show variables like '% query_cache% ';
    

MySQL storage engine

The MySQL storage engine layer is responsible for data storage and retrieval. Its architectural model is plug-in and supports multiple storage engines such as InnoDB, MyISAM, Memory, Archive, and NDB Cluster. The most commonly used one is InnoDB. I will introduce InnoDb, MyISAM and Mymery storage engines in detail.

We can use show create table table name;to view the storage engine used when creating the table.

 
     create table test (id int);
     show create table test;
    

1) InnoDB storage engine

InnoDB is MySQL's default storage engine. It supports ACID (atomicity, consistency, isolation, and durability) transactions and provides row-level locking, foreign key constraints, and crash recovery. It is suitable for most application scenarios, especially applications that require transaction support and high concurrent read and write operations.

It has the following features:

1. Transaction support: The InnoDB engine supports the ACID properties of transactions, ensuring the atomicity, consistency, isolation and durability of data. This means that BEGIN, COMMIT and ROLLBACK statements can be used to manage transactions and ensure data integrity and consistency.
2. Row-level locking: InnoDB uses row-level locks to handle concurrent access and modification of data, rather than table-level locks. This means that multiple transactions can access different rows of the same table at the same time, improving concurrency performance and concurrency control.
3. Foreign key constraints: InnoDB supports foreign key constraints, which can achieve data consistency and integrity at the database level. It provides options such as CASCADE, RESTRICT and SET NULL to handle foreign key relationships.
4. Crash recovery: InnoDB has the ability to recover from crashes, ensuring data integrity even in the event of a system crash or power failure. It uses the transaction log (redo log) to recover unfinished transactions and recover committed transactions.
5. Auto-grow columns: InnoDB supports auto-grow columns. You can specify an automatically incrementing integer value for a column in the table, simplifying data insertion operations.
6. Rollback segment: InnoDB uses rollback segment (Rollback Segment) to store the data of uncommitted transactions so that rollback operations can be performed when needed.
7. Online hot backup is possible: The InnoDB engine supports online hot backup, which can back up the database without stopping the MySQL server.
8. Supports MVCC (Multi-version Concurrency Control): InnoDB uses multi-version concurrency control to handle concurrent transactions, allows write operations while reading operations, and achieves data isolation and consistency through row versions.
9. High performance: The InnoDB engine improves the performance of reading data by using the Buffer Pool to cache popular data and indexes.

2) MyISAM storage engine

MyISAM is another common storage engine for MySQL. It does not support transactions and row-level locking, but has good performance. MyISAM is suitable for applications that are primarily read operations, such as data warehouses, archives, and non-transactional applications.

It has the following features:

1. Fast reading speed: The MyISAM storage engine is very efficient when reading data, and performs well for applications that are mainly read operations. This is because MyISAM tables handle concurrency using table-level locking, and read operations can be executed concurrently without contention caused by row-level locking.
2. Support full-text indexing: The MyISAM storage engine provides good support for full-text indexing and can provide efficient text search capabilities by creating full-text indexes.
3. Saving disk space: Compared with the InnoDB storage engine, MyISAM is usually more space-saving in disk usage because it does not support transactions, row-level locking, crash recovery and other functions, reducing the need to store additional metadata and logs.
4. Table-level locking: The MyISAM storage engine uses table-level locking, which means that one write operation locks the entire table, so concurrency performance may be degraded if write operations are frequent.
5. Does not support transactions and foreign keys: The MyISAM storage engine does not support transaction operations, nor does it support foreign key constraints. This means that when using MyISAM, you cannot use transaction operations such as BEGIN, COMMIT, and ROLLBACK, nor can you define foreign key constraints to maintain data integrity.
6. Does not support crash recovery: The MyISAM storage engine does not have crash recovery capabilities, which means that if the MySQL server crashes during a write operation, data inconsistency may result.
7. Automatically maintain index statistics: The MyISAM storage engine automatically maintains index statistics for tables, and these statistics are used to optimize query execution plans.
8. Multi-purpose: MyISAM storage engine is suitable for application scenarios that are mainly read operations, such as reports, log analysis, and static websites.

We can specify the storage engine when creating the table.

 
    create table table name ( ) engine = storage engine name
    

 
     create table test1 (id int) engine = myisam;
     show create table test1;
    

It is precisely because of these characteristics of the MyISAM storage engine that it is suitable for the following scenarios:

1. Scenarios that do not require transaction support
2. A single business scenario with a lot of reading or writing is not suitable for frequent reading and writing.
3. Businesses with low concurrent read and write access
4. Businesses with relatively few data modifications
5. Reading-based business
6. Businesses that do not have very high requirements for data consistency
7. An environment where server hardware resources are relatively poor

3) Memory storage engine

The Memeory storage engine stores the data in the table in memory instead of on the disk, which means that if MySQL is restarted or shut down, the data at this time will be lost.

 
    create table test2 (id int,name varchar(20)) engine = memory;
    show create table test2;
    insert into test2 values (1,'zhangsan');
    select * from test2;
    

 
    # Restart MySQL
    systemctl restart mysqld
    

 
    select * from test2;
    

Output information

 
    Empty set (0.00 sec)
    

The Memory storage engine has the following characteristics:

1. High-speed reads and writes: Because data is stored in memory, the Memory storage engine provides very fast read and write performance. It can perform query and write operations faster than other storage engines.
2. Temporary data and cache tables: Since the data is stored in memory, the Memory storage engine is very efficient for handling temporary data and cache tables. If you need to create some temporary data during the query process and no longer need it after the query is completed, the Memory engine is a good choice.
3. Cache index: The Memory storage engine performs very fast index queries because the index data is completely stored in memory, reducing disk I/O overhead.
4. Not persistent: The data of the Memory engine will not be persisted to the disk. Once the MySQL server is restarted or shut down, the data stored in the Memory engine will be lost. Therefore, the Memory storage engine is suitable for handling non-persistent data and can reload data after a server restart.
5. Suitable for small-scale data: Since data is stored in memory, the capacity of the Memory storage engine is limited by the available memory size. It is not suitable for processing large data sets as memory may become a limiting factor.
6. No support for transactions and crash recovery: The Memory storage engine does not support transactions and does not support crash recovery. Therefore, you need to pay attention to data consistency and durability when using the Memory storage engine.