提高此 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]);
    }

时间范围可以是一天中每小时一次、一个月中每天或一年中每周一次。

调用日期列被标记为索引列。

该表当前包含 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必须为每一行计算的间隔，而是MySQL可以对其执行即时比较的完整DateTime列：

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 ?)

我不能保证此解决方案的性能提高很大，尽管根据您的数据集，它可能是一个明显的性能提升。

对于星号 cdrs，你可以这样做

假设您使用了：

$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))

因此，只需首先将查询限制为该stop_time所在的间隔。

但是添加列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 ;

当然，您需要在调用日期和call_end_time列上创建索引并使用联合而不是OR(否则一部分将不使用索引(

如果磁盘空间不如速度重要，请尝试：

ALTER TABLE cdr ROW_FORMAT = FIXED;

相关内容

最新更新

热门标签：