[MariaDB][성능] 스칼라 서브쿼리 실행계획2

[테스트 환경] 

OS : CentOS7 

DB : MariaDB 10.1.12

1. 스칼라 서브쿼리 실행계획 분석

  - 스칼라 서브쿼리에서 사용되는 인덱스를 삭제하면 스토리지 엔진이 DEPT 테이블의 데이터를 MariaDB 엔진으로 전달하고 조인 조건에 맞는 데이터만 필터링한다.

-- PK 삭제
ALTER TABLE DEPT DROP PRIMARY KEY;

ANALYZE  FORMAT=JSON
SELECT   A.EMPNO
        ,A.ENAME
        ,(SELECT   S1.DNAME 
          FROM     DEPT S1 
          WHERE    S1.DEPTNO = A.DEPTNO) AS DNAME
FROM     EMP A;

{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 0.3681,
    "table": {
      "table_name": "A",
      "access_type": "ALL",
      "r_loops": 1,
      "rows": 14,
      "r_rows": 14,
      "r_total_time_ms": 0.0458,
      "filtered": 100,
      "r_filtered": 100
    },
    "subqueries": [
      {
        "expression_cache": {
          "r_loops": 14,
          "r_hit_ratio": 78.571,
          "query_block": {
            "select_id": 2,
            "r_loops": 3,
            "r_total_time_ms": 0.0529,
            "table": {
              "table_name": "S1",
              "access_type": "ALL",
              "r_loops": 3,
              "rows": 4,
              "r_rows": 4,
              "r_total_time_ms": 0.0263,
              "filtered": 100,
              "r_filtered": 25,
              "attached_condition": "(S1.DEPTNO = A.DEPTNO)"
            }
          }
        }
      }
    ]
  }
}

2. 스칼라 서브쿼리와 OUTER 조인 비교

  - 스칼라서브쿼리는 캐싱효과로 인해 테이블 액세스가 6번 발생했으며, OUTER 조인은 Driving 테이블의 데이터 건수만큼 Driven 테이블을 액세스 한다. 여기서 r_rows는 MGR이 NULL값인 데이터가 1건이 존재해서 (MGR이 NULL값이 아닌 데이터 / 전체 데이터 = 13/14 ) 0.9286 으로 출력되었다. MGR에 NULL값이 존재하지 않도록 만들면 다음과 같이 출력된다.

-- 스칼라서브쿼리
ANALYZE   FORMAT=JSON
SELECT    A.*
         ,(SELECT    S1.ENAME 
           FROM      EMP S1 
           WHERE     S1.EMPNO = A.MGR) AS MGR_NM
FROM      EMP A;

{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 0.1144,
    "table": {
      "table_name": "A",
      "access_type": "ALL",
      "r_loops": 1,
      "rows": 14,
      "r_rows": 14,
      "r_total_time_ms": 0.0271,
      "filtered": 100,
      "r_filtered": 100
    },
    "subqueries": [
      {
        "expression_cache": {
          "r_loops": 14,
          "r_hit_ratio": 50,
          "query_block": {
            "select_id": 2,
            "r_loops": 7,
            "r_total_time_ms": 0.0217,
            "table": {
              "table_name": "S1",
              "access_type": "eq_ref",
              "possible_keys": ["PRIMARY"],
              "key": "PRIMARY",
              "key_length": "4",
              "used_key_parts": ["EMPNO"],
              "ref": ["DA.A.MGR"],
              "r_loops": 6,
              "rows": 1,
              "r_rows": 1,
              "r_total_time_ms": 0.0132,
              "filtered": 100,
              "r_filtered": 100
            }
          }
        }
      }
    ]
  }
}

-- OUTER 조인
ANALYZE   FORMAT=JSON
SELECT    A.*
         ,B.ENAME AS MGR_NM
FROM      EMP A
          LEFT OUTER JOIN EMP B ON(B.EMPNO = A.MGR);
          
{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 0.0957,
    "const_condition": "1",
    "table": {
      "table_name": "A",
      "access_type": "ALL",
      "r_loops": 1,
      "rows": 14,
      "r_rows": 14,
      "r_total_time_ms": 0.038,
      "filtered": 100,
      "r_filtered": 100
    },
    "table": {
      "table_name": "B",
      "access_type": "eq_ref",
      "possible_keys": ["PRIMARY"],
      "key": "PRIMARY",
      "key_length": "4",
      "used_key_parts": ["EMPNO"],
      "ref": ["DA.A.MGR"],
      "r_loops": 14,
      "rows": 1,
      "r_rows": 0.9286,
      "r_total_time_ms": 0.0252,
      "filtered": 100,
      "r_filtered": 100,
      "attached_condition": "trigcond(trigcond((A.MGR is not null)))"
    }
  }
}

3. 조인 대상 컬럼 NOT NULL 값으로 변경

-- MGR에 NULL값이 존재하지 않도록 함
UPDATE    EMP A
SET       A.MGR = 7566
WHERE     A.MGR IS NULL;

ANALYZE   FORMAT=JSON
SELECT    A.*
         ,B.ENAME
FROM      EMP A
          LEFT OUTER JOIN EMP B ON(B.EMPNO = A.MGR);
          

{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 0.1761,
    "const_condition": "1",
    "table": {
      "table_name": "A",
      "access_type": "ALL",
      "r_loops": 1,
      "rows": 14,
      "r_rows": 14,
      "r_total_time_ms": 0.0671,
      "filtered": 100,
      "r_filtered": 100
    },
    "table": {
      "table_name": "B",
      "access_type": "eq_ref",
      "possible_keys": ["PRIMARY"],
      "key": "PRIMARY",
      "key_length": "4",
      "used_key_parts": ["EMPNO"],
      "ref": ["DA.A.MGR"],
      "r_loops": 14,
      "rows": 1,
      "r_rows": 1,
      "r_total_time_ms": 0.0487,
      "filtered": 100,
      "r_filtered": 100,
      "attached_condition": "trigcond(trigcond((A.MGR is not null)))"
    }
  }
}