У меня есть две таблицы: экзамен (ExamID, дата, модальность) и CT (ctdivol, ExamID (FK)) с атрибутами в скобках.
Заметка: Таблица CT содержит около 100 000 записей.
Я хочу рассчитать среднее значение ctdivol за определенный интервал дат.
У меня есть этот код, который работает, но слишком медленно:
function get_CTDIvolAVG($min, $max) {
$values = 0;
$number = 0;
$query = "SELECT (unix_timestamp(date)*1000), examID
from exam use index(dates)
where modality = 'CT'
AND (unix_timestamp(date)*1000) between '" . $min . "' AND '" . $max . "';";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
while($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
$avg = "SELECT SUM(ctdivol_mGy), count(ctdivol_mGy)
from ct use index(ctd)
where examID ='" . $line["examID"] ."'
AND ctdivol_mGy>0;";
$result1 = mysql_query($avg) or die('Query failed: ' . mysql_error());
while ($ct = mysql_fetch_array($result1, MYSQL_ASSOC)) {
$values = $values + floatval($ct["SUM(ctdivol_mGy)"]);
$number = $number + floatval($ct["count(ctdivol_mGy)"]);
}
}
if ($number!=0) {
echo $values/$number;
}
}
Как я могу сделать это быстрее?
использование EXPLAIN
чтобы увидеть план выполнения запроса.
Для этого первого запроса MySQL не может эффективно использовать операцию сканирования диапазона индекса. Это выражение в WHERE
пункт должен быть оценен для каждый строка в таблице. Мы получаем лучшую производительность, когда мы делаем сравнение с голый колонка. Сделайте манипуляцию на буквальной стороне … конвертируйте эти значения в тип данных столбца, с которым вы сравниваете.
WHERE e.date BETWEEN expr1 AND expr2
За expr1
, вам нужно выражение, которое преобразует ваши $min
значение в дату и время. Просто будьте осторожны с преобразованием часовых поясов. Я думаю, что это может сделать то, что вам нужно для expr1:
FROM_UNIXTIME( $min /1000)
Что-то вроде:
WHERE e.date BETWEEN FROM_UNIXTIME( $min /1000) AND FROM_UNIXTIME( $max /1000)
Тогда мы увидим, что MySQL может эффективно использовать индекс с ведущим столбцом даты. Вывод EXPLAIN должен показать range
для типа доступа.
Если количество возвращаемых столбцов является небольшим подмножеством, рассмотрим индекс покрытия. Затем EXPLAIN покажет «Использование индекса», что означает, что запрос может быть полностью выполнен из индекса, без поиска страниц в базовой таблице.
Во-вторых, избегайте многократного выполнения запросов в цикле. Как правило, более эффективно запускать один запрос, который возвращает один набор результатов, из-за накладных расходов, связанных с отправкой SQL в базу данных, той базы данных, которая анализирует текст SQL, для правильного синтаксиса (ключевые слова в нужных местах), допустимой семантики (идентификаторы ссылаться на действительные объекты), учитывая возможные пути доступа и определяя, какая из них является наименьшей стоимостью, затем выполняет план запроса, получает блокировки метаданных, генерирует набор результатов, возвращает его клиенту и затем очищает. Это не заметно для одного оператора, но когда вы запускаете много операторов в тесном цикле, он начинает складываться. Соедините это с неэффективным запросом, и он станет действительно заметным.
ЕСЛИ examID
колонка в exam
является уникальным и не нулевым (или его ПЕРВИЧНЫЙ КЛЮЧ exam
тогда, похоже, вы могли бы использовать один запрос, например так:
SELECT UNIX_TIMESTAMP(e.date)*1000 AS `date_ts`
, e.examID AS `examID`
, SUM(ct.ctdivol_mGy) AS `SUM(ctdivol_mGy)`
, COUNT(ct.ctdivol_mGy) AS `count(ctdivol_mGy)`
FROM exam e
LEFT
JOIN ct
ON ct.examid = e.examID
AND ct.ctdivol_mGy > 0
WHERE e.modality = 'CT'
AND e.date >= FROM_UNIXTIME( $min /1000)
AND e.date <= FROM_UNIXTIME( $max /1000)
GROUP
BY e.modality
, e.date
, e.examID
ORDER
BY e.modality
, e.date
, e.examID
Для достижения наилучшей производительности вам нужно охватить индексы:
... ON exam (modality, date, examID)
... ON ct (examID, ctdivol_mGy)
Мы бы хотели увидеть EXPLAIN
выход; мы ожидаем, что MySQL может использовать индекс на экзамене для выполнения GROUP BY (и избегать операции «Использование файловой сортировки»), а также использовать ref
операция по индексу ct
,
Чтобы повторить … этот запрос требует, чтобы examID
быть первичным ключом exam
таблица (или, по крайней мере, гарантированно быть уникальным и ненулевым). В противном случае результат этого может отличаться от исходного кода. В отсутствие этой гарантии мы могли бы использовать либо встроенное представление, либо подзапросы в SELECT
список. Но с точки зрения производительности, мы не хотим идти туда без уважительной причины.
Вот только некоторые общие идеи, а не жесткие и быстрые «это будет быстрее».
Вы можете записать объединение на первой таблице в таблицу подзапроса с помощью exam_id:
$query = "SELECT (unix_timestamp(date)*1000) as time_calculation, ed.examID, inner_ct.inner_sum, inner_ct.inner_count "" FROM exam ed,". " ( SELECT SUM(ctdivol_mGy) as inner_sum, count(ctdivol_mGy) as inner_count, examID". " FROM ct". " WHERE ctdivol_mGy>0 ) inner_ct". " WHERE ed.modality = 'CT' AND time_calculation between". " '$min' and '$max'". " AND ed.examId = inner_ct.examID";
( SELECT . . .) inner_ct
создает таблицу в памяти, к которой вы можете присоединиться. Полезно, если вы выбираете составные данные (суммы в вашем случае) в объединении.
И наоборот, вы можете использовать следующий синтаксис:
$query = "SELECT (unix_timestamp(date)*1000) as time_calculation, ed.examID, inner_ct.inner_sum, inner_ct.inner_count "" FROM exam ed,". " LEFT JOIN ( SELECT SUM(ctdivol_mGy) as inner_sum, count(ctdivol_mGy) as inner_count, examID". " FROM ct". " WHERE ctdivol_mGy>0 ) inner_ct". " ON ed.examID = inner_ct.examID". " WHERE ed.modality = 'CT' AND time_calculation between". " '$min' and '$max'";
Вы не предоставили образец данных в вопросе, поэтому мы пытаемся ответить на предположения. Если есть только один exam
строка для многих строк в ct
— но может существовать строка экзамена, в которой вообще нет строк ct — тогда этот единственный запрос должен предоставить требуемые результаты.
SELECT
exam.examID
, (unix_timestamp(exam.date) * 1000
, SUM(ct.ctdivol_mGy)
, COUNT(ct.ctdivol_mGy)
FROM exam
LEFT OUTER JOIN ct on exam.examID = ct.examID AND ct.ctdivol_mGy > 0
WHERE exam.modality = 'CT'
AND exam.date >= @min AND exam.date < @max
GROUP BY
exam.examID
, (unix_timestamp(exam.date) * 1000)
;
Обратите внимание, я не пытаюсь PHP-код, а просто сосредоточиться на SQL. я использовал @min
а также @max
указать 2 даты, требуемые в пункте where. Они должны быть того же типа данных, что и столбец exam.date
сделайте эти вычисления в PHP перед добавлением в строку запроса.
Я хочу рассчитать среднее значение ctdivol в конкретном интервале
даты.
Если вы пытаетесь вернуть одну цифру, то это должно помочь:
SELECT
AVG(ct.ctdivol_mGy)
FROM exam
INNER JOIN ct on exam.examID = ct.examID AND ct.ctdivol_mGy > 0
WHERE exam.modality = 'CT'
AND exam.date >= @min AND exam.date < @max
;
Обратите внимание, что для этого варианта нам, вероятно, не нужно левое соединение (но опять же из-за отсутствия выборочных данных и ожидаемого результата, который является предположением).