인덱스 시작하기 가이드

인덱스 시작하기 가이드

인덱스는 데이터베이스 쿼리를 빠르게 만들기 위한 핵심 도구예요. 이 가이드는 MariaDB의 여러 인덱스 종류와 그 성격, 그리고 쓰는 방법을 설명해요. 기본 키(PRIMARY KEY), 유니크 인덱스(UNIQUE), 일반 인덱스(INDEX)를 만들고 관리하는 법과, 효과적인 인덱스를 고르고 유지하기 위한 고려 사항을 다루죠.

출처: https://mariadb.com/docs/server/mariadb-quickstart-guides/mariadb-indexes-guide

참고: MariaDB에서 SQL 문장 안의 KEY와 INDEX는 대체로 같은 의미로 쓰여요.

인덱스 종류 개요

네 가지 주요 인덱스가 있어요.

  • 기본 키(Primary Key) — 고유하고 NULL이 아니어야 해요.
  • 유니크 인덱스(Unique Index) — 고유해야 하지만 NULL 값을 가질 수 있어요.
  • 일반 인덱스(Plain/Regular Index) — 고유할 필요가 없어요.
  • 전문 인덱스(Full-Text Index) — 전문(full-text) 검색을 위해 사용돼요.

기본 키(Primary Key)

기본 키는 테이블의 각 레코드를 고유하게 식별해요. 값은 고유해야 하고 NULL을 포함할 수 없으며, 테이블당 하나만 가질 수 있어요.

InnoDB 고려 사항:

  • InnoDB 테이블에서는 기본 키가 다른 모든 인덱스의 접미사로 포함돼요. 따라서 기본 키를 컴팩트하게(가능하면 적절한 정수형으로) 유지하는 게 성능과 저장 효율에 중요해요.
  • 명시적 기본 키도 UNIQUE 인덱스도 없다면, InnoDB가 보이지 않는 6바이트 클러스터 인덱스를 자동으로 만들어요.

AUTO_INCREMENT 활용: 숫자 기본 키에는 AUTO_INCREMENT 속성을 흔히 붙여 새 행마다 고유 ID를 자동 생성하게 해요.

CREATE TABLE `Employees` (
  `ID` TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  `First_Name` VARCHAR(25) NOT NULL,
  `Last_Name` VARCHAR(25) NOT NULL,
  `Position` VARCHAR(25) NOT NULL,
  PRIMARY KEY (`ID`)
);

기존 테이블에 기본 키를 추가할 때는 ALTER TABLE을 써요. CREATE INDEX로는 기본 키를 만들 수 없어요.

ALTER TABLE Employees ADD PRIMARY KEY(ID);

유니크 인덱스(Unique Index)

유니크 인덱스는 인덱스된 컬럼(또는 컬럼 조합)의 모든 값이 고유함을 보장해요. 다만 기본 키와 달리 NULL 값을 저장할 수 있어요. SQL에서 NULL은 다른 NULL과 같지 않다고 보기 때문에, 유니크 제약이 있어도 NULL은 여러 개 들어갈 수 있어요.

테이블 생성 시, 또는 이후에 만들 수 있어요.

CREATE TABLE `Employees` (
  `ID` TINYINT(3) UNSIGNED NOT NULL,
  `Employee_Code` VARCHAR(25) NOT NULL,
  `First_Name` VARCHAR(25) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `UK_EmpCode` (`Employee_Code`)  -- 이름을 붙이는 게 좋은 습관
);

-- ALTER TABLE 로 추가
ALTER TABLE Employees ADD UNIQUE `UK_HomePhone` (`Home_Phone`);

-- CREATE UNIQUE INDEX 로 추가
CREATE UNIQUE INDEX `IX_Position` ON Employees(Position);

여러 컬럼에 걸친 유니크 인덱스도 만들 수 있어요. 인덱스가 여러 컬럼을 다룰 때 MariaDB는 왼쪽 일부(leftmost parts)만 사용할 수 있어요(해시 인덱스 제외).

일반 인덱스(Plain Index)

일반 인덱스는 고유성을 강제하지 않고, 데이터 조회 속도를 높이는 데 주로 사용돼요.

CREATE TABLE t2 (a INT NOT NULL, b INT, INDEX `idx_a_b` (a,b));
INSERT INTO t2 VALUES (1,1), (2,2), (2,2);  -- 중복 (2,2) 허용
SELECT * FROM t2;

전문 인덱스(Full-Text Index)

텍스트 데이터에 대한 전문 검색을 수행하는 데 사용돼요.

인덱스 선택 시 고려 사항

  • 쿼리에 맞춰 인덱스 만들기 — 애플리케이션의 WHERE 절, JOIN 조건, ORDER BY 절에 맞는 인덱스를 추가해요.
  • 과도한 인덱스 피하기 — 인덱스가 많아지면 저장 공간을 차지하고 INSERT·UPDATE·DELETE를 느리게 만들어요.
  • 테이블 크기 영향 — 인덱스는 아주 작은 테이블보다 큰 테이블(버퍼 크기보다 큰)에서 더 큰 속도 향상을 줘요.
  • EXPLAIN 사용 — EXPLAIN 문장으로 쿼리를 분석해서 인덱스가 효과적으로 쓰이는지 확인하고, 인덱싱이 도움이 될 컬럼을 찾아봐요.
  • LIKE '%word%' — 앞에 와일드카드가 붙은 LIKE 쿼리는 표준 BTREE 인덱스를 효과적으로 못 쓰고 전체 테이블 스캔으로 이어질 수 있어요. 전문 인덱스가 없다면 말이죠.
  • 대용량 테이블 — 매우 큰 테이블에서는 빈 테이블에 인덱스를 만들고 데이터를 넣는 것보다, 데이터를 먼저 로드한 뒤 인덱스를 만드는 게 더 빠른 경우가 많아요.

인덱스 보기와 제거

  • SHOW INDEX FROM table_name; — 테이블의 모든 인덱스 정보를 보여줘요.
  • SHOW CREATE TABLE table_name; — 모든 인덱스 정의를 포함한 CREATE TABLE 문장을 보여줘요.

인덱스가 거의·전혀 사용되지 않는다면 제거를 고려해요. 사용되지 않는 인덱스도 데이터 수정 작업 중 오버헤드를 일으키거든요.

더 알아보기