Week2 SQL

SQL

SQL이 C언어와 같은 프로그래밍 언어와 어떤 차이가 있는지 설명해 주세요.

• SQL은 데이터를 처리하는 데 있어 "무엇"을 할지 지정하는 데이터베이스 질의 언어이며, 데이터 중심 작업에 특화되어 있습니다.

• C 언어는 선언형 언어로, 어떻게 할지 지정합니다. 저수준 작업과 논리적 흐름을 처리하는 범용 프로그래밍 언어로, 하드웨어와의 상호작용이나 메모리 관리를 수행할 수 있습니다.

SQL은 결과를 지정하게되고, C언어는 결과가 아닌 과정을 지정합니다.

개발자가 작성한 SQL이 MySQL에서 어떤 과정을 통해 실행되나요?

• 파싱(Parser): SQL 쿼리가 MySQL에 도착하면 쿼리 파서가 이를 분석. 쿼리 문법이 올바른지 체크. 구문 트리(Parse Tree)를 생성하여 쿼리의 구조를 파악

• 전처리(Preprocessor): 파싱된 Parse Tree를 기반으로 SQL 문장이 논리적으로 문제가 없는지 확인. (참조된 테이블과 컬럼이 유효한지, 접근 권한이 있는지도 포함)

• 옵티마이저(Optimizer): 옵티마이저는 쿼리를 효율적으로 실행할 수 있도록 실행 계획을 수립

• 쿼리 실행(Execution): 쿼리 실행 엔진이 옵티마이저에서 수립한 실행 계획을 기반으로 쿼리를 실제로 수행. (스토리지 엔진을 호출)

DML이 뭐고 어떤 구문이 있나요?

• 데이터를 조작하는 데 사용되는 SQL 언어의 한 부분

• 조회, 삽입, 수정, 삭제 (CRUD)

DDL이 무엇이고 어떤 구문이 있나요?

• 데이터 정의 언어로, 데이터베이스의 구조를 정의하거나 변경하는 데 사용

• CREATE, ALTER, DROP, TRUNCATE(구조는 유지하고 테이블의 모든 데이터 삭제)

DCL이 무엇이고 어떤 구문이 있나요?

• 데이터베이스에 대한 접근 권한을 제어하는 SQL의 한 부분

• GRANT, REVOKE(권한)

CASCADE 설정에 대해서 설명해주세요

• 관계형 데이터베이스에서 참조 무결성을 유지하기 위한 설정

• 데이터가 삭제될 때, 해당 부모 데이터와 연관된 자식 테이블의 데이터도 자동으로 삭제 (DELETE)

• 기본키 값이 수정될 때, 그 기본키를 참조하는 자식 테이블의 외래키 값도 자동으로 수정 (UPDATE)

• 락 범위 문제, 롤백 문제

참조 무결성이 무엇인가요?

• 부모 테이블과 자식 테이블 간의 데이터 일관성을 보장하는 제약 조건

• 자식 테이블의 외래키 값은 반드시 부모 테이블의 기본키 값과 일치해야 합니다

• 기본키가 삭제되었는데도 자식테이블에 존재하면 참조 무결성 위반.

• ON UPDATE CASCADE 등으로 관련 데이터를 갱신해야합니다.

실무에서 보통 CASCADE 설정을 하지 않는 이유는 무엇인가요?

• 성능 이슈: 재귀적으로 삭제나 수정 작업이 전파됨. 읽고 쓰는 IO작업이 모든 레코드에 대해 동일하게 발생하여 성능저하

• 락 범위 확장: Isolation 레벨에 따른 경합상태(충돌) 또는 성능 저하가 발생. 재귀적이기 떄문에 데드락 발생가능

• 롤백 문제: 다시 로그를 추적하여 복구 비용이 큽니다.

다만, CASCADE를 사용하지 않았을때는 코드의 복잡성이 증가하고, 데이터 일관성 문제가 발생할 수 있으며, 개발 빛 유지보수 비용이 증가하며 실시간 일관성 보장이 어렵습니다. 관련된 테이블이 늘어나게 되면 전략패턴 또는 옵저버 패턴을 사용하면 좋을것 같습니다.

