[Java 풀스택 개발자] SQL편: SQL의 작동원리와 기본 문법

 

0. 들어가기에 앞서

React 실전에 대한 글을 쓰려고 했지만 기간이 많이 지나기도 했고 여러 모로 고민되어 이것은 작업일지가 될듯 하다. 그러하여 오늘은 SQL에 대해서 살펴볼 예정이다. 먼저 SQL이 무엇인지, 작동원리가 무엇이고 DB의 구조가 어떻게 되는지, 그리고 간단한 문법에 대해서 다뤄보고자 한다.

사실 SQL이 뭔가, 작동원리가 무엇인가에 대해서는 많이들 인지하고 있지 않다. 모든 코딩 수업이 비슷하듯이, 내부 구조가 어떻게 되는가 등보다 한 줄이라도 문법을 외우는 것이 더 코딩에 도움이 되기 때문이다. 다만 수업에서 설명해주신 것도 있고, 이론을 아는 것이 더욱 도움이 된다고 생각한다.

여태까지의 TIL도 그랬지만, 이번 TIL에서는 강의 내용의 정리 외에도 보충적인 내용을 기술한다.

 

 

1. SQL은 왜 존재하는가?

일단 알아둬야할 것은, SQL은 하나의 언어다. Structured Query Language의 준말로, 구조적 쿼리 언어라고도 한다. SQL은 에스큐엘 혹은 시퀄로 읽는데, 대체로는 에스큐엘이라고 읽는 편이다.

 

그러면 SQL은 무엇을 위한 언어인가? 그건 바로 데이터베이스 관리 시스템, 즉 DBMS(Database Management System)를 다루기 위해서다. 우리가 프로그래밍 언어로 컴퓨터에 명령을 내리듯이, SQL은 데이터베이스에 명령을 내리는 언어라고 보면 된다. 데이터를 꺼내고, 넣고, 수정하고, 지우는 모든 작업을 SQL이라는 언어로 표현하는 것이다.

 

여기서 중요한 건 SQL은 일반적인 프로그래밍 언어(C, Java, Python 등)와는 성격이 조금 다르다는 점이다. 일반 언어는 명령형(어떻게 할지를 지시)이지만, SQL은 선언형(무엇을 원하는지만 말함)이다. 예를 들어 나이가 20살 이상인 사람을 찾는다고 하면, 일반 언어는 반복문을 돌면서 하나하나 확인하는 방식으로 써야 하지만, SQL은 그냥 WHERE age >= 20 이라고만 적으면 된다. 어떻게 찾을지는 데이터베이스가 알아서 한다.

 

데이터베이스란?

데이터베이스는 데이터의 저장소, 혹은 데이터 집합체라고 할 수 있다. 우리가 홈페이지에 로그인을 할 때도 이런 데이터베이스의 도움을 받는다. 데이터베이스의 예시는 여러 가지로 비유할 수 있는데, 대표적으로 우리가 자주 사용하는 엑셀(스프레드시트)도 데이터베이스와 비슷한 역할을 하고 있다고 보면 된다. 현실에 비유하면 출석부나 고객 명단과도 같다. 출석부를 보고 선생님이 학생을 관리하듯이(학생 번호, 이름, 학년 등의 정보-데이터-가 들어 있다) 데이터베이스 또한 웹이나 프로그램에서 데이터를 관리하기 위해 존재한다. 출석부나 고객명단이 특정한 논리성(집단성)을 띄듯이, 데이터베이스 또한 특정한 논리와 연관된 데이터들을 담고 있다.

 

정리된 목록(출석부)으로 실제 집단(학급)을 관리하는 것처럼, 데이터베이스도 현실의 데이터를 표 형태로 관리한다.

 

 

