メモリーテーブルは、処理が早いので使い方によってはかなり
作業効率をあげてくれるかと思います。
mysql> CREATE TABLE MEM_TABLE (i INT) ENGINE = MEMORY;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> show table status like 'MEM%'\G
*************************** 1. row ***************************
Name: MEM_TABLE
Engine: MEMORY
Version: 10
Row_format: Fixed
Rows: 0
Avg_row_length: 5
Data_length: 0
Max_data_length: 10485760
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.01 sec)
mysql> DROP TABLE MEM_TABLE;
Query OK, 0 rows affected (0.00 sec)
===============================================================
そのニ
===============================================================
mysql> CREATE TABLE MEM ENGINE = MEMORY
-> select id,comment from T1;
Query OK, 12 rows affected (0.00 sec)
Records: 12 Duplicates: 0 Warnings: 0
mysql> show table status like 'MEM'\G
*************************** 1. row ***************************
Name: MEM
Engine: MEMORY
Version: 10
Row_format: Fixed
Rows: 12
Avg_row_length: 156
Data_length: 260160
Max_data_length: 16357692
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql>
オリジナルテーブル
mysql> select id,comment from T1;
+----+---------------+
| id | comment |
+----+---------------+
| 1 | test update |
| 2 | 22222222222 |
| 3 | test 20090117 |
| 4 | test 20090117 |
| 5 | test 20090117 |
| 6 | test 20090117 |
| 7 | test 20090117 |
| 8 | test 20090117 |
| 9 | test 20090117 |
| 10 | TEST DE SU |
| 11 | TEST DE SU |
| 12 | TEST DE SU |
+----+---------------+
12 rows in set (0.00 sec)
メモリー（HEAP)テーブル
mysql> select * from MEM;
+----+---------------+
| id | comment |
+----+---------------+
| 1 | test update |
| 2 | 22222222222 |
| 3 | test 20090117 |
| 4 | test 20090117 |
| 5 | test 20090117 |
| 6 | test 20090117 |
| 7 | test 20090117 |
| 8 | test 20090117 |
| 9 | test 20090117 |
| 10 | TEST DE SU |
| 11 | TEST DE SU |
| 12 | TEST DE SU |
+----+---------------+
12 rows in set (0.00 sec)
mysql>
[root@colinux TEST]# /etc/init.d/mysql.server restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
[root@colinux TEST]#
mysql> show tables;
+----------------+
| Tables_in_TEST |
+----------------+
| MEM |
| MEM2 |
| MEM3 |
| T1 |
| T2 |
+----------------+
5 rows in set (0.00 sec)
mysql> select * from MEM;
Empty set (0.00 sec)
mysql>
※ 再起動するとメモリーテーブルにデータはありません。
.frmファイルはあるので、テーブルはDROPしない限り再起動しても存在します。
ハッシュインデックスを使用し、処理が非常に高速で、
テンポラリーテーブルを作成するのに大変便利です。
ただし、サーバがクラッシュすると、この MEMORY
テーブルに格納されたすべてのデータが失われます。
テーブル自体は、定義がディスク上に .frm ファイルで
格納されているので引き続き存在しますが、サーバが
再起動したときにはデータは全て失われています。
MEMORY テーブルは、テーブルあたり最大32インデックスまで、
インデックスあたり16列、インデックスの最大幅は500バイトです。
The MEMORY ストレージエンジンは HASH と BTREE 両方のインデックスを
使って実行します。ここに記すように USING 節 を追加することに
よりどちらのインデックスであるかを明確にすることが出来ます。
=========================================================
HASH
=========================================================
mysql> CREATE TABLE MEM2
-> (id INT, INDEX USING HASH (id))
-> ENGINE = MEMORY;
Query OK, 0 rows affected (0.01 sec)
=========================================================
BTREE
=========================================================
mysql> CREATE TABLE MEM3
-> (id INT, INDEX USING BTREE (id))
-> ENGINE = MEMORY;
Query OK, 0 rows affected (0.00 sec)
mysql>desc MEM;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | 0 | |
| comment | varchar(50) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> desc MEM2;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | MUL | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> desc MEM3;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | MUL | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql>
■MEMORY テーブルに、一意でないキーを使用出来ます (ハッシュテーブルにはあまり見られない機能です)。
■MEMORY テーブル上に重複キーをもつハッシュインデックスがある場合(作成されるインデックスは同じ
値を持つことが多い)、 キーの値に影響を与えるテーブルアップデートと、
全ての消去は非常に処理が遅くなります。処理速度がどの程度落ちるかは、
重複の度合いに比例します (或いは、インデックス濃度に反比例します。) 。
BTREE インデックス を使用すれば、この問題は生じません。
■インデックスを張ったカラムが NULL 値を含みます。
■MEMORY テーブルが固定長のレコードフォーマットを使用しています。
■MEMORY テーブルは BLOB や TEXT カラムをサポートしません。
■MEMORY は AUTO_INCREMENT カラムのサポートもしています。
■INSERT DELAYED のスレッドを MEMORY テーブルで使用出来ます。
■MEMORY テーブルは全てのクライアント間で共有されます(他の TEMPORARY と同様。)。
■MEMORY テーブルのデータはメモリ内に格納されますが、MEMORY
テーブルは、クエリ実行中サーバが作成するテンポラリーテーブルとその情報を共有します。
■テンポラリーテーブルのサイズが大きくなりすぎると、サーバは自動的に
テーブル形式を変換してディスクに格納します。最大サイズはシステム変数
tmp_table_size から求められます。
■MEMORY テーブルは決してディスク上のテーブルに変換されないようにします。
誤った操作を行わないために、 max_heap_table_size システム変数をセットして
MEMORY テーブルの最大サイズを設定することが出来ます。
個々のテーブルに関しては、CREATE TABLE ステートメントの中で
MAX_ROWS のオプションを指定することも出来ます。
■サーバには、同時に動作する全ての MEMORY テーブルを維持するための十分なメモリが必要です。
■MEMORY テーブルの不要になったメモリを開放するには、DELETE または TRUNCATE TABLE
を実行するか、DROP TABLEを指定してテーブルをまとめて削除します。
■MySQLサーバ起動時に MEMORY テーブルにデータを投入したい場合は、
–init-file オプションを指定します。例えば、ファイルに
INSERT INTO … SELECT または LOAD DATA INFILE iなどの
ステートメントを使用し、固定データソースからテーブルを取り込むことが出来ます。
■レプリケーションを使用している場合、 シャットダウン、再起動後のマスタサーバの
MEMORY テーブルは空になっています。ところが、スレーブはこれを認識しないため、
そこにある情報は古いものとなってしまいます。マスタ起動後、マスタ上のMEMORY
テーブルを初めて使う際、DELETE ステートメントがマスタのバイナリログに自動的に
書き込まれます。このようにして、スレーブをマスタに再び一致させます。 ここで注意することは、
この方法によっても、マスタ再起動からその後のテーブル使用時までのインターバル中、
スレーブは依然として古いデータを保存し続けているということです。ただし、マスタ起動時
MEMORY テーブルにデータを投入する際 –init-file オプションを使用することにより、
このインターバルタイムはゼロになります。
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
━━━MEMORYテーブル上、一行に必要なメモリは以下の表現で算出━━━
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
SUM_OVER_ALL_BTREE_KEYS(max_length_of_key + sizeof(char*) × 4)
+ SUM_OVER_ALL_HASH_KEYS(sizeof(char*) × 2)
+ ALIGN(length_of_row+1, sizeof(char*))
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
ALIGN() は行の長さを char ポインタサイズのちょうど倍数にするための数式です。
sizeof(char*) は３２ビットマシンでは４、６４ビットマシンでは８です。
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
MEMORY (HEAP) ストレージエンジン
REPLICATION環境での留意点 add on 2015/05/13
root@localhost [test]> show create table T_MEM01\G
*************************** 1. row ***************************
Table: T_MEM01
Create Table: CREATE TABLE `T_MEM01` (
`id` int(11) DEFAULT NULL,
`text` varchar(100) DEFAULT NULL,
KEY `idx_T_MEM01_id` (`id`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
root@localhost [test]>
root@localhost [test]> insert into T_MEM01 select * from T_ONLINE_DDL;
Query OK, 9 rows affected (0.01 sec)
Records: 9 Duplicates: 0 Warnings: 0
root@localhost [test]> select * from T_MEM01;
+------+-----------------+
| id | text |
+------+-----------------+
| 1 | Group by Test A |
| 2 | Group by Test A |
| 3 | Group by Test A |
| 4 | Group by Test B |
| 5 | Group by Test B |
| 6 | Group by Test B |
| 7 | Group by Test C |
| 8 | Group by Test C |
| 9 | Group by Test C |
+------+-----------------+
9 rows in set (0.00 sec)
root@localhost [test]>
root@localhost [test]> explain select * from T_MEM01 where id = 8;
+----+-------------+---------+------+----------------+----------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+----------------+----------------+---------+-------+------+-------+
| 1 | SIMPLE | T_MEM01 | ref | idx_T_MEM01_id | idx_T_MEM01_id | 5 | const | 2 | NULL |
+----+-------------+---------+------+----------------+----------------+---------+-------+------+-------+
1 row in set (0.00 sec)
root@localhost [test]>
再起動してみる
[admin@GA01 ~]$ sudo -s
[sudo] password for admin:
[root@GA01 admin]# /etc/init.d/mysql.server restart
Shutting down MySQL... SUCCESS!
Starting MySQL. SUCCESS!
[root@GA01 admin]# exit
[admin@GA01 ~]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.24-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
root@localhost [(none)]> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
root@localhost [test]> select * from T_MEM01;
Empty set (0.00 sec)
root@localhost [test]> show create table T_MEM01\G
*************************** 1. row ***************************
Table: T_MEM01
Create Table: CREATE TABLE `T_MEM01` (
`id` int(11) DEFAULT NULL,
`text` varchar(100) DEFAULT NULL,
KEY `idx_T_MEM01_id` (`id`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
root@localhost [test]>
※ Masterは再起動してデータは無いが、Slaveには残っているので注意！！
※ レプリケーション環境で利用する場合はレプリケーション対象外にする方法もある。
その他、17.4.1.21 Replication and MEMORY Tablesに色々と留意点が載っているので要確認。
補足：
MEMORYテーブル以外には、TEMPORARY TABLEを利用する方法もあります。
MYSQL: Create Temporary Table with Index and SELECT
http://stackoverflow.com/questions/14397785/mysql-create-temporary-table-with-index-and-select
![variable.jp [データベース,パフォーマンス,運用]](https://variable.jp/wp-content/uploads/2013/09/cropped-variable.jp_.jpg)
