DB/MySQL

이것이MySQL이다 - 02) 인덱스

록's 2023. 2. 9. 16:20
728x90
반응형

인덱스 생성/변경/삭제

 

 

인덱스 생성

인덱스 생성 문법

 

 

 

인덱스 제거

인덱스 삭제 형식
 

 
간단히 인덱스 삭제하는 구문

 

 

인덱스의 성능 비교

인덱스 없는 경우, 클러스터형 인덱스, 보조 인덱스를 설정하여 쿼리 속도 비교, 서버 부하 비교
  • 실습할 데이터베이스 만듬
    • CREATE DATABASE IF NOT EXISTS indexdb;
  • employees의 employees의 개수를 파악
    • USE indexdb;
    • SELECT COUNT(*) FROM employees.employees;
  • 테이블 3개로 복사
    • CREATE TABLE emp SELECT * FROM employees.employees ORDER BY RAND();
    • CREATE TABLE emp_c SELECT * FROM employees.employees ORDER BY RAND();
    • CREATE TABLE emp_Se SELECT * FROM employees.employees ORDER BY RAND();

 

 

 

인덱스의 성능 비교

 

인덱스 없는 경우, 클러스터형 인덱스, 보조 인덱스를 설정하여 쿼리 속도 비교, 서버 부하 비교
  • 테이블 순서 확인
    • SELECT * FROM emp LIMIT 5;
    • SELECT * FROM emp_c LIMIT 5;
    • SELECT * FROM emp_Se LIMIT 5;

 

 

 

 

 

 

 

 

 

 

 

인덱스 없는 경우, 클러스터형 인덱스, 보조 인덱스를 설정하여 쿼리 속도 비교, 서버 부하 비교
  • SHOW TABLE STATUS문으로 테이블에 인덱스 있는지 확인
    • 세 테이블 모두 인덱스 없음

 

 

 

인덱스의 성능 비교

인덱스 없는 경우, 클러스터형 인덱스, 보조 인덱스를 설정하여 쿼리 속도 비교, 서버 부하 비교
  • emp_c에는 클러스터형 인덱스(=Primary Key 인덱스)
  • emp_Se에는 보조 인덱스를 생성
    • ALTER TABLE emp_c ADD PRIMARY KEY(emp_no);
    • ALTER TABLE emp_Se ADD INDEX idx_emp_no (emp_no);
 

 

 

 

 

 

 

인덱스 없는 경우, 클러스터형 인덱스, 보조 인덱스를 설정하여 쿼리 속도 비교, 서버 부하 비교
  • 생성한 인덱스 적용
    • ANALYZE문 사용
      • ANALYZE TABLE emp, emp_c, emp_Se;

 

 

 

인덱스의 성능 비교

인덱스 없는 경우, 클러스터형 인덱스, 보조 인덱스를 설정하여 쿼리 속도 비교, 서버 부하 비교
  • 인덱스 생성 후 테이블 인덱스 확인

 

인덱스 없는 경우, 클러스터형 인덱스, 보조 인덱스를 설정하여 쿼리 속도 비교, 서버 부하 비교
  • 인덱스 생성 후 테이블 인덱스 확인
    • emp 테이블은 인덱스 없음
    • emp_c는 클러스터형(PRIMARY) 인덱스 생성되어 Data_free 영역이 줄어듬
    • emp_Se는 보조 인덱스 생성되어 데이터 변화 없음, 인덱스 페이지만 추가 생성됨.
 

 

인덱스 없는 경우, 클러스터형 인덱스, 보조 인덱스를 설정하여 쿼리 속도 비교, 서버 부하 비교
  • MySQL 전체의 시스템 상태 초기화
  • 인덱스 없는 emp 테이블 조회
    • SHOW GLOBAL STATUS LIKE 'Innodb_pages_read'; -- 쿼리 실행 전의 읽은 페이지 수
    • SELECT * FROM emp WHERE emp_no = 100000;
    • SHOW GLOBAL STATUS LIKE 'Innodb_pages_read'; -- 쿼리 실행 후에 읽은 페이지 수

 

 

 

 

