mysql indexing tip

kth 데이터지능팀 성동찬

안녕하세요. 오늘은 MySQL을 사용할 때 지켜야할 사항 몇 가지 정리합니다.

나름 혼자서 정리를 해 본 것들인데, MySQL로 서비스를 준비 중이라면 한 번쯤은 고려를 해봤으면 하는 내용입니다.^^

테이블 설계 시 유의 사항

1. 반드시 Primary Key를 정의하고 최대한 작은 데이터 타입을 선정한다.

• 로그 성 테이블에도 기본적으로 PK 생성을 원칙으로 함
• InnoDB에서 PK는 인덱스와 밀접한 관계를 가지므로 최대한 작은 데이터 타입을 가지도록 유지

2. 테이블 Primary Key는 auto_increment를 사용한다.

• InnoDB에서는 기본 키 순서로 데이터가 저장되므로, Random PK 저장 시 불필요한 DISK I/O가 발생 가능
• InnoDB의 PK는 절대 갱신되지 않도록 유지
  (갱신 시 갱신된 행 이후 데이터를 하나씩 새 위치로 옮겨야 함)

3. 데이터 타입은 최대한 작게 설계한다.

• 시간정보는 MySQL데이터 타입 date/datetime/timestamp 활용
• IP는 INET_ATON(‘IP’), INET_NTOA(int) 함수를 활용
• 정수 타입으로 저장 가능한 문자열 패턴은 최대한 정수 타입으로 저장

4. 테이블 내 모든 필드에 NOT NULL 속성을 추가한다.

• NULL을 유지를 위한 추가 비용 발생
  (NULL 허용 칼럼을 인덱싱 할 때 항목마다 한 바이트 씩 더 소요)

5. Partitioning을 적절하게 고려하여 데이터를 물리적으로 구분한다.

• 데이터 및 인덱스 파일이 커질수록 성능이 저하되므로Partitioning 유도
• PK 존재 시 PK 내부에 반드시 Partitioning 조건이 포함되어야 함

인덱스 설계 시 유의 사항

1. 인덱스 개수를 최소화 한다.

• 현재 인덱스로 Range Scan이 가능한지 여부를 사전에 체크
• 인덱스도 서버 자원을 소모하는 자료구조이므로 성능에 영향을 줌

2. 인덱스 칼럼은 분포도를 고려하여 선정한다.

• 인덱스 칼럼 데이터의 중복이 줄어들수록 인덱스는 최대의 효과를 가짐
• 하단 쿼리 결과 값이 1에 가까울수록(0.9이상 권고) 인덱스 컬럼으로 적합함
  

  1 2	SELECT count(distinct INDEX_COLUMN)/count(*) FROM TABLE;

3. 커버링 인덱스(Covering Index)를 활용한다.

• 쿼리 조건이 인덱스 안에 포함된 경우 인덱스에서만 연산 유도
• 인덱스는 일반적으로 행 전체보다 작으므로 불필요한 Disk I/O 회피 가능
  “MySQL에서 커버링 인덱스로 쿼리 성능을 높여보자!!” 편 참고

4. 스토리지 엔진 별 INDEX 특성을 정확히 인지한다.

• InnoDB에서 데이터는 PK 순서로 저장되고, 인덱스는 PK를 Value로 가짐
• MyISAM은 PK와 일반 인덱스의 구조는 동일하나, Prefix 압축 인덱스를 사용
  (MyISAM 엔진에서 ORDER BY 시 DESC는 가급적 지양)

5. 문자열을 인덱싱 시 Prefix 인덱스 활용한다.

• 긴 문자열 경우 Prefix 인덱스(앞 자리 몇 글자만 인덱싱)를 적용
  

  1	CREATE INDEX IDX01 ON TAB1(COL(4), COL(4))

• Prifix Size는 앞 글자 분포도에 따라 적절하게 설정
  (하단 결과가 1에 가까울 수록 최적의 성능 유지, 0.9이상 권고)
  

  1 2	SELECT count(distinct LEFT(INDEX_COLUMN,3))/count(*) FROM TABLE;

6. CRC32함수 및 Trigger를 활용하여 인덱스 생성한다.

• URL/Email같이 문자 길이기 긴 경우 유용
• INSERT/UPDATE 발생 시 Trigger로 CRC32 함수 실행 결과 값을 인덱싱
• CRC32 결과값을 저장할 칼럼 추가 및 인덱스 생성
  

  1 2	alter table user_tbl add email_crc int unsigned not null; create index idx01_email_crc on user_tbl (email_crc);

