[MySQL] MySQL에서의 Boolean 컬럼에 대해서 with 실행 계획

안녕하세요. 오늘은 MySQL Boolean 타입 관련해서 팀에서 피드백을 받아서 관련 개념들을 정리해보려고 합니다.

설명에 앞서, 해당 포스팅은 아래에 첨부한 블로그 포스팅들을 참고한 내용입니다.

저는 포스팅 내용을 읽어보면서 잘 이해가 되지 않았던 부분들과 실제로 테스트를 해보고 내용들을 작성하려고 합니다.

 

- https://medium.com/daangn/mysql-boolean-%EC%BB%AC%EB%9F%BC-7abd9b35c664

MySQL BOOLEAN 컬럼

- https://zzang9ha.tistory.com/436

MySQL EXPLAIN 실행계획 마스터하기(feat. RealMySQL 8.0)

 

PostgreSQL 에서는 Boolean 타입의 컬럼을 지원합니다. 그래서 True / False 외에도 다양한 Boolean 값을 활용해서 저장할 수 있습니다.

하지만, MySQL 에서는 외부적으로는 Boolean 타입을 지원하는 것처럼 보이지만, 실제로 내부 동작에서는 Boolean 타입을 지원하지 않는다고 합니다.

 

MySQL 에서의 Boolean -> TINYINT(1)

CREATE TABLE users (
    id      INT AUTO_INCREMENT PRIMARY KEY,
    name    VARCHAR(20),
    enabled BOOL /** 또는 BOOLEAN */
);

SHOW CREATE TABLE users;

CREATE TABLE `users` (
  `id`      int         NOT NULL AUTO_INCREMENT,
  `name`    varchar(20) DEFAULT NULL,
  `enabled` tinyint(1)  DEFAULT NULL,   /** NULLABLE */
  PRIMARY KEY (`id`)
);

 

MySQL 에서는 BOOL 또는 BOOLEAN 컬럼 타입을 통해 테이블을 생성하면 MySQL 서버에서 실제 테이블은 TINYINT(1) 로 컬럼이 생성되는 것을 확인할 수 있습니다. 그리고, ROW INSERT 시에는 TRUE / FALSE 또는 0 / 1 로 사용합니다. (예제는 생략)

 

그리고 WHERE 에서 Boolean을 사용할 때 TRUE / FALSE 말고 0 또는 1로 조회할 때 2나 3과 같은 숫자로 비교할 때는 원하는 결과값을 받지 못할수도 있습니다.

 

Boolean 컬럼에서의 인덱스 활용

ALTER TABLE users ADD INDEX idx (enabled, name),
ALGORITHM=inplace,
LOCK=none;

 

 

위 구문은 users 테이블에 인덱스를 추가하겠다는 의미입니다. ALGORITHM과 LOCK을 설정할 수 있는 것은 몰랐는데...

ALGORITHM은 테이블 구조 변경 시 사용할 알고리즘을 지정하는 것이고, LOCK은 인덱스 생성 중 테이블 잠금 수준 지정 여부를 설정하는 것입니다. 자세한 내용은 생략하도록 하겠습니다.

 

이후 users에 소량의 데이터를 넣고 실행계획을 출력해보도록 해보겠습니다.  (2개 ROW)

/** 2개의 ROW 생성 */
INSERT INTO users VALUES (1, 'Matt', TRUE);
INSERT INTO users VALUES (2, 'Lara', 0);

/** 실행 계획 출력 */
EXPLAIN
SELECT *
FROM users
WHERE enabled AND name = 'Matt';

/** 결과 출력 */
+----+-------------+-------+-------+-----------------+---------+------+------+----------+-------------------------+
| id | select_type | table | type  | key             | key_len | ref  | rows | filtered | Extra                   |
+----+-------------+-------+-------+-----------------+---------+------+------+----------+-------------------------+
|  1 | SIMPLE      | users | index | ix_enabled_name | 85      | NULL |    2 |    50.00 | Using where; Using index|
+----+-------------+-------+-------+-----------------+---------+------+------+----------+-------------------------+

