호스팅 사용법
호스팅 신청하기
도메인 연결하기
홈페이지 올리기
이메일 설정하기
서버 사용법
FTP사용법
메일사용법
텔넷/리눅스
MySQL사용법
MSSQL사용법
리눅스명령어 모음
시스템사용법
서버세팅
네트워크/보안
L4 매뉴얼
백업/미러링
호스팅용어 모음
프로그램 사용법
알리미사용법
제로보드설치방법
워드프레스설치방법
태터툴즈설치방법
그누보드설치방법
우편번호DB사용법
폼메일사용법
주문서사용법
예전 게시판/방명록
전자지불 서비스


HOME > 호스팅 > 사용안내 > MySQL사용법 > 인덱스
소개  기초문법  인덱스  조인  쿼리성능 
모델링  함수목록  PHP연동  DB백업  phpMyAdmin 사용법 

Index란?
    - Index는 전체 내용물 중에서 특정한 부분을 바로 찾을 수 있는 목차나 색인이다.
    - Index는 사용자가 임의적으로 생성하고, 변경하고, 삭제할 수 있는 데이터베이스(Database) 내의 실질적으로 저장되는 물리적 구조체이다.
    - Index는 좋은 것이며, 좋은 것이 많이 있으면 더 좋아질 것이라는 생각은 문제가 있다. Index는 Optimizer가 최적의 처리경로를 찾도록 하는 전략적 요소로 Index의 생성/삭제/변경은 전략적 측면에서 시행되어야 한다.

    - Index는 일반적으로 오름차순으로 정렬된다. MySQL의 경우 Alter 명령을 통해 오름차순으로 정렬된 Index를 내림차순으로 정렬하여 역정렬에 부담을 줄일 수 있으나 이후 insert되는 자료는 오름차순으로 정렬되어 점점 부담이 가중된다. 따라서 내림차순으로 정렬하고자 하는 Index는 insert되는 데이터가 없거나 극히 적은 경우에 적합하다.
 

Index의 종류

    ① Index 생성 문법
  create [unique|fulltext|spatial] index index_name [index_type]
      on tbl_name (index_col_name,...)

  index_col_name:
      col_name [(length)]

  index_type:
      using {btree | hash}

  - 사용예: mysql> create index id_index using btree on lookup (id);
  - []는 지정하지 않아도 된다는 것을 의미함.

    Index 생성 시 index_type을 지정하지 않으면 BTREE가 기본 Index로 지정된다.

    ② 부분 Index(Partial Index)
  ㉮ mysql > create index part_of_name on customer (name);
  ㉯ mysql > create index part_of_name on customer (name(10));

    ㉮와 같이 Index를 생성하면 name의 모든 크기가 Index로 생성이 되나 ㉯처럼 생성할 경우 name의 처음 10자를 이용하여 Index를 생성한다. 만약 200만명 회원의 영문이름을 8자만을 이용하여 Index를 생성해도 약 16GB의 물리적 공간이 필요하다. 이 경우 부분 Index를 생성하면 전체적인 물리적 공간이 줄어 데이터 입력 시 속도가 보다 빠르다.
  mysql > alter table phone_book add index (last_name(4));
  mysql > select * from phone_book where last_name = 'Smith';
    위와 같이 부분 Index를 생성 시 Optimizer는 Smit 영문을 사용하여 Index를 검색하게 된다. Smith, Smitty 등은 모두 Smit Index를 사용하여 테이블을 검색하게 되며 이때 Index에서 테이블로의 랜덤액세스(Random Access)가 발생하게 된다.
    즉, Smith 철자로 Index가 구성되면 딱 1번의 테이블 액세스가 발생하지만 Smit 철자를 이용할 경우 Index에 다수의 Smit가 생성되어 속도저하의 원인이 될 수 있다. 이런 형태의 Index를 생성하는 경우는 대량메일시스템의 이메일 리스트처럼 대량 의 메일주소가 입력되어 파일사이즈를 절약하면서도 속도가 그렇게 중요한 요소가 아닌 페이지에 적합하다.

    ③ 결합 Index
    하나의 컬럼으로 만든 Index를 싱글 Index라고 한다면 2개 이상의 컬럼으로 만든 Index를 결합 Index 또는 복합 Index라고 한다.
  mysql> alter table phone_book add index (last_name, first_name);
  mysql> select * from phone_book
     -> where last_name = 'Woodward'
     -> and first_name = 'Josh';

    Optimizer는 하나의 SQL에서 테이블당 오직 하나의 Index만을 사용할 수 있다.(단, UNION은 예외) 따라서 동시에 조건을 만족해야 하는 경우에 결합 Index를 사용하면 테이블 액세스를 줄여줘 성능을 향상시킬 수 있다.
