반응형

데이터베이스 사용자 생성 및 권한 관리

데이터베이스에서 사용자를 생성하고 적절한 권한을 설정하는 일은 보안과 효율적인 관리의 핵심입니다. 이 글에서는 MySQL을 중심으로 사용자 생성, 권한 부여, 권한 확인의 과정을 자세히 설명하겠습니다.

1. 데이터베이스 사용자 생성

MySQL에서는 CREATE USER 명령을 사용하여 새 사용자를 생성합니다.

예시

CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password123';
  • 'new_user'@'localhost': 사용자 이름과 접근 가능 위치(여기서는 로컬 호스트).
  • IDENTIFIED BY 'password123': 사용자의 비밀번호 설정.

다른 접근 위치

  • 모든 호스트에서 접속 가능: 'new_user'@'%'
  • 특정 IP에서만 접속 가능: 'new_user'@'192.168.1.100'

2. 사용자 확인

MySQL에서는 mysql.user 테이블을 조회하여 사용자 정보를 확인할 수 있습니다.

예시

SELECT User, Host FROM mysql.user;
  • User: 사용자 이름.
  • Host: 해당 사용자가 접근할 수 있는 위치.

결과 예시

User Host
root localhost
new_user localhost

 

3. 권한 설정

GRANT 명령을 사용하여 사용자에게 특정 권한을 부여합니다.

예시

GRANT [권한] ON [my_database].[my_table] TO 'new_user'@'localhost';
  • my_database.my_table: 권한을 적용할 데이터베이스와 테이블.

3.1. 권한 종류

데이터베이스 및 테이블 작업 관련 권한

권한 설명
SELECT 데이터를 읽는 권한을 부여합니다. (예: SELECT * FROM table_name;)
INSERT 데이터를 삽입할 수 있는 권한을 부여합니다. (예: INSERT INTO table_name ...)
UPDATE 기존 데이터를 수정할 수 있는 권한을 부여합니다. (예: UPDATE table_name SET ...)
DELETE 데이터를 삭제할 수 있는 권한을 부여합니다. (예: DELETE FROM table_name WHERE ...)
CREATE 새 데이터베이스나 테이블을 생성할 수 있는 권한입니다. (예: CREATE TABLE ...)
DROP 데이터베이스나 테이블을 삭제할 수 있는 권한입니다. (예: DROP TABLE ...)
ALTER 테이블의 구조를 변경할 수 있는 권한입니다. (예: ALTER TABLE table_name ...)
INDEX 테이블에 인덱스를 생성하거나 삭제할 수 있는 권한입니다. (예: CREATE INDEX ...)
TRIGGER 트리거를 생성하거나 삭제할 수 있는 권한입니다.
LOCK TABLES 테이블 잠금을 설정하여 다른 작업을 제한할 수 있는 권한입니다.

프로시저와 함수 관련 권한

권한 설명
CREATE ROUTINE 스토어드 프로시저와 함수를 생성할 수 있는 권한입니다.
ALTER ROUTINE 기존의 스토어드 프로시저와 함수를 수정할 수 있는 권한입니다.
EXECUTE 스토어드 프로시저와 함수를 실행할 수 있는 권한입니다.

뷰(View) 관련 권한

권한 설명
CREATE VIEW 뷰를 생성할 수 있는 권한입니다. (예: CREATE VIEW view_name AS ...)
SHOW VIEW 뷰의 정의를 조회할 수 있는 권한입니다. (예: SHOW CREATE VIEW view_name)

데이터베이스 관리자 권한

권한 설명
GRANT OPTION 다른 사용자에게 권한을 부여할 수 있는 권한입니다.
CREATE USER 새 사용자를 생성할 수 있는 권한입니다. (예: CREATE USER ...)
DROP USER 사용자를 삭제할 수 있는 권한입니다.
RELOAD 서버 설정을 다시 로드할 수 있는 권한입니다. (예: FLUSH PRIVILEGES)
SHOW DATABASES 서버에 존재하는 데이터베이스 목록을 볼 수 있는 권한입니다.

복제와 관련된 권한

권한 설명
REPLICATION SLAVE 슬레이브 서버가 마스터 서버로부터 데이터를 복제할 수 있는 권한입니다.
REPLICATION CLIENT 복제 상태를 확인하거나 로그 파일을 볼 수 있는 권한입니다.

전체 서버와 관련된 권한

권한 설명
SUPER 서버 관리자 권한으로, 대부분의 작업을 수행할 수 있습니다.
FILE 서버에서 파일을 읽거나 쓸 수 있는 권한입니다. (예: LOAD DATA INFILE)
PROCESS 현재 실행 중인 프로세스를 확인하거나 관리할 수 있는 권한입니다.

기타 권한

권한 설명
EVENT 이벤트 스케줄러를 생성하거나 관리할 수 있는 권한입니다.
USAGE 기본 권한으로, 아무 작업도 수행할 수 없도록 설정됩니다.

모든 권한

권한 설명
ALL PRIVILEGES 위의 모든 권한을 갖도록 설정됩니다.

 

 

4. 권한 확인

MySQL에서는 SHOW GRANTS 명령을 사용하여 특정 사용자의 권한을 확인합니다.

예시

SHOW GRANTS FOR 'new_user'@'localhost';

결과 예시

GRANT SELECT, INSERT ON `my_database`.* TO 'new_user'@'localhost'

5. 권한 철회

REVOKE 명령을 사용하여 특정 권한을 철회합니다.

예시

REVOKE INSERT ON my_database.my_table FROM 'new_user'@'localhost';

6. 실생활 예시

