2. SQL

1. SQL이란?

SQL은 Strucutred Query Language로 구조화된 질의 언어라고 해석할 수 있습니다. 앞선 포스팅에서 DBMS는 응용 프로그램과 컴퓨터 언어를 통해 데이터를 주고 받으며, 다양한 처리를 한다고 하였습니다. SQL은 관계형 DBMS와의 통신에서 사용되는 언어입니다. SQL을 사용하면 복잡한 데이터베이스에서 원하는 데이터를 추가, 삭제, 수정, 조회를 쉽고 빠르게 처리할 수 있습니다.

1) 기존 프로그래밍 언어와의 차이점은 무엇일까?

SQL은 C, Java와 같은 기존 프로그래밍 언어와 마찬가지로 컴퓨터에서 작업을 수행할 수 있는 컴퓨터 언어입니다. 또한 응용 소프트웨어를 만들기 위해 사용됩니다. 언어별로 다양한 데이터 타입(VARCHAR / CHAR 등)을 지원하며, 언어별로 내장된 함수들이 존재합니다. 컴퓨터가 이해할 수 있는 기계어로 변한하기 위해 컴파일러가 사용됩니다.

 

하지만 기존의 프로그래밍 언어인 C, Java 등은 실제 운영체제의 시스템 콜을 호출하여 컴퓨터의 자원을 사용합니다. 따라서 프로그래밍 언어를 활용하면 다양한 파일의 입출력을 제어할 수 있습니다. 반면에, SQL은 관계형 DBMS가 관리하는 테이블 형식의 데이터만 제어할 수 있습니다.

 

또한, 프로그래밍 언어는 개발자가 프로그램의 처음과 끝을 실행 순서에 따라 정의해야 합니다. SQL은 개발자 혹은 특정 프로그램이 필요한 데이터 집합만을 정의하기 때문에 프로그램의 절차를 정의하지 않습니다.

2) SQL이 처리되는 순서

https://dev.mysql.com/doc/refman/8.4/en/pluggable-storage-overview.html

 

MySQL 기준으로 SQL은 파싱 / 쿼리 최적화 / 쿼리 실행 / 결과 반환으로 이루어집니다.

[1] 파싱, Parsing

파싱은 개발자 혹은 응용 프로그램이 보낸 SQL 언어를 분리 시켜 하나의 Tree를 만드는 과정입니다. 이를 통해, SQL의 오타 및 문법 에러를 확인할 수 있습니다. SQL을 잘못 작성했을 때, 에러가 발생하는 경우, 대부분 이 과정에서 다 걸러지게 됩니다.

https://www.cs.emory.edu/~cheung/Courses/554/Syllabus/5-query-opt/intro-parsing.html

 

하나의 SQL 문을 각각 구조별로 나누었기 때문에 쿼리문에 작성된 속성들이 제대로 표기되었는지, DBMS에서 정의한 구문을 올바르게 지켰는지를 보다 쉽고 빠르게 파악할 수 있습니다. 따라서 DBMS는 파싱을 통해 다음과 같은 내용을 확인할 수 있습니다.

• 타입 누락 혹은 불일치 에러
• SQL 구문 에러
• 기타 오타 에러

[2] 쿼리 최적화, Query Optimizer

MySQL의 경우, 쿼리 최적화를 담당하는 옵티마이저를 갖고 있습니다. 이 옵티마이저를 통해 가장 효율적인 방법으로 해당 쿼리가 원하는 데이터를 찾을 수 있는 방법을 탐색합니다. 옵티마이저를 통해 다음과 같은 내용들을 검토합니다.

• 찾고자 하는 테이블의 인덱스가 무엇인지
• 두 개 이상의 테이블을 사용해야 할 경우, 조인이 가능한지
• 데이터 분포에 대한 통계

이렇게 검토한 뒤에 옵티마이저는 실행 계획을 만듭니다. 이 실행 계획에는 테이블에서 데이터를 가져오고, 필터링하는 방법, 인덱스를 사용하는지 여부, 테이블을 조인하는 방법 등에 대한 자세한 정보가 담겨있습니다.

