tencent cloud

목적

• TencentDB for MySQL에 대한 관리 및 점검을 표준화하여 잘못된 작업으로 TencentDB for MySQL에 사용 불가 등의 영향이 발생하지 않도록 합니다.
• TencentDB for MySQL가 최적의 성능을 발휘할 수 있도록 데이터베이스 개발자의 효율적인 SQL 작성 지침을 안내합니다.

권한 관리 규범

• TencentDB for MySQL의 안정성과 보안성을 위해 TencentDB for MySQL은 super, shutdown, file 권한을 제한합니다. 간혹 TencentDB for MySQL에서 set 명령을 실행할 경우 다음과 같은 오류가 발생합니다.

  #1227-Access denied;you need(at least one of)the SUPER privilege (s) for this operation
  

  해결 방법: set 관련 매개변수의 수정이 필요한 경우, 콘솔 인스턴스 관리 페이지의 [데이터베이스 관리]>[매개변수 설정] 기능을 사용해 완료할 수 있습니다.
• 필요에 따라 권한을 부여합니다. 일반적으로 응용 프로그램은 DML(SELECT, UPDATE, INSERT, DELETE) 권한만 부여하면 사용 가능합니다.
• 객체 권한 최소화가 원칙이며, 일반적인 응용 프로그램 액세스 사용자에게는 데이터베이스 레벨에 따라 권한을 부여합니다.
• 사용자에게 액세스 권한 부여 시 특정 IP 또는 IP 세그먼트만 액세스를 허용하며, 콘솔에서 보안 그룹을 설정하여 제한할 수 있습니다. 보안 그룹 설정은 반드시 콘솔에서 안내하는 기준에 따라 작업합니다. 공용 네트워크에서 보안 그룹 설정에 액세스하는 시나리오의 경우 반드시 모든 관련 출력 IP를 개방하십시오.
• 관리 계정은 개발 계정과 분리합니다.

일반적인 작업 규범

주의 사항

• 데이터베이스 인스턴스 보안성 향상을 위해 보안이 취약한 비밀번호는 사용하지 마십시오.
• 내부 네트워크 연결 로그인은 클라이언트 측의 클라우드 서버 CVM과 클라우드 데이터베이스 MySQL이 같은 계정과 같은 지역 및 같은 VPC의 기계임을 확보해야 한다.
• 콘솔에서 다운로드한 binlog 로그를 로컬에서 분석해야 할 경우, 클라이언트 측 MySQL 버전과 TencentDB for MySQL 인스턴스 버전이 반드시 일치해야 합니다. 그렇지 않을 경우 분석 시 글자가 깨지는 현상이 발생할 수 있습니다. 3.4 버전 이상의 mysqlbinlog 사용을 권장합니다.
• 콘솔에서 내부 네트워크를 통해 CVM에서 콜드 스탠바이 파일을 업로드/다운로드할 때는 url을 따옴표로 묶으십시오. 그렇지 않을 경우 404 오류가 발생할 수 있습니다.

권장 사항

• 최대한 비즈니스 피크 시간대를 피해 online ddl 작업을 진행하시기 바랍니다. 사용 가능한 툴은 pt-online-schema-change를 참고하십시오.
• 최대한 비즈니스 피크 시간대를 피해 데이터 일괄 작업을 진행하십시오. 업무가 몰리지 않는 시간대에 나눠서 작업하는 것이 가장 좋습니다.
• 최대한 한 인스턴스에서 여러 비즈니스를 실행하지 마십시오. 결합도가 지나치게 높으면 비즈니스 간에 서로 영향을 미칠 수 있습니다.
• 트랜잭션 자동 제출을 비활성화하고 온라인 작업 시 begin을 먼저 실행하면 오작업으로 인한 데이터 손실 위험을 낮출 수 있습니다. 오작업 시 TencentDB for MySQL의 롤백 기능을 사용할 수 있습니다(현재 5일 이내 임의 시간대로 롤백 지원). 관련 테이블이 데이터베이스 간, 테이블 간 로직과 관련되어 있지 않다면 빠른 롤백 또는 고속 롤백을 사용해 데이터를 더욱 빨리 복구할 수 있습니다. 롤백 시 DB 테이블 이름은 '기존 DB 테이블 이름_bak'으로 생성됩니다.
• 서비스에는 홍보 이벤트 등이 있습니다. 미리 리소스를 예측하고 인스턴스 관련 최적화 작업을 완료하시기 바랍니다. 수요가 많은 경우 해당 서비스 매니저에게 문의하시기 바랍니다.