인덱스 없는 경우, 클러스터형 인덱스, 보조 인덱스를 설정하여 쿼리 속도 비교, 서버 부하 비교
  • 클러스터형 인덱스가 있는 테이블 조회
    • SHOW GLOBAL STATUS LIKE 'Innodb_pages_read'; -- 쿼리 실행 전의 읽은 페이지 수
    • SELECT * FROM emp_c WHERE emp_no = 100000;
    • SHOW GLOBAL STATUS LIKE 'Innodb_pages_read'; -- 쿼리 실행 후의 읽은 페이지

 

 

 

 

인덱스 없는 경우, 클러스터형 인덱스, 보조 인덱스를 설정하여 쿼리 속도 비교, 서버 부하 비교
  • 보조 인덱스가 있는 테이블 조회
    • SHOW GLOBAL STATUS LIKE 'Innodb_pages_read'; -- 쿼리 실행 전의 읽은 페이지 수
    • SELECT * FROM emp_Se WHERE emp_no = 100000;
    • SHOW GLOBAL STATUS LIKE 'Innodb_pages_read'; -- 쿼리 실행 후의 읽은 페이지 수

 

인덱스 없는 경우, 클러스터형 인덱스, 보조 인덱스를 설정하여 쿼리 속도 비교, 서버 부하 비교
  • 데이터 중복도에 따른 인덱스의 효용
  • 인덱스를 만들지 않은 emp테이블의 gender(성별)열에 인덱스 생성
    • ALTER TABLE emp ADD INDEX idx_gender (gender);
    • ANALYZE TABLE Emp; -- 생성한 인덱스를 통계에 적용시킴
    • SHOW INDEX FROM Emp;
    • SELECT * FROM emp WHERE gender = 'M' LIMIT 500000;
  • 쿼리 비용 약 2만 정도 나옴
  • Data Read 19MB 정도 읽음

 

 

 

인덱스 없는 경우, 클러스터형 인덱스, 보조 인덱스를 설정하여 쿼리 속도 비교, 서버 부하 비교
  • 강제로 인덱스를 사용하지 못하게 한 후에 실행 계획 비교
    • SELECT * FROM emp IGNORE INDEX (idx_gender) WHERE gender = 'M' LIMIT 500000;
  • 쿼리 비용 약 3만 정도 나옴

 

인덱스의 성능 비교

실습 결과
  • 데이터의 중복도가 높은 경우에, 인덱스 사용하는 것이 효율이 있음
  • 하지만 인덱스의 관리 비용과 INSERT 등의 구문에서는 오히려 성능이 저하될 수 있다는 점 등을 고려하면 인덱스가 반드시 바람직하다고 보기는 어려움

 

 

 

 

 

 

 

 

결론 : 인덱스를 생성해야 하는 경우와 그렇지 않은 경우

 

인덱스에 대한 결론

  • 인덱스는 열 단위에 생성
    • 두 개 이상의 열을 조합해서 인덱스 생성 가능
  • WHERE절에서 사용되는 열에 인덱스를 만들어야 함
    • 테이블 조회 시 WHERE절의 조건에 해당 열이 나오는 경우에만 인덱스 주로 사용
  • WHERE절에 사용되더라도 자주 사용해야 가치가 있음
    • SELECT문이 자주 사용 되어야 효과적
    • INSERT문이 자주 사용되고 생성된 인덱스가  클러스터형이면 효율 감소
  • 데이터의 중복도가 높은 열은 인덱스 만들어도  효과 없음
    • 인덱스의 관리 비용 때문에 인덱스가 없는 편이 나은 경우도 있음
  • 외래 키 지정한 열에는 자동으로 외래 키 인덱스가 생성

 

 

인덱스에 대한 결론

  • JOIN에 자주 사용되는 열에는 인덱스를 생성해 주는 것이 좋음
  • INSERT/UPDATE/DELETE가 얼마나 자주 일어나는지 고려해야 함
    • 인덱스는 단지 읽기에서만 성능 향상
    • 데이터의 변경에서는 오히려 부담
  • 클러스터형 인덱스는 테이블당 하나만 생성 가능
    • 클러스터형 인덱스를 생성할 열은 범위(BETWEEN, >, < 등의 조건)로 사용하거나 집계 함수를 사용하는 경우 아주 적절하게 사용
  • 클러스터형 인덱스가 테이블에 아예 없는 것이 좋은 경우도 있음
  • 사용하지 않는 인덱스는 제거
    • 공간 확보 및 데이터의 입력 시에 발생되는 부하 줄임

 

728x90
반응형