[웹 프로그래머를 위한 데이터베이스를 지탱하는 기술] 4장: SQL 문의 특징과 이를 잘 다루는 법

본문은 [웹 프로그래머를 위한 데이터베이스를 지탱하는 기술]를 읽고 간단하게 정리한 글입니다. 필요에 따라 생략/수정된 부분이 있을 수 있으며, 내용이 추후 변경될 수 있습니다.

 

1. SQL문의 실행 효율 의식하기

SELECT문, UPDATE문, DELETE문은 많은 레코드 중에서 조건에 맞는 것을 찾아오는 처리이므로 검색 처리를 고속으로 끝내는 것이 매우 중요하다. 

 

1) 적절한 인덱스가 사용되고 있는지 확인

• 고속의 SQL문을 작성하는 데 있어서 중요한 것이 여럿 있지만, 가장 중요한 것은 적절한 인덱스를 사용하는 것이다
• 인덱스를 전혀 사용할 수 없는 경우는 테이블 전체의 레코드를 검색해야 한다
  • 테이블이 거대한 경우 이 작업은 매우 무거워지며, 다른 작업에도 영향을 미쳐 서비스 다운을 초래할 수 있다
• 인덱스가 적절히 사용되는지에 대한 여부는 SQL문 자체 외에도 테이블 정의에 따라 달라진다
  • 테이블에 인덱스가 전혀 없으면 전체 테이블 검색이 된다
  • 따라서 DML 사용 시 SQL문뿐만 아니라 테이블 정의도 함께 보아야 한다

EXPLAIN

• 많은 데이터베이스 제품에서는 이러한 SQL문의 실행 방식이 적절한지 여부를 실제로 실행하지 않고 판정만 해주는 기능을 가지고 있다
  • 이러한 기능을 EXPLAIN이라고 한다
• 그림 4-6은 MySQL에서 EXPLAIN 사용 예를 나타낸 것이다
  • 여기서 특별히 주목할 점은 표시 순서와 table, type, key, ref,rows이다

그림 4-6

• 특히 중요한 것은 rows인데, 대략적으로 말하자면 대부분의 경우에 rows의 값이 실제로 데이터베이스 레코드에 액세스되는 횟수가 된다(이것은 추정치로, 어디까지나 기준 값이다)
  • 이번 경우는 등장하는 모든 테이블에서 rows가 1이므로 각 테이블 모두 1 레코드밖에 엑세스하지 않는 것을 알 수 있다
  • 이것이 가장 액세스 효율이 좋은 패턴으로, 조인을 이러한 형태로 할 수 있으면 매우 고속이 된다
  • 반대로 인덱스를 제대로 사용하지 않는 경우는 전체 테이블 검색을 할 수밖에 없게 되어 rows 값이 커지고 시간이 오래 걸린다
• 쿼리를 실행하기 전에 EXPLAIN을 처리해 봄으로써 나쁜 쿼리를 사전에 찾아낼 수 있다

쿼리 분석도구

• 다양한 쿼리를 실행하는 애플리케이션을 운용해 나가다 보면 도대체 어느 쿼리가 느린지 파악하기 어렵게 된다
  • 따라서 EXPLAIN을 실행하기 전에 먼저 어떤 쿼리가 수상한지 찾을 필요가 있다
• 이러한 분석 지원계의 기능도 데이터베이스 제품에 포함되어 있으며, 널리 보급된 제품이라면 직접적인 커맨드 없이도 오픈 소스 지원 도구 등을 사용하여 쿼리를 분석할 수 있다
  • 최근엔 많은 모니터링 툴에서 쿼리 속도를 분석해주고 있다

 

2) 관리계 명령

• 시스템을 오랫동안 운용하면 데이터베이스의 안정성과 성능이 이상하게 되는 경우가 발생한다
• 이럴 때에 테이블이 아니라 데이터베이스의 내부 구조까지 깊게 파악하고 싶을 수 있다
• 예를 들어 다음과 같은 정보를 구하고 싶을 수 있다
  • 1) 초당 몇 차례의 SELECT/INSERT/UPDATE/DELETE를 실행하고 있는가?
  • 2) 초당 몇 개의 레코드를 읽고 있는가?
  • 3) 초당 몇 차례의 무거운 쿼리가 실행되고 있는가?
