데이터베이스 2주차 스터디

1. SQL

1) C언어와 SQL의 차이점

SQL은 데이터베이스와의 상호작용을 위해 설계된 언어이고, C언어는 일반적인 프로그래밍을 위한 언어.

• C언어는 프로그래밍 언어로 개발자가 직접 프로그램 흐름을 제어하고 메모리 관리 및 최적화를 수행해야 함.
• SQL은 고수준 선언적 언어로, 개발자는 원하는 결과(데이터베이스에서 데이터를 처리하고 관리하는 데 필요한 쿼리문)를 명시, DBMS가 성능 최적화, 제어 흐름, 병행성, 동시성 제어, 결과 처리 등 DBMS가 처리방식 결정 및 실행

특징	C언어	SQL
목적	시스템 및 애플리케이션 소프트웨어 개발	관계형 데이터베이스에서 데이터 관리
언어 유형	절차적 프로그래밍 언어	선언적 질의 언어 (데이터베이스 질의)
실행 방식	컴파일 후 실행	DBMS에서 직접 해석 및 실행
제어 흐름	명시적으로 제어문 사용 (if, for, while 등)	제어 흐름 없음 (어떻게 실행할지 DBMS가 결정)
데이터 처리	변수와 메모리를 사용하여 직접 처리	데이터베이스 테이블에서 집합으로 처리
동작 원리	프로그래머가 명시적으로 모든 흐름과 로직을 정의	필요한 데이터를 선언적으로 명시
언어의 수준	저수준 언어 (하드웨어와 가까운 작업 가능)	고수준 언어 (데이터베이스 작업에 특화)
데이터 저장	변수와 메모리 할당 (직접 메모리 관리)	데이터베이스 테이블에 저장 (DBMS가 관리)
성능 최적화	개발자가 성능 최적화 (메모리, 연산 등)	DBMS가 최적화 (쿼리 최적화기 사용)
사용 예시	운영체제, 게임, 시스템 프로그래밍 등	데이터 조회, 삽입, 업데이트, 삭제 등
병행성 관리	프로그래머가 직접 스레드 및 동기화 관리	DBMS가 동시성 제어 (잠금, 트랜잭션 관리 등)
동시성 제어	명시적으로 동기화 필요 (뮤텍스, 세마포어 등)	내장된 트랜잭션 처리로 동시성 제어
결과 처리 방식	함수나 출력문을 통해 결과를 직접 처리	DBMS가 실행 후 처리 결과 클라이언트에게 반

 

 

2) SQL 쿼리 실행 과정

SQL은 선언적인 언어로서, 개발자는 원하는 결과를 명시하고, DBMS가 어떻게 데이터를 처리할지를 결정.

쿼리 작성 - 파싱 - 쿼리 최적화 - 실행 계획 생성 - 실행 - 결과 반환 - 트랜잭션 종료

 

1. SQL 쿼리 작성 : 개발자는 SQL 쿼리를 작성하여 데이터베이스에 요청

 

2. SQL 파싱 (Parsing) : DBMS는 쿼리를 파싱(parsing)하여 구문을 분석. SQL 쿼리가 문법적으로 올바른지 확인하고, 쿼리의 각 요소를 이해할 수 있는 구조로 변환. 토큰화(tokenization), 구문 분석(syntax analysis) 이루어짐

• 쿼리에서 키워드 (예: SELECT, FROM, WHERE 등)와 식별자 (예: employees, department)를 분리하고, 이들을 내부적으로 처리할 수 있는 데이터 구조로 변환합.

