ComputerScience/인덱스

인덱스 최적화 기법

dev_swan 2023. 11. 24.

✏️ 인덱스 최적화 기법

데이터베이스의 인덱스 최적화는 성능 향상 및 쿼리 응답 시간 단축을 목표로 합니다. 인덱스 최적화 기법은 다양하지만, 주요 기법들을 몇 가지 소개하겠습니다.

📌 적절한 인덱스 선택

예시 : SELECT * FROM orders WHERE customer_id = 123; 쿼리가 자주 실행된다면 customer_id에 인덱스를 생성합니다.

데이터베이스에서 SELECT 쿼리는 데이터를 검색할 때 사용됩니다. 이때, 어떤 조건으로 데이터를 찾을지 정의하는 것이 WHERE 절입니다. 위의 예시에서는 customer_id가 123인 주문 데이터를 찾기 위한 쿼리입니다.

인덱스 없이 데이터를 검색할 때

데이터베이스는 orders 테이블의 첫 번째 레코드부터 마지막 레코드까지 모든 레코드를 검사합니다.
각 레코드의 customer_id 값을 확인하며, 값이 123인 레코드들만 선택하여 결과로 반환합니다.

이런 검색 방식을 풀 테이블 스캔이라고 합니다. 데이터가 많을 경우, 풀 테이블 스캔은 매우 비효율적입니다.

customer_id에 인덱스가 있을 때

인덱스는 일종의 목차와 비슷하게 구성되어 있습니다. 책의 목차를 보면 특정 주제나 키워드를 찾아 바로 해당 페이지로 이동할 수 있습니다. 인덱스도 마찬가지로, customer_id 값이 123인 레코드를 빠르게 찾아주는 구조로 되어 있습니다.
데이터베이스는 인덱스를 사용하여 customer_id 값이 123인 레코드들의 위치를 바로 알아내고, 해당 레코드들만 읽어 결과로 반환합니다.

이 방식은 모든 레코드를 검사하지 않기 때문에 훨씬 빠르고 효율적입니다.

📌 복합 인덱스 사용

예시 : SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01'; 쿼리의 경우 (customer_id, order_date) 복합 인덱스를 고려합니다.

SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01'; 이 쿼리는 orders 테이블에서 customer_id가 123이면서 order_date가 '2023-01-01'보다 큰 모든 레코드를 선택합니다.

단일 인덱스만 있는 경우

customer_id에만 인덱스가 있다면, 이 인덱스를 사용하여 customer_id가 123인 모든 레코드를 먼저 찾습니다.
그 후, 찾아낸 레코드 중에서 order_date가 '2023-01-01'보다 큰 레코드를 필터링합니다.
이 경우, order_date에 대한 필터링은 인덱스 없이 수행되기 때문에 상대적으로 느릴 수 있습니다.

복합 인덱스 (customer_id, order_date)가 있는 경우

인덱스는 (customer_id, order_date) 순으로 정렬되어 있습니다. 따라서, customer_id가 123이면서 order_date가 '2023-01-01'보다 큰 레코드를 빠르게 찾을 수 있습니다.
복합 인덱스는 두 조건을 동시에 만족하는 레코드를 효율적으로 검색하는 데 도움을 줍니다.

결론적으로, 복합 인덱스는 여러 조건을 동시에 처리할 때 효율적인 검색 성능을 제공합니다. 따라서 WHERE 절에 여러 조건이 자주 사용되는 경우, 해당 조건에 맞는 복합 인덱스를 고려하여 성능을 최적화할 수 있습니다.

📌 인덱스 크기 최적화

예 : 긴 URL을 저장하는 website_url 컬럼에 대해 인덱스를 생성하면, 인덱스의 크기가 매우 커질 수 있습니다. 인덱스가 너무 큰 경우, 그 성능이 떨어지고, 데이터베이스의 저장 공간도 불필요하게 많이 차지하게 됩니다.

해결 방안

모든 URL의 전체 내용을 인덱싱하는 대신, URL의 처음 50 문자(또는 다른 적당한 길이)만을 인덱싱하는 것을 고려합니다. 대부분의 경우, URL의 처음 몇 문자만으로도 해당 URL을 고유하게 식별할 수 있기 때문에, 이 방법이 효과적일 수 있습니다.

📌 분할 인덱스 (Partitioning)

예시 : 매년 거래 데이터가 저장되는 transactions 테이블에서 연도별로 파티션을 생성합니다.

💡 파티셔닝

파티셔닝(Partitioning)이란 데이터베이스의 파티셔닝은 큰 테이블을 논리적인 조각들, 즉 파티션으로 나누는 것을 말합니다.

각 파티션은 테이블의 일부 데이터를 포함하며, 각각 독립적으로 저장될 수 있습니다. 파티셔닝의 주요 목적은 쿼리 성능을 향상시키기 위함입니다.

