MySql optimization architecture and storage engine (summary sharing)

This article brings you relevant knowledge about the mysql architecture and storage engine summary. I hope it will be helpful to you.

1. MySQL structure system

Generally speaking, we can MySQL is divided into three layers. If the layering is more detailed, the storage engine layer can be further divided into the engine layer and the storage layer:

Connection layer
The top layer is some clients and links Services, including local sock communication and most TCP/IP-like communication implemented by client/server tools. It mainly completes some connection processing, authorization authentication, and related security solutions. The concept of thread pool is introduced on this layer to provide threads for clients that securely access through authentication. SSL-based secure links can also be implemented on this layer. The server also verifies the operating permissions it has for each client that securely accesses it.

Service layer
The second layer architecture mainly completes most of the core service functions, such as SQL interface, and completes cached queries, SQL analysis and optimization, and some built-in functions. implement. All cross-storage engine functions are also implemented in this layer, such as procedures, functions, etc. At this layer, the server will parse the query and create the corresponding internal parse tree, and complete the corresponding optimization such as determining the order of table queries, whether to use indexes, etc., and finally generate the corresponding execution operations. If it is a select statement, the server will also query the internal cache. If the cache space is large enough, this can greatly improve the performance of the system in an environment that solves a large number of read operations.

Engine layer
Storage engine layer, the storage engine is really responsible for the storage and retrieval of data in MySQL. The server communicates with the storage engine through API. Different storage engines have different functions, so we can choose the appropriate storage engine according to our needs.

Storage layer
The data storage layer mainly stores data on the file system and completes the interaction with the storage engine. Compared with other databases, MySQL is a little different. Its architecture can be applied and work well in many different scenarios. Mainly reflected in the storage engine, the plug-in storage engine architecture separates query processing from other system tasks and data storage and extraction. This architecture allows the selection of appropriate storage engines based on business needs and actual needs.

2. Components of MySQL Server

Each layer of the MySQL structure is composed of one or several parts:

Connection layer components

Connectors: interactive interface

Interactive components provided by MySQL, such as java,.net , PHP and other languages ​​can use this component to operate SQL statements and realize interaction with SQL

Service layer component

Connection Pool: Connection pool component
Used to manage and buffer user connections, thread processing and other requirements that require caching

Management Services & Utilities: Management services and tool components
Used for system management and control tools, such as backup and recovery, Mysql replication, clustering, security management, etc.

SQL Interface: SQL interface component
Used to accept user SQL commands , such as DML, DDL and stored procedures, etc., and return the final results to the user

Parser: Query analyzer component
When the SQL command is passed to the parser, it will be Verification and analysis, first analyze the legality of the SQL command syntax, and try to decompose the SQL command into a data structure. If the decomposition fails, it will prompt that the SQL statement is unreasonable

Optimizer: Optimizer component
The SQL statement will use the query optimizer to optimize the query before querying.
For example: select id,name from user where age = 20;

1. This select query first selects based on the where statement, instead of querying all the tables first and then filtering by age
2. This select query first performs attribute projection based on id and name, instead of taking out all the attributes and then filtering them
3. Connect these two query conditions to generate the final query result

Caches & Buffers: Buffer pool component
If the query cache has a hit query result, the query statement can directly fetch data from the query cache. This caching mechanism is composed of a series of small caches. For example, table cache, record cache, key cache, permission cache, etc.

Engine layer components

Pluggable Storage Engines: Storage Engine
Storage engine is the implementation of technologies such as storing data, establishing indexes, updating query data, etc. The storage engine is table-based, not library-based. So storage engines can also be called table types. The creation of tables, data storage, retrieval, updates, etc. are all completed by the MySQL storage engine, which is also the important role the MySQL storage engine plays in MySQL.

MYSQL provides a plug-in storage engine architecture, so users can choose different storage engines for data tables according to different needs. Users can also write their own storage engines or write storage engines according to their own needs. Even different tables in a library using different storage engines are allowed.

Storage layer components

File System: File system

is used to actually store MySQL Database files and some log files, etc. Common ones include Linux, Windows, etc.

3. Storage engine

The difference between commonly used storage engines

In MySQL Before version 5.5, the default storage engine was MyISAM, which comes with MySQL. After version 5.5, the default storage engine was changed to InnoDB, which was developed by a third-party company for MySQL. Why change it?

The main reason is that InnoDB supports transactions and row-level locks, which is more suitable for scenarios with high business consistency requirements.

