[MariaDB][성능] LEFT OUTER JOIN과 스칼라 서브쿼리 실행계획 비교2

[테스트 환경] 

OS : CentOS7 

DB : MariaDB 10.1.12 

1. 스칼라 서브쿼리 & LEFT OUTER JOIN

  - 메인쿼리의 데이터마다 다른 데이터를 리턴하도록 데이터 수정을 한 후 스칼라 서브쿼리와 LEFT OUTER JOIN의 성능을 비교한다.

  - 메인쿼리의 데이터마다 다른 데이터를 호출하는 쿼리를 작성했을 경우, 서브쿼리는 캐싱효과를 사용할 수 없기 때문에 LEFT OUTER JOIN이 성능이 좋다. 

  - 스칼라 서브쿼리일 때 캐싱할 수 있는 데이터의 개수가 200개가 넘어가면 더이상 캐싱을 하지 않고 상태가 disabled로 바뀐다. 

-- MGR 업데이트
UPDATE    EMP A
SET       A.MGR = CASE WHEN A.EMPNO = 1600000 
                         THEN 1 
                       ELSE A.EMPNO 
                  END + 1;

-- 스칼라 서브쿼리
ANALYZE FORMAT=JSON
SELECT   A.EMPNO
        ,A.ENAME
        ,(SELECT    S1.ENAME
           FROM      EMP S1
           WHERE     S1.EMPNO = A.MGR) AS MANAGER_NAME
FROM     EMP A;
     
{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 2823.1,
    "table": {
      "table_name": "A",
      "access_type": "ALL",
      "r_loops": 1,
      "rows": 1591810,
      "r_rows": 1.6e6,
      "r_total_time_ms": 441.11,
      "filtered": 100,
      "r_filtered": 100
    },
    "subqueries": [
      {
        "expression_cache": {
          "state": "disabled",
          "r_loops": 200,
          "r_hit_ratio": 0,
          "query_block": {
            "select_id": 2,
            "r_loops": 1600000,
            "r_total_time_ms": 1877.7,
            "table": {
              "table_name": "S1",
              "access_type": "eq_ref",
              "possible_keys": ["PRIMARY"],
              "key": "PRIMARY",
              "key_length": "4",
              "used_key_parts": ["EMPNO"],
              "ref": ["DA_DB.A.MGR"],
              "r_loops": 1600000,
              "rows": 1,
              "r_rows": 1,
              "r_total_time_ms": 1033.7,
              "filtered": 100,
              "r_filtered": 100
            }
          }
        }
      }
    ]
  }
}
    
-- LEFT OUTER JOIN    
ANALYZE FORMAT=JSON
SELECT   A.EMPNO
        ,A.ENAME
        ,B.ENAME AS MANAGER_NAME
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": 1788.5,
    "const_condition": "1",
    "table": {
      "table_name": "A",
      "access_type": "ALL",
      "r_loops": 1,
      "rows": 1591810,
      "r_rows": 1.6e6,
      "r_total_time_ms": 421.34,
      "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_DB.A.MGR"],
      "r_loops": 1600000,
      "rows": 1,
      "r_rows": 1,
      "r_total_time_ms": 1001.8,
      "filtered": 100,
      "r_filtered": 100,
      "attached_condition": "trigcond(trigcond((A.MGR is not null)))"
    }
  }
}

2. 캐싱효과 확인 

  - DEPTNO를 204개 전체로 했을 때 캐싱효과가 발생하는지 확인한다.  

  - EMP의 데이터를 액세스 할 때, 처음 200건의 데이터 중, DEPTNO가 중복되어 캐싱된 데이터를 사용할 수 있기 때문에 캐싱효과가 발생한 것으로 보인다. (메인쿼리의 데이터를 몇 건 정도 돌렸을 때 r_hit_ratio이 낮으면 서브쿼리 캐싱을 disable 시키는 것으로 보여진다.)

-- 랜덤으로 DEPTNO 업데이트

UPDATE EMP A
SET  A.DEPTNO = (SELECT S.DEPTNO FROM DEPT S ORDER BY RAND() LIMIT 1);

