Azure Database for MySQL - 유연한 서버 문제 해결을 위한 모범 사례
적용 대상:
Azure Database for MySQL - 단일 서버 Azure Database for MySQL - 유연한 서버
Important
Azure Database for MySQL 단일 서버는 사용 중지 경로에 있습니다. Azure Database for MySQL 유연한 서버로 업그레이드하는 것이 좋습니다. Azure Database for MySQL 유연한 서버로 마이그레이션하는 방법에 대한 자세한 내용은 Azure Database for MySQL 단일 서버에 대한 새로운 소식을 참조하세요.
다음 섹션을 사용하여 Azure Database for MySQL 유연한 서버 데이터베이스를 원활하게 실행하고 이 정보를 스키마가 최적으로 설계되고 애플리케이션에 최상의 성능을 제공하도록 보장하는 지침 원칙으로 사용합니다.
인덱스 수 확인
사용량이 많은 데이터베이스 환경에서 높은 I/O 사용량을 관찰할 수 있으며 이는 불량한 데이터 액세스 패턴을 나타낼 수 있습니다. 사용되지 않는 인덱스는 디스크 공간과 캐시를 소비하고 쓰기 작업(INSERT/DELETE/UPDATE) 속도를 저하시키므로 성능에 부정적인 영향을 미칠 수 있습니다. 사용되지 않는 인덱스는 불필요하게 더 많은 스토리지을 소비하고 백업 크기를 증가시킵니다.
인덱스를 제거하기 전에 더 이상 사용되지 않는지 확인하기 위해 충분한 정보를 수집해야 합니다. 이 확인을 통해 분기별 또는 매년 실행되는 쿼리에 중요한 인덱스를 실수로 제거하는 것을 방지할 수 있습니다. 또한 인덱스가 고유성 또는 순서를 적용하는 데 사용되는지 여부를 고려해야 합니다.
참고 항목
인덱스를 주기적으로 검토하고 테이블 데이터에 대한 수정 사항을 기반으로 필요한 업데이트를 수행해야 합니다.
SELECT object_schema, object_name, index_name FROM performance_schema.table_io_waits_summary_by_index_usage WHERE index_name IS NOT NULL AND count_star = 0 ORDER BY object_schema, object_name;
(또는)
use information_schema; select tables.table_name, statistics.index_name, statistics.cardinality, tables.table_rows from tables join statistics on (statistics.table_name = tables.table_name and statistics.table_schema = '<YOUR DATABASE NAME HERE>' and ((tables.table_rows / statistics.cardinality) > 1000));
서버에서 가장 바쁜 인덱스 나열
다음 쿼리의 출력은 데이터베이스 서버의 모든 테이블과 스키마에서 가장 많이 사용되는 인덱스에 대한 정보를 제공합니다. 이 정보는 각 인덱스에 대한 쓰기 대 읽기 비율, 읽기 대기 시간 수 및 개별 쓰기 작업을 식별하는 데 도움이 됩니다. 이는 기본 테이블 및 종속 쿼리에 대해 추가 튜닝이 필요함을 나타낼 수 있습니다.
SELECT
object_schema AS table_schema,
object_name AS table_name,
index_name, count_star AS all_accesses,
count_read,
count_write,
Concat(Truncate(count_read / count_star * 100, 0), ':',
Truncate(count_write / count_star * 100, 0)) AS read_write_ratio,
count_fetch AS rows_selected ,
count_insert AS rows_inserted,
count_update AS rows_updated,
count_delete AS rows_deleted,
Concat(Round(sum_timer_wait / 1000000000000, 2), ' s') AS total_latency ,
Concat(Round(sum_timer_fetch / 1000000000000, 2), ' s') AS select_latency,
Concat(Round(sum_timer_insert / 1000000000000, 2), ' s') AS insert_latency,
Concat(Round(sum_timer_update / 1000000000000, 2), ' s') AS update_latency,
Concat(Round(sum_timer_delete / 1000000000000, 2), ' s') AS delete_latency
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL AND count_star > 0
ORDER BY sum_timer_wait DESC
기본 키 디자인 검토
Azure Database for MySQL 유연한 서버는 모든 비임시 테이블에 대해 InnoDB 스토리지 엔진을 사용합니다. InnoDB를 사용하면 데이터가 B-Tree 구조를 사용하여 클러스터형 인덱스 내에 저장됩니다. 테이블은 기본 키 값을 기반으로 실제로 구성됩니다. 즉, 기본 키 순서로 행이 저장됩니다.
InnoDB 테이블의 각 보조 키 항목에는 데이터가 저장된 기본 키 값에 대한 포인터가 포함됩니다. 즉, 보조 인덱스 항목에는 항목이 가리키는 기본 키 값의 복사본이 포함됩니다. 따라서 기본 키 선택은 테이블의 스토리지 오버헤드 양에 직접적인 영향을 미칩니다.
키가 실제 데이터(예: 사용자 이름, 이메일, SSN 등)에서 파생된 경우 이를 자연 키라고 합니다. 키가 인공적이고 데이터에서 파생되지 않은 경우(예: 자동 증가 정수) 가상 키 또는 서로게이트 키라고 합니다.
일반적으로 자연 기본 키를 사용하지 않는 것이 좋습니다. 이러한 키는 종종 매우 넓고 하나 또는 여러 열의 긴 값을 포함합니다. 이는 차례로 각 보조 키 항목에 복사되는 기본 키 값과 함께 심각한 스토리지 오버헤드를 유발할 수 있습니다. 게다가 자연 키는 일반적으로 미리 결정된 순서를 따르지 않으므로 행이 삽입되거나 업데이트될 때 성능이 크게 저하되고 페이지 조각화가 유발됩니다. 이러한 문제를 방지하려면 자연 키 대신 단계적으로 증가하는 서로게이트 키를 사용합니다. 자동 증가(큰) 정수 열은 단계적으로 증가하는 서로게이트 키의 좋은 예입니다. 특정 열 조합이 필요한 경우 고유해야 하며 해당 열을 고유한 보조 키로 선언합니다.
애플리케이션 빌드의 초기 단계에서는 테이블이 20억 개의 행에 접근하기 시작할 때를 상상하지 못할 수도 있습니다. 결과적으로 ID(기본 키) 열의 데이터 형식에 대해 부호 있는 4바이트 정수를 사용하도록 선택할 수 있습니다. 모든 테이블 기본 키를 확인하고 8바이트 정수(BIGINT) 열을 사용하도록 전환하여 높은 볼륨 또는 증가 가능성을 수용해야 합니다.
참고 항목
데이터 형식 및 최댓값에 대한 자세한 내용은 MySQL 참조 설명서에서 데이터 형식을 참조하세요.
커버링 인덱스 사용
이전 섹션에서는 MySQL의 인덱스가 B-트리로 구성되고 클러스터형 인덱스에서 리프 노드가 기본 테이블의 데이터 페이지를 포함하는 방법을 설명했습니다. 보조 인덱스는 클러스터형 인덱스와 동일한 B-트리 구조를 가지며 클러스터형 인덱스 또는 힙이 있는 테이블이나 뷰에서 정의할 수 있습니다. 보조 인덱스의 각 인덱스 행에는 클러스터되지 않은 키 값과 행 로케이터가 포함됩니다. 이 로케이터는 클러스터형 인덱스 또는 키 값이 포함된 힙의 데이터 행을 가리킵니다. 결과적으로 보조 인덱스와 관련된 모든 조회는 루트 노드에서 시작하여 분기 노드를 거쳐 올바른 리프 노드로 이동하여 기본 키 값을 가져와야 합니다. 그런 다음 시스템은 데이터 행을 가져오기 위해 기본 키 인덱스에서 임의의 IO 읽기를 실행합니다(루트 노드에서 분기 노드를 통해 올바른 리프 노드로 다시 한 번 탐색).
데이터 행을 가져오기 위해 기본 키 인덱스에서 이러한 추가 임의 IO 읽기를 방지하려면 쿼리에 필요한 모든 필드를 포함하는 커버링 인덱스를 사용합니다. 일반적으로 이 방법을 사용하면 I/O 바운드 워크로드 및 캐시된 워크로드에 유용합니다. 따라서 가장 좋은 방법은 커버링 인덱스가 메모리에 적합하고 모든 행을 검사하는 것보다 읽기에 더 작고 효율적이기 때문에 사용하는 것입니다.
예를 들어, 2000년 1월 1일 이후에 회사에 합류한 모든 직원을 찾는 데 사용하는 테이블을 생각해 보세요.
mysql> show create table employee\G
*************************** 1. row ***************************
Table: employee
Create Table: CREATE TABLE `employee` (
`empid` int(11) NOT NULL AUTO_INCREMENT,
`fname` varchar(10) DEFAULT NULL,
`lname` varchar(10) DEFAULT NULL,
`joindate` datetime DEFAULT NULL,
`department` varchar(10) DEFAULT NULL,
PRIMARY KEY (`empid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)`
`mysql> select empid, fname, lname from employee where joindate > '2000-01-01';
이 쿼리에 대해 EXPLAIN 계획을 실행하면 현재 인덱스가 사용되지 않고 where 절만 사용하여 직원 레코드를 필터링하고 있음을 알 수 있습니다.
mysql> EXPLAIN select empid, fname, lname from employee where joindate > '2000-01-01'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employee
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
filtered: 33.33
Extra: Using where
1 row in set, 1 warning (0.01 sec)
그러나 프로젝션된 열과 함께 where 절의 열을 포함하는 인덱스를 추가하면 인덱스가 열을 훨씬 빠르고 효율적으로 찾는 데 사용되고 있음을 알 수 있습니다.
mysql> CREATE INDEX cvg_idx_ex ON employee (joindate, empid, fname, lname);
이제 동일한 쿼리에 대해 EXPLAIN 계획을 실행하면 "추가" 필드에 "인덱스 사용" 값이 나타납니다. 이는 InnoDB가 이전에 만든 인덱스를 사용하여 쿼리를 실행한다는 것을 의미하며 이를 커버링 인덱스로 확인합니다.
mysql> EXPLAIN select empid, fname, lname from employee where joindate > '2000-01-01'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employee
partitions: NULL
type: range
possible_keys: cvg_idx_ex
key: cvg_idx_ex
key_len: 6
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where; Using index
1 row in set, 1 warning (0.01 sec)
참고 항목
쿼리를 올바르게 제공하려면 커버링 인덱스에서 올바른 열 순서를 선택하는 것이 중요합니다. 일반적인 규칙은 먼저 필터링할 열(WHERE 절)을 선택한 다음 정렬/그룹화(ORDER BY 및 GROUP BY) 및 마지막으로 데이터 프로젝션(SELECT)을 선택하는 것입니다.
이전 예에서 쿼리에 대한 커버링 인덱스가 있으면 더 효율적인 레코드 쿼리 경로를 제공하고 동시성이 높은 데이터베이스 환경에서 성능을 최적화하는 것을 보았습니다.
다음 단계
가장 중요한 질문에 대한 동료 답변을 찾거나 질문을 게시하거나 답변하려면 Stack Overflow를 방문합니다.