Myisam 儲存引擎-MYI索引檔案-1

-- Myisam 儲存引擎的索引檔案.MYI -1

1. 介紹

.MYI檔案是MYISAM表的索引檔案,它一般與.MYD檔案同時建立,並存放在相同的目錄中,.

.MYI索引檔案主要包括兩個部分: The header information and The key values.

下面我們將從這兩個部分介紹索引檔案.

2. The .MYI Header

".MYI Header "主要包括以下幾塊內容:

Section Occurrences
------- -----------
state Occurs 1 time
base Occurs 1 time
keydef (including keysegs) Occurs once for each key
recinfo Occurs once for each field

為了能更清楚地為大家講述這個章節,下面我們先來建立一個例子:
例-2.0 :
DROP TABLE IF EXISTS heyf;
CREATE TABLE heyf (S1 CHAR(1), S2 CHAR(2), S3 CHAR(3)) TYPE MYISAM DEFAULT CHARSET=latin1;
CREATE UNIQUE INDEX I1 ON heyf (S1);
CREATE INDEX I2 ON heyf (S2,S3);
INSERT INTO heyf VALUES ('1', 'aa', 'b');
INSERT INTO heyf VALUES ('2', 'aa', 'bb');
INSERT INTO heyf VALUES ('3', 'aa', 'bbb');
DELETE FROM heyf WHERE S1 = '2';

隨後,我們用HEXDUMP工具,將T.MYI檔案以十六進位制形式列印出來:
*****************************************************
0000000 fefe 0107 0200 a201 b000 6400 d400 0300
0000010 0000 0002 0108 0000 0100 ff39 0000 0000
0000020 0000 0200 0000 0000 0000 0100 0000 0000
0000030 0000 0300 0000 0000 0000 0700 0000 0000
0000040 0000 000c 0000 0000 0000 1500 0000 0000
0000050 0000 0700 0000 0000 0000 0000 0000 0000
0000060 0000 0000 0000 0000 0000 0000 0000 5e3c
0000070 0000 3b00 0000 0000 0000 0400 0000 0000
0000080 0000 0004 0000 0000 0000 0008 ffff ffff
0000090 ffff ffff 0000 0000 0000 0000 b649 5710
00000a0 0000 0000 0000 0300 0000 0000 b649 5710
00000b0 0000 0000 0000 0000 0000 0000 b649 5710
00000c0 0000 0000 0000 0000 0000 0000 0000 0000
00000d0 0000 0000 0000 0000 0000 0004 0000 0000
00000e0 0000 0000 0000 0000 0000 0000 0000 0000
*
0000100 0000 0700 0000 0700 0000 0700 0000 0700
0000110 0000 1400 0000 0400 0000 0000 0606 0002
0000120 0000 0000 0004 1800 0000 0000 0000 0000
0000130 0000 0000 0000 0000 0101 4900 0004 0800
0000140 0800 0800 0801 0002 0000 1000 0100 0000
0000150 0100 0000 0000 0102 4800 0004 0d00 0d00
0000160 0d00 0801 0004 0000 1000 0200 0000 0200
0000170 0000 0000 0801 0008 0000 1000 0300 0000
0000180 0400 0000 0000 0000 0100 0000 0000 0000
0000190 0201 0000 0000 0200 0004 0000 0000 0803
00001a0 0000 0000 0000 0000 0000 0000 0000 0000
*
0000400 1200 3101 0000 0000 0000 3301 0000 0000
0000410 0200 3301 0000 0000 0200 0000 0000 0000
0000420 0000 0000 0000 0000 0000 0000 0000 0000
*
0000800 1c00 6101 0161 2062 0020 0000 0000 0100
0000810 6161 6201 6262 0000 0000 0200 6101 0161
0000820 6262 0062 0000 0000 0002 0000 0000 0000
0000830 0000 0000 0000 0000 0000 0000 0000 0000
*
0000c00
*****************************************************

下面我們來根據實際的內容逐個來進行分析.

2.1 ".MYI Header " -- state

This section is written by mi_open.c, mi_state_info_write().

