Ein paar Dinge zu klären:
^@
ist die Caret-Notation für NUL, nicht für Zeilenvorschub^J
.- Eine bessere Möglichkeit als
cat -A
das Anzeigen von Binärdateien isthexdump -C
.
Hier ist eine .MYI-Beispieldatei mit hexdump -C
:
root@host1 [/var/lib/mysql/deltik_main]# hexdump -C nodes.MYI 00000000 fe fe 07 01 00 01 01 8c 00 b0 00 64 00 c4 00 01 |...........d....| 00000010 00 00 01 00 08 01 00 00 00 00 00 ff 00 00 00 00 |................| 00000020 00 00 00 06 00 00 00 00 00 00 00 00 00 00 00 00 |................| 00000030 00 00 00 06 ff ff ff ff ff ff ff ff 00 00 00 00 |................| 00000040 00 00 08 00 00 00 00 00 00 00 03 14 00 00 00 00 |................| 00000050 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| 00000060 00 00 00 06 00 00 00 00 6d c0 14 47 00 00 05 85 |........m..G....| 00000070 00 00 01 d5 00 00 00 00 00 00 00 03 00 00 00 00 |................| 00000080 00 00 04 00 ff ff ff ff ff ff ff ff 00 00 00 00 |................| 00000090 00 00 00 00 5a f0 11 99 00 00 00 00 00 00 00 01 |....Z...........| 000000a0 00 00 00 00 5a 85 b3 8e 00 00 00 00 00 00 00 00 |....Z...........| 000000b0 00 00 00 00 5a f0 11 99 00 00 00 00 00 00 00 06 |....Z...........| 000000c0 00 00 00 01 00 00 00 00 00 00 04 00 00 00 00 00 |................| 000000d0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| * 000000f0 00 00 0a e0 00 00 0a e5 00 00 00 08 80 00 0a ef |................| 00000100 00 00 00 14 00 00 00 0a 00 00 00 03 06 05 01 01 |................| 00000110 00 01 00 01 04 00 00 10 00 00 00 00 00 00 00 00 |................| 00000120 00 00 00 00 00 00 00 00 01 01 00 11 04 00 00 0a |................| 00000130 00 0a 00 0a 04 3f 00 00 00 00 00 40 00 04 00 00 |.....?.....@....| 00000140 00 00 00 00 00 00 00 03 00 04 00 00 00 00 03 00 |................| 00000150 04 00 00 00 00 08 00 81 00 00 00 00 08 01 02 00 |................| 00000160 00 00 00 04 00 0c 00 00 00 00 08 00 41 00 00 00 |............A...| 00000170 00 08 02 02 00 00 00 00 08 02 02 00 00 00 00 08 |................| 00000180 01 02 00 00 00 00 08 04 02 00 00 00 00 00 00 00 |................| 00000190 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| * 00000400 00 3e 00 00 00 01 00 00 00 00 00 00 00 00 00 02 |.>..............| 00000410 00 00 00 00 00 8c 00 00 00 03 00 00 00 00 01 1c |................| 00000420 00 00 00 04 00 00 00 00 02 40 00 00 00 05 00 00 |.........@......| 00000430 00 00 01 a8 00 00 00 06 00 00 00 00 02 e0 00 00 |................| 00000440 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| * 00000800
In diesem Beispiel können Sie das Muster der NUL-Bytes deutlicher erkennen. Wie die Datei strukturiert ist, wird im MySQL Internals Manual » MyISAM Storage Engine » Die .MYI-Datei definiert .
Es gibt vier Abschnitte:
- state - Tritt einmal am Anfang der Datei auf
- base - Tritt einmal nach dem Zustand auf
- keydef - Tritt einmal für jeden Schlüssel auf
- recinfo - Tritt einmal für jedes Feld auf
Wo der Basisabschnitt beginnt, wird durch den Wert von base_pos
in state definiert, der zwei Bytes beträgt, beginnend mit 0xd
. In dem obigen Beispiel ist der Wert 0x00c4
, was bedeutet, dass Basis 196 Bytes oder 5 beginnt ten Position in der 000000c0
Reihe.
Sie können die Zeiger auf der Manpage finden, um genau zu bestimmen, warum die .MYI-Datei so strukturiert ist, wie sie ist.
Um Ihre Fragen zu beantworten:
Warum so viele
Zeilenvorschübe[NUL-Bytes] in einer MySQL-Datei?
Viele der NUL-Bytes sind nur Datenstruktur-Member mit niedrigen Werten, ähnlich wie eine kleine 32-Bit-Ganzzahl wie 1
gespeichert werden kann 00 00 00 01
.
Ist es notwendig, so viele
Zeilenvorschübe[NUL-Bytes] in eine MySQL-Datei einzufügen ?
Ja. Schauen Sie sich state->state.records
zum Beispiel an. Dies ist eine 64-Bit-Zahl, die die von MyISAM unterstützten maximal 2 64 Zeilen erklärt . In meinem Beispiel (beginnend bei 0000001c
) habe ich nur 6 Zeilen in dieser Tabelle ( 00 00 00 00 00 00 00 06
), aber ich hätte 18.446.744.073.709.551.616, wenn 0xff
stattdessen jedes Byte des Members ( ff ff ff ff ff ff ff ff
) wäre.
Diese "Füllzeichen" -Nullen werden benötigt, um die Größe des Strukturelements state->state.records
sowie aller anderen Elemente zu trennen. Jedes Mitglied und seine Größe werden im Abschnitt "Zusätzliche Ressourcen" unten dargestellt.
Zusätzliche Ressourcen
Struktur des "Zustands" gemäß dem MySQL Internals Manual
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 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 if shutdown (so one can examine this to see if there was an update without proper 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 # of deleted records state->split 8 00 00 00 00 00 00 00 03 # 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->state.key_file_length 8 00 00 00 00 00 00 0c 00 2048 state->state.data_file_length 8 00 00 00 00 00 00 00 15 = size of .MYD file state->state.empty 8 00 00 00 00 00 00 00 00 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 09 E6 from getpid(). process of last update state->unique 4 00 00 00 0B 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 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 3F 3F EB F7 "timestamp of create" state->key_map 8 00 00 00 03 "what keys are in use" state->create_time 8 00 00 00 00 3F 3F EB F7 "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 3F 3F EB F7 "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 00 00 00 00 rec_per_key_parts 00 00 00 00 occurs 3 times)
Struktur der "Basis" gemäß dem MySQL Internals Manual
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 base->max_key_file_length 8 00 00 00 00 00 00 00 00 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 00 00 00 04 4 fields: 3 defined, plus 1 extra base->pack_fields 4 00 00 00 00 base->rec_reflength 1 04 base->key_reflength 1 04 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 10 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] that is, filler 6 00 00 00 00 00 00