SELECT    COUNT(A.CNT)
FROM      (SELECT    COUNT(*) AS CNT
           FROM      EMP
           GROUP BY  DEPTNO)A;


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": 983.14,
    "table": {
      "table_name": "A",
      "access_type": "ALL",
      "r_loops": 1,
      "rows": 1607429,
      "r_rows": 1.6e6,
      "r_total_time_ms": 404.35,
      "filtered": 100,
      "r_filtered": 100
    },
    "subqueries": [
      {
        "expression_cache": {
          "r_loops": 1600000,
          "r_hit_ratio": 99.987,
          "query_block": {
            "select_id": 2,
            "r_loops": 204,
            "r_total_time_ms": 1.2702,
            "table": {
              "table_name": "S1",
              "access_type": "eq_ref",
              "possible_keys": ["PRIMARY"],
              "key": "PRIMARY",
              "key_length": "4",
              "used_key_parts": ["DEPTNO"],
              "ref": ["DA_DB.A.DEPTNO"],
              "r_loops": 204,
              "rows": 1,
              "r_rows": 1,
              "r_total_time_ms": 1.0955,
              "filtered": 100,
              "r_filtered": 100
            }
          }
        }
      }
    ]
  }
}

3. DEPTNO 중복에 따른 실행 계획

 - 메인쿼리의 DEPTNO 중복값이 하나 차이지만 위의 쿼리는 캐싱효과가 나타나고 아래쿼리는 캐싱이 disabled 되었다.

-- 메인쿼리의 DEPTNO 중복값이 204개일 때
{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 964.98,
    "table": {
      "table_name": "A",
      "access_type": "ALL",
      "r_loops": 1,
      "rows": 1600000,
      "r_rows": 1.6e6,
      "r_total_time_ms": 414.05,
      "filtered": 100,
      "r_filtered": 100
    },
    "subqueries": [
      {
        "expression_cache": {
          "r_loops": 1600000,
          "r_hit_ratio": 99.987,
          "query_block": {
            "select_id": 2,
            "r_loops": 204,
            "r_total_time_ms": 0.385,
            "table": {
              "table_name": "S1",
              "access_type": "eq_ref",
              "possible_keys": ["PRIMARY"],
              "key": "PRIMARY",
              "key_length": "4",
              "used_key_parts": ["DEPTNO"],
              "ref": ["DA_DB.A.DEPTNO"],
              "r_loops": 204,
              "rows": 1,
              "r_rows": 1,
              "r_total_time_ms": 0.2254,
              "filtered": 100,
              "r_filtered": 100
            }
          }
        }
      }
    ]
  }
}

-- 메인쿼리의 DEPTNO 중복값이 205개일 때
{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 2542.6,
    "table": {
      "table_name": "A",
      "access_type": "ALL",
      "r_loops": 1,
      "rows": 1600000,
      "r_rows": 1.6e6,
      "r_total_time_ms": 443.32,
      "filtered": 100,
      "r_filtered": 100
    },
    "subqueries": [
      {
        "expression_cache": {
          "state": "disabled",
          "r_loops": 205,
          "r_hit_ratio": 2.439,
          "query_block": {
            "select_id": 2,
            "r_loops": 1599995,
            "r_total_time_ms": 1589.8,
            "table": {
              "table_name": "S1",
              "access_type": "eq_ref",
              "possible_keys": ["PRIMARY"],
              "key": "PRIMARY",
              "key_length": "4",
              "used_key_parts": ["DEPTNO"],
              "ref": ["DA_DB.A.DEPTNO"],
              "r_loops": 1599995,
              "rows": 1,
              "r_rows": 1,
              "r_total_time_ms": 852.03,
              "filtered": 100,
              "r_filtered": 100
            }
          }
        }
      }
    ]
  }
}

4. EMP 활용한 스칼라 서브쿼리

  - EMP의 처음 204개의 데이터에 대해서 다른 DEPTNO를 가지도록 데이터 수정

  - 스칼라 서브쿼리에서건이 넘어갈 경우 LEFT OUTER JOIN이 유리하다. 

  - 처음 액세스하는 메인쿼리의 데이터 200건에서 캐싱된 데이터를 사용할 수 없을 경우, MariaDB는 서브쿼리 캐싱을 중단한다. 

