1. 인덱스의 이점
- 테이블 전채 스캔은 속도가 느리고, 비효율적임
- 인덱스 파일은 해당 데이터를 순차적으로 정렬한 데이터 모음
~ MyISAM 테이블 : 데이터 파일과 인덱스 파일이 분리, 한 테이블 내의 모든 인덱스가 하나의 인덱스 파일에 저장됨
~ InnoDB 테이블 : 데이터와 인덱스 저장을 관리하는 하나의 테이블스페이스를 사용함
- 인덱스 사용 요건
~ WHERE 절이나 조인을 수행할 때 일치하는 행을 찾을 때 검색 속도 향상
~ MIN()이나 MAX() 함수를 사용하는 질의의 경우 풀스캔하지 않고 값을 찾음
~ ORDER BY와 GROUP BY절의 정렬 작업과 그룹 작업을 효율적으로 수행할 때 인덱스 사용
2. 인덱스의 단점
- 인덱스는 검색 속도는 빠르나 인덱스 컬럼의 값을 업데이트하는 것 뿐만 아니라 전체 테이블의 입력 및 삭제 속도 저하
- 인덱스는 추가 데이터 공간을 차지함
- InnoDB 테이블은 동일한 파일 안에 데이터와 인덱스 값을 같이 저장하므로 인덱스를 추가하면 해당 파일은 최대 파일 크기에 도달함
3. 인덱스 선택 조건
- 칼럼 카디널리티(칼럼이 갖고 있는 구별된 값의 개수) 고려
~ sql 예제 : SELECT COUNT(*), COUNT(DISTINCT state) FROM member
- 짧은 값들을 인덱스함 (값이 짧을수록 비교도 빨라지고 디스크 I/O도 적어짐)
- 키 값들이 짧으면 키 캐시 안의 인덱스 블록들은 더 많은 키 값들을 저장함
- n개 칼럼의 복합 인덱스를 생성할 때 n개의 인덱스를 생성한다.
~ country, state, city의 복합 인덱스는 가장 왼쪽 행을 접두사라 칭함
=> country / state / city | country / state | country 3개의 인덱스가 존재함
- InnoDB와 MyISAM은 기본적으로 B-tree 사용 : B-tree 인덱스는 <, <=, =, >=, >, <>, !=, BETWEEN 연산자를 사용하는 값 일치 또는 범위 기반의 비교 작업에 굉장히 효율적임
4. 효율적인 질의를 위한 자료형
- 작은 타입으로 충분하다면 더 큰 타입을 사용하지 않음
- 칼럼들을 NOT NULL로 선언함
- ENUM 칼럼 사용을 고려함
- PROCEDURE ANALYSE()를 사용함
~ sql 예제 : SELECT * FROM tbl_name PROCEDURE ANALYSE();
- 테이블의 단편화를 제거함 : mysqldump로 테이블을 덤프한 후 덤프 파일을 이용해 테이블을 삭제 후 재생성함.
- 합성 인덱스를 사용 : BLOB나 TEXT 칼럼에 대해 MD5() 함수를 사용해 해시 값 생성 후 비교 질의로 값을 찾을 수 있음
- 가변 길이의 행에 대해 자주 삭제하거나 업데이트 하는 경우 테이블 단편화가 다 많이 발생하므로 OPTIMIZE TABLE 주기적 실행 필요
5. MySQL의 스케쥴링 / 락 / 동시성
- 스케쥴링 정책
~ 쓰기는 읽기보다 높은 우선순위를 지님
~ 테이블 쓰기는 반드시 하나씩 일어나고, 쓰기 요청들은 도착하는 순서대로 처리
~ 테이블에서 복수의 읽기는 동시에 처리
- InnoDB 테이블은 행 수준에서 락이 이루어짐 ~ 트랜잭션 시 필요한 모든 락을 획득하지 않기 때문에 데드락이 발생할 수 있음
- MyISAM은 테이블 수준에서 락이 이루어짐 ~ 데드락 발생하지 않음
