DB - Sybase Indexes

1. 인덱스가 성능에 미치는 영향 (How indexes affect performance)
. 높은 성능을 위해서는 테이블에 대한 인덱스 설계가 잘 되어야한다
  무분별한 인덱스 추가는 삽입,수정,삭제 작업시 오히려 성능에 악영향을 준다
. Adaptive Server query optimizer는 쿼리마다 분석을 통해 비용이 가장 적은 쿼리 실행계획을 선택한다
. 성능을 높이기 위한 방법
 - 데이터 엑세스할 때 테이블 스캔을 피하기
 - 포인트 쿼리에서 데이터 접근할때 특정값 조건을 넣어서 사용하기
 - 범위검색 쿼리에서 상한, 하한을 지정해서 사용하기
 - 인덱스 커버를 사용해서 데이터 페이지 접근을 피하기
 - 정렬된 데이터를 이용해 정렬작업을 피하거나 비용이 많이드는 작업인 조인등을 피하기
 - 인덱스를 이용해 데이터 고유성을 강화하고 삽입시 저장위치를 랜덤하게 할 수 있음
 - sp_chgattribute 'concurrency_opt_threshold' parameter 이용해 동시성을 좋게할 수 있음
   (sp_chgattribute table_name, "concurrency_opt_threshold", min_page_count)
. select가 오래 걸리거나 조인 시 느리거나 select는 잘 되지만 변경은 잘 안된다면 인덱스가 문제있는 것이다



2. 잘못된 인덱스 증상 (Symptoms of poor indexing)
. 인덱스를 사용하여 성능을 향상시키는 주된 목표는 디스크에서 테이블의 모든 페이지를 읽는 테이블 스캔이나
  디스크에서 데이터 페이지 만 읽는 부분 테이블 스캔을 피하는 것이다
. 600개의 데이터 페이지가 있는 테이블에서 고유 값을 검색하는 쿼리에는 600 개의 물리적 및 논리적 읽기가 필요하다
  인덱스가 데이터 값을 가리키면 동일한 쿼리가 2~3번의 읽기, 200에서 300배의 성능 향상을 만족할 수 있다


** 인덱스 사용되지 않는 경우 (Lack of indexes is causing table scans)
. select 값이나 조인이 너무 길다면 인덱스가 존재하더라도 사용하지 않을 수 있다
. showplan으로 테이블 엑세스 정보 등을 볼 수 있고 테이블 스캔을 한다면 dbcc traceon(302) 사용해서
  해당 이유를 확인할 수 있다, 해당 내용이 잘 보이지 않는다면 쿼리 작성에 문제가 있을 수도 있다
  dbcc traceon(310)를 사용해 더 주의깊게 살펴볼 수 있다


** 인덱스가 충분히 선택적이지 않는 경우 (Index is not selective enough)
. 옵티마이저는 특정행을 구별하기 좋을 경우 인덱스를 사용한다
  여권번호와 같이 특정행을 구별하기 좋으면 사용하지만 sex(M, F) 같은 경우 고유하지 않으므로 사용하지 않을 수 있다


** 범위 쿼리를 지원하지 않는 인덱스 (Index does not support range queries)
. 일반적으로 clustered indexes 및 covering indexes는 range queries 및 많은 행과 일치하는 검색에 우수한 성능을 제공함
  noncovering indexes의 키를 참조하는 range queries는 제한된 수의 행을 반환하는 범위의 인덱스를 사용한다
  그러나 쿼리가 반환하는 행 수가 증가하면 nonclustered index 또는 data-only-locked의 clustered index는 테이블 스캔보다 많은 비용이 든다


** 데이터 변경을 느리게 하는 많은 인덱스 (Too many indexes slow data modification)
. 너무 많은 인덱스는 검색을 빠르게 하지만 데이터 변경 작업 시에 성능을 안좋게한다
. 모든 삽입/삭제 작업은 data-only-locked의 clustered index와 nonclustered index에 영항을 준다
. allpages-locked의 clustered index가 변경되면 nonclustered index도 모두 변경되어야한다


