Блог

Я пытаюсь добавить индекс для простого запроса, однако добавление его изменяет результаты. Возможно ли, что индексы влияют на результаты запроса?

Когда я удаляю индекс, результаты возвращаются такими, какими они были раньше.

Запрос настолько прост, насколько это:

 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_quittancement_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_quittancement_echeance», «dernier_montant» :

«100313 V.0», «2018-04-01», «2067.64»

Вы понимаете, почему результаты отличаются в обоих случаях?

У вас есть поле в предложении select, которого нет в group by: gid . montant_ht_actualise_echeance

Это очень опасная особенность MySQL и MariaDB, которая, как вы уже выяснили, может дать неожиданные результаты.

Другие базы данных отклонят ваш запрос, но если ваш режим SQL не содержит «ONLY_FULL_GROUP_BY», MariaDB примет запрос и затем выдаст вам первое значение, с которым он столкнется при чтении.

Добавление индекса изменяет порядок извлечения записей, поэтому вы получаете что-то другое. Фактически, даже добавление / обновление / удаление других записей может изменить результат группы, поскольку может измениться, в каком блоке находится запись.

Вы можете исправить свой запрос, добавив gid . montant_ht_actualise_echeance для оператора Group by .

В качестве альтернативы вы можете выбрать функцию aggegrate для вычисления sum, max, first_value или last_value.

В ответ на комментарий:

GROUP BY означает «Для каждой комбинации этих полей создайте одну запись». Поэтому, если у вас есть «ГРУППА ПО годам, месяцам», вы получите одну запись для каждой комбинации года и месяца, найденной в таблице. Кроме того, вы помещаете сюда все значения, которые, как вы знаете, имеют уникальное значение в группах. Это означает, что здесь следует указать «квартал», поскольку месяц всегда имеет уникальное значение для квартала. «Название компании» также должно быть там, если для всех записей есть только одно значение.

Для всех других полей вам нужно сообщить базе данных, как обрабатывать несколько найденных значений. Числовые поля просты: вы можете СУММИРОВАТЬ (цена) или ПОДСЧИТЫВАТЬ (идентификатор) И т. Д. Для текстовых полей вам нужно выбрать: MIN, MAX (в алфавитном порядке), FIRST_VALUE (это то, что у вас есть сейчас, неявно) или даже GROUP_CONCAT, чтобы добавить все значения в одну строку.

Чтобы получить значение gid.montant_ht_actualise_echeance, связанное с последним (макс.) gid date_quittancement_echeance , вам нужно сначала определить записи с максимальной датой и использовать их для выбора желаемых значений из таблицы.

В MySQL / MariaDB это чаще всего делается путем самостоятельного объединения таблицы. Если в таблице есть столбец unqiue key / 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 для каждой версии контракта, а ВНУТРЕННЕЕ СОЕДИНЕНИЕ сохраняет только записи, найденные в обоих, удаляя все записи, у которых НЕТ max_date.

Предполагается, что предложение WHERE предназначено только для тестирования и будет удалено позже. В противном случае вся группа by не имеет смысла.