MySQL 운영 노하우(Know-how) I

(읽기 전 주의)

이 글은 개인의 경험에 의해 작성된 것이므로, 100% 정답이 아니며, 더 다양한 방법, 더 나은 방법이 존재할 수 있습니다. 또한, 자세한 설명보다는 왜 운영 노하우로 선정했는지, 흔히 하는 실수가 무엇인지, 내가 놓쳤던 것들은 무엇인지에 대한 이해를 돕기 위해, 비교적 간단하게 경험을 바탕으로 설명하고 있습니다 (혹은, 설명하려고 노력하고 있습니다).

 

 

이 글은 계속해서 운영하면서 실수하거나, 동료들이 쉽게 놓치거나 하는 부분을 계속해서 추가해 나갈 것입니다.

또한, 좋은 팁들이 있다면 댓글에 공유 부탁드립니다!

1. EXPLAIN 커맨드를 사용하라!

select 절 안에 explain 절을 붙이면, MYSQL에서 해당 query를 어떻게 실행할 것인지 실행 계획이 나온다. 실제로 query를 실행하지 않고, 어떤 방식으로 결과를 받아올지에 대한 쿼리 실행 전략에 대해서만 나온다.

EXPLAIN SELECT * FROM itseminars WHERE writer='김하나';

 

쿼리 실행은 MySQL 엔진에서 알아서 잘 처리하겠지? 라고 생각한다면 오산!

만약 개발자가 MySQL의 신이라, 스키마를 "완벽하게" 작성하였고, 쿼리를 "완벽하게" 작성한다면 EXPLAIN이라는 건 필요가 없을 것이다. 하지만 불완전한 스키마 구성과 비즈니스 로직의 필요에 의해 이것저것 덧붙여진 쿼리들은 MySQL에 최적화된 쿼리와는 거리가 조금씩 멀어진다. 이 부분을 감지/수정/보완하기 위해 EXPLAIN 쿼리(Query)를 사용한다.

 

불완전한 스키마 구성과 비즈니스 로직의 필요에 의해 이것저것 덧붙여진 쿼리들은 MySQL에 최적화된
쿼리와는 거리가 조금씩 멀어진다. 이 부분을 감지/수정/보완하기 위해 EXPLAIN 쿼리(Query)를 사용한다.

 

MySQL을 제대로 사용하고 싶다면, 꼭 MySQL EXPLAIN 문을 어떻게 사용하는지 확인해 보아라! - 영어가 되신다면, 물론 원문을 읽어보는게 최고 !

MySQL :: MySQL 8.0 Reference Manual :: 13.8.2 EXPLAIN Statement

(이 내용은 다음에 시간이 된다면 내 블로그에도 정리를 하겠으나(나를 위해서), 이미 구글에 검색만 해 봐도 정말 좋은 글들이 많다)

 

 

 

2. INDEX를 이해하라!

검색할 때, INDEX만 설정해두면 조회가 빠르게 되는 거아냐? 그니까 많으면 좋은 거 아냐?

물론, 아니다.

인덱스라는 것은 "조회"를 하기 위해 최적화를 "미리" 실행해 놓는 것으로, INSERT, DELETE, UPDATE를 할 때의 성능이 감소하며, 인덱스 저장을 위해 저장공간 또한 더 사용하게 된다. INSERT / DELETE / UPDATE 가 많은 테이블이라면, INDEX를 추가할 때, 정말 필요한 필드만 INDEX 설정을 해두어야 한다.

 

인덱스라는 것은 "조회"를 하기 위해 최적화를 "미리" 실행해 놓는 것

그렇다면, INDEX 필드를 설정할 때, 무엇을 기준으로 하는 게 좋을까?
절대 실생활에서는 이렇게 사용하진 않겠지만, 극단적인 예를 들어 설명을 해보도록 하겠다.
회원 정보를 보관하고 있는 다음과 같은 테이블이 있다. 

PK	이름	주민등록번호	필수 동의 여부(bool)	마케팅 동의 (bool)
1	김하나	950101-1212121	1	1
2	김두리	950102-1212122	1	0
3	김세미	950103-1212123	1	1
4	김사미	950104-1212124	1	0
5	김오미	950105-1212125	1	1

(필수 동의 여부는 무조건 1 - 회원 가입하려면 동의해야 하니까)

INDEX 조회에 영향을 주는 것은 Cardinality라고 하는 개념인데, 중복도를 표시한다고 생각하면 된다. 결론부터 말하자면, 중복이 높은 경우(Cardinality가 낮음)에는 INDEX를 사용해도 큰 의미가 없고 저장 공간만 차지하고, 데이터 수정/추가/삭제에 영향만 미친다.

 