DB 테이블 설계 규범

주의 사항

• TencentDB for MySQL 5.6 이상 버전에서는 MyISAM 엔진과 Memory 엔진을 지원하지 않습니다. Memory 엔진이 필요한 경우 TencentDB for Redis 및 Memcached 사용을 권장합니다. 자체구축 데이터베이스를 TencentDB for MySQL에 마이그레이션할 경우 자동으로 MyISAM 엔진을 InnoDB 엔진으로 전환합니다.
• 자동 추가 열이 있는 테이블의 경우, 자동 추가 열에 반드시 1개 이상의 단독 인덱스가 있거나 자동 추가 열로 시작하는 복합 인덱스가 있어야 합니다.
• row_format은 반드시 고정되지 않아야 합니다.
• 모든 테이블에는 반드시 기본 키가 있어야 합니다. 기본 키로 선택할 적합한 열이 없더라도 반드시 무의미한 열을 추가하여 기본 키를 만들어야 합니다. MySQL의 1순위 표준형 기준인 InnoDB 보조 인덱스 리프 노드는 기본 키 값을 저장합니다. 자동으로 추가된 짧은 열을 기본 키로 설정하면 인덱스가 디스크에서 차지하는 용량이 적어져 효율이 향상됩니다. binlog_format이 row인 경우 일괄 삭제 데이터에 기본 키가 없으면 심각한 마스터/슬레이브 딜레이가 발생합니다.
• 필드를 최대한 NOT NULL로 정의하고 기본값을 작성합니다. NULL은 SQL 개발에 여러 문제를 일으켜 인덱스를 불가능하게 만듭니다. NULL 계산 시 IS NULL과 IS NOT NULL만 사용해 판단합니다.

권장 사항

• 비즈니스 시나리오 분석과 데이터 액세스(데이터 읽기/쓰기 QPS, TPS, 스토리지 용량 등) 예측을 통해 데이터베이스 사용 리소스를 합리적으로 계획합니다. 콘솔의 클라우드 모니터링 인터페이스에서도 TencentDB for MySQL 인스턴스의 각 항목 모니터링을 설정할 수 있습니다.
• 데이터베이스 생성 원칙은 동일 유형 비즈니스의 테이블을 같은 데이터베이스에 두는 것입니다. 서로 다른 비즈니스의 테이블은 최대한 같은 데이터베이스를 공유하지 않도록 하고, 프로그램에서 데이터베이스 간의 연결 작업을 실행하지 않도록 합니다. 해당 작업은 이후 빠른 롤백에도 일정한 영향을 미칩니다.
• 문자 세트는 일괄적으로 utf8mb4를 사용해 글자가 깨지는 리스크를 줄입니다. 일부 복잡한 한자와 이모티콘은 반드시 utf8mb4 방식을 사용해야만 정상적으로 표시됩니다. 문자 세트를 수정하면 수정 후 생성하는 테이블에만 적용되므로, TencentDB for MySQL를 구매하고 인스턴스를 초기화할 때 utf8mb4로 선택하는 것을 권장합니다.
• 소수 필드는 decimal 유형 사용을 권장합니다. float과 double은 정밀도가 부족합니다. 특히 금전과 관련된 비즈니스는 반드시 decimal을 사용해야 합니다.
• 대용량 텍스트, 바이너리 데이터, 이미지, 파일 등의 콘텐츠 저장 시 최대한 로컬 디스크 파일로 저장하고, 데이터베이스에서 text/blob을 사용한 저장은 피하십시오. 데이터베이스에는 해당 인덱스 정보만 저장됩니다.
• 외래 키는 가급적 사용하지 마십시오. 외래 키 로직은 응용 레이어에서 실행하는 것을 권장합니다. 외래 키와 캐스케이드 업데이트는 동시 접속이 높은 시나리오에는 적합하지 않습니다. 삽입 성능을 저하시켜 대규모 동시 접속 시 데드락이 쉽게 발생합니다.
• 비즈니스 로직과 데이터 스토리지의 결합도를 낮추고, 데이터베이스 스토리지 데이터를 위주로 합니다. 비즈니스 로직은 최대한 응용 레이어를 통해 실행하고, 저장 과정, 트리거, 함수, event, 뷰 등 고급 기능 사용을 최소화합니다. 해당 기능들은 이식성, 확장 가능성이 비교적 낮으므로 인스턴스에 해당 객체가 존재할 경우 기본 값을 definer로 설정하지 않기를 권장합니다. 마이그레이션 계정과 definer의 불일치로 마이그레이션이 실패할 수 있습니다.
• 단기간 내에 비즈니스가 비교적 큰 규모에 도달하지 못할 경우 파티션 테이블은 사용하지 않는 것을 권장합니다. 파티션 테이블은 주로 보관 관리에 사용되며, 대부분 물류업과 전자 상거래 주문표에 사용됩니다. 비즈니스의 80% 이상이 필드를 구분하여 쿼리하는 경우를 제외하고 성능을 높이는 역할을 하지 않습니다.
• 읽기 부하가 크고, 일치성 요구가 비교적 낮은(데이터 수신 시 초 단위 딜레이) 비즈니스 시나리오는 읽기 전용 인스턴스를 구매해 슬레이브 데이터베이스로 읽기/쓰기 분리 정책 구현을 권장합니다.

