MySQL 요약 노트-5

MySQL 요약 노트 (2022년 1월 ~ 2022년 3월까지 공부했던 내용 정리)
혼자 공부하는 SQL책과 한빛미디어에서 제공하는 유튜브 강의로 공부함

📌 6강 인덱스

✔️ 06-1 인덱스 개념

• 인덱스(index): 데이터를 빠르게 찾을 수 있도록 도와주는 도구
  • 책 뒤의 '찾아보기'='색인', '인덱스'
  • 인덱스의 문제점: 필요 없는 인덱스를 만들면 데이터베이스가 차지하는 공간만 더 늘고, 인덱스를 이용해서 데이터를 찾는 것이 전체 테이블을 찾아보는 것보다 느려질 수 있음
  • 데이터베이스에 인덱스를 생성해 놓아도, 인덱스를 사용해서 검색하는 것이 빠를지 아니면 전체 테이블을 검색하는 것이 빠를지 MySQL이 알아서 판단함
  • 인덱스의 장점과 단점
    • 장점: SELECT 문으로 검색하는 속도가 매우 빨라짐, 그 결과 컴퓨터의 부담이 줄어들어서 결국 전체 시스템의 성능이 향상됨
    • 단점: 인덱스도 공간을 차지해서 데이터베이스 안에 추가적인 공간이 필요함, 처음에 인덱스를 만드는 데 시간이 오래 걸릴 수 있음, SELECT가 아닌 데이터의 변경 작업(INSERT, UPDATE, DELETE)이 일어나면 오히려 성능이 나빠질 수도 있음
  • 클러스터형 인덱스: 기본 키로 지정하면 자동 생성되고 테이블에 1개만 만들 수 있음, 기본 키로 지정한 열을 기준으로 자동 정렬됨
    • 기본 키(PRIMARY)로 지정하면 자동으로 해당 열에 클러스터형 인덱스가 생성됨(기본 키는 테이블에 하나만 지정 가능)
    • 자동으로 정렬되는 클러스터형 인덱스(클러스터형 인덱스가 생성된 열로 데이터가 자동 정렬됨)
  • 보조 인덱스: 고유 키로 지정하면 자동 생성되고 여러 개를 만들 수도 있지만 자동 정렬되지 않음
    • 고유 키(UNIQUE)로 지정하면 자동으로 해당 열에 보조 인덱스가 생성됨(고유 키는 테이블에 여러 개 지정 가능)
    • 정렬되지 않는 보조 인덱스(보조 인덱스를 생성해도 데이터의 내용이나 순서는 변경되지 않음)
    • 보조 인덱스는 여러 개 생성할 수 있지만 데이터베이스의 공간을 차지하므로 꼭 필요한 열에만 적절히 보조 인덱스를 생성할 것!
  • SHOW INDEX 문: 테이블의 인덱스 정보 확인(Key_name, Column_name)
    • Non_Unique 부분: 고유하지 않다는 의미로 중복이 허용되는가?(0은 False, 1은 True)
    • Key_name 부분: PRIMARY(기본 키)나 열 이름
    • Column_name 부분: 인덱스가 만들어진 해당 열의 이름

    SHOW INDEX FROM 테이블_이름;

  • 고유 인덱스: 인덱스의 값이 중복되지 않는다
    • 기본 키나 고유 키로 지정하면 고유 인덱스가 생성됨
    • 그 외의 인덱스는 단순 인덱스

✔️ 06-2 인덱스의 내부 작동

• 균형 트리: '자료 구조'에 나오는 범용적으로 사용되는 데이터의 구조
  • 트리에서 뿌리(루트), 줄기(중간), 잎(리프)
• 균형 트리 구조
  • 균형 트리는 데이터를 검색할 때(SELECT 문) 아주 뛰어난 성능을 발휘함
  • 노드: 데이터가 저장되는 공간
    • 루프 노드: 노드의 가장 상위 노드
    • 중간 노드: 루트 노드와 리프 노드의 중간에 끼인 노드들
    • 리프 노드: 제일 마지막에 존재하는 노드
  • 노드는 MySQL에서 페이지라고 부름
    • 페이지는 최소한의 저장 단위 = 16Kbyte(16384byte) 크기
  • 균형 트리 예시
    1) 루트 페이지 검색
    2) 리프 페이지로 이동 후 검색
  • 페이지 분할: 새로운 페이지를 준비해서 데이터를 나누는 작업
    • 인덱스를 구성하면 데이터 변경 작업(INSERT, UPDATE, DELETE)시 성능이 나빠짐(입력이 느려짐)
    • 페이지 분할이 일어나면 MySQL이 느려지고, 너무 자주 일어나면 성능에 큰 영향을 줌
  • 페이지 분할 예시
    1) 새로운 데이터 INSERT된다고 가정
    2) 새로운 삽입으로 인해 리프 페이지의 페이지 분할 작업이 일어남
    3) 루트 페이지의 페이지 분할 작업으로 중간 페이지가 형성됨
    4) 새로운 루트 페이지 생성
