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)