[MySQL] "아는 만큼 빨라진다" 마이SQL 성능 튜닝 팁 10가지  

마이SQL은 세계에서 가장 광범위하게 사용되는 오픈소스 데이터베이스이며, 전체 데이터베이스의 인기 순위에서는 (근소한 차이로) 2위에 올라 있다. 마이SQL은 효과적인 관계형 데이터베이스 관리 시스템으로 오랫동안 인기 있는 애플리케이션의 중심에 위치해왔다. 그러나 사용하기가 까다로울 수 있고 성능을 개선할 수 있는 여지도 많다. 지난 몇 년 동안 몇 가지 중요한 개발도 이뤄졌다. 여기서는 바론 슈와르츠가 쓴 마이SQL 성능 튜닝 팁의 업데이트 내용을 다룬다. 
 

마이SQL 성능 팁 1. 스키마 설계는 다른 어떤 마이SQL 설정 못지않게 중요 

스키마 설계는 데이터베이스에 해야 하는 매우 중요한 일 중 하나다. 교차 관계형 데이터베이스 기술 원칙은 1970년대에 표준형이 나왔다. 마이SQL은 버전 5.6에서 기본 스토리지 엔진을 이노DB(InnoDB)로 전환했으므로 스키마 설계는 더욱 중요해졌다. 

이유가 무엇일까? 이노DB에서는 모든 것이 기본 키(primary key)다. 이는 이노DB가 데이터를 정리하는 방식과 관련된다. 이노DB에서 기본 키는 군집화되고 모든 보조 키(secondary key)는 기본 키에 엔트리 포인터를 추가한다. 스키마 설계에서 이 부분을 감안하지 않으면 성능 측면에서 불이익을 받게 된다. 데이터는 B-트리 인덱스를 사용해서 저장되므로 데이터를 순서가 정해진 방식으로(즉, 유사 순차 값) 삽입하면 기본 키의 단편화가 방지되고 따라서 리프 노드를 찾는 데 필요한 I/O 작업이 감소한다. 

순차 기본 키가 맞지 않는 경우도 있다. 대표적인 예가 범용 고유 식별자(Universally Unique IDentifier), 줄여서 UUID다. UUID와 기본 키에 대한 더 심층적인 내용은 여기서 볼 수 있다. 대부분 경우에는 순차 기본 키를 사용하는 것이 좋다. 
 

마이SQL 성능 팁 2. 보조 키를 적대시하지 말 것 

보조 키는 백그라운드 프로세스에 의해 업데이트되며 성능 영향은 흔히 생각하는 것만큼 크지 않다. 대신 보조 키를 추가하면 스토리지 요구사항이 증가하므로 디스크 사용량 측면의 문제가 있다. 인덱스가 없는 필드에 대한 필터링은 쿼리가 실행될 때마다 전체 테이블 스캔을 유발할 수 있다. 물론 이 경우 막대한 성능 영향이 발생한다. 따라서 보조 키는 없는 것보다는 있는 편이 낫다. 

단, 데이터베이스의 과도한 인덱싱은 피해야 한다. 많은 인덱스를 실행해도 원하는 성능 개선을 얻지 못할 수 있기 때문이다. 또한 부가적인 인덱스는 스토리지 비용을 높일 수 있고, 이노DB가 인덱스를 최신 상태로 유지하기 위해 많은 백그라운드 작업을 해야 한다. 
 

마이SQL 성능 팁 3. 인덱스에서 행 제공 가능 

이노DB는 인덱스에서 바로 행을 찾아서 제공할 수 있는 반면 보조 키는 기본 키를 가리키고 기본 키에는 행 자체가 포함돼 있다. 또한 이노DB 버퍼 풀이 충분히 크다면 대부분의 데이터를 메모리에 유지할 수 있다. 또한 쿼리에서 개별적인 열별 키보다 대체로 더 효과적인 복합 키를 사용할 수도 있다. 마이SQL은 테이블 액세스당 하나의 인덱스를 사용할 수 있으므로 WHERE x=1 and y=2와 같은 절로 쿼리를 실행하는 경우 x, y에 대한 인덱스를 두는 것이 전체 열에 대한 개별 인덱스를 두는 것보다 낫다. 

또한 x, y에 대한 복합 인덱스는 다음 쿼리의 성능도 개선할 수 있다. 

마이SQL은 커버링 인덱스를 사용하고 이 인덱스에서 메모리에 있는 y를 제공한다. 실무에서 복합 인덱스를 사용할 기회가 있으면 사용해 성능을 개선할 수 있다. 인덱스를 설계할 때는 이 인덱스가 읽히는 방식, 즉 항상 왼쪽에서 오른쪽 방향으로 읽힌다는 점을 염두에 둬야 한다. 다음 쿼리를 보자. 

경축! 아무것도 안하여 에스천사게임즈가 새로운 모습으로 재오픈 하였습니다.
어린이용이며, 설치가 필요없는 브라우저 게임입니다.
https://s1004games.com

여기서 a, b에 대한 인덱스는 쿼리에 도움이 될 것이다. 그러나 다음과 같은 형식을 보자. 

인덱스는 쓸모가 없고 전체 테이블 스캔을 유발한다. 항상 왼쪽부터 인덱스를 읽는다는 개념은 일부 다른 사례에도 적용된다. 예를 들어 다음 쿼리를 보자. 

여기에는 열 b에 의한 WHERE 절 필터링이 없으므로 a, b, c에 대한 인덱스는 첫 번째 열만 읽게 된다. 따라서 이 경우 마이SQL은 인덱스를 부분적으로 읽을 수 있고, 이게 전체 테이블 스캔보다는 낫지만 최선의 쿼리 성능을 얻는 측면에서는 여전히 부족하다. 

쿼리 설계와 관련된 또 다른 요소는 마이SQL에서 사용되는 일반적인 최적화인 왼쪽 끝 인덱스 접근이다. 예를 들어 a, b, c의 인덱스는 select a,c where c=x와 같은 쿼리에서는 작용하지 않는다. 이 쿼리는 인덱스의 첫 번째 부분, 즉 a, b를 건너뛸 수 없기 때문이다. select c,count(c) where a=x group by c와 같은 쿼리도 마찬가지다. b의 인덱스를 건너뛸 수 없으므로 group by에 대해 a, b, c의 인덱스를 사용할 수 없다. 그러나 select c,count(c) where a=x and b=y group by c와 같은 쿼리가 있고 이 쿼리가 a, b에 대해 필터링을 하고 c에 대해 group by를 수행한다면 a, b, c의 인덱스 하나가 필터링과 group by에 모두 도움이 될 수 있다. 
 

원문보기:
https://www.itworld.co.kr/tags/196293/mysql/266739#csidxb0f62748424836c8db741e9db5d0e33