2024. 4. 21. 02:49ㆍ데이터베이스 & SQL/데이터베이스
잡담
처음 개발을 공부했을 때 ORM부터 배워서 그런지 개인적으로 통계성 쿼리 종류가 약한 게 아니라 그냥 못한다는 느낌이 들었다. 특히, 학부 때 DB 개론 수업이 SQL보다 DB 내의 동작원리가 보통 어려워서 평균이 낮다보니 시험 때 비중이 되게 컸었다. 그러다보니 자연스럽게 그것만 공부하게 돼서 그런 것도 있는 것 같다. 심지어.... 그 내용은 오래돼서 이미 머리에서 없어졌다...
아무튼, 책과 개인 블로그를 다니며 공부한 내용을 간단하게 요약해보면, SQL 자체를 잘 짜서 더 나은 성능을 가지는 경로를 쿼리 옵티마이저가 선택하게 하는 방법과 SQL이 아닌 버퍼풀, 메모리, 백그라운드 쓰레드, 특정 전략을 잘 선택하는 방법이 있다.
후자보다는 전자에 조금 더 집중을 해서 계속 글을 쓸 예정이며, SQL 최적화를 위해서 알아야 하는 것은 Index에 관해 아는 것, 실행 전략에 관해 아는 것, 윈도우 함수에 관한 이해가 제일 기초라고 생각이 된다. 그래서 이 중에서도 Index에 관해서 이야기를 해보고자 한다.
1. 인덱스와 인덱스를 사용하는 이유
인덱스에는 클러스터링 인덱스와 논-클러스터링 인덱스가 있다. 이 두 인덱스를 한마디로 정의를 하자면, "정렬을 통한 바이너리 서치", "DB에서의 해시테이블"이라고 생각을 한다. 장점을 가져다 주는 이유, 단점이 일어나는 원인, 잘못 썼을 때의 생기는 문제점이 거의 비슷하다고 생각한다.
인덱스의 종류에는 2가지가 있다.
○ 클러스터링 인덱스
클러스터링 인덱스는 실제 데이터와 동기화가 된 인덱스이다. 클러스터링 인덱스로 선택된 컬럼 혹은 컬럼들에 따라 데이터는 사전순으로 정렬이 돼서 있으며, 실제 데이터 테이블과 저장과 직접 연관이 되어있다. 이를 잘 확인할 수 있는 방법은 다음과 같이 SQL을 짜서 확인해보면 된다.
MySQL을 기준으로 코드를 작성했으며, 아래의 코드를 작동시켜보면 PK로 선정된 shop_id를 삽입할 때에 순서를 마음대로 했지만 맨아래의 SELECT 문에서 조회해보면 PK를 기준으로 정렬이 된 데이터가 조회됨을 확인할 수 있다.
CREATE TABLE SHOP(
shop_id CHAR(8) PRIMARY KEY,
phno CHAR(50),
location CHAR(50)
);
INSERT INTO SHOP VALUES(7, "12345678", "서울시 강서구");
INSERT INTO SHOP VALUES(1, "12345678", "서울시 강남구");
INSERT INTO SHOP VALUES(5, "12345678", "서울시 강동구");
INSERT INTO SHOP VALUES(6, "12345678", "서울시 강북구");
INSERT INTO SHOP VALUES(2, "12345678", "서울시 동대문구");
INSERT INTO SHOP VALUES(4, "12345678", "서울시 서대문구");
INSERT INTO SHOP VALUES(3, "12345678", "서울시 도봉구");
SELECT * FROM SHOP;
클러스터링 인덱스는 정렬을 통해서 바이너리 서치를 하듯이 데이터를 검색한다. 실제로는 여러 B-Tree 종류를 이용해서 작성하지만 그건 조금만 있다가 알아보자. 특정한 PK에서의 값을 통해서 해당 행만을 조회해오는 상황을 생각해보자. 정렬된 자료에서 바이너리 서치를 사용하면 업다운 게임처럼 값을 찾을 수 있다. 만약에 100개의 데이터 행이 있고 최소값이 1, 최대값이 100이면 이 내부의 값은 어떤 값이든 상관없이 9번 이내로 검색이 가능하다. 좁은 연속된 범위 혹은 특정한 ID 값을 읽기를 기준으로 매우 빠른 조회 성능을 보인다.
○ 논-클러스터링 인덱스
논-클러스터링 인덱스는 실제 데이터와 동기화가 아니라 별도의 저장공간을 따로 두어 책 목차와 같은 색인을 만드는 것이다. 따라서, 실제 데이터와는 별개의 데이터를 만든다.
그래서, 논-클러스터링 인덱스를 만든다고 한들 클러스터링 인덱스처럼 데이터의 실제 저장에는 영향을 주지 않고, 순서에 대한 정보를 저장한다. 이 외에는 클러스터링 인덱스와 동일한 특징을 보인다.
○ 공통점과 차이점
둘의 공통점은 전체 테이블에서 특정한 값을 빨리 찾기 위해서 실제 저장 데이터를 정렬하거나 별도의 색인 공간을 만들어서 정렬한다. 따라서, 모든 데이터를 조회하는 경우보다는 그 중 매우 소량의 데이터를 조회해올 때 성능적 이점을 준다.
둘의 차이점은, 클러스터링 인덱스는 실제 데이터 저장과 관련이 있기 때문에 오직 1개만 선택이 가능하며, 1개는 PK를 기준으로 선택이 된다. UNIQUE + NOT NULL로 거는 방법도 있는데, PK가 있으면 PK가 우선순위이다. 또한 논-클러스터링 인덱스는 실제 데이터의 저장과는 연관이 없는 별도의 데이터 공간을 만들어 저장한 색인이기에 테이블에 여러 개를 설정할 수 있다. 이를 만드는 방법은 특정한 컬럼 혹은 컬럼들에 대해 UNIQUE 조건을 걸어주거나 CREATE INDEX 명령어로 생성해주는 방법이 있다.
2. 인덱스를 구현하는데 B-Tree류를 사용하는 이유
알고리즘을 풀 때에 특정한 데이터를 O(1)로 조회하는 방법은 Key값을 해싱해서 버킷에 담아 해시 테이블을 이용하는 방법이 있다. 이를 이용해서 해시 인덱스를 생성할 수 있는데, DB에서 조회를 할 때에는 보통 개별 데이터를 조회하는 것이 아닌, 범위로 선택을 해서 조회하는 경우가 많다. 이런 범위 조회의 경우에는 해시 인덱스에서는 빠른 검색을 위한 최적화 혜택을 보기 어렵기 때문에 다른 방법을 사용한다.
이런 문제 해결을 위해서 B-Tree라는 자료구조를 선택해서 인덱스를 구현한다. 특히, 이를 개선한 B+Tree라는 자료 구조를 사용하는데, B-Tree의 특징은 모든 리프까지의 거리에 균형이 잡혀있고, 리프 노드끼리 링크드 리스트로 저장하였다는 점이다. 여기까지만 B-Tree라고 하고 대부분의 DBMS는 B+Tree 구조를 사용하니 B+Tree라고 이야기를 하도록 하겠다.
우선 균형이 잡혀있다는 점에 대해서 생각을 해보자. 아래처럼 List 형태로 데이터가 있고 Binary Search로 데이터를 조회한다고 생각해보자. 시작점과 끝점을 정하고 모든 개별 데이터를 하나씩 조회하는 생각을 해보자. 시작점을 1, 끝점을 9로 선택한 뒤 5를 찾는 경우와 9를 찾는 경우를 비교해보자. 해보면 선택하려는 값에 따라, 항상 같은 횟수의 연산으로 조회하지 못하는 문제가 발생한다.
하지만, 아래처럼 B+Tree 구조를 이용해서 개별 데이터를 검색해보면 균형이 잡혀있기 때문에 리프노드까지 도달하는데 어떤 리프노드를 선택하건 동일하다.
리프노드까지 도달하는데 어떤 리프노드를 선택하건 동일하다는 것은 검색 작업에 있어서의 일정한 검색 시간을 보장한다. 이는 이후에 실행 계획과 쿼리 옵티마이저와 관련된 내용에서 이야기할 실행 비용 예측 계산을 할 때에도 이점을 가져다 준다. DB 내에서도 해싱이나 부가적인 계산을 하는 경우, DB 내에 있는 메모리를 사용한다. B+Tree를 통한 검색은 부가적인 계산을 줄여, 메모리 사용을 줄인다. DB에서는 메모리와 디스크 I/O 사이의 시간 차이가 굉장히 크다. 메모리는 굉장히 귀중한 자원인데, 이를 아껴 디스크 I/O가 아닌 메모리 영역에서 모든 처리가 일어나게 돕는다.
그 다음으로 링크드 리스트로 되어있다는 점에 대해서 생각을 해보자. 만약에 데이터를 조회해올 때마다 매번 B+Tree 루트부터 데이터를 검색한다고 하면 여러 데이터를 조회해올 때 매번 leaf 노드의 레벨 x 데이터 개수만큼의 연산이 필요하다. 이는 깊이가 깊어질수록, 한번에 조회해올 데이터가 클수록 오히려 순차적인 조회를 해오는 것보다 느릴 수 있다. 따라서, 리프 노드끼리는 서로 링크드리스트로 한 방향으로 이동할 수 있게 만들어주면, 초기 시작점만 찾은 이후에는 연속적으로 이동하며 탐색 수 있다.
아래는 이해를 돕기 위한 이미지이다. 정리를 해보자면, 인덱스라는 데이터 색인을 통해 정렬된 순서에 관한 정보를 알고 있기에 수직적으로 탐색할 때에는 찾고자하는 내용의 가장 낮은 값 혹은 높은 값을 찾는다. 그리고 링크드리스트로 된 구조로 인해 이를 순차적으로 범위가 끝날 때까지 수평적으로 탐색한다. 이 탐색 순서에 따른 비용차이도 있는데, 이 내용에 관해서는 따로 언급하지 않겠다. 아무튼, 다른 자료형이 아닌 B+Tree 구조가 데이터를 찾고 읽는데에 채택된 이유이다.
이해의 편리함을 위해 번호로만 작성을 해놨는데, 그럼 클러스터링 인덱스와 논-클러스터링의 인덱스는 B+Tree 상에서 어떻게 저장 방식에 있어서 차이가 있는지가 궁금할 것이다.
결과만 말하자면 클러스터링 인덱스는 실제 데이터를 가리키고 논-클러스터링 인덱스에서는 클러스터링 인덱스 상에서의 페이지 주소 값을 가리킨다.
아래의 이미지는 클러스터링 인덱스의 구성으로 실제 데이터가 저장된 페이지 주소에 대해 주소값을 가지고 연결이 되어있는 것을 알 수 있다.
하지만 논-클러스터링 인덱스는 실제 데이터의 정렬과 관련이 없다. 그래서 아래와 같이 실제 데이터 페이지의 주소를 담는 방식으로 저장을 한다. 마치 목차에서 특정 내용은 몇 페이지에 있어라는 내용처럼 말이다. 이번에는 주소에 인덱스를 걸었다고 가정하고 논-클러스터링 인덱스와 B+Tree구조에 관해서 이야기를 하겠다.
그런데 위의 방식에는 문제가 있다. 해당 데이터가 있는 페이지의 주소를 저장하는 방법은 이후 데이터가 추가 혹은 삭제가 되었을 때 다른 페이지로 이동하는 경우, 실제 메모리 주소를 저장한 논-클러스터링 인덱스 구조는 변경에 취약해질 수 있다.
따라서, 아래처럼 실제로는 주소 값을 저장하지 않고 클러스터링 인덱스 상의 구분 값을 저장한다. 그러면 논-클러스터링 인덱스에서 클러스터링 PK 값을 찾아 클러스터링 인덱스 트리 상에서 조회를 하는 방식으로 조회를 할 수 있다. 이로 인해, 조회는 복잡해졌지만 변경에 있어서 인덱스용 B+Tree를 수정하는 비용을 줄일 수 있게 되었다.
3. 인덱스를 만들 때의 생각해볼 점
인덱스가 검색에 있어 효율을 높여주고 좋은 것 같지만 항상 그렇지만은 않다. 검색하려는 조건, 테이블의 상태, 테이블 내부의 데이터의 특성을 잘 고려해야 한다. 특히나 무지성으로 인덱스를 만드는 경우에는 조회 성능은 개선되지 않고 오히려 삭제, 변경, 삽입 시간만 쓸모없이 늘어나는 상황이 될 수도 있다. 아래에서 인덱스를 구성하기 전 고려해보아야 할 것들에 대해서 한번 이야기해보자.
[ 카디널리티와 선택률 ]
인덱스는 테이블 내의 특정 필드 집합에 대해서 생성이 된다. 인덱스를 고려하기 전에 생각할 점은 필드의 카디널리티와 선택률이다. 카디널리틴 값의 균형을 나타내는 값으로 유일하게 식별되는 값의 개수라고 생각하면 된다. 만약에 데이터가 1,1,1,1,1,1이 있고 1,2,3,4,5,6이 있다면 카디널리티가 높은 쪽은 후자이다. 반대로 선택률은 앞의 예시에서 1을 선택할 확률을 이야기할 때, 전자는 100%, 후자는 17%로 후자가 낮다.
특히나, 동일한 값이 많다는 것은 비효율적인 방법으로 데이터를 찾아야함을 의미한다. 앞의 1,1,1,1,1,1에서 다른 필드값은 다른 경우를 생각해보자. 1이라는 값을 가지고 인덱스를 통해 스캔하게 되면 모든 스캔을 해야하는데, 기존 풀테이블 스캔처럼 일렬로 선택하는 것은 주소의 연속성으로라도 선택이 가능하다. 하지만 인덱스로 스캔을 하는 경우, 비효율적으로 연속된 값을 마치 비연속적인 값을 선택하는 것처럼 스캔하게 된다.
따라서, 카디널리티는 높은, 선택률은 낮은 필드에 인덱스를 걸어주고 이를 통해 값을 조회해야지 인덱스를 효율적으로 사용할 수 있다.애매한 것은 차라리 풀테이블 스캔이 빠를 수도 있다.
따라서, 무조건 인덱스를 탈 수 있다고 테이블 설계만 보고 인덱스를 컬럼에 거는 것이 아닌, 현재 본인이 운영하는 서비스의 특성과 내부 데이터 분석을 꾸준히 모니터링해보고 결정을 해야 한다. 왜냐하면 아래와 같은 경우가 있을 수 있기 때문이다.
가게와 리뷰테이블이 있다고 생각을 해보자. 이 때, 특정 1개의 가게가 엄청난 인기가 있어. 리뷰 테이블의 절반을 차지하고 나머지 100개의 가게가 같은 비율로 테이블을 차지한다고 가정을 해보자. 이 때는 쿼리 옵티마이저가 내부 최적화를 통해 방식을 선택할텐데, 인기 가게는 풀테이블 스캔을 하고 후잔 인덱스 스캔을 해주면 좋겠지만, 전자에서 인덱스 스캔을 선택하는 사고가 일어날 수 있다. 따라서 이렇게 특정 사용자가 DB 내에서 많은 비중을 차지한다면 인덱스를 쓸 때 다른 방법이 없을지 한번 고려해보아야 한다.
[ 여러 컬럼에 적용된 인덱스의 순서 ]
추가적으로 현재까지는 인덱스를 만들 때, 1개의 필드로 인덱스를 만들었다. 인덱스는 한 개의 필드만으로가 아닌 여러 개의 필드로 작성할 수 있다. 그런 경우, 인덱스를 만들 때에 그럼 필드의 순서가 중요한지를 묻는다면, 중요할 수 있다. 하지만 탐색하고 선택하는 레코드의 개수는 순서에 상관없이 동일하다.
이 말의 의미는 이전 가게 테이블 예시에서 서울시 서대문구에 있는 레코드는 50개, 전화번호가 12345678로 된 레코드는 5개. 서울시 서대문구이면서 12345678로 된 레코드는 2개가 있다고 해보자. 이때 (주소, 전화번호)로 인덱스를 만들거나 (전화번호, 주소)로 인덱스를 만들건 상관없이 12345678이면서 서울시 서대문구인 레코드는 총 2번의 검사로 끝난다. 왜냐하면 두 개의 조합으로 정렬이 되기 때문이다.
주소 별로 정렬되고 전화번호 별로 정렬돼서 주소에서 50개의 레코드를 추출하고 거기서 2개의 레코드를 검사하는 게 아니라는 말이다. 인덱스 쌍으로 정렬된 내용을 기준으로 가장 앞편 혹은 뒷편 레코드를 찾아 순차적으로 검사한다는 원칙은 인덱스 컬럼이 한 개이건 여러 개이건 동일하게 작동함을 기억하자.
대신, 쿼리가 여러 개일 때, 2개 이상의 컬럼을 조합해 인덱스를 만들어놓은 경우, 그 중 한 컬럼만 사용하는 경우엔 영향이 간다.
4. 인덱스를 만든 이후 쿼리를 작성할 때에 고민해보면 좋을 점
앞에서는 인덱스를 만들 때 고려해볼 점에 관해 이야기를 해보았다면, 이후에는 만들어진 인덱스를 활용해 쿼리를 작성할 때의 주의점에 관해서 이야기를 해보고자 한다.
[ 명확한 시작점과 종료 지점을 모르는 경우 ]
인덱스는 꼭 SELECT문에만 사용되는 것이 아니라, DELETE, UPDATE문에서도 사용이 가능하며, WHERE절, GROUP BY, ORDER BY에서도 사용될 수 있다. 정렬이 되었을 때 더 효율적인 곳 어디서든 인덱스의 효과를 볼 수 있는데, 몇몇의 경우에는 인덱스의 효과를 볼 수 없는 경우들이 있다. 이는 명확한 시작점과 종료지점을 알 수 없는 경우이다. 아래는 그런 경우들의 예시들이다.
○ 레코드 내의 압축 조건이 없는 경우
이 말은 WHERE절, ON절 같이 뭔가 데이터를 거르는 게 없이 테이블 전체를 선택하는 경우를 말한다. 이 때는 쿼리 옵티마이저가 인덱스를 고르지 않는다.
이런 경우는 전체 사원의 연봉을 10%인상해라 같은 벌크성 쿼리를 날리는 경우가 아니라면 드물기 때문에 이런 실수는 거의 없을 것이라 생각한다.
○ like, IS NULL, 부정형을 사용하는 경우
위의 경우는 조건 절에 위의 조건이 있는 경우 인덱스를 선택하지 않는 경우를 모아놨다. 그런데 될 수도 있꼬 안될 수도 있다.
like를 사용하는 경우는 어떻게 like를 사용하느냐에 따라 인덱스의 효과를 볼 수도 있고 안 볼 수도 있다. like는 보통 와일드 카드가 맞는 표현인지 모르겠는데, %나 _를 통해 매칭 조건을 설정할 수 있다. 만약에 like 절을 '서울시%'와 같은 형태로 썼다고 해보자. 이 경우에는 index의 효과를 볼 수 있다. 왜냐하면 정렬조건과 관련이 있기 때문이다. 가나다 순으로 찾으면 서울시 무슨구 식으로 쉽게 찾을 수 있다.
그런데, 문제는 이 경우이다. '%광주시%' 이런 형태로 되어있으면, 정렬이 가나다 순이긴 하지만 곤란하다. 왜냐하면 광주시는 경기도 광주시도 있고 전라도 광주시도 있다. 그런데, 둘의 정렬에서 거리는 매우 멀며, 그걸 '%광주시%'라는 조건만 가지고 옵티마이저는 평가할 수 없다. 따라서 like절은 와일드 카드가 맨 앞에 있는 경우에는 인덱스의 효과를 볼 수 없다.
IS NULL과 부정형은 웬만한 데서는 인덱스를 사용하지 않는데, IS NULL의 경우 DB2 같은 특정 DB는 인덱스에 NULL을 저장하기도 하기 때문에 DB마다 다르다. 하지만 일반적인 DB에서는 인덱스를 사용하지 않는다.
○ 데이터에 추가적인 처리, 가공을 하는 경우
데이터에 추가적인 처리, 가공 한다는 말이 굉장히 모호하게 적어놨는데, 두가지 예시를 들어보려고 한다.
첫번째 예시는 WHERE 절에서 아래와 같이 필드에 추가적인 연산을 넣는 경우이다. 이런 경우에는 PRICE * 1.1을 기준으로 인덱스가 만들어져있지 않기 때문에 PRICE 필드에 인덱스를 걸어놨어도 적용이 되지 않는다.
SELECT * FROM PRODUCT WHERE PRICE * 1.1 > 10000;
이런 검색 조건을 사용하고 싶다면, 차라리 아래와 같이 쿼리를 작성하는게 좋다. 이 경우는 PRICE에 따라서 인덱스가 만들어져있으므로 정상적으로 인덱스가 동작한다.
SELECT * FROM PRODUCT WHERE PRICE > 100/1.1;
이 외에도 시간 테이블에 대해서 SUBSTRING으로 년, 월을 잘라버리거나 하는 경우도 첫 번째 예시에 속한다. 단, SUBSTRING의 경우, 만약 단어가 "서울시 도봉구"인데 substring(1,4)로 서울시까지만 되면 이 경우에는 정렬 조건으로 시작점과 끝점을 알 수 있으므로 인덱스를 활용할 수 있다.
두 번째 예시는 서브 쿼리를 만드는 경우이다. 서브 쿼리로 만들어진 데이터는 임시적인 값이다. 다음과 같은 테이블을 만들었다고 생각을 해보자. SQL 레벨업이라는 책에서 예시를 가지고 왔다.
CREATE TABLE Receipts (cust_id CHAR(1) NOT NULL,
seq INTEGER NOT NULL,
price INTEGER NOT NULL,
PRIMARY KEY (cust_id, seq));
INSERT INTO Receipts VALUES ('A', 1, 500);
INSERT INTO Receipts VALUES ('A', 2, 1000);
INSERT INTO Receipts VALUES ('A', 3, 700);
INSERT INTO Receipts VALUES ('B', 5, 100);
INSERT INTO Receipts VALUES ('B', 6, 5000);
INSERT INTO Receipts VALUES ('B', 7, 300);
INSERT INTO Receipts VALUES ('B', 9, 200);
이제 각 고객별 가장 seq가 낮은 레코드를 구하는 경우를 생각해보자. 그러면 서브 쿼리를 이용해서, 아래와 같이 코드를 작성할 것이다. 문제가 많은 코드이지만, 아래의 WHERE 절에 있는 서브쿼리는 실제 데이터가 아니라 임시로 만들어진 데이터이다. 따라서, 처음에 테이블을 생성할 때 PK로 설정한 내용에 대한 이점을 전혀 볼 수 없고 실행 계획을 보면 풀 테이블 스캔을 통해 데이터를 조회함을 볼 수 있다.
SELECT cust_id, seq, price
FROM Receipts R1
WHERE seq = (SELECT MIN(seq)
FROM Receipts R2
WHERE R1.cust_id = R2.cust_id);
○ 인덱스로 선택된 컬럼이 여러 개인 경우
위의 상황에서 seq과 price 필드를 묶어 인덱스를 (seq, price)에 맞게 설정을 해놓았다고 생각해보자. 이 때, 인덱스를 타기 위해서 검색 조건을 설정한다고 해보자. (seq, price)를 검색 조건으로 하는 경우에는 반드시 인덱스 범위 스캔이 되는 상황이니 부가적인 설명을 하지 않고 이 외의 상황에 대해서 알아보자.
1. seq을 검색 조건으로 하는 경우
인덱스를 (seq,price)로 설정해놓으면 정렬 순서가 seq을 기준으로 정렬하고 seq이 같으면 price 순서대로 정렬이 된다. 따라서, seq 컬럼만을 검색 조건으로 하는 경우, seq 기준으로 정렬이 되어있기 때문에 시작점과 끝점을 알 수 있기 때문에 인덱스 범위 스캔이 가능하다.
2. price를 검색 조건으로 하는 경우
seq을 기준으로 정렬이 우선적으로 되어있기 때문에 price는 여러 곳에 흩어져있다. 따라서, 시작점과 끝점을 알 수 없으니 인덱스 범위 스캔이 불가능하다.
○ 형변환이 의도하건 의도하지 않건 들어가는 경우
SQL을 짜다보면, 데이터베이스마다 다르지만 Oracle 같은 데이터베이스는 숫자형과 문자형 같은 충돌이 일어나는 경우에 이를 자동적으로 형변환해 처리한다.
예를 들면, 숫자로 저장된 번호를 like 절로 검사하는 경우, like는 문자형 데이터이기 때문에 각각의 숫자 데이터를 문자로 변경한다. 혹은 문자형인데 우측에서 비교되는 값은 숫자인 경우, 숫자형이 우선으로 설정된 데이터베이스의 경우 문자를 숫자로 변경한다. 이를 지원하지 않는 데이터베이스는 바로 오류를 뱉지만 그러지 않는 경우에는 두 가지 이유로 성능저하가 발생한다.
첫 번째 이유는 데이터베이스 내부에서 형변환 자체를 하는 것도 내부 메모리를 사용하는 행위이다. 또한 두 번쨰 이유는 이렇게 가공된 인덱스 컬럼의 데이터의 경우, 맨 앞 부분이 일치하여 개발자의 생각에서는 인덱스 범위 스캔이 가능할 것 같겠지만, 타입이 다르기 떄문에 인덱스를 제대로 활용하지 못한다.
○ 위의 사례의 공통점과 인덱스를 사용할 때 고려사항
이 때까지 말한 모든 내용 중 매개변수에 관한 이야기를 제외하고 인덱스 범위 스캔이 제대로 이루어지지 않는 경우들의 공통점은 딱 한 개의 시작점과 종료지점만 명확하게 가지지 않는다는 것이다.
예를 들면, like 절에서 맨 앞에 와일드카드가 있는 경우, 데이터를 추가가공한 경우, 실제 데이터와 다르므로 시작점을 어찌저찌 찾더라도 그게 정렬과 관련이 없기 때문에 시작점이 여러 개가 된다. 따라서, 결국엔 명확한 한 개의 시작점과 종료지점이란 보장이 없으니 풀테이블 스캔을 인덱스로 하게 되는 것이다.
결과적으로 여기서 알 수 있는 내용은 인덱스를 제대로 활용한다는 것은 인덱스를 통해 단 한 개의 시작점과 종료지점이 나오게 쿼리를 짜는 것을 의미한다는 것이다. 또한 무조건적으로 특정한 명령어에 대해서는 안된다, 특정 컬럼에 대한 조작이 일어났을 때에는 안된다가 아님을 이해하는 것이라고 생각한다.
5. 내용 정리
결과적으로 글이 엄청 길어진 것 같아 정리를 해보면, 인덱스는 선택률이 낮은 필드에 대해 걸어 조회했을 때의 성능 최적화를 위해서 사용할 수 있다. 인덱스를 사용할 때에는 조회에서의 검색 조건과 테이블 내에서의 데이터 상태를 확인하며 설정을 해야 한다.
인덱스를 설정하는 방법에는 PK 혹은 UNIQUE + NOT NULL로 클러스터링 인덱스를 걸어 데이터 자체를 정렬해버리거나 UNIQUE 혹은 CREATE INDEX 명령어를 통해 논-클러스터링 인덱스를 걸어 색인 형태로 데이터를 정렬하는 방법으로 설정을 할 수 있다.
인덱스를 걸게 되면, 추가적인 메모리 자원을 사용하게 되고 데이터의 조회에서는 이점을 얻을 수 있겠지만 삽입, 변경, 삭제에서는 B+Tree 구조를 사용하기 때문에 복잡하며 인덱스가 늘어날 수록 한 개의 레코드 변경을 위해서 더 많은 변경이 일어나기 때문에 신중하게 사용을 해야한다. 신중하게 사용을 하기 위해서는 데이터의 특성에 관한 이해와 지속적인 모니터링이 필요하다.
마지막으로 인덱스를 잘 설정해놨어도, SQL을 어떻게 짜느냐에 따라서 인덱스가 사용이 안되는 like 절 예시, 서브 쿼리 예시가 있으니 이런 상황을 잘 보고 쿼리를 작성해야 함을 꼭 기억하고 넘어가자.
[ 참고 자료 ]
SQL 레벨업 - 미크
친절한 SQL 튜닝 - 조시형
https://mangkyu.tistory.com/286
https://hongcana.tistory.com/97
https://www.youtube.com/watch?v=edpYzFgHbqs&t=1064s
https://www.youtube.com/watch?v=IMDH4iAQ6zM
'데이터베이스 & SQL > 데이터베이스' 카테고리의 다른 글
| 쿼리 실행 계획(TIL) (0) | 2024.04.24 |
|---|---|
| 데이터 베이스 메모리(TIL) (0) | 2024.04.22 |
| JOIN의 종류와 주의사항 (TIL) (0) | 2024.04.21 |
| MySQL의 격리 수준 (0) | 2024.02.25 |
| 무결성 제약 조건 - MySQL 코드 첨부 (0) | 2024.02.24 |