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

In this issue, the editor will bring you about how to use mysql innodb to view the index height. The article is rich in content and analyzed and described from a professional point of view. I hope you can get something after reading this article.

First get the index on the table

Mysql > SELECT b.name, a.name, index_id, type, a.space, a.PAGE_NO FROM information_schema.INNODB_SYS_INDEXES a, information_schema.INNODB_SYS_TABLES b WHERE a.table_id = b.table_id AND a.space 0 and b. +-+ | name | name | index_id | type | space | PAGE_NO | +-+-+ -+ | ming/test02 | PRIMARY | 71 | 3 | 44 | 3 | | ming/test02 | idx_test02_c2 | 400 | 0 | 44 | 39 | +- -+ 2 rows in set (0.85 sec)

Type:

0 is a non-unique secondary index.

3 is a clustering index.

1 is automatically generated clustered index (GEN_CLUST_INDEX)

2 is the only non-clustered index of unique nonclustered index;

32 is a full-text index

Query the size of the innodb page

Mysql > show global variables like 'innodb_page_size';+-+-+ | Variable_name | Value | +-+-+ | innodb_page_size | 16384 | +-+-+ 1 row in set (0.67 sec)

Next, use the operating system command hexdump to view the file in hexadecimal

Grammar

Hexdump [options] [files]...

Option

-n length formats only the first length bytes of the input file. -C outputs hexadecimal and ASCII codes of the specification. -b single-byte octal display. -c single byte character display. -d double-byte decimal display. -o double-byte octal display. -x double byte hexadecimal display. -s outputs from the offset. -e specifies the format string, which is contained in a pair of single quotation marks, such as:'a format1 b', "format2"'.

View index height

[root@mdb01 ming] # hexdump-s 49216-n 10. / test02.ibd000c040 0200 0000 0000 0000 4700 000c04a [root@mdb01 ming] # hexdump-s 639040-n 10. / test02.ibd 009c040 0200 0000 0000 0000 9001 009c04a

The formula for calculating the specified offset is page_no * innodb_page_size + 64.

49216 = 3 * 16384 + 64.

The value of PAGE_LEVEL is 0200, which means that the height of the secondary index tree is 3 (2-1).

The next 4700 and 9001 are the index_id of the index.

Convert hexadecimal to decimal on the operating system:

[root@mdb01 ming] # echo $((0x47)) 71 [root@mdb01 ming] # echo $((0x0190)) 400

The reading order of 9001 should be read backwards in groups of two, so it is 01 90.

The above is the editor for you to share how to use mysql innodb to view the index height, if you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.