-- DEPTNO 업데이트
UPDATE EMP A
SET  A.DEPTNO = A.EMPNO * 10
WHERE A.EMPNO BETWEEN 1 AND 204 ;

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": 2555,
    "table": {
      "table_name": "A",
      "access_type": "ALL",
      "r_loops": 1,
      "rows": 1607429,
      "r_rows": 1.6e6,
      "r_total_time_ms": 448.26,
      "filtered": 100,
      "r_filtered": 100
    },
    "subqueries": [
      {
        "expression_cache": {
          "state": "disabled",
          "r_loops": 200,
          "r_hit_ratio": 0,
          "query_block": {
            "select_id": 2,
            "r_loops": 1600000,
            "r_total_time_ms": 1593.2,
            "table": {
              "table_name": "S1",
              "access_type": "eq_ref",
              "possible_keys": ["PRIMARY"],
              "key": "PRIMARY",
              "key_length": "4",
              "used_key_parts": ["DEPTNO"],
              "ref": ["DA_DB.A.DEPTNO"],
              "r_loops": 1600000,
              "rows": 1,
              "r_rows": 1,
              "r_total_time_ms": 845.67,
              "filtered": 100,
              "r_filtered": 100
            }
          }
        }
      }
    ]
  }
}

5. 데이터 정렬 성능 체크

  - 데이터의 정렬이 성능에 영향을 미치는지 확인

  - 각 테이블의 DEPTNO 컬럼과 IDX03 인덱스에 대해서 통계정보를 수집한다.

  - 인덱스 통계정보 테이블에서(index_stats) 인덱스 컬럼 순서(prefix_arity)를 보면 DEPTNO에 대해서만 인덱스를 생성했는데 2의 값이 나타난다. 이는 MariaDB의 인덱스는 인덱스 컬럼 + PK 컬럼으로 EMPNO가 추가된 것을 확인할 수 있다.

  - avg_frequency는 중복 값을 가진 데이터 수로 204개의 DEPTNO에 대한 데이터를 가진 EMPNO의 수이다.(7843.1373 * 204 = 1600000.0092)

-- EMP 테이블에서 DEPTNO를 정렬하여 EMP_OB 테이블 생성
CREATE TABLE EMP_OB 
AS 
SELECT    A.* 
FROM      EMP A 
ORDER BY  A.DEPTNO ;

-- PRIMARY KEY 생성
ALTER TABLE EMP_OB ADD CONSTRAINT PRIMARY KEY (EMPNO);

-- EMP_OB 테이블에 DEPTNO 인덱스 생성
CREATE INDEX EMP_OB_DEPT_IDX03 ON EMP_OB(DEPTNO);

-- EMP 테이블에 DEPTNO 인덱스 생성
CREATE INDEX EMP_DEPT_IDX03 ON EMP(DEPTNO);


-- 통계정보 수집
MariaDB [(none)]> set global use_stat_tables = 'preferably';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show global variables like 'use_stat_tables';
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| use_stat_tables | PREFERABLY |
+-----------------+------------+
1 row in set (0.00 sec)

MariaDB [(none)]> analyze table DA_DB.EMP_OB PERSISTENT FOR COLUMNS(DEPTNO) INDEXES(EMP_OB_DEPT_IDX03);
+--------------+---------+----------+-----------------------------------------+
| Table        | Op      | Msg_type | Msg_text                                |
+--------------+---------+----------+-----------------------------------------+
| DA_DB.EMP_OB | analyze | status   | Engine-independent statistics collected |
| DA_DB.EMP_OB | analyze | status   | OK                                      |
+--------------+---------+----------+-----------------------------------------+
2 rows in set (1.52 sec)

MariaDB [mysql]> analyze table DA_DB.EMP PERSISTENT FOR COLUMNS(DEPTNO) INDEXES(EMP_DEPT_IDX03);
+-----------+---------+----------+-----------------------------------------+
| Table     | Op      | Msg_type | Msg_text                                |
+-----------+---------+----------+-----------------------------------------+
| DA_DB.EMP | analyze | status   | Engine-independent statistics collected |
| DA_DB.EMP | analyze | status   | OK                                      |
+-----------+---------+----------+-----------------------------------------+
2 rows in set (1.52 sec)