3. 쿼리 최적화 (Query Optimization : 쿼리 최적화기(query optimizer)에 의해 최적화. 여러 실행 경로를 고려하여 가장 빠른 경로를 선택.

• 예를 들어, 인덱스를 사용할 수 있다면 인덱스를 활용하도록 계획을 수립하고, 불필요한 연산을 줄이기 위한 최적화를 수행.
• 실행 계획(Execution Plan)을 생성하여 어떤 방식으로 쿼리를 실행할지 결정. DBMS는 이 실행 계획을 바탕으로 쿼리를 처리할 경로를 설정.

4. 실행 계획 (Execution Plan) 생성 : 실제 DBMS가 데이터를 어떻게 조회하고 처리할지를 결정. 실행 계획은 주로 다음과 같은 정보를 포함

• 인덱스 사용 여부: 특정 테이블에 대해 인덱스를 사용할지 여부
• 조인 방식: 여러 테이블을 조인할 경우, 어떤 방식으로 조인을 할지 (예: 중첩 루프 조인, 해시 조인)
• 정렬 순서: 데이터를 어떻게 정렬할지
• 필터링: 조건에 맞는 행을 어떻게 필터링할지

5. 실제 실행 (Execution) : DBMS는 실행 계획에 따라 쿼리를 실제로 실행. 이 단계에서는 데이터베이스에서 데이터 읽기, 수정, 삽입 또는 삭제 작업을 수행. 

• SELECT 쿼리라면, 해당 데이터를 메모리로 로드하여 결과를 준비
• 읽기 작업이 발생할 수 있으며, 데이터베이스가 물리적으로 저장된 데이터를 디스크 I/O를 통해 읽어옴
• 필요하다면, 중간 결과를 저장하거나 인덱스를 활용하여 빠르게 데이터를 찾음

6. 결과 반환 : DBMS는 쿼리의 결과를 클라이언트로 반환

7. 트랜잭션 종료 : 트랜잭션이 성공적으로 완료되면 커밋이 이루어지고, 데이터베이스에 변경사항이 영구적으로 반영됨. 오류가 발생하면, 롤백이 되어 이전 상태로 되돌릴 수 있음.

 

3) SQL의 분류

- DDL (Data Definition Language) 데이터 정의어

스키마, 도메인, 테이블, 뷰, 인덱스를 정의, 변경, 삭제할 때 사용되는 언어

명령어	기능
CREATE	스키마, 도메인, 테이블, 뷰, 인덱스 정의
ALTER	테이블 정의 변겅
DROP	스키마, 도메인, 테이블, 뷰, 인덱스 삭제
TRUNCATE	테이블에서 전체 데이터 삭제

 

- DML (Data Manipulation Language) 데이터 조작어

데이터베이스 사용자가 응용 프로그램이나 질의어를 통하여 저장된 데이터를 실질적으로 처리하는 데 사용하는 언어이다.

명령어	기능
SELECT	테이블에서 조건에 맞는 튜플 검색
INSERT	테이블에 새로운 튜플 삽입
DELETE	테이블에서 조건에 맞는 튜플 삭제
UPDATE	테이블에서 조건에 맞는 튜플의 내용 변경

 

- DCL (Data Control Language) 데이터 제어어

데이터의 보안, 무결성, 데이터의 회복, 병행수행 제어 등을 정의하는 데 사용하는 언어

명령어	기능
COMMIT	명령 수행 결과를 실제 물리적 디스크로 저장하고, 데이터베이스 조작 작업이 정상적으로 완료되었음을 관리자에게 알려줌
ROLLBACK	데이터베이스 조작 작업이 비정상적으로 종료되었을 때 원래의 상태로 복구함
GRANT	데이터베이스 사용자에게 사용 권한을 부여함
REVOKE	데이터베이스 사용자의 사용 권한을 취소함

 

CASCADE : 메인 테이블 데이터 삭제 시 외래키에 부합되는 모든 데이터 삭제(연쇄 삭제, 모든 권한 해제)

삭제 요소를 참조하는 요소가 있다면 같이 제거해라

DROP, REVOKE 등의 조건으로 사용

RESTRICTED : 

삭제 요소를 참조하는 요소가 있다면 삭제하지 마라

외래 키에 의해 참조되는 값은 메인 테이블에서 삭제할 수 없다(FROM절에서 사용자의 권한만을 해제)

 

 

4) SELECT문

SELECT 절의 사용

SELECT - FROM - WHERE - GROUP BY - HAVING - ORDER BY

SELECT 절의 처리 순서

FROM -> JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY

 

5) SELECT ~ FOR UPDATE

일반적인 SELCT문은 shard Lock , SELEC ~ FOR UPDATE는 locking 구문 

SELECT ~ FOR UPDATE 로 조회하면 선택한 ROW들에 대해 배타적인 LOCK 설정.  트랜잭션이 처리 중인 행을 다른 트랜잭션이 수정하거나 읽지 못하도록 잠금.

데이터 무결성 유지 및 동시성 문제 해결할 수 있음.금융 거래, 재고 관리 등에 사용

• 데이터 무결성 유지 : 여러 사용자가 동시에 데이터를 수정하려할 때 발생할 수 있는 데이터 불일치 방지
• 동시성 문제 해결 : 배타적 LOCK을 통해 특정 데이터에 동시 접근 제한해 데이터 무결성 유지

 

6) 삭제하는 명령어 비교 (DROP vs TRUNCATE vs DELETE)

	DROP	TRUNCATE	DELETE
