MySQLでのデータ型とストレージスペースの関係(2)

"用途から型を選択する時に容量を節約する" 時に逆引きするためのメモです。

IPアドレス

- VARCHAR(15)よりもINT UNSIGNED を使った方が容量を消耗しない。IPアドレスが32-bit integerだから。
- address とintegerの相互変換のためにINET_ATON( )、INET_NTOA( )という関数が用意されてる。

UUID

8038115e-52d6-11df-913e-001377d1a28d
- Universally Unique IdentifierはBINARY(16)に保存した方が容量を消耗しない。

'-'を取り除いて、hexをバイナリにして保存すれば良い

mysql> select unhex(replace(uuid(), '-', ''));
+---------------------------------+
| unhex(replace(uuid(), '-', '')) |
+---------------------------------+
| �N�rR� ߑ>  wѢ�                |  -- バリナリが返ってくる
+---------------------------------+
1 row in set (0.00 sec)

- hex とbynaryの相互変換のためにUNHEX( )、HEX( )という関数が用意されてる。

URL

URLをインデクスにする場合（URLが長いと）BTreeのサイズが大きくなってしまう。そこでBTreeを使用しながらもハッシュインデクスのエミュレートし、URLを表すハッシュ値を格納するコラムを別途格納するというテクニックがある。ハッシュ値の生成にはSHA1( )やMD5( )を使用せず、CRC32を使用して誤り検出符号を生成する。

準備

view plainprint?

1. CREATE TABLE websites (  
2. url VARCHAR(255) NOT NULL,  
3. url_crc INT UNSIGNED NOT NULL,  
4. name VARCHAR(50) NOT NULL,  
5. KEY (url_crc)  
6. ) ENGINE=Innodb;  
7.   
8. #　トリガーを作成する。  
9. DELIMITER |  
10. CREATE TRIGGER url_crc_ins BEFORE INSERT ON websites FOR EACH ROW BEGINSET NEW.url_crc=crc32(NEW.url);  
11. END;  
12. |CREATE TRIGGER url_crc_upd BEFORE UPDATE ON websites FOR EACH ROW BEGINSET NEW.url_crc=crc32(NEW.url);  
13. END;  
14. |DELIMITER ;  
15.   
16. mysql> show index from websites;  
17. +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+  
18. | Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |  
19. +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+  
20. | websites |          1 | url_crc  |            1 | url_crc     | A         |           0 |     NULL | NULL   |      | BTREE      |         |   
21. +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+  
22.   
23. # インサート  
24. mysql> insert into websites(url, name) values  
25. ('engineerflies.blogspot.com/', 'エンジニアは空を飛ぶ'),  
26. ('www.google.com', 'Google'),('dev.mysql.com', 'Mysql');  
27.   
28. # CRC32()を作ったインサート。CRC32()の部分をトリガーがやってくれる。  
29. mysql> insert into websites values  
30. ('engineerflies.blogspot.com/', crc32('engineerflies.blogspot.com/'), 'エンジニアは空を飛ぶ'),  
31. ('www.google.com', crc32('www.google.com'), 'Google'),  
32. ('dev.mysql.com', crc32('dev.mysql.com'), 'Mysql');  

CREATE TABLE websites (
url VARCHAR(255) NOT NULL,
url_crc INT UNSIGNED NOT NULL,
name VARCHAR(50) NOT NULL,
KEY (url_crc)
) ENGINE=Innodb;

#　トリガーを作成する。
DELIMITER |
CREATE TRIGGER url_crc_ins BEFORE INSERT ON websites FOR EACH ROW BEGINSET NEW.url_crc=crc32(NEW.url);
END;
|CREATE TRIGGER url_crc_upd BEFORE UPDATE ON websites FOR EACH ROW BEGINSET NEW.url_crc=crc32(NEW.url);
END;
|DELIMITER ;

mysql> show index from websites;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| websites |          1 | url_crc  |            1 | url_crc     | A         |           0 |     NULL | NULL   |      | BTREE      |         | 
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

# インサート
mysql> insert into websites(url, name) values
('engineerflies.blogspot.com/', 'エンジニアは空を飛ぶ'),
('www.google.com', 'Google'),('dev.mysql.com', 'Mysql');

# CRC32()を作ったインサート。CRC32()の部分をトリガーがやってくれる。
mysql> insert into websites values
('engineerflies.blogspot.com/', crc32('engineerflies.blogspot.com/'), 'エンジニアは空を飛ぶ'),
('www.google.com', crc32('www.google.com'), 'Google'),
('dev.mysql.com', crc32('dev.mysql.com'), 'Mysql');

検索実行

view plainprint?

1. mysql> explain select url from websites where url_crc=crc32('dev.mysql.com') and url = 'dev.mysql.com';  
2. CRCを使っても結構な頻度で衝突が起こるらしいので2つ目の条件でcrc32が期待したURLを取得しているか検査している。  
3. ＊<a href="http://www.asahi-net.or.jp/~kc2h-msm/excel/excela01.htm">Birthday paradox参照</a>  
4.   
5. +----+-------------+----------+------+---------------+---------+---------+-------+------+-------------+  
6. | id | select_type | table    | type | possible_keys | key     | key_len | ref   | rows | Extra       |  
7. +----+-------------+----------+------+---------------+---------+---------+-------+------+-------------+  
8. |  1 | SIMPLE      | websites | <b>ref</b>  | url_crc       | <b>url_crc</b> | <b>4</b>       | const |    1 | Using where |   
9. +----+-------------+----------+------+---------------+---------+---------+-------+------+-------------+  
10. type=ref プライマリキーでないインデクスを使って等価検索が行われている。  
11. key=url_crc オプティマイザーがキーにurl_crcを使用したkey_len キーの長さは4bytes=32bit=url_crcの長さ(INT unsigned)  

mysql> explain select url from websites where url_crc=crc32('dev.mysql.com') and url = 'dev.mysql.com';
CRCを使っても結構な頻度で衝突が起こるらしいので2つ目の条件でcrc32が期待したURLを取得しているか検査している。
＊Birthday paradox参照

+----+-------------+----------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table    | type | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+----------+------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | websites | ref  | url_crc       | url_crc | 4       | const |    1 | Using where | 
+----+-------------+----------+------+---------------+---------+---------+-------+------+-------------+
type=ref プライマリキーでないインデクスを使って等価検索が行われている。
key=url_crc オプティマイザーがキーにurl_crcを使用したkey_len キーの長さは4bytes=32bit=url_crcの長さ(INT unsigned)

.