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

This article will give you a detailed explanation of the advanced architecture of MySQL. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

5 MySQL architecture

Next, we choose some commonly used storage engines for simple explanation. The storage engine used by mysql will have a direct impact on the performance of the database. I also hope that you can carefully understand some of the characteristics of the storage engine before using the storage engine.

5.1 MyISAM

MyISAM was the default storage engine prior to MySQL5.5. For this reason, there are still many servers using MyISAM as a storage engine. At the same time, MyISAM is currently the storage engine used by many system tables and temporary tables. The temporary table we are talking about here is not the table created by us through create table, but refers to the temporary table established by the query optimizer when the number exceeds a certain size in sorting, grouping and other operations.

The MyISAM storage engine is composed of MYD and MYI. MYD is the extension of the data file, and MYI is the extension of the index file. The storage engine stores tables in the data file and index file with these two extensions.

Properties:

Concurrency and lock level

MyISAM uses table-level locks, not row-level locks, which means that the entire table needs to be locked when the data in the table is modified, and shared locks are added to all tables when reading the table. From here, we can see that the table reading and writing operations of the MyISAM engine are mutually exclusive. From this you can see that MyISAM is not very good at concurrent operations on read and write. Performance is also good in terms of concurrency if it is only for read-only operations, because shared locks do not block shared locks.

Repair of table damage

MyISAM supports MyISAM tables damaged due to any accidental shutdown for inspection and repair operations, but the repair mentioned here is not data recovery, because MyISAM is not a transactional storage engine, so it cannot carry out the relevant logs needed for transaction recovery, so be aware that the recovery of MyISAM tables may cause data loss.

We can check the table through check table tablename and restore the table through repair table tablename.

Types of indexes supported by the MyISAM table

MyISAM supports full-text indexing and is the only official storage engine that natively supports full-text indexing before the mysql5.7 version.

MyISAM table supports data compression

If MyISAM represents a large read-only table, that is, no changes will be made to the table after the table has created the imported data, then we can compress the table to reduce the disk Imax O. We can use the myisampack command to compress the table. Compression compresses the table independently, so when reading a row of data, it is not necessary to decompress the entire table.

Restrictions:

Version

The bit 256TB is supported by default in mysql5.0

Applicable scenarios:

Non-transactional application

Read-only applications (reports, etc.)

Spatial application

5.2 Innodb

Innodb is the default storage engine for MySQL5.5 and later versions, and Innodb is the storage engine for transaction storage, that is to say, it supports transaction processing.

Innodb has its own concept of tablespace, and then the data is stored in the tablespace, which is determined by the parameter innodb_file_per_table. If this parameter is ON, a system file with the extension ibd will be created for each Innodb table. If this parameter is OFF, the data will be stored in the system's shared tablespace, that is, a number represented by ibdataX,X, starting from 1 by default.

The command to view this parameter is:

Show variables like 'innodb_file_per_table'

The command to modify this parameter is:

Set global innodb_file_per_table=off

5.2.1 how to select system tablespace and independent tablespace

Compare:

System tablespaces independent tablespaces cannot simply shrink file sizes. Shrinking system files through the optimize table command will create IO bottlenecks that can refresh data to multiple files at the same time.

Recommendations:

Use separate tablespaces for Innodb

The method of transferring tables that originally exist in the system tablespace to a separate tablespace.

Steps:

Export all database table data using mysqldump

Stop the MySQL service, modify parameters, and delete Innodb related files

Restart the MySQL service and rebuild the Innodb system tablespace

Re-import data

5.2.2 Features of the Innodb storage engine

Innodb is a transactional storage engine

Fully support the ACID features of transactions (atomicity, consistency, etc., described earlier)

Redo Log and Undo Log

Redo Log implements transaction persistence and consists of two parts, one is the working log persistent buffer in memory, whose size is determined by innodb_log_buffer_size, and the other is to reconstruct log files, that is, ib_logflie-related files that we see in the file system. Undo Log implements the atomicity of the transaction and rolls back when the transaction fails. Redo Log reads and writes sequentially, Undo Log reads and writes randomly, and data can be stored on solid state drives if possible to improve performance.

Innodb supports row-level locks

