[MySQL][힙테이블] heap table

HEAP 또는 MEMORY 형식의 테이블 
MEMORY engine은 HEAP engine을 의미한다.

HEAP 형식의 테이블은 원래 테이블의 인덱스를 고쳐만든(hashed) 인덱스를 사용하여 메모리(RAM)에 저장한다. 메모리에 저장되기 때문에 속도가 매우 빠르지만, MySQL이 충돌하면 메모리에 저장된 모든 데이터를 잃게 된다. HEAP 형식의 테이블은 임시 테이블로 유용하다. HEAP 테이블에서 DELETE, INSERT 문은 고쳐만든(hashed) 테이블이기 때문에 원래의 테이블에는 아무런 영향을 미치지 않는다. 내부적으로 HEAP 테이블은 100% 메모리를 사용하기 때문에 다른 목록에 사용할 여유공간이 없다.
HEAP 형식의 테이블과 MEMORY 엔진을 사용한 테이블은 동일하다.

【예제】
mysql> select * from river;
+----+------------+----------+
| id | river_name | province |
+----+------------+----------+
|  1 | 금강       | 전라북도 |
|  2 | 대동강     | 평안도   |
|  3 | 영산강     | 전라남도 |
|  4 | 한강       | 강원도   |
+----+------------+----------+
4 rows in set (0.00 sec)

mysql> create table test type=heap -> select river_name,province from river;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from test;
+------------+----------+
| river_name | province |
+------------+----------+
| 금강       | 전라북도 |
| 대동강     | 평안도   |
| 영산강     | 전라남도 |
| 한강       | 강원도   |
+------------+----------+
4 rows in set (0.00 sec)

mysql> delete from test where river_name="대동강";
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------------+----------+
| river_name | province |
+------------+----------+
| 금강       | 전라북도 |
| 영산강     | 전라남도 |
| 한강       | 강원도   |
+------------+----------+
3 rows in set (0.00 sec)

mysql> select * from river;
+----+------------+----------+
| id | river_name | province |
+----+------------+----------+
|  1 | 금강       | 전라북도 |
|  2 | 대동강     | 평안도   |
|  3 | 영산강     | 전라남도 |
|  4 | 한강       | 강원도   |
+----+------------+----------+
4 rows in set (0.00 sec)

mysql> update test set province="경기도" -> where river_name="한강";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test;
+------------+----------+
| river_name | province |
+------------+----------+
| 금강       | 전라북도 |
| 영산강     | 전라남도 |
| 한강       | 경기도   |
+------------+----------+
3 rows in set (0.00 sec)

mysql> select * from river;
+----+------------+----------+
| id | river_name | province |
+----+------------+----------+
|  1 | 금강       | 전라북도 |
|  2 | 대동강     | 평안도   |
|  3 | 영산강     | 전라남도 |
|  4 | 한강       | 강원도   |
+----+------------+----------+
4 rows in set (0.00 sec)

mysql> drop table test;
Query OK, 0 rows affected (0.00 sec)

mysql>

HEAP 형식의 테이블이 가지는 특징은 다음과 같다.
 • heap 테이블을 만들 때 모든 메모리를 사용하지 않도록 MAX_ROWS를 지정해야한다.
 • 인덱스는 =나 <=>와 함께 사용한다.
 • heap 테이블은 row를 찾기 위해서 key의 전체(whole)를 사용한다. 이는 MyISAM 테이
    블에서는 key의 prefix를 사용하는 것과 비교된다.
 • HEAP 테이블은 고정 레코드 길이 포맷을 가진다.
 • HEAP 테이블은 BLOB/TEXT 컬럼을 지원하지 않는다.
 • HEAP 테이블은 AUTO_INCREMENT 컬럼을 지원하지 않는다.
 • HEAP 테이블은 non-unique key를 가질 수  있다. 이는 hashed한 테이블의 공통 사항
    이 아니다.
 • 모든 클라이언트가 HEAP 테이블을 공유할 수 있다.
 • order에 의한 다음 엔트리를 찾지 못한다.
 • heap 테이블의 데이터는 작은 블록으로 자리를 차지하지만, INSERT에서 100% 
    dynamic하여 컬럼 필드의 크기를 가변한다. 그러므로 overflow 영역이 발생하지 않고 
    extra key의 공간이 필요하지 않다.
 • 모든 heap 테이블을 동시에 사용하려면 충분한 여분의 메모리가 요구된다.
 • DELETE FROM heap_table, TRUNCATE hweap_table, DROP TABLE heap_table을 실행하여 메
    모리 점유를 해지한다.
 • MySQL은 두 값 사이에 몇 개의 row가 있는지 알아내지 못한다.
 • HEAP 테이블은 NULL 컬럼을 지원하지 않는다.
 • max_heap_table_size보다 더 큰 heap 테이블을 만들지 못한다.

 HEAP 테이블이 차지하는 메모리의 크기 :

    SUM_OVER_ALL_KEYS(max_length_of_key + sizeof(char*)*2) + ALIGN(length_of_row + 1, sizeof(char*))

    여기서 sizeof(char*)은 32비트 시스템은 4, 64비트 시스템은 8이다.