View가 무엇인지 설명하고 이를 사용하는 이유에 대해 설명해주세요.

• 가상의 테이블을 생성하는 SQL 객체. View는 실제 데이터를 저장하지 않으며, 단순히 SQL 쿼리의 결과를 테이블처럼 조회할 수 있게 만들어줍니다.

• 간편: 복잡한 SQL 쿼리를 미리 정의하여, 이를 간단하게 호출

• 캡슐화: 원본 테이블을 직접 노출하지 않고, 특정 컬럼이나 데이터만 조회할 수 있도록 설정.

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

• FROM (JOIN)

• WHERE

• GROUP BY

• HAVING

• SELECT

• DISTINCT

• ORDER BY

• LIMIT

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

• 데이터를 조회할 때 해당 데이터에 잠금(Lock)을 거는 기능

• 트랜잭션 내에서 데이터를 수정하기 전에 읽고 있는 동안 다른 트랜잭션이 데이터를 수정하지 못하게 하는 것이 목적

• 동시성 문제 해결

• SELECT FOR UPDATE 쿼리가 실행될 때마다 해당되는 행을 하나씩 읽고, 그 행에 대해 즉시 락을 취득

• SELECT FOR UPDATE로 잠금이 걸린 행은 다른 트랜잭션에서 쓰기 작업(업데이트, 삭제 등)을 할 수 없습니다.

GROUP BY 절에 대해 설명해주세요.

• GROUP BY는 특정 컬럼을 기준으로 데이터를 그룹화하여, 각 그룹에 대해 집계 함수를 사용할 수 있게 해주는 SQL 절

• 집계 함수와 함꼐 사용됩니다. (SUM, COUNT, AVG, MAX, MIN)

ORDER BY 절에 대해 설명해주세요.

• 조회된 결과를 정렬할 때 사용하는 구문 (기본값은 오름차순)

• ORDER BY 절에서는 하나 이상의 컬럼을 기준으로 정렬

INNER JOIN과 OUTER JOIN의 차이점

INNER JOIN (교집합)

• 두 테이블에서 조건을 만족하는 행만 결과에 포함

OUTER JOIN (합집합)

• 한쪽 테이블에만 존재하는 데이터도 결과에 포함시킵니다. 일치하지 않는 행도 반환되며, 해당되는 값이 없는 경우 NULL로 표시됩니다.

• Left OUTER JOIN, Right OUTER JOIN, FULL OUTER JOIN

LEFT OUTER JOIN과 RIGHT OUTER JOIN에 대해서 설명해주세요.

• LEFT OUTER JOIN(또는 LEFT JOIN)은 왼쪽 테이블(첫 번째 테이블)의 모든 데이터를 가져오고, 오른쪽 테이블(두 번째 테이블)과 일치하는 데이터가 있으면 그 값을 함께 가져옵니다. 만약 오른쪽 테이블에 일치하는 값이 없으면 해당 값은 NULL로 반환됩니다.

• RIGHT OUTER JOIN(또는 RIGHT JOIN)은 오른쪽 테이블(두 번째 테이블)의 모든 데이터를 가져오고, 왼쪽 테이블과 일치하는 데이터가 있으면 그 값을 함께 가져옵니다. 만약 왼쪽 테이블에 일치하는 값이 없으면 해당 값은 NULL로 반환됩니다.

CROSS JOIN에 대해 설명해주세요

• 두 테이블의 모든 가능한 조합을 생성하는 JOIN 방식. Cartesian Product.

• 예) 1000개의 행과 500개의 행이 있으면 500,000개의 결과가 나옵니다. (A-1,A-2, B-1,B-2, C-1,C-2)

결과 데이터가 급격히 증가할 수 있으므로 사용할떄 조심해야 합니다.

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

• 하나의 SQL 쿼리 안에 포함된 또 다른 SQL 쿼리