인덱스 설계 규범

주의 사항

• 업데이트가 매우 빈번하고 구분도가 높지 않은 열에 인덱스를 생성하지 마십시오. 기록 업데이트 시 B+ 트리가 변경됩니다. 업데이트가 잦은 필드에 인덱스를 생성하면 데이터베이스 성능이 크게 저하됩니다.
• 복합 인덱스 생성 시 구분도가 가장 높은 열을 인덱스 가장 왼쪽에 둡니다. 예를 들어, select xxx where a = x and b = x;에서 a와 b가 함께 그룹 인덱스를 생성하고 a의 구분도가 더 높은 경우 idx_ab(a,b)'를 생성합니다. 비등호와 등호가 혼합된 판단 조건이 있는 경우 반드시 등호 조건 열을 앞에 놓아야 합니다. 예를 들어, where a xxx and b = xxx`는 a의 구분도가 더 높아도 반드시 b를 인덱스의 가장 앞 열에 놓아야 합니다. 인덱스 a에 접근할 수 없기 때문입니다.

권장 사항

• 단일 테이블의 인덱스 수는 5개를 초과하지 않고, 단일 인덱스에서 필드 수는 5개를 초과하지 않는 것이 좋습니다. 너무 많으면 필터링할 수 없으며, 인덱스도 용량을 차지하여 관리에 리소스가 소모됩니다.
• 비즈니스 SQL에서 가장 많이 필터링되고, cardinality 값이 비교적 높은 열에 인덱스를 생성하십시오. 비즈니스 SQL에서 접근하지 않는 열에 인덱스를 생성하면 아무 의미가 없습니다. 필드의 고유성이 높을수록, 즉 대표하는 cardinality 값이 높을수록 인덱스 필터 효과도 좋아집니다. 일반적으로 인덱스 열의 cardinality 기록 수가 10% 미만이면 저효율 인덱스라 판단합니다(예: 성별 필터).
• varchar 필드에 인덱스를 만들 때 인덱스 길이를 지정하는 것이 좋으며, 직접 전체 열에 인덱스를 생성하는 것은 권장하지 않습니다. 일반적으로 varchar 열은 비교적 길어서 일정 길이를 지정해 인덱스를 만들 경우 이미 구분도가 높으므로, 전체 열에 인덱스를 생성할 필요가 없습니다. 전체 열에 인덱스를 생성하면 용량이 커져 인덱스 점검 비용이 증가할 수 있습니다. count(distinct left(열 이름, 인덱스 길이))/count(*)를 사용해 인덱스 구분도를 확인할 수 있습니다.
• 인덱스가 중복되는 것을 피하십시오. 두 개의 인덱스 (a,b) (a)가 동시에 존재한다면, (a)는 잉여 인덱스 redundant index에 속합니다. 조회 필터 조건이 a열이면, (a,b) 인덱스로 충분하므로 (a) 인덱스를 단독으로 생성하지 않아도 됩니다.
• 인덱스 덮어쓰기를 합리적으로 이용하여 IO 부하를 낮춥니다. InnoDB 2단계 인덱스의 리프 노드에는 자체 키 값과 기본 키 값만 저장합니다. SQL 쿼리가 인덱스 열이나 기본 키가 아닌 경우, 이 인덱스를 실행해 먼저 해당하는 기본 키를 찾고, 다시 기본 키에 따라 찾으려는 열을 찾습니다. 이것이 테이블 복구(flashback table)입니다. 이 경우 별도의 IO 부하가 발생하며, 이때 인덱스 덮어쓰기를 이용해 문제를 해결할 수 있습니다. 예를 들어, select a,b from xxx where a = xxx에서 a가 기본 키가 아니라면 a, b 두 개 열의 복합 인덱스를 생성할 수 있습니다. 이 경우 테이블 복구를 하지 않습니다.