Name Size Dump From Example File Comment
-------------------- --- ---------------------- ----------------------------------------------------------------------
file_version |4 |FE FE 07 01 | from myisam_file_magic
options |2 |00 02 | HA_OPTION_COMPRESS_RECORD etc.
header_length |2 |01 A2 | this header example has 0x01A2 bytes = 419B
state_info_length |2 |00 B0 | = MI_STATE_INFO_SIZE defined in myisamdef.h
base_info_length |2 |00 64 | = MI_BASE_INFO_SIZE defined in myisamdef.h
base_pos |2 |00 D4 | = where the base section starts
key_parts |2 |00 03 | a key part is a column within a key
unique_key_parts |2 |00 00 | key-parts+unique-parts
keys |1 |02 | here are 2 keys -- I1 and I2
uniques |1 |00 | number of hash unique keys used internally in temporary tables
| | | (nothing to do with 'UNIQUE' definitions)
language |1 |08 | "language for indexes"
max_block_size |1 |01 |
fulltext_keys |1 |00 | # of fulltext keys. = 0 if version <= 4.0
not_used |1 |00 | to align to 8-byte boundary
state->open_count |2 |00 01 |
state->changed |1 |39 | set if table updated; reset when shutdown
state->sortkey |1 |FF | "sorted by this key" (not used)
state->state.records |8 |00 00 00 00 00 00 00 02 | number of actual, un-deleted, records
state->state.del |8 |00 00 00 00 00 00 00 01 | count of deleted records
state->split |8 |00 00 00 00 00 00 00 03 | count of "chunks" (e.g. records or spaces left after record deletion)
state->dellink |8 |00 00 00 00 00 00 00 07 | "Link to next removed "block". Initially =HA_OFFSET_ERROR
state->key_file_length |8 |00 00 00 00 00 00 0c 00 | 3072, = size of .MYI file.
state->data_file_length |8 |00 00 00 00 00 00 00 15 | 21 , = size of .MYD file
state->state.empty |8 |00 00 00 00 00 00 00 07 |
state->state.key_empty |8 |00 00 00 00 00 00 00 00 |
state->auto_increment |8 |00 00 00 00 00 00 00 00 |
state->checksum |8 |00 00 00 00 00 00 00 00 |
state->process |4 |00 00 3c 5e | from getpid(). process of last update
state->unique |4 |00 00 00 3B | initially = 0
state->status |4 |00 00 00 00 |
state->update_count |4 |00 00 00 04 | updated for each write lock (there were 3 inserts + 1 delete, total 4 operations)
state->key_root |8 |00 00 00 00 00 00 04 00 | offset in file where I1 keys start, can be = HA_OFFSET_ERROR,0X7C
| |00 00 00 00 00 00 08 00 | state->key_root occurs twice because there are two keys
state->key_del |8 |FF FF FF FF FF FF FF FF | delete links for keys (occurs many times if many delete links)
state->sec_index_changed |4 |00 00 00 00 | sec_index = secondary index (presumably) not currently used
state->sec_index_used |4 |00 00 00 00 | "which extra indexes are in use" not currently used
state->version |4 |49 b6 10 57 | "timestamp of create", from_unixtime(0x49b61057+0);
state->key_map |8 |00 00 00 03 | "what keys are in use"
state->create_time |8 |00 00 00 00 49 b6 10 57 | "time when database created" (actually: time when file made)
state->recover_time |8 |00 00 00 00 00 00 00 00 | "time of last recover"
state->check_time |8 |00 00 00 00 49 b6 10 57 | "time of last check"
state->rec_per_key_rows |8 |00 00 00 00 00 00 00 00 |
state->rec_per_key_parts |4 |00 00 00 00 | (key_parts = 3, so rec_per_key_parts occurs 3 times)
| |00 00 00 00 |
| |00 00 00 00 |

2.2 ".MYI Header " -- base

This section is written by mi_open.c, mi_base_info_write().
The corresponding structure in myisamdef.h is MI_BASE_INFO.

在我們的這個例子中,BASE的資料從0XD4開始(從STATE.base_pos可以看出),共100個位元組.

Name Size Dump From Example File Comment
-------------------------- --- ---------------------- -----------------------------
base->keystart |8 |00 00 00 00 00 00 04 00 |keys start at offset 1024 (0x0400),
base->max_data_file_length |8 |00 00 00 00 00 00 00 00 |determined by definition of user
base->max_key_file_length |8 |00 00 00 00 00 00 00 00 |determined by definition of user
base->records |8 |00 00 00 00 00 00 00 00 |
base->reloc |8 |00 00 00 00 00 00 00 00 |
base->mean_row_length |4 |00 00 00 00 |
base->reclength |4 |00 00 00 07 |length(s1)+length(s2)+length(s3)=7
base->pack_reclength |4 |00 00 00 07 |
base->min_pack_length |4 |00 00 00 07 |
base->max_pack_length |4 |00 00 00 07 |
base->min_block_length |4 |00 00 00 14 |
base->fields |4 |04 00 00 04 |4 fields: 3 defined, plus 1 extra
base->pack_fields |4 |00 00 00 00 |
base->rec_reflength |1 |06 |
base->key_reflength |1 |06 |
base->keys |1 |02 |was 0 at start
base->auto_key |1 |00 |
base->pack_bits |2 |00 00 |
base->blobs |2 |00 00 |
base->max_key_block_length |2 |04 00 |length of block = 1024 bytes (0x0400)
base->max_key_length |2 |00 18 |including length of pointer
base->extra_alloc_bytes |2 |00 00 |
base->extra_alloc_procent |1 |00 |
base->raid_type |1 |00 |
base->raid_chunks |2 |00 00 |
base->raid_chunksize |4 |00 00 00 00 |
[extra] i.e. filler |6 |00 00 00 00 00 00 |

