500===Dev Database/MySQL

MySQL 데이터베이스 완벽 가이드 😋

블로글러 2024. 5. 26. 11:03

요약: MySQL은 세계에서 가장 널리 사용되는 오픈 소스 관계형 데이터베이스 관리 시스템(RDBMS)으로, 다양한 규모의 애플리케이션에서 데이터 저장 및 관리에 활용됩니다. 이 글에서는 MySQL의 핵심 아키텍처, 스토리지 엔진, 트랜잭션 처리, 인덱싱 등 중요 개념을 체계적으로 설명하고, 실제 사용 사례와 최적화 방법을 함께 제시합니다.

MySQL이 뭔가요? 🤔

여러분이 도서관을 상상해보세요.

  • 책(데이터)을 체계적으로 분류하고
  • 필요할 때 빠르게 찾을 수 있도록 정리된 시스템

MySQL은 바로 이런 디지털 도서관입니다!

MySQL은 1995년 스웨덴의 MySQL AB에서 개발된 오픈 소스 관계형 데이터베이스 관리 시스템으로, 현재는 Oracle Corporation이 소유하고 있습니다[^1]. 'My'는 개발자 Michael Widenius의 딸 이름에서 따왔으며, 'SQL'은 데이터베이스 언어인 Structured Query Language를 의미합니다.

관계형 데이터베이스란?

관계형 데이터베이스는 테이블 형태로 데이터를 저장하며, 이 테이블들 간의 관계를 통해 데이터를 구조화합니다.

도서관으로 비유하면:
- 테이블 = 책장
- 행(row) = 개별 책
- 열(column) = 책의 속성(제목, 저자, 출판일 등)
- 관계 = 책들 간의 연관성(시리즈, 같은 주제 등)

MySQL의 핵심 특징:

  • 오픈 소스: 커뮤니티 버전은 무료로 사용 가능
  • 크로스 플랫폼: Windows, Linux, macOS 등 다양한 운영체제 지원
  • 확장성: 소규모 웹사이트부터 대규모 엔터프라이즈 시스템까지 확장 가능
  • ACID 준수: 데이터 무결성 보장을 위한 트랜잭션 지원
  • 다양한 스토리지 엔진: 용도에 맞는 엔진 선택 가능

MySQL 아키텍처 💻

MySQL은 마치 효율적인 회사처럼 각 부서가 역할을 분담하여 작동합니다.

![MySQL 아키텍처]

1. 전체 구조

MySQL 서버는 크게 두 부분으로 나눌 수 있습니다[^2]:

  1. MySQL 엔진: 두뇌 역할 - SQL 파싱, 최적화, 캐싱
  2. 스토리지 엔진: 손발 역할 - 실제 데이터 저장 및 조회
회사 비유:
- MySQL 엔진 = 경영진과 관리자(의사결정, 업무 조율)
- 스토리지 엔진 = 실무자(실제 작업 수행)

2. 세부 컴포넌트

MySQL 엔진 (상단 레이어)

  • 연결 핸들러: 클라이언트 연결 관리 (접수처)
  • 쿼리 파서: SQL 구문 분석 (번역가)
  • 옵티마이저: 쿼리 최적화 (전략가)
  • 쿼리 캐시: 자주 사용하는 쿼리 결과 저장 (메모장)
  • 권한 관리자: 사용자 권한 확인 (보안 담당)

스토리지 엔진 (하단 레이어)

  • 실제 데이터를 디스크에 저장하고 읽어오는 역할
  • 여러 종류의 스토리지 엔진 선택 가능 (작업 특성에 맞는 팀 선택)

3. 쿼리 처리 흐름

1. 클라이언트가 쿼리 요청
   ↓
2. 연결 핸들러가 연결 확인
   ↓
3. 쿼리 파서가 SQL 구문 분석
   ↓
4. 옵티마이저가 실행 계획 생성
   ↓
5. 실행 엔진이 스토리지 엔진에 명령
   ↓
6. 스토리지 엔진이 데이터 조작
   ↓
7. 결과를 클라이언트에 반환

스토리지 엔진 🏭

MySQL의 가장 특별한 점은 다양한 스토리지 엔진을 선택할 수 있다는 것입니다[^3].

1. InnoDB (기본 엔진)

현대 자동차 공장처럼 안전하고 강력한 성능을 제공합니다.

  • 특징: 트랜잭션 지원, 외래 키 제약조건, 행 레벨 잠금, 자동 복구
  • 사용 케이스: 트랜잭션이 중요한 금융 시스템, 온라인 쇼핑몰
  • 장점: 데이터 무결성 보장, 충돌 시 복구 기능
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

2. MyISAM