데이터베이스에서 실제로 웹에서 작용하는 예시를 간단히 말해본다. 우리가 네이버나 구글에 로그인 할 때, 우리의 아이디와 비밀번호를 친다. 이 아이디와 비밀번호가 맞는지를 어떻게 대조할까? 분명히 저장해둬야 하는 공간이 있어야할 것이다. 한두 명의 정보 정도야 간단하게 저장하고 꺼내올 수 있겠지만, 그게 수십, 수백 명이 되면 체계적인 관리 방법이 필요할 것이다. 즉, 빼곡한 서류들을 서류함에 정리하듯이, 데이터베이스도 데이터를 체계적으로 보관한다고 할 수 있다. 웹에서는 데이터베이스에 저장한 회원 정보를 불러와 우리가 입력한 아이디와 비밀번호와 매칭한 다음, 맞으면 로그인을 시켜주고 그렇지 않으면 로그인처리를 하지 않는다(경고 메시지를 부른다).

 

이런 데이터베이스를 서비스하기 위한 것이 데이터베이스 시스템(Database System)이다. 이것의 구성요소는 데이터베이스(DB), 데이터베이스 관리 시스템(DBMS), 데이터 언어(Data Langauge), 사용자(User), 데이터베이스 관리자 (DBA), 데이터베이스 컴퓨터 (Database Computer), 시스템으로 되어 있다.

 

여기서 바로 데이터베이스 관리 시스템(DBMS)이 나온다. DBMS는 데이터베이스를 운영하고 관리하는 소프트웨어다. 데이터를 수정하고자 할 때, 우리가 직접 데이터 파일을 열어서 수정하는 게 아니라, DBMS가 중간에서 안전하게 데이터를 다루도록 도와주게 된다. 이 시스템의 목적은 첫 번째로 공유성, 두 번째로는 독립성이라고 할 수 있다. 공유성이란 여러 사용자가 동시에 데이터베이스에 접근할 수 있다는 의미고, 독립성이란 데이터베이스 구조가 바뀌어도 응용 프로그램은 영향을 받지 않는다는 의미다.

 

이런 역할을 하는 DBMS 안에서는 여러 구성 요소가 함께 동작한다. 아래 그림처럼 SQL 인터페이스, 질의 처리기, 버퍼 관리자, 트랜잭션 관리자, 스토리지 엔진 등이 서로 연결되어 데이터베이스 파일을 관리한다.

 

DBMS의 구성도

 

이러한 DBMS에는 계층형(Hierarchical DBMS)과 망형(Network DBMS), 그리고 관계형(Relational DBMS)이 사용되는데, 최근에는 관계형, 줄여서 RDBMS만이 주로 사용되고 있다. 계층형은 트리 구조로 데이터를 관리하는데, 데이터 간 관계가 복잡해지면 표현이 어려웠고, 망형은 그래프 구조로 관리하지만 구조 변경이 어렵다는 단점이 있었다. RDBMS는 데이터를 테이블 형태로 저장하고 관계를 맺어서 관리하는 방식인데, 유연하고 확장성이 좋아 지금은 거의 모든 곳에서 RDBMS를 쓴다고 보면 된다.

 

 

SQL이란

밝혔듯이, SQL이란 이 DBMS에서 사용하는 언어다. 다만 일반적인 개발언어와는 다소 다르다고 할 수 있다. 1986년에 표준화된 후로 꾸준히 국제표준화기구(ISO)에 의해서 표준을 정하여 발표하고 있으나 실제 DBMS를 제공하는 회사에서는 나름의 문법을 만들어 사용하고 있다. 개발환경이나 선호도에 따라서 회사 및 프로젝트에서 DBMS 제품의 선택이 다르기 때문에, 표준 언어를 알고 있는 쪽이 좋다. 이 SQL의 문법에 대해서는 후술한다.

 

현재 널리 사용되고 있는 DBMS 제품은 다음과 같다. 

 

 

상용 제품으로는 Oracle Database, Microsoft SQL Server, IBM DB2가 있고, 오픈소스로는 MySQL, PostgreSQL, MariaDB가 있다. 그 외에도 가벼운 파일 기반의 SQLite나, NoSQL 계열로 MongoDB, Redis 같은 것들도 있다. 우리가 수업에서 배우고 있는 건 Oracle Database다.

 

 