• WHERE 절, FROM 절, 또는 SELECT 절 내에서 사용되며, 내부 쿼리가 먼저 실행되고, 그 결과가 외부 쿼리에 의해 사용

Example

WHERE 절에서의 서브쿼리:

SELECT name
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM departments
    WHERE department_name = 'Sales'
);

• 이 쿼리는 departments 테이블에서 department_name이 'Sales'인 부서의 department_id를 먼저 찾은 후, 해당 부서에 속한 직원들의 이름을 반환합니다.

FROM 절에서의 서브쿼리:

SELECT AVG(salary)
FROM (
    SELECT salary
    FROM employees
    WHERE department_id = 1
) AS dept_salaries;

• 이 쿼리는 employees 테이블에서 department_id가 1인 직원들의 salary를 먼저 가져온 후, 그들의 평균 급여를 계산합니다. 서브쿼리의 결과는 임시 테이블처럼 사용됩니다.

SELECT 절에서의 서브쿼리:

SELECT name, (SELECT COUNT(*) FROM orders WHERE customer_id = customers.id) AS order_count
FROM customers;

• 이 쿼리는 customers 테이블에서 각 고객의 이름과 해당 고객이 주문한 주문 수를 서브쿼리를 통해 계산하여 함께 반환합니다.

DROP , TRUNCATE, DELETE에 대해 설명해주세요

• DROP: 테이블 자체를 삭제 (복구 불가)

• TRUNCATE: 테이블의 모든 데이터를 삭제하지만, 테이블 구조와 인덱스는 남겨두는 명령어. (롤백 불가)

• DELETE는 특정 레코드를 삭제할 때 사용 (트랜잭션 지원, 롤백 가능)

DISTINCT에 대해서 설명해주시고, 사용해본 경험도 공유해주세요.

• 중복된 결과를 제거하여, 고유한 값들만 반환하는 데 사용

• 사용자 댓글 분석: 예를 들어, 게시글에 몇 명의 서로 다른 유저가 댓글을 남겼는지 확인하고 싶다면, 댓글 테이블에서 사용자 ID를 기준으로 DISTINCT를 사용하여 고유한 유저 수를 조회할 수 있습니다.

SQL Injection 공격이 무엇인가요?

• SQL Injection은 악의적인 사용자가 입력 필드를 통해 SQL 쿼리를 조작하여, 데이터베이스에 의도하지 않은 쿼리를 실행하도록 만드는 공격 기법

• 파라미터화된 쿼리(Prepared Statements): 입력된 값이 SQL 구문으로 해석되지 않고, 데이터로 처리되므로, SQL Injection 공격을 예방

• ORM 사용: 객체 지향적으로 데이터베이스와 상호작용

알고 있는 SQL 안티패턴이 있으면 설명해주세요.

SQL 쿼리 작성 시 피해야 할 비효율적인 패턴

• SELECT * 사용하는 것

• 인덱스를 타지 않는 쿼리 사용하는 것

• 대규모 트랜잭션 사용하는 것

• N+1 문제. (N번의 추가 쿼리가 반복적으로 실행되는 것) - Join사용

페이지네이션을 구현할 때 SQL 쿼리를 어떻게 작성해야 하나요?

• 페이지네이션은 대량의 데이터를 여러 페이지로 나누어 부분적으로 조회하는 방식

OFFSET 기반

• Offset은 조회할 데이터의 시작 위치를 지정하고, Limit은 한 번에 조회할 데이터 개수를 지정

• 문제점: 테이블이 커질수록 OFFSET 값이 커지면, 테이블 전체 스캔이 발생해 성능이 저하

커서 기반:

• 마지막으로 조회된 데이터의 ID 또는 타임스탬프 등을 기준으로, 그 이후의 데이터를 가져오는 방식

• 마지막으로 본 데이터 이후의 결과를 가져옵니다. 일반적으로 큰 테이블에서 성능이 더 좋습니다.

• 문제점: 데이터의 삽입, 삭제, 수정 등으로 순서가 바뀌면 정확도가 떨어질 수 있습니다.