[3] 쿼리 실행, Query Exceution, Storage Engine

쿼리의 파싱과 최적화 이후에는 디스크에서 데이트를 가져올 수 있도록 스토리지 엔진에 쿼리를 전달합니다. 스토리지 엔진은 전달받은 쿼리를 통해 실제로 조회해야 하는 데이터를 가져옵니다. MySQL의 경우, 대표적인 스토리지 엔진은 Inno DB입니다.

[4] 결과 반환

스토리지 엔진은 최종적으로 DBMS에게 결과가 담긴 집합(Result Set)을 반환합니다. 이 결과에는 상황에 따라 다양한 형태의 데이터가 담길 수 있습니다. 실제 조회한 데이터가 될 수도 있고, 상황에 따라 에러 메시지가 담길 수도 있습니다.

 

2. DML, DDL, DCL

1)  DML, Data Manipulation Language, 데이터 조작 언어

DML은 실제 데이터베이스에 저장된 데이터를 대상으로 사용하는 언어입니다. 대표적으로 SELECT, INSERT, UPDATE, DELETE가 있습니다.

 

SELECT는 SQL문 중 가장 중요하며, 활용 빈도가 높은 명령어입니다. SELECT는 저장된 데이터에서 특정 튜플을 조회하는 명령어이며, 특정 조건에 따라 정렬하거나 조건을 추가하여 조회할 수도 있습니다.

SELECT 컬럼1, 컬럼2, ...
FROM 테이블 이름
WHERE 조건식
GROUP BY 그룹화할 컬럼
HAVING 그룹화된 집합에 대한 조건
ORDER BY 정렬 조건
LIMIT 조회할 튜플의 최대 개수

 

FROM 절에는 조회할 테이블의 이름이 들어가며, SELECT는 한 개 이상의 컬럼을 조회할 수 있습니다. *을 사용하면 모든 컬럼을 조회합니다. SELECT는 필요에 따라 다양한 내장함수와 조합하여 사용될 수 있습니다.

 

용도	SQL문 예시	설명
패턴 검색	SELECT * FROM students WHERE major LIKE '_a%'	WHERE와 LIKE 조합을 통해 특정 패턴을 갖는 튜플을 조회할 수 있습니다. "_"는 정확히 한 개의 임의의 문자이며, "%"는 0개 이상의 임의의 문자를 나타냅니다. 따라서 예시에서 사용된 패턴은 두 번째 문자가 a인 major 이름을 조회한다가 되겠습니다.
연산/집계	SELECT COUNT(*), AVG(gpa), MIN(gpa), MAX(gpa) FROM students	- COUNT : 조회된 튜플의 개수 - AVG : 조회된 튜플의 컬럼 평균 값 - MIN : 조회된 튜플의 컬럼 최소 값 - MAX : 조회된 튜플의 컬럼 최대 값

 

INSERT는 데이터를 테이블에 추가하는 명령어입니다. INSERT는 INTO, VALUES라는 구문과 함께 사용되어 어떠한 테이블에 어떠한 값을 저장할 것인지를 정의해야 합니다.

INSERT INTO users (user_id, name, age) VALUES (1, '오영일', 45), (2, '성기훈', 43);

 

INSERT는 하나의 튜플만 추가할 수도 있고, 여러 튜플을 하나의 INSERT문으로 추가할 수도 있습니다. 만약, 튜플이 갖는 PK가 자동으로 증가하는 경우라면, ID 값을 지정하지 않아도 자동으로 추가가 됩니다. DBMS가 무결성 제약조건을 관리하기 때문에 특정 조건의 경우, 일일이 ID 값을 지정하지 않아도 됩니다.

 

INSERT를 사용할 때 유의해야 할 점은 무결성 제약 조건을 지켜야 한다입니다. 테이블을 생성할 때, 각 컬럼별로 가져야 할 제약조건을 준수하지 않은 채로 데이터를 추가할 경우, 실행이 되지 않습니다. 가령, NOT NULL인 컬럼의 값에 NULL 혹은 값을 추가하지 않는다면 해당 쿼리는 실행되지 않습니다.

