MySQL-storage engine Analysis

Mysql logical architecture view main storage engine: www.2cto.com engine Mysql version transaction lock granularity reference not to mention InnoDB all support Mvcc row-Level Lock transaction processing MyISAM all support concurrent insert table lock Select, myISAM Merge does not support Concurrent Insertion of table locks for segmented archiving, and many global lookup Memory (HEAP) does not support table lock intermediate computation, static data validation for large datasets, persistent storage, data loss after restart Falcon 6.0 support Mvcc row-Level Lock transaction processing Archive 4.1 Support Mvcc row-Level Lock log records, aggregate analysis, only support insert, the select operation requires random reading and deletion. CSV 4.1 does not support table lock log records, and large-scale loading of external data requires random reading. NDB Cluster 5.0, such as indexes, supports row-Level Lock. high-availability clusters typically reference Maria. 6. x supports mvcc row-level locks instead of MyISAM. You can use show table status like 'table name' \ G to view table information lock granularity: www.2cto.com 1. table Lock: when a user writes (add, delete, modify) a Table, a write Lock is obtained, and other users are prohibited from performing read/write operations, when no one performs a write operation, other users can obtain the read lock. The read lock does not conflict with the read lock. Table locks are the minimum lock policy and have good performance. Table locks are implemented by Mysql itself and the storage engine. 2. Row-level locks support maximum concurrent processing, but also bring about the maximum Lock overhead. Row-level locks are implemented in InnoDB and the Falcon storage engine. Row-level locks are implemented by the storage engine, rather than the Mysql server itself. 3. MVCC row-Level Lock MVCC is a Multiversion Concurrency Control technology. MVCC is not unique to mysql and is also used by other databases such as Oracle and PostgreSQL. Each storage engine implements MVCC in different ways, such as optimistic concurrency control and pessimistic concurrency control. The following describes how it works in Innodb: innodb adds two hermit fields to each data row to implement MVCC. These two hermit fields record the creation time and expiration time (deletion time) of the row ), each row stores the system version number when an event occurs, which is used to replace the actual time when the event occurs. Each time a new transaction is started, the version number increases, and each transaction will save its "system version" record at the beginning, and each query will be based on the transaction version number, check the version number of each data row. SELECT www.2cto.com Innodb checks each row of data to ensure that they comply with two standards * Innodb only searches for data whose version number is earlier than or equal to the current version number, this ensures that the data rows read by the current transaction exist before the start of the transaction, or the rows created or modified by the current transaction. * The row deletion identifier must be undefined or later than the transaction version. This ensures that the queried data is not deleted at the beginning of the transaction. INSERT Innodb records the current version number of the new data row. DELETE modifies the expiration time of the row to be deleted. UPDATE creates a copy of the new row for each row to be updated, and record the current system version for the new row copy. The system version number is recorded for the number of rows before the update, which is used as the delete version ID of the old row. The advantage of saving these additional records is that most concurrent operations do not have to apply for shackles, which makes read Operations faster, because read operations only need to select data lines that conform to the standard, the disadvantage of this method is that the storage engine must store more additional data for each row, waste space, perform more checks, and sort out additional data. Www.2cto.com locking policy concurrent system overhead engine table-level locking minimum MyISAM, Merge, MyISAM Merge row-level locking high NDB Cluster support MVCC row-level locking maximum InnoDB, Falcon selects the appropriate engine: 1. if the application needs transaction processing, Innodb is still the most stable. If no transaction is required, the main operation is to process some SELECT and INSERT operations or dedicated log records, MYISAM is a good choice. 2. Concurrency 3. Backup 4. Recovery after crash