Mysql Fuction Based Index (함수기반인덱스)

 

쿼리를 짜다보면 두개의 컬럼을 조합하는 조건으로 쿼리를 짜야하는 경우가 있다.

다음과 같이 성과 이름을 조합한 조건값을 조건으로 하였을때

where절 좌변을 concat함수를 통해 두개의 컬럼을 합친 조건을 만들수 가 있다.

그렇지만 이렇경우 index를 탈 수 없는 상황이 발생하게 되어

성능에 큰 영향을 미칠수 있다.

 

아래와 같이 Plan을 보면 Table scan으로 전체의 row를 읽어 성능이슈가 발생하게 된다.

root@localhost:employees 09:14:32>explain format=tree
    -> select * from employees
    -> where concat(first_name, ' ', last_name) = 'Bezalel Simmel';
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                        |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (concat(employees.first_name,' ',employees.last_name) = 'Bezalel Simmel')  (cost=30156.85 rows=299246)
    -> Table scan on employees  (cost=30156.85 rows=299246)
 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

root@localhost:employees 09:15:14>select * from employees
    -> where concat(first_name, ' ', last_name) = 'Bezalel Simmel';
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.30 sec)

root@localhost:employees 09:15:25>

 

이럴때는 아래와 같이 Function Based Index(함수기반)를 활용할 수 있다.

좌변의 함수자체를 index로 만들어서 사용이 가능하다.

root@localhost:employees 09:15:29>create index idx_full_name on employees((concat(first_name, ' ', last_name)));
Query OK, 0 rows affected (1.95 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost:employees 09:15:44>
root@localhost:employees 09:15:48>explain format=tree
    -> select * from employees
    -> where concat(first_name, ' ', last_name) = 'Bezalel Simmel';
+-----------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                           |
+-----------------------------------------------------------------------------------------------------------------------------------+
| -> Index lookup on employees using idx_full_name (concat(first_name,_utf8mb4' ',last_name)='Bezalel Simmel')  (cost=0.35 rows=1)
 |
+-----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost:employees 09:15:56>select * from employees
    -> where concat(first_name, ' ', last_name) = 'Bezalel Simmel';
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.00 sec)

root@localhost:employees 09:16:00>

 

또한 Mysql 8.0부터는 Table에 concat함수를 이용한 두개의 컬럼을 가상 컬럼으로 생성하는 방법도 있다.

실제 두개의 컬럼을 조합한 가상의 컬럼이 생성이 되며 그 가상의 컬럼을 인덱스로 생성이 가능하다.

root@localhost:employees 09:23:16>alter table employees add 
    -> full_name varchar(50) as (concat(first_name, ' ', last_name)) virtual;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost:employees 09:23:52>
root@localhost:employees 09:23:52>desc employees ;
+------------+---------------+------+-----+---------+-------------------+
| Field      | Type          | Null | Key | Default | Extra             |
+------------+---------------+------+-----+---------+-------------------+
| emp_no     | int           | NO   | PRI | NULL    |                   |
| birth_date | date          | NO   |     | NULL    |                   |
| first_name | varchar(14)   | NO   |     | NULL    |                   |
| last_name  | varchar(16)   | NO   |     | NULL    |                   |
| gender     | enum('M','F') | NO   |     | NULL    |                   |
| hire_date  | date          | NO   |     | NULL    |                   |
| full_name  | varchar(50)   | YES  |     | NULL    | VIRTUAL GENERATED |
+------------+---------------+------+-----+---------+-------------------+
7 rows in set (0.01 sec)

root@localhost:employees 09:23:56>
root@localhost:employees 09:23:58>create index idx_full_name on employees(full_name);
Query OK, 0 rows affected (1.84 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost:employees 09:24:18>explain format = tree
    -> select * from employees
    -> where concat(first_name, ' ', last_name) = 'Bezalel Simmel';
+----------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                            |
+----------------------------------------------------------------------------------------------------+
| -> Index lookup on employees using idx_full_name (full_name='Bezalel Simmel')  (cost=0.35 rows=1)
 |
+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost:employees 09:24:29>select * from employees
    -> where concat(first_name, ' ', last_name) = 'Bezalel Simmel';
+--------+------------+------------+-----------+--------+------------+----------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  | full_name      |
+--------+------------+------------+-----------+--------+------------+----------------+
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 | Bezalel Simmel |
+--------+------------+------------+-----------+--------+------------+----------------+
1 row in set (0.01 sec)

root@localhost:employees 09:24:37>