[MSSQL] 링크드 서버(MySQL) 사용 시 openquery로 쿼리 속도 빠르게 하기

MS SQL Server 에 이 기종(MySQL)의 DB를 Linked Server로 등록하고 MySQL의 테이블 데이터를 select, update 등을 할 때 속도가 느려지는 증상이 있어 확인해 보니 쿼리문에 문제가 있다는 것을 알고 조치하였습니다.

​

openquery의 기본 사용법에 대해 알아보겠습니다.

 

 

Linked Server 테이블의 잘못된 사용 예

먼저 아래 예제를 보겠습니다.

동작은 되지만 좋지 않은 예입니다.

select col1,col2
from
[링크드서버이름]...[테이블명] with (nolock)
where col1='조건'

mysql linked server의 특정 테이블에서 특정 조건의 데이터를 가져와서 출력하려는 의도입니다.

그런데 데이터가 적을 때에는 별 문제가 되지 않습니다.

쿼리 실행 즉시 얻고자 하는 값을 얻을 수 있습니다.

개발 초기에는 당연히 데이터가 거의 없기 때문에 '어 잘 출력되네' 하고 넘어갈 수 있는데

문제는 시간이 흘러 mysql에 데이터가 누적되어 수십만 건 이상이 되면 문제가 발생하기 시작합니다.

문제는 SQL Server에 발생하는데 먼저 CPU 사용량이 급격히 올라가고 동시에 시스템 메모리를 많이 사용하게 되어 DB서버의 전체적인 성능이 떨어지게 되고 급기야 서버가 다운될 수도 있다는 것입니다.

위 예문을 보면 col1에 조건을 걸어서 최소한의 데이터만 가져오는 것으로 오해할 수도 있는데 절대 그렇지 않습니다.

로컬 서버의 테이블에 접근하여 select를 하는 경우라면 조건에 따른 필터링이 적용되어 적은 데이터를 즉시 얻어낼 수 있지만 링크드 서버의 경우에는 이야기가 달라집니다.

​

from 절에 Linked Server의 테이블이 있는 경우에는 아무리 where절에 조건을 완벽하게 준다 하더라도 그 조건에 맞는 데이터만 가져오질 않습니다.

그럼 뭘 가져오느냐?

where절에 정의한 조건에 상관없이 링크드서버의 테이블에 있는 모든 데이터를 다 가져옵니다.

--> 이 부분때문에 쿼리비용이 많이 발생합니다.

모든 데이터를 full scan하여 다 가져온 다음에 where 절에 정의한 조건이 필터링이 된다는 것이죠.

​

흔히 초급 개발자가 많이 하는 오류라고 볼 수 있겠습니다.

 

 

Linked Server 테이블의 올바른 사용 예

​openquery를 사용한 올바른 예제입니다.

SELECT col1,col2
FROM
OPENQUERY([링크드서버이름], 'SELECT col1, col2 FROM [테이블명] where col1=''조건''');

이렇게 OPENQUERY를 사용하면 링크서버에서 테이블 데이터를 가져올 때 전체 데이터를 가져오지 않고 where 조건에 맞는 데이터만 가져오므로 쿼리속도가 매우 빨라지게 됩니다.

출발하기 전에 미리 짐을 가볍게 정리한 채로 오기 때문에 오는 동안 힘이 덜 드는 원리인거죠.

​

​

​

그럼 추가로 openquery로 Update하는 예제를 볼게요

UPDATE OPENQUERY([링크드서버이름], 'SELECT col1 FROM [테이블명] WHERE col1=''조건''')
SET col1='갱신할데이터값';

 

 

다음은 openquery로 Delete하는 예제를 볼게요

DELETEOPENQUERY([링크드서버이름], 'SELECT [컬럼명] FROM [테이블명] WHERE [조건절]');

 

다음은 openquery로 Insert하는 예제를 볼게요

INSERT INTO OPENQUERY([링크드서버이름], 'SELECT [컬럼명] FROM [테이블명]') VALUES ('[값]');

 

이상으로 openquery를 사용하여 서버 쿼리성능을 개선하는 방법을 알아보았습니다.

다음에는 오픈쿼리를 좀 더 확장해서 스토어드 프로시저(stored procedure) 에서 조건문에 변수를 대입하여 보다 유연하게 사용하는 방법을 알아보겠습니다.