Row-level lock is different from table-level lock, the characteristic of row-level lock is that it can support concurrency to the maximum extent, and row-level lock is implemented by storage engine layer.

5.2.3 Innodb status check

You can check the Innodb status using the following command:

Show engine innodb status

5.2.4 applicable scenarios

Innodb is suitable for most OLTP applications because after the mysql5.7 version, Innodb already supports full-text indexing and spatial functions.

5.2.4 (extension) what is a lock

5.2.3.1 what is a lock?

The main function of lock is to manage concurrent access to shared resources.

Locks are used to achieve transaction isolation

5.2.3.2 Type of lock:

Shared lock (also known as read lock)

Exclusive lock (also known as write lock)

5.2.3.3 compatibility between write and read locks (compatibility for one line)

Write lock read lock incompatible write lock

In practice, the results may be different from those in the above table, mainly because the locking mechanism in Innodb is a very complex thing, and there are many locks that affect the final result.

5.2.3.4 granularity of locks:

Table level lock

Row level lock

5.2.3.5 blocking and deadlock

Blocking: blocking is due to the compatibility between different locks. At some point, a lock in one transaction needs to wait for the lock of another transaction to be released, and the resources it occupies form blocking.

Deadlock: deadlock is an exception that occurs when two or more transactions occupy the resources that each other is waiting for. As can be seen from the definition, multiple transactions in blocking occupy the resources that blocked transactions are waiting for, while deadlock is when multiple blocked transactions occupy each other's waiting resources.

5.3 CSV Storage engine

CSV storage engine can deal with csv files as table files of mysql. The storage format of this storage engine is ordinary csv files. The data storage mode of csv storage engine is very special. If we store tables in MyISAM or Innodb, we cannot view the data files directly, because these two files are stored in binary format, while CSV storage engine is different. CSV data is stored in the file as text, that is, we can view it through the command to view the file, such as more, or use the vi command to view and edit the table in the csv storage engine, as long as it meets the format and requirements of the CSV file, we do not have to worry about corrupting the data.

When we set up the CSV storage engine table in mysql, we should be able to see the files in the three file systems. All three take the table name as the file name, but will be suffixed with csv,csm,frm respectively, where the csv file is the data file in the CSV storage engine. The csm file stores the metadata and table state and data volume of the table. The frm file stores table structure information.

5.3.1 Features of the CSV storage engine

The biggest feature is to store data in CSV format.

Each column in the CSV is separated by, and the contents of the text are enclosed in double quotes, as shown in the following figure:

All columns must not be NULL

When creating a table, all columns must be non-empty and cannot be stored as NULL values.

Indexing is not supported

Not suitable for large tables, not suitable for online processing

You can edit the data file directly

Save the contents of the text file

5.3.2 applicable scenarios for CSV storage engine

CSV storage engine is suitable as an intermediate table for data exchange.

5.4 Archive Storage Engin

5.4.1 File system storage characteristics

The Archive storage engine caches all writes and uses zlib to compress the inserted rows, so the Archive storage engine saves more disk I than the tables of the MyISAM storage engine. For the same order of magnitude of data, the Archive storage engine saves more storage space than MyISAM and Innodb. A multi-terabyte Innodb table is stored in the Archive storage engine and may require only a few hundred megabytes of storage space.

The data of the table of the Archive storage engine is a file with the suffix ARZ. Like other engines, there is a system file with the suffix of frm, which is used to store the structure information of the table.

5.4.2 Features of the Archive storage engine

Only insert and select operations are supported

Indexing is only allowed on self-increasing ID columns

5.4.3 Archive Storage engine usage scenario

Scenario 1: log and data collection data

Because Archive does not support modification and deletion, and we ORDB will certainly modify the data, but for some warehouse applications, or some special tables, it is still useful, for example, log tables or data collection tables, because it needs to collect a lot of data, so it is more suitable to use Archive storage engine. Because the Archive storage engine has the smallest storage space among all the engines, it should be noted that even in the application of data acquisition or log, the Archive storage engine can not update these data, so if you log or modify the data in the data acquisition application, you may not be able to use Archive storage engine.

5.5 Memory Storage engine

5.5.1 Storage characteristics of file system

