MySQL 인덱싱: 2초 검색에서 30ms 결과로

적절한 인덱스는 2초 제품 검색을 30ms로 바꿀 수 있습니다 – 98% 개선. 그러나 대부분의 WordPress 사이트는 기본 인덱스로만 실행되며, 많은 맞춤 PHP 앱은 인덱싱 전략 없이 구축됩니다. 이 가이드는 데이터베이스를 빠르게 만드는 인덱스를 설계하는 방법을 알려줍니다.

인덱싱이 웹 성능에 중요한 이유

사용자가 데이터베이스를 쿼리하는 페이지(제품 목록, 검색 결과, 사용자 프로필)를 방문하면 MySQL은 관련 행을 찾아야 합니다. 인덱스가 없으면 MySQL은 전체 테이블 스캔을 수행합니다 – 일치하는 항목을 찾을 때까지 모든 행을 읽습니다. 100,000행 테이블의 경우 전체 스캔은 200‑500ms가 걸립니다. 1,000명의 동시 사용자가 있으면 지연 시간이 몇 초가 되고 서버 CPU가 최대치에 도달합니다.

인덱스는 책의 색인과 같습니다: 행을 정확히 찾을 수 있는 위치를 MySQL에 알려주어 O(n) 작업을 O(log n)으로 바꿉니다. 결과적으로 쿼리는 초가 아닌 밀리초 단위로 반환됩니다.

MySQL 인덱스 작동 방식 (간단히 설명)

MySQL은 기본적으로 B‑Tree 인덱스를 사용합니다. 열 값을 정렬된 트리 구조에 저장합니다. 인덱스된 열에 WHERE 절을 사용하여 쿼리를 실행하면 MySQL은 트리를 탐색하여 약 log₂(N) 단계로 일치하는 값을 찾습니다. 100만 행의 경우 100만 번 스캔 대신 약 20단계입니다.

중요 규칙: 인덱스는 동등 (=), 범위 (<, >, BETWEEN) 또는 접두사 일치 (LIKE 'term%')로 필터링할 때 도움이 됩니다. LIKE '%term%'(선행 와일드카드) 또는 WHERE DATE(created_at) = '2025-01-01'와 같은 함수 호출에는 도움이 되지 않습니다.

느린 쿼리 식별 – EXPLAIN 사용

인덱스를 추가하기 전에 가장 느린 쿼리를 찾으세요. MySQL 슬로우 쿼리 로그를 활성화합니다:

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 0.5;  -- log queries slower than 0.5 seconds
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

그런 다음 mysqldumpslow -s t /var/log/mysql/slow.log를 실행하여 상위 느린 쿼리를 확인합니다.

다음으로, 의심스러운 쿼리 앞에 EXPLAIN을 추가하여 MySQL이 어떻게 실행하는지 확인합니다. 예:

EXPLAIN SELECT * FROM products WHERE category_id = 5 AND price < 100;

type 열을 확인하세요:

• ALL – 전체 테이블 스캔(최악).
• range 또는 ref – 인덱스 사용(좋음).
• const – 기본 키 조회(최상).

rows – 스캔된 행 수도 확인합니다. 테이블 총계에 가까우면 인덱스를 추가하세요.

실용적인 인덱싱 전략 – 단일 vs 복합 인덱스

단일 열 인덱스

한 열을 자주 필터링할 때 사용합니다.

CREATE INDEX idx_user_id ON orders (user_id);

복합(다중 열) 인덱스

여러 열을 필터링할 때 복합 인덱스는 별도의 인덱스보다 훨씬 효율적입니다. MySQL은 테이블 참조당 하나의 인덱스만 사용할 수 있지만 복합 인덱스는 여러 열을 포괄합니다.

-- Slow: 2 seconds, scans 50,000 rows
SELECT * FROM products WHERE category_id = 5 AND price < 100;

-- Add composite index (order matters!)
CREATE INDEX idx_category_price ON products (category_id, price);

-- Now: 30ms, scans 127 rows

순서 규칙: 가장 선택적인 열을 먼저 배치합니다(가장 많은 행을 필터링하는 열). 일반적으로 category_id는 50,000 → 5,000으로 줄이고, price는 200으로 줄입니다. 반대로(price, category_id) 하면 인덱스는 가격 범위 쿼리에 덜 효율적입니다.

커버링 인덱스(성배)

쿼리에 인덱스 내 열만 필요한 경우 MySQL은 테이블 데이터를 건드리지 않고 인덱스에서 완전히 쿼리에 답할 수 있습니다. 이것은 매우 빠릅니다.

-- Query only needs id and name
SELECT id, name FROM products WHERE category_id = 5;

-- Covering index
CREATE INDEX idx_category_id_name ON products (category_id, name);

-- EXPLAIN will show "Using index" in Extra column

실제 예: 이커머스 제품 필터링 – 전과 후

50,000개 제품이 있는 온라인 스토어에는 카테고리, 브랜드, 가격대별로 필터링하는 검색 페이지가 있었습니다. 원본 쿼리:

SELECT * FROM products 
WHERE category_id = 12 
  AND brand_id IN (3,7,9) 
  AND price BETWEEN 50 AND 200 
ORDER BY price LIMIT 24;

인덱스 없음: 전체 테이블 스캔 – 2.3초, 50,000행 스캔.

분석 후: 복합 인덱스를 추가했습니다:

CREATE INDEX idx_category_brand_price ON products (category_id, brand_id, price);

