InnoDB_nonpk.md

mysql56> CREATE TABLE t1 (num int) Engine= InnoDB;
Query OK, 0 rows affected (0.06 sec)

mysql56> INSERT INTO t1 VALUES (2);
Query OK, 1 row affected (0.01 sec)

mysql56> INSERT INTO t1 VALUES (1);
Query OK, 1 row affected (0.02 sec)

mysql56> SELECT * FROM t1;
+------+
| num  |
+------+
|    2 |
|    1 |
+------+
2 rows in set (0.00 sec)

• 暗黙の行IDがセットされるため、並び順はINSERTした順番。

mysql56> DELETE FROM t1 WHERE num = 2;
Query OK, 1 row affected (0.01 sec)

mysql56> INSERT INTO t1 VALUES (3);
Query OK, 1 row affected (0.00 sec)

mysql56> SELECT * FROM t1;
+------+
| num  |
+------+
|    1 |
|    3 |
+------+
2 rows in set (0.00 sec)

• 暗黙の行IDはカウントアップされるので、途中の行を消してもその後のINSERTは最後尾に並ぶ。

InnoDB_pk.md

mysql56> CREATE TABLE t2 (num int PRIMARY KEY) Engine= InnoDB;
Query OK, 0 rows affected (0.05 sec)

mysql56> INSERT INTO t2 VALUES (2);
Query OK, 1 row affected (0.01 sec)

mysql56> INSERT INTO t2 VALUES (1);
Query OK, 1 row affected (0.01 sec)

mysql56> SELECT * FROM t2 IGNORE INDEX(PRIMARY);
+-----+
| num |
+-----+
|   1 |
|   2 |
+-----+
2 rows in set (0.00 sec)

• クラスターインデックス(この場合はPRIMARY)の順に並ぶ。

mysql56> DELETE FROM t2 WHERE num = 2;
Query OK, 1 row affected (0.01 sec)

mysql56> INSERT INTO t2 VALUES (3);
Query OK, 1 row affected (0.01 sec)

mysql56> SELECT * FROM t2 IGNORE INDEX(PRIMARY);
+-----+
| num |
+-----+
|   1 |
|   3 |
+-----+
2 rows in set (0.00 sec)

• 行の削除, 再INSERTがあってもクラスターインデックスの順番に並ぶ。

MyISAM.md

mysql56> CREATE TABLE t3 (num int) Engine= MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql56> INSERT INTO t3 VALUES (2);
Query OK, 1 row affected (0.01 sec)

mysql56> INSERT INTO t3 VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql56> SELECT * FROM t3;
+------+
| num  |
+------+
|    2 |
|    1 |
+------+
2 rows in set (0.00 sec)

• MyISAMは.MYDファイル上に並んでいる順番で返るので、INSERTした順で返ってくる、ただし。

mysql56> DELETE FROM t3 WHERE num = 2;
Query OK, 1 row affected (0.00 sec)

mysql56> INSERT INTO t3 VALUES (3);
Query OK, 1 row affected (0.00 sec)

mysql56> SELECT * FROM t3;
+------+
| num  |
+------+
|    3 |
|    1 |
+------+
2 rows in set (0.00 sec)

• DELETEによって隙間ができた場合、可能な限りそこを再利用してしまうので、運用中のデータが.MYDファイル上でどんな順番になっているかを判断することは現実的に不可能。
  • 行がvarchar型のカラムを含んでいた場合はなおさら不可能。

mysql56> CREATE TABLE t4 (num int PRIMARY KEY) Engine= MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql56> INSERT INTO t4 VALUES (2);
Query OK, 1 row affected (0.00 sec)

mysql56> INSERT INTO t4 VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql56> SELECT * FROM t4 IGNORE INDEX(PRIMARY);
+-----+
| num |
+-----+
|   2 |
|   1 |
+-----+
2 rows in set (0.00 sec)

• MyISAMのテーブルスキャンの場合、Primary Keyの有無にかかわらず同じ動作。

MyISAM_mysqldump.md

$ mysqldump56 d1 t3 t4 | grep INSERT
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
INSERT INTO `t3` VALUES (3),(1);
INSERT INTO `t4` VALUES (1),(2);

mysql56> SELECT * FROM t4;
+-----+
| num |
+-----+
|   1 |
|   2 |
+-----+
2 rows in set (0.00 sec)

mysql56> EXPLAIN SELECT * FROM t4;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | t4    | index | NULL          | PRIMARY | 4       | NULL |    2 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql56> SELECT * FROM t4 IGNORE INDEX(PRIMARY);
+-----+
| num |
+-----+
|   2 |
|   1 |
+-----+
2 rows in set (0.00 sec)

• PRIMARY KEY(および一切のキーのない) t3テーブルは.MYDファイルの順に並んでいるが、
• PRIMARY KEYのあるt4テーブルはPRIMARY KEYに沿ってフェッチしているので.MYDファイルの順番が保存されない