6.1 웹 애플리케이션용 데이터베이스 사용자

  • 사용자: web_app_user
  • 권한: 애플리케이션에서 데이터 읽기/쓰기만 허용.

설정 예시 (MySQL)

CREATE USER 'web_app_user'@'%' IDENTIFIED BY 'secure_password';
GRANT SELECT, INSERT, UPDATE ON web_app_db.* TO 'web_app_user'@'%';

6.2 관리자용 데이터베이스 사용자

  • 사용자: admin_user
  • 권한: 데이터베이스의 모든 권한 허용.

설정 예시 (PostgreSQL)

CREATE ROLE admin_user WITH LOGIN PASSWORD 'admin_password';
GRANT ALL PRIVILEGES ON DATABASE company_db TO admin_user;

7. 마무리

데이터베이스 사용자와 권한 관리는 보안과 데이터 접근 제어의 핵심입니다. 필요에 따라 최소한의 권한만 부여하고, 주기적으로 권한을 점검하여 불필요한 접근을 차단하는 것이 중요합니다. 위 과정을 숙지하면 데이터베이스를 보다 안전하고 체계적으로 운영할 수 있습니다.

반응형
저작자표시 비영리 변경금지

'개발 부트캠프 > 데이터베이스' 카테고리의 다른 글

[DB] 스토어드 프로시저(Stored Procedure)  (0) 2024.12.02
[DB] 인덱스(Index)  (0) 2024.12.02
[DB] SQL 성능 확인  (0) 2024.12.02
[DB] ERD  (2) 2024.11.27
[DB] 정규화  (0) 2024.11.27
반응형

데이터베이스 스토어드 프로시저

데이터베이스에서 **스토어드 프로시저(Stored Procedure)**는 효율성과 생산성을 높이는 중요한 기능 중 하나입니다. 이 글에서는 스토어드 프로시저의 개념, 주요 역할, 사용법, 그리고 활용하기 좋은 상황과 실제 예시를 통해 자세히 알아보겠습니다.

1. 스토어드 프로시저란 무엇인가?

스토어드 프로시저는 데이터베이스에 저장된 SQL 코드의 집합으로, 특정 작업을 수행하기 위해 재사용 가능한 방식으로 작성됩니다. 간단히 말해, 자주 반복되는 SQL 쿼리나 복잡한 로직을 데이터베이스에 미리 저장해 두고 필요할 때 호출해서 실행하는 것입니다.

💡 비유를 통한 이해

스토어드 프로시저를 프로그래밍에서 함수나 메서드에 비유할 수 있습니다. 일련의 작업(예: 데이터 삽입, 조회, 업데이트 등)을 하나의 함수로 만들어 필요할 때 호출하면 코드를 반복 작성하지 않아도 됩니다.

2. 스토어드 프로시저의 주요 역할

2.1. 재사용성

  • 한 번 작성하면 여러 곳에서 호출해 사용할 수 있습니다.
  • 중복된 SQL 쿼리를 줄이고 관리 효율성을 높입니다.

2.2. 성능 향상

  • SQL 코드가 데이터베이스에 미리 컴파일되어 저장되므로 실행 속도가 빠릅니다.

2.3. 보안 강화

  • 사용자가 직접 SQL 문을 실행하는 대신 스토어드 프로시저를 호출하므로, 민감한 데이터와 로직을 보호할 수 있습니다.

2.4. 데이터베이스 작업 단순화

  • 복잡한 로직(조건문, 반복문 등)을 데이터베이스에서 처리하여 클라이언트와 서버 간의 데이터 전송을 줄입니다.

3. 스토어드 프로시저 사용하는 방법

3.1. 스토어드 프로시저 작성

스토어드 프로시저는 CREATE PROCEDURE 문을 사용하여 작성합니다.

DELIMITER //

CREATE PROCEDURE procedure_name (IN param1 INT, OUT param2 VARCHAR(100))
BEGIN
    -- 작업 로직
    SELECT column_name INTO param2
    FROM table_name
    WHERE id = param1;
END //

DELIMITER ;
 

3.2. 스토어드 프로시저 실행

작성한 스토어드 프로시저는 CALL 문으로 실행합니다.

CALL procedure_name(1, @output_param);
SELECT @output_param;
 

4. IN, OUT, INOUT 파라미터의 차이점

스토어드 프로시저에서 사용하는 파라미터는 크게 IN, OUT, INOUT 세 가지로 나뉩니다. 각 파라미터는 데이터의 전달 방향에 따라 동작 방식이 달라지며, 특정 상황에서 적합하게 사용됩니다. 아래에서 각각의 특성과 차이점을 자세히 살펴보겠습니다.

 

4.1. IN 파라미터

  • 특징:
    • 프로시저에 입력값을 전달하는 데 사용됩니다.
    • 프로시저 내부에서 값을 변경할 수 있지만, 호출한 쪽에 영향을 주지 않습니다.
    • 기본 파라미터 유형으로, 별도 선언 없이 사용 가능합니다.
  • 사용 예시:
     
CREATE PROCEDURE SayHello(IN userName VARCHAR(50))
BEGIN
    SELECT CONCAT('Hello, ', userName) AS Greeting;
END;

-- 실행
CALL SayHello('Alice');

 

  • 실행 결과:
Greeting
--------
Hello, Alice

4.2. OUT 파라미터

  • 특징:
    • 프로시저 실행 후 호출자에게 출력값을 반환하는 데 사용됩니다.
    • 호출 시 초기값은 전달하지 않으며, 프로시저 내부에서 값을 설정해야 합니다.
    • 호출자가 결과를 확인하려면 출력 변수를 선언해야 합니다.
  • 사용 예시:
CREATE PROCEDURE GetSquare(IN inputNumber INT, OUT result INT)
BEGIN
    SET result = inputNumber * inputNumber;
END;

-- 실행
SET @square = 0;
CALL GetSquare(4, @square);
SELECT @square AS SquareResult;
 
  • 실행 결과:
SquareResult
------------
16

 

4.3. INOUT 파라미터

  • 특징:
    • 입력과 출력을 모두 처리할 수 있는 파라미터입니다.
    • 호출 시 값을 전달하고, 프로시저 실행 후 수정된 값을 반환받습니다.
    • 초기값이 필요하며, 결과도 저장됩니다.
  • 사용 예시:
CREATE PROCEDURE MultiplyByTwo(INOUT number INT)
BEGIN
    SET number = number * 2;
END;

-- 실행
SET @num = 5;
CALL MultiplyByTwo(@num);
SELECT @num AS Result;
  • 실행 결과:
     
Result
------
10

4.4. 세 가지 파라미터의 차이점 정리

파라미터 유형 호출 시 값 전달 프로시저 내부에서 값 변경 호출한 곳에 결과 반환
IN O 가능 X
OUT X 필수 O
INOUT O 가능 O

4.5. 활용 시나리오

  • IN 파라미터: 입력값만 필요한 경우 (예: 특정 사용자 데이터 조회).
  • OUT 파라미터: 결과를 반환해야 하는 경우 (예: 계산 결과, 상태 코드 반환).
  • INOUT 파라미터: 입력값을 기반으로 결과를 다시 출력해야 하는 경우 (예: 값 변경 후 반환).

5. 스토어드 프로시저 사용하기 좋은 상황

5.1. 반복 작업 자동화

  • 매일 정해진 시간에 특정 데이터를 업데이트하거나 리포트를 생성해야 할 때 유용합니다.
  • 예: 일일 매출 데이터 집계

5.2. 복잡한 비즈니스 로직 처리

  • 여러 쿼리를 결합해 실행해야 하는 경우(예: 조건문, 반복문 포함 로직).
  • 예: 여러 테이블의 데이터를 기반으로 계산 작업 수행

5.3. 애플리케이션과 데이터베이스 간 트래픽 최소화

  • 복잡한 쿼리를 클라이언트에서 여러 번 호출하는 대신, 데이터베이스에서 한 번에 처리.

5.4. 보안 강화

  • 클라이언트가 직접 SQL을 실행하지 못하게 하고, 스토어드 프로시저만 호출하도록 제한.

6. 스토어드 프로시저 활용 예시

예시 1: 특정 조건에 맞는 사용자 데이터 조회

CREATE PROCEDURE GetUserById (IN userId INT)
BEGIN
    SELECT * 
    FROM Users 
    WHERE id = userId;
END;

-- 실행
CALL GetUserById(5);
 

예시 2: 여러 테이블에 걸친 데이터 삽입

CREATE PROCEDURE InsertOrderAndDetails (
    IN customerId INT, 
    IN productId INT, 
    IN quantity INT
)
BEGIN
    -- 1. 주문 테이블에 데이터 삽입
    INSERT INTO Orders (customer_id, order_date) 
    VALUES (customerId, NOW());
    
    -- 2. 마지막 주문 ID 가져오기
    SET @orderId = LAST_INSERT_ID();
    
    -- 3. 주문 상세 테이블에 데이터 삽입
    INSERT INTO OrderDetails (order_id, product_id, quantity) 
    VALUES (@orderId, productId, quantity);
END;

-- 실행
CALL InsertOrderAndDetails(1, 101, 2);
 

예시 3: 자동 데이터 정리 작업

CREATE PROCEDURE CleanOldLogs ()
BEGIN
    DELETE FROM Logs 
    WHERE log_date < DATE_SUB(NOW(), INTERVAL 30 DAY);
END;

-- 실행
CALL CleanOldLogs();
 

7. 스토어드 프로시저 사용 시 주의사항

  • 복잡한 로직 관리: 스토어드 프로시저가 지나치게 복잡하면 유지보수가 어려워질 수 있습니다.
  • 데이터베이스 종속성: 프로시저는 특정 데이터베이스 플랫폼에 종속될 가능성이 있으므로, 이식성을 고려해야 합니다.
  • 디버깅 어려움: 스토어드 프로시저는 디버깅이 쉽지 않으므로, 충분한 테스트가 필요합니다.

마무리

스토어드 프로시저는 데이터베이스 성능 최적화와 반복 작업 간소화에 탁월한 도구입니다. 특히, 보안과 효율성을 중요시하는 환경에서 필수적인 기능으로 활용됩니다. 작성과 활용이 익숙해지면 데이터베이스와 애플리케이션의 협업을 더 원활하게 만들 수 있습니다.

반응형
저작자표시 비영리 변경금지

'개발 부트캠프 > 데이터베이스' 카테고리의 다른 글

[DB] 사용자 생성 및 권한 관리  (1) 2024.12.03
[DB] 인덱스(Index)  (0) 2024.12.02
[DB] SQL 성능 확인  (0) 2024.12.02
[DB] ERD  (2) 2024.11.27
[DB] 정규화  (0) 2024.11.27
반응형

데이터베이스 인덱스

데이터베이스를 효율적으로 관리하려면 "인덱스(index)"라는 강력한 도구를 이해하고 적절히 활용해야 합니다. 특히, Primary Key와 Foreign Key에서 기본적으로 설정되는 이유와 인덱스 설정의 좋은 예시를 알면, 성능 최적화에 큰 도움이 됩니다. 이 글에서는 인덱스의 핵심 개념과 활용법을 쉽게 풀어보겠습니다.

