15.5 TEXT, BLOB
- MySQL에서 대량의 데이터를 저장하려면 TEXT나 BLOB 타입을 사용해야 하는데, 이 두 타입은 많은 부분에서 거의 똑같은 설정이나 방식으로 작동한다.
TEXT와 BLOB의 차이점
- TEXT 타입은 문자열을 저장하는 대용량 칼럼이라서 문자집합이나 콜레이션을 가진다
- BLOB 타입은 이진 데이터 타입이라서 별도의 문자집합이나 콜레이션을 가지지 않는다.
| 데이터 타입 | 필요 저장 공간 (L = 저장하고자 하는 데이터의 바이트 수) | 최대 저장 가능한 바이트 수 |
|---|---|---|
| TINYTET, TINYBLOB | L + 1 바이트 | 2^8 - 1 (255) |
| TEXT, BLOB | L + 2 바이트 | 2^16 - 1 (65,535) |
| MEDIUMTEXT, MEDIUMBLOB | L + 3 바이트 | 2^24 - 1 (16777, 215) |
| LONGTEXT, LONGBLOB | L + 4 바이트 | 2^32 - 1 (4,294,967,295) |
| 고정 길이 | 가변 길이 | 대용량 | |
|---|---|---|---|
| 문자 데이터 | CHAR | VARCHAR | TEXT |
| 이진 데이터 | BINARY | VARBINARY | BLOB |
TEXT나 BLOB 타입은 주로 다음과 같은 상황에서 사용하는 것이 좋다.
- 칼럼 하나에 저장되는 문자열이나 이진 값의 길이가 예측할 수 없이 클 때 TEXT나 BLOB를 사용한다. 하지만 다른 DBMS와 달리 MySQL에서는 값의 크기가 4000바이트를 넘을 때 반드시 BLOB이나 TEXT를 사용해야 하는 것은 아니다. MySQL에서는 레코드의 전체 크기가 64KB를 넘지 않는 한도 내에서는 VARCHAR나 VARBINARY의 길이는 제한이 없다. 그래서 용도에 따라서는 4000바이트 이상의 값을 저장하는 칼럼도 VARCHAR나 VARBINARY 타입을 이용할 수 있다.
- MySQL에서 버전에 따른 조금씩의 차이는 있지만 일반적으로 하나의 레코드는 전체 크기가 64KB를 넘어설 수 없다. VARCHAR나 VARBINARY와 같은 가변 길이 칼럼은 최대 저장 가능 크기를 포함해 64KB로 크기가 제한된다. 만약 레코드의 전체 크기가 64KB를 넘어서서 더 큰 칼럼을 추가할 수 없다면 일부 칼럼을 TEXT나 BLOB타입으로 전환해야 할 수도 있다.
MySQL 에서는 쿼리의 특성에 따라 임시 테이블을 생성해야 할 때도 있다. 이때 사용되는 임시 테이블은 메모리에 저장될 수도 있고 디스크에 저장될 수도 있다. 임시 테이블을 메모리에 저장할 때는 MEMORY 스토리지 엔진을 사용하게 된다. 하지만 MEMORY 스토리지 엔진은 VARCHAR나 VARBINARY와 같은 가변 길이 타입뿐 아니라 TEXT나 BLOB과 같은 타입을 지원하지 않는다.
만약 SELECT 쿼리를 통해 가져오려는 BLOB나 TEXT 타입 칼럼의 값이 크지 않거나 일부만 조회해도 될 때가 있다. 이럴 때는 CAST()나 SUBSTRING() 함수 등을 이용해 강제로 CHAR나 VARCHAR로 변환해서 조회한다면 필요한 임시 테이블이 메모리에 생성되도록 유도할 수 있다.
MyISAM vs InnoDB
| MyISAM | InnoDB | |
|---|---|---|
| 트랜잭션 | 미지원 | 지원 |
| 외래키 | 미지원 | 지원 |
| 모델 복잡도 | 단순 | 복잡 |
| 무결성 | 미지원 | 지원 |
| 시스템 자원 사용 | 적음 | 많음 |
| 복구 | 미지원 | 지원 |
| Lock | Table level locking | Row level locking |
- MyISAM은 메모장과 같이 아주 기본적인 기능만으로 제공하는 대신 상당히 가볍다는 장점이 있다.
-
- SELECT와 같은 검색에 상당히 강력하다.
- 테이블 단위로 락킹이 걸리므로 동시에 여러가지 데이터 수정작업이 일어나는 테이블에 사용할 경우 성능이 저하될 우려가 크다.
- 따라서 정적인 데이터를 저장하고 자주 읽기 작업이 일어나는 테이블에 적합하다.
- InnoDB는 이클립스와 같은 IDE같이 다양한 기능을 제공하는 대신 복잡도가 높고 무겁다는 단점이 있다.
-
- 중요 데이터를 다룰 때, 무결성이 보장되고, 트랜잭션 처리가 가능하고, 오류 복구 기능을 제공한다.
- 락킹의 단위가 행 단위라서 읽고 쓰는 상황이 많은 경매 테이블에 적합하다.
BLOB이나 TEXT 타입의 칼럼이 포함된 테이블에 실행되는 INSERT나 UPDATE 문장 중에서 BLOB이나 TEXT 칼럼을 조작하는 SQL 문장은 매우 길어질 수 있는데, MySQL 서버의 max_allowed_packet 시스템 설정에 정의된 값보다 큰 SQL 문장은 MySQL 서버로 전송되지 못하고 오류가 발생하게 된다. 만약 대용량 BLOB이나 TEXT 칼럼을 사용하는 쿼리가 있다면 MySQL 서버의 max_allowed_packet 시스템 설정을 필요한 만큼 충분히 늘려 주는 것이 좋다.