Chat about MySQL InnoDB storage engine (end)

Chat about MySQL InnoDB storage engine (i)

Chat about MySQL's InnoDB storage Engine (ii)

Chat about MySQL InnoDB storage engine (end)

In this paper, we discuss the processing method of Phantom reading in InnoDB engine and the detection of deadlock and some precautions to avoid deadlock. In this article, we will study the index of the next InnoDB.

There are four main indexes involved in InnoDB, namely, clustered index (Clustered index), sub-index (secondary index), full-text index (fulltext index), hash index.

Clustered index and secondary index

Each InnoDB table has a unique clustered index. In general, the primary key for each table is the clustered index. If your table does not have a primary key defined, MySQL will use the first non-null unique index as the clustered index. If there is neither a primary key nor a suitable unique index in the table, InnoDB generates a hidden clustered index on its own.

The query speed through the clustered index is very fast because the queried index points directly to the data row. If the amount of data in a table is very large, the clustered index is frequently read and written, causing the I/O load to be taught high. In particular, data files and index files are not in a single file.

Secondary index, the index of all nonclustered indexes is referred to as the secondary index. Secondary indexes can have many, and each secondary index record contains a primary key column. When a user uses a secondary index query, MySQL queries the primary key corresponding to the secondary index. If the primary key takes up too large a byte, the secondary index requires more space. So, the primary key or the shorter the better.

Full-Text Indexing

InnoDB supports full-text indexing after 5.5. Full-text indexing helps users quickly query for columns that have full-text indexes set. Full-text indexing can be set using the syntax settings such as CREATE TABLE or ALTER TABLE or CREATE INDEX.

A full-text index has a design called a "reverse index." Reverses the index stores every word that appears in the data column. It divides the documents in the data column into different words, storing information such as the word, the location information of the word, the offset, and so on, in the full-text index table.

The following describes the full-text index table, which stores full-text indexes, first:

mysql> use test;

Mysql> CREATE TABLE Opening_lines

(ID INT UNSIGNED auto_increment not NULL PRIMARY KEY,

Opening_line TEXT (500),

Author VARCHAR (200),

Title VARCHAR (200),

Fulltext IDX (Opening_line))

Engine=innodb;

Query OK, 0 rows affected (0.11 sec)

Mysql> Select table_id, name, space from INFORMATION_SCHEMA. Innodb_sys_tables WHERE name like ' test/% ';

+----------+----------------------------------------------------+-------+

| table_id | name | Space |

+----------+----------------------------------------------------+-------+

| 54 |    test/fts_0000000000000030_0000000000000039_index_1 | 40 |

| 55 |    Test/fts_0000000000000030_0000000000000039_index_2 | 41 |

| 56 |    Test/fts_0000000000000030_0000000000000039_index_3 | 42 |

| 57 |    Test/fts_0000000000000030_0000000000000039_index_4 | 43 |

| 58 |    test/fts_0000000000000030_0000000000000039_index_5 | 44 |

| 59 |    Test/fts_0000000000000030_0000000000000039_index_6 | 45 |

| 51 |    test/fts_0000000000000030_being_deleted | 37 |

| 52 |    Test/fts_0000000000000030_being_deleted_cache | 38 |

| 53 |    Test/fts_0000000000000030_config | 39 |

| 49 |    test/fts_0000000000000030_deleted | 35 |

| 50 |    Test/fts_0000000000000030_deleted_cache | 36 |

| 45 |    Test/b#p#p0 | 31 |

| 46 |    TEST/B#P#P1 | 32 |

| 47 |    TEST/B#P#P2 | 33 |

| 21 |     Test/imptest | 7 |

| 48 |    Test/opening_lines | 34 |

| 20 |     test/product | 6 |

| 42 |    test/t | 28 |

+----------+----------------------------------------------------+-------+

Rows in Set (0.01 sec)

First create a full-text indexed table "Opening_lines" within the database, and then view the system table information for InnoDB.

Similar to TEST/FTS_XXXXXXX_XXXXXXXX_INDEX_XX is the index table. The index table is prefixed with fts_ and index_xx is a suffix. The table naming rules are as follows: we can see that the table_id of the Opeining_lines table is 48,48 converted to 16 in 30, and the first underscore in the name of the index table is followed by 00000. 030, they are the opposite. The other one that has a corresponding relationship is the value after the second underscore in the name of the index table, in this case the value is 39, the conversion to 10 is 57, and the value is the index ID. The table_id can be queried based on the index ID.

Mysql> Select Index_id,name,table_id,space from INFORMATION_SCHEMA. Innodb_sys_indexes WHERE index_id=57;

+----------+------+----------+-------+

| index_id | name | table_id | Space |

+----------+------+----------+-------+

| 57 |       IDX |    48 | 34 |

+----------+------+----------+-------+

1 row in Set (0.01 sec)

As you can see, the table_id corresponding to the value of the query is the corresponding table_id of Opening_lines.

Full-Text indexing filters words within a document into different index tables, which can result in a large number of I/O operations in high concurrency, affecting system performance. To do this, MySQL designed the full-text index cache. The cache stores the most recently inserted data index, and when the cache is full, the index data is written to disk in bulk. Due to the presence of a cache, in a transaction, MySQL has a special way of handling full-text indexing, and the full-text index must not take effect until the transaction is committed. You can refer to the following examples:

Mysql> begin;

Query OK, 0 rows Affected (0.00 sec)

Mysql> INSERT into Opening_lines (opening_line,author,title) VALUES

(' Call me Ishmael ', ' Herman Melville ', ' Moby-dick '),

(' A screaming comes across the sky. ', ' Thomas Pynchon ', ' gravity\ ' s Rainbow '),

(' I am an invisible man. ', ' Ralph Ellison ', ' Invisible Man '),

(' Where now? who now? When now? ', ' Samuel Beckett ', ' The Unnamable '),

(' It is love at first sight. ', ' Joseph Heller ', ' Catch-22 '),

(' All this happened, more or less ', ' Kurt Vonnegut ', ' slaughterhouse-five '),

(' Mrs. Dalloway said she would buy the flowers herself. ', ' Virginia Woolf ', ' Mrs Dalloway '),

(' It is a pleasure to burn. ', ' Ray Bradbury ', ' Fahrenheit 451 ');

Query OK, 8 rows Affected (0.02 sec)

Records:8 duplicates:0 warnings:0

Mysql> SELECT COUNT (*) from Opening_lines WHERE MATCH (opening_line) against (' Ishmael ');

+----------+

| COUNT (*) |

+----------+

| 0 |

+----------+

1 row in Set (0.02 sec)

Mysql> commit;

Query OK, 0 rows Affected (0.00 sec)

Mysql> SELECT COUNT (*) from Opening_lines WHERE MATCH (opening_line) against (' Ishmael ');

+----------+

| COUNT (*) |

+----------+

| 1 |

+----------+

1 row in Set (0.00 sec)

Before the submission, the query is not the keyword ' Ishmael ', after the submission of the query.

Hash index

The parameter that starts the hash index is innodb_adaptive_hash_index. The hash index hashes the keywords on the b-tree index tree onto the hash table. Hash indexes are primarily used for exact lookups, such as =,in. Lookups such as like or wildcard characters are not appropriate for using hash indexes. The hash index is hashed by the index keyword, and the size of the transformed hash value cannot correspond to the original value. Therefore, the hash index cannot be used to avoid sorting operations on the data.

This article is from the architect's path blog, so be sure to keep this source http://wangweiak47.blog.51cto.com/2337362/1592569

Chat about MySQL InnoDB storage engine (end)