1. IntroductionThe InnoDB storage engine supports the following types of sleep indexes: 1.1 B + Tree index (Balanced tree index) 1.2 Full-text index 1.3 hash index InnoDB storage The hash index supported by the engine is adaptive, and the InnoDB storage engine automatically generates a hash index for the table based on the usage of the table, which means that the hash index cannot be generated for the table. B + Tree index is the traditional index, the current relational database to find the most commonly used and most effective index. B + Tree Index primers are constructed similar to binary trees, where data is quickly found based on key values. Note: The B + Tree index cannot find a specific row for a given key value. The B + Tree index can find only the page where the data row is located. The database then finds the data in memory by reading the page into memory.
2. Data structure and algorithmIntroduce some of the algorithms and data structures that are closely related to the index before you introduce it to help readers better understand how the B + Tree index works. 2.1, Binary search method 2.2, binary search tree and Balance binary tree 2.3, B + Tree
3. B + Tree IndexThe data structure of B + tree and its general operation are discussed earlier, and the essence of B + Tree Index is the implementation of B + tree in database. B + Tree Index in the database is characterized by high fan-out, so the B + tree debugging is generally on the 2-4 layer, that is, to find a key value of the row records up to 2 to 4 times the IO. The B + Tree index in the database is divided into
Clustered Indexes (clustered index)And
secondary Indexes (secondary index), but whether it is a clustered index or a secondary index, the interior is B+SCF, highly balanced, and the leaf node holds all the data. Clustered index and secondary index difference: whether the leaf node is storing a whole row of information 3.1, clustered index clustered index (clustered index) constructs a B + tree according to the primary key of each table, while the leaf node holds the number of row records for the entire table The leaf node of the clustered index is also called the data page. Since the actual data page can only be sorted by a B + tree, each table can have only one clustered index. The query optimizer tends to adopt a clustered index. The clustered index can find the data directly on the leaf nodes of the B + Tree index, because it defines the logical order of the data. The clustered index applies to queries against range values. Pros: Sort lookups and range lookups for primary keys very quickly. 3.2, secondary index (non-clustered index) auxiliary cable (secondary index), leaf node does not contain all the data of the row record. In addition to the key values, the leaf node contains a bookmark (bookmark) in the index row in each leaf node. This bookmark is used to tell the InnoDB storage engine where to find the row data corresponding to the index. There can be multiple secondary indexes on each table, and when looking up data through a secondary index, the InnoDB storage engine traverses the secondary index and obtains a primary key to the primary key index through a leaf-level pointer, and then finds the finished row record through the primary key index.
4. Hashing AlgorithmThe hashing algorithm is a common algorithm with a time complexity of O (1). 4.1. hash table (hash table) hash table is also known as a hash table, which is improved by the direct addressing list. Direct addressing technology has a very obvious problem, if the domain U is large, in a typical computer can be easily limited, to store the size of U in the machine a table t is a bit impractical, even impossible. So the hash table appears. In the hash mode, the element is in H (k), using the hash function h to calculate the position of the slot based on the keyword K. Hash-Collision, Workaround: Link method (chaining) The InnoDB storage engine uses a hashing algorithm to find the dictionary, the conflict mechanism is linked to the list, and the hash function uses the division hashing method. For page pages in the buffer pool, there is a chain pointer to the same hash function.
5. Full-Text SearchB + Tree index is a feature that can be searched by the prefix of the indexed field. For example: SELECT * from Bolog where the content like ' xxx% ' the above SQL statement can look up the blog with the content of xxx beginning of the article, as long as the contents of the B + Tree index, you can use the index for quick query. But more scenarios are supported for '%xxx% ' while B + tree indexes are not supported, InnoDB provides full-text search support. Inverted index: Full-text retrievals are typically implemented using inverted indexes (inverted index). An inverted index is also an index structure. It stores the mappings between the word and the word itself in one or more documents in the secondary table.
InnoDB Storage Engine-Index
