20160924-1--mysql Storage Engine

First, the main storage engine

After 5.5, the default storage engine was changed from MyISAM to InnoDB; recommended on line InnoDB

Second, InnoDB storage engine (a) structure

The InnoDB storage engine is roughly divided into three parts, shown in (1) (2) (3). As for the MySQL server layer, it is the layer component of the SQL interface, the parser, the optimizer, and the buffer pool.
In
(1) is a cache layer, consisting of a large innodb buffer pool and a number of other small memory components, used to buffer the data, INNODB data read write not directly manipulate the file, but from the file loaded into the cache, in the buffer to do operations, and then flush to disk file ';
(2) is a variety of background threads, such as IO thread, log thread, monitoring thread, etc.;
(3) is a variety of data file layer, such as innodb data files, redo log, etc.;

(ii) Characteristics of InnoDB

1, InnoDB support transactions, transaction acid features full support:
(1) atomicity (A): The rollback of rollback segments to ensure atomicity, that is, Unlog log log rollback guarantees atomicity;
(2) Consistency (C): By supporting the main foreign key to ensure consistency;
(3) Isolation (I): Through the transaction version + rollback segment =MVCC (multi-version concurrency), that is, through the MVCC (multi-version concurrency) mechanism to ensure isolation;
(4) Persistence (D): Ensure persistence through the transaction log (redo log);

(iii) TRANSACTION ISOLATION LEVEL

1, support four different transaction isolation levels, the default transaction isolation level is Repeatable-read (repeatable Read):

(iv) lock of the InnoDB storage engine

1, support row-level lock, write does not block read; The writing between the peers does not block each other; concurrency performance is good
2. Row locks are added to the index (or row-level locks are implemented on the index), and row locks are converted to table-level locks If there are no indexes on the update field.

(v) INNODDB data block Cache pool

1, the data read and write needs to be cached (the data read and write is not directly read and write disk files, but first load the file data into memory, then read and write (Dirty page after the update), and then periodically refresh the dirty pages to disk according to the configured policy
2, InnoDB data is in full page (page,16k) units, read into the cache;
3. The data in the cache is swapped out with LRU policy;
4. High IO efficiency

(vi) INNODB data file storage structure

1, InnoDB storage type of table, is the Index organization table (cluster table), its entire table data is an index table. Table data is sorted by primary key, and if no index is used, the unique index key is checked, the unique key is indexed, and if there is no primary key and no unique index key, an ROWID is automatically assigned internally.
2, INNODB structure of the table is the index structure, data nodes per page is 16K.
3, InnoDB structure table is the Index organization table (cluster table), according to the primary key addressing speed quickly.
Insert inserts with primary key value increment are efficient, and primary key random insert operation is inefficient. Therefore, the InnoDB table must have a primary key, and it is recommended to use the self-increment number.

(vii) INNODB data persistence and transaction log

1. The transaction log (redo log) is persisted in real time,
2, Memory change data (dirty data) incremental asynchronous brush out to disk files;
3, instance failure by replay log recovery;
4, good performance, reliable, fast recovery

Second, MyISAM storage Engine (a) features

1, formerly known as ISAM storage engine;
2, MyISAM storage Engine data organization is a heap table, and the index of the organization table is different (InnoDB data organization is the Index organization table).
For example, insert a data id=2 (ID is the primary key, the table already exists id=1 and id=3 data),
(1) MyISAM storage Engine: If inserting id=2, the insertion position of data is not related to Id=1 id=3, which is unordered;
The Insert property of a heap table is unordered, and other databases such as Oracle PostgreSQL Insert data in the form of a heap table;
(2) InnoDB storage Engine: If inserting id=2, it will be inserted between id=1 and id=3, because InnoDB is the Index organization table is ordered;
The most important feature of index organization table is that it is very efficient to query based on primary key.
3, MyISAM storage engine does not support transactions;
4. The cache () of the MyISAM storage engine does not cache data, only the index is cached. The data cache is given to the operating system's memory for caching.
This can be problematic: if you query a large table, it consumes a lot of operating system memory, and if the table is large, it can waste a lot of operating system memory.
5, lock granularity is large: the use of Read and write locks (read when not allowed to write, when writing is not allowed to read, only read the time allowed to read)
6, the data files can be copied directly, occasionally may be used. But it's not recommended to use the engine on line now.
Because:
A, do not support business;
B, lock granularity is too large concurrency performance is poor;
C, Cache-only index does not cache data, the system memory has a large impact on the risk;
D, 8.0 cancelled the MyISAM engine;

Third, memory storage engine

1, data full memory storage, can not be persisted;
2, high performance;
3, do not support business;
4, suitable for occasional use as a temporary table;
Create Tmpporary table tmp (...)

Iv. Blaclhold Storage Engine

1, the data does not make any storage;
2, using MySQL replicate, as a log server, in the MySQL replicate environment to act as the principal agent.

Generally use Blackhold as the log server to record all changes to the database. Distribute logs to other slave servers.

Five, TOKUDB storage engine

1. Fractal tree storage structure;
2, support business;
3, row lock;
4, the compression efficiency is high;
5, suitable for high-volume insert scene (throughput/io efficiency)
6, Backup is troublesome, only Enterprise Edition has online Backup tool, Community Edition has no online backup tool;

VI. MYSQL CLUSTER

1, multi-master distributed cluster;
2. Redundancy between data nodes, high availability;
3, support business;
4, the design is easy to expand;
5, facing the future, online cautious use;

From for notes (Wiz)

20160924-1--mysql Storage Engine