A preliminary study on MySQL storage engine

A preliminary study on MySQL storage engine

Directory:

1. Storage engine introduction

2. Performance comparison between InnoDB and MyISAM

##3 , Lock comparison between MyISAM and InnoDB

4. Comparison of indexes between two storage engines

1 Storage engine introduction

Description: MySQL database based on 5.7.19 .

Figure 1.1 Database version

Tested in Navicat for MySQL ：

Input sql: show engines;

Figure 1.2 Storage engine Category

Parameter description:

Engine: Storage engine name

Support: Whether MySQL supports it The engine

Comment: Description of the engine

Transaction: Whether transaction processing is supported

XA: Whether distributed transaction processing

#l

InnoDB

Suitable for

high-performance and transaction processing environments

, supports external Key, the default storage engine, "out of the box".

l

MyISAM

##Applicable to

mainly read-only data

in warehouse, e-commerce and enterprise applications. MyISAM uses advanced caching and indexing mechanisms to improve data retrieval and indexing speed, but does not support transactions or foreign keys.

l

Blackhole

##Applies to Test application is indeed writing data Scenarios where you don’t want to store any data on the disk

. The Blackhole storage engine meets a specific requirement. If binary logging is enabled, SQL statements will be written to the log, using the Blackhole storage engine as a relay or proxy in a replication topology. In this case, the relay agent processes data from the master and sends the data to its slaves, but it does not store any data itself.

l CSV

Suitable for writing CSV log files, the structure Quickly import business data into spreadsheet

. The CSV storage engine creates, reads, and writes comma-separated value (CSV) files in tabular format. It does not provide any indexing mechanism, has certain problems when storing and converting datetime values, and is not efficient in storing data, so it should be used with caution.

l Memory

Applicable to static data that is frequently accessed and rarely changed, such as postal code list, province and city list, classification list, etc., as well as databases suitable for using snapshot technology to access distribution data or historical data. Memory (sometimes called HEAP) is an in-memory store that uses a hashing mechanism to retrieve frequently used data, allowing for faster retrieval. Since the data is stored in memory and is only valid within the MySQL session, the data is refreshed and deleted when shutting down.

l Federated

##Suitable for distributed or data set environments. The Federated storage engine allows tables from multiple database servers to be joined. It does not move data and does not require the remote table to use the same storage engine. The Federated storage engine is currently disabled in most distributions of MySQL.

l Archive

Suitable for storing and retrieving large amounts of very few Accessed archived or historical data. The Archive storage engine stores large amounts of data in a compressed format, does not support indexes, and can only be accessed through table scans.

l

MRG_MYISAM

##Suitable for

Very large database applications

, such as a data warehouse, where data is stored in multiple tables in one or more databases. The best feature of the MRG_MYISAM storage engine is speed. It divides a large table into many different small tables, stores them on different disks, merges these small tables, and then accesses them at the same time. Searching and sorting are performed faster. Because each small table needs to manage less data.

Disadvantages:

l The same MyISAM table must be used to form a composite table;

l The replacement operation is not available;

l The index is less efficient than the index of a single table.

##2

Performance comparison between InnoDB and MyISAM

Note: The test table contains 36 fields, and contains 988218 records

.

The test database of the InnoDB storage engine is named Innodbtest, which contains the table, and the table name is Innodbtable; the test database of the MyISAM storage engine is named Myisamtest, which contains the table, and the table name is Myisamtable.

Use InnoDB and MyISAM storage engines in MySQL to test the table. First, do the preliminary work:

(1) Test The storage engine of the MyISAM storage engine table is changed from the default InnoDB to MyISAM:

alter table myisamtable engine=myisam;

##Figure 2.1 Modify the storage engine

(2) Modify the character encoding of the database and set it to utf-8

alter database myisamtest character set utf8;
alter database innodbtest character set utf8;

##Figure 2.2 Modify the InnoDB storage engine test library character encoding

Figure 2.3 Modify the MyISAM storage engine test library character encoding

Some of the two storage engines Features for testing:

l Storage structure

(1) InnoDB:

Table data is stored in a 1.21GB data file - Innodbtable.ibd. Metadata information related to the table is stored in the innodbtable.frm file, including the definition of the table structure. information. Some definition information of the database is defined in db.opt.

Figure 2.4 InnoDB disk storage directory

Figure 2.5 db.opt file content

(2) MyISAM:

.frm file: Stores metadata information related to the table, including definition information of the table structure, etc.;

.MYD file: 853.34MB in size, stores the data of the MyISAM table.

.MYI file: 34.11MB in size, which stores index-related information of the MyISAM table.

db.opt: ​​Defines some definition information of the database.

Figure 2.6 MyISAM disk storage directory

Figure 2.7 db.opt file content

##l select

(1) InnoDB:

Figure 2.8 InnoDB select test

(2) MyISAM:

##Figure 2.9 MyISAM select test

l insert

## (1) InnoDB:

##Figure 2.10 InnoDB insert test

(2) MyISAM ：

Figure 2.11 MyISAM insert test

l update

(1) InnoDB:

Figure 2.12 InnoDB update test

(2) MyISAM:

Figure 2.13 MyISAM update test

l delete

(1) InnoDB:

Figure 2.14 InnoDB delete test

(2) MyISAM:

Figure 2.15 MyISAM delete test

l delete where

(1) InnoDB:

Figure 2.16 InnoDB delete where test

(2) MyISAM:

Figure 2.17 MyISAM delete where test

##l count without where

(1) InnoDB:

Figure 2.18 InnoDB count without where test

(2) MyISAM:

Figure 2.19 MyISAM count without where test

l group by

（1）InnoDB：

 

图2.20 InnoDB的group by测试

（2）MyISAM：

图2.21 MyISAM的group by测试

l 外键

创建一个新表，将测试表的主键作为新表的外键进行测试：

create table `foreigntest`(
`id` int primary key not null,
`taskid` varchar(64) not null,
`host` varchar(128) not null default '',
`month` char(8) not null,
constraint `fk_task_h_m` foreign key (`taskid`,`host`,`month`)
references `innodbtable`(`taskid`,`host`,`month`)
) charset=utf8mb4

（1）InnoDB：

 

图2.22 InnoDB的外键测试

（2）MyISAM：

 

图2.23 MyISAM的外键测试

 

总结如下表：

(263.86 seconds) change##Check

	InnoDB	MyISAM
存储结构	.ibd：存放表数据； .frm文件：存储与表相关的元数据信息，包括表结构的定义信息等； 基于磁盘的资源是InnoDB表空间数据文件和它的日志文件，InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。	每个表在磁盘上存储成三个文件： .MYD文件：存放表的数据。 .MYI文件：存放表的索引相关信息。 .frm文件：存储与表相关的元数据信息，包括表结构的定义信息等；
存储空间	InnoDB tables require more memory and disk storage, and it will establish its own dedicated buffer pool in main memory for caching data and indexes.	MyISAM can be compressed and has smaller storage space.
portability	Free solutions can be to copy data files, back up binlog, or use mysqldump, which is relatively difficult when the data volume reaches dozens of G	##Due to MyISAM The data is stored in the form of files, so it is very convenient for cross-platform data transfer. During backup and recovery, operations can be performed on a table individually
##Transaction security	Support transactions, with transaction (commit), rollback (rollback) and crash repair capabilities	Does not support transactions, each query is atomic
increased	Better (0.15 seconds)	(0.40 seconds)
Delete (with where)	(32.79 seconds)	Better (16.51 seconds)
## Delete all	## is better (0.24 seconds )
(0.20 seconds)	##Better (0.12 seconds)
（139.75 seconds）	##More Excellent (65.57 seconds)	##lock
Supports table locks and row locks. Row locks greatly improve the performance of multi-user concurrent operations. However, InnoDB's row lock is only valid on the primary key of WHERE. WHERE that is not the primary key will lock the entire table.	Only supports table locks	Key
Supported	Not supported
count without where	No specifics of the saved table The number of rows needs to be scanned row by row for statistics (70.88 seconds)	is better because MyISAM saves the specific row number of the table and only needs to be read out simply. (0.09 seconds)
group by	(35.14 seconds)	Better (4.75 seconds)

