MySQL 5.5->5.7 버전 업그레이드 후, 쿼리수정

5.5에서 커서를 통해 15,000여건정도씩이 3~4초에 INSERT가 되었는데, 버전을 올린 후(5.7.15), 동일 쿼리가 5분씩 지체가 되었다.

처음에는 buffer나 join, tmp_table 등 사이즈 나 설정문제인듯하여 조사해보았으나, 갖은 설정을 변경을 해주어도 동일하였다.

그래도 약간의 힌트를 얻었다.

1. 5.5에서 지원하는 최적화쿼리와 5.7에서 지원하는 최적화 쿼리가 약간 상이하다.

  => 기존 프로시저 쿼리를 explain을 걸어보면서 변경

2. 하루가 지체되면서 깨달은 사실은, 쿼리에 subquery가 존재하는데 그 부분에 별도 테이블과 join을 걸면서부터 속도가 급격하게 저하되는걸 확인하였고, subquery로인하여 temporary table에 subquery 결과값이 들어가고 그로인해 기존 테이블에 key가 풀리면서 키로 매핑이 안되다보니 full-scan으로 인하여 속도가 저하되는걸 확인할 수 있었다.

   즉, temporary table에 키를 잡아줘야 속도가 개선이 된다는걸 깨달았다.

조치방법

 1. 아래 command 입력

     set optimizer_switch='derived_merge=off'; 

 2. 문제된 쿼리에 explain을 걸어서 temporary table에 possible_key 컬럼에 <auto_key0>이 잡히면 index설정이 된 것이다.

 

이로 인해 1초내외로 결과가 나오는걸 확인할 수 있다.