# 데이터베이스 설계와 정규화
데이터를 효율적으로 저장하기 위해 데이터베이스를 잘 설계하는 것이 중요하다. 데이터베이스 설계에서 가장 중요한 개념 중 하나는 **정규화(Normalization)**이다. 정규화는 데이터 중복을 줄이고 데이터의 일관성을 유지하도록 테이블을 분리하는 과정이다.
정규화의 단계
- 1차 정규화(1NF): 모든 열이 원자 값(더 이상 나눌 수 없는 값)만 가지도록 한다.
- 예: 하나의 셀에 여러 값(전화번호 여러 개)을 넣지 않는다.
- 2차 정규화(2NF): 기본 키에 대해 부분 종속성이 없는 상태를 만든다. 기본 키의 일부분에만 종속된 컬럼이 없어야 한다.
- 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에서는 동시에 여러 트랜잭션이 실행될 때 데이터 일관성을 유지하기 위해 격리 수준을 설정한다. 격리 수준은 다음과 같다:
- READ UNCOMMITTED: 다른 트랜잭션의 커밋되지 않은 변경 사항도 읽을 수 있다.
- READ COMMITTED: 커밋된 데이터만 읽을 수 있다.
- REPEATABLE READ: 하나의 트랜잭션 내에서 같은 쿼리를 여러 번 실행해도 결과가 동일하다.
- 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-bin2. 슬레이브 서버 설정
[mysqld]
server-id=2
replicate-do-db=CustomerDB3. 슬레이브 시작
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.sql2. 복원
백업한 데이터를 복원할 때는 다음 명령어를 사용한다.
mysql -u root -p CustomerDB < backup.sql'데이터베이스 > Mysql' 카테고리의 다른 글
| MySQL 정리 (2) | 2024.10.12 |
|---|---|
| Mysql 덤프(dump) - 리스토어(restore) (1) | 2024.09.12 |