innodb index page format

Today I had to decode an innodb index page, so I documented the entire process here:

E:\mysql-enterprise-gpl-5.0.66a-winx64\bin>mysqld-nt --console --skip-grant-tables --skip-name-resolve
InnoDB: The first specified data file .\ibdata1 did not exist:
InnoDB: a new database to be created!
080919 14:29:00  InnoDB: Setting file .\ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
080919 14:29:00  InnoDB: Log file .\ib_logfile0 did not exist: new to be created
InnoDB: Setting log file .\ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
080919 14:29:01  InnoDB: Log file .\ib_logfile1 did not exist: new to be created
InnoDB: Setting log file .\ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
080919 14:29:01  InnoDB: Started; log sequence number 0 0
080919 14:29:01 [Note] mysqld-nt: ready for connections.
Version: '5.0.66a-enterprise-gpl-nt'  socket: ''  port: 3306  MySQL Enterprise Server (GPL)


create table t1(a varchar(20) primary key, b varchar(20), c varchar(20),key(b),key(c,b))engine=innodb;
insert into t1(a,b,c) values ('aaaa','bbbbb','cccccc');
insert into t1(a,b,c) values ('aaaaaaa',null,'ccc');
insert into t1(a,b,c) values ('a','b',null);
insert into t1(a,b,c) values ('aaaaaaaaaa','bbb','c');



080919 14:37:59 [Note] mysqld-nt: Normal shutdown

080919 14:37:59  InnoDB: Starting shutdown...
080919 14:38:14  InnoDB: Shutdown completed; log sequence number 0 48536
080919 14:38:14 [Note] mysqld-nt: Shutdown complete



we have secondary indexes on this table

this is key(c,b) (directly from ibdata1):


000d0000h: 35 56 71 04 00 00 00 34 FF FF FF FF FF FF FF FF ; 5Vq....4ÿÿÿÿÿÿÿÿ
000d0010h: 00 00 00 00 00 00 BD 92 45 BF 00 00 00 00 00 00 ; ......½’E¿......
000d0020h: 00 00 00 00 00 00 00 02 00 C3 80 06 00 00 00 00 ; .........À.....
000d0030h: 00 B5 00 05 00 00 00 04 00 00 00 00 00 00 03 05 ; .µ..............
000d0040h: 00 00 00 00 00 00 00 00 00 11 00 00 00 00 00 00 ; ................
000d0050h: 00 02 15 F2 00 00 00 00 00 00 00 02 15 32 01 00 ; ...ò.........2..
000d0060h: 02 00 47 69 6E 66 69 6D 75 6D 00 05 00 0B 00 00 ; ..Ginfimum......
000d0070h: 73 75 70 72 65 6D 75 6D 04 05 06 00 00 00 10 FF ; supremum.......ÿ
000d0080h: EF 63 63 63 63 63 63 62 62 62 62 62 61 61 61 61 ; ïccccccbbbbbaaaa
000d0090h: 07 03 02 00 00 18 FF E9 63 63 63 61 61 61 61 61 ; ......ÿécccaaaaa
000d00a0h: 61 61 01 01 01 00 00 20 00 0B 62 61 0A 03 01 00 ; aa..... ..ba....
000d00b0h: 00 00 28 FF E3 63 62 62 62 61 61 61 61 61 61 61 ; ..(ÿãcbbbaaaaaaa
000d00c0h: 61 61 61 00 00 00 00 00 00 00 00 00 00 00 00 00 ; aaa.............




Let's reformat this page into the correct fields as seen by InnoDB:

0000: 35567104           -> FIL_PAGE_SPACE_OR_CHKSUM
0004: 00000034           -> FIL_PAGE_OFFSET                                    
0008: FFFFFFFF           -> FIL_PAGE_PREV                                      
0012: FFFFFFFF           -> FIL_PAGE_NEXT                                       
0016: 000000000000BD92   -> FIL_PAGE_LSN
0024: 45BF               -> FIL_PAGE_TYPE  (#define FIL_PAGE_INDEX 17855)
0026: 0000000000000000   -> FIL_PAGE_FILE_FLUSH_LSN
0034: 00000000           -> FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID

0038: 0002               -> PAGE_N_DIR_SLOTS
0040: 00C3               -> PAGE_HEAP_TOP   (195 ...)
0042: 8006               -> PAGE_N_HEAP  (6 records in heap (remove 15th bit)
0044: 0000               -> PAGE_FREE
0046: 0000               -> PAGE_GARBAGE
0048: 00B5               -> PAGE_LAST_INSERT
0050: 0005               -> PAGE_DIRECTION  (PAGE_NO_DIRECTION)
0052: 0000               -> PAGE_N_DIRECTION
0054: 0004               -> PAGE_N_RECS
0056: 0000000000000305   -> PAGE_MAX_TRX_ID (773)
0064: 0000               -> PAGE_LEVEL
0066: 0000000000000011     -> PAGE_INDEX_ID ( Page may be an index page where index id is 0 277385)
0074: 000000000000000215F2 -> PAGE_BTR_SEG_LEAF
0084: 00000000000000021532 -> PAGE_BTR_SEG_TOP

infimum:
0094: 01                -> info_bits=0, n_owned=1 (always 1 for the infimum)
0095: 00                -> heap number
0096: 02                -> status bits
0097: 0047              -> next record (71 bytes)
0099: 696E66696D756D00  -> "infimum"

supremum:
0107: 05000b            -> extra bytes
0110: 0000              -> next record, zero since supremum is always last
0112: 73757072656D756D  -> "supremum"

index row1:
0120: 040506            -> field offsets, starting with the last field.
0123: 00000010          -> extra bytes
0127: FFEF              -> offset to next record (17 bytes back (offset 112))
0129: 636363636363      -> 'cccccc' (keypart1)
0135: 6262626262        -> 'bbbbb'  (keypart2)
0140: 61616161          -> 'aaaa'   (primary key appended)

index row2:
0144: 0703              -> field lengths, starting with the last field (excluding null!).
0146: 02000018          -> extra bytes
0150: FFE9              -> offset to next record (23 bytes back (offset 129))
0152: 636363            -> 'ccc' (keypart1)
0155: 61616161616161    -> 'aaaaaaa' (primary key appended)

index row3:
0162: 0101              -> field lengths, starting with the last field (excluding null!).
0164: 01000020          -> extra bytes
0168: 000B              -> offset to next record (11 bytes (offset 181))
0170: 62                -> 'b' (keypart2)
0171: 61                -> 'a' (primary key appended)

index row4:
0172: 0A0301            -> field lengths
0175: 00000028          -> extra bytes
0179: FFE3              -> offset to next record (29 bytes back (offset 152!))
0181: 63                -> 'c' (keypart1)
0182: 626262            -> 'bbb' (keypart2)
0185: 61616161616161616161 -> 'aaaaaaaaaa' (primary key appended)

explanation of "extra bytes" still to be done