MariaDB [mysql]> select * from index_stats;
+---------+------------+-------------------+--------------+---------------+
| db_name | table_name | index_name        | prefix_arity | avg_frequency |
+---------+------------+-------------------+--------------+---------------+
| DA_DB   | EMP_OB     | EMP_OB_DEPT_IDX03 |            1 |     7843.1373 |
| DA_DB   | EMP_OB     | EMP_OB_DEPT_IDX03 |            2 |        1.0000 |
| DA_DB   | EMP        | EMP_DEPT_IDX03    |            1 |     7843.1373 |
| DA_DB   | EMP        | EMP_DEPT_IDX03    |            2 |        1.0000 |
+---------+------------+-------------------+--------------+---------------+
4 rows in set (0.00 sec)

MariaDB [mysql]> select * from column_stats;
+---------+------------+-------------+-----------+-----------+-------------+------------+---------------+-----------+-----------+-----------+
| db_name | table_name | column_name | min_value | max_value | nulls_ratio | avg_length | avg_frequency | hist_size | hist_type | histogram |
+---------+------------+-------------+-----------+-----------+-------------+------------+---------------+-----------+-----------+-----------+
| DA_DB   | EMP        | DEPTNO      | 10        | 2040      |      0.0000 |     4.0000 |     7843.1373 |         0 | NULL      | NULL      |
| DA_DB   | EMP_OB     | DEPTNO      | 10        | 2040      |      0.0000 |     4.0000 |     7843.1373 |         0 | NULL      | NULL      |
+---------+------------+-------------+-----------+-----------+-------------+------------+---------------+-----------+-----------+-----------+
2 rows in set (0.00 sec)

MariaDB [mysql]> select * from table_stats;
+---------+------------+-------------+
| db_name | table_name | cardinality |
+---------+------------+-------------+
| DA_DB   | EMP        |     1600000 |
| DA_DB   | EMP_OB     |     1600000 |
+---------+------------+-------------+
2 rows in set (0.00 sec)

 6. 스칼라 서브쿼리와 LEFT OUTER JOIN에서 정렬 미 사용

  - 스칼라 서브쿼리일 때 DEPTNO에 대한 인덱스를 타기 때문에 EMP테이블의 처음 204이 각각 다른 DEPTNO를 가지고 있다고 하더라도 캐싱효과가 나타난다. 

  - EMP테이블의 DEPTNO 컬럼 정렬여부가 성능에 영향을 미치지 않는다. -> Buffer pinning 효과가 없다.

-- 스칼라 서브쿼리/EMP.DEPTNO 정렬X
ANALYZE FORMAT=JSON
SELECT   A.EMPNO
         ,(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": 640.77,
    "table": {
      "table_name": "A",
      "access_type": "index",
      "key": "EMP_DEPT_IDX03",
      "key_length": "5",
      "used_key_parts": ["DEPTNO"],
      "r_loops": 1,
      "rows": 1583554,
      "r_rows": 1.6e6,
      "r_total_time_ms": 320.26,
      "filtered": 100,
      "r_filtered": 100,
      "using_index": true
    },
    "subqueries": [
      {
        "expression_cache": {
          "r_loops": 1600000,
          "r_hit_ratio": 99.987,
          "query_block": {
            "select_id": 2,
            "r_loops": 204,
            "r_total_time_ms": 1.9013,
            "table": {
              "table_name": "S1",
              "access_type": "eq_ref",
              "possible_keys": ["PRIMARY"],
              "key": "PRIMARY",
              "key_length": "4",
              "used_key_parts": ["DEPTNO"],
              "ref": ["DA_DB.A.DEPTNO"],
              "r_loops": 204,
              "rows": 1,
              "r_rows": 1,
              "r_total_time_ms": 0.7793,
              "filtered": 100,
              "r_filtered": 100
            }
          }
        }
      }
    ]
  }
}

-- 스칼라 서브쿼리/EMP_OB.DEPTNO 정렬O
ANALYZE FORMAT=JSON
SELECT     A.EMPNO
         ,(SELECT    S1.DNAME
           FROM      DEPT S1
           WHERE     S1.DEPTNO = A.DEPTNO) AS DNAME
FROM     EMP_OB A;

{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 642.06,
    "table": {
      "table_name": "A",
      "access_type": "index",
      "key": "EMP_OB_DEPT_IDX03",
      "key_length": "5",
      "used_key_parts": ["DEPTNO"],
      "r_loops": 1,
      "rows": 1583894,
      "r_rows": 1.6e6,
      "r_total_time_ms": 320.54,
      "filtered": 100,
      "r_filtered": 100,
      "using_index": true
    },
    "subqueries": [
      {
        "expression_cache": {
          "r_loops": 1600000,
          "r_hit_ratio": 99.987,
          "query_block": {
            "select_id": 2,
            "r_loops": 204,
            "r_total_time_ms": 1.9821,
            "table": {
              "table_name": "S1",
              "access_type": "eq_ref",
              "possible_keys": ["PRIMARY"],
              "key": "PRIMARY",
              "key_length": "4",
              "used_key_parts": ["DEPTNO"],
              "ref": ["DA_DB.A.DEPTNO"],
              "r_loops": 204,
              "rows": 1,
              "r_rows": 1,
              "r_total_time_ms": 0.813,
              "filtered": 100,
              "r_filtered": 100
            }
          }
        }
      }
    ]
  }
}

-- LEFT OUTER JOIN/EMP.DEPTNO 정렬X
ANALYZE FORMAT=JSON
SELECT   A.EMPNO
        ,B.DNAME
FROM     EMP A
         LEFT OUTER JOIN DEPT B ON(B.DEPTNO = A.DEPTNO);

{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 620.06,
    "const_condition": "1",
    "table": {
      "table_name": "A",
      "access_type": "index",
      "key": "EMP_DEPT_IDX03",
      "key_length": "5",
      "used_key_parts": ["DEPTNO"],
      "r_loops": 1,
      "rows": 1583554,
      "r_rows": 1.6e6,
      "r_total_time_ms": 318.13,
      "filtered": 100,
      "r_filtered": 100,
      "using_index": true
    },
    "table": {
      "table_name": "B",
      "access_type": "eq_ref",
      "possible_keys": ["PRIMARY"],
      "key": "PRIMARY",
      "key_length": "4",
      "used_key_parts": ["DEPTNO"],
      "ref": ["DA_DB.A.DEPTNO"],
      "r_loops": 1600000,
      "rows": 1,
      "r_rows": 1,
      "r_total_time_ms": 1.4856,
      "filtered": 100,
      "r_filtered": 100,
      "attached_condition": "trigcond(trigcond((A.DEPTNO is not null)))"
    }
  }
}

-- LEFT OUTER JOIN/EMP_OB.DEPTNO 정렬O
ANALYZE FORMAT=JSON
SELECT   A.EMPNO
        ,B.DNAME
FROM     EMP_OB A
         LEFT OUTER JOIN DEPT B ON(B.DEPTNO = A.DEPTNO);

{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 620.2,
    "const_condition": "1",
    "table": {
      "table_name": "A",
      "access_type": "index",
      "key": "EMP_OB_DEPT_IDX03",
      "key_length": "5",
      "used_key_parts": ["DEPTNO"],
      "r_loops": 1,
      "rows": 1583894,
      "r_rows": 1.6e6,
      "r_total_time_ms": 318.61,
      "filtered": 100,
      "r_filtered": 100,
      "using_index": true
    },
    "table": {
      "table_name": "B",
      "access_type": "eq_ref",
      "possible_keys": ["PRIMARY"],
      "key": "PRIMARY",
      "key_length": "4",
      "used_key_parts": ["DEPTNO"],
      "ref": ["DA_DB.A.DEPTNO"],
      "r_loops": 1600000,
      "rows": 1,
      "r_rows": 1,
      "r_total_time_ms": 1.0388,
      "filtered": 100,
      "r_filtered": 100,
      "attached_condition": "trigcond(trigcond((A.DEPTNO is not null)))"
    }
  }
}