⭐ 복합 인덱스
두개 이상의 컬럼을 조합하여 생성한 인덱스
① 사용 이유
- 지정한 컬럼을 동시에 검색하기에, 다중 컬럼 검색 속도 개선
- 인덱스는 컬럼 + 주소쌍으로 구성되므로 여러 컬럼을 하나의 인덱스로 사용할 경우, 동일 컬럼의 수만큼 단일 인덱스로 만드는 것에 비해 용량이 축소
② 유의 사항
- 복합 인덱스의 차순위 컬럼부터 단일 인덱스로의 효력이 없기에, 사양에 따라 인덱스의 순서도 고려해야한다. ※ 단 커버링 인덱스인 경우는 예외
- 인덱스의 규모가 클수록 대상 테이블의 변경 처리(생성/수정/삭제)의 속도의 영향을 미치기에, 단일 인덱스와 마찬가지로 불필요한 컬럼 추가는 지양하도록 한다.
🔎 커버링 인덱스
쿼리에 필요한 모든 데이터를 인덱스만으로 커버할 수 있는 인덱스로, 인덱스만으로 처리가 커버되기에 실행계획의 type은 무조건 index가 된다.
※ 실행계획의 type이 index면 무조건 커버링 인덱스?
결론부터 말하자면 "아니다"
type이 index라는 것은, 단순히 인덱스만을 사용해서 데이터를 검색, 정렬 등의 필터링을 하는 것이지, 검색 대상이 되는 컬럼은 고려하지 않는다.
INDEX idx_name_age (name, age)위와 같은 복합 인덱스가 존재한다고 가정할 경우, 아래의 SQL은 결과 컬럼이 다르기에 type이 index는 맞지만 커버링 인덱스라고는 할 수 없다.
SELECT id, name FROM users WHERE age > 30;즉, type이 index인 경우, 검색 대상이 되는 컬럼까지 인덱스 내부의 값을 사용하느냐에 따라서 커버링 인덱스 유무가 나뉘어지게 된다.
⭐ INSERT 최적화
같은 INSERT라도 어떠한 방식으로 하느냐에 따라 부하가 천차만별로 나뉘어진다.
① DB 커넥션 줄이기
Bulk Insert : 하나의 INSERT문에 다량의 값을 넣어서 다중 INSERT처리를 수행
INSERT INTO users (id, name) VALUES (1, '하루1'), (2, '하루2')※ 단, 하나의 SQL로 처리할 수 있는 값은 한계가 있기에 대량의 데이터를 삽입하고자 할 때, 아래의 설정값으로 한계 크기값을 체크하도록 한다.
max_allowed_packet② prepared statement
SQL을 안전하고 효율적으로 실행시키는 방식 중 하나로, 미완성 된(값이 세팅되지 않은) SQL을 사전에 컴파일하고 준비, 추후에 컴파일 된 내용을 기반으로 값을 세팅해서 재사용할 수 있는 인터페이스이다.
$stmt = $conn->prepare("UPDATE users SET name = ? WHERE id = ?");
$stmt->bind_param($name, $id);
foreach ($userInfo as $value) {
$name = $value['name'];
$id = $value['id'];
$stmt->execute();
}위와 같이 첫번째 줄을 통해 값이 세팅되지 않은 SQL을 컴파일 후, 유저 수만큼 반복적으로 SQL을 실행시킬 수 있다. 여기서 SQL 자체의 파싱, 최적화 등의 컴파일 작업은 prepare할 때에만 수행되기에 SQL을 반복 작성 후 실행시키는 것보다 효율이 높다.
⭐ AUTO_INCREMENT LOCK
자동 증가 컬럼을 만들기 위해서는 auto increment라는 기능이 필요하다. 해당 기능을 사용하여 클라이언트가 칼럼의 값을 대입하지 않아도 기존 컬럼의 최신 값 다음의 정수 값을 할당하게 된다. 이 기능은 설정에 따라서 다르게 동작하는데 각각의 설정에 대해서 알아보고자 한다.
innodb_autoinc_lock_mode
innodb_autoinc_lock_mode는 InnoDB에서 자동 증가 값을 관리하기 위한 설정이다. 해당 설정으로 여러 트랜잭션이 동시 삽입 작업을 할 때의 경쟁상태일 경우의 처리 등 auto increment 작업에서의 값 할당에 대한 설정이 가능하다.
// INNODB_AUTOINC_LOCK_MODE_TRADITIONAL
innodb_autoinc_lock_mode = 0주석의 설명 그대로 전통적인 방식으로, 각각의 자동 증가 행마다 잠금을 걸어, 한 번에 하나의 트랜잭션만 자동 증가 값을 사용할 수 있게 한다. 이 설정은 하나의 트랜잭션만 자동 증가 값을 사용하는 것을 보장해주기에, 누락된 자동 증가 값 없이 일관적인 값을 세팅할 수 있다는 장점이 있지만, 여러 트랜잭션에서 동시에 삽입을 시도할 때에 성능 저하가 발생할 수 있다.
// INNODB_AUTOINC_LOCK_MODE_CONCURRENT
innodb_autoinc_lock_mode = 1병행 잠금 모드로, 자동 증가 값이 동시에 여러 트랜잭션에서 생성될 수 있게끔 한다. 성능은 향상되나 중복된 자동 증가 값을 할당하여 값이 충돌할 가능성이 높아진다.
// INNODB_AUTOINC_LOCK_MODE_GROUP
innodb_autoinc_lock_mode = 2