중복이 높은 경우(Cardinality가 낮음)에는 INDEX를 사용해도 큰 의미가 없고 저장 공간만 차지하고,
데이터 수정/추가/삭제에 영향만 미친다.

만약 `SELECT * FROM users WHERE 필수 동의 여부 = 1`의 쿼리를 실행한다고 가정해 보자. 중복이 매우 높기 때문에, INDEX를 하는 데 큰 의미가 없고, 오히려 불필요한 INDEX 추가로 인해 INSERT, DELETE, UPDATE를 실행할 때, 속도 감소로 이어질 것이다.
그리고 위의 예와 같이 필수 동의 여부, 마케팅 동의와 같이 중복이 높다면, Cardinality가 낮다고 얘기하고, 잘못된 INDEX를 사용했다 할 수 있다.

반대로 PK, 주민등록번호 등과 같이 중복이 낮다면, Cardinality가 높은 것이고, INDEX를 적절히 잘 활용했다 할 수 있다.

불행 중 다행(?)으로, MySQL 실행 계획을 보면,
예: EXPLAIN SELECT * FROM users WHERE 필수 동의 여부 = 1 AND 주민등록번호="950101-1212121"
Cardinality가 높은 순으로 실행계획을 잡기 때문에, 조회할 때는 크게 영향이 없다.

참고로 Cardinality를 확인할 수 있는 방법으로는 "SHOW INDEX FROM table_name"을 사용하면 된다.

이 이외에도 여러 주의사항이 있는데, 외래키 사용 여부, PK 사용 여부, INDEX 된 곳에 함수를 사용하여 검색하는 경우 등, 모르고 사용하면 문제 되는 요소들이 정말 많다.

 

 

 

 

3. Query Caching 동작 방식을 이해하라!

(정말 자주 하는 실수이고, 나도 잘 못 알았던 부분이 있었는데, Query Caching 크기를 늘리면 모든 게 해결될 줄 알았으며, Query Caching이 Query Statement를 캐싱하는 줄 알았다.)

캐싱 노하우는 정말 많지만, 그 중에서도 흔히 잘못알고 있는 부분에 대해서 얘기를 하자면, 바로 "Query Caching"은 "결과가 Caching" 되는 것이며, 또한 이 결과가 변하지 않게 캐싱을 계속 유지할 수 있도록 설계하는게 중요하다.

 

그렇다면 어떻게? 왜? 언제 생기나?

 

설계를 잘못했을 때, 프로젝트(애플리케이션)를 사용을 제대로 이해하지 못할 때, 개발시간이 충분하지 않을 때, 혹은 짧고 잦은 주기의 업데이트로 인해 종종 생기는 이슈인 것 같다.

쿼리가 캐싱이 될 때는, "조회"가 실행될 테이블의 데이터 중 하나라도 변경이 되면, 그 테이블에 연결된 쿼리 캐시는 무효가 된다. 경험이 많이 없었을 때는 (잘못된 정보로 알고 있었음, 물론 문서를 읽어보지 않은 나의 잘못!), Query Caching이라고 해서, 

SELECT * FROM tables

쿼리문 자체가 캐싱이 되는 줄 알았다. 하지만 해당 쿼리의 "결과"가 캐싱이 되는 것. 그러니 당연히, 테이블의 어떤 필드가 하나라도 바뀌면, 무효화가 일어나는 것이다.

 

쿼리가 캐싱이 될 때는, "조회"가 실행될 테이블의 데이터 중 하나라도 변경이 되면, 
그 테이블에 연결된 쿼리 캐시는 무효가 된다.

이 걸 방지하기 위해서는 "변화가 있는 칼럼"은 쪼게는 게 좋다. 

(실제 개발할 때는, 빨리-빨리해야 할 때, "기술 부채"로 남겨둘 때가 가끔 있다)

 

예) 아래와 같이 "게시글" 테이블이 있다.

PK	제목	작성자	공개여부	좋아요 수
1	가나다라마바사	김하나	1	100
2	아자차카타파하	김두리	1	105

"'좋아요 수"는 사람들이 좋아요 액션을 할 때마다, +1, -1 된다. 그럼 캐싱하여 "조회"를 하더라도, 좋아요 액션이 있을 때마다, 무효화 처리가 되게 된다.

그래서 이 경우에는,

"좋아요 수"를 쪼게서 "게시글 좋아요 수" 라는 테이블을 만들어 따로 분리해 주는 게 좋다. (Row Splitting이라고도 얘기함)

SELEC * FROM 게시글 WHERE PK=1

작성자가 제목을 수정하기 전에는 해당 쿼리는 계속해서 캐싱이 되어있을 것! (그리고 실제로 저런 것은 쪼게야 Deadlock 현상을 방지할 수도 있다)