MySQL의 인덱스

인덱스란?

추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조다.

보통 책의 색인과 비슷한 기능을 한다.

색인은 책의 저자들이 책의 맨 앞 또는 맨 뒤에 특정 내용의 페이지를 적어둔 것이다. 인덱스 또한 데이터베이스의 테이블의 모든 데이터를 검색하면 시간이 오래 걸리기 때문에 데이터와 데이터의 위치를 포함한 자료구조다.

 

데이터베이스에서 인덱스를 사용하면, 데이터를 검색할 때 인덱스를 사용하요 검색 대상 레코드의 범위를 줄일 수 있다. 대량의 데이터를 다루는 경우 데이터 검색 속도를 크게 향상시킨다.

 

인덱스를 활용하면 조회 쿼리 이외에도 수정 및 삭제 쿼리의 성능 또한 함께 향상된다. 만약 인덱스를 사용하지 않은 컬럼을 조회해야 한다면 톄이블의 데이터를 모든 탐색하는 Full Scan 이 발생한다. 테이블 내의 모든 데이터를 한 번씩 거쳐야 하기 때문에 인덱스를 사용했을 때보다 처리 속도가 현저히 느려지게 된다.

인덱스의 관리

데이터베이스는 인덱스를 항상 최신의 정렬된 상태로 유지해야 원하는 값을 빠르게 탐색할 수 있기 때문에 인덱스가 적용된 컬럼에 추가, 수정, 삭제가 수행된다면 해당 컬렘의 인덱스에 대한 추가적인 작업을 필요로 하기 때문에 오버헤드가 발생한다. 

 

추가의 경우 새로운 데이터에 대한 인덱스를 추가해야 한다. 삭제의 경우 삭제하는 데이터의 인덱스를 사용하지 않도록 작업을 수행해야 하고 수정의 경우 기존의 인덱스를 사용하지 않음 처리하고, 갱신된 데이터에 대해 인덱스를 추가해야 한다.

인덱스의 장단점

장점

• 테이블을 조회하는 속도와 그에 따른 성능을 향상시킬 수 있다.
• 전반적인 시스템의 부하를 줄일 수 있다.

단점

• 인덱스를 관리하기 위해 데이터베이스의 약 10%에 해당하는 저장공간이 필요하다.
• 인덱스 관리를 위해 추가적인 작업이 필요하다.
• 인덱스를 잘못 사용할 경우 성능이 더욱 저하되는 역효과가 발생할 수 있다.
  • 만약 추가, 삭제, 갱신이 빈번한 속성에 인덱스를 걸게 되면 인덱스의 크기가 비대해져서 성능이 오히려 저하된다.
  • 갱신과 삭제의 경우 기존의 인덱스를 삭제하지 않고 사용하지 않음 처리를 하기 때문에 실제 데이터보다 더 많은 인덱스가 존재하게 되기 때문에, SQL문 처리 시 비대해진 인덱스에 의해 오히려 성능이 떨어지게 된다.

인덱스로 사용하면 좋은 속성

• 삽입, 삭제, 갱신이 자주 발생하지 않는 속성
• JOIN, WHERE, ORDER BY에 자주 사용되는 속성
• 데이터의 중복도가 낮은 속성

인덱스를 사용하는 경우

• 대량의 데이터를 검색하는 경우
• 정렬된 결과를 출력하는 경우
• JOIN 연산을 수행하는 경우
• 유니크한 값을 가져오는 경우
• 검색 빈도가 높은 경우

인덱스의 자료구조

인덱스를 구현하기 위해서는 다양한 자료구조를 사용할 수 있다. 그 중 가장 자주 사용되는 것이 해시 테이블과 B+Tree 구조다.

 

해시 테이블은 Key,와 Hash Value 값으로 데이터를 저장하는 자료구조로 빠른 데이터 검색이 필요할 때 유용하다. Key 값을 이용해 고유한 인덱스를 생성하여 그 인덱스에 저장된 값을 꺼내오는 구조다.

 해시 테이블 기반의 인덱스는 데이터를 Key로 데이터의 위치를 Value로 사용하여 데이터로 생성된 해시를 통해 인덱스를 구현한다. 해시 테이블의 시간 복잡도는 O(1) 이기 때문에 매우 빠른 검색을 지원한다. 

 

하지만 인덱스에서 해시 테이블이 사용되는 경우는 매우 제한적인데, 그 이유는 해시가 등호 연산에만 특화되어있기 때문이다. 부등호 연산이 자주사용되는 데이터베이스 검색 시에는 해시 테이블이 적합하지 않다.

 

