조회하고자 하는 데이터의 특성상 가장 최근의 데이터부터 역순으로 인덱스를 조회하는 것이 유리할 경우가 있다.

 

예)

신입사원의 입사정보조회

게시판의 최신 글 내용 조회

로그성 데이터의 정보조회


내림차순인덱스 생성하는 방법

CREATE INDEX 인덱스명 ON BIG_EMP (EMPNO DESC)


조회시 힌트를 사용하는 방법

SELECT /*+ INDEX_DESC  (BIG_EMP 인덱스명) */

EMPNO FROM BIG_EMP ROWNUM<10 ;



Posted by 빨강토끼
,

데이터의 존재여부만 확인하면 되는데 불필요하게 전체 건수를 count 하는 경우다.


SELECT COUNT(*) AS CNT FROM BIG_EMP 

WHERE DEPTNO='60' AND MGR='7698';

 

If(rs.getString("CNT") > 0){

    …

}


Or


SELECT * FROM BIG_EMP 

WHERE DEPTNO='60' AND MGR='7698';

 

If(rs.size() > 0){

   …

}


rownum을 사용하여 부분처리(하나만 조회)를 함으로서 성능 향상을 할 수 있다.

SELECT COUNT(*) CNT FROM EMP

WHERE DEPARTMENT='SALES' 

  AND LOCATION='SEOUL'  AND ROWNUM <= 1;

 

If(rs.getString("CNT") > 0){

   …

}


MS-SQL 인 경우 Top N 사용




조건절을 만족하면 첫번째 레코드를 만나는 순간 True를 반환하고 서브쿼리를 종료하는 EXISTS 서브쿼리를
사용하면 성능 향상을 하는 방법도 있다.

 

SELECT COUNT(*) FROM DUAL

WHERE EXISTS(

  SELECT ‘x’ FROM BIG_EMP 

  WHERE DEPT='60' AND MGR='7698'

);


If(rs.getString("CNT") > 0){

    …

}



여러가지 DB에 유연하도록 하기위해서 EXISTS 를 추천한다.

Posted by 빨강토끼
,

한테이블에 모든 정보를 모아놓으면(반정규화) 조인에 의한 부하가 줄어들거라는 기대를 하게된다.

하지만 고려해야되는 것들이 있다.


1. 저장공간의 낭비가 생긴다.

2. I/O가 증가된다.(특히 블럭단위 조회시)


아래 예가 있다.



만일 사원이 40만명이라면 

40만  레코드 X 168 Byte 

= 67,200,000 Byte

= 64Mbyte


64Mbyte 의 저장공간이 필요하게 된다.

그리고 만일 DB에서 한번에 읽어들이는 블록이 8Kbyte 라면 

한번 I/O 때 48개의 레코드를 읽게 된다.



정규화를 한 위의 사원테이블과 부서테이블은 각각


사원 테이블

40만 레코드 X 46 Byte

= 18,400,000 Byte

= 17.5Mbyte


부서 테이블

100 레코드X 130Byte

= 12Kbyte


약 17.6Mbyte 의 저장공간이면 되고

DB에서 한번에 읽어들이는 블록이 대략 178개 레코드를 읽을 수 있다.


저장공간에서는 3배가 줄어들게 되고

풀 스캔시 4배의 I/O 가 감소되게 된다.


실제 필드에서는 수천만 수억, 수십억의 레코드가 있는 테이블도 있으므로 성능의 차이는 더 커진다.

Posted by 빨강토끼
,

테이블에 무조건 인덱스를 만드는 것은 비효율적이다.


  • 인덱스를 만들거나 수정시 DB 성능 지연 발생
실제 구동하며 서비스하고 있는 DB에 아무리 작은 테이블이라도 인덱스를 생성하는 명령을 실행하면
0.001 초라도 DB서버는 버벅이게 된다.
이것은 no logging 등 별의별 옵션을 사용하도라도 기존 서비스에 영향도가 0% 라고 전혀 말 할 수 없다.
인덱스를 추가하는 것을 100% 반대하는 것은 아니지만 운영중에 인덱스를 추가하거나 수정하는 것은
가급적 피해야한다.
  • 분포도가 좋지 않은 컬럼에 인덱스를 생성하더라도 성능에 영향이 적음.
분포도가 좋지 않다는것은 해당 컬럼의 값이 특정 값에 편중되어 있는 비율이 커서 차라리 Full Scan 을 통하여
조회를 하는 것이 유리하다는 것이다.(인덱스 손익분기점)
인덱스컬럼을 추가하던지(복합인덱스),
다른 컬럼을 사용하여 조회하도록 쿼리 튜닝하던지,
Full Scan을 사용하는 방법을 고려해야 한다.
  • INSERT, UPDATE, DELETE 시에 성능 저하