The Memory storage engine is also called the HEAP storage engine, so the data is kept in memory, which means that the table of the data is one-time. Once the MySQL service is restarted, all the data of the Memory storage engine will disappear, but the table structure will be retained, because creating a table under the Memory storage engine will only generate a frm system file, which is used to save the table structure. This is why restarting the MySQL server will lose data and the table structure will not.

From its file storage characteristics, we can know that the Memory storage engine will be much more efficient than MyISAM, because only the index of MyISAM will be saved in memory, and the data will be cached by the operating system, while all the data and indexes of the Memory storage engine will be saved in memory, let's take a look at the functional features of the Memory storage engine.

5.5.2 functional features of Memory

Features:

Support for HASH indexes (default) and BTree indexes

If it is a HASH index, it will be very fast when doing an equivalent query, and if it is a range query, it will not be able to use the HASH index, so when creating a table, we need to pay attention to that if the table requires a large number of equivalent queries, use the HASH index, and the range query uses the BTree index. Different index types can have a significant impact on performance.

All fields are fixed length varchar (10) = char (10)

This requires us to define the table structure, must meet the requirements of the minimum field length, otherwise a lot of memory is wasted.

Large fields such as BLOG and TEXT are not supported

The Memory storage engine uses table-level locks

Maximum size is determined by the max_heap_table_size parameter

The default value of this parameter is only 16 megabytes. If we want to store a large amount of data in the Memory storage engine table, we need to modify this parameter. This parameter modification does not take effect on the existing Memory storage engine table. If you need to take effect on the existing table, you need to rebuild the existing table.

5.5.3 easily confused concepts in Memory

Memory Storage engine Table:

It can be used for all systems, it is not a temporary table.

Temporary table:

There are two kinds of temporary tables, one is that the system used by the query optimizer uses temporary tables, that is, internal temporary tables, and the system uses MyISAM temporary tables when it exceeds the limit (using large BLOB or TEXT fields), and does not exceed the limit on using Memory tables.

The other is a temporary table created by the command create temporary table, which can use any storage engine.

No matter what kind of temporary watch it is, it is only visible internally.

5.5.4 usage scenarios for Memory

A corresponding table used to find or map tables, such as zip codes and regions

Used to save intermediate tables generated in data analysis

Result table for caching cycle aggregate data

Memory data is easy to be lost, so data is required to be renewable.

5.6 Federated Storage engine

5.6.1 characteristics of Federated

Provides a way to access tables on a remote MySQL server

Because the Federated storage engine only establishes a connection to the remote server locally, it can be said that all the tables we want to access are still on the remote server, and the data is not stored locally. Each time the Federated storage engine table is accessed, the query is sent to the remote server to run and retrieve the relevant data from the remote MySQL server.

No data is stored locally, and all the data is placed on the remote server

You need to save the table structure and the connection information of the remote server locally

Therefore, there will also be a frm file in the system for storing remote information and information about how to connect to remote tables.

5.6.2 how to use Federated

Federated storage engine can achieve the function of SQL Server connecting to the server, but because its performance is not very good, it can usually achieve the same purpose through replication, so in the current version of MySQL, Federated storage engine is prohibited by default. If you need to use the Federated storage engine, you need to add federated=1 to / usr/local/mysql/my.cnf, and then restart the MySQL server, and we can use show engine to confirm whether the current MySQL server supports the Federated storage engine.

Instead, use the following connection string in the create table statement

Mysql://user_name [: password] @ host_name [: port_num] / db_name/tbl_name

Remote server binding connection:

Grant select,update,insert,delete on remote.remote_fet to fred_link@'127.0.0.1' identified by '123456'

You can determine the information about the remote server to be queried and some information about the relevant database tables.

5.6.3 applicable scenarios for Federated

Occasional statistical analysis and manual inquiry

Due to the slow performance of Federated, it is only suitable for occasional statistical analysis and manual query.

6 how to choose the right storage engine

Reference conditions:

Business

Backup

Crash recovery

Unique features of the storage engine

Try to avoid mixing storage engines.

This is the end of this article on "MySQL Advanced Architecture knowledge points". I hope the above content can be helpful to you, so that you can learn more knowledge. if you think the article is good, please share it 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.