1. 인덱스란 무엇인가?

인덱스는 데이터베이스에서 특정 컬럼의 값을 빠르게 찾을 수 있도록 만들어진 검색용 구조입니다. 도서관에서 책을 찾기 위해 사용하는 "책 번호"와 같은 역할을 합니다. 데이터베이스에서는 인덱스를 통해 데이터를 탐색하는 속도를 획기적으로 향상시킬 수 있습니다.

🔍 직관적인 이해

생각해봅시다. 도서관에 책이 10,000권 있는데 원하는 책을 순서대로 한 권씩 살펴본다면 시간이 오래 걸립니다(= 전체 테이블 스캔). 반면, 책 번호로 분류된 색인을 사용하면 원하는 책을 금방 찾을 수 있습니다(= 인덱스를 통한 조회).

2. 인덱스의 역할

인덱스는 주로 다음과 같은 작업에서 성능을 향상시킵니다:

  • 데이터 검색 속도 향상: 특정 조건으로 데이터를 조회할 때 빠르게 찾을 수 있습니다.
  • 중복 방지: Primary Key와 같은 고유 값을 보장합니다.
  • 데이터 정렬: 정렬 작업을 더 효율적으로 처리합니다.

하지만 인덱스는 삽입, 업데이트, 삭제 성능에 영향을 줄 수 있습니다. 인덱스를 생성하면 해당 컬럼에 변화가 생길 때마다 인덱스 구조를 업데이트해야 하기 때문입니다.

3. Primary Key와 Foreign Key에 기본 인덱스가 설정되는 이유

Primary Key

  • 특징: 테이블의 각 행을 고유하게 식별하는 데 사용됩니다.
  • 인덱스가 기본 생성되는 이유: Primary Key는 고유성과 빠른 검색을 보장해야 하므로, 인덱스가 필수적입니다. 예를 들어, 학생 테이블의 학생 ID를 Primary Key로 설정하면 특정 학생을 빠르게 조회할 수 있습니다.

Foreign Key

  • 특징: 다른 테이블의 Primary Key를 참조하여 두 테이블 간 관계를 정의합니다.
  • 인덱스가 기본 생성되는 이유: Foreign Key는 종종 다른 테이블과의 조인을 수행하므로, 조인 연산의 성능을 높이기 위해 인덱스를 생성합니다.

4. 인덱스 설정 방법

4.1. SQL을 이용한 인덱스 생성

-- 1. 기본 인덱스 생성
CREATE INDEX idx_column_name ON table_name (column_name);

-- 2. 고유 인덱스 생성 (중복 방지)
CREATE UNIQUE INDEX idx_unique_name ON table_name (column_name);

-- 3. 복합 인덱스 생성 (두 개 이상의 컬럼 결합)
CREATE INDEX idx_composite_name ON table_name (column1, column2);
 

4.2. Primary Key와 Foreign Key 인덱스 설정

Primary Key와 Foreign Key를 설정할 때 인덱스는 자동으로 생성됩니다.

-- Primary Key 생성
ALTER TABLE table_name ADD PRIMARY KEY (column_name);

-- Foreign Key 생성
ALTER TABLE table_name ADD CONSTRAINT fk_name FOREIGN KEY (column_name) REFERENCES other_table (column_name);

 

4.3. 자동 인덱스 확인

데이터베이스의 시스템 테이블을 조회하여 생성된 인덱스를 확인할 수 있습니다.

5. 인덱스 설정의 좋은 예시

5.1. 빈번한 조회가 발생하는 컬럼

  • 예: 사용자 정보 테이블에서 email 컬럼으로 자주 검색된다면 인덱스를 설정합니다.
CREATE INDEX idx_user_email ON users (email);
 

5.2. 대규모 데이터 정렬이 필요한 경우

  • 예: 상품 리스트에서 가격 기준으로 정렬이 자주 발생한다면 인덱스를 설정합니다.
CREATE INDEX idx_product_price ON products (price);
 

5.3. 자주 조인되는 컬럼

  • 예: 주문 테이블과 고객 테이블의 customer_id는 조인이 빈번하므로 인덱스를 설정합니다.
CREATE INDEX idx_order_customer_id ON orders (customer_id);
 

6. 인덱스 설정 시 주의사항

  • 인덱스 과다 생성 금지: 인덱스가 많아지면 데이터 삽입/갱신 속도가 느려질 수 있습니다.
  • 자주 변경되는 컬럼에 인덱스 설정 신중: 자주 업데이트되는 컬럼에 인덱스를 걸면 성능 저하가 발생할 수 있습니다.
  • 테이블 크기에 따라 조정 필요: 소규모 테이블에는 인덱스가 큰 차이를 만들지 않을 수 있습니다.

마무리

인덱스는 데이터베이스 성능 최적화의 핵심 도구입니다. Primary Key와 Foreign Key는 기본적으로 인덱스가 설정되어 있어 관리가 간단하지만, 다른 컬럼에 대한 인덱스는 신중히 설계해야 합니다. 인덱스는 검색 성능을 크게 향상시키지만, 삽입/갱신 작업에 부하를 줄 수 있다는 점을 꼭 고려하세요.

반응형
저작자표시 비영리 변경금지

'개발 부트캠프 > 데이터베이스' 카테고리의 다른 글

[DB] 사용자 생성 및 권한 관리  (1) 2024.12.03
[DB] 스토어드 프로시저(Stored Procedure)  (0) 2024.12.02
[DB] SQL 성능 확인  (0) 2024.12.02
[DB] ERD  (2) 2024.11.27
[DB] 정규화  (0) 2024.11.27
반응형