목적	데이터베이스 객체(스키마, 도메인, 테이블, 뷰, 인덱스) 전체 제거 * 테이블 삭제 시 테이블의 튜플, 인덱스, 뷰 등도 함께 삭제됨	테이블의 모든 데이터 삭제	조건에 맞는 테이블의 튜플/행 삭제
테이블 구조 영향	테이블, 인덱스 등를 완전히 삭제	테이블 구조는 유지, 데이터만 삭제	테이블 구조 유지 (테이블, 컬럼 등)
복구	불가. 자동 COMMIT	불가	COMMIT 실행해야 반영되고 ROLLBACK 가능

 

7) JOIN

: 공통 속성을 중심으로 2개 이상의 테이블을 하나로 만드는 것

• INNER JOIN은 양쪽 테이블에서 조건을 만족하는 데이터만 결과로 반환.
• OUTER JOIN은 조건을 만족하는 데이터뿐만 아니라, 한쪽 테이블에만 존재하는 데이터도 포함. OUTER JOIN은 다시 세 가지로 나뉨.
  • LEFT OUTER JOIN: 왼쪽 테이블에 있는 모든 데이터를 반환하고, 오른쪽 테이블에 일치하는 데이터가 없다면 NULL을 반환
  • RIGHT OUTER JOIN: 오른쪽 테이블에 있는 모든 데이터를 반환하고, 왼쪽 테이블에 일치하는 데이터가 없다면 NULL을 반환
  • FULL OUTER JOIN: 두 테이블의 모든 데이터를 반환하며, 한쪽 테이블에만 존재하는 데이터는 다른 쪽을 NULL로 처리CROSS JOIN : 두 테이블의 모든 가능한 조합을 반환.
    • 왼쪽 테이블의 각 행과 오른쪽 테이블의 모든 행이 결합됨
    • 조건 없이 모든 가능한 N x M 행을 생성

 

 

 

 

3. 뷰

: 사용자에게 접근이 허용된 자료만을 제한적으로 보여주기 위해 하나 이상의 기본 테이블로부터 유도된 가상 테이블.

저장장치 내에 물리적으로 전재하지 않지만, 사용자에게는 있는 것 처럼 간주됨

데이터 보정, 처리과정 시험 등 임시적인 작업을 위한 용도로 활용

 

특징

• 기본 테이블로부터 유도된 테이블이기 때문에 기본 테이블과 같은 형태의 구조를 가짐. 조작도 기본 테이블과 거의 같음
• 필요한 데이터만 뷰로 정의해서 처리할 수 있기 때문에 관리가 용이, 명령문 간단해짐
• 뷰를 통해서만 데이터에 접근하게 하면 뷰에 나타나지 않는 데이터 보호 가능
• 기본 테이블의 기본키를 포함한 속성 집합으로 뷰를 구성해야 삽입, 삭제, 갱신 연산 가능
• 정의된 뷰는 다른 뷰의 정의에 기초가 될 수 있음 -> 기초가 된 뷰 삭제 시 생성된 뷰도 자동으로 삭제

 

장점

• 논리적 테이블 독립성 제공
• 동일 데이터에 대해 동시에 여러 사용자의 상이한 응용, 요구 지원
• 사용자의 데이터 관리를 간단하게 해줌
• 접근 제어를  통한 자동 보안 제공

 

단점

• 독립적인 인덱스 가질 수 없음
• 뷰 정의 변경 불가
• 뷰로 구서왼 내용에 대한 삽입, 삭제, 갱신, 연산 제어에 제약있음

 

 

 

4. 서브 쿼리

: 쿼리 안에 포함된 또 다른 쿼리. 보통 SELECT, INSERT, UPDATE, DELETE 쿼리 내에서 사용

성능에 영향을 미칠 수 있으므로, 가능하다면 조인 방식으로 변경하는 것이 효율적일 수 있음.

• 서브쿼리의 종류:
  1. 단일 값 서브쿼리: 하나의 값을 반환하는 서브쿼리.
  2. 다중 값 서브쿼리: 여러 값을 반환하는 서브쿼리.
  3. IN, EXISTS, ANY 같은 조건문에 서브쿼리 사용.

SELECT * 
FROM employees
WHERE department_id IN (SELECT id FROM departments WHERE location = 'New York');

 

 

 

5. SQL Injection

: 악의적인 사용자가 웹 애플리케이션에서 입력값을 조작하여, 데이터베이스 쿼리를 변조하거나 실행하는 공격. 이를 통해 중요한 데이터에 접근하거나 데이터베이스를 손상시킬 수 있음