2. 데이터베이스의 구조와 SQL의 작동원리

먼저 데이터베이스의 구조에 대해서 알아볼 것이다. 정확히 말하면 관계형 DBMS, 즉 RDBMS의 구조를 의미한다.

 

스키마(데이터베이스 설계도)

스키마라는 말을 처음 들으면 낯설 수도 있다. 쉽게 말하면 데이터베이스의 설계도를 뜻한다. 건물을 지을 때 설계도가 있어야 하듯이, 데이터베이스도 어떤 테이블이 있고, 각 테이블에는 어떤 컬럼이 있고, 그 컬럼의 타입은 무엇인지, 테이블끼리는 어떤 관계를 맺고 있는지를 정의한 것이 바로 스키마다.
예를 들어 학교 데이터베이스를 만든다고 하면, 학생 테이블, 과목 테이블, 성적 테이블 같은 게 필요할 것이다. 학생 테이블에는 학번, 이름, 학년 같은 컬럼이 있고, 성적 테이블에는 학번, 과목 코드, 점수 같은 컬럼이 있을 것이다. 그리고 성적 테이블의 학번은 학생 테이블의 학번을 참조한다는 관계 정보까지 포함된 게 스키마다.

 

3계층 스키마 구조에 대한 이미지

스키마는 데이터베이스의 논리적 구조를 나타내는 것이라서, 실제 데이터가 어떻게 저장되는지(물리적 구조)와는 분리되어 있다. 이게 바로 아까 말한 독립성의 한 예다. 데이터 저장 방식이 바뀌어도, 스키마만 유지되면 응용 프로그램은 그대로 쓸 수 있다.

 

데이터베이스의 구조

RDBMS에서 데이터는 테이블 형태로 저장된다. 테이블은 엑셀의 시트처럼 행과 열로 이루어져 있다. 행(Row)은 하나의 데이터 항목을 나타내고, 열(Column)은 데이터의 속성을 나타낸다. 예를 들어 학생 테이블이 있다면, 한 행은 학생 한 명의 정보고, 각 열은 학번, 이름, 학년 같은 속성이 된다.

 

각 테이블에는 기본키(Primary Key)라는 게 있다. 기본키는 각 행을 유일하게 식별하는 컬럼이다. 학생 테이블에서는 학번이 기본키가 될 수 있다. 학번은 중복될 수 없고, 이 값으로 특정 학생을 찾아낼 수 있기 때문이다. 또 외래키(Foreign Key)라는 것도 있는데, 이건 다른 테이블의 기본키를 참조하는 컬럼이다. 성적 테이블의 학번 컬럼이 학생 테이블의 학번을 참조한다면, 이게 외래키가 되는 것이다. 이런 기본키와 외래키로 테이블끼리의 관계를 맺는 게 관계형 데이터베이스의 핵심이다.
그리고 각 컬럼에는 데이터 타입이 정해져 있다. 정수형, 문자열, 날짜형 등등. Oracle 기준으로 보면 VARCHAR2(문자열), NUMBER(숫자), DATE(날짜) 같은 타입들이 있다. 컬럼마다 NOT NULL 같은 제약조건도 걸 수 있다. 이름 컬럼은 비워두면 안 된다거나, 나이는 0 이상이어야 한다거나 하는 규칙을 데이터베이스 단에서 강제할 수 있는 것이다.
DBMS는 이런 테이블들을 어떻게 실제로 저장하고 관리할까? Oracle 기준으로 보면 테이블스페이스(Tablespace)라는 논리적 단위로 관리한다. 테이블스페이스는 여러 테이블을 담는 컨테이너 같은 것이고, 그 안에는 세그먼트(Segment)라는 단위로 각 테이블이나 인덱스별 저장 공간이 있다. 세그먼트는 다시 익스텐트(Extent)로 나뉘고, 익스텐트는 블록(Block)으로 나뉜다. 블록이 데이터를 읽고 쓰는 최소 단위다. 보통 8KB 정도 크기고, 이 블록 단위로 디스크에서 메모리로 데이터를 읽어 온다.
다만 테이블스페이스, 세그먼트, 익스텐트, 블록 같은 개념은 Oracle에 특화된 용어다. PostgreSQL에서는 블록 대신 페이지라고 부르고, MySQL도 비슷하게 페이지라는 단위를 쓴다. 개념은 비슷하지만 용어와 세부 구조는 DBMS마다 조금씩 다르다. 중요한 건 데이터베이스가 데이터를 효율적으로 저장하고 빠르게 검색하기 위해 여러 계층으로 나눠서 관리한다는 점이다.

 

 

