InnoDB Chinese Reference manual---11 tables and index structures

Reference | Reference Manual | index | Chinese InnoDB chinese Reference Manual---canine (heart-sail) Translation 11 table and index structure
MySQL stores its data dictionary information in the. frm file in the database directory. However, each InnoDB type table also has its own entry point in the internal data dictionary of the InnoDB table space. When MySQL removes (drop) a table or a database, it deletes the. frm file as well as the entry point corresponding to the InnoDB data dictionary. This is why it is not possible to remove the InnoDB type table from the database by simply deleting the. frm file, and why the DROP database cannot be used for InnoDB tables in the version <= 3.23.43 of the MySQL version.

Each InnoDB table has a special index, called a clustered index, that is used to hold record row information. If a table defines a PRIMARY key, the index of the primary key is the clustered index.

If the table does not define a PRIMARY key, MySQL will select the UNIQUE key for the first not NULL field as the primary key, and InnoDB will also use the index of the key as the clustered index. If there is no such key in the table, InnoDB will produce a clustered index internally, consisting of a row of rows sorted by InnoDB assigned to them in the row ID order. This row ID is a 6-byte field that is monotonically increasing and inserting a new row. Thus, the order of records sorted by the row ID is the physical order of the insertion.

Accessing a row of records through a clustered index is very fast, because the record row data is on the same page as the index that guides us to find it. In most database systems, the recording of row and index records is usually not on the same page. If a table is too large, a clustered indexing system is generally more likely to reduce disk I/O than in traditional ways.

A record in a non-clustered index (non-clustered indexes) (which we usually call a secondary index secondary indexes) that contains the primary key value for that row in InnoDB. InnoDB will use this primary key value to find this row in the clustered index. Note If the primary key is too long, the secondary index will occupy more space.

InnoDB when comparing different lengths of CHAR and VARCHAR, the extra length of shorter strings is filled with spaces (spaces).
11.1 Physical structure of the index
All indexes in the indexes in InnoDB are the b-trees of the index record on the leaf page of the tree (leaf pages). The size of an index page defaults to KB. When a new record is inserted, InnoDB attempts to retain 1/16 of the page's spare for future inserts and update index records.

If the index record is inserted in a continuous (ascending or descending order), the index page will be used about 15/16. If inserted in a random order, the page uses about 1/2-15/16. If an index page is dropped below 1/2, then InnoDB shortens the index tree and frees the page space.
11.2 Insert Buffer
The primary key is a unique identifier, and the new record is inserted in ascending order of the primary key, which is a common case in the database system. Therefore, the values inserted in the clustered index do not need to be read randomly on the hard disk.

On the other hand, secondary indexes are usually not unique (non-unique), and inserting them in secondary indexes is a fairly random order. If you do not use a special mechanism in InnoDB this will result in a large number of random disk I/O.

If an index record is inserted into a secondary index that is not unique, InnoDB checks whether the secondary index page is already in the buffer pool. In this case, InnoDB inserts it directly into the index page. However, if the index page is not found in the buffer pool, INNDB inserts the index record into a special insert buffer structure. The insertion buffer is so small that it can be completely placed in the buffer pool, so the insertion speed is fast.

The insert buffer is periodically merged into the secondary index tree in the database. To reduce disk I/O, it is common to merge several inserts on the same page into the index tree at the same time. Inserting a buffer can increase the insertion speed of 15 times times to a table.
11.3 Adaptive Hash Index (Adaptive hash indexes)
A quick way to run a query on a database that is almost full of all the primary shares is to use a hash index (hash indexes). InnoDB has an automatic structure for monitoring index search actions on indexes defined on a table, and if InnoDB discovers that a hash index is useful for queries, it will automatically build the hash index.

Note, however, that the hash index is usually created based on a b-tree that already exists in the table. InnoDB may build a hash index by prefixing the key of any length defined in B-tree, depending on the mode of InnoDB to observe the index on B-tree. A hash index can be partial: it does not require a high-speed buffer of the entire B-tree index in the buffer pool. InnoDB builds the hash index for frequently accessed index pages as needed.

Theoretically, through this adaptive hashing index mechanism, INNPDB makes itself more suitable for large main memory (ample main memory), closer to the main memory database system (the architecture of main memory databases).
11.4 The physical structure of a record each index record in the InnoDB contains a 6-byte header. This head is used for interlocking consecutive records and is also used for row locking. The records in the clustered index contain all user-defined fields. In addition, there is a 6-byte field for logging the transaction ID (transaction ID) and one 7-byte field for the row pointer (roll pointer). In a table, if the user does not define a primary key, then each clustered index record contains a 6-byte row ID field (row ID fields). Each secondary index record contains all the fields defined for the clustered index key. An index contains a pointer to the corresponding field record. If the total length of all fields of a record < 128 bytes, then this pointer is 1 byte, otherwise 2 bytes. The InnoDB also stores fixed-length character fields, such as CHAR (10), in an internal setting. For VARCHAR fields, InnoDB will truncate the ending space. Note that MySQL may internally convert CHAR to VARCHAR. View the MySQL user's manual for "default changes to the column specification" (' Silent column specification changes '). If you store a variable length field, a SQL NULL is stored as a 0 bytes, but if a fixed-length field is stored at a constant length. The destination for storing the fixed-length space in the appropriate NULLs is to update the null field value to a value other than NULL to produce the disk fragmentation of the index page.