그 이유는 해시 함수는 값이 조금이라도 달라질 경우 완전히 다른 해시 값을 생성하기 때문이다. 이러한 이유 때문에 데이터베이스의 인덱스에서는 B+Tree가 주로 사용된다.

 

B+Tree는 인덱스를 위해 자식 노드가 2개 이상인 B-Tree를 개선시킨 자료구조다. B+Tree는 모든 노드에 데이터를 저장했던 B-Tree와는 다른 특성을 가지고 있다. 

 

리프 노드(데이터 노드)만 인덱스와 함께 데이터를 가지고 있고, 나머지 노드들은 데이터를 위한 인덱스만을 갖는다. 리프 노드들은 LinkedList로 연결되어 있으며 데이터 노드 크기는 인덱스 노드의 크기와 같지 않아도 된다.

 

ㄱ인덱스 컬럼은 부등호를 이용한 순차 검색 연산이 자주 발생될 수 있는데, B+Tree의 LinkedList로 연결된 리프 노드들이 순차검색을 용이하게 하는 방식으로 인덱스에 맞게 최적화하였다. MySQL의 데이터베이스 엔진인 InnoDB에서는 같은 레벨의 노드들끼리는 LinkedList가 아닌 Double LinkedList로 연결되었으며, 자식 노드들은 Single LinkedList로 연결되어 있다.

B+Tree 좀 더 알아보기

B+Tree 인덱스에 대한 이해를 위해서는 B-Tree 자료구조에 대한 이해도가 우선적으로 필요하다. B-Tree는 자식 노드를 2개만 가지는 이진 트리를 확장하여 N개의 자식 노드를 가질 수 있도록 고안된 자료구조다. 좌우의 자식 노드 간의 균형이 맞지 않을 경우에는 매우 비효율적이기 때문에, 항상 균형을 맞춘다는 의미에서 Balanced Tree 라고도 부른다.

 

B-Tree는 최상위에 단 하나의 노드만이 존재하는데, 이를 Root Node라 부른다. 그리고 중간 노드를 Branch Node, 최하위 노드를 Leaf Node 라고 한다. 

 

 

Page 란?

인덱스의 저장 방식을 이해하기 위해서는 페이지에 대해 알아야 한다. 페이지란 디스크와 메모리(Buffer Pool)에 데이터를 읽고 쓰는 최소 작업 단위다. 일반적으로 인덱스를 포함해 PK와 같은 클러스터 인덱스와 테이블 등은 모두 페이지 단위로 관리된다.

 

따라서 만약 쿼리를 통해 1개의 레코드를 읽고 싶더라도 결국은 하나의 페이지를 읽어야 하는 것이다. 그렇기 때문에 페이지에 저장되는 개별 데이터의 크기를 최소한으로 하여 1개의 페이지에 많은 데이터들을 저장할 수 있도록 하는 것이 중요하다. 

 

페이지에 저장되는 데이터의 크기가 클수록 디스크 I/O가 많이지거나, 메모리에 캐싱할 수 있는 페이지의 수가 줄어드는 문제가 발생할 수 있다. 

 

만약 레코드를 찾는데 1개의 페이지만으로 처리가 불가능하다면, 다른 페이지를 읽어야 하는데 추가 페이지를 읽는 디스크 I/O 때문에 성능이 저하된다. 또한 디스크 I/O를 통해 페이지를 읽어오면 버퍼풀이라는 메모리에 캐싱해둔다. 만약 페이지의 크기가 커지게 되면 메모리에 캐싱해둘 수 있는 페이지의 수가 줄어들게 되기 때문에 성능 개선 혹은 쿼리 튜닝은 디스크 I/O 자체를 줄이는 것이 핵심인 경우가 많다.  

 

인덱스는 테이블과 독립적인 저장 공간이므로 인덱스를 통해 데이터를 조회하기 위해서는 먼저 PK 찾아야 한다. PK로 레코드를 조회할 때는 PK가 어느 페이지에 저장되어 있는지 알 수 없으므로 랜덤 I/O가 발생한다. 이후에는 PK를 따라 Leaf Node에서 실제 레코드를 읽어온다. 

 

연속된 데이터를 조회하는 경우애는 순차 I/O가 발생하는데, 랜덤 I/O는 임의의 장소에서 데이터를 가져오지만 순차 I/O는 다름 장소에서 데이터를 가져오므로 훨씬 빠르다.

인덱스 사용시 주의사항

