인덱스와 잠금

InnoDB의 잠금은 레코드를 잠그는 방식이 아니라 인덱스를 잠그는 방식을 사용한다. 변경해야 할 레코드를 찾기 위해 검색한 조건에 만족하는 인덱스의 레코드를 모두 잠그는 방식이다.

예제를 통해 알아보자~!

​

show index from employees;

Table	Key_name	Column_name	Cardinality	Index_type	Visible
employees	PRIMARY	emp_no	299645	BTREE	YES
employees	ix_firstname	first_name	1266	BTREE	YES
employees	ix_hiredate	hire_date	4943	BTREE	YES

 

select count(*) cnt from employees
union all
select count(*) cnt from employees where first_name = 'Georgi'
union all 
select count(*) cnt from employees where first_name = 'Georgi' and last_name='Klassen'
;

CNT

300,024

253

1

 

mysql> Update emloyees SET hire_date=NOW()
       where first_name ='Georgi' and last_name = 'Klassen';

​

Mysql 인덱스 구조는 Primary Key 값이 PKV를 포함하고 있다.

• Update 문장이 실행되면 1건의 레코드가 변경된다.
• 인덱스는 ix_firstname 를 사용하고 first_name = 'Georgi'를 만족하는 253건의 레코드에 Lock을 걸고  Update되는 레코드는 1건이다.
• Update를 위해서 특정 테이블에 적절한 인덱스가 존재하지 않는다면 각 클라이언트 간의 동시성이 떨어져서 한 세션에서 그 테이블에 Update 작업을 하고 있다면 다른 세션은 Update문을 실행하기 위해 레코드에 잠금이 풀릴 때까지 기다려야 한다.
• 만약 테이블에 인덱스가 하나도 없다면 테이블을 FULL SCAN하면서 Update작업을 해야 하며 테이블의 모든 레코드를 잠그게 된다.
• 이것은 MySQL 만의 방식이어서 MySQL의 InnoDB에서 인덱스 설계가 중요한 이유이다.

​

Primary Key 조회

select * from information_schema.KEY_COLUMN_USAGE where table_name = 'employees';

​

INDEX 조회

show index from employees;
SELECT TABLE_SCHEMA
     , TABLE_NAME
     , COLUMN_NAME
     , ORDINAL_POSITION
     , DATA_TYPE
     , COLUMN_KEY "COLUMN_KEY(PRI,UNI,MUL)"
 FROM information_schema.COLUMNS 
WHERE table_name = 'employees';

TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	COLUMN_KEY(PRI,UNI,MUL)
employees	employees	emp_no	1	PRI
employees	employees	birth_date	2
employees	employees	first_name	3	MUL
employees	employees	last_name	4
employees	employees	gender	5
employees	employees	hire_date	6	MUL

​

INDEX에 대한 통계정보 조회

SELECT * 
  FROM information_schema.STATISTICS s 
 WHERE table_name = 'employees';

​

MySQL 인덱스구조 참고 사이트

https://mysqldba.tistory.com/265

22. InnoDB의 PK와 일반 인덱스 Key 컬럼의 중복시 저장은 어떻게 될까?

 

*참고도서 - RealMySQL