본문내용
것이다. 인덱스는 query 의 성능을 향상 시키려면 인덱스 생성을 고려해야 하며, column 이나 column의 집합에 값이 유일하도록 하기 윌해 인덱스를 사용할 수 있다.
다시 한번 말하지만 검색과 정렬 속도를 올리는 기능은 잘 사용했을 경우에 가능한 것이다.
다음으로 얘기할 것은 DB 서버마다 index에 대한 미세한 차이점을 보인다는 것이다. 참 짜증 나는 일이다. 하나로 통일이 되었으면좋으련만 하여간에 개발자 들이 노력을 많이 해야 할거 같다.
⑴ 인덱스의 특징
① 인덱스는 테이블의 값을 빠르게 액세스 하도록 하는 데이터베이스 객체다.
② 데이터를 빠르게 찾기 위한 B*TREE을 써서 디스크 입출력 횟수를 줄인다.
③ 데이터베이스 서버가 인덱스를 자동으로 사용하고 유지 보수한다. (클러스터드 인덱스)
④ 인덱스를 만들면 사용자가 직접 조작할 필요가 없게 된다.
⑤ 인덱스는 논리적으로도 물리적으로 테이블과는 독립적이다.
⑥ 언제든지 생성하거나 삭제할 수 있으며 이는 테이블이나 다른 인덱스에 영향을 주지 않는다면 의미
⑵ 인덱스의 생성과 조회
- 자동 생성 : PRIMARY KEY, UNIQUE 제약 조건을 정의할 때 인덱스가 자동으로 생성된다.
- 사용자가 생성 : 행에 대한 엑세스 속도를 하기 위해 Column에 non_unique 인덱스 또는 unique 인덱스 모두 생성가능
① 오라클
CREATE [UNIQUE or BITMAP] INDEX 인덱스명 ON 테이블명(컬럼...)[TABLESPACE tablespace_name];
㉠ UNIQUE : UNIQUE Index를 생성한다. (생략가능)
㉡ BITMAP : BITMAP Index를 생성한다. (생략가능)
㉢ 인덱스명 : 생성하고자 하는 인덱스 이름 예) 테이블명_인덱스명_키값
㉣ 테이블명 : 인덱스를 생송하고자 하는 테이블 이름
㉤ 컬럼 : 인덱스로 생성하고자 하는 컬럼 이름
㉥ tablespace_name : 인덱스가 위치할 (생성될) 테이블 스페이스 이름
현재 로그인한 사용자에 전체 인덱스 목록을 보는 방법
select index_name ,index_type, table_name, uniqueness from user_indexes;
위에 쿼리에서는 어떤 필드가 인덱스로 쓰였는지 알 수 없지만 아래 쿼리 좀더 자세히 알 수 있다.
select index_name , table_name, column_name from user_ind_columns;
② MSSQL
MSSQL도 오라클하고 변반 차이는 없지만 약간의 차이는 있다.
CREATE Clustered Index인덱스명ON테이블명(컬럼)
오라클은 UNIQUE 혹은 BITMAP 이라는 것이 쓰였지만 MSSQL은 Clustered 것이 쓰였다.
만약 Clustered 를 생략하면 자동으로 UNClustered 로 인덱스를 쓰겠다는 의미를 내포한다.
자동 생성된 인덱스는 무조건 Clustered로 생성되고 수정하거나 삭제를 할 수 없다.
인덱스조회는 sp_helpindex \'테이블명\'
※ 인덱스 생성시 주의 사항
㉠ 많은 것이 항상 더 좋은 것은 아니다(DML 후에 모든 인덱스를 갱신하기 위해 더 많은 노력이 필요하게 됨)
㉡언제 인덱스를 생성하는가 ?
- 열은 where 절 또는 조인 조건에서 자주 사용된다.
where 절에서 사용되는 열을 인덱스로 만든다. (포함열이 있는 인덱스)
데이터의 중복도가 높은 열은 인덱스를 만들어도 별 효용이 없다.
- 열은 광범위한 값을 포함한다.
- 열은 많은 많은 null 값을 포함한다.
- 둘 또는 이상의 열은 where 절 또는 조인 조건에서 자주 함께 사용된다.
- 테이블은 대형이고 대부분의 질의들은 행의 2~4%보다 적게 읽어 딜일 것으로 예상된다.
- 외래키가 사용되는 열에는 인덱스를 생성해주는 것이 좋다.
- JOIN에 자주 사용되는 열에는 인덱스를 생성해주는 것이 좋다.
㉢ 언제 인덱스를 생성해서는 안되는가?
- 테이블이 작다.
- 열이 질의의 조건으로 자주 사용되지 않는다.
- 대부분의 질의들은 행의 2~4%이상을 읽어 들일 것으로 예상된다.
- 테이블은 자주 갱신된다.
insert, update, delete가 얼마나 자주 일어나는지를 고려해야 한다.
인덱스는 단지 읽기에서만 성능을 향상시키며 데이터의 변경에서는 오히려 많은 부담을 줄 뿐이다.
그러므러 인덱스를 만들어서 select의 성능을 높일 것인지 , 아니면 만들지 않아서 insert/update/delete 시에
부하를 최소화할 것인지를 잘 결정해야 한다.
- 인덱스컬럼이 비교되기 전에 변형이 일어날 경우
select deptno from dept where substr(dname,1,3) = \'ABC\'
- 부정형(NOT,<>)으로 조건을 기술한 경우
select ename from emp where job <>\'SALES\'
- 인덱스 컬럼이 NULL로 비교되는 경우((NULL, NOT NULL의 사용)
select * from emp where ename is not null
- 옵티마이저가 특정 인덱스의 사용을 취사 선택할 경우 사용되지 않을 수 있음
조건의 순위(RANKING), 사용자의 힌트 통계정보에 의거하여 산출된 액세스 비용 등등...
select * from emp where job like \'AB%\' and empno = \'7890\'
⑶ 인덱스의 종류
[일반적인 분류]
① Unique index
지정된 열의 값이 고유함을 보장
② Non-unique index
데이터를 검색할 때 가장 빠른 결과를 보장
③ Single column index
하나의 열만 인덱스에 존재
④ composite index
여러 열을 결합하여 하나의 인덱스를 생성( 16개)
[MSSQL에서 분류]
① 클러스터형 인덱스
영어사전과 같이 책의 내용 자체가 순서대로 정렬이 되어 있어서 인덱스 자체가 책
다시 한번 말하지만 검색과 정렬 속도를 올리는 기능은 잘 사용했을 경우에 가능한 것이다.
다음으로 얘기할 것은 DB 서버마다 index에 대한 미세한 차이점을 보인다는 것이다. 참 짜증 나는 일이다. 하나로 통일이 되었으면좋으련만 하여간에 개발자 들이 노력을 많이 해야 할거 같다.
⑴ 인덱스의 특징
① 인덱스는 테이블의 값을 빠르게 액세스 하도록 하는 데이터베이스 객체다.
② 데이터를 빠르게 찾기 위한 B*TREE을 써서 디스크 입출력 횟수를 줄인다.
③ 데이터베이스 서버가 인덱스를 자동으로 사용하고 유지 보수한다. (클러스터드 인덱스)
④ 인덱스를 만들면 사용자가 직접 조작할 필요가 없게 된다.
⑤ 인덱스는 논리적으로도 물리적으로 테이블과는 독립적이다.
⑥ 언제든지 생성하거나 삭제할 수 있으며 이는 테이블이나 다른 인덱스에 영향을 주지 않는다면 의미
⑵ 인덱스의 생성과 조회
- 자동 생성 : PRIMARY KEY, UNIQUE 제약 조건을 정의할 때 인덱스가 자동으로 생성된다.
- 사용자가 생성 : 행에 대한 엑세스 속도를 하기 위해 Column에 non_unique 인덱스 또는 unique 인덱스 모두 생성가능
① 오라클
CREATE [UNIQUE or BITMAP] INDEX 인덱스명 ON 테이블명(컬럼...)[TABLESPACE tablespace_name];
㉠ UNIQUE : UNIQUE Index를 생성한다. (생략가능)
㉡ BITMAP : BITMAP Index를 생성한다. (생략가능)
㉢ 인덱스명 : 생성하고자 하는 인덱스 이름 예) 테이블명_인덱스명_키값
㉣ 테이블명 : 인덱스를 생송하고자 하는 테이블 이름
㉤ 컬럼 : 인덱스로 생성하고자 하는 컬럼 이름
㉥ tablespace_name : 인덱스가 위치할 (생성될) 테이블 스페이스 이름
현재 로그인한 사용자에 전체 인덱스 목록을 보는 방법
select index_name ,index_type, table_name, uniqueness from user_indexes;
위에 쿼리에서는 어떤 필드가 인덱스로 쓰였는지 알 수 없지만 아래 쿼리 좀더 자세히 알 수 있다.
select index_name , table_name, column_name from user_ind_columns;
② MSSQL
MSSQL도 오라클하고 변반 차이는 없지만 약간의 차이는 있다.
CREATE Clustered Index인덱스명ON테이블명(컬럼)
오라클은 UNIQUE 혹은 BITMAP 이라는 것이 쓰였지만 MSSQL은 Clustered 것이 쓰였다.
만약 Clustered 를 생략하면 자동으로 UNClustered 로 인덱스를 쓰겠다는 의미를 내포한다.
자동 생성된 인덱스는 무조건 Clustered로 생성되고 수정하거나 삭제를 할 수 없다.
인덱스조회는 sp_helpindex \'테이블명\'
※ 인덱스 생성시 주의 사항
㉠ 많은 것이 항상 더 좋은 것은 아니다(DML 후에 모든 인덱스를 갱신하기 위해 더 많은 노력이 필요하게 됨)
㉡언제 인덱스를 생성하는가 ?
- 열은 where 절 또는 조인 조건에서 자주 사용된다.
where 절에서 사용되는 열을 인덱스로 만든다. (포함열이 있는 인덱스)
데이터의 중복도가 높은 열은 인덱스를 만들어도 별 효용이 없다.
- 열은 광범위한 값을 포함한다.
- 열은 많은 많은 null 값을 포함한다.
- 둘 또는 이상의 열은 where 절 또는 조인 조건에서 자주 함께 사용된다.
- 테이블은 대형이고 대부분의 질의들은 행의 2~4%보다 적게 읽어 딜일 것으로 예상된다.
- 외래키가 사용되는 열에는 인덱스를 생성해주는 것이 좋다.
- JOIN에 자주 사용되는 열에는 인덱스를 생성해주는 것이 좋다.
㉢ 언제 인덱스를 생성해서는 안되는가?
- 테이블이 작다.
- 열이 질의의 조건으로 자주 사용되지 않는다.
- 대부분의 질의들은 행의 2~4%이상을 읽어 들일 것으로 예상된다.
- 테이블은 자주 갱신된다.
insert, update, delete가 얼마나 자주 일어나는지를 고려해야 한다.
인덱스는 단지 읽기에서만 성능을 향상시키며 데이터의 변경에서는 오히려 많은 부담을 줄 뿐이다.
그러므러 인덱스를 만들어서 select의 성능을 높일 것인지 , 아니면 만들지 않아서 insert/update/delete 시에
부하를 최소화할 것인지를 잘 결정해야 한다.
- 인덱스컬럼이 비교되기 전에 변형이 일어날 경우
select deptno from dept where substr(dname,1,3) = \'ABC\'
- 부정형(NOT,<>)으로 조건을 기술한 경우
select ename from emp where job <>\'SALES\'
- 인덱스 컬럼이 NULL로 비교되는 경우((NULL, NOT NULL의 사용)
select * from emp where ename is not null
- 옵티마이저가 특정 인덱스의 사용을 취사 선택할 경우 사용되지 않을 수 있음
조건의 순위(RANKING), 사용자의 힌트 통계정보에 의거하여 산출된 액세스 비용 등등...
select * from emp where job like \'AB%\' and empno = \'7890\'
⑶ 인덱스의 종류
[일반적인 분류]
① Unique index
지정된 열의 값이 고유함을 보장
② Non-unique index
데이터를 검색할 때 가장 빠른 결과를 보장
③ Single column index
하나의 열만 인덱스에 존재
④ composite index
여러 열을 결합하여 하나의 인덱스를 생성( 16개)
[MSSQL에서 분류]
① 클러스터형 인덱스
영어사전과 같이 책의 내용 자체가 순서대로 정렬이 되어 있어서 인덱스 자체가 책
소개글