SQL 작성 규범

주의 사항

• UPDATE, DELETE 작업은 LIMIT를 사용하지 않고, 반드시 WHERE를 사용해 정확히 매칭합니다. LIMIT는 랜덤이므로 해당 작업은 데이터 오류를 일으킬 수 있습니다.

• INSERT INTO t_xxx VALUES(xxx)를 사용하지 마십시오. 테이블 구조 변경으로 인한 데이터 오류가 발생하지 않도록, 반드시 삽입하는 열의 속성을 명시적으로 지정해야 합니다.

• SQL 명령에서 가장 흔한 인덱스의 효력이 사라지는 상황에 주의해야 합니다.

• 내장 유형을 전환합니다. 인덱스 a의 유형이 varchar인 경우, SQL 명령을 where a = 1; varchar로 작성하면 int로 전환됩니다.

• 인덱스 열에 수학 계산과 함수 등의 작업을 진행합니다. 예를 들어, 함수를 사용해 날짜 열을 포맷 처리합니다.

• join 열 문자 세트 불일치 시 주의해야 합니다.

• 여러 열의 정렬 순서 불일치에 주의해야 합니다. 인덱스가 (a,b)인 경우 SQL 명령은 order by a b desclike입니다.

• 모호한 쿼리를 사용할 경우 문자 세트 유형 xx% 형식은 일부 인덱스에 접근할 수 있으나, 다른 상황에서는 인덱스에 접근할 수 없습니다.

• 네거티브 쿼리(not, !=, not in 등) 사용 시 주의해야 합니다.

  권장 사항

• 필요에 따라 요청하고 select *를 거부하여 다음 문제를 방지합니다.

  • 인덱스 덮어쓰기가 불가능하여 테이블 복구 작업으로 I/O가 증가하는 문제
  • 추가적인 메모리 부담 및 대량의 콜드 데이터를 innodb_buffer_pool_size로 가져와 조회 히트율이 감소하는 문제
  • 추가적인 네트워크 전송 부하

• 최대한 대규모 트랜잭션 사용을 피하십시오. 대규모 트랜잭션을 소규모 트랜잭션으로 분할하여 마스터/슬레이브 딜레이를 방지합니다.

• 비즈니스 코드의 트랜잭션을 즉시 제출해 불필요한 락 대기가 발생하지 않도록 합니다.

• 다중 테이블 join을 적게 사용하고, 큰 테이블은 join을 금지합니다. 두 개의 테이블을 join할 때에는 반드시 작은 테이블을 드라이버 테이블로 만들고, join 열은 문자 세트가 일치해야 하며 모두 인덱스가 있어야 합니다.

• LIMIT 페이징을 최적화합니다. LIMIT 80000, 10과 같은 작업은 80010개의 기록을 검색한 후 다시 10개를 반환하는 작업으로, 데이터베이스에 큰 부담을 줍니다. 먼저 첫 번째 기록의 위치를 확인하고 다시 페이징하는 것을 권장합니다. 예시: SELECT * FROM test WHERE id >= ( SELECT sql_no_cache id FROM test order by id LIMIT 80000,1 ) LIMIT 10 ;

• 다중 레이어 서브 쿼리가 중첩된 SQL 명령을 피하십시오. MySQL 5.5 이전의 쿼리 옵터마이저가 in을 exists로 변경하여 인덱스가 유효하지 않게 될 수 있습니다. 외부 테이블이 크면 성능이 저하됩니다.

설명：

• 위와 같은 상황을 완전히 피하기는 어렵습니다. 이런 조건들을 주요 필터링 조건으로 설정하지 않는 것을 권장합니다. 인덱스의 주요 필터링 조건 뒤에 실행하면 문제가 크지 않습니다.
• 모니터링에서 전체 테이블 스캔 양이 비교적 많은 것을 발견할 경우, 콘솔에서 매개변수를 log_queries_not_using_indexes로 설정하고, 잠시 후 슬로우 로그 파일 분석을 다운로드할 수 있습니다. 단, 너무 오래 켜두면 슬로우 로그가 폭증할 수 있습니다.
• 비즈니스 런칭 전 필요한 SQL 심사를 점검해야 하며, 상시 유지보수 시 주기적으로 슬로우 쿼리 로그를 다운로드하여 맞춤형으로 최적화해야 합니다.