SQL의 작동 원리

SQL의 문장이 실행되는 과정을 이미지로 간단히 정리해보면 다음과 같다.

 

 

SQL 문장이 데이터베이스로 전송되면 먼저 Parser가 구문과 의미를 분석하여 쿼리 트리를 만든다. 여기서 구문 분석이란 SQL 문법에 맞게 썼는지를 확인하는 것이다. 예를 들어, SELECT 다음에 FROM이 와야 하는데 WHERE가 먼저 나오면 오류를 내는 식이다. 의미 분석은 테이블이나 컬럼이 실제로 존재하는지, 자신이 그 테이블에 접근할 권한이 있는지를 체크하는 단계다.

다음으로 Optimizer가 쿼리 트리를 받아서 가장 효율적인 실행 계획을 수립한다. 같은 결과를 낼 수 있는 방법이 여러 개 있을 때, 어느 방법이 가장 빠를지를 계산하는 단계다. 예를 들어 테이블을 풀스캔할지, 인덱스를 쓸지, 여러 테이블을 조인할 때 어느 순서로 조인할지 같은 결정을 여기서 내린다. 이 과정이 쿼리 최적화라고 부르는 부분이고, 데이터베이스 성능에 큰 영향을 미친다.

실행 계획이 정해지면 Executor가 그 계획에 따라 실제로 데이터를 찾는다. 이 과정에서 Access Methods가 스토리지 엔진에 접근하여 실제 데이터를 검색한다. 만약 쿼리가 SELECT 같은 읽기 전용이라면 버퍼 관리자를 통해 캐시나 데이터 파일에서 데이터를 가져온다. 캐시에 이미 데이터가 있으면 디스크까지 안 가고 메모리에서 바로 가져오니까 훨씬 빠르다. 캐시에 없으면 디스크에서 읽어서 메모리에 올려놓고, 다음번에는 빠르게 쓸 수 있게 한다.

UPDATE나 INSERT처럼 데이터를 변경하는 문장이라면 트랜잭션 관리자로 전송되어 추가 처리가 이루어진다. 트랜잭션이란 하나의 작업 단위를 말한다. 예를 들어 계좌 이체를 한다고 하면, 내 계좌에서 돈을 빼고 상대 계좌에 돈을 넣는 두 작업이 하나의 트랜잭션이다. 둘 중 하나만 성공하고 하나가 실패하면 안 되니까, 둘 다 성공하거나 둘 다 실패하도록 묶어서 관리한다.

트랜잭션이 진행되는 동안에는 Lock 관리자가 데이터에 잠금을 걸어 다른 작업의 간섭을 막는다. A가 어떤 행을 수정하고 있는 동안 B가 그 행을 동시에 수정하려고 하면 문제가 생기므로, 먼저 작업하는 쪽이 끝날 때까지 다른 쪽은 기다리게 하는 것이다. 그리고 ACID(Atomicity, Consistency, Isolation, Durability) 속성을 보장하여 데이터의 무결성을 유지한다.

