회원가입   로그인
IMG-LOGO
공지사항 :

MySQL 스키마(schema)

lmkfox - 2025-09-21 08:25:41 114 Views 0 Comment

1. 기본 개념

  • 스키마 ≒ 데이터베이스: MySQL에서는 보통 동일하게 취급합니다.

  • 네임스페이스: 같은 서버에 여러 스키마(데이터베이스)를 만들면 db1.users / db2.users처럼 동일한 테이블명을 격리할 수 있습니다.

  • 권한 단위: MySQL 권한은 스키마(데이터베이스) 단위로 할당 가능해 격리·보안에 유용합니다.

2. 스키마 생성/삭제/조회 (기본 SQL)

-- 생성 (CREATE DATABASE 와 동일)
CREATE SCHEMA my_app CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 또는
CREATE DATABASE my_app DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 사용(현재 세션의 기본 DB 설정)
USE my_app;

-- 조회
SHOW DATABASES;
-- 또는 정보 스키마에서 더 상세 조회
SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA;

3. 스키마 수준 설정(문자셋·콜레이션)

  • 스키마 생성 시 기본 문자셋·콜레이션을 지정하면, 그 스키마 안의 테이블이나 컬럼 생성 시 기본값으로 상속됩니다(단, 테이블/컬럼에서 별도 지정 가능).

CREATE DATABASE blog DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

4. 권한(Privileges) 관리 — 스키마 단위

-- 스키마(my_app)에 대해 특정 사용자에 권한 부여
CREATE USER 'alice'@'localhost' IDENTIFIED BY 's3cr3t';
GRANT SELECT, INSERT, UPDATE, DELETE ON my_app.* TO 'alice'@'localhost';
FLUSH PRIVILEGES;

  • 패턴 db_name.*은 스키마 내 모든 테이블을 의미.

  • 더 세부적으로 db.table 단위로 권한 부여 가능.

5. 스키마 객체 접근(완전한 명시)

  • 현재 스키마가 my_app가 아니라면 다음처럼 완전 이름(qualified name)을 사용:

SELECT * FROM my_app.users;

6. 스키마 설계 원칙 (데이터 모델링)

  • 정규화(Normalization): 중복 최소화(1NF~3NF), 하지만 성능·조회 중심이면 일부 비정규화 허용.

  • 인덱싱 전략: 조회 패턴(WHERE, JOIN, ORDER BY, GROUP BY)을 기반으로 인덱스 설계. 복합 인덱스 순서는 쿼리에서 사용되는 컬럼 순서와 맞춰야 함.

  • FK 제약(무결성): InnoDB 사용 시 외래키로 참조 무결성 강제. 단, 성능 고려와 배치 작업 시 제약 해제 필요할 수 있음.

  • 데이터 타입: 적절한 자료형 사용(예: INT 크기, VARCHAR 길이, DATETIME/TIMESTAMP 선택). 잘못된 타입은 저장 공간 및 성능 악영향.

7. 스키마 변경(DDL) — ALTER TABLE 등

  • 기본 문법:

ALTER TABLE users ADD COLUMN last_login DATETIME NULL;
ALTER TABLE users MODIFY COLUMN username VARCHAR(100) NOT NULL;
ALTER TABLE users DROP COLUMN obsolete_col;

  • 주의: 대부분의 DDL은 암묵적 커밋(implicit commit)을 발생시키고, 대용량 테이블에서 블로킹 작업(테이블 잠금 또는 복사)을 유발할 수 있음.

  • 온라인 DDL/비차단 변경:

    • MySQL 5.6/5.7/8.0에서 점진적으로 개선되었으며 ALGORITHM=INPLACE, LOCK=NONE 옵션을 활용하면 일부 변경을 온라인으로 수행 가능:

ALTER TABLE t ADD COLUMN x INT, ALGORITHM=INPLACE, LOCK=NONE;

    • 여전히 지원되지 않는 변경이 있고, 안전을 위해 테스트 필요.

  • 대체 도구:

    • 대규모 프로덕션에서는 pt-online-schema-change (Percona Toolkit) 또는 gh-ost (GitHub) 같은 온라인 스키마 변경 도구를 사용. 이들은 트리거/복제/롤포워딩을 이용해 무중단으로 변경을 수행.

8. 백업·복구 관련(스키마만 추출 등)

  • 스키마(DDL)만 덤프:

mysqldump -u root -p --no-data my_app > my_app_schema.sql

  • 데이터만 덤프:

mysqldump -u root -p --no-create-info my_app > my_app_data.sql

  • 전체 백업(스키마+데이터):

mysqldump -u root -p --routines --triggers --events my_app > my_app_full.sql

  • 복원:

mysql -u root -p < my_app_schema.sql
mysql -u root -p my_app < my_app_data.sql

  • 큰 데이터베이스의 경우 mysqldump 대신 mysqlpump, xtrabackup(Percona) 등 증분/복구 친화적 도구 고려.

