구조적 질의어 (SQL, Structured Query Language) ( 3 )

VIEW란?

뷰는 사용자에게 접근이 허용된 자료만을 제한적으로 보여주기 위해 하나 이상의 기본 테이블로부터 유도된, 이름을 가지는 가상의 테이블이다.
뷰는 저장장치 내에 물리적으로 존재하지 않지만 사용자에게 있는 것처럼 간주된다.
뷰는 데이터 보정작업, 처리과정 시험 등 입시적인 작업을 위한 용도로 활용된다.
뷰는 조인문의 사용 최소화로 사용상의 편의성을 최대화한다. **

SELECT 절의 처리순서에 대해서 설명해주세요.

FROM (+JOIN)
- 쿼리의 첫번째 실행 순서는 FROM 절이다 FROM 절에서는 조회할 테이블을 지정한다.
- 이후 JOIN을 실행하여 하나의 가상 테이블로 결합한다.
WHERE
- 조건에 맞는 데이터를 필터링한다.
GROUP BY
- 선택한 칼럼을 기준으로 조회한 레코드 목록을 그룹핑한다.
HAVING
- 그룹핑 후에 각 그룹에 사용되는 조건 절이다.
- 하지만 WHERE 절에서도 사용할 수 있는 조건이면 WHERE절에서 사용하는 것이 좋다.
  -> 각 그룹에 조건을 걸기 때문에 퍼포먼스가 떨어지기 때문이다.
SELECT
- 어떤 열을 출력할 건지 선택한다.
ORDER BY
- 행의 순서를 어떻게 보여줄지 정렬하는 절이다.
LIMIT
- 몇 개의 행을 보여줄 지 선택한다.

SELECT ~ FOR UPDATE 구문에 대해서 설명해주세요.

동시성 제어를 위하여 특정 데이터(레코드)에 대해 베타적으로 Lock을 거는 행위를 말한다.
즉, 해당 구문을 사용하여 사용자가 접근하면 다른 사용자가 해당 레코드에 접근을 하지 못하게하는 것이다.
하지만 UPDATE나 다른 수정 작업을 하지 않을 경우 계속 대기상태에 놓이게 된다.
-> 데드락이 발생하여 성능 저하가 발생할 수 있다.
사용 예시

SELECT seat 
FROM MOVIE
WHERE movie_num = 4
FOR UPDATE;

JOIN

JOIN이란 두 개의 테이블을 서로 묶어서 하나의 가상의 테이블을 만들어 내는 것을 말한다.

INNER JOIN(내부 조인)
- 두 테이블을 조인할 때, 두 테이블에 모두 지정한 열의 데이터가 있는 것들을 합치는 것을 말한다.
- JOIN이라고만 써도 INNER JOIN으로 인식한다.

SELECT <열 목록>
FROM <첫 번째 테이블>
    INNER JOIN <두 번째 테이블>
    ON <조인 조건>
[WHERE 검색 조건]

OUTER JOIN(외부 조인)
- 두 테이블을 조인할 때, 두 테이블에 있는 모든 데이터를 합치는 것을 말한다. (한 쪽에만 데이터가 있어도 출력하고 없는 쪽은 null로 처리되어 나온다.)
- LEFT OUTER JOIN : 왼쪽 테이블의 모든 값이 출력되는 조인
- RIGHT OUTER JOIN : 오른쪽 테이블의 모든 값이 출력되는 조인
- FULL OUTER JOIN : 왼쪽 외부 조인과 오른쪽 외부 조인이 합쳐진 것

CROSS JOIN (상호 조인)
- 한 쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인하는 기능이다.
- 상호 조인 결과의 전체 행 개수는 두 테이블의 각 행의 개수를 곱한 수 만큼 된다. (카디션 곱(CARTESIAN PRODUCT)이라고도 한다.)

SELECT *
FROM <첫 번째 테이블>
    CROSS JOIN <두 번째 테이블>