예방 방법:

• Prepared Statements: 매개변수를 사용하여 SQL 쿼리를 안전하게 처리.
  SELECT * FROM users WHERE username = ? AND password = ?;
• ORM 사용: Object Relational Mapping을 사용하여 쿼리 작성 시 자동으로 파라미터화된 쿼리를 생성.
• 입력값 검증 및 필터링: 사용자가 입력한 데이터를 검증하고 필터링하여 악의적인 SQL 코드를 차단.
• 권한 제한: 애플리케이션의 데이터베이스 계정에 최소한의 권한만 부여하여 공격을 제한.

 

 

6. SQL 안티패턴

: 비효율적이거나 잘못된 방식으로 SQL을 작성하여 성능 저하나 유지보수 문제를 일으키는 패턴.

• SELECT * 사용: 필요한 컬럼만 선택하는 것이 성능상 유리. SELECT *는 불필요한 데이터를 가져와 성능을 저하시킬 수 있음
• 서브쿼리 중첩: 서브쿼리를 너무 많이 중첩하면 쿼리의 성능이 저하됨. 조인을 사용하여 이를 최적화할 수 있음
• 함수 호출과 조건문: WHERE 절에서 함수 호출을 사용하면 인덱스를 제대로 활용하지 못할 수 있음. 가능한 한 인덱스가 적용되는 방식으로 쿼리를 작성하는 것이 중요

 

7. 페이지네이션 구현

: 페이지네이션의 기본 개념은 데이터를 여러 페이지로 나누어 한 번에 표시할 수 있는 데이터의 양을 제한하는 것.

데이터가 많을 경우, 한 번에 모든 데이터를 로드하면 웹 페이지나 애플리케이션의 성능이 저하됨. 이를 해결하기 위해 데이터를 분할하여 요청된 페이지에 해당하는 데이터만 로드

일반적으로 LIMIT과 OFFSET을 사용하여 구현. 인덱스를 사용하거나 ORDER BY 절을 통해 정렬된 데이터를 반환하는 것이 중요.

예시 1. MySQL

SELECT * 
FROM table_name 
LIMIT 10 OFFSET 20; -- 21번째부터 30번째까지의 데이터

• LIMIT은 한 페이지에 출력할 데이터의 개수를 제한
• OFFSET은 데이터의 시작 위치를 지정

예시 2. Oracle

SELECT * 
FROM ( 
	SELECT rownum AS rn, column_name 
    FROM table_name 
    WHERE rownum <= 30
) 
WHERE rn > 20;

 

 

성능 저하 해결

페이지네이션은 대규모 데이터셋에서 성능 저하를 일으킬 수 있음. 특히 OFFSET 값이 커질수록 성능이 저하됨.(OFFSET이 높은 값을 갖는 경우, 데이터베이스가 해당 위치까지 모든 데이터를 스캔해야 하기 때문)

1) WHERE 절을 활용한 페이지네이션

• 기존 방식 (OFFSET 기반): 데이터가 많아지면 성능이 떨어짐.
• 대안 방식: WHERE 조건을 이용해 이전 마지막 항목을 기준으로 페이지네이션을 하는 방식. 예를 들어, 마지막으로 가져온 항목의 ID나 날짜를 기준으로 그 이후 데이터를 가져오는 방식. -> OFFSET이 커질수록 성능이 떨어지는 문제를 해결 가능

-- 예시: last_seen_id는 마지막으로 가져온 ID 
SELECT * 
FROM products 
WHERE product_id > last_seen_id 
ORDER BY product_id 
LIMIT 10;

 

2) 커서 기반 페이지네이션 (Cursor-based Pagination)

• OFFSET 대신, 마지막으로 본 항목의 ID나 다른 유일한 값을 사용하여 다음 페이지를 로드.
• LIMIT과 WHERE을 결합하여 페이지를 이동할 때마다 이전 데이터를 기억하고, 그 다음 항목부터 가져오는 방식. -> 데이터의 양이 매우 클 때 더 효율적

-- 첫 번째 페이지 
SELECT * 
FROM products 
ORDER BY product_id 
LIMIT 10; 

-- 두 번째 페이지 (이전에 가져온 마지막 product_id 이후의 데이터) 
SELECT * 
FROM products 
WHERE product_id > 100 
ORDER BY product_id 
LIMIT 10;

 

 

 

참고

SQL JOIN 시각화 사이트