[SQL] WHERE 기능, 성능향상 팁 *****

WHERE 절은 "테이블내의 모든 행을 검색하는 대신 검색 조건을 지정하여 사용자가 원하는 행들만 검색하는 기능"이다.

WHERE 조건식은 단일 조건식과 복수 조건식이 있다.

연산자	의 미
=	같다	WHERE 품목 = '구두' ;
>	보다 크다
>=	보다 크거나 같다
<	보다 작다
<=	보다 작거나 같다	WHERE 단가 <= 5000 ;
<>	다르다
!=	다르다
between A and B	두 값(A와 B)의 범위에 포함되는	WHERE 단가 between 5000 and 10,000 ;
IN (set)	괄호 안의 값과 일치하는	WHERE 품목 IN ('연필', '샤프', '풀') ;
LIKE	검색하고자 하는 문자열을 정확히 알 수 없는 경우    % : 0 글자 이상의 임의 문자를 대표    _  : 1 글자의 임의 문자를 대표
IS NULL	널 값
AND	두개의 조건이 TRUE 이면 TRUE 를 리턴
OR	둘 중 하나의 조건이 TRUE이면 TRUE를 리턴
NOT	조건이 False 이면 TRUE 를 리턴

연산의 우선순위는 비교연산자 (=, !=, <>, >, >=, <, <=) SQL연산자(BETWEEN, IN, LIKE, IS NULL), NOT, AND, OR 순이다.

검색조건을 설정할 때 LIKE '%검색어%' 를 사용해야 할 경우가 있는데 변수 앞에 '%' 를 사용하면 해당 칼럼에 Index를 설정했다 하더라도 FULL SCAN을 한다.

조건절에 자주 등장하는 칼럼이라고 판단하여 최적화된 인덱스를 생성했다 하더라도, Index를 사용할 수 없는 상황이 발생하는데 대표적인 경우가 칼럼의 내외부 변형, IS NULL, IS NOT NULL을 사용한 비교, 여러 칼럼에 대한 OR조건 사용, 부정형(NOT, <>, !=, NOT EXISTS) 비교, 그리고 위에 언급한 LIKE 검색시 변수 앞에 %를 사용하는 경우이다.

가령, 주소록 필드를 저장할 때 통으로 된 하나의 필드로 저장하는 경우에는 검색하고자 하는 값이 중간에 들어가게 되어 FULL SCAN을 할 수 밖에 없다. 이럴 때는 칼럼을 여러개로 분리하여 앞에 %를 사용하지 않도록 하고 AND 조건으로 검색을 하도록 한다.

select * from dept where substr(dname,1,3) = 'DEF' ; 에서 dname 칼럼에 번형을 가한 경우에는 dname 칼럼에 인덱스(Index)를 설정해 두었더라도 Index를 타지 않는다.

select * from dept where dname LIKE 'DEF%' ; 로 수정하면 Index를 탄다.

다음은 Oracle SQL Developer 에서 scott 계정에 접속하여 테스트한 결과이다.

먼저, sal 칼럼에 Index를 설정했다.

create index emp_sal_idx on emp (sal) ;  -- sal 칼럼에 인덱스 생성

인덱스(Index) 삭제 명령어는 drop index emp_sal_dix; -- 인덱스 삭제

select * from emp where sal * 12 < 10,000 ;

select * from emp where sal < 10,000 / 12 ;

WHERE 조건식 을 어떻게 사용하느냐에 따라 성능에 영향을 미칠 수 있다. 개발자들의 SQL 사용 능력의 개인차가 심하다. 동일한 실행결과를 추출하는 다양한 형태의 SQL 이 존재한다.

성능향상을 위해서는

• index를 타는지 항상 체크하라.
  index를 활용하지 않은 검색은 데이터가 많으면 많을수록 성능은 급격히 떨어진다
  하지만 성능을 최적화하기 위해 무조건 index를 생성하는 것은 아니다. 경우에 따라서는 테이블 전체를 검색하는 방법이 성능이 보장될 수도 있다.
  
• select 쿼리문 작성시 필요한 칼럼(column)만 명시하라. select * 을 사용하는 것을 피하라.
  사용하지 않는 데이터를 호출하는 것만으로도 이미 많은 부하가 생긴다. 특히 text 타입의 데이터를 호출하는 경우는 그 정도가 심해진다.
• where조건문의 왼쪽은 되도록 변형되지 않은 순수한 column만을 선언하라.
  where 조건을 AND 등으로 여러개 조건을 설정해야 할 경우라면 가장 왼쪽 조건에서 대부분의 데이터가 걸러지도록 칼럼을 선택하고 설정된 칼럼에 인덱스를 설정하는 것이 좋다.
  
• JOIN을 사용하는 경우 INNER JOIN을 되도록 사용하라.
  두개의 테이블을 JOIN 해서 사용할 때 JOIN 방법에 따라서 수행속도의 차이는 현저히 나타난다. 처리결과는 같지만 연결하기 위해 처리하는 일량에서 차이가 난다.
  
• 서브쿼리 사용시 불필요한 select 구문을 줄여라.
  

전반적인 성능향상을 꾀하려면 CPU, 메모리, I/O 의 밸런스를 맞추어야 한다. DB성능 튜닝은 이 세가지 자원에 걸리는 부하를 적절하게 분산시켜 비용대비 최적의 성능을 뽑아내는것이 핵심이다.

일반적으로 대용량 테이블이라 하더라도 인덱스가 제대로 작동하고 있다면 검색 자체가 성능저하를 가져 오지는 않는다. 다만, 복수의 JOIN과 같이 대량의 Nested Loop 가 발생한다면 엄청난 양의 I/O가 발생하고 이것이 RDB전체의 성능을 크게 저하시키는 원인이 되기도 한다.

HDD의 I/O 속도는 메모리에 비하여 많이 느리다. 10,000RPM HDD와 DDR3-2500 메모리의 성능 차이는 약 800배에 이른다. DBMS 같은 시스템에서는 효과적으로 버퍼(캐시)를 구현하여 I/O 발생을 최소화하도록 구현하고 있다. 그래서 히트(hit)율이 높을 때는, HDD에서 SSD로 교체해도 성능 향상 효과가 미미한 경우가 있다. 하지만 SSD를 사용하면 버퍼 크기를 작게 유지해도 되기 때문에 대용량의 메모리를 유지할 필요가 적어지기도 한다.

MYSQL Clone 의 성능을 다룬 자료가 있는데 다시 보면 도움이 될 거 같아서 링크를 걸어둔다. http://blog.embian.com/5

NULL 검색

where username is null;