서브쿼리에 대해서 설명해주세요.

다른 테이블의 값을 기준으로 한 테이블에서 데이터를 검색할 수 있도록 다른 쿼리 내부에 중첩된 쿼리를 말한다.
-> 즉, 다른 쿼리 내부에 포함되어 있는 SELECT 문을 의미
서브쿼리를 포함하고 있는 쿼리를 외부쿼리라고 부른다.
서브쿼리는 내부쿼리라고도 부른다.
외부쿼리에 지정된 조건 또는 기준에 따라 하나 이상의 테이블에서 데이터를 검색한다.
GROUP BY 절을 제외한 모든 절(SELECT, FROM, WHERE, HAVING, ORDER BY, JOIN)에서 사용된다.
단일 값 또는 값 집합을 반환한다.

■ 각 절의 서브쿼리 사용

SELECT
- 특정 단일 값이 필요할 때 사용
- 다중 값을 결과 SET에 표현할 경우에는 에러가 출력된다.

select username, (select top 1 username from testTable)
from testTable;

FROM
- 여러개의 값 SET을 가져올 때 주로 사용한다.
- alias는 거의 필수이다.

select a.*
from
	(
		select username from testTable where username like '%e%'
	) as a

WHERE
- 특정 단일 값이 필요할 때 사용
- 다중 값을 결과 SET에 표현할 경우, 에러 출력
- 다중 값이 필요할 경우 in을 사용한다.

select username
from testTable
where username =
    	(
            select top 1 username from testTable where username like '%e%'
        )
        
        
# in 사용
select username
from testTable
where username in
	(
		select username from testTable where username like '%e%'
	)

JOIN
- 테이블에 조건을 걸어 조인

select a.username
from testTable as a
	inner join
		(
			select id, username
			from testTable
			where username = 'eunbyeol'
		) as b
	on a.id = b.id

DISTINCT에 대해서 설명해주세요.

컬럼명이 중복되지 않고 고유한 자료만으로 별개의 레코드만으로 반환하는 구문이다.
단일 컬럼, 다수의 컬럼, 집계를 위해 사용되기도한다.

■ 사용 예시

단일 컬럼

SELECT DISTINCT city FROM Customers;

다수의 컬럼

SELECT DISTINCT city, country FROM Customers;

집계

SELECT COUNT(DISTINCT city) FROM Customers;

SQL Injection 공격과 예방 방법

SQL Injection은 공격자가 SQL 쿼리에 악의적인 코드를 삽입하여 데이터베이스에 부적절한 접근을 시도하는 공격 방식이다.

■ 예방 방법:

준비된 쿼리(Prepared Statements) 사용: SQL에 매개변수를 전달할 때, 쿼리 자체와 매개변수를 분리해 실행합니다. 이는 삽입된 악의적 SQL 코드가 별도 매개변수로 처리되어 악용될 수 없도록 합니다.
ORM(Object Relational Mapping) 사용: ORM을 통해 쿼리를 작성하면 SQL 구문이 자동 생성되어 인젝션 공격 가능성을 줄일 수 있다.
입력 값 검증: 사용자의 입력 값에 대한 유효성 검사를 진행해 SQL 구문에 직접 영향을 줄 수 없는 값을 입력받도록 제한합니다.
최소 권한 원칙 적용: 데이터베이스 사용자에게 최소한의 권한만 부여하여, SQL 인젝션이 발생하더라도 피해를 최소화합니다.

SQL 안티패턴

SQL 안티패턴이란, SQL을 작성할 때 비효율적이거나 유지보수가 어려운 방식으로 작성된 쿼리 패턴을 의미한다.

SELECT * 사용:
- 설명: SELECT *는 모든 컬럼을 선택하므로 불필요한 데이터를 가져올 가능성이 높습니다.
- 대안
  - 필요한 컬럼만 명시적으로 선택해 성능 최적화 및 명확한 코드 작성이 가능하도록 합니다.
