Mysql 쿼리 튜닝 1

원래 쿼리 -

 

SELECT
      A.userNo,
      A.eventNo,
      A.eventTitle,
      A.eventContent,
      A.startDate,
      A.endDate,
      A.categoryCode,
      A.participantLimit,
      A.noOfParticipants,
      B.registerDate,
      B.cityName,
      B.districtName,
      B.streetName,
      B.detail,
      B.writer
  FROM events AS A
  JOIN
      (SELECT
          E.userNo,
          E.eventNo,
          E.eventTitle,
          E.eventContent,
          E.startDate,
          E.endDate,
          E.categoryCode,
          E.participantLimit,
          E.noOfParticipants,
          E.registerDate,
          EA.cityName,
          EA.districtName,
          EA.streetCode,
          EA.streetName,
          EA.detail,
          M.userName AS writer
      FROM events E
      LEFT OUTER JOIN event_address EA
      ON EA.eventNo = E.eventNo
      LEFT OUTER JOIN category C
      ON C.categoryCode = E.categoryCode
      INNER JOIN members M
      ON E.userNo = M.userNo
      WHERE E.userNo < 1001
      AND E.categoryCode = 1
      ORDER BY registerDate DESC
      LIMIT 10
      ) AS B
  WHERE A.eventNo = B.eventNo;

 

 

DERIVED 제거Permalink

 

SELECT
      E.userNo,
      E.eventNo,
      E.eventTitle,
      E.eventContent,
      E.startDate,
      E.endDate,
      E.categoryCode,
      E.participantLimit,
      E.noOfParticipants,
      E.registerDate,
      EA.cityName,
      EA.districtName,
      EA.streetCode,
      EA.streetName,
      EA.detail,
      M.userName AS writer
  FROM events E
   LEFT OUTER JOIN event_address EA
   ON EA.eventNo = E.eventNo
   LEFT OUTER JOIN category C
   ON C.categoryCode = E.categoryCode
   INNER JOIN members M
   ON E.userNo = M.userNo
  WHERE E.userNo < 1001
   AND E.categoryCode = 1
  ORDER BY registerDate DESC
  LIMIT 10;

 

DERIVED 의 원인이 되는 FROM 절 다음 서브쿼리 부분을 없애주어 SIMPLE 이 뜨도록 변경했습니다. 이렇게 하니 events_address 테이블 EA 가 여전히 테이블 풀스캔과 Using temporary , Using filesort 를 하고 있었고 나머지는 eq_ref 가 뜨게 되었습니다.

 

LEFT OUTER JOIN 수정Permalink

 

SELECT
    E.userNo,
    E.eventNo,
    E.eventTitle,
    E.eventContent,
    E.startDate,
    E.endDate,
    E.categoryCode,
    E.participantLimit,
    E.noOfParticipants,
    E.registerDate,
    EA.cityName,
    EA.districtName,
    EA.streetCode,
    EA.streetName,
    EA.detail,
    M.userName AS writer
FROM events E
 INNER JOIN event_address EA
 ON EA.eventNo = E.eventNo
 INNER JOIN members M
 ON E.userNo = M.userNo
WHERE E.userNo < 1001
 AND E.categoryCode = 1
ORDER BY registerDate DESC
LIMIT 10;

테스트 데이터라 event_address 테이블이 추가 전 입력된 데이터들은 주소지에 대한 정보가 없었습니다. 주소지가 있는 것만 불러와야 하는데 필요없는 데이터까지 조회할 수 있겠다 싶어 LEFT OUTER 를 제거하고 INNER JOIN 으로.

 

 

order by 수정Permalink

 

SELECT
    E.userNo,
    E.eventNo,
    E.eventTitle,
    E.eventContent,
    E.startDate,
    E.endDate,
    E.categoryCode,
    E.participantLimit,
    E.noOfParticipants,
    E.registerDate,
    EA.cityName,
    EA.districtName,
    EA.streetCode,
    EA.streetName,
    EA.detail,
    M.userName AS writer
FROM events E
 INNER JOIN event_address EA
 ON EA.eventNo = E.eventNo
 INNER JOIN members M
 ON E.userNo = M.userNo
WHERE E.userNo < 1001
 AND E.categoryCode = 1
ORDER BY E.eventNo DESC
LIMIT 10;

events 테이블 내에 존재하는 registerDate 는 인덱스가 적용된 컬럼이 아닙니다. 인덱스를 사용하지 못한 정렬작업 때문에 당연히 Using temporary , Using filesort 가 발생할 수 밖에 없었습니다.

그래서 인덱스가 걸려있는 eventNo 로 변경

 

 

WHERE절 수정, 그리고 최종 변경Permalink

 

<!-- 현재 프로젝트 상에 최종 적용된 SQL 입니다! -->

SELECT
      E.userNo,
      E.eventNo,
      E.eventTitle,
      E.eventContent,
      E.startDate,
      E.endDate,
      E.categoryCode,
      E.participantLimit,
      E.noOfParticipants,
      E.registerDate,
      EA.cityName,
      EA.districtName,
      EA.streetCode,
      EA.streetName,
      EA.detail,
      M.userName AS writer
  FROM events E
   INNER JOIN event_address EA
   ON EA.eventNo = E.eventNo

  <!-- 카테고리별 조회-->
  <if test="categoryCode != NULL">
    AND E.categoryCode = 1
  </if>

   INNER JOIN members M
   ON E.userNo = M.userNo 
  WHERE E.userNo < 1001
  ORDER BY EA.eventNo DESC
  LIMIT 10;

그래서 WHERE 이하에 있던 카테고리별 조회 코드를 event_address 와 조인할 때 걸러질 수 있도록 수정을 하여 임시테이블 생성을 차단.

 

 

출처 - https://jane096.github.io/project/refactoring-sql/