가볍고 빠른 오토바이 배달처럼 속도에 최적화되어 있습니다.

  • 특징: 트랜잭션 미지원, 테이블 레벨 잠금, 전체 텍스트 검색
  • 사용 케이스: 읽기 위주 웹사이트, 로그 데이터, 검색 기능
  • 장점: 읽기 성능 우수, 적은 메모리 사용
CREATE TABLE log_data (
    id INT PRIMARY KEY AUTO_INCREMENT,
    log_text TEXT,
    INDEX(log_text(50))
) ENGINE=MyISAM;

3. Memory

램에 모든 것을 저장하는 초고속 시스템입니다.

  • 특징: 데이터를 메모리에 저장, 서버 재시작 시 데이터 소실
  • 사용 케이스: 임시 데이터, 캐싱, 빠른 조회가 필요한 룩업 테이블
  • 장점: 매우 빠른 속도
CREATE TABLE temp_session (
    session_id VARCHAR(100) PRIMARY KEY,
    user_data JSON,
    last_access TIMESTAMP
) ENGINE=MEMORY;

4. 기타 엔진

  • Archive: 로그 데이터 저장에 최적화 (압축 기능)
  • Blackhole: 데이터를 저장하지 않음 (로깅, 복제 테스트용)
  • CSV: CSV 파일 형식으로 데이터 저장
  • Federated: 원격 MySQL 서버의 테이블에 접근

데이터 다루기 📊

MySQL에서는 SQL(Structured Query Language)을 사용하여 데이터를 다룹니다.

1. 데이터베이스 및 테이블 생성

-- 데이터베이스 생성
CREATE DATABASE ecommerce;

-- 데이터베이스 선택
USE ecommerce;

-- 테이블 생성
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock INT DEFAULT 0,
    category VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. 기본 CRUD 연산

CRUD는 Create(생성), Read(읽기), Update(갱신), Delete(삭제)의 약자로, 데이터 조작의 기본 작업입니다.

-- 데이터 삽입 (Create)
INSERT INTO products (name, price, stock, category)
VALUES ('스마트폰', 899000.00, 50, '전자기기');

-- 데이터 조회 (Read)
SELECT * FROM products WHERE category = '전자기기';

-- 데이터 수정 (Update)
UPDATE products SET price = 849000.00 WHERE name = '스마트폰';

-- 데이터 삭제 (Delete)
DELETE FROM products WHERE stock = 0;

3. 조인과 관계

여러 테이블의 데이터를 연결하여 복잡한 정보를 조회할 수 있습니다.

-- 두 테이블 생성
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(12,2)
);

CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    product_id INT,
    quantity INT,
    price DECIMAL(10,2),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- 조인 쿼리 예시
SELECT o.order_id, c.name AS customer_name, p.name AS product_name, 
       oi.quantity, oi.price
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.order_date > '2023-01-01';

인덱싱 🔎

인덱스는 마치 책의 색인과 같아서, 데이터를 빠르게 찾을 수 있게 해줍니다[^4].

1. 인덱스란?

도서관 비유:
- 책 내용 = 테이블 데이터
- 색인 페이지 = 데이터베이스 인덱스

인덱스가 없다면 전체 테이블을 순차적으로 검색해야 하므로(풀 테이블 스캔) 매우 비효율적입니다.

2. 인덱스 유형

  • PRIMARY KEY: 테이블당 하나만 존재, NULL 불가, 중복 불가
  • UNIQUE: 중복 불가, NULL 가능
  • INDEX: 일반 인덱스, 중복 가능
  • FULLTEXT: 텍스트 검색용
  • SPATIAL: 지리 데이터용

3. 인덱스 생성과 관리

-- 테이블 생성 시 인덱스 지정
CREATE TABLE users (
    id INT PRIMARY KEY,  -- 기본 키 인덱스
    email VARCHAR(100) UNIQUE,  -- 고유 인덱스
    name VARCHAR(100),
    last_login TIMESTAMP,
    INDEX idx_name (name),  -- 일반 인덱스
    INDEX idx_login_name (last_login, name)  -- 복합 인덱스
);

-- 기존 테이블에 인덱스 추가
ALTER TABLE products ADD INDEX idx_category (category);

-- 인덱스 삭제
ALTER TABLE products DROP INDEX idx_category;

-- 인덱스 확인
SHOW INDEX FROM products;

4. 인덱스 최적화 팁

  • 자주 검색하는 필드에 인덱스 추가
  • 카디널리티(고유값 수)가 높은 열에 인덱스 적용
  • 복합 인덱스 순서 최적화 (WHERE 조건에 자주 사용되는 열을 먼저 배치)
  • 너무 많은 인덱스는 삽입/수정/삭제 성능 저하
  • 정기적으로 ANALYZE TABLE 실행으로 인덱스 통계 갱신

