9월9일(수)
인덱스(Index)의 핵심 원리와 조회 성능 최적화 정리
1. 인덱스(Index)란 무엇인가요? (쉽고 직관적인 비유)
- 인덱스의 정의: 데이터베이스 테이블의 검색 성능과 속도를 획기적으로 높여주기 위해 사용하는 특별한 물리적 자료 구조입니다.
- 3초 만에 이해하는 두꺼운 전공 책 비유:
우리가 1,000페이지짜리 두꺼운 파이썬 전공 서적에서 'Pandas'라는 단어가 나오는 페이지를 찾는다고 가정해 봅시다.- 인덱스가 없는 상태 (Full Table Scan): 1페이지부터 1,000페이지까지 모든 글자를 한 줄씩 눈으로 다 읽어가며 찾아야 합니다. 시간이 엄청 오래 걸리겠죠?
- 인덱스가 있는 상태 (Index Range Scan): 책 맨 뒤의 '찾아보기(색인, Index)' 페이지를 열어 'Pandas: 247p, 412p'라는 정보를 확인한 뒤, 해당 페이지로 한 번에 날아가 데이터를 찾습니다.
- 컴퓨터 내부에서의 동작 원리:
기본적으로 테이블에 데이터를 삽입하면 기본키(PK) 순서대로 차곡차곡 쌓이게 됩니다. 이때 정렬되지 않은 컬럼(예: 나이, 이름 등)에서 조건에 맞는 데이터를 찾으려면 모든 행을 다 검사해야 합니다.
하지만 특정 컬럼에 인덱스를 생성하면, 데이터베이스가 내부적으로 해당 컬럼을 미리 순서대로 정렬해 둔 '인덱스용 가상 테이블'을 따로 관리합니다. 덕분에 데이터가 시작되는 지점과 끝나는 지점만 찾으면 그 사이의 값들을 고속으로 조회할 수 있습니다.
2. 인덱스를 다루는 핵심 SQL 명령어 (DDL)
인덱스는 데이터베이스 구조(뼈대)의 일부이므로 DDL(데이터 정의어) 명령어를 사용하여 정교하게 관리합니다.
-
인덱스 생성하기 (
CREATE INDEX/ADD INDEX):-- 1. 전용 명령어로 생성하기 (users 테이블의 age 컬럼에 인덱스 지정) CREATE INDEX idx_age ON users(age); -- -- 2. ALTER 문을 이용해 추가하기 ALTER TABLE student ADD INDEX birthdate_idx(birthdate); --- 참고: 중복을 허용하지 않는 고유한 값(예: 이메일, 주민번호 등)에 인덱스를 걸 때는
UNIQUE키워드를 앞에 붙여 데이터 무결성까지 동시에 지킬 수 있습니다.
- 참고: 중복을 허용하지 않는 고유한 값(예: 이메일, 주민번호 등)에 인덱스를 걸 때는
-
인덱스 전체 조회하기 (
SHOW INDEX):
내가 만든 테이블에 어떤 인덱스 책갈피들이 꽂혀있는지 한눈에 검사합니다.SHOW INDEX FROM student; --- 발견: 실제로 조회를 해보면 내가 직접 만든 인덱스 외에도 기본키(PRIMARY KEY)나 외래키(FOREIGN KEY)가 자동으로 인덱스 명단에 올라와 있는 것을 볼 수 있습니다. 즉, 기본키와 외래키는 태생부터 인덱스 역할을 하고 있었던 셈이죠!
-
인덱스 삭제하기 (
DROP INDEX):
더 이상 쓰지 않거나 성능을 오히려 저하시키는 인덱스는 영구히 제거합니다.ALTER TABLE student DROP INDEX birthdate_idx; --
개발자를 위한 MySQL 9.0 최신 꿀팁 (Invisible Index)
전통적인 데이터베이스(MySQL 8.0 등)에서는 인덱스를 만드는 순간 옵티마이저가 즉시 쿼리 조회에 써먹기 시작했습니다. 이 때문에 운영 중인 서버에서 쿼리 실행 계획이 꼬여 성능이 갑자기 내려앉는 무서운 장애가 빈번했죠.
하지만 MySQL 9.0부터는 새로 생성하는 모든 인덱스가 기본적으로 보이지 않는 상태(Invisible Index)로 생성됩니다. 인덱스가 잘 만들어졌는지 꼼꼼하게 사전 테스트를 마친 후, 아래 명령어를 실행해야만 비로소 옵티마이저가 인덱스를 가져다 씁니다. 운영 안전성이 극대화된 훌륭한 기능입니다!ALTER TABLE users ALTER INDEX idx_email VISIBLE; --
3. 인덱스를 무력화시키는 '나쁜 쿼리 습관' 피하기
인덱스를 아무리 완벽하게 만들어 두어도, 내가 작성한 SQL 쿼리 문법이 엉망이면 컴퓨터는 인덱스 책갈피를 완전히 무시하고 다시 테이블 전체를 샅샅이 뒤지는 풀 테이블 스캔(Full Table Scan)을 감행합니다. 이를 방지하기 위한 실무 최적화 작성 규칙 4가지를 꼭 기억하세요.
① LIKE 검색 시 앞부분에 와일드카드(%)를 쓰지 마세요.
LIKE '%김' ➡️ 인덱스 작동 안 함 ❌: 김으로 끝나는 글자를 찾으려면 결국 단어를 끝까지 다 읽어봐야 하므로 풀 스캔이 일어납니다.
LIKE '김%' ➡️ 인덱스 정상 작동 ⭕: 가나다순으로 정렬된 인덱스 가상 테이블에서 '김'으로 시작하는 페이지로 단숨에 이동할 수 있습니다.
② 부정형 검색(NOT, !=, <>)은 가급적 피하세요.
WHERE grade != 'A' ➡️ 인덱스 작동 안 함 ❌: "A가 아닌 모든 것"을 찾으려면 결국 테이블을 전부 열어봐야 합니다. 가능하면 긍정형 조건(WHERE grade IN ('B', 'C'))으로 우회해 작성하는 버릇을 들이는 것이 좋습니다.
③ 인덱스가 걸린 컬럼을 수식이나 함수로 가공하지 마세요.
WHERE age * 2 > 40 ➡️ 인덱스 작동 안 함 ❌: 컬럼 자체에 연산이 걸리면 데이터베이스는 정렬된 인덱스 값을 그대로 쓸 수 없어 풀 스캔을 합니다.
WHERE age > 40 / 2 ➡️ 인덱스 정상 작동 ⭕: 수식 연산은 우측(상수)에서 처리해 컬럼 원본을 온전히 유지해 주어야 합니다.
④ OR 연산자 대신 UNION을 고려하세요.
OR는 컴퓨터가 한 번 스캔할 때 여러 조건을 다 확인하느라 인덱스를 제대로 타지 못할 때가 많습니다. 이럴 때는 각각의 조건을 따로 쪼개어 인덱스를 태운 뒤, 합집합(UNION)으로 묶어주는 것이 실무에서 훨씬 더 빠르게 동작합니다.
4. 인덱스를 무작정 많이 만들면 안 되는 이유 (Trade-off)
인덱스가 조회 속도를 획기적으로 올린다면 모든 컬럼에 다 걸어두면 되지 않을까요? 절대 안 됩니다!
- 쓰기 성능 저하 (CUD 패널티):
인덱스를 추가한다는 것은 데이터를 조회하는 별도의 통계 테이블을 하나 더 만들어서 관리한다는 뜻입니다.
따라서 데이터를 새로 등록하고(INSERT), 수정하고(UPDATE), 지울 때(DELETE)마다 원본 테이블뿐만 아니라 인덱스 가상 테이블까지 매번 새롭게 쓰고 정렬하는 이중 부담이 발생하여 시스템 속도가 현저히 느려집니다. - 저장 공간 낭비:
인덱스가 늘어날 때마다 데이터베이스의 물리적인 저장 공간을 추가로 잠식하게 됩니다. - 최적의 인덱스 설계 기준:
- 테이블 전체 행 중에서 데이터 중복도가 낮고(분포도가 좋고), 고유한 값이 많은 컬럼(예: ID, 이메일 등 카디널리티가 높은 컬럼)에 우선적으로 인덱스를 설계해야 성능이 잘 나옵니다.
- 조회(
SELECT) 횟수가 쓰기/수정(INSERT,UPDATE) 횟수보다 압도적으로 많은 테이블에 한해서 집중적으로 인덱스를 적용해 주는 것이 지혜로운 아키텍처 공식입니다.
5. 내 쿼리가 인덱스를 잘 타고 있는지 감시하는 법 (EXPLAIN)
내가 작성한 쿼리가 실제로 인덱스 책갈피를 타고 빠르게 달리는지, 아니면 테이블 전체를 기어 다니며 풀 스캔하고 있는지 감시하는 방법입니다.
EXPLAIN SELECT * FROM users WHERE age = 20; --쿼리문 앞에 EXPLAIN만 한 칸 띄우고 붙여서 실행해 주면, 데이터베이스 옵티마이저가 수립한 내부 실행 계획표를 모니터링할 수 있습니다.
이때 결과표에서 다음 2가지 컬럼을 매의 눈으로 확인하셔야 합니다:
type(가장 중요!): 데이터를 어떤 방식으로 조회했는지를 말해주는 척도입니다.ALL: 최악의 상태. 풀 테이블 스캔으로 인덱스를 전혀 타지 못함.index: 풀 인덱스 스캔. 전체 테이블을 도는 것보단 낫지만 역시 비효율적.range: 성공적인 상태! 인덱스를 활용해 지정된 특정 범위만 낚아 올림.const/ref: 최상의 상태. 단 1건의 데이터를 인덱스로 즉시 조회함.
key: 쿼리를 돌릴 때 실제로 적용되어 작동한 인덱스의 이름이 표시됩니다. 이 칸이 비어있다면 인덱스가 무시되고 있다는 뜻입니다.rows: 쿼리를 성공시키기 위해 컴퓨터가 테이블에서 실제로 접근(스캔)한 데이터 행의 추정치입니다. 인덱스를 잘 타면 이rows수치가 수백만 건에서 단 몇 건으로 극적으로 줄어드는 마법을 관측하실 수 있습니다.
