Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

What is the arrangement of MySQL8.0 inverted index data?

2025-01-17 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)05/31 Report--

The main content of this article is to explain "what is the arrangement of MySQL8.0 reverse index data". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "what is the data arrangement of MySQL8.0 reverse indexing data?"

1. Prepare data mysql > create table tab_desc-> (id1 int,-> id2 int,-> key (id1),-> key (id2 desc)); Query OK, 0 rows affected (1.29 sec) mysql > select * from tab_desc +-+-+ | id1 | id2 | +-+-+ | 1 | 1 | 2 | 2 | 3 | 3 | 4 | 5 | 5 | 6 | 6 | 7 | 7 | +-+-2. Prove through the execution plan

This is relatively simple, and we use using index type index to access the index and find that they are indeed the opposite.

Mysql > desc select id2 from tab_desc +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + -+ | 1 | SIMPLE | tab_desc | NULL | index | NULL | id2 | 5 | NULL | 7 | 100.00 | Using index | +- -+ 1 row in set 1 warning (0.11 sec) mysql > select id2 from tab_desc +-+ | id2 | +-+ | 7 | | 6 | 5 | 4 | | 3 | 2 | | 1 | +-+ 7 rows in set (0.00 sec) mysql > desc select id1 from tab_desc +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + -+ | 1 | SIMPLE | tab_desc | NULL | index | NULL | id1 | 5 | NULL | 7 | 100.00 | Using index | +- -+ 1 row in set 1 warning (0.00 sec) mysql > select id1 from tab_desc +-+ | id1 | +-+ | 1 | | 2 | 3 | 4 | | 5 | 6 | 7 | +-+ 7 rows in set (0.00 sec) 3. Prove the execution through the tool. / innblock tab_desc.ibd scan 16 get the result = INDEX_ID:136level0 total block is (1) block_no: 4 block_no level: 0 | * | = = INDEX_ID:137level0 total block is (1) block_no: 5 Level: 0 | * | = = INDEX_ID:138level0 total block is (1) block_no: 6 Level: 0 | * | through INNODB_INDEXES, we can see that the ID corresponding to the two indexes is indeed 137Candle 138 | 136 | GEN_CLUST_INDEX | 1059 | 1 | 5 | 4 | 2 | 50 | 1059 | 1059 | id1 | 1059 | 2 | 5 | 2 | 50 | 138 | id2 | | 1059 | 0 | 2 | 6 | 2 | 50 | their logical list information can be obtained through commands. / innblock tab_desc.ibd 516 and. / innblock tab_desc.ibd 616: id1==== Block list info =-Total used rows:9 used rows list (logic): (1) INFIMUM record offset:99 heapno:0 n_owned 1 | Delflag:N minflag:0 rectype:2 (2) normal record offset:126 heapno:2 n_owned 0Magnum deluxe minflag:0 rectype:0 N minflag:0 rectype:0 (3) normal record offset:142 heapno:3 n_owned 0Personnal deluxe minflag:0 rectype:0 N minflag:0 rectype:0 (4) normal record offset:158 heapno:4 n_owned 0Different deluxe minflag:0 rectype:0 N minflag:0 rectype:0 (5) normal record offset:174 heapno:5 n_owned 0Magnum deliciosa N minflag:0 rectype:0 (6) normal record offset:190 heapno:6 n_owned 0 Delflag:N minflag:0 rectype:0 (7) normal record offset:206 heapno:7 n_owned 0Magnum deluxe minflag:0 rectype:0 N minflag:0 rectype:0 (8) normal record offset:222 heapno:8 n_owned 0Magnum delta Velcro N minflag:0 rectype:0 (9) SUPREMUM record offset:112 heapno:1 n_owned 8Die deluxe Vega N minflag:0 rectype:3id2==== Block list info = =-Total used rows:9 used rows list (logic): (1) INFIMUM record offset:99 heapno:0 n_owned 1 Delflag:N minflag:0 rectype:2 (2) normal record offset:222 heapno:8 n_owned 0Magnum deluxe minflag:0 rectype:0 N minflag:0 rectype:0 (3) normal record offset:206 heapno:7 n_owned 0Personnal deluxe minflag:0 rectype:0 N minflag:0 rectype:0 (4) normal record offset:190 heapno:6 n_owned 0Different deluxe minflag:0 rectype:0 N minflag:0 rectype:0 (5) normal record offset:174 heapno:5 n_owned 0Magnum deliciosa N minflag:0 rectype:0 (6) normal record offset:158 heapno:4 n_owned 0 Delflag:N minflag:0 rectype:0 (7) normal record offset:142 heapno:3 n_owned 0 deluxe minflag:0 rectype:0 N minflag:0 rectype:0 (8) normal record offset:126 heapno:2 n_owned 0 Delphi minflag:0 rectype:0 N minflag:0 rectype:0 (9) SUPREMUM record offset:112 heapno:1 n_owned 8 de delta lux N minflag:0 rectype:3

We can see that the logical linked list information of the ID1 general index is:

INFIMUM-> 126-> 142-> 158.-> SUPREMUM

Our reverse index logical linked list information is:

INFIMUM-> 222-> 206-> 190.-> SUPREMUM

So let's interpret the data separately, because the data field arrangement of a general index is: data + primary key, and int represents 4 bytes.

The data of id1 is (bcview, a tool of our own, is used here for easy observation. Of course, if you have to do it with the naked eye, you can also use hexdump):

The 4 bytes after the first line is: 80000001

Current block:00000005--Offset:00126--cnt bytes:04--data is:80000001

4 bytes after the second line: 80000002

Current block:00000005--Offset:00142--cnt bytes:04--data is:80000002

4 bytes after 158bytes of the third line: 80000003

Current block:00000005--Offset:00158--cnt bytes:04--data is:80000003

Line 4: 4 bytes after 174bytes: 80000004

Current block:00000005--Offset:00174--cnt bytes:04--data is:80000004

I will not query the back, I can see that it is from small to big.

Next, let's decompose the data of the inverted index:

The 4 bytes after the first line is: 80000007

Current block:00000006--Offset:00222--cnt bytes:04--data is:80000007

4 bytes after 206bytes of the second line: 80000006

Current block:00000006--Offset:00206--cnt bytes:04--data is:80000006

4 bytes after 190 bytes of the third line: 80000005

Current block:00000006--Offset:00190--cnt bytes:04--data is:80000005

Line 4: 4 bytes after 174bytes: 80000004

Current block:00000006--Offset:00174--cnt bytes:04--data is:80000004

Therefore, we have verified that for inverted indexes, the data is sorted in INFIMUM and SUPREMUM descending order.

At this point, I believe you have a deeper understanding of "what is the data arrangement of MySQL8.0 reverse index data?" you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report