SQL 성능 확인

데이터베이스가 커지고 복잡해질수록 SQL 쿼리의 성능 최적화는 중요합니다. 효율적인 쿼리는 시스템의 성능을 유지하고, 자원을 절약하며, 사용자 경험을 개선합니다. 이 글에서는 SQL 성능 확인의 필요성과 함께, 성능을 확인하는 도구인 EXPLAIN 및 PROFILING의 사용법과 예시를 소개합니다.

1. SQL 성능 확인이 필요한 이유

1.1 성능 저하 문제

SQL 성능 문제는 데이터베이스가 대규모 데이터를 처리하거나 복잡한 조인을 실행할 때 발생합니다. 비효율적인 쿼리는 실행 속도를 느리게 하고, 서버 리소스를 과도하게 소모할 수 있습니다.

1.2 문제 조기 발견 및 해결

성능 확인 도구를 사용하면 다음과 같은 문제를 조기에 발견하고 해결할 수 있습니다:

  • 비효율적인 인덱스 사용: 적절한 인덱스가 없거나 잘못된 인덱스를 사용하는 경우.
  • 과도한 테이블 스캔: 필요한 데이터만 조회하지 못하고 전체 테이블을 읽는 경우.
  • 비효율적인 조인: 복잡한 조인 조건으로 인해 불필요한 계산이 이루어지는 경우.

1.3 최적화된 쿼리 작성

성능 확인 결과를 바탕으로 쿼리와 데이터베이스 구조를 최적화하여 자원 소모를 줄이고 응답 시간을 단축할 수 있습니다.

2. SQL 성능 확인 도구

2.1 EXPLAIN

EXPLAIN은 SQL 쿼리가 실행될 때 데이터베이스가 어떤 계획을 사용하는지 설명합니다. 이를 통해:

  • 어떤 인덱스가 사용되는지
  • 테이블 스캔 여부
  • 조인 순서와 방식

등을 알 수 있습니다.

2.2 PROFILING

PROFILING은 쿼리 실행 과정에서 어떤 작업이 수행되었는지와 각 작업에 소요된 시간을 상세히 보여줍니다.

3. EXPLAIN 및 PROFILING 사용법과 예시

3.1 EXPLAIN 사용법

아래는 EXPLAIN 명령어를 사용하는 기본 예시입니다.

EXPLAIN SELECT * FROM orders WHERE customer_id = 1;

 

EXPLAIN 결과 예시

id select_type table type possible_keys key key_len rows extra
1 SIMPLE orders ref customer_idx customer_idx 4 10 Using index
  • type: 쿼리 실행 방식. 가능한 최적화 순서: ALL (전체 테이블 스캔) → index → range → ref → const.
  • possible_keys: 사용할 수 있는 인덱스.
  • key: 실제 사용된 인덱스.
  • rows: 조회된 데이터의 대략적인 행 수.
  • Extra: 추가 작업 정보 (e.g., Using index는 효율적, Using temporary와 Using filesort는 비효율적).

3.2 PROFILING 사용법

1. PROFILING 활성화

SET profiling = 1;

 

 

2. 쿼리 실행

SELECT * FROM orders WHERE customer_id = 1;

 

 

3. 전체 프로파일 조회

SHOW PROFILES;

 

결과 예시

Query_ID Duration Query
1 0.000543 SELECT * FROM orders WHERE ...

 

4. 특정 쿼리의 상세 정보 조회

SHOW PROFILE QUERY FOR QUERY_ID;
 

결과 예시

Status Duration
Starting 0.000023
Checking permissions 0.000011
Opening tables 0.000134
Sending data 0.000375
End 0.000001

4. 예시: 주문 데이터를 조회하는 SQL 성능 분석

요구 사항

특정 고객의 주문 데이터를 빠르게 조회하는 SQL 쿼리를 작성 및 분석합니다.

데이터베이스 스키마

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10, 2),
    INDEX(customer_idx) (customer_id)
);

 

문제 발견

다음과 같은 쿼리를 실행합니다:

EXPLAIN SELECT * FROM orders WHERE customer_id = 1;
 

결과:

idtabletyperowsExtra

id table type rows Extra
1 orders ALL 5000 Using where
  • type이 ALL인 경우 전체 테이블 스캔이 발생합니다.

해결 방법

customer_id 컬럼에 적절한 인덱스를 추가합니다:

CREATE INDEX customer_idx ON orders(customer_id);
 

다시 쿼리를 실행하면:

EXPLAIN SELECT * FROM orders WHERE customer_id = 1;
 

결과:

idtabletyperowsExtra

id table type rows Extra
1 orders ref 10 Using index

PROFILING 결과 확인

SET profiling = 1;
SELECT * FROM orders WHERE customer_id = 1;
SHOW PROFILES;
SHOW PROFILE QUERY FOR QUERY_ID;
  • Duration에서 Sending data 시간이 대폭 감소했음을 확인할 수 있습니다.

5. 결론

SQL 성능 확인은 데이터베이스의 효율성을 극대화하고 시스템 성능을 유지하는 데 필수적입니다.
EXPLAIN은 쿼리 실행 계획을 분석하는 데 유용하며, PROFILING은 실행 과정의 세부적인 정보를 제공합니다.

 

이 두 도구를 적절히 활용하면 성능 병목 지점을 파악하고 최적화된 쿼리를 작성할 수 있습니다.
이를 통해 시스템의 응답 시간을 개선하고 서버 리소스를 효율적으로 관리할 수 있습니다.

반응형
저작자표시 비영리 변경금지

'개발 부트캠프 > 데이터베이스' 카테고리의 다른 글

