SQL/심화과정

인덱스 Index - (2)

domsam 2025. 3. 4. 17:49
반응형

1. B-Tree 인덱스 키 추가

새로운 키 값이 B-Tree에 저장될 때 테이블의 스토리지 엔진에 따라 새로운 키 값이 즉시 인덱스에 저장될 수도 있고 딜레이 될 수도 있다. B-Tree에 저장될 때는 저장될 키 값을 이용해 B-Tree상의 적절한 위치를 검색해야 한다. 저장될 위치가 결정되면 레코드의 키 값과 대상 레코드의 주소 정보를 B-Tree의 리프 노드에 저장한다. 리프 노드가 꽉 차면 리프 노드가 분리되어야 하는데 이것은 상위 브랜치 노드까지 처리의 범위가 넓어지기 때문에 B-Tree는 상대적으로 쓰기 작업(새로운 키를 추가하는 작업)에 비용이 많이 드는 것으로 알려져 있다. 

인덱스 추가로 인해 INSERT, UPDATE 문장이 어느정도 비용이 발생하는지는 테이블의 컬럼 수, 컬럼의 크기, 인덱스 컬럼의 특성 등 여러가지 요인이 있을 수 있지만 대략적으로 계산하는 방법은 이렇다. 테이블에 레코드를 추가하는 작업 비용을 1이라고 가정하면 해당 테이블의 인덱스에 키를 추가하는 작업 비용을 1.5 정도로 예측하는 것이다. 만약 어떤 테이블에 B-Tree 인덱스가 3개가 있다면 하나의 레코드를 추가하는 비용은 1.5 x 3 + 1 = 5.5 정도로 예측한다. 중요한 것은 이 비용의 대부분이 CPU와 Memory에서 처리하는 시간이 아니라 디스크로부터 인덱스 페이지를 읽고 쓸 때 걸리는 시간이라는 점이다. 

MyISAM이나 MEMORY 스토리지 엔진을 사용하는 테이블에서는 INSERT 문장이 실행되면 즉시 새로운 키 값을 B-Tree 인덱스에 추가한다. 하지만 InnoDB 스토리지 엔진은 이 작업을 조금 더 지능적으로 처리하는데 필요하다면 인덱스 키 추가 작업을 지연시켜 나중에 처리할 수 있다. 이는 InnoDB 스토리지 엔진의 임시 메모리 공간인 체인지 버퍼를 사용하여 성능을 향상시킨다. 하지만 PK나 유니크 인덱스의 경우 중복 체크가 필요하기 때문에 즉시 B-Tree에 추가하거나 삭제한다. 

 

2. B-Tree 인덱스 키 삭제

해당 키 값이 저장된 B-Tree의 리프 노드를 찾아서 그냥 삭제 마크만 하면 작업이 완료된다. 이렇게 삭제 마킹된 인덱스 키 공간은 계속 그대로 방치하거나 재활용할 수 있다. 인덱스 키 삭제로 인한 마킹 작업 또한 디스크 쓰기가 필요하므로 이 작업 역시 디스크 I/O가 필요한 작업이다. MySQL 5.5 이상 버전의 InnoDB 스토리지 엔진에서는 이 작업 또한 체인지 버퍼를 사용하여 지연 처리될 수도 있다. MySQL 서버가 내부적으로 처리하므로 사용자는 신경쓸 필요는 없다. 

 

3. B-Tree 인덱스 키 변경

인덱스의 키 값은 그 값에 따라 저장될 리프 노드의 위치가 결정되므로 B-Tree의 키 값이 변경되는 경우에는 먼저 기존 키 값을 삭제한 후 새로운 키 값을 추가하는 형태로 처리된다. InnoDB 스토리지 엔진을 사용하는 테이블에 대해서는 이 작업 또한 체인지 버퍼를 활용해 지연 처리될 수 있다. 원칙상 인덱스 키 변경은 안 하는 것이 맞다.

 

4. B-Tree 인덱스 키 검색

INSERT, UPDATE, DELETE 작업을 할 때 인덱스 관리에 따르는 추가 비용을 감당하면서 인덱스를 구축하는 이유는 빠른 검색을 위해서다. 인덱스를 검색하는 작업은 B-Tree의 루트 노드부터 시작해 브랜치 노드를 거쳐 최종 리프 노드까지 이동하면서 비교 작업을 수행하는데 이 과정을 '트리 탐색'이라고 한다. 인덱스 트리 탐색은 SELECT에서만 사용하는 것이 아니라 UPDATE, DELETE를 처리하기 위해 항상 해당 레코드를 먼저 검색해야 할 경우에도 사용된다. 
B-Tree 인덱스를 이용한 검색은 100% 일치 또는 값의 앞부분만 일치하는 경우에 사용할 수 있다. 관계 비교 조건(크다, 작다)에서도 인덱스를 활용할 수 있지만 인덱스 키 값의 뒷부분만 검색하는 용도로는 인덱스를 사용할 수 없다. 또한 인덱스의 키 값에 변형이 가해진 후 비교되는 경우에는 B-Tree의 빠른 검색 기능을 사용할 수 없다.

InnoDB 스토리지 엔진에서 인덱스는 더 특별한 의미가 있다. InnoDB 테이블에서 지원하는 잠금(Lock)d은 레코드(Row) 수준의 잠금 방식을 사용한다. 여기서 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식인데 변경할 레코드를 찾기 위해 검색 된 레코드들을 모두 잠금 처리를 한다. 

UPDATE users 
   SET phone_number = 01099998888 
 WHERE first_name = 'Hong' 
   AND last_name = 'GilDong';

예를 들어 위와 같은 쿼리문을 실행할 때 first_name에만 인덱스가 있다면 first_name이 'Hong'인 모든 레코드를 잠금 처리하게 된다. 그래서 가능한 수정, 삭제 때는 WHERE 절에 PK가 들어가는 것이 좋다. 
만약 UPDATE, DELETE 문장이 실행될 때 테이블에 적절히 사용할 수 있는 인덱스가 없으면 불필요하게 많은 레코드를 잠근다. 심지어 테이블의 모든 레코드를 잠글 수도 있다. InnoDB 스토리지 엔진에서는 그만큼 인덱스의 설계가 중요하다.