MySQL Boolean 컬럼의 진실과 주의점

개요

MySQL에서 Boolean 컬럼 타입을 사용해서 테이블을 생성하게되면 내부적으로는 TINYINT(1) 값으로 생성되는 것을 볼 수 있다. 이렇게 생성된 테이블을 조회하기 위해서는 TRUE/FALSE 또는 1/0의 값을 사용할 수 있다.

 

 

사용방법

1. 테이블 생성

아래와 같이 테이블을 생성해보자

CREATE TABLE employee (
    id INT(10) AUTO_INCREMENT PRIMARY KEY,
    NAME VARCHAR(50),
    enabled BOOL 
);

위와 같이 테이블을 생성해도 실제로 DDL을 보면 TINYINT(1)으로 생성된 것을 볼 수 있다. 내부적으로는 Boolean 타입 자체가 없기때문이다.

mysql> SHOW CREATE TABLE employee

CREATE TABLE `employee` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `enabled` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

2. 데이터 삽입

TRUE/FALSE 값으로 데이터를 삽입해보자

mysql> INSERT INTO employee (NAME, enabled) VALUES ('Cho Yeon Seop', TRUE);
mysql> INSERT INTO employee (NAME, enabled) VALUES ('Hong Kil Dong', FALSE);

이번에는 1/0 값으로 데이터를 삽입해보자

mysql> INSERT INTO employee (NAME, enabled) VALUES ('Cho Yeon Seop-1', 1);
mysql> INSERT INTO employee (NAME, enabled) VALUES ('Hong Kil Dong-1', 0);

이번에는 1/0 이외의 값(예 2,3)으로 데이터를 삽입해보자

mysql> INSERT INTO employee (NAME, enabled) VALUES ('Cho Yeon Seop-2', 2);
mysql> INSERT INTO employee (NAME, enabled) VALUES ('Hong Kil Dong-2', 3);

 

결과는 다음과 같다. 1,0이외의 값도 유효성 체크없이 데이터가 삽입이 된다는 것을 볼 수 있다.

그렇기때문에 Boolean 타입 사용시에는 반드시 주의가 필요하다.

id	name	enabled
1	Cho Yeon Seop	1
2	Hong Kil Dong	0
3	Cho Yeon Seop-1	1
4	Hong Kil Dong-1	0
5	Cho Yeon Seop-2	2
6	Hong Kil Dong-2	3

3.  INDEX 를 걸어보자

Optimizer Trace 사용법은 여기 링크를 통하여 알아볼 수 있다.

# optimizer trace를 "on"으로 사용
mysql> SET SESSION OPTIMIZER_TRACE="enabled=on"
# 실행 계획
mysql> EXPLAIN SELECT * FROM employee WHERE enabled AND NAME='Cho Yeon Seop';
# 실제 실행 될 쿼리
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	employee	range	ix_enabled_name	ix_enabled_name	2	(NULL)	5	Using where; Using index

다음과 같이 WHERE enabled 라는 구문을 보면 WHERE enabled=TRUE가 아닌 WHERE enabled<>0 으로 해석되었다는 것을 볼 수 있다.

"expanded_query": "select employee.`id` AS `id`,employee.`name` AS `name`,employee.enabled AS enabled from employee where employee.enabled <> 0 and employee.`name` = 'Cho Yeon Seop'"

무엇이 문제인가?

key_len=2를 보면 enabled 컬럼까지만 인덱스 레인지 스캔으로 읽었기때문에 실제로는 name='Cho Yeon Seop' 조건이 쿼리 성능에 훨씬 더 도움이 되는 조건인데, 이 조건은 쿼리 최적화에 사용되지 못하면서 쿼리의 성능이 떨어지게 된다.

"chosen_range_access_summary": {
	"range_access_plan": {
	  "type": "range_scan",
	  "index": "ix_enabled_name",
	  "rows": 5,
	  "ranges": ["(NULL) < (enabled) < (0)", "(0) < (enabled)"]
	},
	"rows_for_plan": 5,
	"cost_for_plan": 1.279703998,
	"chosen": true
}

 

이성욱님의 블로그를 보면 조금 더 자세히 파악할 수 있다. 단지 여기서는 검증과 내용을 여기서는 보태는 것이라고 보면 된다.

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

MySQL BOOLEAN 컬럼

 

ORM 사용시 주의점

이성욱님의 블로그를 보면 ORM 프레임워크에 따라서 잘못된 쿼리를 생성할 수 있다는 것이다.

이렇게 되면 1/0이 아닌 다른 값들도 다 조회할 수 있는 리스크가 있다는거다.

ent go Framework는 WHERE enabled=TRUE 형태의 조건이 아니라, 성능 문제를 야기할 수 있는 WHERE enabled 형태의 쿼리를 생성한다고 한다.

Python 위주로 나는 개발하기 때문에 sqlalchemy를 사용하여 증명해보자

from sqlalchemy import create_engine, URL, Boolean, Column, Integer, String, Index
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import declarative_base

Base = declarative_base()

master_url = URL.create(
    "mysql+pymysql",
    username='<YOUR_USER_ID>',
    password='<YOUR_PASSWORD>',
    host='localhost',
    port='3306',
    database='<YOUR_DATABASE>',
)

master_engine = create_engine(master_url, echo=True, pool_pre_ping=True, pool_size=1, max_overflow=3)

Session = sessionmaker(bind=master_engine)
session = Session()

enabled_employees = session.query(Employee).filter_by(enabled=True, name="Cho Yeon Seop").all()

아래 실행된 쿼리를 보면 다행히도 sqlalchemy 패키지는 올바른 쿼리를 실행한 것을 볼 수 있다.

sqlalchemy.engine.Engine SELECT employee.id AS employee_id, employee.name AS employee_name, employee.enabled AS employee_enabled 
FROM employee 

2023-08-07 11:43:50,741 INFO sqlalchemy.engine.Engine SELECT DATABASE()
2023-08-07 11:43:50,741 INFO sqlalchemy.engine.Engine [raw sql] {}
2023-08-07 11:43:50,746 INFO sqlalchemy.engine.Engine SELECT @@sql_mode
2023-08-07 11:43:50,746 INFO sqlalchemy.engine.Engine [raw sql] {}
2023-08-07 11:43:50,748 INFO sqlalchemy.engine.Engine SELECT @@lower_case_table_names
2023-08-07 11:43:50,748 INFO sqlalchemy.engine.Engine [raw sql] {}
2023-08-07 11:43:50,751 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-08-07 11:43:50,755 INFO sqlalchemy.engine.Engine SELECT employee.id AS employee_id, employee.name AS employee_name, employee.enabled AS employee_enabled 
FROM employee 
WHERE employee.enabled = true AND employee.name = %(name_1)s
2023-08-07 11:43:50,755 INFO sqlalchemy.engine.Engine [generated in 0.00098s] {'name_1': 'Cho Yeon Seop'}

 

결론

• 데이터 유효성에서 주의가 필요하기때문에 Boolean타입이 아닌 ENUM('FALSE','TRUE')형태도 고민할 수 있다.
• ORM 사용시 잘못된 쿼리를 생성할 수 있기때문에 반드시 확인이 필요하다.
• 내부적으로 다중 인덱스 실행시 쿼리 최적화를 잘 실행하지 못하는 단점이 있을 수 있다.

반응형