[DB] 스토어드 프로시저(Stored Procedure)  (0) 2024.12.02
[DB] 인덱스(Index)  (0) 2024.12.02
[DB] ERD  (2) 2024.11.27
[DB] 정규화  (0) 2024.11.27
[DB] 데이터베이스 = 파일이다.  (0) 2024.11.27
반응형

 

ERD(Entity Relationship Diagram)

1. ERD란 무엇인가?

ERD (Entity Relationship Diagram)는 데이터베이스 설계를 위한 다이어그램으로, 엔티티(Entity)와 엔티티 간의 관계(Relationship)를 시각적으로 표현하는 도구입니다. ERD는 데이터베이스를 구축하기 전에 요구 사항을 분석하고, 데이터베이스에 저장할 데이터의 구조를 설계하는 데 중요한 역할을 합니다.

  • 엔티티 (Entity): 데이터베이스에서 중요한 객체나 개념을 나타냅니다. 예를 들어, 고객, 주문, 책, 직원 등이 엔티티로 정의될 수 있습니다.
  • 속성 (Attribute): 각 엔티티가 가지는 정보나 특성을 나타냅니다. 예를 들어, 고객 엔티티는 이름, 이메일, 전화번호와 같은 속성을 가질 수 있습니다.
  • 관계 (Relationship): 서로 다른 엔티티들 간의 상호작용을 나타냅니다. 예를 들어, 고객은 주문을 하고, 주문은 책을 포함하는 관계입니다.
  • 카디널리티 (Cardinality): 엔티티 간의 관계에서 개체들의 수를 나타내며, 1:1, 1:N, M:N 관계로 나눠집니다.

ERD는 이러한 구성 요소들을 시각적으로 표현하여 데이터베이스 설계를 명확히 하고, 효율적인 데이터 관리와 시스템 개발을 돕습니다.

 

2. 요구 사항 분석을 통한 ERD 작성 예시

2.1 요구 사항 분석

요구 사항: 고객은 책을 주문할 수 있다.

  • 고객은 여러 주문을 할 수 있습니다.
  • 각 주문은 여러 책을 포함할 수 있습니다.
  • 한 권의 책은 여러 주문에 포함될 수 있습니다.
  • 각 주문 항목은 책의 수량과 가격을 기록합니다.

2.2 엔티티 및 속성도출

위의 요구 사항을 기반으로 다음과 같은 엔티티 및 속성들을 도출할 수 있습니다:

  • 고객 (Customer): 고객의 정보
    • 고객ID (Primary Key)
    • 이름
    • 이메일
    • 전화번호
  • 책 (Book): 서점에서 판매하는 책의 정보
    • 책ID (Primary Key)
    • 제목
    • 저자
    • 출판사
    • 가격
  • 주문 (Order): 고객이 한 번에 구매하는 주문
    • 주문ID (Primary Key)
    • 고객ID (Foreign Key)
    • 주문일자
  • 주문 항목 (OrderItem): 각 주문에 포함된 책의 수량과 가격
    • 주문ID (Foreign Key)
    • 책ID (Foreign Key)
    • 수량
    • 가격

2.3 관계 정의

1. 고객 (Customer)과 주문 (Order)은 1:N 관계입니다. 하나의 고객은 여러 개의 주문을 할 수 있습니다.

2. 주문 (Order)과 주문 항목 (OrderItem)은 1:N 관계입니다. 하나의 주문은 여러 개의 주문 항목을 가질 수 있습니다.

3. 책 (Book)과 주문 항목 (OrderItem)은 1:N 관계입니다. 하나의 책은 여러 주문 항목에 포함될 수 있습니다.

4. 주문 (Order)과 책 (Book)은 M:N 관계입니다. 주문은 여러 책을 포함할 수 있으며, 한 권의 책은 여러 주문에 포함될 수 있습니다.

이 M:N 관계를 해결하기 위해 주문 항목 (OrderItem) 테이블을 도입하여, 주문과 책 사이의 관계를 1:N으로 변환합니다.

2.4 ERD 설계

이제 위에서 정의한 관계를 바탕으로 ERD를 설계합니다. 각 엔티티와 관계는 다음과 같습니다:

+-----------------+           +-----------------+         +-------------------+
|    Customer    | 1       N |     Order       | 1     N  |   OrderItem       |
+-----------------+           +-----------------+         +-------------------+
| CustomerID(PK) |           | OrderID(PK)     |         | OrderID(FK)       |
| Name           |           | CustomerID(FK)  |         | BookID(FK)        |
| Email          |           | OrderDate       |         | Quantity          |
| Phone          |           +-----------------+         | Price             |
+-----------------+                                     +-------------------+
                                                        |
                                                        |
                                                        |
                                                  N    |     1
                                                        |
                                                    +-------------------+
                                                    |      Book         |
                                                    +-------------------+
                                                    | BookID(PK)        |
                                                    | Title             |
                                                    | Author            |
                                                    | Publisher         |
                                                    | Price             |
                                                    +-------------------+

2.5 ERD 설명

1. 고객 (Customer):
- 고객ID (Primary Key)는 고객을 고유하게 식별합니다.
- 고객은 여러 주문을 할 수 있으므로, 주문 (Order) 엔티티와는 1:N 관계를 가집니다.

 

2. 주문 (Order):
- 주문ID (Primary Key)는 각 주문을 고유하게 식별하는 기본 키입니다.
- 고객ID (Foreign Key)를 사용하여, 해당 주문이 어느 고객에 의해 이루어졌는지를 식별합니다.
- 주문일자는 주문이 이루어진 날짜를 기록합니다.
- 주문 항목 (OrderItem)과는 1:N 관계를 가지며, 하나의 주문은 여러 주문 항목을 가질 수 있습니다.

 

