[MariaDB][성능]인라인뷰 처리 방식 분석

MariaDB/SQL Tuning

[MariaDB][성능]인라인뷰 처리 방식 분석

DBA_JSH 2016. 8. 17. 08:49

[테스트 환경]

- OS : CentOS7

- DB : MariaDB 10.1.12

- 테이블 : EMP 10만건, DEPT 4건

1. 인라인 뷰의 최적화

- 오라클과 마찬가지로 인라인뷰로 쿼리를 작성하면 View Merge가 발생하여 조인 조건으로 최적화 한다.

1.1 인라인뷰에서 조회 조건 없고, 조인 컬럼의 인덱스 미존재

- 건수가 작은 DEPT 테이블을 DRIVING 테이블로 TABLE FULL SCAN 하고, DRIVEN 테이블인 EMP 테이블과 block-nl-join방식으로 조인 한다.

- EMP테이블에 DEPT컬럼으로 인덱스가 미존재 하기 때문에 DEPT의 데이터를 JOIN BUFFER에 올려서 EMP 테이블의 데이터와 매칭 되는지 하나씩 체크하고, 매칭 되는 결과를 리턴 한다.

[SQL1]

ANALYZE FORMAT=JSON
SELECT   A.EMPNO 
        ,A.ENAME
        ,B.DNAME 
FROM  (SELECT   A.EMPNO
               ,A.ENAME
               ,A.JOB
               ,A.DEPTNO
       FROM     EMP A ) A 
      INNER JOIN DEPT B ON (A.DEPTNO = B.DEPTNO);

-- JSON 실행 계획 및 실행 결과 
{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 113.1,
    "table": {
      "table_name": "B",
      "access_type": "ALL",
      "possible_keys": ["PRIMARY"],
      "r_loops": 1,
      "rows": 4,
      "r_rows": 4,
      "r_total_time_ms": 0.2849,
      "filtered": 100,
      "r_filtered": 100
    },
    "block-nl-join": {
      "table": {
        "table_name": "A",
        "access_type": "ALL",
        "r_loops": 1,
        "rows": 99212,
        "r_rows": 100000,
        "r_total_time_ms": 85.382,
        "filtered": 100,
        "r_filtered": 100
      },
      "buffer_type": "flat",
      "buffer_size": "2048Kb",
      "join_type": "BNL",
      "attached_condition": "(A.DEPTNO = B.DEPTNO)",
      "r_filtered": 25
    }
  }
}

1.2 인라인뷰에서 인덱스 미존재 컬럼 조건 절 추가(JOB), 조인 컬럼의 인덱스 미존재

- [SQL1]과 전체적으로 동일하지만, EMP테이블의 조건절에 JOB컬럼이 사용되었지만, 인덱스가 미존재하기 때문에 성능에 영향을 미치지 못한다.

[SQL2]

ANALYZE FORMAT=JSON
SELECT   A.EMPNO 
        ,A.ENAME
        ,B.DNAME 
FROM  (SELECT   A.EMPNO
               ,A.ENAME
               ,A.JOB
               ,A.DEPTNO
       FROM     EMP A 
       WHERE    A.JOB ='CLERK') A 
      INNER JOIN DEPT B ON (A.DEPTNO = B.DEPTNO);

{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 98.591,
    "table": {
      "table_name": "B",
      "access_type": "ALL",
      "possible_keys": ["PRIMARY"],
      "r_loops": 1,
      "rows": 4,
      "r_rows": 4,
      "r_total_time_ms": 0.2456,
      "filtered": 100,
      "r_filtered": 100
    },
    "block-nl-join": {
      "table": {
        "table_name": "A",
        "access_type": "ALL",
        "r_loops": 1,
        "rows": 99212,
        "r_rows": 100000,
        "r_total_time_ms": 67.104,
        "filtered": 100,
        "r_filtered": 100,
        "attached_condition": "(A.JOB = 'CLERK')"
      },
      "buffer_type": "flat",
      "buffer_size": "2048Kb",
      "join_type": "BNL",
      "attached_condition": "(A.DEPTNO = B.DEPTNO)",
      "r_filtered": 25
    }
  }
}

1.3 인라인뷰에서 조건 조건 컬럼에 인덱스 추가(JOB), 조인 컬럼의 인덱스 미존재

- EMP테이블의 조건인 JOB컬럼에 인덱스를 추가 한 후에는 EMP테이블을 DRIVING 테이블로 읽는다. JOB컬럼의 선택도가 20%이상이 되기 때문에, [SQL2]보다 더 나쁜 성능을 보인다.

[SQL3]

-- 인덱스 추가
CREATE INDEX EMP_JOB_IDX01 ON EMP(JOB);

ANALYZE FORMAT=JSON
SELECT   A.EMPNO 
        ,A.ENAME
        ,B.DNAME 
FROM  (SELECT   A.EMPNO
               ,A.ENAME
               ,A.JOB
               ,A.DEPTNO
       FROM     EMP A 
       WHERE    A.JOB ='CLERK') A 
      INNER JOIN DEPT B ON (A.DEPTNO = B.DEPTNO);
{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 12012,
    "table": {
      "table_name": "A",
      "access_type": "ref",
      "possible_keys": ["EMP_JOB_IDX01"],
      "key": "EMP_JOB_IDX01",
      "key_length": "30",
      "used_key_parts": ["JOB"],
      "ref": ["const"],
      "r_loops": 1,
      "rows": 49780,
      "r_rows": 40000,
      "r_total_time_ms": 11026,
      "filtered": 100,
      "r_filtered": 100,
      "index_condition": "(A.JOB = 'CLERK')"
    },
    "block-nl-join": {
      "table": {
        "table_name": "B",
        "access_type": "ALL",
        "possible_keys": ["PRIMARY"],
        "r_loops": 1,
        "rows": 4,
        "r_rows": 4,
        "r_total_time_ms": 934.01,
        "filtered": 75,
        "r_filtered": 100
      },
      "buffer_type": "flat",
      "buffer_size": "2048Kb",
      "join_type": "BNL",
      "attached_condition": "(B.DEPTNO = A.DEPTNO)",
      "r_filtered": 25
    }
  }
}

