Skip to content

Instantly share code, notes, and snippets.

@yoku0825

yoku0825/myisam_varchar.md

Created July 10, 2015 03:27
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save yoku0825/6ae9dc58598f7084410d to your computer and use it in GitHub Desktop.
Save yoku0825/6ae9dc58598f7084410d to your computer and use it in GitHub Desktop.
Download ZIP
Raw
myisam_varchar.md 
mysql56> CREATE TABLE t5 (val varchar(32)) Engine= MyISAM;
Query OK, 0 rows affected (0.02 sec)

mysql56> INSERT INTO t5 VALUES ('one'), ('two'), ('three');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql56> SELECT * FROM t5;
+-------+
| val   |
+-------+
| one   |
| two   |
| three |
+-------+
3 rows in set (0.00 sec)

$ od -c t5.MYD
0000000 003  \0 005  \v 376 003   o   n   e  \0  \0  \0  \0  \0  \0  \0
0000020  \0  \0  \0  \0 003  \0 005  \v 376 003   t   w   o  \0  \0  \0
0000040  \0  \0  \0  \0  \0  \0  \0  \0 003  \0  \a  \t 376 005   t   h
0000060   r   e   e  \0  \0  \0  \0  \0  \0  \0  \0  \0
0000074
  • レコードは"one", "two", "three"の順番に並んでいる。
mysql56> UPDATE t5 SET val= 'Do you love MySQL?' WHERE val = 'one';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql56> SELECT * FROM t5;
+--------------------+
| val                |
+--------------------+
| Do you love MySQL? |
| two                |
| three              |
+--------------------+
3 rows in set (0.00 sec)

$ od -c t5.MYD
0000000 005  \0 024  \0  \a  \0  \0  \0  \0  \0  \0  \0   < 376 022   D
0000020   o       y   o 003  \0 005  \v 376 003   t   w   o  \0  \0  \0
0000040  \0  \0  \0  \0  \0  \0  \0  \0 003  \0  \a  \t 376 005   t   h
0000060   r   e   e  \0  \0  \0  \0  \0  \0  \0  \0  \0  \t  \0  \r 003
0000100   u       l   o   v   e       M   y   S   Q   L   ?  \0  \0  \0
0000120
  • "one"が入っていた部分をそれより十分長い文字列にすると、そのブロックに入りきらなかった部分はファイルの最後に新しくブロックが作られる。
mysql56> CREATE TABLE t6 LIKE t5;
Query OK, 0 rows affected (0.02 sec)

mysql56> INSERT INTO t6 SELECT * FROM t5;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql56> SELECT * FROM t6;
+--------------------+
| val                |
+--------------------+
| Do you love MySQL? |
| two                |
| three              |
+--------------------+
3 rows in set (0.00 sec)

$ od -c t6.MYD
0000000 003  \0 024  \0 376 022   D   o       y   o   u       l   o   v
0000020   e       M   y   S   Q   L   ? 003  \0 005  \v 376 003   t   w
0000040   o  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0 003  \0  \a  \t
0000060 376 005   t   h   r   e   e  \0  \0  \0  \0  \0  \0  \0  \0  \0
0000100
  • この状態でCREATE, INSERT(mysqldumpからのリストア相当)をすると、フラグメントの情報はもちろん保存されない。
mysql56> UPDATE t6 SET val= 'one' WHERE val= 'Do you love MySQL?';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql56> SELECT * FROM t6;
+-------+
| val   |
+-------+
| one   |
| two   |
| three |
+-------+
3 rows in set (0.00 sec)

$ od -c t6.MYD
0000000 003  \0 005 017 376 003   o   n   e  \0  \0  \0  \0  \0  \0  \0
0000020  \0  \0  \0  \0  \0  \0  \0  \0 003  \0 005  \v 376 003   t   w
0000040   o  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0 003  \0  \a  \t
0000060 376 005   t   h   r   e   e  \0  \0  \0  \0  \0  \0  \0  \0  \0
0000100
  • 短い文字列でUPDATEすると、今まで占有していた部分はNULL埋めされた状態になる。
mysql56> INSERT INTO t6 VALUES ('four');
Query OK, 1 row affected (0.00 sec)

mysql56> SELECT * FROM t6;
+-------+
| val   |
+-------+
| one   |
| two   |
| three |
| four  |
+-------+
4 rows in set (0.00 sec)

