SQL(Structured Query Language)은 관계형 DB에서 데이터를 정의, 조작, 제어하기 위한 표준 프로그래밍 언어이다.
영어가 주 언어인 사람에서 봤을때 간결하고 명시적인 특성 덕분에 이해와 접근에 쉬운 장점이 있다.
C언어와의 차이점
1. 목적
SQL: DB와 상호작용 하기 위해 설계된 프로그래밍 언어
C언어: 범용 프로그래밍 언어로서, 다양한 종류의 SW를 개발하기 위한 도구(운영체제, 임베디드 시스템, 게임 등)
2. 선언적 vs 절차적
SQL: 선언적 언어로, 원하는 결과가 무엇인지 지정하고 컴퓨터가 필요한 단계를 파악한다.
C언어: 절차적 언어로, 원하는 결과를 달성하기 위해 개발자가 수행해야 하는 단계가 존재
❓ 선언적.. 절차적.. 내가 적긴 했지만 이해가 100% 되진 않는다 ㅎ
3. 메모리 관리
SQL: DBMS가 직접 처리한다.
C언어: 개발자가 메모리 관리를 직접 다룬다.
mysql 기준 SQL 실행과정
1. SQL 쿼리 제출
2. 쿼리 캐시 방문(Mysql 8.0 부터 제거됨)
- 동일 SQL 실행시 이전 결과 즉시 반환
💡 제거된 이유는 테이블의 Data가 변경되면 Query Cache의 데이터를 모두 삭제해야 했다. 이러한 것이 심각한 동시 처리 성능 저하를 유발하여 Mysql 8.0 부터 제거되었다.
[추가]
데이터 변경시 쿼리 캐시가 스캔되는데 쓰기 동시성이 있는 경우 쿼리 캐시 스캔 및 제거를 대기해야 하기 때문에 병목현상이 발생할 수 있다. 즉, "쿼리 캐시 잠금 대기" 상태가 많이 발생된다.
2. Query Parsing
- SQL문 문법 검사 후 정상 SQL문일시 Parse Tree 생성
3. 의미 분석(Pre Processor)
- SQL 문이 작업 대상 테이블에 수행가능한 구문인지 확인
- 테이블과 칼럼이 있는지 확인
- SQL 문을 수행할 수 있는 권한 확인
4. 실행계획 생성
- Parse Tree를 기반으로 옵티마이저가 실행계획을 생성한다.
5. 쿼리 실행
- Query Execution Engine으로 옵티마이저에 의해 생성된 실행계획대로 Query를 실행
- 실제 데이터를 보관하는 스토리지 엔진과 지속적으로 Handler API Calls를 주고받음
6. 스토리지 엔진 실행
- 데이터를 디스크에 저장하거나 읽어오는 역할을 담당
7. 결과 반환
Client에게 응답
DDL, DML, DCL
DDL (Data Define Language)
데이터베이스 구조를 생성, 수정, 삭제하는데 사용되는 SQL 명령어
CREATE
- 데이터베이스 또는 객체를 만드는데 사용
DROP
- 객체를 삭제하는데 사용
ALTER
- DB의 구조를 변경
TRUNCATE
- 테이블에서 모든 데이터를 제거
- 롤백 불가능
COMMENT
- 데이터에 주석을 추가
RENAME
- 객체 이름을 변경
DML (Data Manipulation Language)
데이터 조작을 처리하는 SQL 명령어
SELECT
- 데이터 조회
INSERT
- 데이터 삽입
UDDATE
- 데이터 수정
DELETE
- 데이터 삭제
- 커밋 이전에 롤백 가능,
- 인덱스 삭제 X 및 auto_increment 초기화 X
DCL (Data Control Language)
DB 접근 혹은 객체 권한 제어
GRANT
- 액세스 권한 부여
REVOKE
- 액세스 권한 철회
외래키로 연결된 데이터들이 일관성을 유지할수 있도록 하기 위해서 foreign key constraints라는 것이 있다.
종류는 아래와 같다.
CASCADE
부모 테이블의 데이터에 delete 또는 update 명령어를 적용할 때, 자동적으로 자식 테이블에 매치되는 데이터에도 delete 또는 update를 반영한다.
ON DELETE CASCADE
- 부모 테이블에서 데이터를 삭제할 경우 연결된 자식테이블의 데이터도 함께 삭제된다.
ON UPDATE CASCADE
- 부모 테이블에서 데이터가 수정될 경우 연결된 자식테이블의 데이터도 함께 수정된다.
RESTRICT
부모 테이블의 데이터를 delete 또는 update 명령어를 적용할 때, 해당 데이터를 참조하고 있는 개체가 존재하면 명령이 취소된다.
SET NULL
부모 테이블의 데이터를 delete 또는 update 명령어를 적용할 때, 해당 데이터를 참조하고 있는 모든 개체들의 값은 NULL로 세팅한다.
NO ACTION
부모 테이블의 데이터를 delete 또는 update 명령어를 적용해도 변동이 생기지 않는다.
VIEW
DB에 존재하는 가상 테이블이다.
실제 테이블처럼 행과 열을 가지고 있지만 실제로 데이터를 저장하고 있지는 않고, 데이터를 보여주는 역할만 수행한다.
SQL로 생성한 데이터를 뷰를 통해 가상 테이블을 만들어 편리하고 빠르게 데이터 조회 가능
VIEW 장점
- 복잡한 쿼리를 단순화하고 재사용 가능하다.
- 특정 정보만 제공할 수 있어 보안성이 높다.
VIEW 단점
- 한번 정의된 뷰는 변경할 수 없다.
- 독립적인 인덱스를 가질 수 없다.
- 뷰로 구성된 내용에 대한 삽입, 삭제, 수정에 제약이 따른다.
❓ 실무에서 뷰를 종종 활용할까? 우선 나는 써본적이 전무하다..
SELECT 실행 순서
from and join -> where -> group by -> having -> select -> ->distinct -> order by -> limit
실행순서가 중요한 이유
실행순서를 모르면 쿼리를 제대로 작성하기 어렵다.
OrderBy 절에서 Alias 사용
SELECT CONCAT(first_name, last_name) AS full_name
FROM user
ORDER BY full_name;- ORDER BY 절은 SELECT 절보다 뒤에 실행되기 때문에 SELECT 절의 결과를 사용할 수 있다.
Where 절에서 Alias 사용
SELECT CONCAT(first_name, last_name) AS full_name
FROM user
WHERE full_name = 'VioletBeach';- where 절은 select 절보다 먼저 실행된다. 따라서 SELECT문에서 사용한 AS를 활용할 수 없다.
※ SQL을 실행순서 대로 작성하면 튜닝할 수 있는 요소를 발견할 가능성이 높아진다.
SELECT FOR UPDATE란
가장 먼저 LOCK을 획득한 SESSION에서 조회된 데이터들이 update 후 커밋되기 이전까지 다른 세션들은 해당 데이터들을 수정하지 못하도록 하는 기능이다. 읽기는 가능하다
옵션
SELECT FOR UPDATE NOWAIT
- LOCK인 상태면 exception 처리한다.
SELECT FOR UPDATE WAIT 5(초)
- LOCK인 상태면 입력한 시간(초단위) 만큼 기다렸다가 LOCK이 풀리지 않으면 exception 처리한다.
group by
지정된 열에서 동일한 값을 가진 행을 하나의 그룹으로 묶어주는데 사용하는 SQL 명령어
COUNT(), MAX(), MIN(), SUM(), AVG() 등 집계 함수와 함께 사용된다.
group by 질의에서 select 할수 있는 칼럼은 group by에 나열된 칼럼 혹은 집계함수이다.
아래는 에러다. GROUP BY에 사용되지 않은 “P.Name”을 SELECT하려고 했기 때문이다.
SELECT P.Name, P.Player, SUM(F.Amount)
FROM FINES F
INNER JOIN PLAYERS P ON F.Playernr = P.Playernr
GROUP BY P.PlayerMYSQL에서는 sql_mode의 값을 "ONLY_FULLE_GROUP_BY"로 변경하면 잘못된 GROUP BY를 사용하는 것을 방지할 수 있다.
sql_mode 값이 "ONLY_FULLE_GROUP_BY" 이고 위 구문을 실행하고 싶으면 any_value를 지정하면 된다. 그러면 P.Player은 아무거나 1개의 값으로 대표되어 표기되고 쿼리 실행이 된다.
SELECT P.Name, any_value(P.Player), SUM(F.Amount)
FROM FINES F
INNER JOIN PLAYERS P ON F.Playernr = P.Playernr
GROUP BY P.Player
※ having 절은 group by의 결과를 특정 조건으로 필터링하는 SQL 명령어
order by
데이터를 지정된 칼럼으로 정렬하기 위한 SQL 명령어다.
디폴트는 오름차순(asc)이다.
오름차순 및 내림차순 정렬을 할 수 있다.
다중 ORDER BY 조건을 줄 수 있다.
칼럼 번호로도 정렬할 수도 있다.
JOIN은 둘 이상의 테이블을 연결해서 원하는 데이터를 조회하는 방법이다.
INNER JOIN과 OUTER JOIN
INNERT JOIN
- 두 테이블을 조인할 때, 두 테이블에 모두 지정한 열의 데이터가 있어야 한다.
- 일반적으로 조인으로 부르면 내부 조인을 의미한다.
OUTER JOIN
- 두 테이블을 조인할 때, 한쪽에만 데이터가 있어도 결과가 나온다
LEFT OUTER JOIN
- 왼쪽 테이블의 모든 값이 출력되는 조인
RIGHT OUTER JOIN
- 오른쪽 테이블의 모든 값이 출력되는 조인
FULL OUTER JOIN
- 왼쪽과 오른쪽 테이블의 모든 값이 출력되는 조인
- LEFT OUTER JOIN과 RIGHT OUTER JOIN을 합친 결과
CROSS JOIN
- 양쪽 테이블의 모든 행을 조인 시키는 조인
- 전체 행의 개수는 두 테이블의 각 행의 개수를 곱한 수
- 카티션 곱이라고도 한다
SELF JOIN
- 자기 자신을 참조하는 조인이다.
- 별도 문법이 없고 INNER JOIN시 같은 테이블을 조인하면 된다.
- 사용예시
- 각 파트너의 번호 대신 이름을 알고 싶으면?
- 파트너의 정보도 같은 테이블에 존재하기 때문에 SELF 조인을 쓰면 된다.
SELECT A.ID, A.Name, A.Partner, B.Partner PartName
FROM Star A JOIN Star B
ON A.Partner = B.ID
서브쿼리
서브쿼리란 다른 쿼리 내부에 포함되어 있는 SELECT 문을 의미한다.
괄호 안에 있는 쿼리를 서브 쿼리라 말한다.
※ ORDER BY를 사용할 수 없다.
서브쿼리의 위치에 따른 명칭
SELECT col1, (SELECT ...) -- 스칼라 서브쿼리(Scalar Sub Query): 하나의 컬럼처럼 사용 (표현 용도)
FROM (SELECT ...) -- 인라인 뷰(Inline View): 하나의 테이블처럼 사용 (테이블 대체 용도)
WHERE col = (SELECT ...) -- 중첩 서브쿼리: 하나의 변수(상수)처럼 사용 (서브쿼리의 결과에 따라 달라지는 조건절)
스칼라 서브 쿼리
SELECT 문에 사용하는 서브쿼리
하나의 레코드만 조회가 가능하다.
다른 테이블에서 값을 가져올때 사용
인라인 뷰
FROM 문에 사용하는 서브쿼리
반드시 AS 별칭을 지정해줘야 한다.
중첩 서브쿼리
WHERE 문에 나타나는 서브쿼리
서브 쿼리가 사용 가능 한곳
- SELECT
- FROM
- WHERE
- HAVING
- ORDER BY
- INSERT문의 VALUES 부분 대체제
- UPDATE문의 SET 부분 대체제
DROP, TRUNCATE, DELETE
DROP
개체를 삭제하는 방식
롤백 불가능
데이터, 인덱스, 권한 제거
TRUNCATE
테이블에서 모든 데이터를 제거
테이블 명세만 남기고 데이터가 존재하던 공간을 모두 제거하기 위한 명령어
- AUTO_INCREMENT 1로 재설정
롤백 불가능
DELETE
테이블에서 단일 혹은 다중 데이터를 제거
AUTO_INCREMENT 1로 재설정 X
커밋되기 전이면 롤백 가능
DISTINCT란 조회시 중복되는 값을 제거하고 조회한다.
중복 기준은 SELECT문으로 조회한 모든 칼럼들이다.
데이터베이스가 각 행을 처리하고 고유한지 여부를 확인해야 하므로 를 사용하면 DISTINCT특히 대규모 데이터 세트에서 쿼리를 실행하는 데 걸리는 시간이 늘어날 수 있다는 점에 유의해야 합니다. 따라서 신중하게 사용해야 합니다.
Group By랑의 차이는 Group By는 정렬을 해준다. 집계 함수가 필요하면 Group By를 사용해야 한다.
※ 참고로 Group By도 정렬 하기 싫으면 뒤에 order by null을 붙여주면 된다. MYSQL 에서만 되는거라 한다.
개인적으로는 DB의 데이터 분석시 특정 칼럼의 데이터에 들어갈 수 있는 데이터를 조회할 때 활용한다.
직접 경험은 없지만 DAU(Daily Active User)를 구할때 많이 쓰지 않을까 싶다.
- 접속 OS별 DAU
- 구매 카테코리별 DAU
- 7월에 접속한 유저들의 8월 DAU ( 앞 선 예시보다는 고난도)
Select dt, count(distinct user_id) as DAU
from user_daily
where month in (7,8) ---7,8월 데이터만
group by dt
order by dt
limit 500;
SQL Injection 공격이 무엇인지 어떻게 공격을 예방할 수 있는지 설명해주세요.
DB와 연동된 웹 어플리케이션에서 공격자가 입력이 가능한 폼에 조작된 질의문을 삽입하여 웹 서비스의 DB 정보를 열람 또는 조작할 수 있는 취약점
파라미터 바인딩을 사용하면 안전하다.
파라미터 바인딩을 쓰지 않는 방법
파라미터 바인딩을 쓰는 방법
PrepareStatement의 setString 메서드 내부에서 escape해야 할 문자열이 있으면 적절하게 처리해준다.
Spring Data JPA에서 제공하는 API를 사용하면
내부적으로 파라미터 바인딩을 해주기 때문에 안전하다
알고 있는 SQL 안티패턴이 있다면 설명해주세요.
- 조회시 select * 사용하면 데이터 양이 많아져 네트워크 부하와 메모리 사용량을 증가
=> 필요한 열만 명시적으로 지정한다.
- 문자열 연결로 동적 쿼리 생성시 SQL 인젝션 공격에 취약
=> 파라미터 바인딩 방법을 사용
- 인덱스를 건 칼럼을 가공(칼럼에 함수 사용)하면 인덱스를 타지 않으므로 주의
- SELECT DISTINCT를 남용하면 성능에 좋지 않다.
페이지네이션을 구현한다고 했을때 쿼리를 어떻게 작성해야할까요?
1. offset, limit를 활용한다.
간단하다.
데이터가 많으면 성능에 큰 결함을 불러 온다.
offset + limit 만큼 데이터를 불러온 후 불러온 결과에서 limit 만큼만 보여주기 때문이다.
SELECT * FROM user ORDER BY id desc LIMIT 4000000, 10000;- 4,000,000번째 데이터에서 10,000개를 얻고자하는 쿼리입니다. 이 쿼리는 4,000,000개를 가저온 후 마지막 10,000개 를 리턴한다.
2. JOIN을 활용한 성능 개선
SELECT * FROM user
INNER JOIN
(SELECT id
FROM user
ORDER BY id desc LIMIT 4000000, 10000) as u USING(id);from 서브쿼리에서 최대한 인덱스로 검색해서 id 를 가져온 다음에 JOIN 하는 방식
3. PK와 같은 검색 쿼리로 검색 결과 한정 시키기 (Keyset 페이지네이션)
user table의 id가 10,000 이상 데이터 중 0번째에서 100번째 데이터만 가져오라는 것
마지막으로 읽은 레코드의 ID(혹은 특정값)을 기억하고 그 값보다 큰 레코드를 조회하는 방식
select * from user where id > 10000 limit 100;- 반환된 결과값이 10100 이면
select * from user where id > 10100 limit 100;
단점
데이터가 정렬된 순서로 읽여야 하며, 중간에 레코드가 삽입되거나 삭제되는 경우 문제가 발생할 수 있음
❓ 이 방법 잘만 쓰면 좋을것 같긴 한데... id값이 중간의 빈값없이 무조껀 있어야 하는건가? auto_increment를 쓰지 않으면 못쓰는건가?
https://jaehoney.tistory.com/191
https://kdg-is.tistory.com/241
https://coding-factory.tistory.com/224
https://lookingfor.tistory.com/entry/DB-View-table
https://grandma-coding.tistory.com/entry/MySQL-SELECT-FOR-UPDATE-%EB%9E%80
https://velog.io/@dntjd7701/SQL-GROUP-BY
http://jason-heo.github.io/mysql/2014/03/05/char13-mysql-group-by-usage.html
https://hongcoding.tistory.com/146
https://velog.io/@gillog/MySQL-DISTINCT-GROUP-BY
https://www.youtube.com/watch?v=qzas_-u4Nxk
'외부활동 > CS 면접 끝장내기 - 데이터베이스 1기' 카테고리의 다른 글
| 데이터베이스 5주차 스터디 정리 (0) | 2023.08.19 |
|---|---|
| 데이터베이스 4주차 스터디 정리 (3) | 2023.08.15 |
| 데이터베이스 3주차 스터디 정리 (0) | 2023.08.10 |
| 데이터베이스 1주차 스터디 정리 (0) | 2023.07.25 |