다음은 MEMORY엔진과 HEAP 엔진이 같음을 확인할 수 있다.

mysql> create table bb2(a int, name varchar(20))ENGINE=HEAP;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table bb2;
+-------+-------------------------------------------------------------------------------------------------
| Table | Create Table                                                                                                              |
+-------+---------------------------------------------------------------------------------------------------------------------------+
| bb2   | CREATE TABLE `bb2` (
  `a` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create table bb3(a int, name varchar(20))ENGINE=MEMORY;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table bb2;
+-------+-------------------------------------------------------------------------------------------------
| Table | Create Table                                                                                                    |
+-------+-------------------------------------------------------------------------------------------------
| bb2   | CREATE TABLE `bb3` (
  `a` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)

mysql> 

출처 : http://radiocom.kunsan.ac.kr/lecture/mysql/table_heap.html

고려사항
a) You should always use specify MAX_ROWS in the CREATE statement to ensure that you accidentally do not use all memory. 
  : 갑자기 모든 메모리가 고갈되지 않도록 CREATE 구문 사용시 MAX_ROWS 사용

b) Indexes will only be used with = and <=> (but are VERY fast). 
  : = 와 <=> 에 인덱스 사용가능

c) HEAP tables can only use whole keys to search for a row; compare this to MyISAM tables where any prefix of the key can be used to find rows. 
  : MyISAM 처럼 부분키 사용이 가능하지 않고 전체키만 사용가능

d) HEAP tables use a fixed record length format. 
  : 고정된 레코드 길이 포맷사용

e) HEAP doesn't support BLOB/TEXT columns. 
  : BLOB/TEXT 타입 컬럼 지원 안됨

f) HEAP doesn't support AUTO_INCREMENT columns. 
  : AUTO_INCREMENT 지원 안됨

g) Prior to MySQL 4.0.2, HEAP doesn't support an index on a NULL column. 
  : 4.0.2 이전 버전은 NULL 컬럼 인덱스 지원 안함 

h) You can have non-unique keys in a HEAP table (this isn't common for hashed tables). 
  : non-unique 키 사용가능(일반적 hashed table 은 그렇지 않음)

i) HEAP tables are shared between all clients (just like any other table). 
  : 다른테이블과 마찬가지로 모든 클라이언트들이 공유됨.

j) You can't search for the next entry in order (that is, to use the index to do an ORDER BY). 
  : 순서대로 다음 entry 를 검색 불가. (order by 위해 인덱스 사용하는)

k) Data for HEAP tables are allocated in small blocks. The tables are 100% dynamic (on inserting). No overflow areas and no extra key space are needed. Deleted rows are put in a linked list and are reused when you insert new data into the table. 
  : heap 테이블 데이타는 작은 블록으로 위치하고 있고 100% 동적. 오버플로우 영역이과 키 공간이 필요없음. 지원진 열은 링크드 리스트에 놓여저 테이블 안에 새로운 데이타를 입력할 때 재사용됨

l) You need enough extra memory for all HEAP tables that you want to use at the same time. 
  : 모든 힙테이블 사용하기 위해 충분한 여유의 메모리 필요

m) To free memory, you should execute DELETE FROM heap_table, TRUNCATE heap_table or DROP TABLE heap_table. 
  : 메모리 해제하려면 DELETE FROM heap_table, TRUNCATE heap_table or DROP TABLE heap_table 

n) MySQL cannot find out approximately how many rows there are between two values (this is used by the range optimiser to decide which index to use). This may affect some queries if you change a MyISAM table to a HEAP table. 
: 인덱스 사용 결정 위해 옵티마이저가 사용하는 두 값사이의 열갯수를 찾을 수 없음.
   myisam -> heap 변환시 일부 쿼리에 영향을 줄 수 있음.

o) To ensure that you accidentally don't do anything foolish, you can't create HEAP tables bigger than max_heap_table_size. 
:  max_heap_table_size 보다 큰 heap 테이블은 만들 수 없으므로 갑자기 바보짓(^^)하지 않도록.

* 모두가 중요하고 결정적은 순간엔 문제가 될 소지가 있지만 처음 테이블 사용시 a),e),f) 
그리고 m) 항목을 주의하면 메모리 효율을 떨어뜨리지 않을 것 같네요.

* 참고로 필요한 메모리 계산법입니다.
SUM_OVER_ALL_KEYS(max_length_of_key + sizeof(char*) * 2)+ ALIGN(length_of_row+1, sizeof(char*))

sizeof(char*) 는 32-bit 머신에서 4, 64-bit 머신에선 8 임다.(뷰티넷 당근 32-bit)

* MySQL 5.1 에서 view 테이블을 지원할 예정이랍니다