또한 다른 테이블의 PK를 참조하는 FK 컬럼이 있는 경우에도 무결성 제약조건을 위반하는 경우가 발생할 수 있습니다.

INSERT INTO posts (user_id, title, content) VALUES (10, '오징어게임', '시즌 2');

 

다음과 같이 user_id라는 FK를 갖는 posts 테이블에 데이터를 추가한다고 했을 때, 위에서 지정한 user_id가 10인 튜플이 users 테이블에 존재하지 않는다면, 이는 참조 무결성 제약조건을 위반하게 됩니다.

참조 무결성
참조 무결성은 외래 키가 연관된 테이블의 기본 키 값만을 가질 수 있도록 보장하는 특성을 말합니다. 이것이 지켜질 수 있도록 참조 무결성 제약조건이 존재하며, 이 제약조건 덕분에 각 테이블의 관계가 일관되고 유효한 상태를 유지할 수 있습니다.

만약, 주문 테이블에서 고객 테이블의 PK를 참조하고 있을 때(FK로 고객의 PK를 가짐), 고객의 PK를 변경하거나 데이터 자체를 삭제한다면 어떻게 될까요? 해당 고객을 참조하고 있는 주문 테이블에는 더 이상 존재하지 않거나 유효하지 않은 고객에 대한 주문 정보가 남을 것입니다. 따라서 이러한 사태를 막기 위해 참조 무결성을 지켜야 합니다.

참고 자료 : https://terms.naver.com/entry.naver?docId=3431155&categoryId=58430&cid=58430

 

UPDATE는 기존의 테이블에 존재하는 데이터를 수정하는 명령어입니다. 업데이트하고자 하는 테이블을 지정하고 변경하고자 하는 컬럼과 그 값을 SET 절에 입력합니다. 그리고, 조건에 따른 수정을 할 수 있도록 WHERE 절을 추가할 수 있습니다.

UPDATE 테이블 이름
	SET 컬럼 = 값, 컬럼2 = 값2
    WHERE 조건식;

 

DELETE는 기존의 테이블에 존재하는 데이터를 삭제하는 명령어입니다. DELETE 또한 WHERE 절을 통해 특정 조건을 만족하는 데이터만 삭제할 수 있습니다. 만약, 조건식이 없다면 지정한 테이블의 모든 데이터를 삭제합니다.

DELETE FROM 테이블이름
	WHERE 조건식;

 

외래 키 제약조건

UPDATE와 DELETE의 경우, 외래 키 제약조건을 지정할 수 있습니다. ON UPDATE, ON DELETE를 통해 수정/삭제될 경우의 동작을 지정할 수 있습니다. 이들이 사용되는 조건은 다른 테이블을 참조(다른 테이블의 PK를 FK로 갖는다)할 때입니다.

• 참조하다(자식 테이블) : 다른 테이블의 PK를 FK로 갖는다.
• 참조된다(부모 테이블) : 다른 테이블에서 내 PK 값을 FK로 갖는다.

CREATE TABLE posts {
	post_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT
	
    FOREIGN KEY (user_id) REFERENECE users(user_id)
    ON UPDATE CASCADE
    ON DELETE SET NULL
}

posts 테이블은 users 테이블의 PK 값을 외래 키로 갖습니다. 이 때, 참조되고 있는 테이블인 users의 데이터가 삭제되거나 수정될 때, 이를 참조하는 posts 테이블의 데이터에 대한 동작을 정의하게 됩니다.

CASCADE	참조되는 테이블의 데이터가 수정/삭제되면, 참조한 테이블의 데이터도 수정/삭제된다.
SET NULL	참조되는 테이블의 데이터가 수정/삭제되면, 참조한 테이블의 데이터를 NULL로 변경한다.
SET DEFAULT	참조되는 테이블의 데이터가 수정/삭제되면, 참조한 테이블의 데이터를 기본값으로 변경한다.
RESTRICT	참조하는 테이블이 있을 경우, 참조한 테이블의 데이터는 수정/삭제를 할 수 없다.

 

