데이터베이스에 색인을 추가하면 질의 결과 변경

데이터베이스에 색인을 추가하면 질의 결과 변경

단순 쿼리에 대한 인덱스를 추가하려고 하는데 추가 시 결과가 변경됩니다.인덱스가 쿼리 결과에 영향을 미칠 수 있습니까?

인덱스를 삭제하면 결과가 이전 상태로 돌아갑니다.

쿼리는 다음과 같이 간단합니다.

SELECT `gid`.`num_version_contrat` AS `num_version_contrat`, MAX(`gid`.`date_quittancement_echeance`) AS `max_date_quittancement_echeance`,`gid`.`montant_ht_actualise_echeance` AS `dernier_montant`
FROM `gid`
WHERE `gid`.num_version_contrat = "100313 V.0"
GROUP BY `gid`.`num_version_contrat`
ORDER BY `gid`.`num_version_contrat`

인덱스를 사용하지 않으면 다음과 같은 결과가 나옵니다.

"num_version_contrat", "max_date_quitance_echeance", "dernier_montant":

"100313 V.0", "2018-04-01", "32744"

인덱스 추가:

CREATE INDEX `gid_idx_group_by_index` ON `gid` (`num_version_contrat`, `date_quittancement_echeance`, `montant_ht_actualise_echeance`)

인덱스를 사용한 결과:

"num_version_contrat", "max_date_quitance_echeance", "dernier_montant":

"100313 V.0", "2018-04-01", "2067.64"

두 경우 모두 결과가 다른 이유를 알 수 있습니까?

select 구에 다음 항목에 의해 그룹에 포함되지 않은 필드가 있습니다.gid.montant_ht_actualise_echeance

이것은 MySQL과 MariaDB의 매우 위험한 기능으로, 당신이 알게 된 바와 같이 예상치 못한 결과를 초래할 수 있습니다.

다른 데이터베이스는 쿼리를 거부하지만 SQL 모드에 "ONLY_FULL_GROUP_BY"가 포함되지 않는 한 MariaDB는 쿼리를 받아들여 읽기 중에 발견된 첫 번째 값을 제공합니다.

인덱스를 추가하면 주문 레코드가 검색되는 순서가 변경되므로 다른 결과를 얻을 수 있습니다.실제로 다른 레코드를 추가/업데이트/삭제하더라도 레코드가 상주하는 블록이 변경될 수 있으므로 그룹의 결과가 변경될 수 있습니다.

다음을 추가하여 쿼리를 수정할 수 있습니다.gid.montant_ht_actualise_echeance성명서를 통해 그룹에 제출합니다.

또는 집계 함수를 선택하여 합계, 최대, first_value 또는 last_value를 계산할 수 있습니다.

코멘트에 대한 답변:

GROUP BY는 "이러한 필드의 모든 조합에 대해 하나의 레코드를 작성하십시오"를 의미합니다.따라서 "GROUP BY, Month"가 있는 경우 표에 나와 있는 연도와 월의 조합별로 하나의 레코드를 얻을 수 있습니다.또한 그룹 내에서 고유한 값을 가진 모든 값을 여기에 입력합니다.즉, 한 달은 항상 분기마다 고유한 값을 가지므로 "분기"가 여기에 표시되어야 합니다.모든 레코드에 대해 하나의 값만 있는 경우에도 "회사 이름"이 표시되어야 합니다.

다른 모든 필드의 경우 발견된 여러 값을 처리하는 방법을 데이터베이스에 지정해야 합니다.숫자 필드는 간단합니다. SUM(가격) 또는 COUNT(ID) 등을 사용할 수 있습니다.텍스트 필드의 경우 모든 값을 하나의 문자열에 추가하려면 MIN, MAX(알파벳 순서), FIRST_VALUE(암묵적으로 현재 보유하고 있는 값) 또는 GROUP_CONCAT 중 하나를 선택해야 합니다.

에 된 gid하려면 ".montant_ht_actualise_echeance" 를 합니다.gid.date_quittancement_echeance먼저 최대 날짜를 가진 레코드를 식별하고 이를 사용하여 테이블에서 원하는 값을 선택해야 합니다.

MySQL/MariaDB에서는 대부분의 경우 테이블을 직접 결합합니다.테이블에 unquue 키/id 열이 있는 경우 이 컬럼을 사용하여 가입합니다.그렇지 않은 경우 다음과 같습니다.

SELECT `gid`.`num_version_contrat` AS `num_version_contrat`,
`gid`.`date_quittancement_echeance` AS `max_date_quittancement_echeance`, 
`gid`.`montant_ht_actualise_echeance` AS `dernier_montant`
FROM `gid`
INNER JOIN
(
    SELECT `gid`.`num_version_contrat` AS `num_version_contrat`, 
    MAX(`gid`.`date_quittancement_echeance`) AS max_date
    FROM `gid`
    GROUP BY `gid`.`num_version_contrat`
) last_dates
ON `gid`.`num_version_contrat` = `last_dates`.`num_version_contrat`
AND `gid`.`date_quittancement_echeance` = `last_dates`.`max_date`
WHERE `gid`.num_version_contrat = "100313 V.0"

ORDER BY `gid`.`num_version_contrat`

첫 번째 부분은 원하는 필드를 선택합니다.두 번째 부분에서는 계약 버전별로 max_dates만 찾고 INSER JOIN은 양쪽에서 발견된 레코드만 보관하며 max_date가 없는 모든 레코드를 제거합니다.

이것은 WHERE 절이 테스트용이며 나중에 삭제된다고 가정합니다.그렇지 않으면 그룹 전체가 의미가 없습니다.

언급URL : https://stackoverflow.com/questions/55647574/adding-index-to-a-database-change-the-results-of-a-query