Note:

[1]Table space: InnoDB is a tool used to organize machine-independent files, including data, indexes and returns rolling mechanism. By default, all tables share a tablespace (called a shared tablespace). Shared tablespaces do not automatically expand into multiple files. By default, a tablespace occupies only a single file, which grows as data increases. Use the autoextend option to allow the tablespace to create new files.

[2]Crash repair capability: The InnoDB storage engine uses two disk-based mechanisms to store data, namely log files and table spaces. InnoDB will use these logs to rebuild data recovery before shutting down or crashing. At program startup, InnoDB reads the log and automatically writes dirty pages to disk, restoring buffered updates before a system crash.

#3 Comparison of locks between MyISAM and InnoDB

(1) Table-level lock: low overhead, fast locking; no deadlock; large locking granularity, the highest probability of lock conflict, and the lowest concurrency.

(2) Row-level locks: high overhead, slow locking; deadlocks may occur; the locking granularity is the smallest, the probability of lock conflicts is the lowest, and the concurrency is the highest.

(3) Read operations on the MyISAM table will not block other users' read requests for the same table, but will block write requests on the same table; write operations on the MyISAM table , will block other users' read and write requests for the same table; the read and write operations of the MyISAM table, and the write and write operations are serial (when a thread obtains a write lock on a table, only The thread holding the lock can update the table. The read and write operations of other threads will wait until the lock is released)

##(4) Shared lock (s): Allows one transaction to read a row, preventing other transactions from obtaining an exclusive lock on the same data set.

(5) Exclusive lock (X): Allows transactions that acquire exclusive locks to update data and prevents other transactions from acquiring shared read locks and exclusive write locks on the same data set.

(6) For UPDATE, DELETE and INSERT statements, InnoDB will automatically add exclusive locks (X) to the involved data sets; for ordinary SELECT statements, InnoDB will not add any locks.

##4

Comparison of two storage engine indexes

l InnoDB:

l In InnoDB, the table data file itself is an index structure organized by B+Tree. The tree The leaf node data field stores complete data records. The key of this index is the primary key of the data table, so the InnoDB table data file itself is the primary index.

l The index used by the InnoDB table is a clustered index. A clustered index is a data structure that stores not only the index, but also the data itself. Therefore, once a value in the index is located, the data can be retrieved directly without additional disk seeks.

l The primary key index or the first index of the table is created using a clustered index.

l All auxiliary indexes in InnoDB refer to the primary key as the data field. If a auxiliary index is created, the keywords of the clustered index (primary key, unique key or row ID) will also be stored in the auxiliary index, so that you can quickly search according to the keywords and quickly obtain the original data in the clustered index. That is, if you use the primary key column to scan the auxiliary index, the query only needs to use the auxiliary index to obtain data.

l MyISAM:

##l Index files and data files are separated. The file only saves the address of the data record. Using

B+tree

as the index structure, the data field of the leaf node stores the address of the data record.

l In MyISAM, there is no structural difference between the primary index and the secondary index (Secondary key), except that the primary index requires the key to be unique, while the key of the secondary index can be repeated.

l Main differences:

l The difference between primary indexes: InnoDB’s data files themselves are index files. The index and data of MyISAM are separated.

l The difference between auxiliary indexes: InnoDB's auxiliary index data field stores the value of the primary key of the corresponding record instead of the address. There is not much difference between MyISAM's secondary index and the primary index.

Note:

B+ tree: For an m-order B+ tree, it has the following characteristics:

#l There are n subtree nodes containing n keywords.

l All leaf nodes contain information about all keywords and pointers to records containing these keywords. And the leaf nodes themselves are linked in order from small to large according to the size of the keywords.

l All non-terminal nodes can be regarded as index parts, and the node only contains the largest (or smallest) keyword in its subtree (root node).

l In the B+ tree, no matter whether the search is successful or not, each search takes a path from the root to the leaf node.

l Each node in the tree contains at most m subtrees.

The above is the detailed content of A preliminary study on MySQL storage engine. For more information, please follow other related articles on the PHP Chinese website!