/** Optimizer Trace를 ON으로 변경 */
SET SESSION OPTIMIZER_TRACE="enabled=on";

/** 실제 실행되는 쿼리 확인 */
SELECT * FROM information_schema.OPTIMIZER_TRACE;

/** Optimizer Trace */
select
   `users`.`id`      AS `id`,
   `users`.`name`    AS `name`,
   `users`.`enabled` AS `enabled`
from `users`
where ((0 <> `users`.`enabled`) and (`users`.`name` = 'Matt'));

 

 

 

 

실행계획을 해석해보겠습니다.  딱 봐도 알 법한 내용들은 생략하겠습니다.

• select_type: SIMPLE
  
  • select_type은 각 단위 SELECT 쿼리가 어떤 타입의 쿼리인지 표시되는 컬럼.
  • SIMPLE은 단순 SELECT 쿼리라는 의미입니다. 쿼리가 아무리 복잡해도 실행 계획에서 select_type이 SIMPLE인 단위 쿼리는 최대 1개만 존재합니다.
• type: index
  • type은 각 테이블의 레코드를 어떻게 읽었는지에 대한 접근 방식.
  • index는 인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔을 의미.
  • 테이블 풀 스캔(type: all)보다는 빠르지만 반드시 효율적인 방법이라고는 말할 수 없습니다.
• key_len
  • key_len은 선택된 인덱스의 길이를 의미합니다.
  • 85가 나온 이유 (인덱스는 지금 enabled와 name을 적용해서 사용하고 있다)
    • enabled(boolean): 2byte
      • 1byte 아니야?
      • 데이터 저장 1byte + NULL 표시 1byte = 총 2byte 라고 합니다.
      • 여기서 참고할 점! 지금 enabled 컬럼이 NULLABLE 이기 때문에 1byte가 추가된 것입니다. 만약에 enabled 컬럼이 NOT NULL 컬럼인 경우에는 1byte가 추가가 되지 않으니 참고하면 좋을 것 같습니다.
    • name(varchar(20)): 83byte
      • 길이정보: 2byte
      • NULL 표시: 1byte
      • 문자 데이터: 20글자 * 4byte = 80byte (utf8mb4 기준)
      • 여기도 마찬가지! NOT NULL 컬럼인 경우에는 NULL 표시 1byte가 추가되지 않습니다.
    • 따라서, 총 합계 85byte 입니다.
    • 위 내용은 해당 링크를 참고해서 계산하게 되었습니다. 설명이 잘 되어있으니 참고하면 좋을 것 같습니다.
• Extra
  
  • DB Optimizer가 어떻게 동작하는지에 대해 알려주는 힌트 값
  • Using where: 스토리지 엔진으로 부터 받은 데이터를 MySQL 엔진에서 별도의 가공을 해서 필터링 작업을 처리한 경우
  • Using index(커버링 인덱스)
    • 데이터 파일을 전혀 읽지 않고 인덱스만 읽어서 쿼리를 모두 처리할 수 있는 경우
    • 인덱스만으로 쿼리 수행이 가능할 때의 실행계획을 의미하는데, 인덱스만으로 처리되는 방법을 커버링 인덱스(Covering Index) 라고 한다.

필자가 참고한 포스팅과 결과가 다릅니다. 참고한 포스팅의 실행 계획은 type이 range며, key_len이 2였는데요.

이유가 무엇인지 살펴봤는데 ROW 수가 차이가 났기 때문이었습니다.

 

그래서 ROW를 약 500개 이상 넣고 다시 실행계획을 출력해보았습니다.

/** 결과 출력 */
+----+-------------+-------+-------+-----------------+---------+------+------+----------+-------------------------+
| id | select_type | table | type  | key             | key_len | ref  | rows | filtered | Extra                   |
+----+-------------+-------+-------+-----------------+---------+------+------+----------+-------------------------+
|  1 | SIMPLE      | users | range | ix_enabled_name | 2       | NULL | 252  |    10.00 | Using where; Using index|
+----+-------------+-------+-------+-----------------+---------+------+------+----------+-------------------------+

/** Optimizer Trace를 ON으로 변경 */
SET SESSION OPTIMIZER_TRACE="enabled=on";

