MySQL 5.7 Virtual Columns

슬로우 쿼리를 보다보니 function based index 를 써야되는 쿼리가 있다.

 

문제는 5.7 에서는 지원 안한다. 그럼 Virtual Columns을 쓰자.

# 테스트 테이블 생성 
mysql> create table _numbers(num int auto_increment primary key);
Query OK, 0 rows affected (0.02 sec)

# 테스트 데이터 입력 1 ~ 100000
mysql> insert into _numbers 
    -> select null 
    -> FROM information_schema.COLUMNS t1
    ->     JOIN information_schema.COLUMNS t2
    ->     JOIN information_schema.COLUMNS t3
    ->     LIMIT 100000;
Query OK, 100000 rows affected (0.92 sec)
Records: 100000  Duplicates: 0  Warnings: 0



# num 끝자리가 1인걸 검색하는 쿼리 (이런 형태의 쿼리가 날라온다.)
mysql> explain select * from _numbers where num like '%1';
+----+-------------+----------+-------+---------+---------+------+--------+----------+--------------------------+
| id | select_type | table    | type  | key     | key_len | ref  | rows   | filtered | Extra                    |
+----+-------------+----------+-------+---------+---------+------+--------+----------+--------------------------+
|  1 | SIMPLE      | _numbers | index | PRIMARY | 4       | NULL | 100575 |    11.11 | Using where; Using index |
+----+-------------+----------+-------+---------+---------+------+--------+----------+--------------------------+



# Virtual Columns 추가.. 안된다.  왜? 에러메시지를 봐라..
mysql> alter table _numbers  add num_mod int generated always as (num % 10) virtual not null;
ERROR 3109 (HY000): Generated column 'num_mod' cannot refer to auto-increment column.



# num 컬럼에 auto_increment 속성을 제거한다. 
mysql> alter table _numbers modify num int not null;
Query OK, 100000 rows affected (0.80 sec)
Records: 100000  Duplicates: 0  Warnings: 0



# Virtual Columns 추가 된다! 
mysql> alter table _numbers  add num_mod int generated always as (num % 10) virtual not null;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0



# 인덱스도 걸고
mysql> alter table _numbers add index idx__numbers_num_mod (num_mod);
Query OK, 0 rows affected (0.37 sec)
Records: 0  Duplicates: 0  Warnings: 0



# 뚜둥 실행계획 
mysql> explain select * from _numbers where num_mod= 1;
+----+-------------+----------+------+----------------------+---------+-------+-------+----------+-------------+
| id | select_type | table    | type | key                  | key_len | ref   | rows  | filtered | Extra       |
+----+-------------+----------+------+----------------------+---------+-------+-------+----------+-------------+
|  1 | SIMPLE      | _numbers | ref  | idx__numbers_num_mod | 4       | const | 10000 |   100.00 | Using index |
+----+-------------+----------+------+----------------------+---------+-------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)