3. 주문 항목 (OrderItem):
- 주문ID (Foreign Key)는 해당 주문을 식별하는 외래 키입니다.
- 책ID (Foreign Key)는 주문 항목에 포함된 책을 식별하는 외래 키입니다.
- 수량 (Quantity)과 가격 (Price)는 해당 주문 항목에 포함된 책의 수량과 가격을 기록합니다.
- 이 테이블은 주문과 책 간의 M:N 관계를 해결하는 중간 테이블 역할을 합니다.

 

4. 책 (Book):
- 책ID (Primary Key)는 각 책을 고유하게 식별하는 기본 키입니다.
- 책 제목, 저자, 출판사, 가격 등의 정보가 포함됩니다.
- 주문 항목 (OrderItem)을 통해 여러 주문에 포함될 수 있습니다.

- 주문 항목 (OrderItem)과는 N:1 관계를 가지며, 하나의 책은 여러 주문 항목에 포함될 수 있습니다.

2.6 결론

이와 같은 ERD 설계는 고객이 여러 권의 책을 주문하는 시스템에서 발생할 수 있는 복잡한 관계를 명확하게 정의하고 효율적으로 관리할 수 있게 합니다.

 

ERD는 데이터베이스 설계의 중요한 부분으로, 시스템의 요구 사항을 정확하게 분석하고 이를 기반으로 데이터베이스 구조를 설계할 수 있도록 도와줍니다. 이 과정에서 고객, 주문, 책, 주문 항목 간의 관계를 명확히 정의하고, 이를 바탕으로 효율적인 데이터 관리가 가능해집니다. ERD는 데이터베이스 설계를 위한 중요한 청사진 역할을 하며, 이후의 개발 단계에서 매우 중요한 역할을 합니다.

반응형
저작자표시 비영리 변경금지

'개발 부트캠프 > 데이터베이스' 카테고리의 다른 글

[DB] 스토어드 프로시저(Stored Procedure)  (0) 2024.12.02
[DB] 인덱스(Index)  (0) 2024.12.02
[DB] SQL 성능 확인  (0) 2024.12.02
[DB] 정규화  (0) 2024.11.27
[DB] 데이터베이스 = 파일이다.  (0) 2024.11.27
반응형

데이터베이스 정규화와 이상, 그리고 정규화 단계별 예제

데이터베이스 설계의 핵심은 데이터를 효율적으로 관리하고, 무결성을 유지하며, 중복을 최소화하는 것입니다. 이를 위해 사용하는 방법이 **정규화(Normalization)**입니다. 이번 글에서는 정규화의 개념, 데이터 비효율로 인해 발생할 수 있는 이상(Anomalies), 그리고 **정규화의 단계별 과정(1NF ~ 6NF)**을 상세한 예시와 함께 다룹니다

 

 

1. 데이터베이스 정규화란?

정규화는 관계형 데이터베이스를 설계할 때 중복 데이터를 줄이고, 데이터 무결성을 유지하며, 효율적인 데이터 관리를 목표로 데이터를 구조화하는 과정입니다.

핵심 목표:

  • 데이터 중복 최소화
  • 데이터 무결성 유지
  • 이상(Anomalies) 방지

 

2. 데이터베이스 이상의 개념과 종류

데이터베이스 설계가 적절하지 않을 경우, 삽입 이상, 삭제 이상, 갱신 이상과 같은 문제(이상)가 발생할 수 있습니다.

2.1 삽입 이상 (Insertion Anomaly)

새로운 데이터를 삽입할 때 불필요한 정보를 함께 저장해야 하는 문제입니다.

학생ID 이름 학과명 학과장
1 홍길동 컴퓨터학과 김교수

문제점: 새로운 학과(예: 전기공학과)를 추가하려면, 학과장이 없는 상태에서도 '학생ID'와 '이름' 데이터를 함께 입력해야 합니다.

2.2 삭제 이상 (Deletion Anomaly)

데이터를 삭제할 때 의도치 않게 다른 중요한 정보도 삭제되는 문제입니다.

학생ID 이름 학과명 학과장
1 홍길동 컴퓨터학과 김교수

문제점: '홍길동'의 데이터를 삭제하면, 컴퓨터학과의 학과장 정보도 함께 사라집니다.

2.3 갱신 이상 (Update Anomaly)

중복된 데이터 중 일부만 수정되어 데이터 불일치가 발생하는 문제입니다.

학생ID 이름 학과명 학과장
1 홍길동 컴퓨터학과 김교수
2 김영희 컴퓨터학과 김교수

문제점: 학과장이 변경되었을 때(예: 이교수), 일부 데이터만 갱신하면 불일치가 발생합니다.

 

3. 정규화 단계별 설명과 예제

정규화는 데이터를 구조화하기 위한 여러 단계로 이루어져 있으며, 단계가 높아질수록 데이터 중복과 이상이 감소합니다.

3.1 제1정규형 (1NF)

조건: 모든 컬럼 값이 원자값(Atomic Value)을 가져야 합니다.

예시 변환 과정:

주문ID 고객명 연락처 상품명
1 홍길동 010-1234-5678 사과, 바나나
2 김영희 010-5678-1234 딸기, 포도, 배

변환 후:

주문ID 고객명 연락처 상품명
1 홍길동 010-1234-5678 사과
1 홍길동 010-1234-5678 바나나
2 김영희 010-5678-1234 딸기
2 김영희 010-5678-1234 포도
2 김영희 010-5678-1234 배

