1. SQL에 대해서 설명해주세요.
C언어와 같은 프로그래밍 언어와 어떤차이가 있나요?
SQL은 관계형 데이터베이스를 관리하고 조작하기 위한 구조화된 질의 언어입니다.
SQL은 기능에 따라 데이터정의어(DDL), 데이터 조작어(DML), 데이터 제어어(DCL) 등으로 나누어집니다.
SQL(Structured Query Language)
: DBMS에서 데이터를 관리하고 검색하기 위한 언어
SQL은 데이터의 조작 및 관리를 목적으로, 주로 데이터베이스와 상호 작용하기 위해 사용된다.
C언어는 범용 프로그래밍 언어로, 시스템 SW부터 응용 SW까지 다양한 분야에서 사용된다.
2. 개발자가 작성한 SQL이 어떤 과정을 통해 실행 되는지 설명해주세요.
MySQL 기준으로 설명 드리겠습니다.
- 사용자가 작성한 SQL을 데이터베이스로 보냅니다.
- MySQL 쿼리파서는 SQL 문장을 토큰으로 쪼개서 트리를 만듭니다.이 과정에서 문법 오류도 체크합니다.
- 이 트리를 Parse Tree라고 하는데 이를 통해 쿼리를 실행합니다.
- 다음으로 전처리기가 Parse Tree을 기반으로 SQL 문장구조에 문제가 없는지 체크합니다.
- 또한 SQL에 포함된 테이블, 컬럼 이름이 유효한지 접근권한이 있는지 체크합니다.
- 다음으로 옵티마이저가 SQL 실행을 최적화하기 위해 실행 계획을 수립합니다.
- 마지막으로 쿼리 실행엔진이 수립된 실행 계획대로 스토리지 엔진을 호출해서 쿼리를 수행하고 결과를 사용자에게 응답합니다.
1) 파싱(Parsing)
- DBMS에 의해 파싱
- SQL 문장을 token으로 나누고, 문법적으로 검사하여 쿼리의 구조를 분석하는 과정
- 문법 오류나 쿼리의 부정확한 부분이 발견되면, 오류 메시지가 생성된다.
2) 최적화 (Optimization)
- 파싱된 SQL 쿼리를 최적화 과정을 거쳐, 최적의 실행 계획을 생성한다.
- '최적화'는 다양한 실행 계획 중에서 가장 효율적인 방법을 선택하는 과정
- index의 사용, 테이블의 join 순서 등을 결정하여 실행 속도를 최적화한다.
3) 실행 (Execution)
- 최적의 실행 계획에 따라, 실제 데이터베이스에서 데이터를 읽거나 조작한다.
- 테이블의 레코드를 검색하거나 조작하고 결과를 반환한다.
- 이 단계에서 발생하는 오류는 주로 RuntimeError로, 데이터베이스 연결 문제 등이 있다.
3. DML은 무엇인가요? 어떤 구문이 있는지도 설명해주세요.
DML은 데이터 조작어로 테이블에서 데이터를 조회, 삽입, 수정, 삭제하는데 사용됩니다.
주요 구문으로는 SELECT, INSERT, UPDATE, DELETE 등이 있습니다.
DML(Data Manipulation Language)
: DB에서 데이터를 조작하는데 사용되는 언어
데이터의 삽입, 삭제, 수정, 검색과 관련된 작업을 수행한다.
1) SELECT
- DB에서 데이터를 조회하는데 사용
SELECT column1, column2 FROM table WHERE condition;
2) INSERT
- 테이블에 새로운 레코드를 추가하는데 사용
INSERT INTO table (column1, column2) VALUES (value1, value2);
3) UPDATE
- 이미 존재하는 레코드의 값을 갱신 or 수정할 때 사용
UPDATE table SET column1 = value1 WHERE condition;
4) DELETE
- 테이블에서 데이터를 삭제하는데 사용
DELETE FROM table WHERE condition;
4. DDL은 무엇인가요? 어떤 구문이 있는지도 설명해주세요.
DML은 데이터 조작어로 테이블에서 데이터를 조회, 삽입, 수정, 삭제하는데 사용됩니다.
주요 구문으로는 SELECT, INSERT, UPDATE, DELETE 등이 있습니다.
DDL(Data Definition Language)
: DB의 구조를 정의하거나, 수정하기 위해 사용되는 언어
DB 객체(테이블, 인덱스, 뷰..)의 생성, 수정, 삭제 등과 관련된 작업을 수행한다.
1) CREATE
- 데이터베이스 객체를 생성하는데 사용
(ex. 테이블, 뷰, 인덱스, 프로시저 생성)
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
...
);
2) ALTER
- 이미 존재하는 데이터베이스 객체의 구조를 변경할 때 사용
(ex. 열 추가, 삭제, 데이터 형식 변경)
ALTER TABLE table_name
ADD column_name datatype;
3) DROP
- 데이터베이스 객체를 삭제할 때 사용
DROP TABLE table_name;
4) TRUNCATE
- 테이블의 모든 데이터를 삭제하지만 테이블 구조는 그대로 유지할 때 사용
TRUNCATE TABLE table_name;
5. DCL은 무엇인가요? 어떤 구문이 있는지도 설명해주세요.
DCL은 데이터 제어어로 데이터에 대한 접근 권한을 관리하는데 사용됩니다.
주요 구문으로는 GRANT, REVOKE, COMMIT, ROLLBACK, LOCK 등이 있습니다.
DCL(Data Control Language)
: DB에서 데이터에 대한 액세스를 제어하는데 사용되는 언어
DB의 보안과 권한 설정을 관리하는데 사용된다.
1) GRANT
- 사용자나 역할에 대해 특정 데이터베이스 객체에 대한 권한을 부여할 때 사용
GRANT SELECT ON table_name TO user_name;
2) REVOKE
- 사용자나 역할에 대해 특정 데이터베이스 객체에 부여된 권한을 취소할 때 사용
REVOKE SELECT ON table_name FROM user_name;
6. 참조 무결성에 대해서 설명해주세요.
참조 무결성
: 데이터베이스에서 데이터의 일관성과 정확성을 유지하기 위한 개념으로,
테이블 간의 관계를 외래키와 기본키를 이용하여 관리하는 원칙입니다.
이 때, 데이터의 무결성을 유지하기 위한 규칙이 있는데
1) 외래 키 값은 NULL이거나, 참조하는 기본 키 값과 일치해야 한다.
2) 외래키는 참조하는 테이블에 존재하는 값만을 참조해야 한다. (같은 말)
3) 외래 키 갱신 or 삭제를 할 때
- RESTRICT : 참조하는 테이블의 행이 있을 경우, 갱신 or 삭제를 제한한다.
- CASCADE : 참조되는 테이블의 행도 같이 갱신 or 삭제한다.
7. CASCADE 설정에 대해서 설명해주세요.
CASCADE는 참조 무결성을 유지하기 위해 사용되는 설정입니다.
예를들어 설명 드리겠습니다.
주문, 고객 테이블이 있고 주문이 고객 테이블의 기본키를 참조하는 외래키를 갖는다고 했을때,
주문 테이블에 ON DELETE CASCADE 설정을 하면 고객 테이블의 튜플이 삭제 되었을때 연관된 주문 테이블의 튜플도 삭제되게 됩니다.
또 주문 테이블에 ON UPDATE CASCADE 설정을 하면 고객 테이블의 튜플의 기본키가 수정되었을때 연관된 주문 테이블의 튜플 외래키도 수정되게 됩니다.
CASCADE
: 참조하는 테이블의 행이 갱신 or 삭제될 때,
이와 연결된 외래 키를 가진 테이블의 행도 동일한 갱신 or 삭제 동작이 수행된다.
8. VIEW에 대해서 설명해주세요.
VIEW는 여러 테이블들을 합쳐서 만든 가상의 테이블입니다.
VIEW는 여러 테이블들이 합쳐져 하나의 테이블로 정의된 것이기 때문에 SQL 작성을 간단히 할 수 있습니다. 또한 원본 테이블을 노출시키지 않게 함으로 보안성을 제공할 수 있습니다.
VIEW
: 데이터베이스에서 테이블의 일부 데이터나 특정 조인 결과를 저장하지 않고,
실시간으로 생성되는 가상의 테이블
[설명]
데이터를 저장하지 않고 동적으로 생성되기 때문에 실시간으로 업데이트 되는 데이터를 제공하는데 효과적이다.
또한 사용자가 특정한 데이터 조각이나 조인 결과에 자주 접근해야 하는 경우, VIEW를 통해 편리하게 데이터에 접근할 수 있다.
1) 가상의 테이블
- VIEW는 실제로 데이터를 저장하지 않고, DB에서 필요할 때마다 생성되는 가상의 테이블
- VIEW를 사용하여 특정 조건을 만족하는 데이터를 추출하거나 여러 테이블 간의 조인 결과를 조회할 수 있다.
2) 데이터의 동적인 표현
- 저장된 데이터의 논리적인 구조를 동적으로 표현할 수 있다.
3) 저장 프로시저와의 결합
- 저장 프로시저가 복잡한 로직을 담당하고, VIEW가 해당 결과를 사용하는 경우가 많다.
4) 보안 및 권한 관리
- VIEW를 통해 사용자에게 필요한 데이터만 노출시킬 수 있어, 보안 및 권한 관리에 용이하다
9. SELECT 절의 처리순서에 대해서 설명해주세요.
MySQL 기준으로 설명 드리겠습니다.
- 먼저 FROM 절에서 타겟 테이블이 정해 집니다. JOIN이 있다면 적용됩니다.
- WHERE 절이 적용되어 튜플이 필터링 됩니다.
- GROUP BY에 지정한 컬럼을 기준으로 튜플이 그룹화 됩니다.
- DISTINCT가 적용되어 중복된 튜플이 필터링 됩니다.
- HAVING이 적용되어 GROUP BY로 그룹화된 튜플에 대해 추가적으로 필터링 됩니다.
- ORDER BY가 적용되어 튜플이 정렬됩니다.
- LIMIT가 적용되어 명시된 개수만큼 튜플이 반환됩니다.
1) SELECT
- 실제로 가져올 열들을 지정한다.
DB에서 가져온 행들 중에서 특정 열들을 선택하여, 결과 집합을 생성한다.
SELECT JOB_ID, AVG(SALARY) SAL_AVG
2) FROM 절
- 전체 테이블의 결과를 갖고 온다.
FROM Employees
3) WHERE 절
- 조건에 맞는 결과만 갖도록 데이터를 간추린다.
WHERE Salary > 10000
4) GROUP BY
- 선택한 칼럼으로 Grouping 작업을 한 결과를 갖고 있다.
GROUP BY Job_ID
5) HAVING
- GROUP BY 이후 사용되는 그룹화된 결과의 조건 절
- HAVING 절에서 일반 조건들을 다루게 쿼리 실행 순서에 의해 성능이 떨어지게 된다.
HAVING COUNT(*) > 1
6) ORDER BY
- 행의 순서를 어떻게 보여줄지 정렬해주는 절
ORDER BY SAL_AVG DESC
+. where와 having 차이점
where은 행을 필터링하는 데 사용되고,
having은 그룹을 필터링하는 데 사용된다.
10. SELECT ~ FOR UPDATE 구문에 대해서 설명해주세요.
SELECT ~ FROM UPDATE 구문은 튜플들을 조회하면서 락을 겁니다.
그렇기에 다른 트랜잭션이 접근하여 해당 튜플들을 수정할 수 없습니다.
SELECT ... FOR UPDATE
: SQL에서 특정 행을 선택할 때, 해당 행에 대한 업데이트 권한을 확보하는 명령
주로 다중 사용자 환경에서 데이터 일관성을 유지하고, 트랜잭션 간의 충돌을 방지하기 위해 사용된다.
[설명]
SELECT ... FOR UPDATE는 SQL에서 특정 행을 선택할 때 해당 행에 대한 업데이트 권한을 획득하는데 사용됩니다. 이 명령은 다중 사용자 환경에서 데이터 일관성을 유지하고 트랜잭션 간의 충돌을 방지하기 위해 주로 사용됩니다. 트랜잭션 내에서 특정 행을 선택하면 해당 행이 잠기고, 다른 트랜잭션이 같은 행에 대한 업데이트를 시도하면 대기 상태에 들어갑니다. 이러한 잠금은 트랜잭션이 커밋되거나 롤백될 때 해제됩니다
[코드]
START TRANSACTION;
SELECT * FROM 테이블 WHERE 조건 FOR UPDATE;
-- 트랜잭션 진행 중에 다른 트랜잭션이 같은 행을 업데이트 시도하면 대기 상태에 들어감
-- COMMIT 또는 ROLLBACK을 통해 트랜잭션 종료
11. GROUP BY절에 대해서 설명해주세요.
GROUP BY 절을 사용해서 특정 속성을 기준으로 데이터를 그룹화 할 수 있습니다.
주로 집계 함수와 함께 사용됩니다.
예를들어 고객 ID 컬럼과 가격 컬럼이 있는 주문 테이블에서,
고객 ID 기준으로 GROUP BY를 하고 SELECT 절에서,
집계함수 SUM을 사용하면 고객별 총 주문 가격을 조회할 수 있습니다.
GROUP BY
- 특정 열(or 열의 조합)을 기준으로 데이터를 그룹화
- 집계 함수(COUNT, SUM, AVG, MAX, MIN...)를 적용해 그룹 내의 데이터에 대한 통계 정보 추출 가능
[설명]
GROUP BY 절은 SQL에서 데이터를 그룹화하여 특정 열(또는 열의 조합)을 기준으로 집계 함수를 사용하여 데이터를 요약하는 데 사용됩니다. 예를 들어, 주문 테이블에서 각 고객별로 주문한 총 금액을 계산하거나, 날짜에 따른 판매량을 살펴볼 때 주로 사용됩니다. HAVING 절을 함께 사용하여 그룹에 대한 조건을 적용할 수 있습니다.
[코드]
SELECT customer_id, SUM(order_amount) AS total_amount
FROM orders
GROUP BY customer_id;
12. ORDER BY절에 대해서 설명해주세요.
ORDER BY 절을 사용해서 특정 속성을 기준으로 조회 결과를 정렬할 수 있습니다.
기본적으로 오름차순으로 정렬되며, DESC 키워드를 사용하면 내림차순으로 정렬할 수 있습니다.
ORDER BY
- 쿼리의 결과를 특정 열을 기준으로 정렬하는데 사용
[설명]
ORDER BY 절은 SQL에서 쿼리의 결과를 특정 열을 기준으로 정렬하는 데 사용됩니다. 오름차순이 기본이며, 내림차순으로 정렬하려면 DESC 키워드를 사용합니다. 여러 열로 정렬하거나 NULL 값을 처리하는 등의 옵션을 제공하여 쿼리 결과를 원하는 순서로 반환할 수 있습니다.
[코드]
SELECT employee_id, first_name, last_name
FROM employees
ORDER BY last_name, first_name ASC;
13. INNER JOIN과 OUTER JOIN의 차이점에 대해서 설명해주세요.
INNER JOIN을 통해 두 테이블을 결합할 수 있습니다.
ON 절에는 테이블 결합 조건을 지정할 수 있습니다.
예를들어 테이블 A, B에 대하여 INNER JOIN을 하면 ON 절 기준으로 두 테이블의 교집합 결과를 가져옵니다.
1) INNER JOIN
- 두 테이블 간의 일치하는 행만을 반환
즉, 연결 조건을 충족하는 행들만을 결과로 제공하며 양쪽 테이블에 대응되는 행이 없는 경우 해당 행은 제외된다.
- 주로 관련된 데이터만을 가져오는데 사용
2) OUTER JOIN
- 두 테이블 간에 일치하지 않는 행도 반환
[설명]
INNER JOIN은 두 테이블 간에 일치하는 행만을 반환하는 반면, OUTER JOIN은 일치하지 않는 행도 결과에 포함시킵니다.
14. LEFT OUTER JOIN, RIGHT OUTER JOIN에 대해서 설명해주세요.
A, B 테이블이 있을때 LEFT OUTER JOIN을 하면 A 테이블의 모든 결과와 A,B 교집합된 결과를 가져옵니다. A 테이블의 결과중 B 테이블과 일치하는 결과가 없다면 NULL 값으로 채워집니다.
RIGHT OUTER JOIN은 LEFT OUTER JOIN과 반대 방향으로 적용됩니다.
OUTER JOIN
(1) LEFT OUTER JOIN
- 왼쪽 테이블의 모든 행을 가져오며, 오른쪽 테이블과 일치하는 행이 있는 경우에만 오른쪽 테이블의 값이 포함된다.
(2) RIGHT OUTER JOIN
- 오른쪽 테이블의 모든 행을 가져오며, 왼쪽 테이블과 일치하는 행이 있는 경우에만 왼쪽 테이블의 값이 포함된다.
(3) FULL OUTER JOIN
- 양쪽 테이블의 모든 행을 가져오며, 일치하지 않는 행은 NULL 값으로 채워진다.
[설명]
LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN의 세 가지 유형이 있으며, 각각 왼쪽, 오른쪽, 또는 양쪽 테이블의 모든 행을 가져옵니다. OUTER JOIN은 일치하지 않는 행을 NULL 값으로 채워 넣어 연결된 테이블 간의 빈 값을 채우는 데 유용하게 사용됩니다.
15. CROSS JOIN에 대해서도 설명해주세요.
CROSS JOIN을 하면 두 테이블의 모든 가능한 조합 결과를 생성합니다.
두 테이블의 각 레코드 수를 곱한 개수 만큼 결과가 나옵니다.
CROSS JOIN은 카테시안 곱이라고도 부릅니다.
CROSS JOIN
- 각 행이 다른 테이블의 모든 행과 결합
두 테이블 간의 모든 가능한 조합을 생성하는데 사용된다.
- 결과 집합의 행 수 = 왼쪽 테이블의 행 수 * 오른쪽 테이블의 행 수
[설명]
CROSS JOIN은 두 테이블 간의 모든 가능한 조합을 생성하는 데 사용됩니다. 왼쪽 테이블의 각 행이 오른쪽 테이블의 모든 행과 결합되어 결과 집합을 형성합니다. 이를 통해 두 테이블 간의 모든 관계를 확인할 수 있지만, 테이블의 크기가 큰 경우 결과 집합이 매우 크게 증가할 수 있으므로 주의해야 합니다. 보통은 작은 테이블 간에 사용되며, 필요한 경우에만 사용하는 것이 좋습니다.
16. 서브쿼리에 대해서 설명해주세요.
서브쿼리는 SQL안에 포함된 또 다른 SQL입니다.
서브쿼리는 WHERE, SELECT, FROM절에 적용될 수 있습니다.
서브 쿼리
- SQL 쿼리 안에 포함된 또 다른 쿼리
- 외부 쿼리의 조건이나 결과에 의존하는 내부 쿼리를 나타낸다.
- 주로 WHERE, FROM, SELECT, HAVING, INSERT 문의 VALUES 절에서 사용된다.
*이러한 서브쿼리는 데이터베이스에서 복잡한 데이터 추출과 조작을 가능하게 하며,
가독성을 높이고 쿼리의 유연성을 향상시키는데 기여한다.
1) 스칼라 서브쿼리
2) 테이블 서브쿼리
3) 인라인 뷰
17. DROP, TRUNCATE, DELETE에 각각에 대해 설명해주세요. 어떤차이가 있나요?
TRUNCATE는 DDL로 테이블의 모든 데이터를 제거합니다.
수행시 DELETE 처럼 행마다 로그를 남지지 않기 때문에 빠르고 ROLLBACK이 불가능합니다.
AUTO_INCREMENT 값이 초기화 되기도합니다.
DELETE는 DML로 테이블의 튜플을 삭제합니다. 각 행마다 로그를 남겨서 TRUNCATE 보다 느립니다. ROLLBACK을 할 수 있으며 WHERE과 함께 사용하여 특정 튜플만 삭제할 수 있습니다.
DROP은 테이블과 테이블에 포함된 데이터, 인덱스, 트리거 등 테이블과 관련된 모든 정보를 제거합니다.
1) DROP : 테이블 자체를 삭제하고, 테이블 스키마(구조∙데이터∙인덱스 등 모든 것) 완전히 삭제 및 롤백이 불가능하다.
2) TRUNCATE : 테이블의 모든 데이터를 삭제하고, 테이블 스키마만 유지되며 롤백이 불가능하다.
3) DELETE : 테이블에서 특정 조건을 만족하는 행을 삭제하고 테이블 구조는 보존된다. 롤백이 가능하다.
TRUNCATE에 비해 속도는 느리지만, 로그를 남긴다.
18. DISTINCT에 대해서 설명해주세요. 사용해본 경험도 설명해주세요.
DISTINCT를 통해 SELECT 문에서 조회된 중복된 결과를 필터링 할 수 있습니다.
예를들어 주문 테이블에서 회원 정보를 중복되지 않게 조회할때 DISTINCT를 사용할 수 있습니다.
DISTINCT
- 특정 열 집합에서 중복된 값을 제거하여 고유한 값을 반환하는데 사용
[예시 상황]
-- customers: 고객 정보를 저장하는 테이블
-- orders: 주문 정보를 저장하는 테이블
-- order_items: 주문에 속한 제품 정보를 저장하는 테이블
-- products: 제품 정보를 저장하는 테이블
-- 특정 기간 동안 각 고객이 주문한 고유한 제품을 확인하는 쿼리
SELECT DISTINCT
c.customer_id,
c.customer_name,
p.product_id,
p.product_name
FROM
customers c
JOIN
orders o ON c.customer_id = o.customer_id
JOIN
order_items oi ON o.order_id = oi.order_id
JOIN
products p ON oi.product_id = p.product_id
WHERE
o.order_date BETWEEN '시작날짜' AND '종료날짜';
19. SQL Injection 공격이 무엇인지 어떻게 공격을 예방할 수 있는지 설명해주세요.
SQL Injection 공격은 악의적인 사용자가 SQL 구문을 클라이언트단 입력폼에 주입하여 서버의 데이터베이스를 공격하는 것입니다.
실행할 SQL 구문을 미리 Prepared Statement로 정해두고, 사용자가 입력한 값은 파라미터로만 넣어주는 방식을 사용하면, SQL Injection을 통해 비정상적인 쿼리 수행을 방지할 수 있습니다.
SQL Injection
- 해커가 악의적인 SQL 쿼리를 애플리케이션의 입력 필드 or 매개변수에 주입하여, 데이터베이스에 액세스하거나 조작하는 공격이다.
[예방하는 방법]
1) 매개변수화된 쿼리 사용
- 매개변수화된 쿼리를 사용하여, 사용자 입력이 쿼리에 직접 삽입되지 않게끔 한다.
2) 입력 유효성 검사
- 입력 필드에서 허용되지 않는 문자를 거부하거나 필터링
3) 에러 메시지 관리
- 애플리케이션에서 발생하는 에러 메시지를 사용자에게 노출하지 않도록 한다.
20. 알고 있는 SQL 안티패턴이 있다면 설명해주세요.
페이지네이션을 구현한다고 했을때 쿼리를 어떻게 작성해야할까요?
1) SQL 안티패턴
SELECT 쿼리에서 LIKE를 사용할때 와일드카드를 앞뒤로 넣는 방식은 (LIKE %one%)는 테이블 full scan이 되기 때문에 성능이 떨어지기 때문에 사용을 지양해야합니다.
앞에 붙은 와일드 카드로 인해 정렬 기준이 없어져서 인덱스가 사용 되지 않기 때문입니다.
https://engineer-mole.tistory.com/380
(1) SELECT *
- 모든 열을 선택하는 것보다는, 필요한 열만 명시적으로 선택해 사용하는 것이 좋다.
(2) 문자열 연결로 동적 쿼리 생성
- 입력값을 검증하지 않고 쿼리에 포함시키므로, SQL 인젝션 공격에 노출될 수 있다.
- 따라서 파라미터화된 쿼리나 저장 프로시저를 사용하는 것이 좋다.
name = request.getParameter("name");
sql = "SELECT * FROM users WHERE name = '" + name + "'"; -- 안티 패턴
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
name = request.getParameter("name");
sql = "SELECT * FROM users WHERE name = ?"; -- 권장
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, name);
rs = pstmt.executeQuery();
(3) 다중 칼럼 속성
- 하나의 엔티티에 여러 개의 값을 칼럼으로 만들어 대응하는 것
- 문제점
- 검색 : 원하는 정보가 어느 칼럼에 있는지 모두 확인해야 한다.
- 수정 : 여러 칼럼 중 어떤 칼럼을 수정해야 할 지 확인하고 수정해야 한다.
- 일관성 : 여러 칼럼에 중복되는 값이 입력되어 저장되는 것을 예방하기가 어렵다.
- 테이블 잠금 : 칼럼 하나의 값을 수정하기 위해 테이블 전체가 잠금이 설정될 수 있습니다.
- 해결법
- 종속 테이블을 생성해 사용한다.
-- 안티 패턴
CREATE TABLE member (
id INT PRIMARY KEY,
name VARCHAR(10),
hobby1 VARCHAR(10), // 취미1
hobby2 VARCHAR(10), // 취미2
hobby3 VARCHAR(10), // 취미3
);
-- 권장
CREATE TABLE member (
member_id INT PRIMARY KEY,
name VARCHAR(10),
hobby_id INT FOREIGN KEY REFERENCES hobby(hobby_id)
);
CREATE TABLE hobby (
hobby_id INT PRIMARY KEY,
name VARCHAR(10)
);
2) 페이지네이션을 구현하는 SQL 쿼리
LIMIT와 OFFSET을 사용하여 페이지네이션 쿼리를 작성할 수 있습니다.
페이지 번호는 OFFSET을 통해, 페이지에 포함된 행 개수는 LMIT를 통해 표
예를들어 주문 테이블 대상 SELECT 쿼리에서
LMIT가 10이고 OFFSET이 10이라면, 11번째 튜플부터 20번째 튜플까지만 조회할 수 있습니다.
이렇게 하면 10번 페이지에서 10개 행을 보여주는 쿼리가 됩니다.
https://betterdev.tistory.com/17
(1) OFFSET 기반
- Offset과 limit 예약어를 통해 select의 전체 결과 중 일부만 가져오는 방법
SELECT * FROM product LIMIT {페이지 당 자료의 개수} OFFSET {오프셋}
SELECT * FROM product LIMIT 40 OFFSET 0; 1-40 출력
SELECT * FROM product LIMIT 40 OFFSET 40; 41-80 출력
SELECT * FROM product LIMIT 40 OFFSET 80; 81-120 출력
SELECT * FROM product LIMIT 40 OFFSET 120; 121-160 출력
SELECT * FROM product LIMIT 40 OFFSET 160; 161-200 출력
[장점]
- 일반적인 방식으로 쿼리가 복잡하지 않다.
- 다양한 정렬 방식을 쉽게 구현할 수 있다.
[단점]
- 처음 OFFSET 이후의 LIMIT 결과만 받아오는 것이 아니라,
모든 row를 디스크로부터 읽어오고, OFFSET 결과까지 순서대로 진행하며 넘기게 되는 식으로 진행된다.
- 즉, OFFSET을 사용하는 것은 많은 행을 읽어들여오고 이후에 삭제하는 과정을 거치면서
데이터베이스에 많은 부하를 주게 된다.
(2) CURSOR 기반 ( No OFFSET )
- Cursor는 어떤 레코드를 가리키는 포인터이고,
이 cursor가 가리키는 레코드부터 일정 개수만큼 가져오는 방식이다. (where 절 활용)
SELECT * FROM product LIMIT 40 //1~40까지의 값을 반환
SELECT * FROM product WHERE id > {기준값} LIMIT 40;
SELECT * FROM product WHERE id > 40 LIMIT 40; //41~80
SELECT * FROM product WHERE id > 80 LIMIT 40; //81~120
SELECT * FROM product WHERE id > 120 LIMIT 40;//121~160
SELECT * FROM product WHERE id > 160 LIMIT 40;//161~200
(3) 커버링 인덱스
- 인덱스 자체가 필요한 열들을 모두 가지고 있는 인덱스를 의미
- 인덱스만을 사용하여 필요한 데이터를 모두 가져오는 것
'CS > 데이터베이스' 카테고리의 다른 글
| 5주차. DB 트랜잭션, 회복 (0) | 2023.12.04 |
|---|---|
| 4주차. 이상현상, 함수적 종속성, 정규화 (0) | 2023.11.26 |
| 3주차. Index (0) | 2023.11.22 |
| 1주차. 데이터베이스 & 관계형 데이터베이스 기본 개념 (0) | 2023.11.08 |