ACID란 원자성(한 트랜잭션은 전부 성공하거나 전부 실패), 일관성(데이터베이스는 항상 일관된 상태 유지), 고립성(동시에 실행되는 트랜잭션들은 서로 영향을 안 미침), 지속성(성공한 트랜잭션은 영구 저장)을 의미한다. 이 네 가지를 보장해야 데이터베이스를 믿고 쓸 수 있다.

이렇게 SQL 문장 하나가 실행되기까지는 여러 단계의 분석과 최적화, 그리고 안전한 데이터 처리가 이루어진다고 할 수 있다.

 

 

3. SQL의 분류

SQL 문법은 크게 다섯 가지로 분류할 수 있다. 각각이 하는 역할이 다르고, 목적에 따라서 다른 명령어를 쓰게 된다.

 

DDL

DDL(Data Definition Language)은 데이터 정의어라고 하는데, 데이터베이스 구조 자체를 정의하고 수정하는 명령어다. 테이블을 만들거나(CREATE), 구조를 변경하거나(ALTER), 삭제하는(DROP) 작업이 여기 속한다. 예를 들어 CREATE TABLE student 하면 학생 테이블이 만들어지는 것이다. DDL은 구조를 건드리는 거라서 보통 개발 초기나 구조 변경 시에만 쓰고, 일반 사용자는 잘 안 쓴다.

 

DQL

DQL(Data Query Language)은 데이터 질의어라고 하는데, 데이터베이스에서 데이터를 조회하기 위한 명령어다. 가장 많이 쓰는 SELECT 문이 바로 여기 속한다. 사실 SELECT는 너무 중요하고 자주 쓰여서 DQL을 따로 분류하기도 하고, DML에 포함시키기도 한다. 어쨌든 데이터를 읽어오는 모든 작업이 DQL이라고 보면 된다.

 

DML

DML(Data Manipulation Language)은 데이터 조작어라고 하는데, 데이터베이스 안의 데이터를 추가하고(INSERT), 수정하고(UPDATE), 삭제하는(DELETE) 명령어를 말한다. 회원가입을 하면 INSERT로 새 회원 정보를 넣고, 내 정보 수정을 하면 UPDATE로 기존 데이터를 바꾸고, 탈퇴를 하면 DELETE로 데이터를 지우는 것이다. DML은 실제 데이터를 건드리는 것이라서 트랜잭션 관리가 필요하다.

 

DCL

DCL(Data Control Language)은 데이터 제어어라고 하는데, 데이터베이스의 접근 권한을 제어하는 명령어다. GRANT로 권한을 주고, REVOKE로 권한을 뺏는다. 예를 들어 특정 사용자에게 학생 테이블을 볼 수 있는 권한만 주고 수정은 못 하게 한다거나, 관리자만 삭제 권한을 갖게 한다거나 하는 보안 작업에 쓰인다. 보통 DBA(데이터베이스 관리자)가 사용하는 영역이다.

 

TCL

TCL(Transaction Control Language)은 트랜잭션 제어어라고 하는데, 트랜잭션을 관리하는 명령어다. COMMIT은 지금까지의 작업을 확정하는 것이고, ROLLBACK은 작업을 취소하고 이전 상태로 되돌리는 것이다. 예를 들어 UPDATE를 여러 번 했는데 중간에 실수가 있었다 싶으면 ROLLBACK 하면 아무 일도 없던 것처럼 되돌릴 수 있다. COMMIT을 해야 실제로 데이터베이스에 반영되는 것이다.

 

4. SQL의 문법

문법에 대해서 본격적으로 들어가기 전에 SQL 문의 실행 순서부터 확인해보자. 우리가 쿼리를 작성할 때는 보통 SELECT를 먼저 쓰지만, 실제로 데이터베이스가 실행하는 순서는 다르다.

 

실제 SQL문의 실행 순서

 

FROM

