Por que tantos feeds de linha em um arquivo mysql?

0
cat -A dfs.MYI    
M-~M-~^G^A^@^@^AT^@M-0^@d^@M-D^@^A^@^@^A^@^X^A^@^@^@^@^@M-^?^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@M-^?M-^?M-^?M-^?M-^?M-^?M-^?M-^?^@^@^@^@^@^@^D^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@"M-,^@^@^@^@^@^@^@^@^@^@^@^@M-^?M-^?M-^?M-^?M-^?M-^?M-^?M-^?M-^?M-^?M-^?M-^?M-^?M-^?M-^?M-^?^@^@^@^@^@^@^@^@Q:^UM-a^@^@^@^@^@^@^@^A^@^@^@^@Q:^UM-a^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^A^@^@^@^@^@^@^D^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^D^@^@^@^G^@^@^@^D^@^@^@^D^@^@^@^T^@^@^@^B^@^@^@^@^F^F^A^@^@^@^@^@^D^@^@^P^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^A^A^@^A^D^@^@^I^@^I^@^I^L?^@^@^@^@^@@^@^C^@^@^@^A^@^@^@^@^@^@^@^A^@^@^@^@^@^@^C^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@

^ @ é um caractere especial no vim que significa avanço de linha, Por que tantos feeds de linha em um arquivo mysql?

    
por scrapy 21.09.2018 / 03:15

1 resposta

4

Algumas coisas para esclarecer:

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 64 número máximo de linhas suportadas pelo MyISAM . No meu exemplo (começando em 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
    
por 21.09.2018 / 06:01