MySQL BOOLEAN 컬럼

PostgreSQL 서버와 같은 RDBMS 서버는 네이티브하게 BOOLEAN 컬럼 타입을 지원하고 있어요. 그래서 [TRUE | FALSE] 또는 [YES | NO] 와 같은 값들을 이용해서 불리언 값을 저장할 수 있어요. 물론 Oracle RDBMS와 같이 불리언 타입을 지원하지 않는 RDBMS도 있어요. 그런데, MySQL 서버는 외관은 BOOLEAN 타입을 지원하는 것처럼 보이지만, 내부적으로는 BOOLEAN 타입을 지원하지 않아서 조금 혼란스러운 경우도 있어요. 이번에는 MySQL 서버의 BOOLEAN 타입 사용에 있어서 실수하지 않는 방법을 살펴보려고 해요.

Boolean Data Type

BOOLEAN 컬럼 활용

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

mysql> SHOW CREATE TABLE users;
CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `enabled` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

BOOL 또는 BOOLEAN 컬럼 타입을 사용해서 테이블을 생성하면, MySQL 서버에서 실제 테이블은 TINYINT(1)로 컬럼이 생성되는 것을 확인할 수 있어요. 그리고 BOOLEAN 타입의 컬럼에 값을 저장할거나 조회할 때는 [TRUE | FALSE] 또는 [1 | 0] 을 사용해요.

mysql> INSERT INTO users VALUES (1, 'Matt', TRUE);
mysql> INSERT INTO users VALUES (2, 'Lara', 0);

mysql> SELECT * FROM users;
+----+------+---------+
| id | name | enabled |
+----+------+---------+
|  1 | Matt |       1 |
|  2 | Lara |       0 |
+----+------+---------+

mysql> SELECT * FROM users WHERE enabled=TRUE;
+----+------+---------+
| id | name | enabled |
+----+------+---------+
|  1 | Matt |       1 |
+----+------+---------+

mysql> SELECT * FROM users WHERE enabled=1;
+----+------+---------+
| id | name | enabled |
+----+------+---------+
|  1 | Matt |       1 |
+----+------+---------+

WHERE 절의 BOOLEAN 타입 사용

WHERE 조건절에 BOOLEAN 타입 컬럼을 사용하는 경우, 반드시 [TRUE | FALSE] 또는 [1 | 0]만을 사용해야 해요. 그렇지 않은 경우 원하는 검색 결과가 나오지 않을 수 있어요. C/C++ 언어에서와 같이 0은 FALSE이며 0이 아닌 값은 TRUE로 판독하는 경우도 있지만, WHERE 절에서 BOOLEAN 컬럼에 대해서 정수 값으로 비교 조건을 사용할 때에는 0 또는 1로만 비교할 수 있어요.

mysql> SELECT * FROM users WHERE enabled=0 /* FALSE */;
+----+------+---------+
| id | name | enabled |
+----+------+---------+
|  2 | Lara |       0 |
+----+------+---------+

mysql> SELECT * FROM users WHERE enabled=1 /* TRUE */;
+----+------+---------+
| id | name | enabled |
+----+------+---------+
|  1 | Matt |       1 |
+----+------+---------+

mysql> SELECT * FROM users WHERE enabled=2 /* 정수 2는 TRUE도 아니고 FALSE도 아님 */;
Empty set (0.01 sec)

BOOLEAN 컬럼의 인덱스 활용

BOOLEAN 타입 컬럼의 경우, 다음과 같이 비교 상수 값 없이 컬럼만 WHERE 절에 명시해서 사용할 수도 있어요. 컬럼이 이미 BOOLEAN 타입이므로, 아무런 문제가 없어 보이고, 쿼리 결과도 기대했던 대로 출력되는 것을 확인할 수 있어요.

mysql> SELECT * FROM users WHERE enabled;
+----+------+---------+
| id | name | enabled |
+----+------+---------+
|  1 | Matt |       1 |
+----+------+---------+

그럼 이제 enabled 컬럼에 인덱스를 생성하고, 위의 쿼리가 어떤 실행 계획으로 처리되는지를 한번 살펴볼게요.

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

-- // 테스트를 위해서 적절히 테스트용 레코드를 INSERT 해둡니다.
mysql> SELECT COUNT(*) FROM users;
+----------+
| COUNT(*) |
+----------+
|   786272 |
+----------+

