[PostgreSQL] 7. 데이터 인덱싱_(2)

4. 인덱스 명령어
  1) 종류별 인덱스 생성하기

      CREATE INDEX <인덱스 이름> ON <테이블 이름> [USING<인덱스 종류>]
      (
        <컬럼이름> [ASC | DESC],
        ...
      )

    (1) 단일 컬럼 인덱스
      -가장 기본이 되는 단일 컬럼 인덱스를 생성하기 위해서는 CREATE INDEX 명령어를 사용해야 한다
      -EX) CREATE INDEX name_idx ON ramen(name);
        >name_idx라는 이름의 인덱스를 생성
        >ON 명령어 다음에 ramen테이블이름을 설정하고, 괄호 안에 컬럼이름과 순서를 지정한다
        >방향을 지정하지 않으면 디폴트 값으로 오름차순으로 인덱스가 생성된다

    (2) 복합 컬럼 인덱스
      -여러 개의 컬럼에 대한 인덱스를 말한다
      -EX) CREATE INDEX item_idx ON rating(item_type ASC, item_id DESC);
        >item_idx인덱스를 생성했다
        >item_type에 대해서는 오름차순, item_id에 대해서는 내림차순

    (3) 고유 인덱스
      -대상으로 지정한 컬럼에 중복되는 값을 갖지 않도록 하는 인덱스이다
      -예를 들어 음료테이블의 이름 컬럼에 고유 인덱스를 설정하면 같은 이름을 갖는 음료를 테이블에 추가할 수 없다
      -EX) CREATE UNIQUE INDEX dring_name ON drink(name);
        >위의 구조에 UNIQUE라는 설정 값을 중간에 끼워넣으면 된다
        
    (4) 해시 인덱스
      -해시 인덱스를 설정하기 위해서는 테이블이름과 컬럼 설정 사이에 USING HASH를 추가해야 한다
      -사용자 테이블이름 컬럼에 해시 인덱스를 생성하는 명령어

        CREATE INDEX hash_idx ON users USING HASH(name);
     >이와 같이 이름에 해시 인덱스를 생성하면 '=' 연산자를 이용한 일치검색을 B-Tree방식의 인덱스보다 빠르게 수행한다

    (5) GIN 인덱스
      -해시 인덱스와 마찬가지로 USING 절에 GIN이라고 표시하여 인덱스를 생성할 수 있다
      -GIN 인덱스를 활용해서 전문검색을 효율적으로 하기 위해서는 
        tsvector라는 데이터 형식을 사용하여 쿼리를 해야 한다
      -GIN 인덱스 역시 tsvector 데이터 형식에 대해서만 인덱스를 형성할 수 있고

       일반적인 TEXT 타입의 바로인덱스를 생성할 수 없다
      -따라서 to_tsvector 라는 함수를 이용해 TEXT형식을 tsvector로 변환해야 한다

        SELECT to_tsvector('english', content) FROM boards;

        >tsvector로 변환하면 의미를 갖는 단어만 남게 된다
        >a, the, on과 같은 연결하는 단어는 추출되지 않는다
      
      -'time' 이라는 내용을 검색하기 위해서는 ts_query라는 함수를 사용해서 연산한다
      
        WHERE to_tsvector('english', content) @@ to_tsquery('time');

        >검색하고자 하는 tsvector에 tsquery를 @@로 연산하면 해당 단어가 포함된 로우를 찾을 수 있다
        >위의 연산을 할 때 미리 인덱스를 만들려면 GIN 인덱스를 만들면 된다
        >아래는 개시판 테이블 내용 컬럼에 GIN 인덱스를 설정하는 명령어이다

          CREATE INDEX gin_idx ON boards USING GIN(to_tsvector('english', content));

    (6) 부분 인덱스
      -컬럼의 모든 값에 대해서 인덱스를 생성하는 것이 아니라 특정 조건에 맞는 값들에 대해서만 생성한 인덱스 이다
      -부분적으로 인덱스를 생성하면 적은 용량으로 효율적인 쿼리를 생성할 수 있다
      -예를 들어 매운라면에 대해 유통기한을 자주 조회한다고 하면 

        매운라면의 유통기한 정보만 부분 인덱스를 생성가능하다
      
        CREATE INDEX spicy_idx ON ramen(shelf_life) WHERE is_spicy;


  2) 인덱스 수정 및 삭제하기
    -인덱스를 미리 생성하고 운영하다가 만약 인덱스의 이름을 체계적으로 수정하기 위해서는 
      ALTER INDEX 명령어를 사용하면 된다
    -ALTER INDEX 명령으로 인덱스의 이름과 테이블 스페이스를 설정할 수는 있지만,
      인덱스가 대상으로 하는 컬럼이나 테이블 속성을 변경할 수 는 없다
    -만약 컬럼과 테이블 정보를 변경해야 한다면 인덱스를 삭제하고 새로 생성한다
    -EX) 이전에 만들었던 spicy_index의 이름을 partial_index로 변경하는 명령이다 

        ALTER INDEX spicy_index RENAME TO partial_index;
        >변경할 인덱스의 이름을 먼저 작성하고 RENAME TO라는 명령 뒤에 변경할 이름을 작성해 준다

        DROP INDEX partial_index;
        >인덱스를 삭제하고자 할때 DROP INDEX 명령어 사용


  3) 실시간 데이터베이스 인덱스 설정하기
    -인덱스를 생성하려고 하는 테이블의 로우 수가 이미 엄청나게 많은 경우 

      인덱스를 생성하려고 하면 많은 시간이 소요된다
    -이미 많을 때 인덱스를 생성하는 도중에 "읽기 전용" 으로 잠금이 걸린다
    -잠금이 걸리게 되면 해당 테이블에 새러운 정보를 추가하거나 수정할 수 없다
    -이와 같은 문제를 해결하기 위해 postgreSQL은 인덱스 생성 및 삭제에 동시처리 기능을 탑재하고 있다
    -CONCURRENTLY 라는 옵션을 추가해서 인덱스를 생성하면 "읽기전용" 잠금이 발생하지 않는다

      CREATE INDEX CONCURRENTLY spicy_idx ON ramen(shelf_life) 
      WHERE is_spicy;

      DROP INDEX CONCURRENTLY spicy_idx;