Different storage engine table creation statements:

CREATE TABLE `user_innodb` (
  `id` int(11) PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user_myisam` (
  `id` int(11) PRIMARY KEY  AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user_memory` (
  `id` int(11) PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user_archive` (
  `id` int(11) PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL
) ENGINE=Archive DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user_csv` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `gender` tinyint(1) NOT NULL,
  `phone` varchar(11) NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8mb4;

InnoDB
The InnoDB storage engine is the default storage engine of MySQL today. The InnoDB storage engine provides transaction security with commit, rollback, and crash recovery capabilities. However, compared with MyISAM's storage engine, InnoDB's write processing efficiency is less efficient, and it takes up more disk space to retain data and indexes.

The characteristics of the InnoDB storage engine are different from other storage engines:

1. Transaction control
2. Foreign key constraints

InnoDB storage table and There are two ways to index:

1. Use shared table space storage. The table structure of the table created in this way is saved in the .frm file. The data and index are saved in the table space defined by innodb_data_home_dir and innodb_data_file_path. , which can be multiple files.
2. Use multi-table space storage. The table structure of the table created in this way still exists in the .frm file, but the data and indexes of each table are saved separately in .ibd.

InnoDB Suitable usage environment:
InnoDB is the default storage engine of MySQL, used for transaction processing applications, and supports foreign keys. If the application has relatively high requirements for transaction integrity and requires data consistency under concurrent conditions, and data operations include many update and delete operations in addition to insertion and query, then the InnoDB storage engine is a more suitable choice.

In addition to effectively reducing locks caused by deletions and updates, the InnoDB storage engine can also ensure complete submission and rollback of transactions, which is suitable for systems such as billing systems or financial systems that require relatively high data accuracy. system, InnoDB is the most suitable choice.

MyISAM
MyISAM does not support transactions or foreign keys. Its advantage is fast access and no requirements for transaction integrity or mainly SELECT and INSERT. Basically any application can use this engine to create tables.

has the following two important features:

• Does not support transactions
• File storage method

Each MyISAM is on disk It is stored into 3 files. The file names are the same as the table names, but the extensions are:

• .frm (storage table definition);
• .MYD(MYData, Store data);
• .MYI(MYIndex, storage index);

MyISAM suitable usage environment:
If the application is mainly based on read operations and insert operations, only There are very few update and delete operations, and the requirements for transaction integrity and concurrency are not very high, so choosing this storage engine is very suitable.

MEMORY
The Memory storage engine stores table data in memory. Each MEMORY table actually corresponds to a disk file in the format of .frm. This file only stores the structure of the table, and its data files are stored in memory. This is conducive to rapid data processing and improves the efficiency of the entire table.

MEMORY type table access is very fast because its data is stored in memory and uses HASH index by default. However, once the service is shut down, the data in the table will be lost.

MEMORY Suitable usage environment:
Save all data in RAM, which can provide several blocks of access when fast location records and other similar data environments are required.

The flaw of MEMORY is that there is a limit on the size of the table. Tables that are too large cannot be cached in memory. Secondly, it is necessary to ensure that the data in the table can be restored. The data in the table can be restored after the database terminates abnormally. MEMORY tables are usually used for small tables that are updated less frequently to obtain access results quickly.

MERGE
The MERGE storage engine is a combination of a set of MyISAM tables. These MyISAM tables must have the same structure. The MERGE table itself does not store data. MERGE type tables can be queried and updated. , delete operations, these operations are actually performed on the internal MyISAM table.

For the insert operation of the MERGE type table, the inserted table is defined through the INSERT_METHOD clause, which can have three different values. Using the FIRST or LAST value causes the insert operation to be applied to the first or last one accordingly. On the table, if this clause is not defined or defined as NO, it means that the insert operation cannot be performed on this MERGE table.

You can perform a DROP operation on the MERGE table, but this operation only deletes the definition of the MERGE table and has no impact on the internal tables.

MERGE Suitable usage environment:
Used to logically combine a series of equivalent MyISAM tables and reference them as an object.

The advantage of the MERGE table is that it can break through the size limit of a single MyISAM table, and by distributing different tables on multiple disks, the access efficiency of the MERGE table can be effectively improved. This is very suitable for VLDB environments such as data warehousing.

Recommended learning: mysql video tutorial

The above is the detailed content of MySql optimization architecture and storage engine (summary sharing). For more information, please follow other related articles on the PHP Chinese website!