** 인덱스 크기 (Index entries are too large)
. 인덱스 크기를 최대한 작게 만들어야 한다, 인덱스의 총 키 길이를 페이지 크기의 3 분의 1까지 만들 수 있다
  그러나 긴 인덱스는 페이지에 한번에 저장하기 어려우며 인덱스 레벨을 높게된다
  이렇게하면 인덱스 루트에서 리프 페이지로 이동할 페이지 수가 증가하고 쿼리 중에 필요한 디스크 I/O가 증가합다
. 아래 예는 인덱스 사이즈에 다른 레벨을 보여준다

. sp_estspace를 사용해 확인하면 40bytes는 4레벨이 필요하다


** 큰 데이터 및 인덱스에 대한 예외적인 경우 ( Exception for wide data rows and wide index rows)
. wide row로 이뤄진 인덱스는 아래 경우에 유용합니다
 - 테이블의 행 수가 매우 넓어서 데이터 페이지 당 행 수가 매우 적을 때
 - 쿼리 실행이 인덱스 커버링이 사용되어 질 때
 - 쿼리가 충분히 많은 행을 리턴할 때

예 )테이블의 행수가 매우 길고 페이지 당 하나의 행만있는 경우 100개의 행을 반환하는 쿼리는
     100개의 데이터 페이지에 액세스한다
     긴 인덱스 행이 있더라도이 이런 쿼리의 인덱스는 성능을 향상시킬 수 있다

예)인덱스 행이 240바이트 인 경우 인덱스는 페이지 당 8개의 행을 저장하며 쿼리는 12개의 인덱스 페이지에만 액세스한다



3. Index limits and requirements
. 클러스터 인덱스는 키 순으로 데이터가 정렬되므로 테이블당 1개를 만들 수 있다
. 기본적으로 range-, list-, hash-partitioned tables에서는 local index로 생성된다
. nonclustered indexes는 테이블 당 최대 249개 만들 수 있다
. primary key create생성 시 기본적으로 clustered인덱스를 만들고 unique 조건은 nonclustered인덱스를 만듬
. key는 31개 columns까지 생성 가능하고 사이즈는 페이지에 따라 다르다

  Page Size - Max key length
  2048  - 600
  4096  - 1250
  8192  - 2600
  16384 - 5300



4. 인덱스 선택 시 고려할 점 (Choosing indexes)

* 인덱스 선택을 할 때 할 수 있는 질문들
 - 주어진 테이블과 현재 어떤 인덱스가 연관되어 있습니까?
 - 테이블을 사용하는 가장 중요한 프로세스는 무엇입니까?
 - 테이블에서 수행되는 데이터 수정에 대한 작업의 비율은 얼마입니까?
 - 테이블에 클러스터형 인덱스가 생성 되었습니까?
 - 클러스터형 인덱스를 비클러스터형 인덱스로 대체 할 수 있습니까?
 - 인덱스 중에 하나 이상의 중요한 쿼리를 커버할 수 있습니까?
 - 복합 primary key의 고유성을 사용하려면 composite index가 필요합니까?
 - 기존 쿼리에 함수 기반 인덱스를 사용하여 성능을 향상 시킬 수 있습니까?
 - unique 인덱스로 정의 할 수 있는 인덱스는 무엇입니까?
 - 주요 분류 요건은 무엇입니까?
 - 일부 쿼리는 결과 집합의 내림차순 정렬을 사용합니까?
 - 인덱스가 조인 및 참조 무결성 검사를 지원합니까?
 - 인덱싱이 업데이트 유형(직접 또는 지연)에 영향을 줍니까?
 - 커서 위치 지정에 필요한 인덱스는 무엇입니까?
 - dirty reads가 필요할 때 스캔을 지원할 unique 인덱스가 있습니까?
 - unique 인덱스 생성을 위해 IDENTITY columns을 테이블에 추가해야 합니까?

