Mysql 심화 정리

# 데이터베이스 설계와 정규화

데이터를 효율적으로 저장하기 위해 데이터베이스를 잘 설계하는 것이 중요하다. 데이터베이스 설계에서 가장 중요한 개념 중 하나는 **정규화(Normalization)**이다. 정규화는 데이터 중복을 줄이고 데이터의 일관성을 유지하도록 테이블을 분리하는 과정이다.

정규화의 단계

1. 1차 정규화(1NF): 모든 열이 원자 값(더 이상 나눌 수 없는 값)만 가지도록 한다.
   • 예: 하나의 셀에 여러 값(전화번호 여러 개)을 넣지 않는다.
2. 2차 정규화(2NF): 기본 키에 대해 부분 종속성이 없는 상태를 만든다. 기본 키의 일부분에만 종속된 컬럼이 없어야 한다.
3. 3차 정규화(3NF): 기본 키에 대해 이행적 종속성이 없는 상태로 만든다. 기본 키가 아닌 열이 다른 비기본 키 열에 종속되지 않도록 한다.

예를 들어, 고객 정보와 주문 정보를 한 테이블에 넣지 않고, 고객 테이블과 주문 테이블을 분리하는 것이 정규화의 예다.

---

# 고급 SQL 쿼리

JOIN

테이블 간 관계를 이용해 데이터를 조회할 때 사용하는 것이 JOIN이다. JOIN에는 여러 종류가 있다.

1. INNER JOIN: 두 테이블에 모두 존재하는 값만 반환한다.

SELECT Customers.Name, Orders.OrderDate
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

 

2. LEFT JOIN: 왼쪽 테이블의 모든 행과, 오른쪽 테이블에 매칭되는 행을 반환한다. 매칭되지 않는 오른쪽 행은 NULL로 표시한다.

SELECT Customers.Name, Orders.OrderDate
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

3. RIGHT JOIN: 오른쪽 테이블의 모든 행과, 매칭되는 왼쪽 테이블의 행을 반환한다.

4. CROSS JOIN: 두 테이블의 모든 가능한 조합을 반환한다. (경우에 따라 조심해서 사용해야 한다.)

# 서브쿼리(Subquery)

서브쿼리는 쿼리 안에 포함된 또 다른 쿼리다. 주로 조건에 따라 데이터를 필터링할 때 사용한다.

SELECT Name 
FROM Customers 
WHERE CustomerID IN (
    SELECT CustomerID FROM Orders WHERE OrderDate > '2024-01-01'
);

# GROUP BY와 HAVING

GROUP BY는 특정 열의 값으로 데이터를 그룹화하고, HAVING은 그룹화된 데이터에 조건을 적용한다.

SELECT CustomerID, COUNT(OrderID) AS TotalOrders
FROM Orders
GROUP BY CustomerID
HAVING TotalOrders > 5;

---

# 인덱스(Index)와 성능 최적화

1. 인덱스(Index)의 개념

인덱스는 테이블의 특정 열에 대해 검색 성능을 향상시키는 자료 구조다. 인덱스를 추가하면 조회 속도가 빨라지지만, 삽입·수정·삭제 작업은 느려질 수 있다.

CREATE INDEX idx_customer_name ON Customers (Name);

• PRIMARY KEY: 기본 키는 자동으로 인덱스가 생성된다.
• UNIQUE INDEX: 중복이 허용되지 않는 인덱스다.
• FULLTEXT INDEX: 텍스트 검색에 사용되는 인덱스다. (MyISAM 또는 InnoDB에서 지원)

2. 실행 계획(EXPLAIN)

EXPLAIN 명령어를 사용해 쿼리의 실행 계획을 확인하고, 인덱스 사용 여부나 병목 지점을 파악할 수 있다.

EXPLAIN SELECT * FROM Customers WHERE Name = 'John Doe';

---

# 트랜잭션(Transaction)과 격리 수준

1. 트랜잭션(Transaction)

트랜잭션은 데이터베이스에서 일련의 작업을 하나의 단위로 묶어 처리하는 것이다. 모든 작업이 성공적으로 완료되거나(Commit), 실패 시 모두 취소(rollback)된다.

START TRANSACTION;
INSERT INTO Orders (CustomerID, OrderDate) VALUES (1, '2024-10-10');
UPDATE Customers SET Balance = Balance - 100 WHERE CustomerID = 1;
COMMIT;

2. 격리 수준(Isolation Levels)

MySQL에서는 동시에 여러 트랜잭션이 실행될 때 데이터 일관성을 유지하기 위해 격리 수준을 설정한다. 격리 수준은 다음과 같다:

1. READ UNCOMMITTED: 다른 트랜잭션의 커밋되지 않은 변경 사항도 읽을 수 있다.
2. READ COMMITTED: 커밋된 데이터만 읽을 수 있다.
3. REPEATABLE READ: 하나의 트랜잭션 내에서 같은 쿼리를 여러 번 실행해도 결과가 동일하다.
4. SERIALIZABLE: 가장 엄격한 수준으로, 모든 트랜잭션을 순차적으로 실행한다.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

---

# 사용자 권한 관리와 보안

MySQL에서는 각 사용자가 특정 작업을 수행할 수 있는 권한을 부여하거나 제한할 수 있다.

1. 사용자 생성 및 권한 부여

CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT ON CustomerDB.* TO 'user1'@'localhost';

• GRANT: 특정 권한을 부여한다.
• REVOKE: 부여된 권한을 회수한다.

2. 사용자 권한 확인

SHOW GRANTS FOR 'user1'@'localhost';​

---

# 복제(Replication)와 고가용성

복제는 하나의 MySQL 서버(마스터)의 데이터를 다른 서버(슬레이브)로 동기화하는 기능이다. 이는 백업, 데이터 가용성, 읽기 성능 향상에 유용하다.

1. 마스터 서버 설정

[mysqld]
server-id=1
log-bin=mysql-bin

2. 슬레이브 서버 설정

[mysqld]
server-id=2
replicate-do-db=CustomerDB

3. 슬레이브 시작

CHANGE MASTER TO MASTER_HOST='master_host', MASTER_USER='replication_user', MASTER_PASSWORD='password';
START SLAVE;

---

# 성능 튜닝과 모니터링

1. 쿼리 캐시(Query Cache)

쿼리 결과를 캐시에 저장하여 동일한 쿼리의 성능을 높인다. 하지만 MySQL 최신 버전에서는 쿼리 캐시가 더 이상 지원되지 않는다.

2. MySQL 프로파일링

MySQL 프로파일링 도구를 사용해 쿼리 성능을 분석할 수 있다.

SET profiling = 1;
SELECT * FROM Customers WHERE Name = 'John Doe';
SHOW PROFILES;

3. 오류 로그 및 모니터링 도구

MySQL 로그 파일을 확인하여 문제를 진단하고, MySQL Workbench와 같은 모니터링 도구를 사용해 서버 상태를 실시간으로 관찰한다.

---

8. 백업과 복원

1. 백업

정기적으로 데이터를 백업하여 데이터 손실을 방지한다.

mysqldump -u root -p CustomerDB > backup.sql

2. 복원

백업한 데이터를 복원할 때는 다음 명령어를 사용한다.

mysql -u root -p CustomerDB < backup.sql