$ od -c t6.MYD
0000000 003  \0 005 017 376 003   o   n   e  \0  \0  \0  \0  \0  \0  \0
0000020  \0  \0  \0  \0  \0  \0  \0  \0 003  \0 005  \v 376 003   t   w
0000040   o  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0 003  \0  \a  \t
0000060 376 005   t   h   r   e   e  \0  \0  \0  \0  \0  \0  \0  \0  \0
0000100 003  \0 006  \n 376 004   f   o   u   r  \0  \0  \0  \0  \0  \0
0000120  \0  \0  \0  \0
0000124
  • とはいえ削除とは違って(ブロックは行に割り当てられたままとなり)再利用はされない様子。
(gdb) bt
#0  update_dynamic_record (info=0x7f8041ab0000, filepos=20, record=0x7f8041b65418 "\376\fI love MySQLMySQL?", reclength=14)
    at /home/yoku0825/mysql-5.6.25/storage/myisam/mi_dynrec.c:794
#1  0x000000000092e269 in mi_update (info=0x7f8041ab0000, oldrec=0x7f8041aafef8 "\376\003twoyou love MySQL?",
    newrec=<value optimized out>) at /home/yoku0825/mysql-5.6.25/storage/myisam/mi_update.c:152
#2  0x0000000000592e00 in handler::ha_update_row (this=0x7f8041aafc10, old_data=0x7f8041aafef8 "\376\003twoyou love MySQL?",
    new_data=0x7f8041aafe70 "\376\fI love MySQLMySQL?") at /home/yoku0825/mysql-5.6.25/sql/handler.cc:7305
#3  0x0000000000747bf2 in mysql_update (thd=0x7f804199e000, table_list=0x7f80418c2130, fields=..., values=..., conds=
    0x7f80418c2a98, order_num=1, order=0x7f80396c4450, limit=18446744073709551615, handle_duplicates=DUP_ERROR,
    ignore=false, found_return=0x7f80396c52c0, updated_return=0x7f80396c52b8)
    at /home/yoku0825/mysql-5.6.25/sql/sql_update.cc:819
#4  0x00000000006db2ba in mysql_execute_command (thd=0x7f804199e000) at /home/yoku0825/mysql-5.6.25/sql/sql_parse.cc:3314
#5  0x00000000006dde67 in mysql_parse (thd=0x7f804199e000, rawbuf=<value optimized out>, length=<value optimized out>,
    parser_state=<value optimized out>) at /home/yoku0825/mysql-5.6.25/sql/sql_parse.cc:6386
#6  0x00000000006df7bb in dispatch_command (command=COM_QUERY, thd=0x7f804199e000,
    packet=0x7f80419a2001 "UPDATE t5 SET val= 'I love MySQL' WHERE val= 'two'", packet_length=1099702338)
    at /home/yoku0825/mysql-5.6.25/sql/sql_parse.cc:1340
#7  0x00000000006a713d in do_handle_one_connection (thd_arg=<value optimized out>)
    at /home/yoku0825/mysql-5.6.25/sql/sql_connect.cc:982
#8  0x00000000006a7272 in handle_one_connection (arg=0x7f80418a2000) at /home/yoku0825/mysql-5.6.25/sql/sql_connect.cc:898
#9  0x000000000097d957 in pfs_spawn_thread (arg=0x7f8060bd5740) at /home/yoku0825/mysql-5.6.25/storage/perfschema/pfs.cc:1860
#10 0x00000030628079d1 in start_thread (arg=0x7f80396c6700) at pthread_create.c:301
#11 0x00000030624e88fd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:115
  • 動的フォーマットのテーブルの更新は storage/myisam/mi_dynrec.c の update_dynamic_record あたりでやっていそう。
mysql56> DROP TABLE t5;
Query OK, 0 rows affected (0.00 sec)

mysql56> CREATE TABLE t5 (val varchar(32)) Engine= MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql56> INSERT INTO t5 VALUES ('one'), ('two'), ('three');
Query OK, 3 rows affected (3.95 sec)
Records: 3  Duplicates: 0  Warnings: 0

(gdb) b _mi_write_part_record
Breakpoint 1 at 0x916796: file /home/yoku0825/mysql-5.6.25/storage/myisam/mi_dynrec.c, line 619.

mysql56> UPDATE t5 SET val= 'Do you love MySQL?' WHERE val = 'one';
  • ブロックが拡張される様子を覗くためにgdbを突き刺してUPDATE。
Breakpoint 1, _mi_write_part_record (info=0x7f8041ab0000, filepos=0, length=20, next_filepos=18446744073709551615,
    record=0x7f80396c3958, reclength=0x7f80396c3950, flag=0x7f80396c3a8c)
    at /home/yoku0825/mysql-5.6.25/storage/myisam/mi_dynrec.c:619
