Вычисляемые поля MySQL повторно используются в предложении SELECT

Как бы вы выполнили эту задачу, чтобы получить максимальную производительность?

Схема таблицы:

CREATE TABLE `test_truck_report` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `truck_id` INT(11) NOT NULL,
    `odometer_initial` INT(11) NOT NULL,
    `odometer_final` INT(11) NOT NULL,
    `fuel_initial` INT(11) NOT NULL,
    `fuel_final` INT(11) NOT NULL,
    PRIMARY KEY (`id`)
)
ENGINE=InnoDB;

Я пытаюсь выполнить этот запрос:

SELECT
    truck_id,
    (odometer_final - odometer_initial) AS mileage,
    (fuel_initial - fuel_final) AS consumed_fuel,
    (consumed_fuel / mileage) AS consumption_per_km
FROM
    test_truck_report
WHERE
    consumption_per_km > 2

Почему-то эта очевидная на первый взгляд логика не работает, и вместо этого я вынужден использовать этот запрос:

SELECT
    truck_id,
    (odometer_final - odometer_initial) AS mileage,
    (fuel_initial - fuel_final) AS consumed_fuel,
    ((fuel_initial - fuel_final) / (odometer_final - odometer_initial)) AS consumption_per_km
FROM
    test_truck_report
WHERE
    ((fuel_initial - fuel_final) / (odometer_final - odometer_initial)) > 2

Я предполагаю, что постоянный пересчет каждого вычисляемого поля каждый раз, когда его нужно разместить, приводит к значительному снижению производительности. И это всего лишь тестовый пример, реальная рабочая таблица имеет более 50 полей, а некоторые из вычисляемых полей состоят из более чем 10 операндов. Так что это действительно ОГРОМНАЯ проблема на данный момент.

Причина, по которой я не хочу создавать эти поля и выполнять что-то вроде:

UPDATE 
    `test_truck_report` 
SET
    consumed_fuel = fuel_initial - fuel_final

заключается в том, что существующие записи постоянно обновляются пользователями, и в этом случае мне нужно будет постоянно обновлять эти данные. Итак, вы считаете создание реальных полей лучшей идеей? Или есть лучший способ?

Спасибо.


mysql calculated-columns calculated-field
person Nikita    schedule 19.01.2015    source источник
comment
Взгляните на предложение HAVING для операторов SELECT. Здесь используются имена столбцов и вычисляемые значения.   -  person Linus Kleen    schedule 19.01.2015
comment
@LinusKleen HAVING прекрасно работает!   -  person Molasar    schedule 14.05.2018

Ответы (1)


arrow_upward
1
arrow_downward

Попробуйте использовать представления:

Нам нужен вспомогательный вид:

CREATE OR REPLACE VIEW vw_truck_data AS
SELECT truck_id,
      (odometer_final - odometer_initial) AS mileage,
      (fuel_initial - fuel_final)         AS consumed_fuel
FROM test_truck_report;

И окончательный вид:

CREATE OR REPLACE VIEW vw_truck_consumption AS
SELECT data.*,
      (data.consumed_fuel / data.mileage) AS consumption_per_km
FROM vw_truck_data data;

Теперь вы можете запросить, когда захотите, простым и читаемым способом:

SELECT *
  FROM vw_truck_consumption 
 WHERE consumption_per_km > 2

Таким образом, MySQL должен иметь возможность вычитать каждое поле только один раз, поэтому производительность должна быть, по крайней мере, такой же хорошей, как ваше решение, или лучше. Обычно затраты ЦП на добавление полей меньше, чем затраты на получение данных из базы данных, но, конечно, это зависит от вашего оборудования, версии mysql, конфигурации и распределения данных. Сделайте некоторые измерения, если это действительно проблема.

В любом случае, помните, что вы выполняете фильтрацию запроса по потреблению_на_км, которое является функцией полей. Поскольку в MySQL отсутствуют функциональные индексы, наверняка просканирует всю таблицу и будет медленным.

person borjab    schedule 19.01.2015