mysql> EXPLAIN SELECT * FROM users WHERE enabled AND name='Matt';
+----+-------------+-------+-------+-----------------+---------+------+------+----------+-----------------------+
| id | select_type | table | type  | key             | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+-------+-----------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | users | range | ix_enabled_name | 2       | NULL |    2 |    10.00 | Using index condition |
+----+-------------+-------+-------+-----------------+---------+------+------+----------+-----------------------+
Note (Code 1003): /* select#1 */ select `test`.`users`.`id` AS `id`,`test`.`users`.`name` AS `name`,`test`.`users`.`enabled` AS `enabled` from `test`.`users` where ((`test`.`users`.`name` = 'Matt') and (0 <> `test`.`users`.`enabled`))

예상대로 ix_enabled_name 을 사용해서 쿼리가 최적화되는 것을 확인할 수 있어요. 그런데 여기에서 한가지 조금 이상해 보이는 것이 있어요. 실행 계획 하단의 Note를 보면 MySQL 옵티마이저가 파싱된 쿼리를 재조합한 결과를 보면, where (0 <> `test`.`users`.`enabled`) 를 확인할 수 있어요. 이는 쿼리의 WHERE enabled 라는 구문이 WHERE enabled=TRUE 로 해석된 것이 아니라 WHERE enabled<>0 으로 해석되었다는 것을 의미해요.

쿼리의 WHERE 조건절의 enable조건이 부정 조건으로 처되었는데, 어떻게 ix_enabled_name 인덱스를 이용해서 최적화가 될 수 있었을까요 ? 이는 MySQL 서버의 옵티마이저가 enabled<>0 조건을 인덱스를 사용할 수 있는 NULL<enabled<0 AND 0<enabled 조건으로 변환해서 실행했기 때문이에요. 이는 옵티마이저 트레이스 결과를 살펴 보면 쉽게 확인할 수 있어요.

  "chosen_range_access_summary": {
    "range_access_plan": {
      "type": "range_scan",
      "index": "ix_enabled_name",
      "rows": 2,
      "ranges": [
        "NULL < enabled < 0",
        "0 < enabled"
      ] /* ranges */
    } /* range_access_plan */,
    "rows_for_plan": 2,
    "cost_for_plan": 1.21,
    "chosen": true
  } /* chosen_range_access_summary */

그리고 쿼리의 실행 계획에서 key_len이 2바이트인 것을 봐도, enabled 컬럼까지만 인덱스 레인지 스캔으로 읽었다는 것을 알 수 있어요. 실제로는 name='Matt' 조건이 쿼리 성능에 훨씬 더 도움이 되는 조건인데, 이 조건은 쿼리 최적화에 사용되지 못하면서 쿼리의 성능은 떨어지게 되죠.

왜 주의가 필요한가 ?

요즘은 프로젝트에서 ORM을 많이 이용하고 있고, 그만큼 ORM에 대한 신뢰도 높은 편인 것 같아요. 하지만 다양한 ORM들을 경험해보면, ORM을 개발했던 사람들이 다양한 RDBMS에 대해서 깊은 지식을 가지고 있지 않으며 또한 다양한 성능 검증을 거치지 않고 릴리즈되고 있다는 느낌이 많았어요.

GoLang에서 많이 사용되는 ORM Framework인 ent (An Entity Framework For Go)를 이용한 간단한 쿼리 예제를 한번 살펴 볼게요.

package main

import (
    "context"
    "log"
    "fmt"

    "entdemo/ent"
    "entdemo/ent/user"

    _ "github.com/go-sql-driver/mysql"
)

func main() {
    client, err := ent.Open("mysql", "entgo-user:entgo-pass@tcp(127.0.0.1:3306)/test?parseTime=True")
    if err != nil {
        log.Fatalf("Failed to connect : %v", err)
    }
    defer client.Close()
    ctx := context.Background()

    u, err := QueryUser(ctx, client)
    if err != nil {
        log.Fatal(err)
    }

    log.Println("Fetched : ", u)
}


func QueryUser(ctx context.Context, client *ent.Client) (*ent.User, error) {
    u, err := client.User.Query().Where( 
                    user.Enabled(true), 
                    user.Name("Matt") ).Only(ctx)
    if err != nil {
        return nil, fmt.Errorf("Failed to query : %w", err)
    }

    return u, nil
}

