Algumas coisas para esclarecer:
-
^@
é a notação de cursor para NUL , não para o feed de linha, que é^J
. - Uma maneira melhor do que
cat -A
exibir arquivos binários éhexdump -C
.
Aqui está um arquivo .MYI de amostra impresso com 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
Deste exemplo, você pode ver mais claramente o padrão de bytes NUL. Como o arquivo é estruturado é definido no Manual Interno do MySQL » Mecanismo de Armazenamento MyISAM » O arquivo .MYI .
Existem quatro secções:
- state - Ocorre uma vez no início do arquivo
- base - ocorre uma vez após o estado
- keydef - ocorre uma vez para cada chave
- recinfo - Ocorre uma vez para cada campo
Onde a seção base começa é definida pelo valor de base_pos
no estado , que é de dois bytes a partir de 0xd
. No exemplo acima, o valor é 0x00c4
, o que significa que base inicia 196 bytes na posição 5 th na linha 000000c0
.
Você pode encontrar os ponteiros da página de manual para determinar exatamente por que o arquivo .MYI é estruturado da maneira que é.
Para responder às suas perguntas:
Why so many
line feeds[NUL bytes] in a mysql file?
Muitos dos bytes NUL são apenas membros da estrutura de dados que possuem valores baixos, da mesma forma que um pequeno inteiro de 32 bits como 1
pode ser armazenado como 00 00 00 01
.
is it a neccesary to insert so many
line feed[NUL bytes] in a mysql file?
Sim. Dê uma olhada no state->state.records
por exemplo. Esse é um número de 64 bits que explica as 2 0000001c
), eu só tenho 6 linhas nesta tabela ( 00 00 00 00 00 00 00 06
), mas eu teria 18.446.744.073.709.551.616 se cada byte do membro fosse 0xff
( ff ff ff ff ff ff ff ff
).
Esses zeros "filler" são necessários para distribuir o tamanho do membro struct state->state.records
, bem como de todos os outros membros. Cada membro e seu tamanho são reproduzidos na seção "Recursos adicionais" abaixo.
Recursos adicionais
Estrutura do "estado" de acordo com o Manual Interno do MySQL
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)
Estrutura de "base" de acordo com o Manual Interno do MySQL
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