619       if (length > *reclength + MI_SPLIT_LENGTH)
(gdb) bt
#0  _mi_write_part_record (info=0x7f8041ab0000, filepos=0, length=20, next_filepos=18446744073709551615,
    record=0x7f80396c3958, reclength=0x7f80396c3950, flag=0x7f80396c3a8c)
    at /home/yoku0825/mysql-5.6.25/storage/myisam/mi_dynrec.c:619
#1  0x0000000000917b11 in update_dynamic_record (info=0x7f8041ab0000, filepos=0,
    record=0x7f8041b65418 "\376\022Do you love MySQL?", reclength=20)
    at /home/yoku0825/mysql-5.6.25/storage/myisam/mi_dynrec.c:939
#2  0x000000000092e269 in mi_update (info=0x7f8041ab0000, oldrec=0x7f8041aaeaf8 "\376\003one", newrec=<value optimized out>)
    at /home/yoku0825/mysql-5.6.25/storage/myisam/mi_update.c:152
#3  0x0000000000592e00 in handler::ha_update_row (this=0x7f8041aae810, old_data=0x7f8041aaeaf8 "\376\003one",
    new_data=0x7f8041aaea70 "\376\022Do you love MySQL?") at /home/yoku0825/mysql-5.6.25/sql/handler.cc:7305
#4  0x0000000000747bf2 in mysql_update (thd=0x7f804199e000, table_list=0x7f80418c2140, fields=..., values=..., conds=
    0x7f80418c2ac0, order_num=1, order=0x7f80396c4450, limit=18446744073709551615, handle_duplicates=DUP_ERROR,
    ignore=false, found_return=0x7f80396c52c0, updated_return=0x7f80396c52b8)
    at /home/yoku0825/mysql-5.6.25/sql/sql_update.cc:819
#5  0x00000000006db2ba in mysql_execute_command (thd=0x7f804199e000) at /home/yoku0825/mysql-5.6.25/sql/sql_parse.cc:3314
#6  0x00000000006dde67 in mysql_parse (thd=0x7f804199e000, rawbuf=<value optimized out>, length=<value optimized out>,
    parser_state=<value optimized out>) at /home/yoku0825/mysql-5.6.25/sql/sql_parse.cc:6386
#7  0x00000000006df7bb in dispatch_command (command=COM_QUERY, thd=0x7f804199e000,
    packet=0x7f80419a2001 "UPDATE t5 SET val= 'Do you love MySQL?' WHERE val = 'one'", packet_length=1099702345)
    at /home/yoku0825/mysql-5.6.25/sql/sql_parse.cc:1340
#8  0x00000000006a713d in do_handle_one_connection (thd_arg=<value optimized out>)
    at /home/yoku0825/mysql-5.6.25/sql/sql_connect.cc:982
#9  0x00000000006a7272 in handle_one_connection (arg=0x7f80418a2000) at /home/yoku0825/mysql-5.6.25/sql/sql_connect.cc:898
#10 0x000000000097d957 in pfs_spawn_thread (arg=0x7f8060bd5740) at /home/yoku0825/mysql-5.6.25/storage/perfschema/pfs.cc:1860
#11 0x00000030628079d1 in start_thread (arg=0x7f80396c6700) at pthread_create.c:301
#12 0x00000030624e88fd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:115

$ od -c t5.MYD
0000000 003  \0 005  \v 376 003   o   n   e  \0  \0  \0  \0  \0  \0  \0
0000020  \0  \0  \0  \0 003  \0 005  \v 376 003   t   w   o  \0  \0  \0
0000040  \0  \0  \0  \0  \0  \0  \0  \0 003  \0  \a  \t 376 005   t   h
0000060   r   e   e  \0  \0  \0  \0  \0  \0  \0  \0  \0
0000074

(gdb) c
Continuing.
  • 1回目に呼ばれたとき。ブレークしているのでもちろん.MYDはまだ更新されていない。
(gdb) c
Continuing.

Breakpoint 1, _mi_write_part_record (info=0x7f8041ab0000, filepos=60, length=20, next_filepos=18446744073709551615,
    record=0x7f80396c3958, reclength=0x7f80396c3950, flag=0x7f80396c3a8c)
    at /home/yoku0825/mysql-5.6.25/storage/myisam/mi_dynrec.c:619
619       if (length > *reclength + MI_SPLIT_LENGTH)
(gdb) bt
#0  _mi_write_part_record (info=0x7f8041ab0000, filepos=60, length=20, next_filepos=18446744073709551615,
    record=0x7f80396c3958, reclength=0x7f80396c3950, flag=0x7f80396c3a8c)
    at /home/yoku0825/mysql-5.6.25/storage/myisam/mi_dynrec.c:619