1.4 인라인뷰에서 조건 조건 컬럼에 인덱스 추가(JOB), 조인 컬럼의 인덱스 미존재

- EMP테이블의 조회 조건인 EMPNO컬럼은 PK컬럼이며 UNIQUE INDEX이다. 따라서 DRIVING 테이블로 읽고 DEPT테이블을 DRIVEN테이블로 읽는다. 이전 쿼리들과 다른 점은 조인 조건인 DEPT 컬럼에 여전히 인덱스가 존재 하지 않지만, EMP 테이블에서 1건만 읽고 DEPT 테이블로 넘기기 때문에 block-nl-join를 사용하지 않는다.

[SQL4]

ANALYZE FORMAT=JSON
SELECT   A.EMPNO 
        ,A.ENAME
        ,B.DNAME 
FROM  (SELECT   A.EMPNO
               ,A.ENAME
               ,A.JOB
               ,A.DEPTNO
       FROM     EMP A 
       WHERE    A.EMPNO =101) A 
      INNER JOIN DEPT B ON (A.DEPTNO = B.DEPTNO);

{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 0.0093,
    "table": {
      "table_name": "A",
      "access_type": "const",
      "possible_keys": ["PRIMARY", "EMP_EMPNO_IDX01", "EMP_EMPNO_IDX02"],
      "key": "PRIMARY",
      "key_length": "4",
      "used_key_parts": ["EMPNO"],
      "ref": ["const"],
      "r_loops": 0,
      "rows": 1,
      "r_rows": null,
      "filtered": 100,
      "r_filtered": null
    },
    "table": {
      "table_name": "B",
      "access_type": "const",
      "possible_keys": ["PRIMARY"],
      "key": "PRIMARY",
      "key_length": "4",
      "used_key_parts": ["DEPTNO"],
      "ref": ["const"],
      "r_loops": 0,
      "rows": 1,
      "r_rows": null,
      "filtered": 100,
      "r_filtered": null
    }
  }
}

1.4 인라인 뷰의 임시 테이블 처리 방식

- GROUP BY 또는 함수 사용 할 경우, 임시 테이블을 사용하여 처리 한다.

[SQL5]

ANALYZE FORMAT=JSON
SELECT   A.EMPNO 
        ,B.DNAME 
FROM  (SELECT   A.EMPNO
               ,COUNT(A.EMPNO)
              ,A.DEPTNO 
       FROM     EMP A 
       WHERE    A.JOB ='CLERK'
       GROUP BY A.EMPNO,A.DEPTNO ) A 
      INNER JOIN DEPT B ON (A.DEPTNO = B.DEPTNO);

{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 900.18,
    "table": {
      "table_name": "B",
      "access_type": "ALL",
      "possible_keys": ["PRIMARY"],
      "r_loops": 1,
      "rows": 4,
      "r_rows": 4,
      "r_total_time_ms": 16.25,
      "filtered": 100,
      "r_filtered": 100
    },
    "table": {
      "table_name": ,
      "access_type": "ref",
      "possible_keys": ["key0"],
      "key": "key0",
      "key_length": "5",
      "used_key_parts": ["DEPTNO"],
      "ref": ["DA.B.DEPTNO"],
      "r_loops": 4,
      "rows": 497,
      "r_rows": 10000,
      "r_total_time_ms": 5.7783,
      "filtered": 100,
      "r_filtered": 100,
      "materialized": {
        "query_block": {
          "select_id": 2,
          "r_loops": 1,
          "r_total_time_ms": 879.66,
          "filesort": {
            "r_loops": 1,
            "r_total_time_ms": 6.5184,
            "r_used_priority_queue": false,
            "r_output_rows": 40000,
            "r_buffer_size": "976Kb",
            "temporary_table": {
              "table": {
                "table_name": "A",
                "access_type": "ref",
                "possible_keys": ["EMP_JOB_IDX01"],
                "key": "EMP_JOB_IDX01",
                "key_length": "30",
                "used_key_parts": ["JOB"],
                "ref": ["const"],
                "r_loops": 1,
                "rows": 49780,
                "r_rows": 40000,
                "r_total_time_ms": 847.77,
                "filtered": 100,
                "r_filtered": 100,
                "index_condition": "(A.JOB = 'CLERK')",
                "attached_condition": "((A.JOB <=> 'CLERK'))"
              }
            }
          }
        }
      }
    }
  }
}

'MariaDB > SQL Tuning' 카테고리의 다른 글

[MariaDB][성능] LEFT OUTER JOIN과 스칼라 서브쿼리 실행계획 비교1 (0)	2016.08.24
[MariaDB][성능] 스칼라 서브쿼리 실행계획2 (0)	2016.08.17
[MariaDB][성능] 스칼라 서브쿼리 실행계획 (0)	2016.08.15
[MariaDB][성능] order by 실행계획 (0)	2016.08.10
[MariaDB][성능] 실행계획 분석2 (0)	2016.08.08

현재글[MariaDB][성능]인라인뷰 처리 방식 분석

aDBanced Team

MongoDB #WiredTiger,

Today :
Yesterday :

aDBanced Team