• 상당수의 데이터베이스 제품에서는 이러한 조작 또한 SQL문으로 사용할 수 있도록 되어 있다

 

 

2. SQL 장점과 단점

프로그래밍 언어에서 데이터 구조를 조작하는 경우에 비해 SQL문을 사용하여 데이터베이스 액세스하는 작성법은 다양한 장점과 단점이 있다. 여기에서는 조작성과 기능면의 측면에서 설명하고자 한다.

 

1) SQL은 기술 습득이 용이하다

• 프로그래밍 언어를 사용하여 데이터베이스를 조작하는 것은 상당히 어렵다
  • 프로그래밍 언어에 대한 이해와 더불어 데이터베이스 조작을 위한 라이브러리에 대한 이해도 필요하기 때문이다
• SQL문은 그에 비해 사용법이 간단하고 학습이 쉽다
  • SQL문의 조작은 터미널에 접속하면 이후에는 SELECT문만 실행하면 된다
  • SELECT문, FROM절, WHERE절 등 SQL문을 이루는 구성요소들은 영문 그대로 의미가 통하므로 알기 쉽다
• 나쁜 SQL문을 작성하면 성능이 안 좋은 문제도 있지만, 이는 프로그래밍 언어를 사용하여 데이터베이스를 조작하는 경우도 동일하다
  • 대규모 프로젝트 등에서는 SQL문을 모두 전용 설정 파일에서 일괄 관리해 두어 튜닝 시 그 부분을 철저히 검토한다는 방침을 취하고 있는 경우도 있다

하지만 SQL도 심오하다

• SQL은 습득이 용이하다고 했지만, 그것은 일단 사용해 보는 수준에 도달하는 데까지의 이야기다
• 대량의 데이터를 취급하는 데이터베이스에서는 쿼리 성능에 관한 문제가 발생한다
• 이러한 문제를 이해하기 위해선 데이텅베이스 아키텍처와 인덱스 구조, 디스크와 메모리의 속도 차이 등 다양한 관점에서의 이해가 필요하다

 

2) 기능면

• SQL문은 편리한 언어이지만 비즈니스 로직을 SQL문으로 기술하는 것은 어렵다
  • 그러나 데이터베이스가 제공하는 저장 프로시저(Stored Procedure)를 이용하면 다소 복잡한 로직을 구현할 수 있다
• 저장 프로시저는 데이터베이스 서버에서 동작하므로 수십 번 실행해야 하는 SQL문 대신 SQL문을 모아둔 저장 프로시저를 한 번만 호출하는 사용법을 이용하면 네트워크 액세스 빈도를 크게 줄일 수 있다
  • 이것은 성능을 크게 높이는 효과가 있으며, 네트워크 속도가 느린 과거에는 애용되기도 했다
• 그러나 최근에는 저장 프로시저를 사용하는 일이 적고, 비즈니스 로직은 애플리케이션 계층에서 처리하고 데이터베이스는 데이터의 출입만을 취급하는 분담 체제가 주류를 이루고 있다
  • 로직은 프로그래밍 언어, 데이터 조작은 SQL에 분담시키는 큰 이유는 프로그래밍 언어 쪽이 SQL 및 저장 프로시저보다 개발환경이 압도적으로 풍부하여 생산성 측면에서 강점이 있기 때문이다
  • 또한 데이터가 없는 웹 서버 쪽이 데이터가 있는 데이터베이스 서버보다 쉽게 증가하므로 기동력이 높은 것도 하나의 이유이다
• 다만 최근에도 저장 프로시저가 사용되는 일이 있다(참고로 여기서 최근이라는 말은 10년 전이 기준이다)
  • 대규모 웹 서비스 등에서 특정하게 한정된 로직이 매우 높은 빈도로 불리는 경향이 있다
  • 그러한 특수한 처리만을 고속의 저장 프로시저로 최적화하기도 한다