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

This article is about the MySql optimized architecture and the sample analysis of the storage engine. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

I. MySQL structure system

In general, we can divide the MySQL into three tiers. In more detail, we can subdivide the storage engine layer into the engine layer and the storage layer:

Connection layer

The top layer is a number of client and link services, including local sock communications and most TCP/IP-like communications implemented based on client / server tools. Mainly complete some similar connection processing, authorization authentication, and related security schemes. The concept of thread pool is introduced on this layer to provide threads for clients with secure access through authentication. SSL-based secure links can also be implemented on this layer. The server also verifies the operational permissions it has for each client that has secure access.

Service layer

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

Engine layer

In the storage engine layer, the storage engine is really responsible for the storage and extraction of data in MySQL, and 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 own needs.

Storage layer

The data storage layer mainly stores the data on the file system and completes the interaction with the storage engine. Compared with other databases, MySQL is a little different, and its architecture can be applied and work well in a variety of different scenarios. It is mainly reflected in the storage engine, the plug-in storage engine architecture, which separates query processing from other system tasks and data storage extraction. This architecture can choose the appropriate storage engine according to the needs of the business and the actual needs.

II. Components of MySQL Server

Each layer of the MySQL structure consists of one or more parts:

Components of the connection layer

Connectors: interactive interface

The interactive components provided by MySQL, such as java,.net,php, can operate SQL statements and interact with SQL through this component.

Service layer components

Connection Pool: connection pool component

Used to manage and buffer user connections, threading and other caching requirements

Management Services & Utilities: components of management services and tools

Used for system management and control tools, such as backup and restore, Mysql replication, clustering, security management, etc.

SQL Interface: SQL interface component

Used to accept the user's SQL commands, such as DML,DDL and stored procedures, and return the final result to the user

Parser: query Analyzer component

When the SQL command is passed to the parser, it will be verified and parsed by the parser. First, analyze the validity of the syntax of the SQL command, and try to decompose the SQL command into a data structure. If the decomposition fails, it will prompt the SQL statement to be unreasonable.

Optimizer: optimizer component

The SQL statement uses the query optimizer to optimize the query before the query.

For example, select id,name from user where age = 20

This select query is selected according to the where statement first, rather than querying all the tables first and then age filtering.

This select query first projects attributes based on id and name, rather than taking out all the attributes and then filtering them.

Join the two query conditions together 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 consists of a series of small caches. Such as table cache, record cache, key cache, permission cache, etc.

Engine layer component

Pluggable Storage Engines: storage engine

Storage engine is the implementation of technology such as storing data, establishing index, updating query data and so on. The storage engine is based on tables, not libraries. So the storage engine can also be called a table type. Table creation, data storage, retrieval and update are all done by MySQL storage engine, which is also an important role of MySQL storage engine in MySQL.

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

Storage tier component

File System: file system

A system used to actually store MySQL database files, some log files, etc. Common, such as Linux, Windows and so on.

Third, storage engine

Common differences between storage engines

Prior to MySQL version 5.5, the default storage engine was MyISAM, which is native to MySQL. After version 5. 5, the default storage engine has been 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.

Table creation statements for different storage engines:

CREATE TABLE `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_ archive` (`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 now the default storage engine for MySQL. The InnoDB storage engine provides transaction security with commit, rollback, and crash recovery capabilities. But compared to MyISAM's storage engine, InnoDB writes are less efficient and take up more disk space to retain data and indexes.

The InnoDB storage engine differs from other storage engines:

Transaction control

Foreign key constraint

InnoDB stores tables and indexes in the following two ways:

Using shared tablespace storage, the table structure of tables created in this way is saved in .frm files, and the data and indexes are stored in tablespaces defined by innodb_data_home_dir and innodb_data_file_path, which can be multiple files.

With multiple tablespace storage, the table structure of the table created in this way still exists in the .frm file, but the data and indexes for each table are stored separately in .ibd.

Suitable environment for InnoDB:

InnoDB is the default storage engine for MySQL, which is used in transactional applications and supports foreign keys. If the application has high requirements for transaction integrity and requires data consistency under concurrent conditions, data operations not only insert and query accidents, but also include a lot of update and delete operations, then InnoDB storage engine is a more appropriate choice.

InnoDB storage engine can not only effectively reduce the locking caused by deletions and updates, but also ensure the complete commit and rollback of transactions. InnoDB is the most suitable choice for systems that require high data accuracy, such as billing systems or financial systems.

MyISAM

MyISAM does not support transactions and foreign keys, and its advantages are fast access, no requirements for transaction integrity, or applications based on SELECT and INSERT can basically use this engine to create tables.

There are two more important features:

Transactions are not supported

File storage mode

Each MyISAM is stored as three files on disk, all with the same file name as the table name, but with the following extensions:

.frm (storage table definition)

.MYD (MYData, storing data)

.MYI (MYIndex, store index)

Suitable environment for MyISAM:

If the application is mainly read and insert operations, only a few update and delete operations, and the transaction integrity, concurrency requirements are not very high, then it is very appropriate to choose this storage engine.

MEMORY

The Memory storage engine stores the table's data in memory. Each MEMORY table actually corresponds to a disk file, the format is. Frm, this file only stores the structure of the table, and its data files are stored in memory, which is conducive to the rapid processing of data and improve the efficiency of the whole table.

Access to a table of type MEMORY is very fast because its data is stored in memory and the HASH index is used by default, but once the service is turned off, the data in the table is lost.

Suitable environment for MEMORY:

Save all the data in RAM and provide access to several blocks in situations where you need to quickly locate records and other similar data environments.

The defect of MEMORY is that there is a limit on the size of the table, the table is too large to be cached in memory, the second is to ensure that the data of the table can be recovered, and the data in the table can be recovered after the abnormal termination of the database. MEMORY tables are usually used for small tables that are updated less frequently to get access results quickly.

MERGE

MERGE storage engine is a combination of a group of MyISAM tables, these MyISAM tables must have the same structure, MERGE tables themselves do not store data, and MERGE tables can be queried, updated and deleted, which are actually carried out on internal MyISAM tables.

For the insert operation of the MERGE type table, the inserted table is defined by the INSERT_ NO clause, which can have three different values. Use the FIRST or LAST value to make the insert operation be acted on the first or last table accordingly. If this clause is not defined or defined as NO, it means that the insert operation cannot be performed on this table.

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

Suitable environment for MERGE:

Used to logically group a series of equivalent MyISAM tables together and reference them as an object.

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

Thank you for reading! This is the end of this article on "example Analysis of MySql optimized Architecture and Storage engine". 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 out 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.