Index organization table

In InnoDB, tables are Organized according to the order of primary keys, which is called Index Organized Tables.

In InnoDB tables, each table has a primary key. If a primary key is NOT specified when the table is created, the primary key is selected or created as follows: 1) First, determine whether there is a Unique and NOT NUll index in the table, if there is, then specify it as a primary key. 2) InnoDB automatically creates a 6-byte pointer if it does not.

If there are multiple unique and non-empty indexes in the table, the first created unique and non-empty index is selected as the primary key.

_rowid can be used to view the primary key of the current table:

But a joint primary key for multiple fields is not valid.

InnoDB logical storage structure

As can be seen from the figure above, all data is stored in a logical space, called a tablespace. A tablespace consists of segments, extents, and pages.

2.1 the table space

The InnoDB storage engine has a shared tablespace idATA1. When innodb_file_per_table is enabled, each table has its own independent tablespace.

Separate table Spaces only store data, indexes, and insert buffer BitMap pages. Other data, such as rollback information, insert buffer index pages, system transaction information, and secondary write buffers, are still stored in shared table Spaces.

Paragraph 2.2

You can see in the figure above that table Spaces are made up of segments. Common segments are data segment, index segment, rollback segment and so on. Since InnoDB organizes tables based on indexes, its data segments are Leaf node segments of B+ tree, and indexes are non-leaf node seinterfaces. The rollback section is described in more detail later. Segments are controlled by the storage engine itself and do not require manual operation.

2.3 area

Extents are made up of contiguous pages, each of which defaults to 1M in size. To ensure the continuity of extents, InnoDB storage engine requests 4-5 extents from disk at a time. InnoBD storage engine pages are 16K in size, so there are 64 consecutive pages in each section.

Each section is 1M in size, whether you modify the size or use a compressed page.

2.4 pp.

Pages are the smallest unit of management on disk. In InnoDB, the default minimum for each page is 16K, and the size can be set to 4K, 8K, 16K, etc with the innodb_page_size parameter. Once the Settings are complete, they cannot be modified unless new libraries are created through import and export.

Common page types in InnoDB are: 1) data page (B+ tree Node) 2) System page 3) Undo page 4) transaction data page 5) Insert buffer bitmap page 6) Insert buffer free list page 7) Uncompressed binary large object page 8) compressed binary large object page

2.5 line

Data pages are stored in line, and there is a rigid requirement that the number of data lines in each page is 16K/ 2-200 lines, that is, 7992 lines.

3, partition table

Partitioning is not done at the storage engine level, so InnoDB, MyISam, NDB, etc.

In mysql5.6, you can use the following command to check whether table partitioning is enabled:

show variables like '%partition%';
Copy the code

You can use the following methods in mysql8:

mysql> select table_schema, table_name, partition_name,partition_method,partition_expression from information_schema.PARTITIONS where table_name = 'bssp_sys_menu'; +--------------+---------------+----------------+------------------+----------------------+ | TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_METHOD | PARTITION_EXPRESSION | +--------------+---------------+----------------+------------------+----------------------+ | bssp | bssp_sys_menu | NULL | NULL | NULL | +--------------+---------------+----------------+------------------+----------------------+ 1 row In the set (0.01 SEC)Copy the code

The type of partition supported by mysql is horizontal partition, and its partition is local index partition, that is, a partition stores both data and indexes.

Mysql supports several partition types: 1) RANGE partition: row data in a given RANGE of consecutive column values, blame to join the partition. 2) LIST partition: Compared to the RANGE partition type, only LIST partition faces discrete values. 3) HASH partition: Partitions are determined based on user-defined return values, which cannot be negative values. 4) KEY partition: partition according to the hash function provided by mysq.

Regardless of how partitions are created, when a primary key or unique index exists in the table, the unique index can be empty and the partitioned column must be part of it (such as a joint unique index, which contains only one column).

If the primary key and unique index are not specified, partitions can be created with any column.

Partitioned performance databases are divided into OLTP (online transaction processing application) and OLAP (online analysis application).

For OLAP, performance can be significantly improved. For example, if you have a table with hundreds of millions of columns, the user needs to constantly query them. If we partition the table by time segment, we only need to query the table data for that partition.

With OLTP, partitions need to be relatively careful. In general, there are few requirements to fetch more than 10% of the data in a table, and two or three IO’s of a B+ tree can do a good job for requests that require only a few or even a single data. Partitioning is not required. Poor design, on the other hand, can cause serious performance problems.