Shulou(Shulou.com)06/01 Report--

MySQL architecture

To understand the performance impact of MySQL itself, you need to be familiar with MySQL's architecture and common storage engines. MySQL is not perfect, but it is flexible enough to adapt to demanding environments, such as Web applications. At the same time, MySQL can not only be embedded in applications, but also support various application types, such as data warehouse, content indexing and deployment software, highly available redundant systems, online transaction processing system (OLTP) and so on.

The most important and distinctive feature of MySQL is its storage engine architecture, which is designed to separate query processing (Query Processing) and other system tasks (Server Task) from data storage / extraction. This design of separation of processing and storage allows you to choose how to store data based on performance, features, and other requirements.

The architecture of MySQL, the first layer:

Client layer, which represents various clients that can connect to the MySQL server through the MySQL connection protocol, such as PHP, JAVA, C API, ODBC, JDBC, and so on. It can be seen that these are not unique to MySQL, and most web-based Cmax S (client / server) tools or services have a similar architecture. This layer is mainly: connection processing, authorization authentication and security-related functions. Each connection to the MySQL client will have a thread in the server process, and the query for this connection will only be executed in this separate thread, that is, the query for each connection can only use the core of one CPU.

The second floor:

The second tier architecture is the interesting part of MySQL. Most of the core service functions of MySQL are in this layer, which includes query cache, query parser, query optimizer, and so on. And all of MySQL's built-in functions (for example, date, time, math, encryption functions, etc.). In short, all cross-storage engine functions are implemented at this layer. Because this layer is called the MySQL services layer, features independent of the storage engine are implemented in this layer. So what are the features that have nothing to do with storage engines? For example, the select statement, this statement for all storage engines, the function is the same. (get the data stored in the file, filter it according to our filtering conditions, and then display the data.) so the function of this select statement can be realized in the MySQL service layer, but how to get the data we want to query from the file is realized by the next storage engine layer.

The third floor:

Storage engine layer, which is the biggest difference between other databases. MySQL defines a series of storage engine interfaces, so we can also develop and customize storage engines that meet our own needs according to these interfaces. For example, Innodb is a storage engine developed by third-party companies from the very beginning. Storage engine is mainly responsible for data storage and extraction in MySQL. Each storage engine has its advantages and disadvantages. The storage engine API contains dozens of underlying functions, but the storage engine does not parse SQL (Note: InnoDB is an exception, it parses foreign key definitions because the MySQL service layer itself does not implement this function), and different storage engines do not communicate with each other, but simply respond to requests from upper-level servers. At present, the commonly used storage engines are innodb, myisam, XtraDB, CSV, Memory and so on. Because of the flexibility of MySQL, a plug-in storage engine, we can easily choose different storage engines according to different characteristics. Note that the storage engine is for tables, not libraries. (in theory, different tables in a library can use different storage engines, but this is not recommended.)

The MySQL architecture diagram is as follows:

MyISAM of common storage engines in MySQL

MyISAM is the default storage engine in versions prior to mysql5.58, and the MyISAM storage engine stores tables in two system files, one in which the data file ends in .MYD and the other in which the index file ends in .MYI. For all mysql storage engines, there is a file that ends with .frm, which is a file that records the structure of the table, so .MYD and .MYI files are specific to the MyISAM storage engine.

Features of MyISAM:

Feature one:

The first is concurrency and lock levels. MyISAM uses table-level locks instead of row-level locks, which means that the entire table is locked when the data in the table is modified. When reading the data in the table, we also need to add a shared lock to all the tables, from which we can see that the table reading and writing operations using the MyISAM engine are mutually exclusive. MyISAM is not very concurrency for read-write mixed operations, and concurrency is acceptable for read-only operations, because shared locks do not block shared locks.

Feature 2:

The second feature needs to be seen from the point of view of table corruption repair. MyISAM supports inspection and repair operations for corrupted MyISAM tables due to accidental shutdown, but this does not refer to transaction recovery. Because myisam is not a storage engine that supports transactions, it is not possible to have logs related to transaction recovery. It should be noted that repairing the MyISAM table may result in data loss, using the check table tablename command to check the table, and using the repair table tablename command to repair the defective table.