이처럼 참조 관계를 갖는 테이블 사이에서 수정/삭제 시, 제약조건을 설정함으로써 참조 무결성을 지킬 수 있습니다.

 

물론, CASCADE 설정을 하는 것이 무조건 장점만 있는 것은 아닙니다. 만약, 고객 데이터를 삭제했는데, 관련된 주문 데이터까지 삭제된다면, 추후 추적이나 데이터 활용이 어려워질 것입니다. 즉, CASCADE 설정을 충분히 파악하지 못하면 의도치 않게 데이터를 삭제할 우려가 존재합니다. 고객이 수천 개의 주문 데이터를 갖고 있을 때, 고객이 삭제된다면 수천 개의 주문 데이터까지 전부 삭제해야 하므로 한 번의 삭제가 시스템에 큰 부하를 줄 수 있을 것입니다.

 

따라서 자식 테이블의 데이터 수정/삭제는 DB가 아닌 어플리케이션 단에서 서비스 정책에 맞게 제어하는 방법을 선택할 수도 있습니다. 

2) DDL, Data Definition Language, 데이터 정의 언어

데이터를 정의하기 위한 언어인 DDL은 대표적으로 다음과 같습니다.

CREATE	데이터베이스 혹은 데이터베이스 객체 생성
ALTER	데이터베이스 객체 갱신 (ex. 테이블의 특정 컬럼 or 제약조건을 추가/삭제)
DROP	데이터베이스 객체 삭제 (ex. 테이블 혹은 데이터베이스를 삭제)
TRUNCATE	테이블 구조를 유지한 채 모든 데이터를 삭제

 

데이터베이스 객체란 데이터베이스에서 정의될 수 있는 대상을 통칭하는 용어입니다. 대표적으로 테이블, 인덱스, 뷰 등이 있습니다.

 

CREATE는 데이터베이스(CREATE DATABASE), 테이블(CREATE TABLE), 뷰(CREATE VIEW), 인덱스(CREATE INDEX), 사용자(CREATE USER)와 같이 데이터베이스에서 사용할 수 있는 다양한 대상을 정의할 수 있습니다.

CREATE TABLE 테이블이름 (
	컬럼이름 타입 제약조건 ,
    컬럼이름 타입 제약조건
)

 

CREATE는 테이블을 생성할 때, 각 컬럼의 이름과 타입 그리고 제약조건을 정의할 수 있습니다.

대표적인 제약조건

PRIMARY KEY	특정 컬럼을 기본 키로 지정
UNIQUE	특정 컬럼이 중복되지 않는 고유한 값을 갖도록 지정 (기본 키와 비슷하지만 테이블 당 하나만 존재해야 하는 기본키와 달리 여러 컬럼이 UNIQUE 할 수 있으며 NULL 값을 허용)
FOREIGN KEY	특정 컬럼을 외래 키로 지정
DEFAULT 기본값	기본값 지정
NULL/NOT NULL	특정 컬럼에 NULL 값을 허용/허용하지 않음

 

이처럼 테이블을 생성할 때, 특정 컬럼에 제약조건을 추가할 수 있습니다! 또한 제약조건은 CONSTRAINT라는 명령어를 사용하여 이름을 부여할 수 있습니다.

CREATE TABLE user (
	id INT PRIMARY KEY AUTO_INCREMENT
    name VARCHAR(50)
    
	CONSTRAINT uniq_name UNIQUE (name)
)

 

다음과 같이 name이라는 컬럼에 UNIQUE 제약조건을 부여한 것을 uniq_name이라는 이름으로 지정할 수 있습니다.

 

ALTER는 기존에 생성된 테이블의 컬럼 혹은 제약조건을 추가/수정/삭제할 수 있는 명령어입니다.

https://www.w3schools.com/mysql/mysql_alter.asp

W3Schools.com

해당 글을 통해 ALTER를 통해 할 수 있는 기능들을 빠른 실습과 함께 알아볼 수 있습니다.

 

DROP은 테이블 혹은 데이터베이스를 삭제할 수 있는 명령어입니다.

DROP DATABASE 데이터베이스 이름;
DROP TABLE 테이블 이름;

 