이 예제가 실행되면, ent go Framework는 MySQL 서버로 아래와 같은 쿼리를 실행해요.

SELECT `users`.`id`, 
       `users`.`name`, 
       `users`.`enabled`
FROM `users` 
WHERE `users`.`enabled` 
      AND `users`.`name` = 'Matt' 
LIMIT 2

ent go Framework는 WHERE enabled=TRUE 형태의 조건이 아니라, 성능 문제를 야기할 수 있는 WHERE enabled 형태의 쿼리를 생성하는 것을 확인할 수 있어요.

BOOLEAN 컬럼 주의 사항

BOOLEAN 타입으로 컬럼을 생성한다 하더라도, 네이티브 BOOLEAN 타입이 없는 MySQL 서버는 (이미 위에서 확인해본 바와 같이) TINYINT 정수 타입 컬럼으로 생성해요. 뿐만 아니라 TINYINT 컬럼이기 때문에 [0 | 1]만 저장할 수 있는 것이 아니라, -128부터 +127까지의 정수 값을 모두 저장할 수 있어요. 그런데 TINYINT 컬럼에 [0 | 1] 이외의 정수 값을 혼용해서 사용하는 경우 [TRUE | FALSE] 비교는 예상과는 다른 결과를 보여줄 때도 있으니 주의가 필요해요.
간단히 다음 예제를 보면서, 각 쿼리의 예상 결과가 어떤 것이었는지 그리고 출력된 결과와 일치하는지 한번 생각해보면 도움이 될것 같아요.

mysql> INSERT INTO users VALUES (1, 'Matt', TRUE);
mysql> INSERT INTO users VALUES (2, 'Lara', FALSE);
mysql> INSERT INTO users VALUES (3, 'Shane', 3);
mysql> INSERT INTO users VALUES (4, 'Ford', 4);

mysql> SELECT * FROM users;
+----+-------+---------+
| id | name  | enabled |
+----+-------+---------+
|  1 | Matt  |       1 |
|  2 | Lara  |       0 |
|  3 | Shane |       3 |
|  4 | Ford  |       4 |
+----+-------+---------+

mysql> SELECT * FROM users WHERE enabled;
+----+-------+---------+
| id | name  | enabled |
+----+-------+---------+
|  1 | Matt  |       1 |
|  3 | Shane |       3 |
|  4 | Ford  |       4 |
+----+-------+---------+

mysql> SELECT * FROM users WHERE enabled=TRUE;
+----+------+---------+
| id | name | enabled |
+----+------+---------+
|  1 | Matt |       1 |
+----+------+---------+

mysql> SELECT * FROM users WHERE enabled=FALSE;
+----+------+---------+
| id | name | enabled |
+----+------+---------+
|  2 | Lara |       0 |
+----+------+---------+

결론

가능하다면 MySQL 서버에서는 (네이티브 BOOLEAN 타입을 지원하지 않기 때문에), 1 타입이나 [TRUE | FALSE] 값 보다는 TINYINT 컬럼을 사용하고 [0 | 1] 만을 저장하는 형태를 고려하는 것이 오히려 더 혼란을 방지할 수 있는 방법이 아닐까 싶기도 해요. 그래도 BOOLEAN 타입이나 [TRUE | FALSE]를 사용하고 싶다면, [0 | 1] 이외의 값이 저장되지 않도록 일관성을 유지하는 것이 더 좋아 보여요. 그리고 값의 유효성 체크를 엄격하게 하고자 한다면, ENUM('FALSE','TRUE')과 같은 형태도 고민해볼 가치는 있어 보여요.

그리고 BOOLEAN 타입 컬럼은 ORM 없이 쿼리를 직접 작성해서 사용하는 경우에도 조심해야 하겠지만, ORM을 사용해서 쿼리를 직접 작성하지 않는 경우에는 어떤 쿼리가 실행되는지 보이지 않기 때문에 더 주의가 필요해요. BOOLEAN 타입 케이스뿐만 아니라 여러 케이스에서 ORM 들이 생성하는 쿼리 문장이 성능과는 거리가 먼 형태를 많이 보였어요. 가능하다면, ORM이 자동으로 생성하는 쿼리 문장들의 실행 계획을 직접 확인하는 과정이 필요해 보여요.

당근마켓에서 함께 고민을 나누고 싶다면 여기를 눌러 당근마켓 채용 공고를 확인해보세요!