* 인덱스를 얼마나 만들지 고려할 사항
 - 공간 제약
 - 테이블에 대한 엑세스 경로
 - 데이터 modifications 대 select 작업 비율
 - OLTP 대 보고서의 성능 요구 사항
 - 인덱스 변경의 성능 영향도
 - update statistics 얼마나 자주 할 수 있는지


** 인덱스와 논리적인 키 관계 (Index keys and logical keys)
. index key와 logical key는 구별해야 합니다, logical key는 데이터 베이스 설계의 일부분으로서
  primary keys, foreign keys, common keys를 정의 합니다
. index key는 성능상의 이유로 선택합니다
. 흔한 오류는 범위 쿼리나 결과 집합에 상관없이 PK에 클러스터형 인덱스를 만드는 것입니다


** 클러스터 인덱스 지침 (Guidelines for clustered indexes)
. 대부분의 allpages-locked tables은 clustered indexes를 사용하거나
  파티션을 사용해서 heap table의 마지막 페이지에서의 경합을 줄입니다
. 많은 삽입이 필요한 경우 IDENTITY column과 같이 지속적으로 증가하는 clustered index대신
  잠금 경합을 줄이기 위해 임의의 페이지에 삽입을 하는 키를 선택해야 합니다
  primary key는 이 조건에 맞지 않습니다
  data-only-locked tables 보다 allpages-locked tables에서 보통 경합이 더 심합니다
. 클러스터 인덱스는 범위 검색의 인수에 매칭이 될때 우수한 성능을 보입니다
  예) where colvalue >= 5 and colvalue < 10
  allpages-locked tables에서 행은 순서를 유지하고 클러스터된 인덱스 사용시 빠릅니다
  data-only-locked tables에서는 인덱스 생성 시 순서를 유지하지만, 시간이 지나면 클러스터링이 감속합니다
. allpages-locked tables에서 자주 업데이트되는 columns은 clustered indexes에 포함하지 않습니다


** 클러스터 인덱스 선택 시 고려할 점 (Choosing clustered indexes)
. 클러스터 인덱스를 선택하는 경우는 아래와 같습니다
 - primary key, where절에 사용되고 임의의 insert 작업을 하는 경우
 - 아래와 같이 범위별 엑세스가 발생될 때
   col1 between 100 and 200
   col12 > 62 and < 70
 - order by에 사용되는 Columns
 - 자주 변경되지 않는 Columns
 - 조인에 사용되는 Columns

. 여러 선택이 있다면 첫번재로 물리적 순서로 처음 나오는 항목을 선택합니다
  두번째로 범위 쿼리를 찾습니다
  경합으로 인한 “hot spots”이 발생되는지 성능 테스트를 합니다


** 비클러스터형 인덱스 후보 선정 시 고려할 점 (Candidates for nonclustered indexes)
. 클러스터 인덱스 선택이 되지 않은 모든 가능성을 고려합니다
. index covering으로 성능향상이 가능한지 고려합니다
. data-only-locked tables에서 clustered indexes는 leaf레벨이 data레벨 위에 있으므로
  index covering을 사용할 수 있습니다
. allpages-locked tables에서 범위를 벗어난 쿼리는 clustered indexes에서 동작될 수 있지만
  nonclustered indexes에서는 범위 크기에 따라 지원되지 않을 수 있습니다
. 비번한 조회와 중요한 쿼리를 covering하기 위해 복합 인덱스를 고려합니다
. 가장 중요한 쿼리는 포인트 쿼리와 일치되는 검색을 수행할 수 있어야 합니다
. 일치되지 않는 검색도 테이블 스캔을 피할 수 있어야 합니다