#1  0x0000000000917b11 in update_dynamic_record (info=0x7f8041ab0000, filepos=60, record=0x7f8041b6541f "u love MySQL?",
    reclength=13) at /home/yoku0825/mysql-5.6.25/storage/myisam/mi_dynrec.c:939
#2  0x000000000092e269 in mi_update (info=0x7f8041ab0000, oldrec=0x7f8041aaeaf8 "\376\003one", newrec=<value optimized out>)
    at /home/yoku0825/mysql-5.6.25/storage/myisam/mi_update.c:152
#3  0x0000000000592e00 in handler::ha_update_row (this=0x7f8041aae810, old_data=0x7f8041aaeaf8 "\376\003one",
    new_data=0x7f8041aaea70 "\376\022Do you love MySQL?") at /home/yoku0825/mysql-5.6.25/sql/handler.cc:7305
#4  0x0000000000747bf2 in mysql_update (thd=0x7f804199e000, table_list=0x7f80418c2140, fields=..., values=...,
    conds=0x7f80418c2ac0, order_num=1, order=0x7f80396c4450, limit=18446744073709551615, handle_duplicates=DUP_ERROR,
    ignore=false, found_return=0x7f80396c52c0, updated_return=0x7f80396c52b8)
    at /home/yoku0825/mysql-5.6.25/sql/sql_update.cc:819
#5  0x00000000006db2ba in mysql_execute_command (thd=0x7f804199e000) at /home/yoku0825/mysql-5.6.25/sql/sql_parse.cc:3314
#6  0x00000000006dde67 in mysql_parse (thd=0x7f804199e000, rawbuf=<value optimized out>, length=<value optimized out>,
    parser_state=<value optimized out>) at /home/yoku0825/mysql-5.6.25/sql/sql_parse.cc:6386
#7  0x00000000006df7bb in dispatch_command (command=COM_QUERY, thd=0x7f804199e000,
    packet=0x7f80419a2001 "UPDATE t5 SET val= 'Do you love MySQL?' WHERE val = 'one'", packet_length=1099702345)
    at /home/yoku0825/mysql-5.6.25/sql/sql_parse.cc:1340
#8  0x00000000006a713d in do_handle_one_connection (thd_arg=<value optimized out>)
    at /home/yoku0825/mysql-5.6.25/sql/sql_connect.cc:982
#9  0x00000000006a7272 in handle_one_connection (arg=0x7f80418a2000) at /home/yoku0825/mysql-5.6.25/sql/sql_connect.cc:898
#10 0x000000000097d957 in pfs_spawn_thread (arg=0x7f8060bd5740) at /home/yoku0825/mysql-5.6.25/storage/perfschema/pfs.cc:1860
#11 0x00000030628079d1 in start_thread (arg=0x7f80396c6700) at pthread_create.c:301
#12 0x00000030624e88fd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:115

$ od -c t5.MYD
0000000 005  \0 024  \0  \a  \0  \0  \0  \0  \0  \0  \0   < 376 022   D
0000020   o       y   o 003  \0 005  \v 376 003   t   w   o  \0  \0  \0
0000040  \0  \0  \0  \0  \0  \0  \0  \0 003  \0  \a  \t 376 005   t   h
0000060   r   e   e  \0  \0  \0  \0  \0  \0  \0  \0  \0
0000074
  • 2回目に呼ばれたところでブレーク。1回目の処理で"Do yo"あたりまでもとのブロックが書き換えられたことがわかる。
(gdb) c
Continuing.

$ od -c t5.MYD
0000000 005  \0 024  \0  \a  \0  \0  \0  \0  \0  \0  \0   < 376 022   D
0000020   o       y   o 003  \0 005  \v 376 003   t   w   o  \0  \0  \0
0000040  \0  \0  \0  \0  \0  \0  \0  \0 003  \0  \a  \t 376 005   t   h
0000060   r   e   e  \0  \0  \0  \0  \0  \0  \0  \0  \0  \t  \0  \r 003
0000100   u       l   o   v   e       M   y   S   Q   L   ?  \0  \0  \0
0000120
  • ブレークから抜けて2回目の _mi_write_part_record が走り終わると、ファイルの後ろにブロックが追加されている。
  • ということは、行の先頭は常にIN-PLACEな場所が更新される(そして行の先頭順に並ぶ)ので、順番は保証できる？？？
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment