[MariaDB][성능] 실행계획 분석[정리필요]

#ERD

(ERD 출처: https://www.ntu.edu.sg/home/ehchua/programming/sql/SampleDatabases.html

 소스 출처: https://code.google.com/archive/p/northwindextended/downloads)

1. 단일 쿼리

explain
select   a.customerid
        ,b.OrderDate
        ,d.ProductName
from     Customers a
         inner join Orders b on(b.customerid = a.customerid)
         inner join `Order Details` c on(c.OrderID = b.OrderID)
         inner join Products d on(d.ProductID = c.ProductID)
where    a.City = 'London';

  - 단일 쿼리이기 때문에 id가 1, select_type이 SIMPLE로 출력된다.

    - 조인 순서 Customers -> Orders -> Order Details -> Products

    - a, b, c의 접근 조건절이 해당 테이블 데이터가 유일한지 여부를 알 수 없기 때문에 type이 ref로 출력되었으며, d의 접근 조건절로 사용된 ProductID는 d의 PK이기 때문에 데이터 한 건이라는 보장이 되어 eq_ref로 출력된다.

  - 옵티마이저가 쿼리를 최적화 시키는데 사용한 인덱스는 possible_keys에서 확인할 수 있으며, 대개 테이블이 가지고 있는 모든 인덱스 리스트가 출력되기 때문에 튜닝에 도움이되지 않는다. 실제로 쿼리를 실행시킬 때 사용된 인덱스는 key 컬럼에서 확인할 수 있다.  

  - ref는 조인 시에 어떤 조건으로 해당 테이블에 액세스되었는지 나타낸다.

    - Customer 테이블에서 City로 조건절이 포함되었기 때문에 const(상수)로 출력

  - key_len은 사용한 인덱스 중 몇 바이트의 데이터를 사용했는지를 알 수 있다.

    - 1. a 테이블의 City 인덱스는 City[varchar(15), nullable] 컬럼의 단일 인덱스로 생성되어 있기 때문에 16바이트 * 3(캐릭터 셋 utf-8) = 48 바이트로 출력된다.

    - 2. b 테이블의 FK_Orders_Customers는 CustomerID[varchar(5),nullable] 컬럼의 단일 인덱스로 생성되어 있기 때문에 6바이트 * 3 = 18 바이트로 출력된다.

    - 3,4. c 테이블 OrderID[int(11)], ProductID[int(11)] -> int는 4바이트로 출력

  - rows는 실제로 출력되는 로우의 수가 아닌, 테이블에서 데이터를 출력하기 위해 디스크에서 읽고 메모리에서 처리한 모든 로우의 수를 의미한다.

  - Extra

    - 1. a 테이블

      - using where -> MariaDB 엔진에서 City가 London인 데이터만 필터링 

      - using index -> 인덱스만 읽고 데이터를 가져옴(innodb의 보조 인덱스는 레코드 주소 값으로 primary 값을 가지기 때문에, City인덱스로 customerid 값을 가져올 수 있다.)

    - 2. c 테이블

      - using index -> 테이블에서 인덱스 데이터 이외의 데이터를 출력하지 않기 때문에 인덱스만 읽고 데이터를 가져옴

2. SUBQUERY

explain
select   a.CustomerID
        ,a.CompanyName
        ,(select  max(s1.OrderDate) 
          from    Orders s1 
          where   s1.CustomerID = a.CustomerID) as recent_order
from     Customers a;

  - select 가 두 번 사용되었기 때문에 id가 1, 2로 출력된다.

    - 1. 메인 쿼리는 select_type가 PRIMARY로 출력되며, 따로 조건절이 없기 때문에 type이 index로 나타나서 index full scan을 한 것을 알 수 있다. select list에 CompanyName이 사용됨에 따라 인덱스를 CompanyName을 사용했고 CustomerID는 PK여서 인덱스 CompanyName에 같이 저장되어 인덱스 액세스만 발생했다.

    - 2. 메인 쿼리에 의존적인 쿼리인 서브쿼리는 DEPENDENT SUBQUERY로 출력된다. 메인 쿼리가 출력되고 난 뒤에 그 값을 받고 출력하기 때문에 일반 조인보다 느리다. Orders 테이블은 한 고객이 여러 주문을 할 수 있기 때문에 데이터가 유일하지 않아서 type이 ref로 출력되었고 FK_Orders_Customers 인덱스를 사용해서 테이블에 액세스 되었다. 

3. DERIVED(INLINE VIEW)

explain
select   a.ProductID
        ,a.cnt
        ,b.ProductName
        ,c.CompanyName
from     (select  a.ProductID
                 ,count(*) as cnt
          from `Order Details` a
          group by a.ProductID) a
          inner join Products b on(b.ProductID=a.ProductID)
          inner join Suppliers c on(c.SupplierID = b.SupplierID)
order by a.ProductID;

  - 조인 순서

    - Suppliers -> Products -> 인라인뷰 Order Detail

    - 1. Supplies 테이블은 CompanyName 인덱스로 인덱스 풀 스캔이 발생했으며, Order Details 테이블에서 사용한 group by로 Using temporary, Using filesort가 발생했다.

    - 2. Products 테이블은 FK_Products_Suppliers 인덱스로 액세스 되었으며, 데이터가 유일하지 않기 때문에 type이 ref로 출력되었다.

    - 3. derived는 인라인 뷰를, 뒤의 숫자는 id를 의미한다. Order Details 테이블은 FK_Order_Details_Products 인덱스 인덱스 풀 스캔되었으며, 테이블 액세스는 발생하지 않았다.

-- 인덱스 생성
alter table `Order Details` add index (ProductID);

explain
select   a.ProductID
        ,a.cnt
        ,b.ProductName
        ,c.CompanyName
from     (select  a.ProductID
                 ,count(*) as cnt
          from `Order Details` a
          group by a.ProductID) a
          inner join Products b on(b.ProductID=a.ProductID)
          inner join Suppliers c on(c.SupplierID = b.SupplierID)
order by a.ProductID;

인덱스 생성 후에도 Using temporary, Using filesort가 발생한다.

-- order by 제거
explain
select   a.ProductID
        ,a.cnt
        ,b.ProductName
        ,c.CompanyName
from     (select  a.ProductID
                 ,count(*) as cnt
          from `Order Details` a
          group by a.ProductID) a
          inner join Products b on(b.ProductID=a.ProductID)
          inner join Suppliers c on(c.SupplierID = b.SupplierID);

order by 절 때문에 Using temporary, Using filesort가 발생했던 것을 확인할 수 있다.

 -- 드라이빙 테이블에서 order by를 할 경우
explain
select   straight_join
         a.ProductID
        ,a.cnt
        ,b.ProductName
        ,c.CompanyName
from     (select  a.ProductID
                 ,count(*) as cnt
          from `Order Details` a
          group by a.ProductID
          order by a.ProductID) a
          inner join Products b on(b.ProductID=a.ProductID)
          inner join Suppliers c on(c.SupplierID = b.SupplierID);

드라이빙 테이블에서 order by를 할 경우, 인덱스를 이용해서 sort작업이 진행된다.

 -- 드라이빙 테이블 컬럼을 메인쿼리에서 order by 할 경우
explain
select   straight_join
         a.ProductID
        ,a.cnt
        ,b.ProductName
        ,c.CompanyName
from     (select  a.ProductID
                 ,count(*) as cnt
          from `Order Details` a
          group by a.ProductID) a
          inner join Products b on(b.ProductID=a.ProductID)
          inner join Suppliers c on(c.SupplierID = b.SupplierID)
order by a.ProductID;

모든 조인 작업을 완료한 후 order by 작업을 진행해서 Using filesort가 발생한 것으로 추측된다.