MariaDB 강좌 / 조회 조건·정렬·페이지 나누기를 개선하기

조회 개선은 결과의 의미를 유지하면서 조건·정렬·페이지 접근을 인덱스와 맞추는 작업입니다. 날짜 범위와 고유한 보조 정렬을 명시하고 OFFSET 방식과 마지막 정렬값을 이용하는 방식을 비교하되, 개선 효과는 실제 실행 계획과 데이터로 확인해야 합니다.

결과 보존과 비용 감소를 따로 확인하기

빠른 쿼리가 되어도 날짜 경계나 동점 행을 빠뜨리면 개선이 아닙니다. 바꾸기 전후 반환 기본키와 정렬을 먼저 대조하고 그다음 실행 계획·읽은 행·지연을 비교합니다. WHERE 조건을 줄여 빨라진 결과를 같은 조회라고 취급하지 않습니다. 기본 정렬·키셋 경계는 ORDER BY·LIMIT로 정렬하고 조회 범위 제한하기, 인덱스 전후 계획을 읽는 과정은 EXPLAIN으로 실행 계획 읽고 인덱스 전후 비교하기과 연결해 검토합니다.

기간과 정렬을 비교할 주문 데이터 준비하기

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

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

USE shopcourse;
CREATE TABLE c41_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 c41_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 c41_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_c41_order_customer FOREIGN KEY (customer_id)
  REFERENCES c41_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c41_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_c41_item_order FOREIGN KEY (order_id)
  REFERENCES c41_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c41_item_product FOREIGN KEY (product_id)
  REFERENCES c41_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c41_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c41_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c41_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 c41_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);

CREATE INDEX idx_c41_time ON c41_orders(ordered_at,order_id);

주문 네 건을 날짜·번호로 정렬할 수 있습니다. 데이터가 작아 전체 스캔이 선택될 수 있으므로 이 실습은 정답 행과 조건 의미를 검증하는 데 초점을 맞춥니다. 큰 데이터의 효과는 대표 분포로 따로 측정합니다.

날짜 함수와 명시적 범위의 결과 비교하기

SELECT order_id FROM c41_orders WHERE DATE(ordered_at)='2026-01-10'
ORDER BY order_id;
SELECT order_id FROM c41_orders
WHERE ordered_at>='2026-01-10' AND ordered_at<'2026-01-11' ORDER BY order_id;

두 결과 모두 101번입니다. 범위 조건은 시간·소수 초가 포함된 날 전체의 의미를 명확하게 나타냅니다. MariaDB 11.1부터는 지원 조건에서 DATE·YEAR 식을 인덱스 범위로 자동 변환할 수 있으므로, 열에 함수가 있으면 언제나 인덱스를 못 쓴다고 단정하지 않습니다.

DATE·YEAR 조건의 버전별 최적화를 확인하세요. 명시적 범위는 버전 간 의도를 읽기 쉽지만 이것만으로 성능 우위를 보장하지 않습니다. 같은 SELECT에 EXPLAIN을 적용해 실제 키·범위를 비교합니다. 날짜를 문자열로 포맷한 다음 비교하는 방식은 표시와 검색의 역할을 섞을 수 있으므로 타입도 확인해야 합니다.

동점까지 포함한 마지막 값으로 다음 페이지 찾기

SELECT order_id,ordered_at FROM c41_orders
ORDER BY ordered_at DESC,order_id DESC LIMIT 2;
SELECT order_id,ordered_at FROM c41_orders
WHERE ordered_at<'2026-02-05 12:00:00'
 OR(ordered_at='2026-02-05 12:00:00' AND order_id<103)
ORDER BY ordered_at DESC,order_id DESC LIMIT 2;

첫 페이지는 104·103번, 103번을 마지막 값으로 보관한 다음 페이지는 102·101번입니다. 시각이 같은 행을 빠뜨리지 않기 위해 번호 조건도 함께 사용했습니다. 정렬이 DESC면 뒤의 값은 더 작은 값이며 ASC로 바꾸면 비교 방향도 함께 검토해야 합니다.

마지막 값 조회는 깊은 OFFSET을 피하는 데 도움이 될 수 있지만 임의의 페이지 번호로 바로 이동하는 기능과는 다릅니다. 두 요청 사이 데이터가 수정되면 목록이 달라질 수 있으므로 완전히 고정된 스냅샷이 필요한지 업무 요구를 확인합니다. ORDER BY·LIMIT과 페이지 경계를 참고하세요.

같은 시각의 경계 행도 확인하려면 다음 변경을 트랜잭션 안에서만 시험합니다.

START TRANSACTION;
UPDATE c41_orders SET ordered_at='2026-02-05 12:00:00' WHERE order_id=104;
SELECT order_id,ordered_at FROM c41_orders
ORDER BY ordered_at DESC,order_id DESC LIMIT 1;
SELECT order_id,ordered_at FROM c41_orders
WHERE ordered_at<'2026-02-05 12:00:00'
   OR (ordered_at='2026-02-05 12:00:00' AND order_id<104)
ORDER BY ordered_at DESC,order_id DESC LIMIT 1;
ROLLBACK;

첫 결과는 104번, 다음 결과는 같은 시각의 103번입니다. 날짜 조건만 사용하면 이 행을 빠뜨릴 수 있습니다. ROLLBACK 뒤에는 104번의 원래 시각이 유지되는지 확인합니다.

필요한 열·선택 비율·문자 검색을 함께 검토하기

필요 없는 큰 본문 열까지 SELECT *로 가져오면 전송·메모리·테이블 접근 비용이 늘 수 있습니다. 결과 열을 줄이는 것과 조건 행을 줄이는 것을 구분하고 인덱스에 포함된 열만으로 조회 가능한지도 계획에서 확인합니다.

일반적인 B-tree에서 LIKE '문자%' 같은 접두 검색과 LIKE '%문자%' 포함 검색은 인덱스 활용 조건이 다릅니다. 포함 검색을 빨라지게 하려고 접두 검색으로 바꾸면 결과 의미가 달라집니다. 필요한 검색 방식과 collation을 정한 뒤 적합한 별도 검색 전략을 검토합니다. 조회별 인덱스 설계를 참고하세요.

측정 조건을 기록하고 실습 테이블 정리하기

변경 전후 쿼리, 결과 기본키, 서버 버전·인덱스·행 수·데이터 분포를 함께 기록합니다. 두 번째 실행이 캐시 때문에 빨라진 것인지도 구분하고, 무조건 특정 인덱스를 강제하지 않습니다. 쿼리를 개선한 뒤에는 영향을 받는 다른 정렬·페이지 요구도 확인해야 합니다.

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

DROP TABLE shopcourse.c41_items;
DROP TABLE shopcourse.c41_orders;
DROP TABLE shopcourse.c41_products;
DROP TABLE shopcourse.c41_customers;