2. N+1 문제:
- 한 번의 쿼리로 해결할 수 있는 작업을 여러 번에 걸쳐 처리하는 문제입니다.
- 예를 들어, 게시글 목록과 각 게시글의 작성자 정보를 동시에 조회할 때, 게시글 개수만큼 작성자 정보를 각각 조회하는 경우
- 대안
  - 조인이나 서브쿼리를 사용하여 한 번에 데이터 조회가 가능하도록 설계합니다.
3. ENUM 타입의 과도한 사용:
- ENUM은 상태나 범주를 정의하는 데 유용하지만, 변경이 잦거나 유연성이 필요한 경우 데이터베이스 설계가 복잡해질 수 있다.
- 대안
  - 별도의 상태를 정의한 테이블을 만들어 조인으로 연결하여 관리하거나, 상수로 관리하는 방법을 고려합니다.

3. 페이지네이션 구현을 위한 SQL 쿼리 작성

페이지네이션은 대량의 데이터를 나눠서 보여주는 방법으로, LIMIT과 OFFSET을 사용하여 구현할 수 있다.
예시 쿼리

SELECT * FROM Orders ORDER BY OrderID LIMIT 10 OFFSET 20;

LIMIT: 한 페이지에 보여줄 데이터의 개수를 제한합니다. 여기서는 10개를 가져옵니다.
OFFSET: 조회를 시작할 위치를 지정합니다. 여기서는 20행 이후부터 조회하므로, 3페이지에 해당하는 데이터를 가져오는 것입니다.
단점
- OFFSET은 앞선 모든 ROW를 우선 가져오고 필요없는 부분은 버리는 방식으로 수행한다. 그래서 적은 데이터일 때는 모르나, 많은 데이터를 수집할 경우 해당 범위가 아님에도 가져와 성능 저하가 이뤄지는 것을 볼 수 있다.

NO OFFSET (커서 기반** 페이지네이션)

NO OFFSET의 경우 WHERE 절을 이용하여 현재 위치를 커서로 지정한 뒤 자료를 읽어오기에 앞선 데이터들의 양이 많아도, 가져오지 않아서 빠른 조회가 가능하다는 장점이있다.
하지만 NO OFFSET은 순차적인 페이지 네이션을 위해 사용되다보니, 특정 조건을 가미한 검색을 불가해진다.
예를 들어, "상품명에 특정 키워드가 포함된 데이터를 검색" 같은 경우, id > ? 조건만으로는 처리할 수 없다.
이러한 단점으로 인해 보다 복잡한 코드를 요구하게 되는데, 이렇게 되면 성능이 떨어져 페이지 네이션을 사용하는 의미가 없어진다.

참고

커서 기반 : 위치를 추적하는 도구

참고 자료 : https://coding-factory.tistory.com/224,
https://jaehoney.tistory.com/191,
https://dololak.tistory.com/446,https://luvris2.tistory.com/514,

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

'DB' 카테고리의 다른 글

인덱스 (Index) (1)	2024.11.15
순차 IO / 랜덤 IO (0)	2024.11.14
구조적 질의어 (SQL, Structured Query Language) ( 2 ) (0)	2024.11.06
구조적 질의어 (SQL, Structured Query Language) ( 1 ) (1)	2024.11.05
MySQL - InnoDB (2)	2024.11.01

VIEW란?

SELECT 절의 처리순서에 대해서 설명해주세요.

SELECT ~ FOR UPDATE 구문에 대해서 설명해주세요.

JOIN

서브쿼리에 대해서 설명해주세요.

■ 각 절의 서브쿼리 사용

DISTINCT에 대해서 설명해주세요.

■ 사용 예시

SQL Injection 공격과 예방 방법

■ 예방 방법:

SQL 안티패턴

3. 페이지네이션 구현을 위한 SQL 쿼리 작성

NO OFFSET (커서 기반** 페이지네이션)

참고

'DB' 카테고리의 다른 글

티스토리툴바