** 함수형 인덱스 선택 시 고려할 점 (Choosing function-based indexes)
. 함수기반 인덱스는 특정 legacy 프로그램의 성능을 향상 시키는 옵션을 제공합니다
. 하나 이상의 표현식을 직접 인덱스로 만들 수 있습니다
  각 표현식의 결과값으로 인덱스를 생성하게 됩니다
. 함수나 조작한 결과 값을 컬럼에 저장하고 다른 컬럼 또는 상수와 비교해야할 때
  함수기반 인덱스를 효과적으로 사용할 수 있습니다
. 함수 기반 인덱스의 장점은 기존 쿼리에 사용 된 식과 일치하는 인덱스를 기존 테이블에 간단히 추가 할 수 있다는 것입니다


** 인덱스 선택 (Index selection)
. 자주 사용하는 index와 사용하지 않은 index를 구분합니다
. 모니터링이 가동중이라면 아래와 같은 정보를 확인할 수 있습니다
 * monOpenObjectActivity
 - IndexID : index 식별 id
 - OptSelectCount : 옵티마이저가 사용하기 위해 엑세스한 오브젝트 수
 - LastOptSelectDate : OptSelectCount증가된 마지막 시간
 - UsedCount : 쿼리가 실행될 때 엑세스한 오브젝트 수
 - LastUsedDate : UsedCount증가된 마지막 시간

. OptSelectCount는 이미 컴파일되어 캐시된 경우 실행해도 증가하지 않고
  UsedCount는 실행되면 증가합니다
  exec가 설정되어 있지 않으면 OptSelectCount는 증가하지만 UsedCount는 증가하지 않습니다
. 서버가 재실행되면 모니터링한 값은 없어집니다
// 특정 오브젝트에 대한 모든 인덱스가 옵티마이저가 마지막으로 선택된 시간과 // 실행 중에 실제로 사용 된 시간에 대한 모니터링 테이블을 조회하고 계수를 보고합니다 select DBID, ObjectID, IndexID,

OptSelectCount, LastOptSelectDate, UsedCount, LastUsedDate from monOpenObjectActivity where DBID = db_id("financials_db") and ObjectID = object_id('financials_db..expenses') // 응용 프로그램에서 사용되었

거나 현재 사용되지 않은 모든 인덱스를 표시합니다 select DBID, ObjectID, IndexID, ObjectName = object_name(ObjectID, DBID), LastOptSelectDate, UsedCount, LastUsedDate from monOpenObjectActivity where DBID

= db_id("MY_1253_RS_RSSD") and ObjectID = object_id('MY_1253_RS_RSSD..rs_columns') // 현재 데이터베이스에서 현재 사용되지 않는 모든 인덱스를 표시합니다 select DB = convert(char(20), db_name()), TableName

= convert(char(20), object_name(i.id, db_id())), IndexName = convert(char(20),i.name), IndID = i.indid from master..monOpenObjectActivity a, sysindexes i where a.ObjectID =* i.id and a.IndexID =* i.indid

and (a.UsedCount = 0 or a.UsedCount is NULL) and i.indid > 0 and object_name(i.id, db_id()) not like "sys%" order by 2, 4 asc



** 인덱스 선택 시 추가로 고려할 점 (Other indexing guidelines)
. 인덱스 키를 unique하게 정의하면 옵티마이저는 검색 또는 조인 시에 일치하는 값이 하나만 있음을 알 수 있음
. 데이터베이스 설계 시 참조 무결성을 정의한다면 참조한 columns에 unique index가 있어야 함
. 삽입 작업이 많은 테이블에 인덱스를 만드는 경우 fillfactor를 사용하여 페이지 분할을 임시로 최소화하고
  동시성을 향상 시키며 교착 상태를 최소화한다
