우리가 가장 관심있어하는 정보는 테이블 안에 있고, 이 테이블 안의 여러 데이터를 제어하는 것이 우리가 가장 하고 싶어하는 일이다. 이 테이블을 묶어서 그룹핑하고, 이름을 붙인 것이 스키마이고, 이 스키마를 관리하기 위한 것이 데이터베이스이다. 여러개의 데이터베이스를 묶은 것을 클러스터라고도 하고 DBMS라고도 하는데, 이 DBMS를 데이터베이스라고 부르기도 한다.
MySQL에서 데이터를 조회할 때 사용하는 기본 SQL 명령어를 설명해주세요
SELECT
SELECT절은 여러 조건들을 처리한 후 남은 데이터에서 어떤 ROW를 출력할 지 선택하는 역할을 한다.
WHERE
WHERE절은 FROM절에서 읽어온 테이블에서 조건에 맞는 결과만 갖도록 데이터를 필터링한다.
FROM
조회하려는 전체 테이블의 정보를 가져온다.
HAVING
HAVING절은 그룹핑 후 각 그룹에 사용되는 조건절로, HAVING절은 각 그룹에 조건을 걸기 때문에 퍼포먼스가 떨어지는 단점이 있다. 그래서 WHERE에 조건을 쓸 수 있다면 WHERE에 조건을 쓰는게 좋다.
JOIN
JOIN절은 2개나 그 이상의 테이블을 연결하고, 연결한 테이블로부터 필요한 열을 조회할 수 있도록 한다.
ORDER BY
ORDER BY절은 출력할 ROW를 어떤 순서에 맞게 보여줄 지 결정한다.
SQL 명령문의 실행 순서는?
먼저 FROM절에서 조회하려는 전체 테이블의 정보를 가져온다. JOIN이 있다면 다른 테이블과 조인하여 필요한 정보를 가져온다.
WHERE절로 FROM절에서 조회한 테이블에서 필요한 데이터를 필터링한다.
GROUP BY로 선택한 칼럼으로 그룹핑한다.
HAVING으로 그룹핑한 각 그룹에서 조건을 걸어 다시 필터링한다.
SELECT절로 여러 조건들을 처리한 후의 데이터를 가져온다.
ORDER BY를 이용하여 SELECT문에서 가져온 ROW를 어떤 순서에 맞게 보여줄 지 정한다.
LIMIT로 보여줄 ROW의 개수를 정한다.
'Primary Key'와 'Foreign Key'의 차이와 각각의 역할에 대해 설명해주세요.
PK
Primary Key는 데이터베이스 테이블의 각 레코드를 고유하게 식별하는 역할을 합니다.
한 테이블에는 하나의 PK만 존재할 수 있으며, 이 PK는 해당 테이블의 모든 레코드에 대해 고유해야 합니다.
PK는 NULL이 될 수 없습니다.
PK로 지정된 컬럼은 클러스터링의 기준으로 사용되어, 데이터가 저장되는 방식을 결정합니다.
FK
Foreign Key는 다른 테이블의 PK를 참조하여 두 테이블 간의 관계를 설정하는 역할을 합니다.
FK로 지정된 필드 혹은 필드 셋의 값은 참조하는 PK에 있는 값 중 하나여야 합니다. 이 규칙은 참조 무결성의 제약 조건을 만족시키기 위한 것입니다.
FK는 NULL일 수도 있으며, 참조하는 PK에 없는 값을 가질수도 있습니다.
차이점
PK는 한 테이블 내에서 각각의 ROW / RECORD를 고유하게 식별하기 위해 사용되며, FK는 한 테이블과 다른 테이블의 관계를 구축하기 위해 사용됩니다.
PK는 같은 컬럼 안의 모든 레코드가 UNIQUE해야하는 반면 FK는 같은 컬럼 안의 레코드가 중복될수도 있고, NULL이나 참조하는 PK의 ROW에 해당하지 않는 값을 가질수도 있습니다.
MySQL에서 'JOIN'이란 무엇이며, 'INNER JOIN'과 'LEFT JOIN'의 차이점은 무엇인가요?
JOIN은 두 개 이상의 TABLE에서 ROW를 결합하는 데 사용되는 연산이며, 주로 테이블 간에 관계를 맺고 있을 때 사용됩니다. JOIN의 형태는 여러개지만, 가장 기본적인 형태는 INNER JOIN과 LEFT JOIN입니다.
INNER JOIN
INNER JOIN은 두 테이블 간의 교집합을 반환합니다.
즉, 조인 조건에 일치하는 레코드만 결과로 반환합니다.
예를 들어 A라는 테이블과 B라는 테이블이 있어서 INNER JOIN을 하면, A와 B 모두에 들어있는 데이터만 반환합니다.
LEFT JOIN(LEFT OUTER JOIN)
LEFT JOIN은 첫 번째 테이블의 모든 레코드와 일치하는 두 번째 테이블의 레코드를 반환합니다.
오른쪽(두 번째) 테이블에서 일치하는 값이 없을 경우 NULL을 반환합니다.
정규화(Normalization)란 무엇이며, 왜 중요한가요?
NORMALIZATION은 테이블의 각 레코드의 중복을 최소화하고, 데이터 구조를 효율적으로 만드는 과정입니다. NORMALIZATION을 통해 데이터의 무결성(INTEGRITY)와 일관성(CONSISTENCY)을 유지할 수 있습니다.
정규화의 단계는 1NF, 2NF, 3NF 등이 있지만 너무 많은 정규화를 사용할 경우 성능하락이 동반될 수도 있으므로 성능과 무결성 사이에서 적절한 균형을 찾아야 합니다.
mysql:latest 이미지를 기반으로 하는 mysql-container라는 이름을 가진 container를 띄울 거야. -e라는 옵션으로 environment variable(환경 변수)를 줄 건데, MYSQL_ROOT_PASSWORD는 로 줄거야. -d라는 옵션으로, 백그라운드에도 이 컨테이너를 계속 돌릴거야. (dispatch) -p라는 옵션으로, host OS의 3306번 포트와 컨테이너의 3306번 포트(mySql 기본 포트)를 포트포워딩 해줘.
<password>에는 꼭 기억하기 쉬운 패스워드를 넣도록 하자.
이제, 설치가 되었는지 확인하자.
$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
47d2686d7c9b mysql:latest "docker-entrypoint.s…" 7 minutes ago Up 7 minutes 0.0.0.0:3306->3306/tcp, 33060/tcp mysql-container
잘 설치가 되었음을 확인할 수 있다.
이제 다음 명령어를 한 번에 실행해보자.
Container를 띄워서 bash shell을 실행하고,
mysql root 계정에 접속해보자.
$ docker exec -it mysql-container bash
bash-4.4# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.1.0 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql >
query를 입력할 수 있는 라인이 뜨면, 정상적으로 접속한 것이다.
default로 생성된 DB를 확인해보자.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
총 4개의 DB가 디폴트로 생성되어 있음을 확인할 수 있다.
이제, 우리가 사용할 database를 만들어보자.
나는 회사와 관련된 스키마를 만들고 테스트 할 것이므로, company라는 DB를 만들었다.
mysql> CREATE DATABASE company;
Query OK, 1 row affected (0.00 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| company |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
이제, 이 company를 이용해야 하므로 다음과 같은 명령어를 준다.
mysql > use company;
이제, 테이블을 만들어보자.
mysql> create table DEPARTMENT(
-> id INT PRIMARY KEY,
-> name VARCHAR(20) NOT NULL UNIQUE,
-> leader_id INT
-> );
Query OK, 0 rows affected (0.05 sec)
세미콜론(;)을 넣지 않으면 자동으로 shell에서 줄바꿈을 지원한다.
처음으로는 '부서(department)'에 관한 테이블을 만들었다.
mysql> show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| DEPARTMENT |
+-------------------+
1 row in set (0.00 sec)
테이블이 잘 만들어진 것을 확인할 수 있다.
그렇다면, 추가적으로 workbench에서도 이 내용이 반영되었을까?
WORKBENCH에서 DATABASE 확인하기
docker-mysql이라는 커넥션을 만들고 연결한 다음,
아까 host의 3306번 포트와 container의 3306번 포트를 연결시켰으므로 포트에 3306 옵션을 주고 들어간다.
쿼리 콘솔에서 select문으로 테이블을 확인해보면, 다음과 같이 잘 만들어졌음을 알 수 있다.
그럼 반대로, workbench에서 실행한 내용도 container에 잘 들어 갈까?
내가 결국 만들 db는 위와 같은 형태를 띄고 있으므로,
다음은 employee TABLE을 만들어보자.
CREATE TABLE EMPLOYEE(
id INT PRIMARY KEY,
name VARCHAR(30) NOT NULL,
birth_date DATE,
sex CHAR(1) CHECK(sex in ('M', 'F')),
position VARCHAR(10),
salary INT DEFAULT 5000000,
dept_id INT,
FOREIGN KEY(dept_id) references DEPARTMENT(id)
on delete SET NULL on update CASCADE,
CHECK(salary >= 5000000)
);
QUERY CONSOLE에서 위 SQL을 입력했고, 테이블까지 잘 만들어졌다.
이제 CLI에서 내용이 잘 실행되었는지를 확인해보자.
mysql> show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| DEPARTMENT |
| EMPLOYEE |
+-------------------+
2 rows in set (0.00 sec)
잘 적용되었음을 확인할 수 있다. 과연 내용들도 잘 만들어 졌을까? DESC(DESCRIBE) TABLE 명령어로 테이블 구조나 attributes를 확인할 수 있다.
mysql> DESC EMPLOYEE
-> ;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(30) | NO | | NULL | |
| birth_date | date | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| position | varchar(10) | YES | | NULL | |
| salary | int | YES | | 5000000 | |
| dept_id | int | YES | MUL | NULL | |
+------------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
그 중, 2023년 현재는 아직도 RDB의 전성시대이며, 그 중 Oracle, MySQL, MsSQL에 이어
4위를 차지하고 있는 PostgreSQL은 왜 사용하고 있는지 알아보고, 어떻게 사용하는 지 알아보자.
Oracle, MySQL이라는 거함이 있고, MongoDB같은 NoSQL이 아님에도 불구하고,
PostgreSQL은 어떤 장점이 있어서 사용하는 것일까?
Chat GPT는 다른 rdbms에 비해 PostgreSQL이 가진 장점을 다음과 같이 요약한다.
고급 기능 및 SQL 규정 준수: PostgreSQL은 풍부한 고급 기능 세트와 높은 수준의 SQL 규정 준수로 유명합니다. 많은 고급 데이터 유형, 인덱싱 방법 및 전체 텍스트 검색, JSON 지원 및 지리적 데이터 유형과 같은 기능을 지원합니다. 프로젝트에 복잡한 쿼리가 필요하거나 특수 데이터 유형으로 작업해야 하는 경우 PostgreSQL이 더 나은 선택일 수 있습니다.
ACID 준수: PostgreSQL은 ACID(Atomicity, Consistency, Isolation, Durability) 준수를 강조합니다. 강력한 트랜잭션 지원을 제공하며 이는 정확한 데이터 무결성과 안정성이 필요한 애플리케이션에 중요할 수 있습니다.
확장성: PostgreSQL의 확장 시스템을 사용하면 사용자 정의 함수, 연산자 및 데이터 유형을 추가할 수 있습니다. 이는 특정 애플리케이션 요구 사항에 맞게 데이터베이스 기능을 확장해야 할 때 유리할 수 있습니다.
동시성 제어: PostgreSQL의 동시성 제어 처리는 종종 찬사를 받습니다. MVCC(Multi-Version Concurrency Control)를 사용하여 서로를 과도하게 차단하지 않고 여러 트랜잭션이 동시에 발생할 수 있도록 합니다. 이것은 높은 수준의 동시 읽기 및 쓰기 작업이 있는 애플리케이션에 특히 유용할 수 있습니다.
복잡한 쿼리에 대한 성능: 여러 조인 또는 대규모 데이터 세트와 관련된 복잡한 쿼리가 있는 시나리오에서 PostgreSQL의 쿼리 최적화 프로그램은 종종 보다 정교하고 효율적인 쿼리 계획을 생성할 수 있는 것으로 간주됩니다.
ACID를 지키거나 MVCC같은 경우는 다른 rdbms도 지원하고 있으므로,
MySQL이나 Oracle보다 조금 더 복잡한 쿼리를 효율적으로 다룰 수 있고, 복잡한 데이터도 특수 데이터로 다룰 수 있는것이 장점인 모양이다.
그러나 fetch join은 연관된 정보를 미리 다 땡겨옴으로서 오히려 단순 조회에서는 성능이 더 안 나올때도 있다.
예를 들어 다음의 엔티티 테이블이 있다고 생각해보자.
[Project] 1 : N [Project_User] N : 1 [User]
Project와 User의 다대다 관계를 해결하기 위해 일대다 관계로 두번 나누었고, 행위엔티티를 기록하기 위해 Project_User라는 테이블을 만들어 주었다.
그리고 User에 있는 id와 연관된 모든 project를 가지고 오고 싶다고 생각해보자.
JPQL로 나타내면
"select p from Project p join p.projectUserList pu where pu.user.userId = :userid"일 것이다.
그러나 나는 여기에 join fetch를 걸어 주어서 다음과 같은 JPQL문이 나가게 되었다.
"select p from Project p join fetchp.projectUserList pu where pu.user.userId = :userid"
join fetch를 걸면 쿼리가 2방이 나가게 된다. 살펴보자.
Hibernate:
select
...
projectuse1_.project_id as project_2_5_1_,
projectuse1_.user_id as user_id3_5_1_,
projectuse1_.project_id as project_2_5_0__,
projectuse1_.id as id1_5_0__
from
project project0_
inner join
project_user projectuse1_
on project0_.project_id=projectuse1_.project_id
where
projectuse1_.user_id=?
Hibernate:
select
...
from
users user0_
where
user0_.user_id=?
project에 대한 정보와 projectuser에 대한 정보를 select하는 쿼리와 동시에 projectuser까지 전부 조회해버리는 쿼리가 나가서 결국 user에 대한 정보까지 긁어오는 것을 볼 수 있다.
그러나 user까지 조회할 것이 아니라면 단순히 join만 걸어주면 쿼리를 훨씬 절약하게 된다.
join만 걸고 나가는 쿼리는 다음과 같다.
Hibernate:
select
...
from
project project0_
inner join
project_user projectuse1_
on project0_.project_id=projectuse1_.project_id
where
projectuse1_.user_id=?
단순히 프로젝트에 대한 정보만 가지고 오는 것을 볼 수 있다.
물론 이 조회에서 더 나아가서 user info까지 말아서 데이터를 전송해야 하는 입장이면 join fetch가 맞다.
프로젝트 내의 jpql로 이루어진 조회 / 수정 쿼리들을 Querydsl을 이용한 쿼리로 migration하는 날
오늘 갑자기 CQS 원칙을 지키고 싶어져서 다음과 같이 Repository를 만들어 주었다.
repository
|-ProjectRepository
|-ProjectViewRepositoryCustom
|-ProjectViewRepositoryImpl
그리고 ProjectRepository 안의 userId로 projectResponseDto를 찾는 다음과 같은 쿼리도 변경해주어야 했다.
- ProjectRepository
@Query("select new com.ddalggak.finalproject.domain.project.dto.ProjectBriefResponseDto(p.projectId, p.projectTitle, p.thumbnail) from Project p join p.projectUserList pu where pu.user.userId = :userId")
List<ProjectBriefResponseDto> findAllinDtoByUserId(Long userId);
분명히 연관관계도 문제가 없었고, 모든게 잘 되었다고 생각했는데 뜻하지 않은 오류를 만났다.
org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'projectController' defined in file [~/ProjectController.class]: Unsatisfied dependency expressed through constructor parameter 0; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'projectService' defined in file [~/ProjectViewRepositoryCustom.findProjectAllByUserId(java.lang.Long)! No property 'userId' found for type 'Project'
ProjectViewRepositoryCustom 내부에 있는 findProjectAllByUserId 메소드의 파라미터로 받는 userId가 Project 내부에 있지 않아 UnsatisfiedDependencyException이 걸린 것이었다.
아니 그럼 Querydsl에서는 join문 못쓰나? 라고 생각해봤지만 그럴 리가 없었다.
그렇다고 user의 id값을 projectdto에 넣어주는것도 말이 되지 않았다.
그래서 Project 안에 있는 ProjectUser라는 행위 엔티티에서 User를 뽑아내어 어떻게든 UserId와 연결지어주려는
장장 3시간의 혈투가 시작되었다.
join문을 걸었다가 inner join을 걸었다가 join on을 걸었다가 참 많은 삽질 끝에 메소드 구현체를 지워봤는데도 똑같은 오류가 떴다.
