반응형
동일한 인덱스를 가진 유사한 테이블에서 서로 다른 성능을 갖는 MariaDB
현재 MariaDB(v10.5.10)를 사용하는 애플리케이션에서 성능을 최적화하려고 합니다.성능이 낮은 쿼리를 분석할 때 ANALYZE 문은 특정 블록에서 실행 시간이 예상보다 길어 혼란스럽습니다.
관련 테이블의 CREATE 문입니다.
CREATE TABLE `position` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`comment` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`created_at` DATETIME NULL DEFAULT NULL,
`updated_at` DATETIME NULL DEFAULT NULL,
`deleted_at` DATETIME NULL DEFAULT NULL,
`version` INT(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1692
;
CREATE TABLE `position_info` (
`id` CHAR(36) NOT NULL COLLATE 'utf8_bin',
`position_id` INT(11) NULL DEFAULT NULL,
`bezeichnung` TEXT NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`location` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`bereich` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`valid_from` DATETIME NULL DEFAULT NULL,
`valid_to` DATETIME NULL DEFAULT NULL,
`created_by` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`updated_by` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`deleted_by` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`created_at` DATETIME NULL DEFAULT NULL,
`updated_at` DATETIME NULL DEFAULT NULL,
`deleted_at` DATETIME NULL DEFAULT NULL,
`version` INT(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `position_info_position_idx` (`position_id`) USING BTREE,
INDEX `position_info_created_idx` (`created_by`) USING BTREE,
INDEX `position_info_updated_idx` (`updated_by`) USING BTREE,
INDEX `position_info_deleted_idx` (`deleted_by`) USING BTREE,
INDEX `pi_valid_from_index` (`position_id`, `valid_from`) USING BTREE,
CONSTRAINT `position_info_created_idx` FOREIGN KEY (`created_by`) REFERENCES `db`.`user` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT `position_info_deleted_idx` FOREIGN KEY (`deleted_by`) REFERENCES `db`.`user` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT `position_info_position_idx` FOREIGN KEY (`position_id`) REFERENCES `db`.`position` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT `position_info_updated_idx` FOREIGN KEY (`updated_by`) REFERENCES `db`.`user` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;
성능이 낮은 SQL 쿼리의 단축 버전(총 2172ms).
SELECT `positionInfo`.*
FROM (
SELECT *
FROM (
SELECT `id`, `position_id` AS `positionId`, `bezeichnung`, `location`, `bereich`, `valid_from` AS `validFrom`, `valid_to` AS `validTo`, `created_by` AS `createdBy`, `updated_by` AS `updatedBy`, `deleted_by` AS `deletedBy`, `created_at`, `updated_at`, `deleted_at`, `version`, `created_at` AS `createdAt`, `updated_at` AS `updatedAt`, `deleted_at` AS `deletedAt`
FROM `position_info` AS `positionInfo`
WHERE (`positionInfo`.`deleted_at` IS NULL AND ((`positionInfo`.`deleted_at` IS NULL AND `positionInfo`.`valid_from` <= '2022-03-20 22:59:59.999'))) AND `positionInfo`.`position_id` = 256
ORDER BY `validFrom` DESC
LIMIT 1) AS sub UNION ALL
SELECT *
FROM (
SELECT `id`, `position_id` AS `positionId`, `bezeichnung`, `location`, `bereich`, `valid_from` AS `validFrom`, `valid_to` AS `validTo`, `created_by` AS `createdBy`, `updated_by` AS `updatedBy`, `deleted_by` AS `deletedBy`, `created_at`, `updated_at`, `deleted_at`, `version`, `created_at` AS `createdAt`, `updated_at` AS `updatedAt`, `deleted_at` AS `deletedAt`
FROM `position_info` AS `positionInfo`
WHERE (`positionInfo`.`deleted_at` IS NULL AND ((`positionInfo`.`deleted_at` IS NULL AND `positionInfo`.`valid_from` <= '2022-03-20 22:59:59.999'))) AND `positionInfo`.`position_id` = 18
ORDER BY `validFrom` DESC
LIMIT 1) AS sub UNION ALL
...
쿼리는 JavaScript ORM인 Suquetize에 의해 더 복잡한 쿼리에 대한 별도의 쿼리로 생성됩니다.where 문은 position_id 필드와 valid_from 필드를 모두 사용하므로 두 필드 모두에 대한 복합 인덱스인 pi_valid_from_index를 생성했습니다.
분석해보니 다음과 같은 결과가 나옵니다.
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 1809.757232,
"table": {
"table_name": "<derived2>",
"access_type": "ALL",
"r_loops": 1,
"rows": 350,
"r_rows": 171,
"r_table_time_ms": 0.46998943,
"r_other_time_ms": 0.151807862,
"filtered": 100,
"r_filtered": 100,
"materialized": {
"query_block": {
"union_result": {
"table_name": "<union2,4,6,8,10,12,14,16,18,20,22,24,26,28,30,32,34,36,38,40,42,44,46,48,50,52,54,56,58,60,62,64,66,68,70,72,74,76,78,80,82,84,86,88,90,92,94,96,98,100,102,104,106,108,110,112,114,116,...>",
"access_type": "ALL",
"r_loops": 0,
"r_rows": null,
"query_specifications": [
{
"query_block": {
"select_id": 2,
"r_loops": 1,
"r_total_time_ms": 16.59932409,
"table": {
"table_name": "<derived3>",
"access_type": "ALL",
"r_loops": 1,
"rows": 2,
"r_rows": 1,
"r_table_time_ms": 0.044354854,
"r_other_time_ms": 0.01983318,
"filtered": 100,
"r_filtered": 100,
"materialized": {
"query_block": {
"select_id": 3,
"r_loops": 1,
"r_total_time_ms": 0.10711399,
"table": {
"table_name": "positionInfo",
"access_type": "range",
"possible_keys": [
"position_info_position_idx",
"pi_valid_from_index"
],
"key": "pi_valid_from_index",
"key_length": "11",
"used_key_parts": ["position_id", "valid_from"],
"r_loops": 1,
"rows": 1,
"r_rows": 1,
"r_table_time_ms": 0.028658321,
"r_other_time_ms": 0.019230482,
"filtered": 100,
"r_filtered": 100,
"attached_condition": "positioninfo.position_id <=> 256 and positioninfo.deleted_at is null and positioninfo.deleted_at is null and positioninfo.valid_from <= '2022-03-20 22:59:59.999'"
}
}
}
}
}
},
{
"query_block": {
"select_id": 4,
"operation": "UNION",
"r_loops": 1,
"r_total_time_ms": 11.18885812,
"table": {
"table_name": "<derived5>",
"access_type": "ALL",
"r_loops": 1,
"rows": 2,
"r_rows": 1,
"r_table_time_ms": 0.040932656,
"r_other_time_ms": 0.01641161,
"filtered": 100,
"r_filtered": 100,
"materialized": {
"query_block": {
"select_id": 5,
"r_loops": 1,
"r_total_time_ms": 0.097741398,
"table": {
"table_name": "positionInfo",
"access_type": "range",
"possible_keys": [
"position_info_position_idx",
"pi_valid_from_index"
],
"key": "pi_valid_from_index",
"key_length": "11",
"used_key_parts": ["position_id", "valid_from"],
"r_loops": 1,
"rows": 1,
"r_rows": 1,
"r_table_time_ms": 0.025234867,
"r_other_time_ms": 0.020159014,
"filtered": 100,
"r_filtered": 100,
"attached_condition": "positioninfo.position_id <=> 18 and positioninfo.deleted_at is null and positioninfo.deleted_at is null and positioninfo.valid_from <= '2022-03-20 22:59:59.999'"
}
}
}
}
}
},
...
DB가 예상대로 인덱스를 사용하고 있습니다.앞서 살펴본 바와 같이 중간 블록의 r_total_time_ms는 대부분의 블록에서 약 10ms이지만 더 깊은 블록의 시간은 여기에 해당하지 않습니다.DB에서 발생하는 다른 일들과 같은 원인도 있을 수 있다는 것을 알고 있지만, 추가 트래픽 없이 테스트 DB에서 테스트됩니다.
가장 혼란스러운 것은 더 깊은 블록에서는 r_total_time_ms가 비슷하지만 중간 블록에서는 시간이 훨씬 짧은 유사한 테이블에 두 번째 쿼리가 있다는 것입니다.아래 두 번째 테이블에서 더 빠르게 작동하는 동일한 정보를 추가했습니다.
또 다른 놀라운 점은 position_id에 대한 인덱스만 사용할 때 복합 인덱스가 없어도 성능이 동일하게 유지된다는 것입니다.두 번째 표에 인덱스를 추가하면 성능이 약 80ms에서 7ms로 향상되었습니다.
관련 테이블의 CREATE 문입니다.
CREATE TABLE `aircraft` (
`id` CHAR(36) NOT NULL COLLATE 'utf8_bin',
`registration` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`created_by` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`created_at` DATETIME NULL DEFAULT NULL,
`updated_at` DATETIME NULL DEFAULT NULL,
`deleted_at` DATETIME NULL DEFAULT NULL,
`version` INT(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `aircraft_created_idx` (`created_by`) USING BTREE,
CONSTRAINT `aircraft_created_idx` FOREIGN KEY (`created_by`) REFERENCES `db`.`user` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;
CREATE TABLE `aircraft_assignment` (
`id` CHAR(36) NOT NULL COLLATE 'utf8_bin',
`aircraft_id` CHAR(36) NULL DEFAULT NULL COLLATE 'utf8_bin',
`airline_id` INT(11) NULL DEFAULT NULL,
`type` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`class` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`category` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`created_by` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`valid_from` DATETIME NULL DEFAULT NULL,
`valid_to` DATETIME NULL DEFAULT NULL,
`created_at` DATETIME NULL DEFAULT NULL,
`updated_at` DATETIME NULL DEFAULT NULL,
`deleted_at` DATETIME NULL DEFAULT NULL,
`version` INT(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `aircraft_assignment_aircraft_idx` (`aircraft_id`) USING BTREE,
INDEX `aircraft_assignment_airline_idx` (`airline_id`) USING BTREE,
INDEX `aircraft_assignment_created_idx` (`created_by`) USING BTREE,
INDEX `aa_valid_from_index` (`aircraft_id`, `valid_from`) USING BTREE,
CONSTRAINT `aircraft_assignment_aircraft_idx` FOREIGN KEY (`aircraft_id`) REFERENCES `db`.`aircraft` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT `aircraft_assignment_airline_idx` FOREIGN KEY (`airline_id`) REFERENCES `db`.`airline` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT `aircraft_assignment_created_idx` FOREIGN KEY (`created_by`) REFERENCES `db`.`user` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;
더 나은 성능을 제공하는 SQL 쿼리의 단축 버전(총 31ms)
SELECT *
FROM (
SELECT `id`, `aircraft_id` AS `aircraftId`, `airline_id` AS `airlineId`, `type`, `class`, `category`, `created_by` AS `createdBy`, `valid_from` AS `validFrom`, `valid_to` AS `validTo`, `created_at`, `updated_at`, `deleted_at`, `version`, `created_at` AS `createdAt`, `updated_at` AS `updatedAt`, `deleted_at` AS `deletedAt`
FROM `aircraft_assignment` AS `aircraftAssignment`
WHERE (`aircraftAssignment`.`deleted_at` IS NULL AND ((`aircraftAssignment`.`deleted_at` IS NULL AND `aircraftAssignment`.`valid_from` <= '2022-03-20 22:59:59.999'))) AND `aircraftAssignment`.`aircraft_id` = 'd27feb20-7de9-4500-b0a5-9965aa9fb80a'
ORDER BY `validFrom` DESC
LIMIT 1) AS sub UNION ALL
SELECT *
FROM (
SELECT `id`, `aircraft_id` AS `aircraftId`, `airline_id` AS `airlineId`, `type`, `class`, `category`, `created_by` AS `createdBy`, `valid_from` AS `validFrom`, `valid_to` AS `validTo`, `created_at`, `updated_at`, `deleted_at`, `version`, `created_at` AS `createdAt`, `updated_at` AS `updatedAt`, `deleted_at` AS `deletedAt`
FROM `aircraft_assignment` AS `aircraftAssignment`
WHERE (`aircraftAssignment`.`deleted_at` IS NULL AND ((`aircraftAssignment`.`deleted_at` IS NULL AND `aircraftAssignment`.`valid_from` <= '2022-03-20 22:59:59.999'))) AND `aircraftAssignment`.`aircraft_id` = 'de648ae2-4dff-431f-bbe7-7514d8902d0e'
ORDER BY `validFrom` DESC
LIMIT 1) AS sub UNION ALL
...
분석해보니 다음과 같은 결과가 나옵니다.
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 7.936409288,
"table": {
"table_name": "<derived2>",
"access_type": "ALL",
"r_loops": 1,
"rows": 342,
"r_rows": 171,
"r_table_time_ms": 0.166444022,
"r_other_time_ms": 0.15610209,
"filtered": 100,
"r_filtered": 100,
"materialized": {
"query_block": {
"union_result": {
"table_name": "<union2,4,6,8,10,12,14,16,18,20,22,24,26,28,30,32,34,36,38,40,42,44,46,48,50,52,54,56,58,60,62,64,66,68,70,72,74,76,78,80,82,84,86,88,90,92,94,96,98,100,102,104,106,108,110,112,114,116,...>",
"access_type": "ALL",
"r_loops": 0,
"r_rows": null,
"query_specifications": [
{
"query_block": {
"select_id": 2,
"r_loops": 1,
"r_total_time_ms": 0.128546205,
"table": {
"table_name": "<derived3>",
"access_type": "ALL",
"r_loops": 1,
"rows": 2,
"r_rows": 1,
"r_table_time_ms": 0.013479732,
"r_other_time_ms": 0.016444884,
"filtered": 100,
"r_filtered": 100,
"materialized": {
"query_block": {
"select_id": 3,
"r_loops": 1,
"r_total_time_ms": 0.102809089,
"table": {
"table_name": "aircraftAssignment",
"access_type": "range",
"possible_keys": [
"aircraft_assignment_aircraft_idx",
"aa_valid_from_index"
],
"key": "aa_valid_from_index",
"key_length": "115",
"used_key_parts": ["aircraft_id", "valid_from"],
"r_loops": 1,
"rows": 5,
"r_rows": 3,
"r_table_time_ms": 0.040696599,
"r_other_time_ms": 0.030328298,
"filtered": 100,
"r_filtered": 33.33333333,
"attached_condition": "aircraftassignment.aircraft_id <=> 'd27feb20-7de9-4500-b0a5-9965aa9fb80a' and aircraftassignment.deleted_at is null and aircraftassignment.valid_from <= '2022-03-20 22:59:59.999'"
}
}
}
}
}
},
{
"query_block": {
"select_id": 4,
"operation": "UNION",
"r_loops": 1,
"r_total_time_ms": 0.049281287,
"table": {
"table_name": "<derived5>",
"access_type": "ALL",
"r_loops": 1,
"rows": 2,
"r_rows": 1,
"r_table_time_ms": 0.002692682,
"r_other_time_ms": 0.006288156,
"filtered": 100,
"r_filtered": 100,
"materialized": {
"query_block": {
"select_id": 5,
"r_loops": 1,
"r_total_time_ms": 0.035126031,
"table": {
"table_name": "aircraftAssignment",
"access_type": "range",
"possible_keys": [
"aircraft_assignment_aircraft_idx",
"aa_valid_from_index"
],
"key": "aa_valid_from_index",
"key_length": "115",
"used_key_parts": ["aircraft_id", "valid_from"],
"r_loops": 1,
"rows": 9,
"r_rows": 1,
"r_table_time_ms": 0.013620362,
"r_other_time_ms": 0.009586675,
"filtered": 0.100033343,
"r_filtered": 100,
"attached_condition": "aircraftassignment.aircraft_id = 'de648ae2-4dff-431f-bbe7-7514d8902d0e' and aircraftassignment.deleted_at is null and aircraftassignment.valid_from <= '2022-03-20 22:59:59.999'"
}
}
}
}
}
},
...
보이는 것처럼, 중간 블록의 r_total_time_ms는 예상대로 ca. 더 깊은 시간의 합과 같습니다.두 테이블 간의 주요 차이점은 위치 테이블은 id와 같은 정수를 사용하고 항공기 테이블은 uuid를 사용한다는 것입니다. 하지만 저는 이것이 성능에 실질적인 영향을 미치지 않을 것이라고 생각합니다. 그러면 정수 id가 더 나은 성능을 기대할 수 있습니다.
테이블의 총 행 수는 다음과 같습니다.
: 1691 ▁16 : 1691
치정보: 790
: 1735년식
배정: 항공기 배정: 8997
positionInfo는 aircraftAssignment보다 행 수가 적지만 성능이 더 떨어집니다.
무엇이 더 나쁜 성과를 초래할 수 있는지 생각해 보셨습니까?저는 가능하면 쿼리를 그대로 두고 인덱스의 내용을 수정하고 싶습니다.
언급URL : https://stackoverflow.com/questions/75809470/mariadb-having-different-performance-on-similiar-tables-with-same-indices
반응형
'programing' 카테고리의 다른 글
| Oracle ORA-04068의 빈번한 오류: 기존 패키지 상태가 삭제되었습니다. (0) | 2023.07.27 |
|---|---|
| Thymeleaf에서 의 인스턴스 사용 (0) | 2023.07.27 |
| 핸들러 대 비동기 작업 대 스레드 (0) | 2023.07.27 |
| 마리아에서 변수를 선언하고, 채우고, 사용할 때 고착됨DB (0) | 2023.07.27 |
| Python에서 참조로 정수 전달 (0) | 2023.07.27 |