. read-only table에 인덱스를 만드는 경우 fillfactor를 100으로 사용하여 테이블 또는 인덱스를 가능한 작게 한다
. key의 크기는 가능한 작게 해야 인덱스 트리가 더 평평해져서 트리 탐색을 더 빠르게 한다
. 적합한 작은 데이터 유형을 사용한다
  숫자형은 문자열보다 약간 빠르다
  가변길이는 고정 길이보다 많은 overhead있으므로 길이 차이가 많지 않다면 고정 길이를 사용한다
  null값 허용하는 문자열 및 이진 형식은 가변 길이 이다
  index key로 사용하는 columns은 가능하면 null 값이 아닌 값으로 한다
. 다른 테이블과 조인 시 데이터 유형이 호환 가능해야 한다
  호환되지 않는다면 인덱스를 사용하지 않을 수 있음


** 비클러스터형 인덱스 선택 시 고려할 점 (Choosing nonclustered indexes)
. 비클러스터형 인덱스 추가할 경우 아래 항목을 고려해야한다
 - 데이터 수정 시간 증가에 비해 검색 시간이 얼마나 단축될지
 - 인덱스가 얼마나 많은 공간을 사용하는지
 - 후보 columns은 얼마나 자주 변경이 있을지
 - index key는 얼마나 선택률이 좋을지
 - index key에 중복값이 얼마나 있을지
. 데이터 수정에 대한 성능 테스트 후에 사용여부를 판단해야 한다

* 데이터 수정을 위한 비용 (Performance price for data modification)
. all locking에서는 nonclustered index는 key값이 삽입/삭제 될 때마다 업데이트가 필요하다
. index keys의 일부만 변경하는 테이블은 해당 인덱스만 업데이트하면 됨
. allpages-locked tables에서 exclusive locks일 경우 트랜젝션 동안 인덱스가 잠기게되어
  경합과 오버헤드가 증가하게 됩니다
. 많은 데이터를 가지는 테이블에서 인덱스 3~4개 정도로 성능에 큰 영향을 미치지는 않음


** 복합인덱스 선택 시 고려할 점 (Choosing composite indexes)
. 인덱스 후보가 하나 이상의 후보로 분석되어진다면 복합 인덱스를 고려해 볼 수 있음
 - 범위 쿼리일 때
 - 그룹화된 columns아니 그룹화된 값이 포함된 Vector(그룹화된) 집계일 경우
 - 높은 수의 중복을 반환하는 쿼리일 때
 - 정렬이 필요한 쿼리일 때
 - 테이블 스캔 쿼리이지만 작은 columns만을 사용하는 쿼리일 때
. 읽기 전용 테이블일 경우 많은 인덱스 사용에 부담이 없다


** 복합인덱스에서 Key순서에 따른 성능 (Key order and performance in composite indexes)
. 인덱스 커버링을 사용할 수 있다면 성능이 매우 좋아진다
// 아래 쿼리에서 au_lname, au_fname, au_id 순으로 인덱스가 생성되 있을 때 select au_id from authors where au_fname = "Eliot" and au_lname = "Wilk" // 아래 경우에는 인덱스 커버링은 되지만 au_id만 사용하므

로 효율적이지 않음 select au_fname, au_lname from authors where au_id = "A1714224678" // 아래 처럼 select 절에 다른 columns이 추가되면 성능이 좋아지지 않음 select au_fname, au_lname, phone from authors

where au_id = "A1714224678"
. 아래 표는 where조건에 따른 성능을 보여준다




** 복합인덱스 장/단점 (Advantages and disadvantages of composite indexes)
. 복합인덱스 장점
 - 인덱스 커버링을 사용할 수 있음
 - 쿼리가 각 검색하는 값을 제공하면 단일 인덱스보다 적은 I/O가 발생됨
 - 여러 속성의 고유값을 강화하는 방법임

. 복합인덱스 사용이 필요한 경우
 - Lookup 테이블
 - 자주 엑세스 되는 경우 columns이 있을 때
 - 벡터 집계 사용이 필요할 때
 - 많은 columns이 있을 때 자주 사용하는 서브집합이 존재할 때