DROP은 데이터베이스, 테이블 이외에도 뷰, 인덱스에도 적용할 수 있습니다.

 

TRUNCATE는 테이블의 구조를 유지한 채로 테이블의 모든 데이터를 삭제하는 명령어입니다. 테이블을 아예 삭제하는 DROP과 달리 테이블은 삭제되지 않습니다. 따라서 TRUNCATE 사용 후에 DESC라는 명령어로 테이블 구조를 확인할 수 있습니다.

 

https://www.inflearn.com/community/questions/868672/truncate-table-%EC%8B%9C-auto-increment-%EC%BB%AC%EB%9F%BC-1%EB%A1%9C-%EC%B4%88%EA%B8%B0%ED%99%94?srsltid=AfmBOorv5BF-SHqb0UUxdeWqYMHbIAKf0khNc4OtxO4varUGbcB8qaD7

TRUNCATE TABLE 시 AUTO_INCREMENT 컬럼 1... - 인프런 | 커뮤니티 질문&답변

TRUNCATE를 사용하면 AUTO_INCREMENT의 속성을 초기화하여 PK가 다시 1부터 시작하도록 할 수 있습니다. (MySQL 기준) 따라서 이를 사용하여 DB를 사용해야 하는 테스트의 격리를 보장할 수 있습니다.

 

DROP, DELETE, TRUNCATE 모두 튜플 혹은 테이블을 삭제하는 SQL문이지만 이들 사이에는 약간의 차이가 존재합니다. DROP의 경우, DDL로써 테이블 스키마, 제약조건, 뷰 등을 삭제하기에 롤백이 되지 않습니다.

 

반면 DELETE는 특정 테이블의 데이터를 조건절과 함께 사용하여 삭제하거나, 테이블 내 데이터를 전부 삭제할 수 있습니다. 또한, DELETE는 롤백을 지원합니다. 하지만, 테이블 내 모든 데이터를 삭제해야 한다면, DELETE 보다는 TRUNCATE가 더 나은 선택이 될 것입니다.

 

왜냐하면, DELETE는 행 하나하나를 삭제하기 때문에 트랜잭션 로그에 각 행에 대한 삭제 로그가 남습니다. 하지만 TRUNCATE의 경우, 테이블 스키마, 제약조건을 유지한 채 데이터만 통으로 삭제하기 때문에 성능 상 더욱 빠릅니다.

 

3) DCL, Data Control Language, 데이터 제어 언어

DBMS도 데이터베이스를 관리하는 프로그램으로 일종의 서버라고 할 수 있습니다. 따라서 DBMS에 접근하는 사용자들에게 접근 권한을 부여하거나 회수할 수 있습니다. 이 때, 사용하는 것이 DCL입니다. 제어라는 키워드를 통해 쉽게 이해할 수 있습니다.

GRANT	사용자에게 권한을 부여
REVOKE	사용자로부터 권한을 회수

 

3. 서브 쿼리, 조인, 그리고 뷰

서브 쿼리, 조인, 그리고 뷰 이들의 공통점은 효율적으로 DBMS에 쿼리를 보내는 방법이라는 것입니다.

1) 서브 쿼리

서브 쿼리는 완전한 SQL문 안에 존재하는 또 다른 SQL문을 말합니다. 조인은 두 개 이상의 테이블을 하나로 합치는 것을 의미합니다. 서브 쿼리와 조인은 다른 개념이지만, 둘 다 복수의 테이블을 활용하여 문제를 해결해야 할 때 유용하게 사용할 수 있습니다.

https://dev.mysql.com/doc/refman/8.4/en/subqueries.html

MySQL은 서브 쿼리를 다음과 같이 정의하고 있습니다. 즉, 괄호로 감싸져 SQL문 안에 존재하는 SELECT문이라고 할 수 있습니다. 서브 쿼리는 SELECT 뿐만 아니라 DELETE 문에서도 사용될 수 있습니다. 또한, 서브쿼리는 FROM / WHERE절, SELECT의 컬럼에 위치할 수 있습니다. 

 