/** 실제 실행되는 쿼리 확인 */
SELECT * FROM information_schema.OPTIMIZER_TRACE;

/** Optimizer Trace */
select
   `users`.`id`      AS `id`,
   `users`.`name`    AS `name`,
   `users`.`enabled` AS `enabled`
from `users`
where ((0 <> `users`.`enabled`) and (`users`.`name` = 'Matt'))"

 

• type: range
  • range
    • 인덱스 레인지 스캔 형태의 접근 방법. 인덱스를 하나의 값이 아닌 범위로 검색하는 경우
    • 주로, <, >, IS NULL, BETWEEN, IN, LIKE 등의 연산자를 통해 인덱스를 검사할 때 사용
  • key_len
    • 위 결과와 다르게 key_len이 2로 출력.
    • 이는 enabled 컬럼까지만 인덱스 레인지 스캔으로 읽었다는 것을 확인할 수 있음.

 

이 시점에서 궁금한 점이 생겼습니다. 왜 실행 계획에서 type은 range가 출력되었고 key_len은 2로 출력이 된거지?

key_len이 2인 이유는 파악했으니 type이 range인 이유에 대해서 파악해보도록 하겠습니다.

 

실행계획이 range로 발생한 이유

필자가 궁금한 점은 조금의 데이터로 조회를 했을 때는 index가 출력되는데, 500개 이상의 데이터를 조회하니까 range로 출력이 된 이유에 대해서 궁금한 것입니다.

 

위에서 설명한대로 index 스캔 방식은 인덱스의 모든 엔트리를 처음부터 끝까지 읽는 인덱스 풀스캔 방식이고, range 스캔 방식은 인덱스에서 조건에 맞는 범위만 선택적으로 읽는 방식입니다.

 

index 스캔 방식은 결국에는 풀스캔 방식이기 때문에 데이터가 적을 때는 오히려 빠를 수도 있지만, 데이터가 많아지면 불필요한 인덱스 까지 모두 읽어야 된다고 생각합니다.

 

그렇기 때문에 옵티마이저는 테이블의 ROW 수가 2개일 때는 수가 적기 때문에 index 스캔 방식을 선택한 것이고, 테이블의 ROW 수가 500개 이상일 때는 수가 상대적으로 많이 때문에 range 스캔 방식을 선택한 것이 아닐까 라고 생각합니다.

 

실제로 실행된 쿼리문에 대한 간단 분석

 

/** 실행 계획 출력 */
SELECT *
FROM users
WHERE enabled AND name = 'Matt';

/** Optimizer Trace */
select
   `users`.`id`      AS `id`,
   `users`.`name`    AS `name`,
   `users`.`enabled` AS `enabled`
from `users`
where ((0 <> `users`.`enabled`) and (`users`.`name` = 'Matt'))"​

 

실제로 수행된 쿼리문입니다. where users.enabled <> 0 이라는 구문을 처음봤는데요. where users.enabled = TRUE로 해석된 것이 아닌 것입니다.

(실제로 users.enabled = TRUE로 설정하게 되면 더욱 효율적인 스캔 방식이 채택될 것임 - 해보니까 ref 방식이 출력됨)

 

옵티마이저는 enabled<>0을 NULL<enabled<0 AND 0<enabled로 변환해서 실행을 했습니다. 이는 옵티마이저가 조건절이 부정 조건으로 처리되어있는 것을 인덱스를 사용할 수 있는 구조로 변환을 해서 실행을 한 것이기 때문입니다.

---

이렇게 MySQL Boolean 컬럼에 대해서 잘 설명해준 블로그 글을 보면서 이해가 안되었고 궁금했던 내용을 직접 찾아가면서 공부를 해봤습니다. 필자가 참고한 포스팅의 결론은 지금 보는 포스팅에 담지 않았으니 결론이 궁금한 독자 분들은 위에서 참고링크 걸어둔 포스팅 참고하면 좋을 것 같습니다.

 

좋은 포스팅을 제공해주신 당근 테크 블로그의 Sunguck Lee님 감사합니다.