. 복합인덱스 단점
 - 인덱스 페이지 당 항목이 적어지고 인덱스 페이지가 많아짐
 - 복합 인덱스 속성을 업데이트하면 인덱스가 수정됨, 인덱스는 자주 업데이트되지 않아야 함

. 복합인덱스를 피하는 경우
 - data rows와 거의 동일한 index row 일 때
 - where에서 사소한 내용만 사용될 때



5. Techniques for choosing indexes

** 범위 쿼리를 위한 인덱스 (Choosing an index for a range query)
select title from titles where price between $20.00 and $30.00
위 쿼리에서 아래와 같은 테이블의 통계정보가 있을 경우
 - 테이블은 1,000,000 rows가지고 있고, allpages locking임
 - 페이지당 10 rows를 가지고 있고 페이지의 75%는 차있어 테이블에 약 135,000 페이지가 존재
 - price가 $20에서 $30사이인 데이터는 약 19%정도가 존재함

. 인덱스가 없으면 전체 135,000 pages를 모두 스캔함
. price에 클러스터 인덱스가 있다면 첫 20달러를 찾은 후 순차적으로 30달러까지 찾게 됨
  페이지가 약 75%가 찼다면 평균 rows수는 7.5이고 190,000 개의 일치하는 행을 읽으려면
  쿼리가 약 25,300 페이지와 3 또는 4 개의 인덱스 페이지를 읽게됨
. price에 비클러스터 인덱스를 사용하면 leaf수준의 약 19%를 읽어서 약 1,500페이지를 읽어야 함
. price가 임의로 분배되어 있다면 읽어야 하는 페이지가 데이터 페이지 수(190,000)만큼 있을 수 있고
  테이블 검색에는 135,000 페이지 만 있으면되므로 클러스터되지 않은 인덱스는 사용하지 않아도됨
. 선택할 수 있는 다른 경우는 price, title로 구성된 비클러스터 인덱스이다
  첫 20달러를 인덱스 스캔으로 찾은 후 30달러까지 leaf레벨에서 스캔하면서 찾아갈 수 있음
  이 경우 약 35,700개의 leaf페이지가 필요하므로 일치하는 leaf페이지를 스캔하려면이
  index 페이지의 약 19% 또는 약 6,800개의 읽기가 필요하다
. 이 쿼리의 경우 price, title으로 하는 비클러스터형 인덱스가 가장 좋습니다


** 다른 인덱스를 필요로 하는 쿼리 (Adding a point query with different indexing requirements)
. 위 예시에서 이제 title에 대한 조건이 있는 쿼리가 있는 경우
select price from titles where title = "Looking at Leeks"
. 중복된 제목이 거의 없다는 것을 알고 있으므로 이 쿼리는 하나 또는 두개의 결과를 반환한다
. 아래 표는 4가지 인덱스 전략에 대한 분석이다


. price에 대한 범위 쿼리는 4번이 가장 좋음, 1/3번은 16K I/O에서 사용 가능함
. titles에 대한 포인트 쿼리는 1/2/3번이 가장 좋음
. 이 두개의 쿼리는 2개의 인덱스를 만드는 것이 최상의 선택임
. price를 위한 것은 4번
. title을 위한 것은 2번이 최상의 선택임

* 인덱스 선택을 할 때 고려할 사항
 - 각 쿼리의 빈도 및 쿼리가 실행되는 시간은 언제인지
 - 응답 시간은 어느 정도 필요한지
 - 업데이트 응답 시간은 어느 정도 필요하면 인덱스가 하나 이상일때 느려지는지
 - 값의 범위는 일반적인지 또는 자주 사용하는 범위는 얼마인지
 - 대용량 캐시가 있는지, 자체 캐시가 있다면 속도가 매우 빠름
 - 다른 어떤 쿼리 및 검색 조건이 사용되는지
 - 다른 테이블과 자주 조인되는지