MariaDB 강좌 / 인덱스 만들기: 단일·복합 인덱스와 열 순서

인덱스는 행을 찾고 정렬하는 데 사용할 별도의 자료 구조이며, 복합 인덱스의 열 순서는 어떤 조회에 유용한지를 좌우합니다. 자주 쓰는 조건·정렬·결과 열과 기존 인덱스를 확인한 뒤 추가하고, 읽기 이득뿐 아니라 쓰기·저장 비용도 함께 평가해야 합니다.

인덱스는 데이터 복사와 유지 비용을 갖는 구조

테이블의 모든 행을 찾는 대신 정렬된 키를 이용해 필요한 범위를 좁힐 수 있습니다. 하지만 INSERT·UPDATE·DELETE 시 인덱스도 유지해야 하므로 많을수록 항상 좋은 것은 아닙니다. 사용 빈도·선택되는 행 비율·결합·정렬을 기준으로 후보를 정합니다. 추가한 키의 실제 선택은 EXPLAIN으로 실행 계획 읽고 인덱스 전후 비교하기로 확인하고, 결과 의미를 보존하는 조건·페이지 변경은 조회 조건·정렬·페이지 나누기를 개선하기에서 대조합니다.

기존 자동 인덱스부터 조회하기

학습용 MariaDB 서버의 shopcourse에 course_user로 접속합니다. 명령줄 클라이언트와 UTF-8 SQL 파일 실행 방법대로 코드를 UTF-8 파일에 저장하고 SOURCE C:/mariadb-lab/lesson.sql;처럼 실제 파일 경로로 실행할 수 있습니다. SQL 문은 운영체제 터미널이 아닌 MariaDB 연결 안에서 실행합니다.

먼저 SELECT VERSION(), DATABASE();와 SHOW TABLES;로 대상 서버·DB·이름 중복을 확인합니다. 아래 c39_customers, c39_products, c39_orders, c39_items는 이 글의 독립된 연습용 이름입니다. 이미 존재하면 정의와 행을 확인하고, 보관할 자료가 없는 이 글의 실습 테이블임이 확실할 때만 마지막 절의 정리 순서로 지운 뒤 다시 만듭니다. 기존 강좌의 동명 없는 테이블이나 운영 데이터는 삭제하지 않습니다.

USE shopcourse;
CREATE TABLE c39_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c39_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c39_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c39_order_customer FOREIGN KEY (customer_id)
  REFERENCES c39_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c39_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c39_item_order FOREIGN KEY (order_id)
  REFERENCES c39_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c39_item_product FOREIGN KEY (product_id)
  REFERENCES c39_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c39_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c39_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c39_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c39_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

SHOW INDEX FROM c39_orders;

주문 기본키와 고객 외래키를 위해 필요한 인덱스가 이미 있을 수 있습니다. 자동 생성 여부와 이름을 실제 출력으로 확인하고 이름만 보고 내용이 다르다고 판단하지 않습니다. 복합키의 열 순서는 Seq_in_index로 읽습니다.

고객·시각·번호 순으로 복합 인덱스 만들기

CREATE INDEX idx_c39_customer_date ON c39_orders(customer_id,ordered_at,order_id);
EXPLAIN SELECT order_id,ordered_at,status FROM c39_orders
WHERE customer_id=1 AND ordered_at>='2026-01-01' AND ordered_at<'2026-02-01'
ORDER BY ordered_at,order_id;
SELECT order_id FROM c39_orders
WHERE customer_id=1 AND ordered_at>='2026-01-01' AND ordered_at<'2026-02-01'
ORDER BY ordered_at,order_id;
SHOW INDEX FROM c39_orders;

예제 결과는 주문 101·102번입니다. customer_id는 같은 고객으로 범위를 좁히고 ordered_at은 기간을 제한하며 order_id는 같은 시각의 순서를 명확히 합니다. 작은 네 행에서는 최적화기가 전체 스캔을 선택해도 이상하지 않으므로, 이 데이터만으로 성능 향상을 보장하지 않습니다. 조회에 맞는 인덱스 설계 안내를 참고하세요.

선두 열·범위·커버링을 단정하지 않고 읽기

복합 인덱스는 일반적으로 왼쪽 선두 열부터 조건을 활용하는지가 중요합니다. 고객 조건 없이 날짜만 지정한 조회가 같은 효율적인 범위 접근을 사용한다고 가정하지 않습니다. 그렇다고 선두 조건이 없으면 어떤 형태의 인덱스 읽기도 절대 불가능하다고 단정할 수는 없습니다.

범위 조건 뒤의 열, 정렬 방향, 문자열 앞부분 검색, 결과 열 포함 여부에 따라 사용 방식이 달라집니다. 필요한 열이 인덱스에 포함되면 테이블 행 접근을 줄이는 커버링이 가능할 수 있지만 무조건 모든 열을 인덱스에 넣으면 키 크기와 쓰기 비용이 커집니다. 실제 EXPLAIN의 선택 키와 Extra를 확인합니다.

불필요한 중복과 삭제 의존 관계 확인하기

인덱스 이름이 달라도 열·순서·고유성·접두 길이가 같거나 선두가 겹칠 수 있습니다. 기존 고객 외래키용 인덱스와 새 복합 인덱스가 어떻게 관리되는지도 SHOW INDEX로 확인합니다. 지원 인덱스가 하나뿐인 외래키를 남긴 채 해당 인덱스를 삭제하면 제약 유지 때문에 거절될 수 있습니다.

삭제가 필요하면 실제 제약의 지원 키와 다른 쿼리 사용 여부를 먼저 확인하고 필요한 대체 인덱스를 준비합니다. 기본키·UNIQUE는 단순 성능 도구만이 아니므로 사용되지 않는 것처럼 보여도 업무상 고유 조건을 함부로 없애지 않습니다.

같은 조회 결과와 실행 계획을 대조하기

인덱스를 추가해도 결과 행과 정렬 의미는 같아야 합니다. 대표 쿼리와 데이터 분포에서 실행 계획·실제 읽은 행·지연·쓰기 부담을 비교하고, 테스트 환경과 측정 방법을 기록합니다. 캐시가 데워진 두 번째 실행만 보고 인덱스의 효과라고 결론 내리지 않습니다.

반복하기 전 현재 DB와 실제 정의·행을 확인하고 필요한 자료를 백업합니다. 보관할 데이터가 없는 이 글의 전용 실습 객체만 아래 순서로 정리합니다. 외래키가 있으면 자식부터 지웁니다. DROP TABLE은 구조와 데이터를 모두 없애며 일반적인 ROLLBACK으로 복원되지 않으므로 취소 가능한 DML 실습과 분리합니다.

DROP TABLE shopcourse.c39_items;
DROP TABLE shopcourse.c39_orders;
DROP TABLE shopcourse.c39_products;
DROP TABLE shopcourse.c39_customers;