서브 쿼리가 들어간 SQL문을 이해할 때, 먼저 외부 쿼리(서브 쿼리를 감싸는 가장 밖에 있는 SQL문)의 구조를 파악합니다.

SELECT // 외부 쿼리 
	users.username,
    (SELECT COUNT(*) // 서브 쿼리
     FROM posts
     WHERE posts.user_id = users.user_id) AS post_count
FROM users;

 

다음과 같이, 서브 쿼리가 포함된 SQL문을 어떻게 해석하면 좋을까요? 먼저, 외부 쿼리를 보면 users라는 테이블에서 username과 서브쿼리의 결과 값을 조회한다라는걸 알 수 있습니다. 그리고, 서브 쿼리를 보면 posts라는 테이블에서 posts의 user_id와 users의 user_id가 같은 튜플의 개수를 post_count라고 정의한다는걸 알 수 있습니다.

 

즉, 사용자별로 몇 개의 게시글 개수를 갖는지를 확인하는 쿼리문임을 알 수 있습니다.

2) 조인

서브 쿼리를 활용하면 여러 테이블들을 조합하여 데이터를 조회할 수 있습니다. 하지만, SQL문이 복잡해진다는 위험도 있습니다. 여러 테이블을 기반으로 SQL문을 작성할 수 있는 또 다른 방법은 조인입니다. 조인은 말 그대로 두 개 이상의 테이블을 합쳐서 결과 값을 조회함을 의미합니다.

[1] INNER 조인

조인을 할 때에는 조인 조건을 지정해주는데, 이 조인 조건을 만족하는 데이터만을 가져오는 것이 INNER 조인입니다. 즉, 두 테이블 기준으로 이들의 교집합(조인 조건을 만족하는 데이터 집합)만을 가져오는 것입니다.

https://www.w3schools.com/sql/sql_join_inner.asp

 

다음과 같이, Products와 Categories를 조인하여 CategoryID가 같은 데이터만을 조회합니다.

이와 같이, 조인을 활용하여 두 개 이상의 테이블을 결합하여 일종의 새로운 테이블을 조회할 수 있습니다.

[2] OUTER 조인

https://www.w3schools.com/sql/sql_join_left.asp

LEFT (OUTER) 조인입니다. LEFT는 SQL문 기준 FROM에 오는 테이블을 말합니다. 다이어그램을 보면 알 수 있듯이, LEFT 테이블은 모두 조회가 되며, 조인 조건을 만족하는 RIGHT 테이블의 일부도 함께 조회가 됩니다.

저 교집합에 속하는 조건은 조인 조건입니다. 따라서 조인 조건을 만족하지 못하는 RIGHT 테이블의 값은 NULL로 조회됩니다.

https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_join_left

 

CustomerID가 같은 두 테이블의 데이터를 조인하여 가져옵니다. 하지만, LEFT 조인이기 때문에 LEFT 테이블의 데이터를 모두 가져오되, 조인 조건을 만족하지 못하는 경우 NULL(공백) 값으로 나타납니다. 조인의 핵심은 서로 다른 테이블을 합치는 것입니다. 따라서 LEFT 조인도 두 테이블을 합쳐서 조회합니다. 다만, LEFT 테이블 기준으로 데이터를 가져오기에 조인 조건을 만족하지 않는 LEFT 테이블의 데이터도 전부 조회가 되는 것입니다. 따라서 LEFT 조인의 경우, LEFT 테이블의 데이터가 적을수록 조회 면에서는 좀 더 유리할 수 있습니다. (조건에 상관없이 LEFT 테이블의 데이터는 전부 스캔을 하기 때문입니다.)

 

LEFT 조인의 경우, LEFT 테이블이 갑이 됩니다. 조인 조건을 만족하면 가져오고, 만족하지 않으면 NULL로 둡니다. 약간, 아님 말고~ 하는 마인드라고 할 수 있습니다.

 

RIGHT 조인은 LEFT 조인의 반대라고 생각하면 됩니다. 이번에는 조인 절(ON)에 오는 테이블을 기준으로 모든 데이터를 탐색하고, FROM 절에 오는 LEFT 테이블 중 조건을 만족하지 못하는 값은 NULL로 채워지게 됩니다.

