작년에는 밀리초가 걸렸던 쿼리가 오늘날에는 테이블이 늘어났기 때문에 몇 분이 걸릴 수 있습니다. SQL 쿼리 최적화는 영리한 트릭보다는 반복 가능한 프로세스에 관한 것입니다. 즉, 데이터베이스가 수행하는 작업을 파악하고, 낭비되는 작업을 제거하고, 도움이 되었는지 확인하는 것입니다.

우리는 SQLite 3.50에서 생성된 백만 개의 행에 대해 아래의 각 기술을 테스트했습니다. 타이밍은 메모리 내 데이터베이스를 사용하여 하나의 노트북에서 여러 번 실행하는 것 중 가장 좋습니다. 이는 시스템과 일치하지 않으며 PostgreSQL, MySQL 및 SQL Server에는 각각 고유한 최적화 프로그램과 계획 형식이 있습니다. 각 개선의 규모와 그 뒤에 숨은 원칙에 집중하세요.

결과 한눈에 보기

테스트 설정

CREATE TABLE customers (
  id INTEGER PRIMARY KEY, name TEXT, city TEXT
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY, customer_id INT, status TEXT,
  created TEXT, total INT, note TEXT
);
-- 50,000 customers and 1,000,000 orders with random values

간단한 최적화 프로세스

1. 중요한 쿼리를 찾으세요. 느린 쿼리 로그나 모니터링을 사용하세요. 1분에 수천 번 실행되는 200ms 쿼리는 10초 분량의 월별 보고서보다 더 중요할 수 있습니다.
2. 계획을 읽어보세요. 데이터베이스가 실제로 무엇을 하고 있는지 확인하세요.
3. 한 가지를 변경하세요. 색인을 추가하거나 쿼리의 일부를 다시 작성하세요.
4. 현실적인 데이터를 바탕으로 다시 측정합니다. 100개의 테스트 행에 대한 계획은 백만 개에 대해 거의 알려주지 않습니다.
5. 시간을 두고 지켜보세요. 데이터가 증가하고 통계가 변화함에 따라 계획이 변경될 수 있습니다.

실행 계획 읽기

실행 계획은 사용하는 인덱스와 테이블 조인 순서를 포함하여 쿼리를 실행하기 위한 데이터베이스의 단계별 전략입니다. 이를 보려면 쿼리에 접두사를 붙이세요.

PostgreSQL의 EXPLAIN ANALYZE은 실제로 쿼리를 실행하므로 UPDATE 또는 DELETE과 함께 사용할 때는 주의하세요. 먼저 찾아야 할 사항은 다음과 같습니다.

전체 검사가 자동으로 나쁜 것은 아닙니다. 쿼리에 작은 테이블이 대부분 필요한 경우 검색이 가장 빠른 선택이 될 수 있습니다. PostgreSQL의 EXPLAIN ANALYZE에서는 추정된 행과 실제 행도 비교합니다. 큰 차이는 종종 테이블 통계가 오래되었음을 의미합니다.

올바른 색인 추가

이 쿼리는 한 고객의 주문을 찾습니다.

SELECT id, total FROM orders WHERE customer_id = 123;

인덱스를 추가하기 전 계획은 전체 스캔이었습니다.

SCAN orders
-- 20 rows in 73 ms

그런 다음 필터 열에 인덱스를 추가했습니다.

CREATE INDEX idx_orders_customer ON orders(customer_id);

SEARCH orders USING INDEX idx_orders_customer (customer_id=?)
-- 20 rows in 0.01 ms

동일한 20개의 행이 수천 배 더 빨리 반환되었습니다. 데이터베이스가 더 이상 행을 찾기 위해 백만 개의 행을 읽지 않기 때문입니다. 이 기사의 다른 어떤 기술도 우리에게 더 큰 승리를 가져다주지 못했습니다. 좋은 첫 번째 후보는 WHERE, JOIN 및 ORDER BY에 사용된 열이며, 특히 결과를 몇 개의 행으로 좁힐 때 더욱 그렇습니다. SQL 인덱스 가이드에서는 작동 방식을 설명합니다.

함수에서 인덱싱된 열을 래핑하지 마세요.

다음은 created에 대한 색인을 사용하여 3월 주문을 찾는 두 가지 방법입니다.

