Ch07. 인덱스 - 인덱스 생성, 조회, 삭제

CREATE INDEX : 인덱스 생성하기

CREATE INDEX 인덱스이름 ON 테이블이름 (컬럼1, 컬럼2, ...);

• 인덱스 이름: 생성할 인덱스에 고유한 이름을 붙여준다. 보통 idx_테이블명_컬럼명과 같은 규칙으로 지으면 관리하기 편하다.
• 테이블 이름: 인덱스를 생성할 대상 테이블.
• 컬럼: 인덱스를 구성할 컬럼. 하나 이상의 컬럼을 지정할 수 있다.

우리는 "items" 테이블에서 특정 "item_name"으로 상품을 검색하는 경우가 많다고 가정하고 "item_name" 컬럼에 대한 인덱스를 생성해 보겠다.

CREATE INDEX idx_items_item_name ON items (item_name);

• 이 쿼리를 실행하면, 데이터베이스는 "items" 테이블의 모든 "item_name" 값을 읽어 정렬한 뒤, "idx_items_item_name" 인덱스를 디스크에 생성한다.
• 테이블의 데이터가 많을수록 이 작업은 시간이 오래 걸릴 수 있다.

SHOW INDEX : 테이블의 인덱스 정보 확인하기

"show index" 명령어를 사용하면 테이블에 걸려있는 모든 인덱스의 정보를 한눈에 볼 수 있다.

SHOW INDEX FROM items;

쿼리 결과

• Key_name: 인덱스의 이름이다. 우리가 방금 만든 "idx_items_item_name"이 보인다.
• Column_name: 해당 인덱스가 어떤 컬럼을 기반으로 만들어졌는지 보여준다.
• PRIMARY와 seller_id: 여기서 흥미로운 점은, 우리는 "item_name" 컬럼에 대한 인덱스만 만들었는데 "PRIMARY"와"fk_items_sellers"라는 인덱스가 이미 존재한다는 것이다.
  • MySQL에서는 "PRIMARY KEY"(기본 키)나 "FOREIGN KEY"(외래 키) 제약조건을 설정하면, 해당 컬럼에 대해 자동으로 인덱스를 생성한다. 
  • "item_id"는 기본 키이므로 PRIMARY 인덱스가, "seller_id"는 외래키이므로 "fk_items_sellers" 인덱스가 이미 존재했던 것이다. (매우 중요한 사실이니 꼭 기억해 두자!)
• "UNIQUE" 제약조건도 마찬가지로 인덱스가 생성된다.
  • Non_unique: "1"이면 중복 값을 허용하는 인덱스, "0"이면 중복을 허용하지 않는 고유 인덱스("UNIQUE" 또는"PRIMARY KEY")라는 의미다.
    • "idx_items_item_name"은 "1" 이므로, 상품명이 같은 다른 상품이 등록될 수 있다는 것을 알 수 있다.
• Cardinality: 인덱스에 저장된 유니크한 값의 개수에 대한 추정치다. 이 값이 높을수록 중복도가 낮다는 의미이며, 인덱스의 성능이 좋다고 판단할 수 있다. 

sellers 인덱스 확인

show index from sellers;

쿼리 결과

• PRIMARY: "seller_id" 기본 키로 인덱스가 자동 생성되었다.
• seller_name : "seller_name"에 UNIQUE 제약조건으로 인덱스가 자동 생성되었다.

유니크 제약조건에 인덱스를 자동 생성하는 이유

• 유니크 제약조건은 컬럼 내 데이터의 유일성을 보장해야 한다.
• 따라서 새로운 데이터를 삽입(INSERT)하거나 기존 데이터를 수정(UPDATE)할 때마다, 입력하려는 값이 테이블에 이미 존재하는지 빠르게 확인해야 한다.
• 만약 인덱스가 없다면 이 중복 검사를 위해 매번 풀 테이블 스캔이 발생하여 쓰기 성능이 크게 저하될 것이다.

DROP INDEX : 인덱스 삭제하기

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

 

방금 만들었던 "idx_items_item_name" 인덱스를 삭제해 보자.

DROP INDEX idx_items_item_name ON items;

• "idx_items_item_name" 인덱스를 구성하던 데이터 구조가 디스크에서 완전히 사라진다
• "item_name"을 조건으로 검색하면 다시 풀 테이블 스캔이 발생할 것이다
  • 물론, 원본 "items" 테이블의 데이터에는 아무런 영향이 없다.