인덱스를 통해 데이터를 조회하는 것은 아래의 작업이 순차적으로 진행되며 이루어진다.

• 인덱스를 통해 PK를 찾는다
• PK를 통해 레코드를 찾는다

데이터베이스의 옵티마이저는 인덱스를 통해 1건의 레코드를 읽는 것이 테이블을 통해 직접 읽는 것보다 4~5배 정도 비용이 더 많이 드는 것으로 예측한다. 하지만 DBMS는 개발자가 원하는 레코드가 어디있는지 모르기 때문에, 모든 테이블을 살펴가며 레코드를 찾아야 한다.

 

인덱스를 통해 레코드 1건을 읽는 것이 4~5배 정도 비용이 더 들어가는 것은 사실이기 때문에, 읽어야 할 레코드의 건수가 테이블 레코드의 20~25%를 넘어서면 인덱스를 이용하지 않는 것이 효율적이다. 이런 경우 옵티마이저는 인덱스를 이용하지 않고 테이블 전체를 읽어서 처리한다.

인덱스 사용에 영향을 주는 요소

PK의 크기

PK가 레코드의 물리적인 저장 위치를 결정하기 때문에, 인덱스는 PK에 의존한다. 인덱스가 PK가 아닌 실제 레코드의 주소를 갖게 할 수도 있지만 그럴 경우, PK가 변경될 때 레코드의 주소가 변경되고 모든 인덱스에 저장된 레코드 주소를 변경해야 한다. 이러한 오버헤드를 피하기 위해 인덱스는 레코드의 주소가 아닌 PK를 저장하고 있다.

 

따라서 PK 값이 클수록 인덱스에 좋지 않다. PK가 클수록 한 페이지에 담을 수 있는 인덱스 정보도 줄어들고 메모리도 비효율적으로 사용되기 때문이다. 또한 트리의 깊이도 지나치게 깊어지면서 읽어야 하는 페이지가 많아져서 성능에 좋지 않다.

 

Cardinality

카디널리티란 특정 컬럼에 존재하는 데이터의 고유성을 의미한다. 카디널리티가 높을수록 중복도가 낮고 유니크한 값이 많다는 것이다. 항상 그런 것은 아니지만 일반적으로 유니크한 값이 많아지게 될 경우 검색 대상이 줄어서 처리가 빠르다. 이러한 이유로 일반적으로 인덱스는 유니크할수록 효율적이다.

 

인덱스의 정렬 및 스캔 방향

인덱스는 설정된 정렬 규칙에 따라 정렬되어 저장된다. 예를 들어 인덱스 생성 시 다음과 같이 정렬 순서를 설정할 수 있다.

CREATE INDEX idx_dept_emp (`dept_no`, `emp_no`) ON employees (dept_no ASC, emp_no DESC);

 

인덱스를 정렬 순서대로만 읽을 수 있는 것은 아니다. 인덱스가 오름차순으로 생성되었어도 내림차순으로 읽는 것이 가능하며, 인덱스를 읽는 방향은 옵티마이저가 실시간으로 만들어내는 실행 계획에 따라 결정된다. 인덱스를 순서대로 읽는 것을 Index Forward Scan, 반대 방향으로 읽는 것을 Index Backward Scan 이라고 한다.

SELECT * FROM employees ORDER BY first_name DESC LIMIT 1;

 

위와 같은 쿼리를 실행시킨다면 옵티마이저는 뒤에서부터 읽는 것이 최적임을 알고 역순으로 접근해 레코드를 읽어온다. 인느 ORDER BY, MIN(), MAX() 등에도 동일하게 적용될 수 있다. 하지만 Index Backward Scan 은 Forward Scan 보다 속도가 느리다. 

 

Leaf Node의 페이지는 이중 연결 리스트로 되어 있다. 하지만 페이지 잠금 과정에서 데드락을 방지하기 위해 잠금을 획득하는 것이 Forward Scan 에서만 가능하고, 역순은 복잡한 과정이 필요하다. 이 부분은 구조적으로 인덱스 스캔이 Forward Scan 에 적합하다고 알고 있으면 된다.

 

또한, 페이지 내에서 인덱스가 단방향으로만 연결되어 있기 때문이다. 페이지 내부에서는 순차적으로 4~8개 정도씩 묶어서 그룹을 만든다. 그리고 그룹의 대표키를 선정해서 리스트로 관리하는데, 이를 Page Directory 라고 한다.

 