문제 상황 : "transactions"라는 테이블에는 매년 수백만 건의 거래 데이터가 저장됩니다. 이런 큰 데이터를 한 번에 검색하면 성능 문제가 발생할 수 있습니다.
파티셔닝의 도입 : 이 문제를 해결하기 위해, 매년 발생하는 거래 데이터를 연도별로 분리하여 저장하는 방법을 고려합니다. 예를 들어, 2021년의 거래 데이터는 2021 파티션에, 2022년의 거래 데이터는 2022 파티션에 저장됩니다.
실제 동작 : 사용자가 "2021년의 거래 데이터를 조회해주세요"라는 쿼리를 요청하면, 데이터베이스는 전체 "transactions" 테이블을 스캔하는 대신 "2021" 파티션만을 탐색합니다. 이로 인해 훨씬 빠른 응답 시간을 기대할 수 있습니다.

파티셔닝의 장점

특정 연도의 거래 데이터만 검색할 때, 해당 연도의 파티션만 탐색하면 되므로 검색 시간이 크게 줄어듭니다.
오래된 데이터를 삭제할 때, 전체 테이블을 건드리지 않고 해당 연도의 파티션만 삭제하면 되므로 데이터 관리가 용이합니다.

📌 인덱스 정비

예시 : MySQL에서 OPTIMIZE TABLE table_name; 명령어를 사용하여 인덱스를 재구성하고 파편화를 줄입니다.

💡 파편화

데이터베이스에서 데이터를 저장하거나 삭제할 때, 테이블의 저장 공간이 비효율적으로 사용될 수 있습니다.

특히 행을 삭제하거나 업데이트할 때, 사용하지 않는 공간(파편화)이 생길 수 있습니다. 이로 인해 디스크의 공간 사용이 비효율적이게 되고, 데이터 접근 속도도 느려질 수 있습니다.

💡 OPTIMIZE TABLE

MySQL에서는 OPTIMIZE TABLE이라는 명령어를 제공하여 테이블의 파편화를 해결할 수 있습니다. 이 명령어는 테이블과 관련된 공간을 재조정하고, 불필요한 공간을 회수합니다. 또한, 테이블의 인덱스도 재구성하여 검색 성능을 향상시킵니다.

📌 쿼리 최적화

예시 : SELECT * FROM users WHERE YEAR(birthdate) = 1990; 쿼리는 인덱스를 사용하지 못합니다. 대신 birthdate BETWEEN '1990-01-01' AND '1990-12-31';로 변경하여 인덱스를 활용할 수 있도록 합니다.

📌 Covering Index 사용

예시 : SELECT customer_id, order_date FROM orders WHERE customer_id = 123; 쿼리는 (customer_id, order_date) 복합 인덱스를 사용하면 테이블에 접근하지 않고도 모든 데이터를 가져올 수 있습니다.

SELECT customer_id, order_date FROM orders WHERE customer_id = 123; 이 쿼리는 orders 테이블에서 customer_id가 123인 주문의 customer_id와 order_date를 요청하고 있습니다.

이제 (customer_id, order_date) 복합 인덱스를 가정해 봅시다. 이 인덱스는 customer_id로 먼저 정렬되고, 그다음 order_date로 정렬됩니다.

이 복합 인덱스가 있다면, 데이터베이스는 customer_id가 123인 모든 데이터를 찾기 위해 해당 인덱스를 사용할 수 있습니다. 더 나아가, 이 인덱스 안에는 customer_id와 order_date 둘 다의 정보가 이미 포함되어 있기 때문에, 데이터베이스는 실제 테이블(orders 테이블)에 추가적인 접근 없이도 결과를 반환할 수 있습니다.

Covering Index는 특정 쿼리의 성능을 크게 향상시킬 수 있는 효과적인 방법입니다. 쿼리의 요구사항과 사용되는 컬럼을 잘 분석하여 적절한 복합 인덱스를 설계하는 것이 중요합니다.

💡 Covering Index

복합 인덱스는 두 개 이상의 컬럼에 걸친 인덱스를 말합니다. 그런데 때로는 복합 인덱스가 쿼리에서 요청하는 모든 데이터를 포함할 수 있게 설계됩니다.

이렇게 쿼리의 결과를 반환하기 위해 복합 인덱스만으로 모든 필요한 데이터를 제공할 수 있는 인덱스를 Covering Index라고 합니다.

📌 인덱스의 데이터 타입 일치

예시 : age 컬럼이 INTEGER 타입인데, 쿼리에서 WHERE age = '25'처럼 문자열로 비교하면 인덱스 사용이 비효율적일 수 있습니다. 대신 WHERE age = 25로 타입을 일치시켜야 합니다.

'ComputerScience > 인덱스' 카테고리의 다른 글

인덱스의 필요성 (0)	2023.11.22