9. 정보 스키마와 진단

  • information_schema와 performance_schema는 메타데이터·성능 정보 제공.

-- 테이블 목록과 엔진 확인
SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'my_app';

-- 컬럼 정보
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA='my_app' AND TABLE_NAME='users';

10. 다중 테넌시(Multi-tenant) 설계 관점: 스키마 전략 비교

  1. 스키마(데이터베이스) 당 테넌트

    • 장점: 강한 격리, 백업/복원·마이그레이션 용이, 권한 분리 쉬움

    • 단점: 많은 DB(스키마) 생성 시 관리 부담, 리소스 사용 증가

  2. 단일 스키마, 테이블에 tenant_id 컬럼

    • 장점: 쉬운 확장·관리, 쿼리 최적화 가능

    • 단점: 데이터 격리 약함, 복구 시 테넌트 단위 복구 어려움

  3. 하이브리드: 규모 작은 테넌트는 공용 테이블, 큰 고객은 별도 스키마 등

  • 선택은 보안 요구·운영 편의·규모에 따라 결정.

11. 성능 관련 팁

  • 인덱스 설계: 주로 조회되는 컬럼·조합에 대해 복합 인덱스 고려. 너무 많은 인덱스는 쓰기 성능 저하.

  • EXPLAIN으로 쿼리 플랜 분석:

EXPLAIN SELECT u.* FROM users u JOIN orders o ON u.id = o.user_id WHERE u.status = 'active';

  • 파티셔닝(Partitioning): 매우 큰 테이블에서 파티션으로 조회·삭제 성능 개선. 파티셔닝 기준은 범위(range), 해시 등.

  • 테이블 엔진: 대부분 InnoDB 권장(트랜잭션, 외래키, MVCC 지원).

  • 통계 갱신: ANALYZE TABLE로 옵티마이저 통계 재생성.

12. 무결성·제약조건

  • 외래키(FK): InnoDB에서 지원. 복잡한 데이터 변경 시 FK 때문에 작업 실패할 수 있으니 순서를 신경 쓸 것.

ALTER TABLE orders
ADD CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id);

  • 유일성, 체크 제약: UNIQUE 인덱스 활용. MySQL 8.0부터 CHECK 제약도 제대로 지원(실행시 적용).

13. 마이그레이션 관리(버전 관리)

  • 이유: 여러 환경(dev/staging/prod)에서 DB 스키마 일관성 유지.

  • 도구: Flyway, Liquibase, Phinx, Doctrine Migrations 등.

  • 권장 방식:

    • SQL 기반 마이그레이션 파일을 버전 컨트롤(Git)에 보관.

    • CI/CD에서 마이그레이션 자동 적용(프로덕션은 수동 승인 단계 포함).

    • 마이그레이션은 되돌리기(rollback) 스크립트도 포함하면 안전.

14. 트랜잭션과 DDL

  • DDL은 보통 암묵적 커밋: CREATE TABLE/ALTER TABLE 등은 트랜잭션 경계를 무시하고 커밋을 발생시킵니다. 따라서 DDL 수행 전/후 트랜잭션 처리를 주의해야 함.

  • MySQL 8에서도 대부분 DDL은 커밋을 유발하므로 대량 작업 시 유의.

15. 운영상 체크리스트(실무 노하우)

  • 변경 전: 스키마 변경 영향분석(쿼리/인덱스/복제/백업 영향).

  • 테스트 환경: 반드시 staging에서 온라인 DDL 검증.

  • 백업: 스키마+데이터 백업 및 복구 절차 문서화.

  • 모니터링: slow query 로그, 성능 스키마로 변경 영향 관찰.

  • 권한 최소화: 서비스 계정에 필요한 권한만 부여.

  • 마이그레이션 자동화: 스크립트로 반복 가능한 변경 관리.

16. 실무 예제 모음

  1. 스키마 생성과 사용자 권한 부여

CREATE DATABASE shop DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'shop_app'@'%' IDENTIFIED BY 's3cUr3';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'shop_app'@'%';
FLUSH PRIVILEGES;

  1. 테이블 생성(인덱스·외래키 포함)

CREATE TABLE shop.users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY ux_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

  1. 스키마만 덤프

mysqldump -u root -p --no-data shop > shop_schema.sql

  1. 온라인 변경(가능한 경우)

ALTER TABLE shop.orders ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'pending',
ALGORITHM=INPLACE, LOCK=NONE;

17. 결론(요약)

  • MySQL에서 스키마는 데이터베이스와 동일하며, 네임스페이스·권한 단위로 중요합니다.

  • 설계(정규화·타입·인덱스), 변경(안전한 ALTER), 운영(백업·모니터링), 마이그레이션(버전 관리)이 모두 균형 있게 관리되어야 안정적인 서비스 운영이 가능합니다.

  • 대규모/프로덕션 환경에서는 온라인 DDL 도구와 마이그레이션 도구(Flyway 등)를 적극 활용하세요.


댓글