페이지 디렉토리는 단방향 연결이기 때문에 역방향으로 접근이 불가능하다. 그래서 Backward Scan의 경우 일부 단방향 접근이 필요하므로 Backward Scan이 Forward Scan보다 느린 것이다.

레코드 삽입, 삭제, 갱신이 인덱스에 미치는 영향

레코드 삽입

레코드가 삽입되면 인덱스도 같이 추가되어야 하며, 인덱스는 항상 정렬된 상태를 유지해야 하므로 적절한 위치 탐색 후에 저장된다. 설정된 인덱스의 수가 늘어날 수록 작업 비용 또한 비례하여 증가하는데, 이때의 작업 비용은 디스크 I/O 비용이기 때문에 성능에 크게 영향을 미친다.

 

그래서 인덱스 추가 작업을 즉시 처리하지 않고, 메모리에 모아서 한 번에 쓰도록 지연시킬 수도 있다. 이로 인해 디스크 쓰기 횟수를 줄일 수도 있고, 요청 시에 메모리에서 바로 결과를 반환할 수 있다는 장점이 존재한다.

 

하지만 유니크 인덱스처럼 중복 체크 등의 무결성이 필요하다면 즉각 반영되기도 한다. 이때 중복된 값을 체크하기 위해서는 읽기 잠금을 사용하고 값을 삽입할 때는 쓰기 잠금을 사용하는데, 이 과정에서 데드락이 아주 빈번히 발생한다. 그렇기에 유니크 인덱스는 반드시 유일성이 보장되어야 하는 경우에 사용하는 것이 좋다.

 

레코드 삭제

레코드를 삭제하면 인덱스도 삭제되어야 하는데, 이는 인덱스의 Leaf Node 에 삭제 마킹만 하면 된다. 삭제 마킹 역시 디스크 쓰기 작업이므로 이 작업도 지연 처리될 수 있다. 또한 삭제 마킹된 공간은 계속해서 방치시킬 수도 있고 재활용할 수도 있다. 

 

MySQL 에서 인덱스는 조금 더 특별한 주의가 필요한데, MySQL 의 잠금 중 일부는 검색을 수행한 인덱스를 잠근 후 테이블의 레코드를 잠그는 방식으로 구현되어 있다. 따라서 갱신이나 삭제를 실행할 때 적절히 사용할 수 있는 인덱스가 없으면 불필요하게 많은 레코드를 잠그게 되며, 심할 경우 테이블의 모든 레코드를 잠글 수도 있다.

 

레코드 갱신

레코드 갱신은 레코드를 추가하고 삭제하는 것이며, 갱신 대상은 크게 PK, 인덱스, 일반 값이 있다. PK가 수정되는 경우에는 삭제와 삽입, 최소 두 개의 쓰기 작업이 필요하다. 그리고 해당 테이블에 인덱스가 있다면 인덱스에도 추가 작업이 필요하므로 상당한 비용이 든다. 

 

인덱스가 갱신되는 경우에는 테이블 뿐만 아니라 인덱스에 추가 작업이 반드시 필요하므로, 해당 작업도 비용이 많이 든다.

 

따라서 인덱스와 PK는 최대한 변경을 피해야 한다. 그 외의 값이 수정되는 경우는 테이블만 작업하면 되므로 비교적 수월하다. 참고로 레코드 수정은 결국 삭제 후에 삽입하는 작업이라서 PK나 인덱스가 사용되는 쿼리라면 이를 활용하여 처리된다.

인덱스 스캔의 종류

인덱스 레인지 스캔

인덱스 레인지 스캔은 범위가 결정된 인덱스를 읽는 방식으로, 정해진 범위만 접근하면 되므로 이 방식은 다른 방식들보다 빠르다. 일반적으로 인덱스를 탄다고 하면 인덱스 레인지 스캔으로 데이터를 조회하는 것을 의미한다.

 

1. 인덱스 탐색 : 인덱스의 조건을 만족하는 값이 저장된 위치를 찾는다.

2. 인덱스 스캔 : 시작 위치부터 필요한 만큼 인덱스를 순서대로 읽는다.

3. 랜덤 I/O : 읽어들인 인덱스와 PK를 이용해 최종 레코드를 읽어온다.

 

아래와 같은 쿼리를 실행시켰을 때 

SELECT * FROM fruit WHERE name BETWEEN 'Lemon' AND 'Mango';

 

출처 : https://mangkyu.tistory.com/286