트랜잭션과 ACID 🔒

트랜잭션은 여러 작업을 하나의 논리적 단위로 처리하는 개념입니다[^5].

1. 트랜잭션이란?

은행 송금을 생각해보세요:

  1. A 계좌에서 돈을 빼고
  2. B 계좌에 돈을 넣는 과정

이 두 작업은 반드시 함께 성공하거나 함께 실패해야 합니다. 이를 '트랜잭션'이라고 합니다.

-- 트랜잭션 예시
START TRANSACTION;

UPDATE accounts SET balance = balance - 100000 WHERE id = 123;
UPDATE accounts SET balance = balance + 100000 WHERE id = 456;

-- 모든 작업이 정상적이면 커밋
COMMIT;

-- 문제가 발생하면 롤백
-- ROLLBACK;

2. ACID 속성

트랜잭션은 다음 네 가지 ACID 속성을 보장합니다:

  • Atomicity (원자성): 트랜잭션의 모든 작업이 완전히 수행되거나 전혀 수행되지 않음

    • 송금 예시: 출금과 입금이 모두 성공하거나, 둘 다 실패해야 함

  • Consistency (일관성): 트랜잭션 전후로 데이터베이스는 일관된 상태를 유지

    • 송금 예시: 총 금액은 항상 동일해야 함 (A+B=const)

  • Isolation (격리성): 동시에 실행되는 트랜잭션이 서로 영향을 미치지 않음

    • 송금 예시: 동시에 여러 송금이 발생해도 각각 독립적으로 처리

  • Durability (지속성): 커밋된 트랜잭션의 결과는 영구적으로 저장

    • 송금 예시: 송금 완료 후 시스템 장애가 발생해도 데이터 보존

3. 트랜잭션 격리 수준

MySQL에서는 다음 네 가지 격리 수준을 제공합니다:

  • READ UNCOMMITTED: 가장 낮은 격리 수준, 더티 리드 발생 가능
  • READ COMMITTED: 커밋된 데이터만 읽기 가능
  • REPEATABLE READ: MySQL 기본값, 같은 트랜잭션 내에서 일관된 읽기 보장
  • SERIALIZABLE: 가장 높은 격리 수준, 완전한 격리 보장
-- 현재 격리 수준 확인
SELECT @@transaction_isolation;

-- 격리 수준 설정
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

복제 및 고가용성 🌐

MySQL은 데이터 복제를 통해 가용성과 확장성을 높일 수 있습니다[^6].

1. 복제란?

복제는 데이터를 여러 서버에 동일하게 유지하는 기술입니다.

신문 배포 비유:
- 인쇄소(Master)에서 신문을 인쇄
- 여러 배포점(Slave)에 동일한 신문 배포
- 독자들은 가까운 배포점에서 신문을 받음

2. 복제 유형

비동기 복제 (기본)

  • Master에서 변경 사항 발생
  • Binary Log에 기록
  • Slave가 변경 내용을 가져와 적용

반동기 복제

  • Master에서 변경 사항 발생
  • 최소 하나의 Slave가 변경 사항을 적용했음을 확인 후 커밋

3. 복제 설정 예시

-- Master 서버 설정
-- my.cnf 파일에 추가:
-- server-id=1
-- log-bin=mysql-bin

-- Slave 계정 생성
CREATE USER 'replication_user'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';

-- Master 상태 확인
SHOW MASTER STATUS;

-- Slave 서버 설정
-- my.cnf 파일에 추가:
-- server-id=2

-- Slave 시작
CHANGE MASTER TO
  MASTER_HOST='master_host_ip',
  MASTER_USER='replication_user',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='recorded_log_file_name',
  MASTER_LOG_POS=recorded_log_position;

START SLAVE;

-- Slave 상태 확인
SHOW SLAVE STATUS\G

4. 복제 토폴로지

  • 단일 마스터: 하나의 Master, 여러 Slave
  • 마스터-마스터: 두 서버가 서로의 Master이자 Slave
  • 멀티 소스 복제: 여러 Master에서 하나의 Slave로 복제

5. 고가용성 구성

  • MySQL Group Replication: 자동 장애 조치 기능을 갖춘 그룹 통신 시스템
  • MySQL InnoDB Cluster: MySQL Shell, Group Replication, MySQL Router 조합
  • MySQL NDB Cluster: 높은 가용성과 확장성을 위한 분산 데이터베이스 클러스터

실제 사용 예시 📱

MySQL은 다양한 애플리케이션에서 활용됩니다.

1. 웹 애플리케이션 백엔드

