Повысить скорость этого запроса MYSQL
Я использую PHP и MYSQL для отображения параллелизма вызовов из базы данных Asterisk CDR,
В настоящее время я использую следующее подготовленное утверждение:
$query=$cdrdb->prepare('select count(acctid) from cdr where calldate between ? and ? or DATE_ADD(calldate, INTERVAL duration SECOND) between ? and ?');
а затем следующий цикл foreach для ввода переменных:
foreach ($timerange as $startdatetime){
$start=$startdatetime->format("Y-m-d H:i:s");
$enddatetime=new DateTime($start);
$enddatetime->Add($interval);
$end=$enddatetime->format("Y-m-d H:i:s");
if(!$query->execute(array($start, $end, $start, $end))){
echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
}
if (!($res = $query->fetchall())) {
echo "Getting result set failed: ";
}
array_push($callsperinterval,$res[0][0]);
}
Временной интервал может быть каждый час в течение дня, каждый день в течение месяца или каждую неделю в течение года.
столбец calldate помечается как столбец индекса.
В настоящее время таблица содержит 122000 записей.
Результат выполнения EXPLAIN для запроса:
mysql> explain select count(acctid) from cdr where calldate between '2014-10-02 23:30:00' and '2014-11-03 00:00:00' or DATE_ADD(calldate, INTERVAL duration SECOND) between '2014-10-02 23:30:00' and '2014-11-03 00:00:00';
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | cdr | ALL | calldate | NULL | NULL | NULL | 123152 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
Один запуск запроса занимает около 0,14 с, поэтому в течение 24-часового периода с часовым интервалом сценарий должен завершиться примерно за 3,36 секунды, но в итоге он занимает около 12 секунд.
В настоящее время весь процесс может занимать до 20 секунд в течение 24 часов. Может ли кто-нибудь помочь мне повысить скорость этого запроса?
Решение
Эта часть является узким местом в вашем запросе:
DATE_ADD(calldate, INTERVAL duration SECOND)
Это потому, что MySQL выполняет «математику» на каждая строка первого подмножества определяется из вашего первого каждая строка в вашей таблице, которая не соответствует первой части вашего WHERE состояниеWHERE заявление, так как вы используете WHERE ORне WHERE AND,
Я предположил, что ваша таблица выглядит примерно так:
acctid | calldate | duration
========================================
1 | 2014-12-01 17:55:00 | 300
... etc.
Попробуйте переписать схему так, чтобы не использовать интервалы, которые MySQL должен рассчитывать для каждой строки, а полные столбцы DateTime, с которыми MySQL может выполнить немедленное сравнение:
acctid | calldate | duration_end
==================================================
1 | 2014-12-01 17:55:00 | 2014-12-01 18:00:00
Чтобы переписать эту схему, вы можете создать этот новый столбец, а затем выполнить его (это может занять некоторое время, но в долгосрочной перспективе вам пригодится):
UPDATE cdr SET duration_end = DATE_ADD(calldate, INTERVAL duration SECOND);
Затем очистите duration столбец и переписать приложение, чтобы сохранить в новый столбец!
Ваш полученный запрос будет:
select count(acctid) from cdr where calldate > ? and (calldate < ? or duration_end between ? and ?)
Если предположить, что ничего не может измениться в схеме, то вы застряли с этой функцией. Однако вы можете попытаться заставить MySQL работать с подмножествами, чтобы он не занимался математикой для стольких строк:
select
count(acctid)
from
cdr
where
calldate > ? and
(calldate < ? or DATE_ADD(calldate, INTERVAL duration SECOND) between ? and ?)
Я не могу гарантировать значительного увеличения производительности от этого решения, хотя оно может быть заметным в зависимости от вашего набора данных.
Другие решения
Для звездочек CDR вы можете просто сделать это
Допустим, вы использовали:
$query=$cdrdb->prepare('select count(acctid) from cdr where calldate between ? and ? or DATE_ADD(calldate, INTERVAL duration SECOND) between ? and ?');
$query->execute(array($start, $end, $start, $end))
Вы должны использовать это
$query=$cdrdb->prepare('select count(acctid) from cdr where calldate between ? and DATE_ADD(?, interval ? SECOND) and (calldate between ? and ? or DATE_ADD(calldate, INTERVAL duration SECOND) between ? and ?)
');
$MAX_CALL_LENGHT_POSIBLE = 60*60*10; # usualy 10 hr is not reachable on most calls. If you limit it in call, you can decrease to even less values
$query->execute(array($start, $end,$MAX_CALL_LENGHT_POSIBLE,$start,$end $start, $end))
Так что просто сначала ограничьте запрос интервалом, где может быть время остановки.
Но гораздо проще будет добавить столбец call_end_time и создать триггер
DROP TRIGGER IF EXISTS cdr_insert_trigger;
DELIMITER //
CREATE TRIGGER cdr_insert_trigger BEFORE INSERT ON cdr
FOR EACH ROW BEGIN
Set NEW.call_end_time=DATE_ADD(OLD.calldate,interval OLD.duration second);
END//
DELIMITER ;
Конечно, вам нужно создать индекс в обоих столбцах calldate и call_end_time и использовать Union вместо OR (иначе одна часть не будет использовать index)
Если дисковое пространство менее важно, чем скорость, попробуйте:
ALTER TABLE cdr ROW_FORMAT = FIXED;