먼저 Root Node 에서 시작해서 Branch Node를 거쳐 원하는 인덱스가 저장된 Leaf Node로 이동한다. 읽고 쓰는 단위는 페이지이므로 원하는 인덱스를 찾기 위해서는 페이지의 처음부터 시작해서 시작점인 'Lemon' 을 찾는데 이를 탐색이라 한다. 

 

시작점을 찾았으면 이후에는 순서대로 검색 범위의 마지막 인덱스인 'Mango' 까지 읽으면 되는데 이를 스캔이라고 한다. 스캔이 가능한 이유는 인덱스가 순차적으로 정렬되어 있기 때문이며, 스캔을 하다가 페이지의 끝에 도달하면 페이지 간의 링크를 이용해 다음 페이지로 넘어간다. 

 

만약 쿼리가 인덱스나 PK가 아닌 다른 값을 필요로 한다면 테이블로부터 레코드를 조회해와야 하며, 이때는 PK를 이용해 랜덤 I/O를 통해 레코드를 조회해야 한다. 만약 인덱스나 PK만 필요로 한다면 해당 작업은 실행되지 않는데, 이를 커버링 인덱스라고 한다. 커버링 인덱스는 랜덤 I/O 작업을 줄일 수 있어서 성능이 훨씬 뛰어나다. 

 

인덱스에 PK가 저장되는 것은 MySQL의 InnoDB가 클러스터 테이블이기 때문에 이러한 구조를 갖는 것이지 다른 DBMS의 경우에는 다를 수 있다.

 

인덱스 풀 스캔

인덱스 풀 스캔은 처음부터 끝까지 페이지를 이동하며 모든 인덱스를 읽는 방식이다. 쿼리가 인덱스나 PK만 필요로 한다면 해당 방식이 주로 사용되고, 그 외의 컬럼이 필요하다면 절대 이 방식으로 처리되지 않는다.

 

또한 쿼리의 조건절에 사용된 컬럼이 인덱스의 첫 번째 컬럼이 아닌 경우에 사용된다. 

 

인덱스 풀 스캔이 사용되는 이유는 테이블에는 다른 레코드들도 포함되어 있으니 비효율적이기 때문이다. 해당 방식이 아주 빠르지는 않지만 테이블 풀 스캔보다는 적은 디스크 I/O로 쿼리를 처리할 수 있다. 그렇다고 하여 인덱스 풀 스캔을 위해서 인덱스를 생성해서는 안되며, 인덱스 풀 스캔은 일반적으로 인덱스를 탄다고 하지 않는다.

 

루스 인덱스 스캔

루스 인덱스 스캔은 말 그대로 인덱스를 듬성듬성 읽는 방식이다. 일반적으로 GROUP BY 혹은 MIN(), MAX()와 같은 집계 합수를 사용하는 쿼리를 최적화할 때 사용되며, 중간에 불필요한 인덱스는 무시하고 넘어간다.

 

예를 들어 (dept_no, emp_no) 로 인덱스가 생성되어 있는 테이블에 아래와 같은 쿼리를 실행했다.

SELECT dept_no, MIN(emp_no)
FROM dept_emp 
WHERE dept_no BETWEEN 'D002' AND 'D004'
GROUP BY dept_no;

 

위의 쿼리에서 emp_no는 dept_no에 의존하여 정렬되므로, dept_no 그룹 별로 가장 처음의 emp_no 값만 읽으면 된다. 옵티마이저 또한 이러한 부분을 알고 있기 때문에, 처음이 아닌 emp_no는 무시하고 넘어간다.

출처 : https://mangkyu.tistory.com/286

이러한 부분은 인덱스의 정렬 특성 때문이므로, 인덱스를 구성하는 컬럼의 순서는 상당히 중요하다.

 

인덱스 스킵 스캔

인덱스 스킵 스캔은 MySQL 8.0부터 추가된 기능으로, 인덱스의 뒷 컬럼만으로 검색하는 경우에 옵티마이저가 자동으로 쿼리를 최적화하여 인덱스를 타도록 하는 읽기 방식이다. 

 

예를 들어 아래와 같은 employee 테이블에 다읔과 같은 인덱스를 생성했다고 가정한다.

ALTER TABLE employee
ADD INDEX idx_gender_birthdate (gender, birth_date);

이 인덱스를 사용하기 위해서는 WHERE 절에 gender 에 대한 비교 조건이 반드시 존재해야 한다. birth_date 만으로 검색하는 경우라면 인덱스를 새롭게 만들어줘야 한다. 

 

아래와 같은 쿼리는 인덱스를 타지 못한다.