2.3 ".MYI Header " -- keydef

This section is written by mi_open.c, mi_keydef_write(). The corresponding structure in myisamdef.h is MI_KEYDEF.

緊跟著BASE資訊,就是KEYDEF(鍵定義)資訊.
如果有多個索引,則KEYDEF會出現多次.
key definition --&gt 12B
keyseg --&gt 18B

Name Size Dump From Example Comment
-------------------------- --- ------------------ -----------------------------
/* key definition for I1 */
keydef->keysegs |1 |01 |there is 1 keyseg (for column S1).
keydef->key_alg |1 |01 |algorithm = Rtree or Btree
keydef->flag |2 |00 49 |HA_NOSAME + HA_SPACE_PACK_USED + HA_NULL_PART_KEY
keydef->block_length |2 |04 00 |i.e. 1024
key def->keylength |2 |00 08 |field-count+sizeof(S1) + sizeof(ROWID) --&gt 1+1+4=6
keydef->minlength |2 |00 08 |
keydef->maxlength |2 |00 08 |
/* keyseg for S1 in I1 */ | | |
keyseg->type |1 |01 |/* I1(S1) size(S1)=1, column = 1 */ = HA_KEYTYPE_TEXT
keyseg->language |1 |08 |
keyseg->null_bit |1 |02 |
keyseg->bit_start |1 |00 |
keyseg->bit_end |1 |00 |
[0] i.e. filler |1 |00 |
keyseg->flag |2 |00 10 |HA_NULL_PART +HA_PART_KEY
keyseg->length |2 |00 01 | length(S1) = 1
keyseg->start |4 |00 00 00 01 |offset in the row
keyseg->null_pos |4 |00 00 00 00 |
|
/* key definition for I2 */ |
keydef->keysegs |1 |02 |keysegs=2, for columns S2 and S3
keydef->key_alg |1 |01 |algorithm = Rtree or Btree
keydef->flag |2 |00 48 |HA_SPACE_PACK_USED + HA_NULL_PART_KEY
keydef->block_length |2 |04 00 |i.e. 1024
key def->keylength |2 |00 0d |field-count+ sizeof(all fields)+ sizeof(RID) --&gt 2+5+4=11
keydef->minlength |2 |00 0d |
keydef->maxlength |2 |00 0d |
/* keyseg for S2 in I2 */ |
keyseg->type |1 |01 |/* I2(S2) size(S2)=2, column = 2 */
keyseg->language |1 |08 |
keyseg->null_bit |1 |04 |
keyseg->bit_start |1 |00 |
keyseg->bit_end |1 |00 |
[0] i.e. filler |1 |00 |
keyseg->flag |2 |00 10 |HA_NULL_PART + HA_PART_KEY
keyseg->length |2 |00 02 |length(S2) = 2
keyseg->start |4 |00 00 00 02 |
keyseg->null_pos |4 |00 00 00 00 |
/* keyseg for S3 in I2 */ |
keyseg->type |1 |01 |/* I2(S3) size(S3)=3, column = 3 */
keyseg->language |1 |08 |
keyseg->null_bit |1 |08 |
keyseg->bit_start |1 |00 |
keyseg->bit_end |1 |00 |
[0] i.e. filler |1 |00 |
keyseg->flag |2 |00 10 |HA_NULL_PART + HA_PART_KEY
keyseg->length |2 |00 03 |length(S3) = 3
keyseg->start |4 |00 00 00 04 |
keyseg->null_pos |4 |00 00 00 00 |

2.4 ".MYI Header " -- recinfo

The recinfo section is written by mi_open.c, mi_recinfo_write(). The corresponding structure in myisamdef.h is MI_COLUMNDEF.