Here we use a simple example to demonstrate this feature. Create a new myIsam table in the database, and the storage engine is myIsam. The SQL statement for creating the table is as follows:

Create table myIsam (id int,c1 varchar (10)) engine=myisam

After the table is created, three files, myIsam.frm, myIsam.MYD and myIsam.MYI, will appear in the corresponding MySQL data storage directory, in which myIsam.frm stores the structure information of the table, myIsam.MYD file stores the data information of the myIsam table, and myIsam.MYI stores the index information:

At this point, we use check table myIsam; to check the table, and then use repair table myIsam; to repair the table. Msg_text shows that ok represents no problem:

If you find a damaged table, you can use repair table myIsam; to fix it. In addition, mysql provides a command-line tool: myisamchk. This tool can also fix the myisam table, but it is important to note that if you use this command-line tool to repair the myisam table, stop the mysql service first. Because if you use this tool to repair the table while mysql is running, it is possible to do some damage to the table.

Feature 3:

Indexes supported by MyISAM, myisam supports full-text indexing, and in previous versions of mysql5.7, the only native is the official storage engine that supports full-text indexing. In addition, the myisam table also supports this prefix index for the first 500 characters of fields such as test, belog, and so on.

Feature 4:

MyISAM supports table compression. If myisam is a large read-only table, that is, after the table is created and the data is imported, no operations will be done on the table, then we can compress such tables and reduce disk IHAGO. If you compress a table, you can use the myisampack command to compress the data in the table. The data in the table is compressed independently, so when reading single data, it is not necessary to decompress the whole table.

You can compress a table using the following command:

Myisampack-b myIsam.MYI

It should be noted that the compressed table can only be read, not written. If you insert data into the myIsam table at this time, an error will be reported:

Limitations of the MyISAM storage engine:

Before the mysql5.0 version, the default maximum storage size for a single table is 4G. If you want to store more than 4G data, you need to specify the values of the MAX_Rows and AVG_ROW_LENGTH parameters when creating the table. The multiplication of the values of these two parameters is the maximum size of the table storage. If you modify these two parameters to an existing large table, it is tantamount to rebuilding the table, which will take some time. Maximum support for 256TB in single table after Mysql5.0

Scenarios applicable to MyISAM:

For non-transactional applications, myisam itself is a non-transactional storage engine, and myisam does not support compression of transactions, so it is suitable for read-only applications before mysql5.7. Myisam is the only storage engine that supports spatial functions, so it is also suitable for spatial applications such as Innodb of common storage engines in MySQL.

The default storage engine after mysql5.58 is Innodb, which replaces the previous myisam. Different from myisam, Innodb is a transaction storage engine. Innodb supports transaction features, which is suitable for most application scenarios and is more suitable for dealing with a large number of small transactions.

Another difference between Innodb and myisam is the way of storage. Innodb has its own concept of tablespace. Tablespace data is stored in tablespace. Which tablespace is stored is determined by the parameter innodb_file_per_table. If this parameter is ON, a separate tablespace is established for each Innodb table, with a file with the extension tablename.ibd. If this parameter is OFF, the data will be stored in the system shared tablespace, that is, ibdataX, where X represents an integer starting at 1.

We can use the following SQL statement to view the value of this parameter:

Show variables like 'innodb_file_per_table'

The implementation results are as follows:

Next, create a new myinnodb table to see how the table is stored. The SQL statement for creating the table is as follows:

Create table myinnodb (id int, C1 varchar (10)) engine='innodb'

You can see that there are two files, one is myinnodb.frm and myinnodb.ibd, where the .frm file is the record table structure, and the .idb file is where the table data is actually stored:

Note: the version of MySQL8.x has changed. There will be only .ibd files, not .frm files. You can refer to the official documentation for details.

We can use the following command to change how the table is stored, for example, in the system tablespace:

Set global innodb_file_per_table=off

Next, create a new myinnodb_g table with the following SQL statement:

Create table myinnodb_g (id int, C1 varchar (10)) engine='innodb'

At this point, you can see that there is only one myinnodb_g.frm file, and there is no .idb file:

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.