https://www.w3schools.com/sql/sql_join_right.asp

 

만약, 조인 조건을 만족하지 않는 데이터까지도 전부 가져와야 하는 경우라면 OUTER 조인을 사용해야 합니다.

[3] Cross 조인

https://hongong.hanbit.co.kr/sql-%EA%B8%B0%EB%B3%B8-%EB%AC%B8%EB%B2%95-joininner-outer-cross-self-join/

 

Cross 조인은 두 테이블의 조건을 따지지 않고, 나올 수 있는 모든 조합을 만드는 연산입니다. 따라서 두 테이블의 행 개수의 곱만큼 데이터가 조회됩니다.

필자의 MySQL 서버에서 Cross 조인을 실행한 결과, 100만개 이상의 데이터가 조회되었습니다. 이처럼 Cross 조인을 사용하면 대량의 더미데이터를 만들어낼 수 있습니다.

3) 뷰

서브 쿼리 그리고 조인을 통해 복수의 테이블을 조합하여 데이터를 효과적으로 조회하는 방법을 배웠습니다. 하지만, 서브쿼리든 조인이든 연산 데이터를 자주 사용해야 한다면 아무리 효율성이 높아도 중복되는 쿼리가 발생할 것입니다. 이 때, 자주 사용하는 데이터를 일종의 논리적인 테이블인 뷰로 생성하면 중복되는 쿼리를 줄일 수 있습니다.

 

뷰는 SELECT문의 결과로 만들어진 가상의 테이블이라고 할 수 있습니다. DDL언어인 CREATE, DROP을 사용해 추가/삭제할 수 있습니다.

select m.major as `학과 이름`, count(m.major_id) as `학생 수` from user as u
    join major as m on u.major_id = m.major_id
	group by m.major_id;

필자는 다음과 같이, 학생과 학과 테이블을 조인하여 학과별 학생 수를 보여주는 데이터를 조회하였습니다. 해당 데이터를 통해 사용자별 특성을 고려해볼 수 있을 것입니다. 하지만, 이 데이터를 조회하기 위해서는 3줄씩이나 되는 쿼리를 매번 작성해야 합니다. 그래서 다음과 같이 뷰로 생성하여 조회할 때도 쿼리가 아닌 뷰로 조회하도록 하였습니다.

create view major_user_count as
	select m.major as `학과 이름`, count(m.major_id) as `학생 수` from user as u
		join major as m on u.major_id = m.major_id
	group by m.major_id;

select * from major_user_count;

MySQL Workbench

 

마치 하나의 테이블을 조회하는 것처럼 뷰로부터 데이터를 조회할 수 있습니다. 이제 중복되는 쿼리를 생성하지도 요청하지도 않아도 됩니다!

또한, 사용자의 쿼리문으로 생성한 논리적 테이블인 뷰로부터 원본 테이블의 구조를 감출 수 있습니다. 이는 보안성을 높이는데에도 활용될 수 있습니다.

 

4. SELECT문 심화

1) SELECT문의 처리 순서

SELECT문의 처리 순서를 이해하면, 쿼리를 읽는 것뿐만 아니라 성능 이슈를 최소화하는 쿼리를 작성할 때에도 많은 도움을 줄 수 있습니다!

https://jaehoney.tistory.com/191

 

1. FROM 절을 통해 조회할 테이블을 선택하고, JOIN절을 통해 복수의 테이블과 결합하여 가상의 테이블을 생성한다.
2. WHERE 절을 통해 1번에서 생성한 가상의 테이블에 데이터가 존재해야 할 조건을 확인한다. (조건에 맞게 필터링)
3. GROUP BY 절을 통해 지정한 컬럼을 기준으로 그룹화한다.
4. HAVING 절을 통해 3번에서 그룹화된 각각의 그룹이 존재해야 할 조건을 확인한다. (조건에 맞는 그룹만 필터링)
5. SELECT 문을 통해 최종으로 걸러진 데이터만 남은 테이블에서 어떠한 컬럼을 출력할 지를 지정한다.
6. ORDER BY 절을 통해 출력할 데이터를 지정한 조건(오름차순, 내림차순)으로 정렬한다.
7. LIMIT 절을 통해 최대 몇 개의 데이터만 출력할지 지정한다.

