MYSQL_ Multi-version concurrency control, storage engine, Index introduction

Multi-version concurrency control

Most of MySQL's transactional storage engines do not implement simple row-level locks. Based on the consideration of improving concurrency performance, they generally implement multi-version concurrency control at the same time.

MVCC can be thought of as a variant of row-level locking, but in many cases it avoids lock-up operations because of lower overhead.

The MVCC of InnoDB is implemented by the two hidden columns that are saved at the end of each row of records, the two columns, the creation time of a saved row, the expiration time (or deletion time) of a saved row, of course, not the actual time value stored, but the system version is good. Each start of a new transaction, the system version number is automatically incremented. The system version number at the start of a transaction is compared to the version number of the transaction that is used to query for each row.

Repeatable Read isolation level, the implementation of MVCC:

• SELECT

• InnoDB the lookup version of the data row that is earlier than the current transaction version number, which ensures that the transaction reads the row, either before the transaction begins, or the transaction itself is inserted or modified.

• The deleted version of the row is either undefined or larger than the current transaction version number, which ensures that the transaction read to the row is not deleted before the transaction begins.

• INSERT

• InnoDB saves the current system version number as the row version number for each newly inserted row.

• DELETE

• InnoDB saves the current system version number for each row deleted as a row delete identity.

• UPDATE

• InnoDB to insert a new record, save the current system version number as the line version number, while saving the current system version number to the original line as the row to delete the version number.

MVCC only works on the two isolation levels of repeatable read and read commited. The other two isolation levels are incompatible with MVCC. Because read uncommited always reads the most recent data rows, not the data rows that match the current version of the transaction. The serializable will lock the rows of all the data being read.

Storage Engine

InnoDB Storage Engine

InnoDB is the default transactional engine for MySQL and the most important and widely used storage engine. Unless there are very specific reasons to use other storage engines, the InnoDB engine should be a priority.

The InnoDB employs MVCC to support high concurrency and achieves four standard isolation levels. The default level is REPEATABLE READ (repeatable read), and through the gap lock +MVCC policy to prevent Phantom read implementation, the Gap lock allows InnoDB not only to lock the query design rows, but also to lock the gaps in the index to prevent the insertion of phantom rows.

Gap Lock: When we retrieve data with a range condition rather than an equal condition, and request a shared or exclusive lock, InnoDB locks the index entry for an existing data record that meets the criteria, and for a record in which the key value is within the condition but does not exist, called "gap", InnoDB also locks the "gap". This locking mechanism is called a gap lock (Next-key lock).
Reference: Gap Lock (Next-key Lock)

The primary index is a clustered index that saves data in the index, thereby avoiding direct disk reads, thus greatly improving query performance.

Many optimizations are made within the InnoDB, including the predictable pre-reading of data from disk, the ability to automatically create a hash index in memory with an adaptive hash index for acceleration operations, and an insertion buffer that accelerates the insertion operation.

MyISAM Storage Engine

In mysql5.1 and previous versions, MyISAM is the default storage engine. MyISAM provides a number of features, including full-text indexing, compression, spatial functions, and so on, but does not support transactional and row-level locks, and there is a no-doubt flaw that cannot be safely recovered after a crash.

The MyISAM engine can still be used for read-only data, or if the table is small and can tolerate repair operations.

When creating the MyISAM table, if the Delay_key_write option is specified, the modified index data will not be written to disk immediately at the completion of each modification execution, 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, requiring a repair operation.

Comparison

• Transaction: InnoDB supports transactions, MyISAM does not support transactions.

• Lock granularity: InnoDB supports table-level locks with row-level locks, while myisam supports table-level locks only.

• FOREIGN key: InnoDB supports foreign keys.

• Backup: InnoDB supports hot backup, but requires tools.

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

• Other features: MyISAM supports full-text indexing, compression, spatial functions, and more.

Type of Backup

• Cold (cold Backup): Need to shut down the MySQL service, read and write requests are not allowed in the state;

• Win Bei (warm backup): Service online, but only support read requests, do not allow write requests;

• Hot backup: The business is not affected at the same time as the backup.

Index

An index (also called a "key") is a data structure that the storage engine uses to quickly find records.

B-tree Index

This index is supported by most MySQL engines.

Although the term "b-tree" is used, but different storage engines may use different storage structures, the NDB cluster storage engine is actually using T-TREE,INNODB to use B+tree.

The B-tree index speeds up access to data because the storage engine does not need a full table scan to get the data it needs, and instead starts searching from the root node of the index, so the lookup is much faster.

