프로젝트를 하면서 Database 설계를 하다보니 다양한 쿼리를 작성하게 되었다. 그러다보니 쿼리 성능을 자연스럽게 생각해야 했다. SQL 명령어를 통해 쿼리 성능을 간접적으로 볼 수 있지만 좀 더 간단하게 볼 수 있는 웹 툴을 만들어보기로 결정했다. 또 생성형 ai를 사용해서 더욱 최적화된 쿼리를 찾을 수 있는 기능도 넣어보았다. 이 툴은 MySQL을 기준으로 만들어졌다.
쿼리 성능에 있어서 가장 먼저 생각해야 하는 것은 실행 시간이었다. 쿼리 실행시간이 길어질 수록 성능이 떨어지기 때문이다.
// 쿼리 실행 시간 기록
const start = Date.now();
await conn.query(query);
const end = Date.now();
const elapsed = end - start;간단한 코드이다. 지금 시간을 쿼리를 실행시킨 후 시간을 가져와서 차이를 구해주면 쿼리 실행 시간을 계산할 수 있다. 그럼 의문점이 하나 생긴다.
실행 시간만으로 쿼리의 성능을 측정할 수 있는가?
그건 아니다.
MySQL의 Server Status Variables 중 몇 개를 살펴보겠다. MySQL 공식 문서를 참고하였다.
1. Handler_read_key
Key order에 따라 다음 row를 읽을 의 요청의 수이다. 제약조건과 함께 인덱스 column을 쿼리 실행하거나 index scan을 한다면 증가한다. 간단히 이야기 하자면 인덱스를 통해 특정 record를 읽은 수로 인덱스 없이 table full scan을 한다면 이 값이 0에 가까울 것이다. 따라서 이 값이 클 수록 인덱스를 잘 활용했다는 뜻이니까 좋은 쿼리일 확률이 크다! 이 것은 실행 결과 기반이다.
2. Last_query_cost
MySQL에서 쿼리를 실행하기 전에 쿼리 옵티마이저가 계산한 예상 비용이다. 절대적인 실행 시간이 아니고 실행 전에 쿼리가 얼마나 복잡하거나 리소스를 사용하는 지 확인한다. 이 값이 작을 수록 쿼리가 단순하고 인덱스 활용도가 높으므로 더 좋은 쿼리일 가능성이 크다.
따라서 두 값을 같이 보면 실행 전 예상 비용과 실행 중 실제 인덱스 활용을 비교 가능하다.
const [handlerReadKey] = await conn.query("SHOW SESSION STATUS LIKE 'Handler_read_key'");
const [queryCost] = await conn.query("SHOW STATUS LIKE 'Last_query_cost'");따라서 두 개의 값을 받아오고 json 형식으로 넘겨주었다.
또한, 쿼리 실행 계획을 볼 수 있는 부분을 추가하였다.
EXPLAIN 명령어를 활용하여 유저가 시각화된 테이블을 쉽게 확인
아래와 같이 쿼리 실행 계획을 테이블로 나타내었고
html += '<h4> 해석 코멘트</h4><ul>';
rows.forEach(row => {
// 코멘트를 통해 사용자가 더 나은 방향으로 쿼리를 개선할 수 있도록
if (row.type === 'ALL') {
html += `<li><strong>${row.table}</strong>: Full Table Scan 발생 - 인덱스 고려 필요</li>`;
} else if (row.type === 'index') {
html += `<li><strong>${row.table}</strong>: Index Scan 사용됨</li>`;
} else if (row.type === 'eq_ref' || row.type === 'ref') {
html += `<li><strong>${row.table}</strong>: 효율적인 Key-based Join 사용</li>`;
}
});row type에 따라 comment를 설정하였다.
마지막으로 생성형 AI를 통한 쿼리 최적화 기능을 추가하였다.
https://aistudio.google.com/apikey
위의 웹사이트를 사용하여 Google Gemini를 연결하였다.
Ai api를 사용할 때 가장 중요한 것은 프롬프팅이다.
다음 MySQL 쿼리를 성능 최적화해주세요. 쿼리의 내용 자체는 분석하지 않습니다. 간단하게 고려할 사항을 4줄 정도로 작성해
주세요. 그리고 같은 기능을 수행하되 성능이 뛰어날 것으로 예측되는 쿼리를 알려주세요. 또 최적의 index 추천도 부탁해요. \n쿼리: ${query}
아래 프롬프트를 이용하면 다음과 같은 결과가 나온다.
자 이제 유저가 쉽게 쿼리 실행계획 및 성능을 체크하고, ai를 참고하여 쿼리 최적화를 하는 툴이 만들어졌다.
마지막으로 프론트엔드 디자인 작업을 해주면 기본 화면이 다음과 같이 완성된다.
기본적으로 유저는 본인의 .env 파일에 정보를 입력하여 자신의 로컬 디비와 연결하여 쓰는 것이지만 테스트를 위해 Example query를 랜덤 생성하여 확인해볼 수 있다. 보다 자세한 실행 방법은 아래 첨부된 깃허브 README.md를 참고하면 된다.