3.2 제2정규형 (2NF)

조건: 제1정규형을 만족하고, 부분 종속(Partial Dependency)을 제거해야 합니다.

예시 변환 과정:

변환 전:

주문ID 상품ID 상품명 고객명
1 101 사과 홍길동
1 102 바나나 홍길동
2 103 딸기 김영희
2 104 포도 김영희

변환 후:

주문ID 고객명
1 홍길동
2 김영희
상품ID 상품명
101 사과
102 바나나
103 딸기
104 포도

3.3 제3정규형 (3NF)

조건: 제2정규형을 만족하고, 이행적 종속(Transitive Dependency)이 없어야 합니다.

예시 변환 과정:

변환 전:

학생ID 이름 학과ID 학과명
1 홍길동 101 컴퓨터학과
2 김영희 102 기계공학과

변환 후:

학생ID 이름 학과ID
1 홍길동 101
2 김영희 102
학과ID 학과명
101 컴퓨터학과
102 기계공학과

3.4 BCNF (Boyce-Codd Normal Form)

조건: 제3정규형을 만족하며, 모든 결정자가 후보 키여야 합니다.

3.5 제4정규형 (4NF)

조건: 다치 종속(Multi-Valued Dependency) 제거

3.6 제5정규형 (5NF)

조건: 조인 종속(Join Dependency) 제거

3.7 제6정규형 (6NF)

조건: 무손실 분해(Lossless Decomposition)

데이터베이스 설계의 핵심은 효율성과 무결성을 극대화하는 과정입니다. 1NF ~ 3NF는 필수적이고, 고급 정규형(BCNF ~ 6NF)은 특수 요구사항에 적용됩니다.

 

 

반응형
저작자표시 비영리 변경금지

'개발 부트캠프 > 데이터베이스' 카테고리의 다른 글

[DB] 스토어드 프로시저(Stored Procedure)  (0) 2024.12.02
[DB] 인덱스(Index)  (0) 2024.12.02
[DB] SQL 성능 확인  (0) 2024.12.02
[DB] ERD  (2) 2024.11.27
[DB] 데이터베이스 = 파일이다.  (0) 2024.11.27
반응형

데이터베이스

데이터베이스(DB)는 데이터를 체계적으로 저장하고 관리하는 시스템으로, 파일 시스템 위에서 작동합니다.

  • 파일 시스템은 하드 디스크의 특정 위치에 데이터를 저장하고 이를 관리하는 역할을 합니다.
  • 데이터베이스 역시 결국에는 파일로 하드 디스크에 저장됩니다.

이를 실습을 통해 확인하기 위해 MariaDB를 설치하고 설정해보겠습니다.

 

DB 파일 확인 실습

  1. 관리자 권한으로 로그인
    DB 설정과 설치에는 관리자 권한이 필요합니다.
  2. IP 설정
  3. 레포지토리 목록 갱신위 명령어로 시스템의 레포지토리 목록을 갱신합니다.
apt update
  1. MariaDB 설치
    MariaDB는 오픈소스 관계형 데이터베이스 관리 시스템(RDBMS)입니다.
apt install -y mariadb-server
  1. MariaDB 설정 변경
    MariaDB가 외부 접속을 허용할 수 있도록 MariaDB 설정 파일의 바인드 주소를 수정합니다.
vi /etc/mysql/mariadb.conf.d/50-server.cnf
  • 27번 라인에서 bind-address 값을 아래와 같이 변경:
bind-address = 0.0.0.0 # 모든 IP에서 접속 허용
  1. MariaDB 실행
systemctl restart mariadb
  1. MariaDB 실행 상태 확인
    MariaDB가 정상적으로 실행 중인지 확인합니다.
systemctl status mariadb
apt install -y net-tools
netstat -anlp | grep :3306

3306 포트가 열려 있다면 MariaDB가 실행되고 있는 것입니다.

  1. DB 서버 초기화
mysql_secure_installation
1. 여러 번 Enter를 눌러 초기 설정을 진행합니다.  
2. 새로운 비밀번호를 설정: db\_admin\_password (예: secure\_password123)  
3. 나머지는 Enter로 기본 설정을 유지합니다.
  1. 데이터베이스 생성
    MariaDB에 접속하여 새로운 데이터베이스를 생성합니다.
mariadb -u root -p
# 설정한 비밀번호 입력: secure_password123
  • 데이터베이스 생성 명령어:
    CREATE DATABASE example_db;
  • 데이터베이스 생성 후 종료:
    exit
  1. DB 파일 확인
    데이터베이스 파일은 하드 디스크의 /var/lib/mysql/ 디렉토리에 저장됩니다.
ls -al /var/lib/mysql/example_db

위 명령어를 실행하면 example_db라는 이름의 폴더를 확인할 수 있으며, 해당 폴더 안에 db.opt파일을 확인할 수 있습니다. 이는 우리가 생성한 데이터베이스가 실제로 파일 형태로 저장되었음을 보여줍니다.

 

이 실습은 DB가 파일로 관리된다는 점을 확인하는 과정이며, MariaDB 설정과 초기화를 통해 기본적인 데이터베이스의 동작을 이해할 수 있습니다.

반응형
저작자표시 비영리 변경금지

'개발 부트캠프 > 데이터베이스' 카테고리의 다른 글

[DB] 스토어드 프로시저(Stored Procedure)  (0) 2024.12.02
[DB] 인덱스(Index)  (0) 2024.12.02
[DB] SQL 성능 확인  (0) 2024.12.02
[DB] ERD  (2) 2024.11.27
[DB] 정규화  (0) 2024.11.27

+ Recent posts

티스토리툴바