SQL
SQL은 Structed Query Language의 약자로 데이터 관리 시스템인 DBMS에서 데이터를 조작하고 조회하기 위해 사용하는 언어이다. DBMS의 종류는 다양한데 이러한 DBMS 벤더들이 각자의 언어와 문법만 고집한다면 사용자들은 DBMS 사용이 어려워진다. 따라서 ANSI는 표준이 되는 SQL을 만들었고 이를 ANSI SQL이라 부른다. 이 ANSI SQL은 현대의 대부분의 DBMS에서 작동한다. 따라서 학습 시간을 줄여주고 벤더 변경시 변경 비용이 적다는 장점이 있다.
SQL을 프로그래밍 언어와 비교하면, SQL은 데이터베이스 관리를 위한 언어이며 데이터 조작을 위한 목적으로 사용되지만, C언어 같은 프로그래밍 언어는 다양한 프로그래밍 영역에서 사용되는 일반 목적의 프로그래밍 언어이다.
작성한 SQL이 실행되는 과정
- 개발자가 SQL 쿼리문을 작성한다.
- 작성된 SQL은 DBMS로 전송된다. 예를 들어 스프링부트 프로젝트인 경우 application.yml 파일에 설정한 정보를 토대로 연결된 DBMS로 전송된다.
- SQL 파싱: DBMS는 전송된 SQL을 분석한다. 이 단계에서 쿼리의 구조와 문법 검사를 진행한다. (MySQL이 쿼리를 실행하는 과정: https://olsohee.tistory.com/117)
- 최적화: DBMS는 분석된 쿼리를 최적화하여 실행 계획을 수립힌다. 이 과정에서 쿼리를 효율적으로 실행하기 위한 인덱스 사용 여부, 테이블 간 조인 순서 등이 결정된다.
- 실행: 최적화된 실행 계획에 따라 쿼리를 실행한다.
- 결과 반환: 쿼리 실행이 완료되면 실행 결과를 반환합니다. 이 결과는 일반적으로 개발자가 지정한 형식에 따라 테이블, 레코드, 필드 등의 형태로 제공된다.
DML, DDl, DCL
DML(Data Manipulation Language)
데이터 조작어로, 데이터 조회, 삽입, 수정, 삭제 등의 명령어를 말한다.
- SELECT: 데이터 조회
- INSERT: 데이터 삽입
- UPDATE: 데이터 수정
- DELETE: 데이터 삭제
DDL(Data Definition Language)
데이터 정의어로, 테이블 같은 데이터 구조를 정의하는데 사용되는 명령어이다.
- CREATE: 테이블 생성
- ALTER: 테이블 수정
- DROP: 테이블 삭제
- TRUNCATE: 테이블 초기화
DCL(Data Control Language)
데이터베이스에 접근하고 객체들을 사용하도록 권한을 주고 회수하는 명령이다.
- GRANT: 특정 데이터베이스 사용자에게 특정 작업에 대한 수행 권한을 부여
- REVOKE: 특정 데이터베이스 사용자에게 특정 작업에 대한 수행 권한을 박탈, 회수
- COMMIT: 트랜잭션 작업 저장
- ROLLBACK: 트랜잭션 작업 취소 후 복구
참조 무결성과 CASCADE 설정
참조 무결성은 데이터베이스 상의 참조가 모두 유효함을 의미한다. 즉, 데이터베이스에서 하나의 속성이 다른 테이블의 속성을 참조하고 있다면, 참조한 해당 속성이 반드시 존재해야 한다. 참조 무결성을 위해 자신의 PK를 참조하고 있는 FK가 있다면, 해당 PK는 수정이나 삭제가 불가하다. 해당 PK를 참조하고 있는 FK가 있기 때문에 참조 무결성을 보장하기 위함이다.
그렇다면 어떻게 하면 PK 값을 수정하거나 해당 레코드를 삭제할 수 있을까? 한 가지 방법으로는 해당 PK를 참조하고 있는 FK를 모두 NULL로 만들어 참조를 끊은 후 PK를 수정/삭제하면 된다. 그런데 만약 FK에 NOT NULL 제약 조건이 걸려있으면 이 방법은 불가하고, 또 PK를 참조하는 FK를 꼼꼼이 찾아야 하므로 위험한 방법이다.
이럴 때 사용하는 옵션이 CASCADE 옵션이다. PK에 CASCADE 옵션을 주면 PK 값을 수정하거나 삭제하는 것이 가능하게 해준다.
- ON UPDATE CASCADE
- 만약 PLAYER 테이블이 TEAM 테이블을 참조할 때 참조되고 있는 PK를 (TEAM 테이블의 ID 칼럼) 1에서 2로 변경하면, 다음과 같이 참조하고 있는 테이블의 FK 값도 1에서 2로 변경된다.
- ON DELETE CASCADE
- 참조되고 있는 레코드를 삭제하면 해당 레코드의 PK를 참조하고 있던 테이블의 레코드도 삭제된다.
- ON DELETE SET NULL
- 사실 참조하고 있던 레코드가 사라진다고 해서 해당 레코드까지 사라질 필요가 없을 수 있다. 따라서 ON DELETE SET NULL을 사용하면 참조하고 있던 레코드가 사라지면 해당 FK를 NULL로 바꿔버린다.
뷰(VIEW)
뷰는 데이터베이스 개체 중의 하나로, 실제 데이터가 저장된 테이블을 볼 수 있게 해주는 개체이다. 뷰를 생성해 놓으면 사용자 입장에서는 테이블이라고 생각하고 사용해도 될 정도로 테이블과 겅의 동일한 개체로 취급된다. 그러나 뷰는 테이블처럼 실제 데이터를 가지고 있지는 않는다. 비유하자면 바탕화면의 바로가기 아이콘과 비슷하다. 뷰는 단순 뷰와 복합 뷰로 나뉘는데, 단순 뷰는 하나의 테이블과 연관된 뷰를 말하고 복합 뷰는 2개 이상의 테이블과 연관된 뷰를 말한다. 뷰를 만드는 형식은 "CREAT VIEW 뷰이름 AS SELECT문;"이다.
그렇다면 테이블을 사용하면 되는데 굳이 뷰를 사용하는 이유는 무엇일까? 그 이유는 보안때문이다. 예를 들어 member 테이블에 회원 아이디, 이름, 주소 뿐만 아니라 보안이 중요한 주민등록번호, 연락처 등이 있다고 가정하자. 뷰를 사용하지 않고 테이블을 대상으로 SELECT 쿼리를 통해 데이터를 조회해오면 주민등록번호, 연락처 등까지 조회될 수 있다. 그러나 뷰를 만들어서 뷰에 보안이 중요한 정보는 담지 않으면, 뷰를 대상으로 SELECT 할 때 보안이 중요한 데이터는 조회되지 않는다. 즉 보안이 중요한 데이터를 제외하여 뷰를 생성하고, 회원 테이블에 접근하지 못하도록 권한을 제한하고, 뷰에만 접근할 수 있도록 권한을 준다면 사용자는 회원의 개인정보 데이터를 조회할 수 없어서 보안을 강화할 수 있다.
SELECT 절의 처리 순서
SELECT 절의 처리 순서는 다음과 같다.
- FROM, JOIN: FROM을 통해 조회할 테이블을 지정한다. 그리고 JOIN을 통해 하나의 가상 테이블로 결합한다.
- WHERE: 테이블에서 조건에 맞는 데이터를 필터링한다.
- GROUP BY: 선택한 칼럼을 기준으로 그룹핑한다.
- HAVING: 그룹핑 후에 각 그룹에 사용되는 조건 절이다. 쉽게 말해 그룹을 필터링한다고 생각하면 된다.
- SELECT: 여러 조건들을 처리한 후 남은 데이터들에서 어떤 열을 출력할지 선택한다.
- ORDER BY: 어떤 열까지 출력할지 정했다면 각 행의 순서를 정렬한다.
- LIMIT: 결과 중 몇개의 행을 보여줄지 선택한다.
SELECT FOR UPDATE
SELECT FOR UPDATE문을 실행하면 LOCK을 획득하고, 해당 세션이 UPDATE 쿼리 후 COMMIT하기 전까지는 다른 세션들이 해당 ROW를 수정하지 못하도록 하는 기능이다.
JOIN
조인은 두 개의 테이블을 서로 묶어서 하나의 결과를 만들어 내는 것을 말한다.
- (INNER) JOIN: 두 테이블을 조인할 때 두 테이블에 모두 저장된 교집합을 말한다.
- OUTER JOIN: 두 테이블을 조인할 때 1개의 테이블에만 데이터가 있어도 된다.
- LEFT OUTER JOIN: 왼쪽 테이블을 기준으로 조인 (즉, 왼쪽 테이블의 모든 값이 출력된다.)
- RIGHT OUTER JOIN: 오른쪽 테이블을 기준으로 조인
- FULL OUTER JOIN: 왼쪽과 오른쪽 테이블의 모든 값 출력
- CROSS JOIN: 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인시키는 기능이다. CROSS JOIN 결과의 전체 행 개수는 두 테이블의 각 행의 개수를 곱한 수이다.
서브쿼리
서브쿼리는 다른 쿼리 내부에 포함되어 있는 SELECT 문을 의미한다. 이때 서브쿼리를 포함하고 있는 쿼리를 외부쿼리라고 부르고, 서브쿼리는 내부쿼리라고도 부른다.
DISTINCT
DISTINCT는 중복을 제거해주는 키워드이다.
SQL Injection
SQL Injection은 말 그대로, 개발자가 만들어 놓은 SQL 쿼리 문에 해커가 데이터를 삽입한 후에 악의적으로 활용하는 것을 의미한다.
예제 1
예를 들어 관리자 페이지에 사용자가 접속하기 전에 현재 사용자가 관리자 권한이 있는지 검증하는 로직이 있다고 가정해보자. 이때 검증 로직은 다음과 같다. 이때 "admin"과 "aaa"가 외부로부터 받은 데이터이다.
SELECT * FROM user WHERE auth = 'admin' AND id = 'aaa';
이때 외부로부터 "im not admin"과 " ' OR '1' = '1 "이라는 데이터가 입력되면 어떻게 될까?
SELECT * FROM user WHERE auth = 'im not admin' AND id = '' OR '1' = '1';
이 경우 OR '1' = '1'로 인해 무조건 참이 된다(OR보다 AND 연산자가 우선순위가 높다). 따라서 보안상 문제가 된다.
예제 2
또 다른 예로 다음과 같은 SQL Injection으로 모든 유저의 정보를 DB에서 삭제시킬 수도 있다. 외부로부터 "admin"과 " '; DROP TABLE user;--'"이라는 데이터가 입력된 상황이다. 즉, 첫 번째 세미콜론으로 SQL문을 종료시키고, 새로운 DROP 구문을 통해 삭제 요청을 보낸 후 - 2개로 기존의 남은 SQL문(')을 주석처리 해버리는 것이다.
SELECT * FROM user WHERE auth = 'admin' AND id = ' ';
DROP TABLE user;--'대응 방안
- 입력 값에 대한 검증 로직을 두어, 특수 문자가 입력되지 않도록 검증한다.
- Prepared Statement나 JPA 등을 사용하면 문자열 형태로 SQL 쿼리문을 구성하지 않을 수 있기 때문에 SQL Injection을 예방할 수 있다.
SQL 안티 패턴
SQL 안티 패턴이란 SQL 쿼리나 데이터베이스 설계에서 흔히 발생하는 잘못된 패턴을 말한다. SQL 안티 패턴은 성능 저하, 데이터 무결성 손상, 보안 취약점 등을 야기할 수 있다.
- SELECT * FROM
- SELECT * FROM은 테이블의 모든 컬럼을 조회하는 쿼리이다. 이 쿼리는 필요하지 않은 컬럼까지 조회하므로 네트워크 부하와 메모리 사용량을 증가시킨다. 또한 테이블 구조가 변경되거나 테이블에서 특정 컬럼이 추가/삭제되면 예상치 못한 결과를 가져올 수 있다.
- 문자열 연결을 통한 동적 쿼리 생성
- 문자열을 연결하여 동적으로 쿼리를 생성하면 SQL 인젝션 공격에 취약하다. 따라서 문자열 대신 파라미터를 사용하는 것이 좋다.
- 잘못된 데이터 타입 사용
- 예를 들어 날짜나 시간을 문자열로 저장하면 날짜나 시간 관련 함수를 사용할 수 없다. 또한 정렬이나 비교도 불가하다. 따라서 데이터의 성격과 범위에 맞는 데이터 타입을 사용하는 것이 좋다.
SQL을 통한 페이지네이션 구현
페이지네이션을 구현하기 위해서는 LIMIT과 OFFSET이라는 키워드를 사용할 수 있다. LIMIT은 가져올 레코드 수를 지정하고, OFFSET은 시작점을 지정하는 키워드이다.
예를 들어 10개의 레코드를 한 페이지로 보여주고 싶다면, 첫 번째 페이지를 가져오는 쿼리는 다음과 같다.
SELECT * FROM post LIMIT 10 OFFSET 0;
그리고 두 번째 페이지를 가져오는 쿼리는 다음과 같다.
SELECT * FROM post LIMIT 10 OFFSET 10;
그런데 이 방법에는 한 가지 문제가 있다. 바로 성능이다.
- MySQL은 OFFSET을 사용하면 처음부터 해당 위치까지 모든 레코드를 읽는다. 따라서 1000번째 포스트를 읽어오려면 0번부터 999번의 포스트까지 읽는 과정이 생긴다.
- 또한 특정 페이지에서 다음 페이지로 이동하는 도중에 그 사이에 새로운 행이 추가된다면, 추가된 행 수만큼 데이터가 중복 조회될 수도 있다.
따라서 이런 문제를 해결하기 위해 WHERE 절과 인덱스를 사용하는 방법이 있다. 인덱스를 사용하면 MySQL은 인덱스를 통해 필요한 레코드만 읽어오므로 성능이 훨씬 좋아진다. 예를 들어 id 컬럼에 인덱스가 걸려있을 때 첫 번째 페이지 쿼리는 다음과 같다.
SELECT * FROM post WHERE id <= 10 ORDER BY id DESC;
그리고 두 번째 페이지 쿼리는 다음과 같다.
SELECT * FROM post WHERE id <= 20 AND id > 10 ORDER BY id DESC;
정리하자면 OFFSET을 사용하는 방법은 쿼리가 간단하고 직관적이지만 성능이 떨어질 수 있다. 반면 WHERE 절과 인덱스를 사용하는 방법은 쿼리가 복잡해지지만 성능에 더 좋다.
Reference
- https://papimon.tistory.com/90
- https://jaehoney.tistory.com/191
- https://hongong.hanbit.co.kr/sql-%EA%B8%B0%EB%B3%B8-%EB%AC%B8%EB%B2%95-joininner-outer-cross-self-join/
- https://velog.io/@msung99/SQL-Injection-%EB%B3%B4%EC%95%88%EC%9D%98-%ED%97%88%EC%A0%90%EC%9D%84-%EC%9D%B4%EC%9A%A9%ED%95%9C-%EC%95%85%EC%9D%98%EC%A0%81%EC%9D%B8-SQL-%EA%B3%B5%EA%B2%A9%EA%B8%B0%EB%B2%95
- https://velog.io/@yoonuk/%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4-Pagination%EC%9D%84-%EA%B5%AC%ED%98%84%ED%95%98%EB%8A%94-SQL
- https://velog.io/@yoonuk/%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4-Pagination%EC%9D%84-%EA%B5%AC%ED%98%84%ED%95%98%EB%8A%94-SQL
'CS > Database' 카테고리의 다른 글
| [Real MySQL] 8. 인덱스 - B-Tree, B+Tree, Hash 인덱스 (0) | 2024.03.18 |
|---|---|
| [Real MySQL] 8. 인덱스 - 디스크 읽기 방식, 인덱스 (1) | 2024.03.18 |
| 데이터베이스 기본 개념 (0) | 2024.03.06 |
| 관계형 데이터베이스(RDB) vs 비관계형 데이터베이스(NoSQL) (0) | 2024.03.04 |
| [Real MySQL] 5. 트랜잭션과 락 (2) | 2024.02.27 |