MySQL 스키마(schema)

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) 설계 관점: 스키마 전략 비교 - 스키마(데이터베이스) 당 테넌트 - 장점: 강한 격리, 백업/복원·마이그레이션 용이, 권한 분리 쉬움 - 단점: 많은 DB(스키마) 생성 시 관리 부담, 리소스 사용 증가 - 단일 스키마, 테이블에 tenant_id 컬럼 - 장점: 쉬운 확장·관리, 쿼리 최적화 가능 - 단점: 데이터 격리 약함, 복구 시 테넌트 단위 복구 어려움 - 하이브리드: 규모 작은 테넌트는 공용 테이블, 큰 고객은 별도 스키마 등 - 선택은 보안 요구·운영 편의·규모에 따라 결정. 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. 실무 예제 모음 - 스키마 생성과 사용자 권한 부여 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; - 테이블 생성(인덱스·외래키 포함) 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; - 스키마만 덤프 mysqldump -u root -p --no-data shop > shop_schema.sql - 온라인 변경(가능한 경우) ALTER TABLE shop.orders ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'pending', ALGORITHM=INPLACE, LOCK=NONE; 17. 결론(요약) - MySQL에서 스키마는 데이터베이스와 동일하며, 네임스페이스·권한 단위로 중요합니다. - 설계(정규화·타입·인덱스), 변경(안전한 ALTER), 운영(백업·모니터링), 마이그레이션(버전 관리)이 모두 균형 있게 관리되어야 안정적인 서비스 운영이 가능합니다. - 대규모/프로덕션 환경에서는 온라인 DDL 도구와 마이그레이션 도구(Flyway 등)를 적극 활용하세요.