It appears once for each field that appears in a key,
including an extra field that appears at the start and has flags (for deletion and for null fields).

Name size Dump From Example Comment
------------------ ---- ----------------- --------------
recinfo->type |2 |00 00 |extra
recinfo->length |2 |00 01 |
recinfo->null_bit |1 |00 |
recinfo->null_pos |2 |00 00 |
| | |
recinfo->type |2 |00 00 |I1 (S1)
recinfo->length |2 |00 01 |
recinfo->null_bit |1 |02 |
recinfo->null_pos |2 |00 00 |
| | |
recinfo->type |2 |00 00 |I2 (S2)
recinfo->length |2 |00 02 |
recinfo->null_bit |1 |04 |
recinfo->null_pos |2 |00 00 |
| | |
recinfo->type |2 |00 00 |I2 (S3)
recinfo->length |2 |00 03 |
recinfo->null_bit |1 |08 |
recinfo->null_pos |2 |00 00 |

2.5 End of the header.

We are now at offset 0x1A2 within the file T.MYI.
Notice that the value of the third field in the header, header_length, is 0x1A2.
Anything following this point, up till the first key value, is filler.

3. The .MYI Key Values

And now we look at the part which is not the information header:
we look at the key values. The key values are in blocks (MySQL''s term for pages).
A block contains values from only one index.

To continue our example: there is a block for the I1 key values, and a block for the I2 key values.

According to the header information (state->key_root above),
the I1 block starts at offset 0x0400 in the file,
and the I2 block starts at offset 0x0800 in the file.

At offset 0x0400 in the file, we have this:

Name Size Dump From Example File Comment
-------------------- ---- ---------------------- -------
(block header) |2 |00 12 | = FLAG + size (第1位FLAG,0=>葉子,1=>枝)

| | | (first bit of word = 0 meaning this is a B-Tree leaf, see the mi_test_if_nod macro)
(first key value) |2 |01 31 | Value is "1" (0x31).
(first key pointer) |2-8 |00 00 00 00 | Pointer is to Record #0000. pointer length is = base->rec_reflength
(second key value) |2 |01 33 | Value is "3" (0x33).
(second key pointer) |2-8 |00 00 00 02 | Pointer is to Record #0002.
(junk) |1010 |.. .. .. .. .. .. .. | rest of the 1024-byte block is unused

At offset 0x0800 in the file, we have this:

Name Size Dump From Example File Comment
-------------------- ---- ---------------------- -------
(block header) |2 |00 1c |= FLAG + size (第1位FLAG,0=>葉子,1=>枝)
(first key value) |7 |01 61 61 01 62 20 20 |Value is "aa/b "
(first key pointer) |2-8 |00 00 00 00 |Pointer is to Record #0000.
(second key value) |7 |01 61 61 01 62 62 62 |Value is "aa/bbb"
(second key pointer) |2-8 |00 00 00 02 |Pointer is to Record #0002.
(junk) |1000 |.. .. .. .. .. .. .. |rest of the 1024-byte block is unused

3.1 在這裡可能很多同學更關心MYSQL如何透過索引快速訪問到表資料的.
Key Pointer:
1) For fixed-row tables:
The pointer is a fixed-size (4-byte) number which contains an ordinal row number.
The first row is Record #0000.

2) For dynamic-row tables:
The pointer is an offset in the .MYD file.

3.2 索引塊的大小
The normal block length is 0x0400 (1024) bytes.

3.3 一些其它的資訊

These facts are not illustrated, but are also clear:
-- If a key value is NULL, then the first byte is 0x00 (instead of 001 as in the preceding examples) and that's all.
Even for a fixed CHAR(3) column, the size of the key value is only 1 byte.

-- Initially the junk at the end of a block is filler bytes, value = A5.
If MySQL shifts key values up after a DELETE, the end of the block is not overwritten.

-- A normal block is at least 65% full, and typically 80% full.
(This is somewhat denser than the typical B-tree algorithm would cause,
it is thus because "myisamchk -rq" will make blocks nearly 100% full.)

-- There is a pool of free blocks, which increases in size when deletions occur.
If all blocks have the same normal block length (1024), then MySQL will always use the same pool.

-- The maximum number of keys is 32 (MI_MAX_KEY).
The maximum number of segments in a key is 16 (MI_MAX_KEY_SEG).
The maximum key length is 500 (MI_MAX_KEY_LENGTH).
The maximum block length is 16384 (MI_MAX_KEY_BLOCK_LENGTH).
All these MI_... constants are expressed by #defines in the myisamdef.h file.