안녕하세요.
지금가지 Dual Master 구성을 진행 했었는데요. 가능하면 듀얼 마스터를 순환형으로 구축을 한번 해보는것도 좋을것 같다는 생각이 듭니다. 가능하면 여러번 연습해서 내꺼로 만드는걸 강력하게 추천 드립니다. 이번에는 Mysql 에 대해서 자세히 알아갈건데요. Information_schema, Table, Column, Index 등 기본 Design 구조부터 해서 항목들 설명을 진행하게 될것 같습니다. 순서는 아래 순서지만 이번글에 모두 포함되진 않으니 참고하시고 읽어주세요.
1.Information_schema 설명
2.Table, Column 정의 방식
3.Index 정의 방식
4.Table Design 방식
> 기본정의 , 추가 방식, Indexing
> Control, Monitering
> Explain > DDL(데이터 정의 언어)
> DML(데이터 조작 언어)
> DCL(데이터제어 언어)
Information_schema 설명
# Information_schema
> MySQL 서버 내에 존재하는 DB의 메타 정보 (테이블, 칼럼, 인덱스 등의 스키마 정보)를 모아둔 DB
> 읽기 전용 테이블로 단순 조회만 가능
> 실제 수치와 다름(SQL 조회시 메타 정보를 MySQL 서버의 메모리에서 가져와서 보여줌)
데이터베이스 또는 테이블의 이름, 컬럼의 데이터 타입, 또는 접근 권한과 같은 것을 말함.
데이터 사전(data dictionary) 과 시스템 카탈로그(system catalog)로도 불림
# 엔진 상태 및 데드락 정보 등 확인
SHOW ENGINE INNODB STATUS;
Mysql Table
1. 가능한 Primary Key를 정의
2. Data Type 및 Length는 작은 타입으로 선정
3. Dual Primary key가 이닐경우는 Primary Key는 auto_increment를 사용
4. 데이터 타입은 작게 선정하고 가능한 INT형을 사용
> date/datetime/timestamp
> IP는 INET_ATON('IP'), INET_NTOA(int) : 권장
5. 테이블 내 모든 필드에 NOT NULL 속성을 추가
6. Partitioning을 사용하여 Data 분산처리
> 주기적인 Data 삭제가 필요할경우 이용하기 좋음
7. data의 장기 보관을 위해서는 물리적으로 테이블을 분산 처리
> 일별,주별,월별,년별 테이블을 생성하여 Data를 보관
Mysql Index
1. 인덱스 개수를 최소화해서 설계
2. 인덱스 칼럼은 분포도를 고려하여 설정
> 인덱스 칼럼 데이터의 중복이 줄어들수록 인덱스는 최적화됨.
3. 커버링 인덱스(Covering Index)를 활용.
4. 스토리지 엔진 별 INDEX 특성에 따라 인덱스를 활용.
> InnoDB에서 데이터는 PK 순서로 저장, 인덱스는 PK를 Value로 가짐
> MyISAM은 PK와 일반 인덱스의 구조는 동일하나, Prefix 압축 인덱스를 사용
CREATE INDEX IDX01 ON TAB1(COL(4), COL(4))
5. 문자열을 인덱싱 시 Prefix 인덱스 활용한다.
6. 중복 인덱스 생성 금지
> Primary Key로 구성된 칼럼과 동일한 인덱스를 생성하지 않도록 함.
기타.
> CRC32함수 및 Trigger를 활용하여 인덱스 생성
> URL/Email같이 문자 길이기 긴 경우 32 bit unsigned 값으로 변환하여 저장 하는 방식
> 트리거에서 재 세팅해서 값을 기록
Table Design - 기본
효율적인 컬럼 타입을 선택
– Unsigned 대신에 Signed : 매출 건수
– DECIMAL(n, m) 대신에 INT : 금액
– INET_NTOA(), INET_ATON() : IP 주소
– String타입에 대해서 UTF8MB4 권장
• ALTER TABLE t CONVERT TO CHARSET utf8mb4;
– JSON 데이터를 TEXT대신에 JSON 타입으로 – 사용자 등록 테이블, 사용자 태그
• JSOM 함수 지원
• Functional 인덱스 지원
– 항상 “NOT NULL” 지정
Table Design - 추가
InnoDB 스토리지 엔진 추천
• PK 선택을 신중하게 선택하고 항상 명시적으로 지정
• Lock 및 meta-data lock을 감소하기 위해 큰 트랜잭션을 피해야 함
– max_execution_time 로 조회 쿼리 시간을 제어
> SELECT /*+ MAX_EXECUTION_TIME(20) */ mem_no FROM [ TB_NAME ] LIMIT 1000000;
– 별도의 replica 서버에서 복잡하고 긴 쿼리 실행
– OLPT(Online transaction processing) 와 OLAP (Online Analytical processing) 워크로드를 분리할 것을 고려
(많은 사람들에게 실시간으로 서버가 자료를 처리하는 과정 과 대용량 Data를 분석하기 위한 방식은 분리하여 구성해야함)
• Foreign keys 는 성능을 저하 시킴
– 데이터 변경에 대한 FK 검증.
– FK 로 인한 meta-data locks이 발생
• 압축 옵션으로 공간 절약, I/O 효율성 향상
– 이력 및 로그성 데이터, 용량이 크고, 성능에 크게 예민하지 않는 업무
1차 정리
테이블 설계시 하나의 테이블로 진행할것인가 아니면 대량을 고려해서 분산 설계를 할것인가에 따라 테이블 설계 방향을 달라집니다. 그리고 해당 Data의 종류에 따라서 일자,주간,월간등으로 분산할지도 결정이 됩니다. 또한 트리거를 활용하거나 스케쥴러를 활용하고, 파티션을 적용해야 한다면 그 방향은 더욱 심화하겠지만 그래도 설계자의 의도에 맞게 설계를 한다면 다음에 다른 방식으로 변경되더라도 마이그레이션이 가능하지 않을까 싶습니다.
DB 어떻게 보면 어려울수 있지만 어떻게 보면 내가 원하는 방향으로 개발하고, 그리고 Data를 분산한다면 보다 유연한 서비스 운영도 가능합니다.
감사합니다.
