Big DBA Head!

In my previous Post, we took a look at the file structure of the InnoDB primary key index. With the same table structure, but a slightly different dataset:

mysql> select * from test_innodb_growth limit 10;
+----+------------+--------+
| id | data       | spacer |
+----+------------+--------+
|  1 | a          | |      |
|  2 | aa         | |      |
|  3 | aaa        | |      |
|  4 | aaaa       | |      |
|  5 | aaaaa      | |      |
|  6 | aaaaaa     | |      |
|  7 | aaaaaaa    | |      |
|  8 | aaaaaaaa   | |      |
|  9 | aaaaaaaaa  | |      |
| 10 | aaaaaaaaaa | |      |
+----+------------+--------+
10 rows in set (0.00 sec)

All the rows after id = 10 have data = ‘aaaaa’. Let’s take a look at the secondary index ‘idxdata’. I recently had an argument with a client that claimed that a varchar is fully expanded in a secondary index. The following will prove it is not the case. With the InnoDB recovery tool, we find that the ‘idxdata’ index uses 4 pages:

$ ls
11-00000011.page  12-00000012.page  4-00000004.page  8-00000008.page  9-00000009.page

The root page is ‘4-00000004.page’ and the other are leaves. The content of page 4 is shown below:

00000000  cc 57 73 7b 00 00 00 04  ff ff ff ff ff ff ff ff  |.Ws{............|
00000010  00 00 00 00 34 64 ee 9f  45 bf 00 00 00 00 00 00  |....4d..E.......|
00000020  00 00 00 00 00 27 00 02  00 c6 80 06 00 00 00 00  |.....'..........|
00000030  00 b9 00 05 00 00 00 04  00 00 00 00 00 08 77 0e  |..............w.|
00000040  00 01 00 00 00 00 00 00  00 87 00 00 00 27 00 00  |.............'..|
00000050  00 02 02 72 00 00 00 27  00 00 00 02 01 b2 01 00  |...r...'........|
00000060  02 00 1c 69 6e 66 69 6d  75 6d 00 05 00 0b 00 00  |...infimum......|
00000070  73 75 70 72 65 6d 75 6d  01 00 10 00 11 00 10 61  |supremum.......a|
00000080  00 00 00 01 00 00 00 08  05 00 00 00 19 00 2a 61  |..............*a|
00000090  61 61 61 61 00 00 01 f3  00 00 00 09 07 00 00 00  |aaaa............|
000000a0  21 ff cd 61 61 61 61 61  61 61 00 00 00 07 00 00  |!..aaaaaaa......|
000000b0  00 0b 05 00 00 00 29 ff  ea 61 61 61 61 61 00 00  |......)..aaaaa..|
000000c0  05 c5 00 00 00 0c 00 00  00 00 00 00 00 00 00 00  |................|
000000d0  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00003ff0  00 00 00 00 00 70 00 63  85 0c 62 f2 34 64 ee 9f  |.....p.c..b.4d..|
00004000

Looking at the content of ‘4-00000004.page’ we can observe 3 node entries pointing to pages 8, 9, 11 and 12. As one can note, the header of the root index page is 120 bytes (before the first value). Here, an entry in the root block (non-leaf) begins by the size of the varchar ‘01′ at position 78 followed by the 6 bytes extra flags, the actual key value (‘a’ at position 7f) and, the corresponding primary key (‘00 00 00 01′ at position 80) and the leaf page with values larger are equal than the key (‘00 00 00 08′ at position 84).

Now, let’s jump to page 8, the first leaf page. We have the following:

00000000  36 1a 97 b7 00 00 00 08  ff ff ff ff 00 00 00 09  |6...............|
00000010  00 00 00 00 34 64 ee 9f  45 bf 00 00 00 00 00 00  |....4d..E.......|
00000020  00 00 00 00 00 27 00 7d  3e 03 83 da 1f 35 1e d5  |.....'.}>....5..|
00000030  00 00 00 05 00 00 01 ec  00 00 00 00 00 08 77 0e  |..............w.|
00000040  00 00 00 00 00 00 00 00  00 87 00 00 00 00 00 00  |................|
00000050  00 00 00 00 00 00 00 00  00 00 00 00 00 00 01 00  |................|
00000060  02 00 1c 69 6e 66 69 6d  75 6d 00 01 00 0b 00 00  |...infimum......|
00000070  73 75 70 72 65 6d 75 6d  01 00 00 00 10 00 0c 61  |supremum.......a|
00000080  00 00 00 01 02 00 00 00  18 00 0d 61 61 00 00 00  |...........aa...|
00000090  02 03 00 00 00 20 00 0e  61 61 61 00 00 00 03 04  |..... ..aaa.....|
000000a0  00 04 00 28 00 0f 61 61  61 61 00 00 00 04 05 00  |...(..aaaa......|
000000b0  00 00 30 00 10 61 61 61  61 61 00 00 00 05 05 00  |..0..aaaaa......|
000000c0  00 00 38 00 10 61 61 61  61 61 00 00 00 0c 05 00  |..8..aaaaa......|
000000d0  00 00 40 00 10 61 61 61  61 61 00 00 00 0d 05 00  |..@..aaaaa......|

As one can observe, the varchar(50) column is not expanded. One can also note the presence of 6 additional bytes per row that are the extra flags. When trying to evaluate the size of an index, these 6 bytes, along with the block header size, can be important.

innodb internals, mysql, performance, yves RSS 2.0 trackback leave a response