SELECT gender, birth_date
FROM employee
WHERE birth_date >= '1994-12-26';

 

하지만 MySQL 8.0부터는 옵티마이저가 인덱스를 타지 못하는 쿼리를 최적화한다. 만약 위와 같은 쿼리를 실행시키면 아래와 같이 실행된다.

SELECT gender, birth_date 
FROM employee 
WHERE gender = 'M' AND birth_date >= '1994-12-26';


SELECT gender, birth_date 
FROM employee 
WHERE gender = 'F' AND birth_date >= '1994-12-26';

인덱스 스킵 스캔이 실행되기 위해서는 다음의 조건들을 모두 만족시켜줘야 한다.

• 조회되는 컬럼은 인덱스만으로 처리 가능해야 한다.
• 인덱스의 선행 컬럼은 WHERE 절에 없어야 한다.
• 인덱스 선행 컬럼의 카디널리티가 낮아야 한다. 즉, 유니크한 값이 적어야 한다.

인덱스를 사용하지 않는 경우

풀 테이블 스캔은 인덱스를 사용하지 않고 테이블의 처음부터 끝까지 읽는 방식이다. 옵티마이저는 아래의 경우에 주로 풀 테이블 스캔을 이용한다.

• WHERE 절이나 ON 절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
• 테이블의 레코드 건수가 너무 적어서 인덱스를 통해 읽는 것보다 풀 테이블 스캔을 하는 것이 더 빠른 경우
• 인덱스 레인지 스캔을 사용할 수 있더라도 일치되는 레코드 건수가 너무 많은 경우

일반적으로 전체 테이블의 크기는 인덱스보다 훨씬 크기 때문에 풀 테이블 스캔은 상당히 많은 디스크 읽기를 필요로한다.

MySQL은 특정 테이블의 연속된 페이지가 읽히면 백그라운드 쓰레드를 통해 다음 페이지의 작업을 미리 읽어 메모리(Buffer Pool)에 넣어두는 Read Ahead 기능을 갖고 있다. 

 

Read Ahead 작업은 4개 또는 8개의 페이지부터 시작하여 최대 64개의 페이지까지 읽어오도록 증가된다. 이는 풀 인덱스 캔에도 동일하게 적용된다.

데이터 변경 시 주의사항

MySQL에는 Gap Lock, Next Key Lock, Auto Increment Lock 등 많은 락들이 존재하는데, 그 중에서 Record Lock, Record Only Lock이 인덱스와 밀접하게 관련되어 있다.

 

레코드 락이란 레코드를 잠그기 위한 락인데, MySQL의 경우에는 실제 레코드 자체가 아니라 인덱스를 잠근다는 정메서 다른 DBMS와 큰 차이가 있다. 그리고 레코드를 잠그는 것과 인덱스를 잠그는 것은 상당히 중요한 차이를 만들어낸다.

 

MySQL에서는 레코드 자체를 잠그는 것이 아니라 인덱스를 잠그기 때문에, 변경해야 할 레코드를 찾기 위해 검색한 모든 인덱스에 락을 걸어야 한다. 아래와 같은 테이블이 존재한다고 가정해보자.

// last_name(성)에 대해서만 인덱스가 걸려 있음
CREATE TABLE student (
    emp_no INT NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(64),
    last_name VARCHAR(64),
    entrance_at TIMESTAMP,
    PRIMARY KEY(emp_no),
    INDEX idx_last_name (last_name)
) ENGINE=InnoDB;

해당 테이블에 대해 아래와 같은 갱신 쿼리를 실행한다.

UPDATE student 
SET entrance_at = NOW() 
WHERE last_name = 'Cho'     // 300건
AND first_name = 'Seungbin'  // 1건

여기서 last_name = 'Cho' 를 만족하는 레코드가 300건, first_name = 'Seungbin' 을 만족하는 레코드가 1건이라고 가정했을 때, last_name 은 인덱스가 존재하지만 first_name 에는 존재하지 않기 때문에 단 하나의 레코드를 갱신하기 위해서는 불가피하게 300건의 인덱스에 락을 걸어야 한다.

 

만약 last_name 에 대한 인덱스 또한 존재하지 않았다면, 갱신 쿼리를 위해 테이블을 풀 스캔하면서 레코드를 갱신하게 되는데, 이때 테이블의 모든 레코드를 잠그게 된다.

 

실제로 레코드 락은 PK 또는 유니크 인덱스를 이용해 변경을 하는 경우에 사용한다.

 

참고 블로그 : https://mangkyu.tistory.com/286