tom__bo’s Blog

MySQL!! MySQL!! @tom__bo

NOT NULL指定しないとカラムの確保するストレージサイズが1byte増える

NULLを許容するカラムをつくるとそれぞれのカラムで必要なストレージサイズが1byte増えていた。

以下はexplainでkey長を見てみた様子。 INTは4byteを確保する訳だけど、NULL (DEFAULT NULL)指定したら各カラムで1byte増えているのがわかる。

NULLABLEかの判定に1bit使っていて、丸められて1byte使っているとしたらNULLABLEなカラムがN個あるとして(N/8+1) byte余計に使うかと思ったらそれぞれのカラムで1byte増えてた。。。(idx8, idx9はその境界値が出たりしないかと思って作った)

mysql> CREATE TABLE `t7` (
    ->   `id` int NOT NULL AUTO_INCREMENT,
    ->   `c1` int DEFAULT NULL,
    ->   `c2` int DEFAULT NULL,
    ->   `c3` int DEFAULT NULL,
    ->   `c4` int DEFAULT NULL,
    ->   `c5` int DEFAULT NULL,
    ->   `c6` int DEFAULT NULL,
    ->   `c7` int DEFAULT NULL,
    ->   `c8` int DEFAULT NULL,
    ->   `c9` int DEFAULT NULL,
    ->   `c10` int DEFAULT NULL,
    ->   `c11` int DEFAULT NULL,
    ->   `c12` int DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `idx1` (`c1`),
    ->   KEY `idx4` (`c1`, `c2`, `c3`, `c4`),
    ->   KEY `idx8` (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8`),
    ->   KEY `idx9` (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8`, `c9`),
    ->   KEY `idx12` (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8`, `c9`, `c10`, `c11`, `c12`)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t7 (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12) values (1,2,3,4,5,6,7,8,9,10,11,12);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t7 (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12)  (select c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12 from t7);
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

-- 省略

mysql> insert into t7 (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12)  (select c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12 from t7);
Query OK, 256 rows affected (0.03 sec)
Records: 256  Duplicates: 0  Warnings: 0

mysql> select count(*) from t7;
+----------+
| count(*) |
+----------+
|      512 |
+----------+
1 row in set (0.02 sec)

mysql> explain select id from t7 where c1 = 1;
+----+-------------+-------+------------+------+---------------------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys             | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t7    | NULL       | ref  | idx1,idx4,idx8,idx9,idx12 | idx1 | 5       | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select id from t7 where c1 = 1 and c2 = 2 and  c3 = 3 and c4 = 4 and c5 = 5 and c6 = 6 and c7 = 7 and c8 = 8;
+----+-------------+-------+------------+------+---------------------------+------+---------+-------------------------------------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys             | key  | key_len | ref                                             | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------------------+------+---------+-------------------------------------------------+------+----------+-------------+
|  1 | SIMPLE      | t7    | NULL       | ref  | idx1,idx4,idx8,idx9,idx12 | idx8 | 40      | const,const,const,const,const,const,const,const |  512 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------------------+------+---------+-------------------------------------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select id from t7 where c1 = 1 and c2 = 2 and  c3 = 3 and c4 = 4 and c5 = 5 and c6 = 6 and c7 = 7 and c8 = 8 and c9 = 9;
+----+-------------+-------+------------+------+---------------------------+------+---------+-------------------------------------------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys             | key  | key_len | ref                                                   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------------------+------+---------+-------------------------------------------------------+------+----------+-------------+
|  1 | SIMPLE      | t7    | NULL       | ref  | idx1,idx4,idx8,idx9,idx12 | idx9 | 45      | const,const,const,const,const,const,const,const,const |  512 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------------------+------+---------+-------------------------------------------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)