따라서 SQL문을 작성할 때도 먼저 FROM, WHERE를 쭉 적고, 마지막에 어떠한 컬럼을 출력할 지를 정하는 SELECT문 적으면 되겠습니다. 이는 SELECT문의 처리 순서에 맞게 작성하는 것이기 때문에 중간에 성능튜닝 포인트를 찾을 수 있습니다.

2) GROUP BY, ORDER BY

GROUP BY는 SELECT문으로 조회할 때, 특정 컬럼을 기준으로 그룹핑할 때 사용할 수 있습니다. 위에서 언급했던 것처럼 학과별 학생 수를 조회하고 싶다면 학과를 기준으로 그룹화를 해야 합니다. 따라서 학과 컬럼을 기준으로 GROUP BY를 사용하면 해당 문제를 해결할 수 있습니다. GROUP BY는 주로 SUM, COUNT와 같은 집계함수와 함께 사용됩니다.

select m.major as `학과 이름`, count(m.major_id) as `학생 수` from user as u
    join major as m on u.major_id = m.major_id
	group by m.major;

 

ORDER BY는 SELECT문으로 조회할 때, 특정 컬럼을 기준으로 정렬하여 데이터를 출력할 때 사용할 수 있습니다. ORDER BY는 GROUP BY와 달리 SELECT문이 처리되고 난 뒤에 처리됩니다. 오름차순은 ASC, 내림차순은 DESC를 붙여서 사용할 수 있으며, 여러 개를 나열함으로써 정렬 순위를 지정할 수도 있습니다.

select m.major as `학과 이름`, count(m.major_id) as `학생 수` from user as u
		join major as m on u.major_id = m.major_id
	group by m.major
    order by `학생 수` DESC, `학과 이름` ASC;

학생 수 기준으로 내림차순, 학생 수가 같을 때는 사전 순으로 정렬

 

다음과 같이 학과별 학생 수를 출력할 때, 먼저 학생 수를 기준으로 내림차순하고, 학생 수가 같을 경우, 학과 이름으로 오름차순하여 정렬할 수 있습니다.

3) SELECT FOR UPDATE란

SELECT FOR UPDATE는 말 그대로 데이터를 수정(UPDATE)하기 위해 특정 데이터를 조회하는 구문입니다. 즉, 해당 구문에 지정된 데이터에 LOCK이 걸림으로써 다른 세션에서는 해당 데이터를 조회할 수는 있으나, 수정/삭제할 수 없습니다. 이를 통해, 금융 시스템/재고 시스템과 같이 특정 리소스에 대한 동시성 제어가 필요한 경우, SELECT FOR UPDATE를 사용할 수 있습니다.

 

https://whxogus215.tistory.com/147

SELECT FOR UPDATE의 권한 범위

해당 게시글을 통해 SELECT FOR UPDATE를 사용했을 때, 접근 권한을 확인할 수 있습니다.

 

 

 

참고 자료:

https://medium.com/@yashpaliwal42/mysql-queries-behind-the-scenes-a-deep-dive-into-how-it-all-works-dea4726ff057

MySQL Queries: Behind the Scenes — A Deep Dive into How It All Works

https://www.inflearn.com/course/%EC%9D%B8%ED%94%84%EB%9D%BC-%EA%B3%B5%EB%B0%A9-%EC%84%9C%EB%B9%84%EC%8A%A4-%EB%A7%8C%EB%93%A4%EA%B8%B0/dashboard

인프라공방 - 그럴듯한 서비스 만들기 강의 | 이동규 - 인프런

https://www.yes24.com/product/goods/130179291

이것이 취업을 위한 컴퓨터 과학이다 with CS 기술 면접 - 예스24

https://jaehoney.tistory.com/191

SQL - SELECT 쿼리 실행 순서 정리!

https://dololak.tistory.com/446

[Database] SELECT ~ FOR UPDATE 란?