MariaDB

temporary table 사용

서적자 2016. 11. 28. 18:04

sql문을 짜다보면 공통된 부분을 분리하여 관리하고 싶어질 때가 있습니다. 마치 javascript에서 function을 사용하듯이 말이죠.

 

이러한 욕구를 충족시키기 위해 Microsoft Sql Server(이하 Mssql)은 with 절을 제공합니다. 사용하기 굉장히 편리해서 매우 빈번하게 사용했습니다.

 

Oracle에는 with절이 있지만 Mssql의 것보다 기능이 제한적이어서 거의 사용해본적이 없습니다. 하지만 grobal temporary table은 Mssql의 with절을 충분히 대체할 수 있었습니다.

 

MaraiDB에도 temporary table이 있습니다. 아래와 같이 사용하면 됩니다.

 

DROP TEMPORARY TABLE IF EXISTS tmp_table;

CREATE TEMPORARY TABLE tmp_table(comp_code VARCHAR(10), dept_code VARCHAR(10), PRIMARY KEY(`dept_code`)) ENGINE=MEMORY;

 

Oracle의 한번 생성하면 계속 사용할 수 있는 grobal temporary table과는 달리 MariaDB의 temporary table은 session이 끊기면 drop되므로 사용전에 반드시 create해야합니다.

 

그런데 왜 create 전에 drop을 할까요? temporary table은 session이 끊겨야 drop되는데 시스템은 일반적으로 connection pool을 사용하므로 session을 재사용합니다. 즉, 재사용된 session에는 temporary table이 drop되지 않고 남아있을 수 있으므로 있다면 drop해야합니다. 데이터만 없으면 되니까 truncate하면 되지 않냐구요? 전에 누가 어떤 schema로 같은 table명을 사용했을지 어떻게 확신하겠습니까.

 

맨 뒤에 ENGINE=MEMORY는 무엇일까요? Oracle의 grobal temporary table은 memory상에 데이터를 저장합니다. 하지만 MariaDB는 기본적으로 일반 table이 저장되는 매체에 데이터를 저장합니다. 보통은 disk에 데이터를 저장하므로 temporary table에 insert를 하면 disk I/O를 수행하게 됩니다. 분산처리를 하기 위해서 일까요? 어쨌든 메모리상에 데이터를 저장해두고 싶다면 ENGINE=MEMORY라고 명시해야합니다.

 

메모리상에 데이터를 저장한다면 용량에 제한이 있습니다. 환경변수 max_heap_table_size로 설정되며 설정된 용량보다 많은 데이터를 저장하려고 하면 the table '[table명]' is full 에러가 발생합니다.

 

그리고 session이 유지되는 동안 drop되지 않는 데이터는 메모리 상에 계속 쌓여있을 것입니다. 메모리 용량이 성능에 영향을 미친다면 사용이 끝난 temporary table은 drop하는 것이 좋겠습니다.

 

PRIMARY KEY는 왜 설정을 하는 것일까요? 물론 중복 여부 확인도 의미가 있지만 보통의 경우엔 index로의 역할이 더 큽니다. 기본 저장매체가 memory가 아니므로 temporary table의 index는 일반 table의 index만큼 중요합니다. 실행계획을 감안하여 설정합니다.

 

위와 같은 Oracle grobal temporary table과의 다른 점 때문에 MariaDB의 temporary table은 사용하기에 불편합니다. 하지만 익숙하지 않아서 그렇다고 볼 수도 있습니다. 하지만 아래의 특성은 익숙함의 차이와는 다른 분명한 단점입니다.

 

MariaDB의 temporary table은 한 sql문에서 두번 이상 접근할 수 없습니다. update문에서와 같이 일반적으로 사용 못하는 경우가 아닌 단순한 select문에서도 두번 이상 접근할 수 없습니다. 이 때문에 여러번 사용하기 위해서는 temporary table을 복제하여 사용해야합니다. 이는 더 많은 메모리를 사용하게함으로서 성능에 악영향을 미칩니다. 이것이 싫어서 공통화를 포기하기엔 MariaDB가 join predicate pushdown transformation을 지원하지 않습니다. sql문이 상상이상으로 복잡해집니다.

 

여러까지 부정적인 면을 서술해왔지만 알고 쓰면 못쓸 정도의 기능은 아닙니다. 그리고 제가 모르는 다른 좋은 방법도 있을 수 있습니다. 알고 계시면 댓글 부탁드립니다.