• 전체 테이블 검색(Full Table Scan): 데이터를 처음부터 끝까지 검색하는 것
• 인덱스의 구조
  • 클러스터형 인덱스 구성
    • 데이터 페이지가 정렬되고 균형 트리 형태의 인덱스가 형성됨
      • 클러스터형 인덱스는 영어사전과 같음(영어사전은 알파벳 순서로 구성된 찾아보기)
    • 각 페이지의 인덱스로 지정된 열의 첫 번째 값을 가지고 루트 페이지를 만듦
    • 인덱스 페이지의 리프 페이지는 데이터 그 자체임
  • 보조 인덱스 구성
    • 보조 인덱스는 데이터 페이지를 건드리지 않음, 별도의 장소에 인덱스 페이지를 생성
      • 보조 인덱스는 일반 책의 찾아보기와 같은 개념
    • 데이터의 위치는 페이지 번호+#위치로 기록됨
• 인덱스 검색: 클러스터형 또는 보조 인덱스를 이용해서 데이터를 검색하는 것으로 속도는 인덱스를 사용하지 않았을 때보다 빠름
  • 클러스터형 인덱스에서 데이터 검색
    1) 가장 먼저 루트 페이지는 읽음
    2) 리프 페이지(=데이터 페이지) 읽음
  • 보조 인덱스에서 데이터 검색
    1) 가장 먼저 루트 페이지는 읽음
    2) 리프 페이지 읽음
    3) 데이터 페이지 읽음

✔️ 06-3 인덱스의 실제 사용

• 인덱스 생성 문법: PRIMARY KEY 문법, UNIQUE 문법, CREATE INDEX문
  • PRIMARY KEY 문법은 클러스터형 인덱스
  • UNIQUE 문법은 보조 인덱스
  • CREATE INDEX 문으로 생성되는 인덱스는 보조 인덱스임
  • UNIQUE: 중복이 안 되는 고유 인덱스를 만드는 것, 생략시 중복 허용됨
  • ASC 또는 DESC: 인덱스의 오름차순(ASC) 또는 내림차순(DESC)

  CREATE [UNIQUE] INDEX 인덱스_이름
    ON 테이블_이름 (열_이름) [ASC| DESC]

• 인덱스 제거 문법: DROP INDEX문
  • 기본 키, 고유 키로 자동 생성된 인덱스는 DROP INDEX로 제거하지 못함
    • 클러스터형 인덱스와 보조 인덱스가 섞여 있을 때는 보조 인덱스를 먼저 제거하는 것이 좋음
      • 클러스터형 인덱스를 먼저 제거해도 되지만, 데이터를 쓸데없이 재구성해서 시간이 더 오래 걸림
    • ALTER TABLE 문으로 기본 키나 고유 키를 제거하면 자동으로 생성된 인덱스도 제거할 수 있음
    • 기본 키를 제거하려면 먼저 관련된 외래 키를 제거해야 함

    DROP INDEX 인덱스_이름 ON 테이블_이름

  • 외래 키 이름: information_schema 데이터베이스의 referential_constraints 테이블 조회

    SELECT table_name, constraint_name
      FROM information_schema.referential_constraints
      WHERE constraint_schema = '데이터베이스_이름';

  • 먼저 외래 키 제거, 기본 키 제거

    ALTER TABLE 테이블_이름
      DROP FOREIGN KEY 열_이름; -- 외래 키 제거
    ALTER TABLE 테이블_이름
      DROP PRIMARY KEY; -- 기본 키 제거

• 인덱스 정보: SHOW INDEX 문

  SHOW INDEX FROM 테이블_이름

• 인덱스의 크기: SHOW TABLE STATUS 문
  • 테이블에 생성된 인덱스의 크기 확인

  SHOW TABLE STATUS LIKE '테이블_이름'

• 생성한 인덱스 실제 적용하기: ANALYZE TABLE 문

  ANALYZE TABLE 테이블_이름 -- 지금까지 만든 인덱스를 모두 적용하기

• 인덱스의 활용: SELECT ~ FROM ~ WHERE 문
  • [Execution Plan] 창에서 Full Table Scan을 제외하고 나머지는 모두 인덱스를 사용했다는 의미
  • WHERE 문에서 열에 연산이 가해지면 인덱스를 사용하지 않음

  SELECT 열_이름
    FROM 테이블_이름 
    WHERE 조건

• 인덱스를 효과적으로 사용하는 방법
  • 인덱스는 열 단위에 생성됨
  • WHERE 절에서 사용되는 열에 인덱스를 만들어야 함
  • WHERE 절에 사용되더라도 자주 사용해야 가치가 있음
  • 데이터의 중복이 높은 열은 인덱스를 만들어도 별 효과가 없음
  • 클러스터형 인덱스는 테이블 당 하나만 생성할 수 있음
  • 사용하지 않는 인덱스는 제거함

📌 참고문헌

우재남. (2021). 혼자 공부하는 SQL. 한빛미디어. p274-p326.