mysql > select * from tab1 where col1 = 'A' and col2 between 113 and 115;


    ▶ 결합 Index를 Index1: COL1 + COL2로 구성했을 때
    - B-Tree 방식으로 COL1 = A이고 COL2 = 113인 첫 번째 Row를 바로 찾는다.
    - ROWID를 이용하여 테이블의 Row를 액세스한다.
    - 다음 Row를 차례로 스캔하면서 COL1이 A가 아니거나 COL2가 115보다 클 때까지
    테이블의 Row를 액세스하고 그렇지 않으면 처리를 종료한다.
    ▶ 결합 Index를 Index2: COL2 + COL1로 구성했을 때
    - B-Tree 방식으로 COL2 = 113이고 COL1 = A인 첫 번째 Row를 바로 찾는다.
    - ROWID를 이용하여 테이블의 Row를 액세스한다.
    - COL2가 115보다 큰 값이 나올 때까지 계속해서 스캔한다. 스캔한 Row는 COL1이
    A인지를 체크하여 성공하면 ROWID를 이용하여 테이블을 액세스한다.
    - COL2가 115보다 커지면 처리를 종료한다.
    결합 Index는 선행 컬럼이 =로 사용되지 않으면 뒤에 있는 컬럼이 비록 =을 사용하더라도처리범위는 줄어들지 않는다.
    이것은 결합 Index의 컬럼 순서를 결정하는 매우 중요한 요소가 된다.
    첫 번째 컬럼과 두 번째 컬럼만의 문제가 아니라 어떤 위치에 있는 컬럼이든지 자기보다 앞에 있는 컬럼이 =로
    사용되지 않으면 자신의 역할은 단순한 체크기능으로 전락하고 전혀 범위를 줄여주는 역할을 할 수 없다.
 

Index의 구조

    ① B-Tree Index
- B-Tree 구조 -

    - B-Tree 또는 Balanced tree라고도 한다. 대부분의 DBMS에서 기본적으로 제공하는 Index이면서 가장 보편적인 Index이다.
    - 각 노드(Node)마다 테이블 컬럼값 및 물리적 위치값(ROWID 또는 유사한 형태)을 저장하고 있다.
    - 테이블 컬럼값을 기준으로 정렬되어 있기 때문에 between, >, < 와 같은 범위처리에서 좋은 성능을 낸다.
 

Index 관리

    B-Tree Index는 데이터의 갱신/수정/삭제 등으로 인해 시간이 지날수록 Index가 비효율적으로 변한다.
     mysql> optimize table ASADAL_BOARD;

    더불어 Optimizer가 사용하는 통계정보 또한 부정확해진다. 이런 경우 아래와 같이 optimize 명령어를 통해 이를 해결할 수 있다.
    테이블 최적화는 테이블의 모든 데이터를 다시 읽어 Index 갱신(Index rebuild)하기 때문에 시간이 오래 걸린다.
    또한 최적화하는 동안 테이블 Lock이 걸리므로 주의해야 한다.
    응급상황이 아니면 반드시 cron을 이용하여 사이트 방문자가 적은 새벽시간에 하도록 한다.
    단지 테이블에 대한 통계정보를 갱신하고자 할 경우 아래와 같이 analyze 명령어를 사용할 수 있다.
    analyze 명령 실행 시 read lock이 걸리므로 응급상황이 아니면 반드시 cron을 이용하여 사이트 방문자가 적은 새벽시간에 하도록 한다.
     mysql> analyze table ASADAL_BOARD;

 
사이트명 : 코비스 | 회사명 : (주)아사달 | 대표이사 : 서창녕, 심재춘 | 대표전화 : 070-7510-3007 | 팩스번호 : 02-2026-2008
사업자등록번호 : 206-81-24351 | 법인등록번호 : 110111-1940504 | 통신판매업신고 : 제18-890호 | 벤처확인번호 : 051134532200563
(우편번호 : 153-803) 서울특별시 금천구 가산동 371-28번지 우림라이온스밸리 A동 8층 (주)아사달
Copyright ⓒ kobis.net All rights Reserved.
페이지 맨 위로 이동하기