인덱스가 정말 사용되는지 확인하는 법 (EXPLAIN)

데이터베이스에는 쿼리를 어떤 방식으로 최적화해서 실행할지 계획하는 기능이 있는데, 이것을 쿼리 옵티마이저(최적화기)라 한다.

 

인덱스를 만들었다고 해서, 데이터베이스가 모든 "SELECT" 문에 항상 그 인덱스를 사용하는 것은 아니다. 데이터의 분포나 쿼리의 형태에 따라, 데이터베이스 옵티마이저는 인덱스를 사용하는 것보다 풀 테이블 스캔이 더 빠르겠다고 판단할 수도 있다. 추가로 사용할 수 있는 인덱스가 여러개 있다면 어떤 인덱스를 사용할지도 선택한다.

 

우리가 만든 인덱스가 실제로 쿼리에 사용되는지 확인하려면 "EXPLAIN"이라는 명령어를 쿼리문 앞에 붙여보면 된다

 

explain select * from items where item_name = '게이밍 노트북';

쿼리 결과

• type: 가장 중요하게 봐야 할 부분이다. "type"은 데이터베이스가 테이블에 어떻게 접근할지를 나타낸다.
  •  ALL : 풀 테이블 스캔(Full Table Scan)을 의미한다.
    • 즉, "items" 테이블의 처음부터 끝까지 모든 데이터를 하나씩 다 읽어서 조건에 맞는 데이터를 찾는다는 뜻이다.
    • 지금은 데이터가 25개뿐이라 문제가 없지만, 실무에서처럼 데이터가 수백만 건에 달한다면 상상만 해도 끔찍한 성능 저하를 일으키는 주범이 된다.
  • ref: "=" 조건이나 JOIN에서 인덱스를 사용했다는 의미이다.
  • range: 범위 검색(Between, >, <, >= 등)에서 인덱스를 사용했다는 의미다.
• key: 쿼리를 실행할 때 사용한 인덱스를 보여준다.
  • 이 값이 "NULL"이라는 것은 어떤 인덱스도 사용하지 못했다는 것을 명확하게 알려준다.
  • "item_name" 컬럼으로 데이터를 찾고 있지만, 해당 컬럼에 인덱스가 없기 때문에 당연한 결과다.
• rows: 옵티마이저가 쿼리를 처리하기 위해 탐색할 것으로 예측하는 행의 수다.
  • 현재 "items" 테이블의 전체 데이터가 25개이므로, 결국 테이블 전체를 다 훑어보겠다고 말하는 것과 같다.
  • "type"이 "ALL"이니 당연히 전체 행의 개수가 표시된다.
  • 이 값이 작을수록 효율적인 쿼리라 할 수 있다. (실제 실행하는게 아니라 데이터베이스 나름의 통계 데이터를 기반으로 하는 예측 정보다. 따라서 정확하게 맞는 것은 아니다.)
• filtered: 테이블에서 읽어온 행들 중에서 "WHERE" 조건으로 필터링되고 난 후, 최종적으로 남을 것으로 예측되는 행의 비율이다.
  • 여기서는 25개의 행을 모두 읽은 후, 그 중 10%인 2.5개 정도의 행이 "item_name= '게이밍 노트북'" 조건을 만족할 것이라고 예측하고 있다. (실제 실행하는게 아니라 데이터베이스 나름의 통계 데이터를 기반으로 하는 예측 정보다. 따라서 정확하게 맞는 것은 아니다.)
• Extra: 데이터를 가져온 후에 "WHERE" 절의 조건(item_name = '게이밍 노트북')을 사용해 필터링 작업을 수행했다는 의미다.
  • 만약 인덱스를 효율적으로 사용했다면, 처음부터 조건에 맞는 데이터만 골라서 가져왔을 것이다.
  • 하지만 인덱스가 없으니 일단 모든 데이터를 다 가져와서, 그 후에 조건에 맞는지 일일이 비교하는 비효율적인 방식으로 일하고 있음을 보여준다.

결론적으로, 이 실행 계획은 "item_name" 컬럼에 인덱스가 없어서 "items" 테이블의 모든 행(25개)을 전부 스캔하는, 매우 비효율적인 방식으로 쿼리가 동작할 것임을 알려준다.