[MySQL] 쿼리 수행시간 분석하는 방법 (EXPLAIN ANALYZE)

MySQL 8.0.18 부터는 EXPLAIN ANALYZE로 쿼리를 분석할 수 있다.

 

EXPLAIN ANALYZE가 기존 MySQL 8.0 부터 제공되던 EXPLAIN FORMAT=TREE 와 다른 점은 실행 계획(estimated cost) 뿐만 아니라 실제 실행했을 때 비용도 같이 보여준다는 점이다.

---

EXPLAIN ANAYLYZE를 사용해 보기 위해 인덱스를 필요로 하는 쿼리에 대해

IGNORE INDEX와 USE INDEX 힌트를 사용해

인덱스를 사용하지 않을 때와 사용할 때 실행 계획을 비교해 봤다.

 

<인덱스를 사용하지 않을 때>

먼저 수행된 단계가 가장 안 쪽으로 indent되어 표시된다.

따라서 먼저 수행된 table scan부터 보면 된다.

• Table Scan 단계)
  • 실행 계획
    
    • cost: 574010
    • rows:5490000
  • 실제 실행 비용
    • actual time: 0.0288...4723
      • actual time에서 앞의 숫자는 첫번째 row를 가져오는 걸린 시간(millisecond 단위)이고 뒤 숫자는 전체 rows를 가져오는데 걸린시간을 의미한다.
      • 여기서는 전체를 가져오는 데 대략 4.7초 정도 걸렸다.
    • rows: 5860000
    • loops=1
      • nested loop join과 같이 특정 데이터를 여러 loop으로 반복해서 읽지 않았으므로 loop은 1이다.
• Filter 단계)
  • 실행 계획
    
    • cost: 574010
    • rows: 593
  • 실제 실행 비용
    • actual time: 0.0425...6518
    • rows: 3460000
    • loops=1

Table Scan 단계에서 5860000 rows를 읽지만, filter 단계에서 실제 사용하는 rows는 3460000으로 240000만큼 데이터는 쓰이지 않고 버려졌다. (여기서 비효율을 발견하고, 인덱스를 만들어야 겠다고 생각했다!)

 

재밌는거는 실행 계획과 실제 실행 비용이 생각보다 차이가 많이 난다는 점이다.

실행 계획은 DB에 쌓인 통계 정보를 바탕으로 optimizer가 미리 계산해 놓은 정보이기 때문에 실제와는 차이가 있다.

 

<인덱스를 사용할 때>

• 실행 계획
  • cost: 348285
  • rows: 2.75e+6
• 실제 실행 비용
  • actual time: 0.0389..14732
  • rows: 3.46e+6
  • loops=1

인덱스를 사용하게 했더니

실행 계획에 index look up이라고 표시가 되었다.

 

실제 수행 시간으로 비교해 보면 인덱스 쓰지 않을때 14초 걸리던 쿼리가 인덱스를 쓰니 6초만에 수행되었다.