[Real MySQL 1권] 인덱스와 잠금

 

1) InnoDB 잠금과 인덱스 

 

- InnoDB의 잠금과 인덱스는 상당히 중요한 연관 관계가 있다. 

  InnoDB의 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식으로 처리된다.

 

- 즉, 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 걸어야 한다.

  정확한 이해를 위해 다음 UPDATE 문장을 한 번 살펴보자. 

 

// 예제 데이터베이스의 employees 테이블에는 아래와 같이 first_name 칼럼만
// 멤버로 담긴 ix_firstname이라는 인덱스가 준비돼 있다.
// KEY ix_firstname (first_name)
// employees 테이블에서 first_name='Georgi'인 사원은 전체 253명이 있으며,
// first_name='Georgi'이고 last_name='Klassen'인 사원은 딱 1명망 있는 것을 아래 쿼리로
// 확인할 수 있다.

mysql> SELECT COUNT(*) FROM employees WHERE first_name='Georgi';
       253
mysql> SELECT COUNT(*) FROM employees WHERE first_name='Georgi' AND last_name='Klassen';
        1
        
// employees 테이블에서 first_name='Georgi'이고 last_name='Klassen'인 사원의
// 입사 일자를 오늘로 변경하는 쿼리를 실행해보자.
mysql> UPDATE employees SET hire_date=NOW() WHERE first_name='Georgi' AND last_name='Klassen'

 

- UPDATE 문장이 실행되면 1건의 레코드가 업데이트될 것이다. 

  하지만 이 1건의 업데이트를 위해 몇 개의 레코드에 락을 걸어야 할까?

  이 UPDATE 문장의 조건에서 인덱스를 이용할 수 있는 조건은 first_name='Georgi'이며,

  last_name 칼럼은 인덱스에 없기 때문에 first_name='Georgi'인 레코드 253건의 레코드가 모두 잠긴다.

 

- 이 예제에서는 몇 건 안 되는 레코드만 잠그지만 UPDATE 문장을 위해 적절히 인덱스가 준비돼 있지 않다면

  각 클라이언트 간의 동시성이 상당히 떨어져서 한 세션에서 UPDATE 작업을 하는 중에는 

  다른 클라이언트는 그 테이블을 업데이트하지 못하고 기다려야 하는 상황이 발생할 것이다. 

 

- 이 테이블에 인덱스가 하나도 없다면 어떻게 될까?

  이러한 경우에는 테이블을 풀 스캔하면서 UPDATE 작업을 하는데,

  이 과정에서 테이블에 있는 30여만 건의 모든 레코드를 잠그게 된다. 

  이것이 MySQL의 방식이며, MySQL의 InnoDB 인덱스 설계가 중요한 이유 또한 이것이다.