-- Function on the column
SELECT id FROM orders WHERE substr(created, 1, 7) = '2026-03';
-- SCAN orders USING COVERING INDEX idx_orders_created
-- 84,961 rows in 96 ms

-- Plain range
SELECT id FROM orders
WHERE created >= '2026-03-01' AND created < '2026-04-01';
-- SEARCH orders USING COVERING INDEX idx_orders_created (created>? AND created<?)
-- 84,961 rows in 33 ms

둘 다 동일한 행을 반환하지만 첫 번째는 약 3배 정도 시간이 걸립니다. 계획은 그 이유를 보여줍니다. 열에 함수가 있으면 데이터베이스가 정렬된 인덱스의 위치로 이동할 수 없으므로 모든 항목을 통해 SCAN해야 합니다. 범위 버전은 SEARCH 올바른 위치로 바로 이동할 수 있습니다. 이런 방식으로 인덱스를 사용할 수 있는 조건을 sargable이라고 합니다.

다음과 같은 일반적인 패턴에서도 동일한 문제가 나타납니다.

우리는 마지막 것도 확인했습니다. 주요 와일드카드가 생성됨SCAN customers, 첫 글자부터 정렬된 색인은 끝 부분의 일치에 도움이 될 수 없습니다.

복합 인덱스

쿼리가 두 개의 열을 필터링하는 경우 단일 열 인덱스는 작업의 일부만 수행합니다.

SELECT id FROM orders
WHERE status = 'paid'
  AND created >= '2026-03-01' AND created < '2026-03-08';

-- Using only the created index: 6,198 rows in 19 ms

CREATE INDEX idx_status_created ON orders(status, created);

-- SEARCH orders USING COVERING INDEX idx_status_created
--   (status=? AND created>? AND created<?)
-- 6,198 rows in 2.2 ms

열 순서는 매우 중요합니다. (status, created)의 색인은 성, 이름 순으로 정렬된 전화번호부처럼 작동합니다. 성만 검색할 수 있지만 이름만 검색할 수는 없습니다. status에 대한 쿼리만으로도 이 인덱스를 사용할 수 있습니다. created만으로는 필터링하는 것만으로는 일반적으로 효율적으로 사용할 수 없습니다. 경험상 동일성이 테스트된 열을 먼저 배치하고 그 뒤에 범위 열을 배치합니다.

해당 계획에는 포함 인덱스도 명시되어 있습니다. 즉, 인덱스에 쿼리에 필요한 모든 것이 포함되어 있으므로 테이블 자체를 읽을 필요가 없습니다. 필요한 열만 선택하면 이런 일이 발생할 가능성이 높아지는데, 이는 SELECT *를 피해야 하는 또 다른 이유입니다.

더 빠른 페이지 매김

딥 페이지에 OFFSET을 사용하면 숨겨진 비용이 발생합니다. 데이터베이스가 건너뛴 모든 행을 계속 지나쳐야 하기 때문입니다.

-- Page deep into the results
SELECT id FROM orders ORDER BY id LIMIT 20 OFFSET 500000;
-- 8.1 ms

-- Keyset pagination: continue from the last id seen
SELECT id FROM orders WHERE id > 500000 ORDER BY id LIMIT 20;
-- 0.01 ms

키 세트 페이지 매김은 아무리 깊이 들어가도 빠르게 유지됩니다. 또한 새 데이터가 추가될 때 페이지 간에 행이 이동하는 것을 방지합니다. 단점은 사용자가 실제로 필요한 경우가 거의 없는 5,000페이지로 바로 이동할 수 없다는 것입니다. 행이 넓거나 건너뛸 행이 더 많은 테이블에서는 테스트에서보다 간격이 훨씬 더 커질 수 있습니다.

간결한 쿼리 작성