// Java + MySQL 연결 예시
public class DbConnection {
    private Connection conn;

    public void connect() throws SQLException {
        String url = "jdbc:mysql://localhost:3306/myapp";
        String user = "username";
        String password = "password";

        conn = DriverManager.getConnection(url, user, password);
        System.out.println("데이터베이스 연결 성공!");
    }

    public List<Product> getProducts() throws SQLException {
        List<Product> products = new ArrayList<>();
        String sql = "SELECT id, name, price FROM products WHERE stock > 0";

        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                Product product = new Product();
                product.setId(rs.getInt("id"));
                product.setName(rs.getString("name"));
                product.setPrice(rs.getBigDecimal("price"));
                products.add(product);
            }
        }

        return products;
    }
}

2. 대용량 트랜잭션 처리

대규모 전자상거래 사이트에서 주문 처리:

-- 주문 처리 트랜잭션
START TRANSACTION;

-- 주문 정보 저장
INSERT INTO orders (customer_id, order_date, status)
VALUES (12345, NOW(), 'PENDING');
SET @order_id = LAST_INSERT_ID();

-- 주문 항목 저장
INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES (@order_id, 101, 2, 25000.00),
       (@order_id, 202, 1, 15000.00);

-- 재고 감소
UPDATE products SET stock = stock - 2 WHERE id = 101;
UPDATE products SET stock = stock - 1 WHERE id = 202;

-- 결제 정보 저장
INSERT INTO payments (order_id, amount, payment_method, status)
VALUES (@order_id, 65000.00, 'CREDIT_CARD', 'COMPLETED');

COMMIT;

3. 데이터 분석 및 보고서

-- 월별 매출 분석
SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(*) AS total_orders,
    SUM(total_amount) AS revenue,
    AVG(total_amount) AS avg_order_value,
    COUNT(DISTINCT customer_id) AS unique_customers
FROM 
    orders
WHERE 
    order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH)
    AND status = 'COMPLETED'
GROUP BY 
    DATE_FORMAT(order_date, '%Y-%m')
ORDER BY 
    month DESC;

4. 성능 최적화 사례

대형 테이블의 쿼리 최적화:

-- 쿼리 실행 계획 확인
EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;

-- 인덱스 추가로 성능 개선
ALTER TABLE orders ADD INDEX idx_customer (customer_id);

-- 파티셔닝 적용
ALTER TABLE orders
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

마치며 🎁

MySQL은 빠른 성능, 신뢰성, 사용 편의성으로 전 세계 수백만 개의 웹사이트와 애플리케이션에서 사용되는 강력한 데이터베이스 관리 시스템입니다. 소규모 블로그부터 대규모 엔터프라이즈 시스템까지, MySQL은 다양한 요구사항에 적합한 솔루션을 제공합니다.

핵심 개념을 이해하면 MySQL을 효과적으로 활용할 수 있습니다:

  • 관계형 데이터베이스의 테이블 구조
  • 다양한 스토리지 엔진의 특성
  • 인덱싱을 통한 쿼리 최적화
  • 트랜잭션의 ACID 속성
  • 복제를 통한 확장성과 가용성 확보

MySQL을 시작하는 가장 좋은 방법은 직접 설치하고 실습해보는 것입니다. 이론적 지식과 실제 경험을 결합하면 데이터베이스 설계와 최적화에 대한 이해도가 크게 향상될 것입니다.

참고 자료

[^1]: Oracle Corporation. (2022). MySQL 8.0 Reference Manual. https://dev.mysql.com/doc/refman/8.0/en/

[^2]: Schwartz, B., Zaitsev, P., & Tkachenko, V. (2012). High Performance MySQL: Optimization, Backups, and Replication (3rd ed.). O'Reilly Media.

[^3]: MySQL 기술 블로그. (2023). MySQL 스토리지 엔진 비교 분석. MySQL Engineering Blog. https://blogs.oracle.com/mysql/

[^4]: Korth, H. F., Silberschatz, A., & Sudarshan, S. (2019). Database System Concepts (7th ed.). McGraw-Hill Education.

[^5]: Berenson, H., Bernstein, P., Gray, J., Melton, J., O'Neil, E., & O'Neil, P. (1995). A critique of ANSI SQL isolation levels. ACM SIGMOD Record, 24(2), 1-10. https://doi.org/10.1145/568271.223785

[^6]: Bell, C. (2022). MySQL for Developers. Apress. https://doi.org/10.1007/978-1-4842-7386-4

728x90
저작자표시 비영리 변경금지

'500===Dev Database/MySQL'의 다른글

  • 현재글MySQL 데이터베이스 완벽 가이드 😋

티스토리툴바