preface

Front has written two chapters in the length of the article, the third I have been thinking about what to write (not), according to the diagram to brain, this article we should explain about the index of knowledge, this is a MySQL performance optimization is the key of knowledge points, don’t miss, but I will be here is relatively in-depth exploration, I believe you will have some harvest after reading.

MySQL > update MySQL > update MySQL > update MySQL > update MySQL > update MySQL > update MySQL > update MySQL > update MySQL > update MySQL

Because the index knowledge is more, the content of the official website is also a lot, if you want to know more about it, you can go to the official website. If you want to read through it first, you can see my summary of the index. This chapter is divided into three parts:

1. Innodb logical storage structure needs to be understood as MySQL related (external) – InnoDB logical storage structure;
2. MySQL > query data model; MySQL > query data model; MySQL > query data model;
3. The use of indexes and optimization rules will also be included as a separate chapter for MySQL related (iv) – key indexes for performance optimization

The brain map mentioned above is as follows. If you want a complete hd picture, you can go to my wechat official account [6 Xixuanxin] and reply to MySQL to obtain the brain map:

The body of the

Innodb logical storage structure

Dev.mysql.com/doc/refman/… Dev.mysql.com/doc/refman/…

The storage structure of MySQL is divided into five levels: table Spaces, segments, clusters, pages, and rows.

Tablespace Table Space

As mentioned in the last article on disk structure, table Spaces can be considered the highest logical structure of InnoDB storage engine. All data is stored in table Spaces. There are system tablespaces, exclusive tablespaces, general tablespaces, temporary tablespaces, and Undo tablespaces.

Section of the Segment

A table space is composed of various segments, such as data segment, index segment, and rollback segment. Segment is a logical concept. An IBD file (independent table space file) can consist of many segments.

Create an index segment (leaf node segment); create an index segment (non-leaf node segment). Index segments manage data for non-leaf nodes. The data segment manages the data of the leaf node.

In other words, the number of segments in a table is the number of indexes multiplied by 2.

Cluster among

A Segment consists of a number of segments, each of which is 1MB (64 contiguous pages) in size.

Each segment has at least one cluster. The space managed by a segment is infinite and can be extended forever, but the smallest unit of expansion is the cluster.

Page page

To manage the physical space efficiently, further subdivide the cluster and you get pages. A cluster is a space made up of contiguous pages, with 64 contiguous pages in a cluster. (1MB / 16KB=64). These pages are physically and logically contiguous.

Like most databases, InnoDB has the concept of pages (also known as blocks), with each page defaulting to 16KB.

A page is the smallest unit of disk management for the InnoDB storage engine, set through Innodb_page_size.

A table space can have a maximum of 2^32 pages. By default, the size of a page is 16KB, meaning that a table space can store a maximum of 64TB of data.

Note that in file systems, there is also the concept of pages.

Operating systems deal with memory, the smallest unit of which is a Page. File system memory pages are typically 4K.

SHOW VARIABLES LIKE 'innodb_page_size';
Copy the code

Assuming that one row is 1K, a data page can hold 16 rows of such data.

Example: Three rows of data on a page.

If a page has been written while inserting data into a table, a new leaf page is created. If all pages in a cluster are used up, a new cluster is allocated from the current page segment.

If the data is not contiguous, inserting data into an already full page will cause the leaf page to split:

Row Row (for understanding only)

InnoDB storage engine is row-oriented, which means data is stored row by row.

Dev.mysql.com/doc/refman/…

As a built-in file format, InnoDB has two row formats:

REDUNDANT [r ɪ ˈ d ʌ nd goes nt] Row Format

COMPACT Row Format (5.6 Default)

Barracuda is a file format supported by InnoDB Plugin. Two new line formats are added:

DYNAMIC Row Format (5.7 Default) COMPRESSED Row Format

Innodb_file_format is specified in the configuration file; Row_format is specified when the data table is created.

show variables like "%innodb_file_format%"; SET GLOBAL innodb_file_format=Barracuda;
Copy the code

Row formats can be specified when creating a table.

CREATE TABLE tf1 (c1 INT PRIMARY KEY) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
Copy the code

View the line format:

SHOW TABLE STATUS LIKE 'student' \G;
Copy the code

MySQL > MySQL > index data model and B+Tree

By the way

There is a problem? Can you leave me a message or chat privately? Just give it a thumbs up

Of course, you can also go to my official account “6 Xi Xuan”,

Reply to “Learn” and receive a copy of the Video tutorial for Advanced Architects for Java Engineers

Answer “interview”, can obtain:

MySQL brain Map MySQL brain map

Because, I trained programmers, PHP, Android and hardware are done, but in the end or choose to focus on Java, so have what questions to ask the public for discussion (emotional pouring technology can ha ha ha), see words will reply as soon as possible, hope can with everyone common learning progress, on the server architecture, Java core knowledge analysis, career, interview summary and other articles will be pushed irregularly output, welcome to pay attention to ~~~