쿼리는 FROM 절에서 시작한다. 어떤 테이블에서 데이터를 가져올지를 먼저 정하는 것이다. FROM student 하면 학생 테이블이 작업 대상이 되는 것이다. 만약 여러 테이블을 쓴다면 FROM에 전부 나열하거나 JOIN으로 엮게 된다.

 

JOIN

여러 테이블을 결합할 때 JOIN을 쓴다. 학생 테이블과 성적 테이블을 합쳐서 학생별 성적을 보고 싶다면 JOIN을 쓰는 것이다. INNER JOIN, LEFT JOIN, RIGHT JOIN 같은 종류가 있는데, 어떻게 합칠지를 정하는 부분이다.

 

ON

JOIN을 쓸 때 어떤 조건으로 테이블을 매칭할지를 ON 절에서 정한다. 예를 들어 student 테이블의 학번과 grade 테이블의 학번이 같은 행끼리 합치라는 식으로 쓴다. ON student.id = grade.student_id 이런 식으로 말이다.

 

WHERE

WHERE 절은 조건을 걸어서 특정 행만 걸러내는 부분이다. 나이가 20 이상인 학생만 보고 싶다면 WHERE age >= 20 이렇게 쓴다. FROM과 JOIN으로 테이블을 준비하고, WHERE로 원하는 행만 필터링하는 것이다.

 

GROUP BY

GROUP BY는 특정 컬럼을 기준으로 데이터를 그룹으로 묶는다. 예를 들어 학년별로 학생 수를 세고 싶다면 GROUP BY grade 이렇게 쓰고, SELECT COUNT()로 개수를 센다. 그룹화를 해야 집계 함수(SUM, AVG, COUNT 등)를 의미 있게 쓸 수 있다.

 

HAVING

HAVING은 GROUP BY로 그룹화한 결과 중에서 또 조건을 거는 것이다. WHERE는 그룹화 전에 행을 거르는 거고, HAVING은 그룹화 후에 그룹을 거르는 것이다. 예를 들어 학년별 평균 점수를 구했는데, 평균이 80점 이상인 학년만 보고 싶다면 HAVING AVG(score) >= 80 이렇게 쓴다.

 

SELECT

이제야 SELECT가 나온다. 최종적으로 어떤 컬럼을 보여줄지를 여기서 정한다. SELECT name, age 하면 이름과 나이만 출력되는 것이다. SELECT * 하면 모든 컬럼을 다 보여준다. 실행 순서상으로는 거의 마지막 단계라서, 여기까지 오면 이미 필터링도 다 끝나고 그룹화도 끝난 상태다.

 

ORDER BY

ORDER BY는 최종 결과를 정렬하는 부분이다. 이름 순으로 오름차순 정렬하고 싶으면 ORDER BY name ASC, 내림차순이면 DESC를 쓴다. 여러 컬럼으로 정렬할 수도 있다. ORDER BY grade DESC, name ASC 하면 학년 높은 순으로 정렬하되, 같은 학년 안에서는 이름 순으로 정렬하는 것이다.

 

LIMIT

마지막으로 LIMIT은 결과 개수를 제한한다. 상위 10개만 보고 싶다면 LIMIT 10 이렇게 쓴다. 다만 LIMIT은 Oracle에서는 ROWNUM이나 FETCH FIRST 같은 다른 문법을 쓰기도 한다. MySQL이나 PostgreSQL에서는 LIMIT을 주로 쓴다.
정리하면 SQL 쿼리는 우리가 작성하는 순서(SELECT → FROM → WHERE...)와 실제 실행 순서(FROM → WHERE → GROUP BY → SELECT...)가 다르다는 점을 기억하면 좋다. 데이터베이스는 효율적으로 처리하기 위해 내부적으로 재배치해서 실행하는 것이다.

 

 

 

5. 정리하며

이번주는 SQL에 대해서 배워보았다. 다음주는 설 연휴가 시작된다. 다음에는 SQL문을 이어서 다룰 수도 있고, 특별편으로 LinkedList와 ArrayList와 관련된 글을 가져올 수도 있다.