Mysql Storage Engine

InnoDB

InnoDB is the default transactional storage engine for MySQL, and only consider using other storage engines if you need to InnoDB unsupported features.

The MVCC is used to support high concurrency, and 4 standard isolation mechanisms are implemented, the default level is repeatable read, and the presence of Phantom reads is prevented through the gap lock policy. The gap lock allows the InnoDB not only to lock the rows involved in the query, but also to lock gaps in the index to prevent the insertion of phantom rows.

The table is based on the clustered index, which improves the query performance of the primary key.

There are many internal optimizations, including a predictable read when reading data from disk, an adaptive hash index that automatically creates Hachiso in memory to speed up read operations, an insert buffer that accelerates insert operations, and so on.

Support real hot backups with a few mechanisms and tools. Other storage engines do not support hot backups, and to get a consistent view you need to stop writing to all tables, while in a read-write mixed scenario, stopping the write may also mean stopping the read.

InnoDB Architecture

The

innodb is a very large system that contains many subsystems and the code is distributed in dozens of directories. A hierarchical design is used.

 

on the first floor, The presence of the Handle API allows InnoDB to be successfully plugged into the MySQL server. The InnoDB also provides an API for the application system, in which case the user can embed the InnoDB storage engine directly into his application system.

The second layer is the transaction layer. In InnoDB, all behavior occurs in a transaction. If we are in my. The Auto-commit attribute is configured in CNF, and each SQL statement we execute is a separate transaction. SQL keyword commit, rollback, etc. are parsed by query parsing subsystem into INNODB-specific query statements.

The third layer is the lock function layer. This layer completes the lock function and transaction management and functionality (such as rollback, commit, and so on). The InnoDB uses a row-level read-write lock. The LOCK0LOCK.C in the lock directory handles all of the lock functions. InnoDB also deliberately uses a lock table to track the situation of various locks.

layer fourth is cache management. The primary goal of the cache management layer is to efficiently store the data in memory. The functionality of this layer is implemented by the source files in directory buf. BUF0BUF.C provides a cache pool for paging files into memory.

layer fifth is cache management. The primary goal of the cache management layer is to provide interfaces for file reads and writes and to maintain the size of the tablespace and log space. The code for this layer is mainly distributed under the FIL directory. InnoDB to provide efficient disk access, the logical fast data is distributed to several physical files using a similar REAID0-like stripe approach.

 

MyISAM

MyISAM provides a number of features, including full-text indexes, compressed tables, spatial data indexes, and so on. It should be noted that MySQL has also added full-text indexing support for the InnoDB storage engine.

Transactions are not supported.

Row-level locks are not supported, only the entire table can be locked, read all the tables needed to read the shared lock, write to the table and lock it. However, colleagues who have read queries on the table can also insert new records into the table, which is called concurrent insertions.

Check and repair operations can be performed manually or automatically, but unlike transactional recovery and crash recovery, some data loss may occur, and the repair operation is very slow.

If the Delay_key_write option is specified, the modified index data will not be written to the disk immediately upon completion of the practice, but will be written to the in-memory key buffer and the corresponding index block will be written to disk only when the key buffer is cleared or the table is closed. This approach can greatly improve write performance, but will cause index corruption when the database or host crashes, and requires a repair operation.

The MyISAM design is simple and data is stored in a compact format. For read-only data, or if the table is small and can tolerate repair operations, you can still continue to use MyISAM.

MyISAM Architecture:

MyISAM when you create a table, the directory typically generates 3 files:. MYD,. Myi and. frm files;. myd store data,. Myi store the index, and the table structure is stored in the. frm.

1, data files (. MYD)

Data file format is relatively simple, that is, data and metadata are interspersed with each other storage. The MyISAM supports three different storage formats-fixed, dynamic, and compressed.

2. index file (. MYI)

Each table of the MyISAM storage engine corresponds to a myi file. The Myi file contains two parts-header information and index values.

3. Meta data file (. frm)

A table in MySQL that has a. frm as the extension file on the disk. The frm format is the same on all platforms.

Comparison:

1, transaction: InnoDB is transactional.

2, Backup: InnoDB Support Online hot backup.

3. Crash recovery: The probability of damage after MyISAM crashes is much higher than InnoDB, and the recovery speed is slower.

4, Concurrency: MyISAM only supports table-level locks, while InnoDB also supports row-level locks.

5. Other features: MyISAM supports compressed tables and spatial data indexes.

Mysql Storage Engine