인덱스를 추가하게되면 당장 SELECT 시에 성능향상을 기대할 수 있겠지만,
INSERT, UPDATE, DELETE 시에 오히려 성능이 저하된다.
일일히 해당 인덱스에서도 INSERT, UPDATE, DELETE 가 수행되기 때문이다.


  • 저장공간의 낭비

과다한 인덱스 생성으로 실제 테이블공간과 비슷하거나 더 많은 저장소 공간을 차지하는 배보다 배꼽이 더 큰 경우도 발생되고 있다.

Posted by 빨강토끼
,

만일 총 직원수가 3만명인 사원테이블(BIG_EMP)에

메니져사원번호(MGR )가 7698 인 직원이 10,000 명이고,

부서번호(DEPTNO )가 60 인 직원이 1,200 명인 상황에서 


SELECT PAYMENT FROM BIT_EMP 

  WHERE MGR='7698' AND DEPTNO='60';


위와 같은 쿼리에서 만일 메니져사원번호(MGR) 컬럼만 인덱스가 생성되어있다면

DB는 인덱스를 통하여 메니져사원번호가 '7698' 인 레코드를 검색후 

해당 레코드의 부서번호가 60인지 판단하는 식으로

조회할 것이다.



하지만 이러한 인덱스컬럼의 조건절이 대부분의(15%이상) 레코드의 조건에 매칭되는 경우에서의

인덱스 스캔방식의 조회는 비효율적이다.

이유는 인덱스 스캔방식에서는 실제테이블 레코드를 조회할때 Single Block I/O 를 사용하기 때문이다.

차라리 인덱스컬럼을 무시하고 전체 테이블을 읽는 풀 스캔이 더 효율적이다.

풀스캔은 Multi Block I/O 를 사용한다.

 

Single Block I/O

인덱스를 사용하여 검색(인덱스 스캔)을 하게 되면 한번의 I/O 요청에 한 블록씩 데이터를 읽는다.

  1. 인덱스 테이블에서 조건절 검색 MGR='7698' 
  2. ROWID 로 테이블 검색 (1만 ROW , 1만번 I/O)
  3. 조건절 검색 ( DEPTNO='60')




Multi Block I/O

전체 데이블 검색(풀 스캔)은 데이터를 읽을 때 한번의 I/O 요청으로 여러 블록을 한꺼번에 읽는다.

  1. 3만 / 150 = 2백 번 IO
  2. 조건절 검색 ( DEPTNO='SALES')


풀 스캔을 유도하는 방법


인덱스가 걸린 컬럼을 가공

SELECT SAL FROM BIG_EMP 

WHERE DEPTNO='60' AND MGR || ‘’ ='7698‘;



힌트를 사용

SELECT /*+ FULL(BIG_EMP) */

  SAL FROM BIG_EMP 

WHERE DEPTNO='60' AND MGR='7698';




Posted by 빨강토끼
,

만일 총 직원수가 3만명인 사원테이블(BIG_EMP)에

메니져사원번호(MGR )가 7698 인 직원이 10,000 명이고,

부서번호(DEPTNO )가 60 인 직원이 1,200 명인 상황에서 

위의 조건에 만족하는 직원의 리스트를 조회하려고 아래와 같은

쿼리를 만들려 한다면,


SELECT * FROM BIG_EMP 

WHERE MGR=‘7698’ AND DEPTNO=’60’;


개발자는 당연히 부서번호(DEPTNO)가 60 인 직원을 먼저 조회한 후에 
메니져사원번호(MGR)가 7698 인 직원인지
판단하는 로직으로 조회쿼리를 만들고 싶어 할 것이다.

물론 DEPARTMENT와 LOCATION 둘중하나의 컬럼에만 인덱스가 생성되어있다면 그 컬럼을 먼저
사용할것이므로 순서를 고민하지 않겠지만 두컬럼이 동일한 조건이라면
  • MGR='7698' AND DEPTNO='60'
  • DEPTNO='60' AND MGR='7698'
위에 두가지중에 무엇이 더 나을까 고민을 할 수도 있다.

규칙(RULE) 기반 옵티마이져라면 
  1. 가장 나중에 생성한 인덱스가 있는 컬럼부터 파싱
  2. AND 조건이면 WHERE 절에서 가까운 순서대로, OR 조건이면 맨오른쪽에서 부터 순서대로

위의 순서대로 파싱을 한다.







비용(COST) 기반 옵티마이져(대부분의 DB가 이것을 사용함)라면 고민 할 필요가 없다.
옵티마이져가 알아서 순서를 정하기 때문이다. 


즉. 대부분의 경우 컬럼의 순서에 신경쓸 필요가 없으며 구지 신경을 쓴다면 맨뒤에서 부터 WHERE 조건을 나열하면 된다.

Posted by 빨강토끼
,