꿈꾸는 개발자, DBA 커뮤니티 구루비

5.8 인덱스 머지(index_merge) (MariaDB)(MySQL)

인덱스를 이용하여 테이블을 읽는 경우 대부분은 하나의 인덱스만 활용할 수 있다.
인덱스 머지 실행계획을 사용하면 하나의 테이블에 대해 2개 이상의 인덱스를 이용하게 된다.

SQL 1 : 건수가 적은 쪽 1 개 인덱스만 이용


EXPLAIN
SELECT *
  FROM employees
 WHERE first_name = 'Matt'                              --   233 건
   AND hire_date BETWEEN '1995-01-01' AND '2000-01-01'  -- 34028 건
;
+------+-------------+-----------+------+--------------------------+--------------+---------+-------+------+------------------------------------+
| id   | select_type | table     | type | possible_keys            | key          | key_len | ref   | rows | Extra                              |
+------+-------------+-----------+------+--------------------------+--------------+---------+-------+------+------------------------------------+
|    1 | SIMPLE      | employees | ref  | ix_firstname,ix_hiredate | ix_firstname | 44      | const |  233 | Using index condition; Using where |
+------+-------------+-----------+------+--------------------------+--------------+---------+-------+------+------------------------------------+

SQL 2 : 둘다 건수가 많은데 인덱스 머지 발생 안함


EXPLAIN
SELECT *
  FROM employees
 WHERE first_name BETWEEN 'A' AND 'B'                   -- 22039 건
   AND hire_date BETWEEN '1995-01-01' AND '2000-01-01'  -- 34028 건
;
+------+-------------+-----------+-------+--------------------------+--------------+---------+------+-------+------------------------------------+
| id   | select_type | table     | type  | possible_keys            | key          | key_len | ref  | rows  | Extra                              |
+------+-------------+-----------+-------+--------------------------+--------------+---------+------+-------+------------------------------------+
|    1 | SIMPLE      | employees | range | ix_firstname,ix_hiredate | ix_firstname | 44      | NULL | 43234 | Using index condition; Using where |
+------+-------------+-----------+-------+--------------------------+--------------+---------+------+-------+------------------------------------+

SQL 3 : sort_intersection 알고리즘 활성화 > 2개 인덱스 모두 이용


MariaDB [employees]> SET optimizer_switch = 'index_merge_sort_intersection = on';    -- 공백 때문에 에러 발생
ERROR 1231 (42000): Variable 'optimizer_switch' can't be set to the value of 'index_merge_sort_intersection = on'
MariaDB [employees]> SET optimizer_switch = 'index_merge_sort_intersection=on';      -- 공백 지우고 정상 수행
Query OK, 0 rows affected (0.00 sec)
EXPLAIN
SELECT *
  FROM employees
 WHERE first_name BETWEEN 'A' AND 'B'                   -- 22039 건
   AND hire_date BETWEEN '1995-01-01' AND '2000-01-01'  -- 34028 건
;
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+-------------------------------------------------------------+
| id   | select_type | table     | type        | possible_keys            | key                      | key_len | ref  | rows | Extra                                                       |
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+-------------------------------------------------------------+
|    1 | SIMPLE      | employees | index_merge | ix_firstname,ix_hiredate | ix_firstname,ix_hiredate | 44,3    | NULL | 9912 | Using sort_intersect(ix_firstname,ix_hiredate); Using where |
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+-------------------------------------------------------------+

5.8.1 Using union

SQL 1 : 2개의 이퀄 조건 OR 로 연결 > index merge


EXPLAIN
SELECT *
  FROM employees
 WHERE first_name = 'Matt'        -- 233 건
    OR hire_date  = '1987-03-31'  -- 111 건
;
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+----------------------------------------------------+
| id   | select_type | table     | type        | possible_keys            | key                      | key_len | ref  | rows | Extra                                              |
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+----------------------------------------------------+
|    1 | SIMPLE      | employees | index_merge | ix_firstname,ix_hiredate | ix_firstname,ix_hiredate | 44,3    | NULL |  344 | Using union(ix_firstname,ix_hiredate); Using where |
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+----------------------------------------------------+

SQL 2 : OR 조건 중 1개 조건이 인덱스를 이용하지 못하면? > 나머지 인덱스도 이용 못함


EXPLAIN
SELECT *
  FROM employees
 WHERE first_name LIKE '%Matt'    -- 233 건
    OR hire_date  = '1987-03-31'  -- 111 건
