Index organization table

All the data in the Innodb storage engines are deposited according to the order of the index, this kind of structure is an index table, when creating a table if there is no statement to the primary key index, see if there is any statement unique index, such as have will list a primary key, the unique index, if there is no life will automatically create a 6 – byte Pointers

InnoDb stores data structures

All data is logically stored in a space called a table space, which is made up of segments, extents, and pages, which in some documents are called blocks

  1. Table space

    Is the highest level of the logical structure where all data is stored

  2. Period of

    A table space consists of multiple segments, including data segments, index segments, and rollback segments. Data segments are leaf nodes of the B+ tree, and index segments are non-leaf nodes of the B+ tree

  3. area

    The default Innodb storage engine page size is 16KB, that is, a total of 64 consecutive pages. Innodb has introduced compressed pages in later versions. A page may only be 9K, but a region is 1M regardless of the page size

  4. page

    A page is the smallest unit of disk management. The default page size is 16kb. Common page types are

    • Data page
    • Undo pp.
    • The system page
    • Transaction page
    • Insert buffer bitmap page
    • Insert buffer free list page

  5. line

    Innodb storage engine is column-oriented. Data is stored in rows, and pages contain row by row data. Innodb storage engine provides two formats for storing navigation records, Compact and Redundant. Oracle varchar2 can store up to 4000 bytes, mysql varchar can store up to 65535 bytes, but we know that a page is 16kb, that is, the maximum should be 16384 bytes, so how to store 65535 bytes? Normally innoDB data is stored in a B-tree node. However, when row overflow occurs, innoDB data is stored in an Uncompress BLOB page

    In addition, the length of varchar (N) we define is actually the length of the character, not the number of bytes. And 65535 is actually a row of bytes that cannot exceed this value. When a row overflow occurs, innoDB will eventually convert the vARCHAR type to text, even though we created the vARCHAR type