• Insert Trigger 생성
  

  1 2 3 4 5 6

  create trigger trg_user_tbl_insert before insert on user_tbl for each row begin set new.email_crc = crc32(lower(trim(new.email))); end$$

• Update Trigger 생성
  

  1 2 3 4 5 6 7 8

  create trigger trg_user_tbl_update before update on user_tbl for each row begin if old.email<> new.email then set new.email_crc = crc32(lower(trim(new.email))); end if; end$$

• 검색 쿼리
  

  1 2 3 4

  select * from user_tbl where email_crc = crc32(lower(trim('mail@domain.com'))) and email= 'mail@domain.com'

  CRC32 결과가 중복되어도, email값을 직접 비교하는 부분에서 중복이 제거됩니다.

7. 중복 인덱스 생성 회피

• MySQL은 동일한 인덱스를 중복 생성해도 에러를 발생하지 않음
• Primary Key로 구성된 칼럼과 동일한 인덱스를 생성하지 않도록 주의

출처 : http://dev.kthcorp.com/2012/05/15/mysql-table-index-design-tip/

최적의 성능을 위한 인덱스의 선택

 webman21님의 블로그 | webman21
 http://blog.naver.com/webman21/17890936

인덱스를 선택하는 것은 많은 SQL Server DBA와 개발자에게 신비로운 대상이다. 물론 우리는 인덱스가 어떤 일을 하며 어떻게 성능을 향상시키는지를 잘 알고 있다. 문제는 이상적인 인덱스 종류와(클러스터드 vs. 넌클러스터드), 인덱스에 포함시킬 컬럼의 개수(다중 컬럼 인덱스가 필요한지 여부), 그리고 어떤 컬럼에 인덱스를 만들 것이냐 하는 점이다.

여기서는 이 질문에 대한 답변을 간략하게 제공하고자 한다. 불행하게도, 각각의 경우에 대한 절대적인 해답은 존재하지 않는다. 다른 성능 튜닝 및 최적화와 마찬가지로 이상적인 인덱스를 찾으려면 직접 테스트하는 수고를 감수해야 한다. 이제 인덱스 생성에 있어서의 보편적인 지침을 먼저 살펴보고 다음에는 클러스터드 인덱스와 넌클러스터드 인덱스를 선택하는 기준에 대해서 자세히 알아보도록 하겠다.


인덱스가 너무 많아서 문제가 될 수도 있는가?

정답은 “그렇다” 이다. 어떤 사람들은 모든 컬럼에 인덱스를 걸면 성능 문제가 모두 해결될 것이라고 생각하기도 하는데 실제로는 전혀 그렇지 않다. 인덱스가 데이터 액세스 속도를 증가시키는 것과 마찬가지로 잘못 선택하면 오히려 액세스 속도를 저하시킬 수도 있다. 인덱스가 많음으로 해서 발생하는 문제는 INSERT, UPDATE, DELETE가 발생할 때 마다 SQL Server가 인덱스를 유지하기 위한 작업을 해야만 한다는 것이다. 하나의 테이블에 한 두개 정도의 인덱스를 관리하는 것은 SQL Server가 처리하기에 별 부담이 없지만 네 개, 다섯 개 혹은 그 이상의 인덱스를 만들게 되면 성능에 큰 장애 요인으로 작용하게 된다. 가능하다면 인덱스를 적게 만드는 것이 좋으며 최적의 성능을 얻기 위해서 적절한 개수의 인덱스를 만드는 것이 요구된다.

단지 인덱스를 추가하는 것이 좋을 것 같다는 생각만으로 무조건 인덱스를 만드는 것은 좋지 않다. 해당 테이블을 이용하는 쿼리에서 사용될 것이 확실한 경우에만 인덱스를 추가해야 한다. 어떤 쿼리가 실행될지 알 수 없다면 확실히 알기 전까지는 아무 인덱스도 만들지 않는 편이 낫다. 어떤 쿼리가 실행될지 추측하고, 거기에 따라서 인덱스를 생성하면 결국 애초의 추측이 틀렸음을 알게 되는 경우가 많기 때문이다. 실행될 쿼리의 유형을 먼저 알아야 하고 그 다음에 가장 적절한 인덱스를 만들기 위한 분석 과정을 거친 후에야 비로소 인덱스를 생성하고 실제로 효과가 있는지 테스트 해야 한다.
OLTP 프로그램은 INSERT, UPDATE, DELETE 작업이 매우 많기 때문에 최적의 인덱스를 선택하기 위한 판단을 내리기가 쉽지 않다. SELECT, UPDATE, DELETE 할 레코드를 신속하게 찾기 위해서는 인덱스가 필요하지만 인덱스가 너무 많으면 INSERT, UPDATE, DELETE 작업이 실행될 때 과도한 오버헤드가 발생하게 된다. 한편, 주로 읽기 작업이 많은 OLAP 프로그램의 경우에는 INSERT, UPDATE, DELETE 작업에 대해서 걱정할 필요가 없기 때문에 필요한 만큼 인덱스를 만들어도 상관 없다. 이와 같이 인덱스 전략을 수립할 때에는 응용프로그램이 어떻게 사용될 것인가가 큰 차이를 가져오게 된다.

인덱스를 선택할 때 고려해야 할 사항을 하나 더 들자면 선택한 인덱스가 SQL Server 쿼리 옵티마이저에 의해서 사용되지 않을 수도 있다는 점이다. 쿼리 옵티마이저가 인덱스를 사용하지 않기로 결정하였다면 인덱스는 SQL Server에게 부담이 될 뿐이기 때문에 아예 삭제하는 편이 낫다. 그렇다면 SQL Server 쿼리 옵티마이저에서 인덱스가 존재함에도 항상 인덱스를 사용하지는 않는 이유는 무엇일까?
여기서 자세하게 답변하기에는 너무 광범위한 내용이지만 한마디로 요약하자면 SQL Server가 인덱스를 사용하는 것 보다 테이블 검색을 수행하는 편이 더 빠르다고 판단하는 경우가 있기 때문이라고 할 수 있다. 여기에는 두 가지 경우가 있는데 하나는 테이블의 크기가 작은 경우이고(레코드 수가 적음) 또 다른 하나는 인덱스가 걸린 컬럼의 유일한 값이 95%보다 적기 때문이다. SQL Server가 인덱스를 사용하지 않을지를 어떻게 알 수 있을까? 이에 대한 답변은 SQL Server 쿼리 애널라이저를 사용하는 방법을 살펴본 다음에 알아보기로 하겠다.

클러스터드 인덱스를 선택하는 방법
하나의 테이블에는 하나의 클러스터드 인덱스만을 만들 수 있기 때문에 어떻게 사용할 것인가를 신중하게 생각해야 한다. 실행할 쿼리의 유형을 고려해 보고 어떤 쿼리가 가장 중요하며 클러스터드 인덱스를 사용했을 때 도움이 될지를 추정해 보도록 한다.

일반적으로는 클러스터드 인덱스를 만들 컬럼을 선택할 때에는 다음과 같은 기준을 사용한다.

• 테이블의 프라이머리 키를 항상 클러스터드 인덱스로 만들어서는 안 된다. 프라이머리 키를 만든 다음에 특별히 지정을 하지 않으면 SQL Server는 자동으로 프라이머리 키를 클러스터드 인덱스로 만든다. 프라이머리 키가 다음과 같은 조건에 부합될 경우에만 클러스터드 인덱스로 만들도록 한다.
• 클러스터드 인덱스는 일정한 범위의 값에 대해서 쿼리를 실행하거나 정렬된 결과를 필요로 할 ‘때 가장 좋은 방법이다. 왜냐하면 데이터가 이미 인덱스 내에서 정렬되어 있기 때문이다. 예를 들면 쿼리에서 BETWEEN, <, >, GROUP BY, ORDER BY, 그리고 MAX, MIN, COUNT와 같은 집합 함수를 사용하는 경우가 여기에 해당된다.
• 클러스터드 인덱스는 유일한 값을 가지고 레코드를 검색하는 쿼리를 사용하거나(예를 들면 직원 번호) 레코드의 거의 모든 데이터를 가져오고자 할 때 효과적이다. 왜냐하면 이 쿼리는 인덱스만으로 처리가 가능하기 때문이다.
• 클러스터드 인덱스는 국가 또는 주(state) 데이터와 같이 유일한 값이 제한적인 컬럼을 액세스 하는 쿼리에 효과적이다. 그러나 컬럼의 데이터가 예/아니오, 남성/여성 처럼 매우 적은 유일한 값을 갖고 있으면 이 컬럼에는 인덱스를 걸지 않는 것이 좋다.
• 클러스터드 인덱스는 JOIN 또는 GROUP BY 절을 사용하는 쿼리에 효과적이다.
• 클러스터드 인덱스는 매우 많은 레코드를 반환하는 쿼리를 사용할 때 효과적이다. 왜냐하면 데이터가 인덱스에 모두 들어 있으므로 다른 곳에서 찾아올 필요가 없기 때문이다.
• 테이블에 INSERT 작업이 많다면 아이디, 날짜 등과 같이 증가하는 컬럼에는 클러스터드 인덱스를 만들지 않아야 한다.왜냐하면 클러스터드 인덱스는 데이터를 물리적으로 정렬되도록 유지하기 때문에 증가하는 컬럼에 생성된 클러스터드 인덱스는 새로운 데이터를 테이블의 동일한 페이지에 삽입하여 테이블이 과도하게 사용됨으로써 I/O 병목현상을 유발할 수 있다. 이 때에는 클러스터드 인덱스로 사용할 다른 컬럼을 찾아보는 것이 좋다.

한 가지 난처한 문제는 클러스터드 인덱스를 만들 필요가 있는 컬럼이 하나 이상인 경우가 있다는 것이다. 그러나 하나의 테이블에는 오직 하나의 클러스터드 인덱스만을 생성할 수 있기 때문에 모든 가능성을 평가한 다음에 가장 효과적일 것으로 판단되는 하나만을 선택해야 한다.


넌클러스터드 인덱스를 선택하는 방법

넌클러스터드 인덱스는 테이블에 원하는 만큼 생성할 수 있기 때문에 클러스터드 인덱스를 선택하는 것보다 비교적 쉽다. 다음은 넌클러스터드 인덱스를 생성할 컬럼을 선택하는 방법이다.

• 넌클러스터드 인덱스는 반환되는 레코드의 개수가 적은 쿼리(하나의 레코드 포함)와 인덱스의 선택도가 높은 경우에(95% 이상) 사용하면 좋다.
• 컬럼의 데이터가 95% 이상의 유일한 값을 갖지 않는다면 SQL Server 쿼리 옵티마이저는 해당 컬럼에 생성되어 있는 넌클러스터드 인덱스를 사용하지 않을 가능성이 높다. 따라서 적어도 95% 이상 유일한 값을 갖지 않는 컬럼에는 넌클러스터드 인덱스를 만들지 않는 것이 좋다. 예를 들어 “예”, “아니오”를 데이터로 갖는 컬럼은 95% 이상 유일해야 한다는 조건에 부합되지 않는다.
• 인덱스의 폭(width)을 가능한 좁게 유지한다. 특히 복합(다중 컬럼) 인덱스를 만들 때는 더욱 그러하다. 이렇게 하면 인덱스의 크기가 감소하기 때문에 인덱스를 읽을 때 필요한 읽기 횟수도 줄어들므로 성능 향상을 얻을 수 있다.
• 가능하다면 문자보다는 정수 값을 가진 컬럼에 인덱스를 만들어야 한다. 정수 값은 문자보다 처리하는데 필요한 오버헤드가 작다.
• 응용프로그램이 같은 테이블에 대해서 동일한 쿼리를 반복해서 실행한다면 해당 테이블에 커버링 인덱스를 만들어 보도록 한다. 커버링 인덱스는 쿼리에서 사용하는 모든 컬럼을 포괄하는 인덱스이다. 인덱스에 찾고자 하는 데이터가 모두 들어 있기 때문에 SQL Server는 데이터를 찾기 위해서 테이블을 참조할 필요가 없으며 결과적으로 논리적 및 물리적 I/O가 감소하는 장점이 있다. 그러나 인덱스가 과도하게 커지면(컬럼이 너무 많아서) I/O가 증가하여 성능이 떨어질 수도 있다.
• 인덱스는 쿼리의 WHERE 절이 인덱스의 가장 왼쪽의 컬럼과 일치할 때에만 사용된다. 따라서 “City, State”와 같은 복합 인덱스를 만들었다면 WHERE City=’Houston’과 같은 쿼리는 인덱스를 사용하겠지만 WHERE STATE=’TX’는 인덱스를 사용하지 않는다.

일반적으로 테이블에 하나의 인덱스만 필요하다면 클러스터드 인덱스로 만드는 것이 좋다. 그러나 테이블에 하나 이상의 인덱스가 필요한 경우에는 넌클러스터드 인덱스를 만들 수 밖에 없다. 위의 내용을 충실하게 따른다면 최적의 인덱스를 선택할 수 있을 것이다.

[출처] [본문스크랩] 최적의 성능을 위한 인덱스의 선택 |작성자 Junyoungs