MariaDB - 실행계획 중 Using where 에 대해서...

MariaDB / Mysql 실행계획을 간단히 정리해보자면 아래와 같습니다.

 

-. 효율적인 플랜

1. const
2. ref
3. eq_ref
4. range

-. 비효율적인 플랜

1. derived : from 절에 사용된 서브쿼리로부터 생성된 인시 테이블 => 가능하면 JOIN으로 풀도록 수정
2. uncacheable subquery : MariaDB의 옵티마이저는 서브쿼리를 최대한 캐싱하여 재사용되도록 유도하지만
   사용자 변수나 함수가 사용되면 이러한 캐시기능을 사용할 수 없음
3. dependent subquery : 외부 쿼리에서 값을 전달 받아 실행되는 서브쿼리의 경우가 해당됨.
   이런 경우 서브쿼리가 먼저 실행되지 못하고 서브쿼리 외부의 결과값에 의존적이기 때문에 성능이 저하됨
4. ALL / index : ALL은 풀테이블 스캔 / index 는 인덱스 풀 스캔으로 OLTP 환경에서는 적합하지 않은 경우가 많음

 

 

이번 글에서 살펴볼 실행계획은 "Using where" 입니다.

 

 

 

MariaDB / Mysql 은 크게 MariaDB 엔진과 스토리지 엔진, 두 개의 레이어로 나뉨

스토리지 엔진에서는 디스크나 메모리 상에서 필요한 레코드를 읽어오고 MariaDB 엔진은 스토리지 엔진으로 부터 받은 레코드를 연산하는 작업을 수행함

 

* 스토리지 엔진 레벨에서 처리 될 때 실행계획에서 Using index(커버링 인덱스) 로 풀리고  MariaDB 엔진으로 올라와 필터링 될 때 Using where 로 풀림

Using where 은 흔한 플랜이지만 처리한 rows 수와 실제 결과값의 차이가 많이 난다면 스토리지 엔진에서 불필요하게 너무 많은 데이터에 access 한다는 의미이므로

인덱스 설계가 제대로 되지않았을 가능성이 높음

 

-. Using where

 

MariaDB [employees]> explain extended select * from employees where emp_no  between 10001 and 10100 and gender='F';
+------+-------------+-----------+-------+---------------+---------+---------+------+------+----------+-------------+
| id   | select_type | table     | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+------+-------------+-----------+-------+---------------+---------+---------+------+------+----------+-------------+
|    1 | SIMPLE      | employees | range | PRIMARY       | PRIMARY | 4       | NULL |  100 |   100.00 | Using where |
+------+-------------+-----------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.000 sec)

MariaDB [employees]> select count(*) from employees where emp_no  between 10001 and 10100 and gender='F';
+----------+
| count(*) |
+----------+
|       37 |
+----------+
1 row in set (0.000 sec)

=> 실제 결과값은 37 건이지만 스토리지 엔진에서는 100 rows 를 읽어감. MariaDB 엔진에서 63건을 필터링 했다는 의미로 스토리지 엔진에서 불필요한 I/O 가 발생한 상황

 

 

MariaDB [employees]> show status like '%handler%read%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Handler_read_first       | 0     |
| Handler_read_key         | 1     |
| Handler_read_last        | 0     |
| Handler_read_next        | 100   |
| Handler_read_prev        | 0     |
| Handler_read_retry       | 0     |
| Handler_read_rnd         | 0     |
| Handler_read_rnd_deleted | 0     |
| Handler_read_rnd_next    | 0     |
+--------------------------+-------+
9 rows in set (0.001 sec)

=> handler 는 쿼리가 storage 엔진의 데이터에 접근하는 패턴 / 방식으로 

Handler_read_next 는 인덱스 컬럼을 range 방식으로 읽을 때 발생하는 것으로 100번 요청됨

 

 

-. Using index

 

MariaDB [employees]> show index from employees;
+-----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| employees |          0 | PRIMARY      |            1 | emp_no      | A         |      299556 |     NULL | NULL   |      | BTREE      |         |               |
| employees |          1 | ix_firstname |            1 | first_name  | A         |        2582 |     NULL | NULL   |      | BTREE      |         |               
| employees |          1 | ix_hiredate  |            1 | hire_date   | A         |       10698 |     NULL | NULL   |      | BTREE      |         |               |
| employees |          1 | test         |            1 | emp_no      | A         |      299556 |     NULL | NULL   |      | BTREE      |         |               |
| employees |          1 | test         |            2 | gender      | A         |      299556 |     NULL | NULL   |      | BTREE      |         |               |
+-----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.001 sec)

=> emp_no + gender 인덱스 추가 생성

 

 

 

MariaDB [employees]> explain extended select * from employees use index(test) where emp_no  between 10001 and 10100 and gender='F';
+------+-------------+-----------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id   | select_type | table     | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
+------+-------------+-----------+-------+---------------+------+---------+------+------+----------+-----------------------+
|    1 | SIMPLE      | employees | range | test          | test | 5       | NULL |   99 |   100.00 | Using index condition |
+------+-------------+-----------+-------+---------------+------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.006 sec)

=> 통계정보 + range 방식으로 접근 rows 는 99건, 별차이 없지만 Using index 로 풀린 것 확인

 

 

MariaDB [employees]> show status like '%handler%read%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Handler_read_first       | 0     |
| Handler_read_key         | 1     |
| Handler_read_last        | 0     |
| Handler_read_next        | 37    |
| Handler_read_prev        | 0     |
| Handler_read_retry       | 0     |
| Handler_read_rnd         | 0     |
| Handler_read_rnd_deleted | 0     |
| Handler_read_rnd_next    | 0     |
+--------------------------+-------+
9 rows in set (0.001 sec)

=> Handler_read_next 37건 요청된 것 확인.