ABOUT ME

-

Today
-
Yesterday
-
Total
-
  • MySQL - INDEX 정리
    Database/MySQL 2020. 2. 25. 17:28

    MySQL의 INDEX 관련 공부를 하던 중 '이것이 MySQL이다' 라는 책에 대한 강의가 유튜브에 올라온것을 보고 참고하였다. DB에서 데이터를 가져오는데 많은 시간이 걸리기 때문에 백엔드에서 데이터베이스 튜닝은 매우 중요하다. 그중에서 많은 비중을 차지하는 INDEX에 대해 정리하였다.

    # unique not null -> clustered index로 사용됨
    create table tbl2(
        a int unique not null,
        b int unique,
        c int unique
    );
    
    create table tbl3(
        a int primary key,
        b int unique not null,
        c int unique not null
    );
    
    show index from tbl3;

    Key_name: PRIMARY_KEY(Clustered index)는 'PRIMARY'로, UNIQUE(Secondary Index)는 컬럼 이름으로 지정

    • PRIMARY_KEY 없이 보조 인덱스로만 구성된 테이블을 생성할 수 있음
    • 책의 색인과 같이 테이블에 있는 데이터를 빠르게 찾을 수 있도록 함
    • 한 페이지의 크기는 16k Bytes
    • 전체 데이터에서 중복되는 데이터가 적을 때 사용하는 것이 좋다
      • 남성 또는 여성 두 개로만 분류되는 컬럼은 인덱스로 사용하기 부적합
        → cardinality(중복도)가 높을 수록 적합

     

    INDEX 장점

    • SELECT 쿼리문을 이용해 데이터를 빠르게 가져올 수 있음
    • 전체 데이터 중 10~15% 내의 데이터를 가져오고자 할 때
      • 그 이상의 데이터는 Full Scan이 더 좋은 성능을 보일 수 있음
    • 자주 사용 되는 특정 컬럼(여러 컬럼을 조합하기도 함)
    • 조건절(WHERE)에 자주 등장하는 컬럼
    • ORDER BY 절에 자주 사용되는 컬럼
      • INDEX는 정렬되어 저장되기 때문에 빠르게 사용 가능

     

    INDEX 단점

    • 저장 공간을 차지 - DB의 10%정도를 index에서 사용
    • 처음 인덱스를 생성하는데 시간이 소요됨
    • INSERT, SELECT, DELETE 시 많은 시간이 소요됨

     

    INDEX 종류(MySQL 기준)

    Clustered Index(영어 사전)

    • 데이터가 정렬된(PK 또는 UNIQUE NOT NULL 컬럼을 기준으로 오름차순) 상태(유지)로 저장
    • 테이블 당 한 개만 생성
    • 제약조건 PRIMARY KEY에 의해 자동으로 생성
    • 제약조건 UNIQUE NOT NULL에 의해 자동으로 생성
      • 단, 한 테이블에 PRIMARY KEY와 UNIQUE NOT NULL이 함께 있을 경우, PRIMARY KEY를 Clustered Index로 사용
    • 데이터 검색 순서
      루트 페이지 → 리프 페이지(= 데이터 페이지)
      = 리프 페이지가 곧 데이터 페이지

    FNT - '푸니타', KAI - '카아이'를 추가로 입력할 경우

    • 리프 페이지가 모두 차있을 경우 페이지 분할이 일어난다 → DB부하가 걸린다

     

    Secondary Index(Non Clustered Index - 책의 '찾아보기')

    • 테이블에 여러개의 보조 인덱스를 생성할 수 있음
    • 제약조건 UNIQUE에 의해 자동으로 생성
    • 별도의 인덱스가 생성되지만, 원본 데이터는 변경되지 않음
    • 데이터 검색 순서
      루트 페이지 → 리프 페이지 → 데이터 페이지(Heap page)
      • Heap: 정렬 기준 없이 구성된 테이블(입력된 순서대로 저장)
      • 리프 페이지데이터 페이지를 가리키는 주소값(RID)을 가지고 있다.

    FNT - '푸니타', KAI - '카아이'를 추가로 입력할 경우

    • 리프 페이지가 모두 차있더라도 페이지 분할이 일어나지 않는다.
      ⇒ INSERT, DELETE, UPDATE 성능이 Clustered Index보다 낫다.

    SELECT 구문에는 Clustered Index가, INSERT, UPDATE, DELETE 구문에는 Secondary Index가 더 좋은 성능을 발휘한다.

     

    한 테이블에 Clustered 및 Secondary Index가 혼합되어있을 경우

    # mixed index
    create database if not exists testDB;
    use testdb;
    create table mixedTbl(
        userID char(8) not null,
        name varchar(10) not null,
        addr char(2)
    );
    
    insert into mixedTbl value('LSG', '이승기', '서울');
    insert into mixedTbl value('KBS', '김범수', '경남');
    insert into mixedTbl value('KKH', '김경호', '전남');
    insert into mixedTbl value('JYP', '조용필', '경기');
    insert into mixedTbl value('SSK', '성시경', '서울');
    insert into mixedTbl value('LJB', '임재범', '서울');
    insert into mixedTbl value('YJS', '윤종신', '경남');
    insert into mixedTbl value('EJW', '은지원', '경북');
    insert into mixedTbl value('JKW', '조관우', '경기');
    insert into mixedTbl value('BBK', '바비킴', '서울');

    # userID를 PK로 지정 -> 클러스터 인덱스 생성(루트 페이지 + 리프 페이지(데이터 페이지))
    alter table mixedTbl
        add constraint PK_mixedTbl_userID
            primary key (userID);
    
    # name을 UNIQUE로 지정 -> 보조 인덱스 생성
    alter table mixedTbl
        add constraint UK_mixedTbl_name
            unique (name);
    
    show index from mixedTbl;

    • 만일 혼합 인덱스에서 보조 인덱스가 데이터 페이지의 주소를 가리키지 않고 PK를 가리킬 경우, 데이터 페이지에 새로운 값이 입력되면 보조 인덱스가 가리키고 있는 주소값이 모두 변경되어야 함
      → INSERT, DELETE, UPDATE의 성능이 매우 떨어지게 됨

     

    인덱스 생성 & 변경 + 삭제

    # Syntax 
    # 생성
    CREATE INDEX index_name ON table_name (column_list)
    # 기본적으로 create index를 이용할 경우 보조 인덱스가 생성된다.
    
    # 삭제
    DROP INDEX index_name on table_name
    
    # 반드시 보조 인덱스 삭제 후 클러스터 인덱스 삭제
    # 1.secondary index 삭제
    drop index UK_mixedTbl_name on mixedTbl;
    drop index idx_mixedTbl on mixedTbl;
    drop index idx_mixedTbl_name_addr on mixedTbl;
    
    # 2.clustered index 삭제(pk)
    alter table mixedTbl
        drop primary key;
    
    # 3. 만일 다른 테이블에서 클러스터 인덱스(pk)를 참조하고 있을 경우 에러 발생
    # -> pk를 참조하는 테이블(referTbl)의 제약조건 이름 확인
    select table_name, constraint_name
    from information_schema.REFERENTIAL_CONSTRAINTS
    where CONSTRAINT_SCHEMA = 'sqlDB';
    # -> 참조하는 테이블의 외래키와 클러스터 인덱스의 외래키 제거
    alter table referTbl
        drop foreign key refertbl_ibfk_1;
    alter table mixedTbl
        drop foreign key;
    • 기본 키로 설정된 클러스터 인덱스의 이름은 'PRIMARY'
      • 클러스터형 인덱스를 삭제할 경우 'DROP INDEX PRIMARY ON table_name
    • ALTER TABLE을 이용해 기본 키를 제거할 경우 클러스터형 인덱스가 제거 됨
    • 혼합 인덱스에서 인덱스를 전부 제거하고자 할 경우, 보조 인덱스부터 삭제

    INDEX를 이용한 테스트 -Notion

    REFERENCES

    [이것이 MySQL이다] 09. MySQL 인덱스(1)

    [이것이 MySQL이다] 09. MySQL 인덱스(2)

    [이것이 MySQL이다] 09. MySQL 인덱스(3)

    [이것이 MySQL이다] 09. MySQL 인덱스(4)

    [mysql] 인덱스 정리 및 팁

    'Database > MySQL' 카테고리의 다른 글

    MySQL - INDEX 정리  (0) 2020.02.25
    ONLY_FULL_GROUP_BY + query 실행 순서  (0) 2020.02.06
    MySQL를 DataGrip 연동할 때 발생하는 에러(KST)  (0) 2020.01.28

    댓글 0

Designed by Tistory.