B-tree is the sequential organization of indexed columns and is ideal for finding range data. Because the index tree is ordered, it can be used for sorting and grouping in addition to user lookups.

You can specify multiple columns as index columns, and multiple indexed columns collectively make up the index key. The B-tree index applies to full-key values, key-value ranges, or key-prefix lookups where the key-prefix lookup is only applicable to lookups based on the leftmost prefix. The lookup must begin with the leftmost column of the index.

Data structures for B-tree indexes

B-tree

In order to describe B-tree, first define a data record as a two-tuple [key,data],key as the key value of the record, for different data records, key is not the same, data records to record the data except key.

• All nodes have the same depth, which means that the b-tree is balanced.

• Keys in one node are not descending from left to right.

• If the left and right neighboring keys of a pointer are keyi and keyi+1, and not NULL, then the pointer points to all keys of the node greater than or equal to keyi and less than or equal to keyi+1.

Lookup algorithm: First in the root node for binary lookup, if found to return the corresponding node of the data, otherwise in the corresponding interval pointer to the node of the recursive search.

Since inserting deletes a new data record destroys the nature of the b-tree, it is necessary to do a split, merge, rotate, and so on to preserve the b-tree nature of the tree when inserting the delete.

B+tree

Compared with B-tree, B+tree has the following characteristics:

• The pointer to each node is capped at 2d instead of 2d+1 (d is the B-tree degree).

• The inner node does not store data, only the key, and the outer node does not store the pointer.

B+tree with sequential access pointers

Generally, the b+tree structure used in database system or file system is optimized on the basis of classical b+tree, and the sequential access pointers are added.

The purpose of this optimization is to provide performance for interval access, if you want to query all records with key 18 to 49.

Advantage

A balanced tree such as red and black trees can also be used for indexing, but file systems and database systems generally use B-tree as the index structure for the following two reasons:

• Better retrieval times: The time complexity of the data retrieved by the balance tree is equal to the tree height h, while the tree height is approximately o (h) = O (Logn), where D is the degree of each node. Red black Tree out of 2, and the b-tree is generally very large, red and black tree tree height h significantly more than b-tree dozen, so the number of retrieval is more. B+tree compared to b-tree more suitable external memory index, because the B+tree node in the data domain is removed, so can have a greater degree of retrieval efficiency will be higher.

• Take advantage of the computer pre-read feature: To reduce disk I/O, the disk is often not read strictly on-demand, but is read-ahead every time. The rationale for this is the well-known local principle of computer science: When a data is used, the data around it is usually used immediately. During the pre-read process, the disks are sequentially read, sequential reads do not require a disk seek, and require only a short rotation time, so the speed is very fast. The operating system typically divides memory and disk into solid-state chunks, each of which is called a page, and memory and disk Exchange data in pages. The database system sets the size of one node of the index to the size of the page, so that one-time I/O can fully load a node, and can take advantage of the read-ahead feature, where adjacent nodes can be preloaded.

Reference: Data structure and algorithm principles behind MySQL index

Hash index

The InnoDB engine has a special feature called an Adaptive hash Index, and when an index value is used very frequently, a hash index is created on top of the B+tree index, allowing the B+tree index to have some advantages of a hash index, such as a fast hash lookup.

A hash index can be found at O (1) time, but it loses its order, and it has the following limitations:

• The hash index contains only the hash value followed by the row pointer, not the field value, so you cannot use the values in the index to avoid all rows.

• cannot be used for sorting and grouping.

• Only exact lookups are supported and cannot be used for partial lookups and range lookups.

• When a hash conflict occurs, the storage engine must traverse all the row pointers in the linked list.

Spatial Data Index (R-TREE)

The MyISAM table supports spatial indexes and can be used as a geographic data store. Spatial Indexes index data from all dimensions, and queries can be combined according to any dimension.

You must use MySQL's GIS-related functions such as mbrontains () to maintain your data.

Full-Text Indexing

A full-text index is a special type of index that looks for keywords in text instead of directly comparing the values in the index. The find condition uses the MATCH against instead of the normal where.

A full-text index is typically implemented using an inverted-sort index, which records the mapping of the document where the keyword expires.

The MyISAM storage engine supports full-text indexing, and the InnoDB storage engine also starts to support full-text indexing in MySQL 5.6.4.

Benefits of indexing

• Significantly reduces the number of data rows the server needs to scan.

• The help server avoids sorting and creating temporary tables (The B+tree index is ordered and can be used for order by and group by operations).

• Turn random I/O into sequential I/O (the B+tree index is ordered, and the adjacent data is stored together).