• 필요한 열만 선택하세요. SELECT * 더 많은 데이터를 보내므로 인덱스가 쿼리를 덮지 못하게 할 수 있습니다.
• 초기 필터링. 그룹화하기 전에 행을 제거하려면 WHERE를 사용하고 집계 조건에만 HAVING을 사용합니다. SQL GROUP BY 및 HAVING을 참조하세요.
• 몇 개의 행만 필요한 경우에는 LIMIT를 사용하세요.
• 중복 항목을 제거할 필요가 없는 경우 UNION ALL을 선호합니다. UNION 대 UNION ALL 테스트는 이것이 무엇을 절약하는지 보여줍니다.
• 불필요한 DISTINCT를 피하세요. 중복을 생성하는 조인을 숨기는 경우가 많습니다.
• 존재 확인을 위해 EXISTS를 사용하세요. SQL 하위 쿼리에 대한 가이드에서 설명하는 것처럼 첫 번째 일치에서 중지될 수 있습니다.
• N+1 쿼리를 살펴보세요. 일반적으로 애플리케이션 코드로 인해 발생하는 루프의 행당 하나의 쿼리가 실제 문제인 경우가 많습니다. 단일 조인 또는 일괄 쿼리로 수백 번의 왕복을 대체할 수 있습니다.

조인

조인하는 열을 색인화합니다. 우리는 한 도시의 고객을 대상으로 총 지출 보고서를 실행했으며 그 계획은 귀하가 원하는 것과 정확히 일치했습니다.

SELECT c.name, SUM(o.total)
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.city = 'Goa'
GROUP BY c.id;

-- SCAN c
-- SEARCH o USING COVERING INDEX idx_cover (customer_id=?)
-- 9,900 rows in 34 ms

고객을 스캔한 다음 색인을 사용하여 각 고객의 주문을 찾습니다. orders.customer_id에 대한 인덱스가 없으면 데이터베이스는 각 고객에 대해 훨씬 더 많은 작업을 수행해야 합니다. 외래 키에 대한 인덱스는 수행할 수 있는 가장 일반적이고 중요한 추가 기능 중 하나입니다. 기본 사항에 대한 이해를 다시 높이려면 SQL 조인 설명을 참조하세요.

인덱스 비용이 발생하는 경우

인덱스는 무료가 아니므로 모든 열에 하나씩 추가하지 마세요.

• 쓰기 속도가 느려집니다. 모든 삽입, 업데이트 및 삭제는 각 인덱스도 업데이트해야 합니다.
• 더 많은 저장 공간과 메모리. 큰 테이블에서는 인덱스가 커질 수 있습니다.
• 비선택적 열에는 거의 이점이 없습니다. 대부분의 행이 일치하는 경우 true 또는 false 열의 인덱스는 도움이 되지 않을 수 있습니다.
• 중복성. (a, b)에 이미 인덱스가 있는 경우 (a)의 인덱스는 불필요한 경우가 많습니다.

주기적으로 사용량 통계를 확인하고, 사용하지 않는 인덱스는 제거하세요. 그리고 옵티마이저는 좋은 계획을 선택하기 위해 통계에 의존하기 때문에 ANALYZE 또는 데이터베이스와 동등한 것을 사용하여 통계를 최신 상태로 유지하십시오. 때때로 느린 부분은 쿼리보다는 경합입니다. 장기 실행 트랜잭션 잠금 보유로 인해 빠른 쿼리가 느리게 보일 수 있습니다.

일반적인 실수

• 측정하지 않고 최적화합니다. 확신에 찬 많은 추측이 잘못된 것으로 판명됩니다.
• 작은 데이터로 테스트합니다. 테이블이 늘어남에 따라 계획과 시기가 크게 변경됩니다.
• 다른 데이터베이스의 팁을 신뢰합니다. 한 엔진에 대한 조언이 다른 엔진에는 적용되지 않을 수 있습니다.
• 무작위로 인덱스를 추가합니다. 인덱스를 추가할 때마다 쓰기 속도가 느려집니다.
• 첫 번째 실행 효과 무시. 캐시는 두 번째 실행 속도를 더 빠르게 하므로 여러 번 실행하세요.

마무리

대부분의 느린 쿼리에는 인덱스 누락, 열의 함수 또는 너무 많은 데이터를 가져오는 등의 일반적인 원인이 있습니다. 이번 주에 가장 느린 실제 쿼리에 대해 EXPLAIN을 실행하고 큰 테이블에서 전체 스캔을 찾으십시오. 한 가지를 수정하고 다시 측정하고 변경된 사항을 기록해 두세요.

자주 묻는 질문

계속 학습하세요