;
+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id   | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
|    1 | SIMPLE      | employees | ALL  | ix_hiredate   | NULL | NULL    | NULL | 299423 | Using where |
+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

SQL 3 : Using union 알고리즘의 숨은 비밀? > 정렬된 결과


SELECT *
  FROM employees
 WHERE first_name = 'Matt'        -- 233 건
    OR hire_date  = '1987-03-31'  -- 111 건
 LIMIT 10
;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10083 | 1959-07-23 | Vishv      | Zockler   | M      | 1987-03-31 |
|  10690 | 1962-09-06 | Matt       | Jumpertz  | F      | 1989-08-22 |
|  12302 | 1962-02-14 | Matt       | Plessier  | M      | 1987-01-28 |
|  13163 | 1963-12-11 | Matt       | Heping    | F      | 1987-03-31 |
|  13233 | 1954-08-11 | Alselm     | Chvatal   | M      | 1987-03-31 |
|  13507 | 1959-09-05 | Matt       | Wallrath  | M      | 1985-06-28 |
|  14004 | 1959-04-10 | Arto       | Wendorf   | M      | 1987-03-31 |
|  15456 | 1957-09-27 | Matt       | Granlund  | M      | 1987-08-17 |
|  15577 | 1961-02-15 | Matt       | Menhardt  | M      | 1985-10-26 |
|  15696 | 1959-05-06 | Matt       | Zhang     | F      | 1987-08-21 |
+--------+------------+------------+-----------+--------+------------+

정렬 없이 어떻게 정렬된 결과를 낼 수 있을까? 또한 양쪽에 중복된 자료는 어떻게 걸러 낼까?

인덱스 검색 결과는 이미 키로 정렬되어 있다.
정렬된 결과를 한건씩 비교하면서 중복 제거하는 깔데기 우선순위 큐(priority Queue)

5.8.2 Using sort_union

SQL 1 : 한쪽 조건이 동등조건이 아닌 경우


EXPLAIN
SELECT *
  FROM employees
 WHERE first_name = 'Matt'                              --  233 건
    OR hire_date  BETWEEN '1987-03-01' AND '1987-03-31' -- 2964 건
;
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+---------------------------------------------------------+
| id   | select_type | table     | type        | possible_keys            | key                      | key_len | ref  | rows | Extra                                                   |
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+---------------------------------------------------------+
|    1 | SIMPLE      | employees | index_merge | ix_firstname,ix_hiredate | ix_firstname,ix_hiredate | 44,3    | NULL | 3196 | Using sort_union(ix_firstname,ix_hiredate); Using where |
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+---------------------------------------------------------+

동등조건이 아니므로 hire_date 인덱스 검색 결과는 emp_no 로 정렬되어 있지 않음
정렬되어 있지 않으므로 우선순위큐 사용 불가. 정렬을 수행하게 됨

5.8.3 Using intersect

SQL 1 : 2개의 동등조건 AND 로 연결


EXPLAIN
SELECT COUNT(*)
  FROM employees
 WHERE first_name = 'Matt'        -- 233 건
   AND hire_date  = '1991-07-22'  --  47 건
;
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+---------------------------------------------------------------------+
| id   | select_type | table     | type        | possible_keys            | key                      | key_len | ref  | rows | Extra                                                               |
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+---------------------------------------------------------------------+
|    1 | SIMPLE      | employees | index_merge | ix_firstname,ix_hiredate | ix_hiredate,ix_firstname | 3,44    | NULL |    1 | Using intersect(ix_hiredate,ix_firstname); Using where; Using index |
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+---------------------------------------------------------------------+

5.8.4 Using sort_intersect (MariaDB)

SQL 1 : 1개의 조건이 동등조건이 아닌 경우


EXPLAIN
SELECT COUNT(*)
  FROM employees
 WHERE first_name LIKE 'Matt%'    -- 233 건
   AND hire_date  = '1991-07-22'  --  47 건
;
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+-------------------------------------------------------------+
| id   | select_type | table     | type        | possible_keys            | key                      | key_len | ref  | rows | Extra                                                       |
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+-------------------------------------------------------------+
|    1 | SIMPLE      | employees | index_merge | ix_firstname,ix_hiredate | ix_hiredate,ix_firstname | 3,44    | NULL |    1 | Using sort_intersect(ix_hiredate,ix_firstname); Using where |
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+-------------------------------------------------------------+