결과: 동일한 쿼리가 80ms로 실행되었고(96% 개선), 312행만 스캔했습니다. 페이지 로드 시간이 3.2초에서 0.6초로 떨어졌습니다.

WordPress 인덱싱(어쩔 수 없이 유지해야 하는 경우)

WordPress는 일반적인 스키마로 인해 느린 쿼리로 악명 높습니다. wp_postmeta 테이블은 모든 사용자 정의 필드를 키-값 쌍으로 저장하여 수백만 행을 만듭니다. 사용자 정의 인덱스를 추가하여 일반적인 쿼리를 개선할 수 있습니다:

-- If you frequently query postmeta by meta_key and meta_value
ALTER TABLE wp_postmeta ADD INDEX idx_meta_key_value (meta_key, meta_value(100));

그러나 인덱싱이 있더라도 WordPress는 여전히 wp_query 오버헤드로 어려움을 겪습니다. 적절한 스키마와 인덱스로 설계된 맞춤 PHP 애플리케이션은 항상 더 빠릅니다.

인덱스 유지 관리 – 추가 시기와 제거 시기

• 인덱스 추가 WHERE, JOIN, ORDER BY, GROUP BY에 사용되는 열에.
• 인덱스 제거 거의 사용되지 않거나 카디널리티가 매우 낮은 열(예: 두 값만 있는 `status` 열). 각 인덱스는 MySQL이 인덱스도 업데이트해야 하므로 INSERT, UPDATE, DELETE를 느리게 합니다.
• 인덱스 사용 모니터링 SHOW INDEX FROM table; 및 SELECT * FROM sys.schema_unused_indexes;(MySQL 8.0) 사용.

인덱싱 분석 도구

• EXPLAIN – 내장, 항상 여기서 시작하세요.
• MySQL Workbench – 시각적 EXPLAIN 및 성능 대시보드.
• pt‑query‑digest(Percona Toolkit) – 슬로우 쿼리 로그를 분석하고 인덱스를 제안합니다.
• phpMyAdmin – "프로파일링" 기능으로 쿼리 실행 세부 정보 확인.

고객 사례 연구: 부동산 플랫폼

부동산 웹사이트에는 200,000개의 부동산 목록이 있었습니다. 사용자는 도시, 가격대, 침실 수, 부동산 유형으로 검색했습니다. 쿼리가 매번 전체 테이블을 스캔했기 때문에 검색 페이지를 로드하는 데 4~6초가 걸렸습니다.

해결책: 슬로우 로그를 분석하고 EXPLAIN을 사용한 후 복합 인덱스를 추가했습니다:

CREATE INDEX idx_city_price_beds_type ON properties (city_id, price, bedrooms, property_type);

또한 `SELECT *` 대신 필요한 열(id, 제목, 가격, 썸네일)만 선택하여 커버링 인덱스를 사용하도록 쿼리를 변경했습니다.

결과:

• 쿼리 시간: 5.2초 → 90ms.
• 서버 CPU 부하 70% 감소.
• 페이지 로드 시간: 6.5초 → 1.2초(프론트엔드 포함).
• 사용자가 느린 검색을 포기하지 않았기 때문에 사용자 참여도 34% 증가.

일반적인 실수 및 해결 방법

실수 1: 모든 열 인덱싱

문제: 너무 많은 인덱스는 쓰기를 느리게 합니다. 해결: WHERE, JOIN 또는 ORDER BY에 나타나는 열만 인덱싱합니다.

실수 2: 인덱스된 열에 함수 사용

나쁨: WHERE DATE(created_at) = '2025-01-01' – created_at의 인덱스를 무시합니다. 좋음: WHERE created_at BETWEEN '2025-01-01 00:00:00' AND '2025-01-01 23:59:59'.

실수 3: LIKE의 선행 와일드카드

나쁨: WHERE name LIKE '%widget%' – 인덱스를 절대 사용하지 않습니다. 좋음: 부분 일치를 위해 전체 텍스트 검색 인덱스를 사용하세요.

실수 4: 전후에 EXPLAIN 사용하지 않기

인덱스를 추가하기 전에 EXPLAIN을 실행하고, 추가한 후에 다시 실행하여 개선을 확인하세요.

고급: 인덱스 제안 자동화

MySQL 8.0의 경우 성능 스키마를 활성화하고 sys 스키마를 사용하여 누락된 인덱스를 찾을 수 있습니다:

SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_redundant_indexes;

이전 버전의 경우 percona-toolkit의 pt-index-usage가 슬로우 로그를 분석하고 인덱스를 제안합니다.

데이터베이스를 최적화할 준비가 되셨나요?

저는 첫날부터 적절한 데이터베이스 인덱싱으로 맞춤 PHP 애플리케이션을 구축합니다. 느린 WordPress 사이트든 쿼리 병목 현상이 있는 맞춤 앱이든, 슬로우 로그를 분석하고 올바른 인덱스를 추가하여 페이지 로드 시간을 50% 이상 줄일 수 있습니다.

데이터베이스 성능 문제에 대해 이야기합시다. 가장 느린 쿼리에 대한 무료 평가를 제공하겠습니다.

모든 성능 수치는 Hostinger VPS에서 MySQL 8.0을 사용한 실제 클라이언트 감사에서 나온 것입니다. 결과는 테이블 크기와 서버 하드웨어에 따라 다를 수 있습니다.