dongxieli3839
dongxieli3839
2012-03-26 23:10

PDO插入查询会降低网站速度

已采纳

On my site I have coded a function that shows you how many unique visitors and how many pageviews I get per day.

The problem is that sometimes the insert query takes forever and in InnoDB theres no DELAYED INSERT function.

Edit: It is using InnoDB, what I mean with long loading time is around 6 seconds instead of like 0.1-0.5 seconds. As soon as I remove the logging the site is much faster.

The $b array bellow contains browser info, but according to XHProf its the PDO query that's taking so long to execute.

The insert code is this:

$values = array(
        'time' => time(),
        'ip' => $_SERVER['REMOTE_ADDR'],
        'page' => rtrim((isset($_GET['q']) ? $_GET['q'] : 'index'), '/'),
        'browser' => $b[11][0] . ' ' . $b[11][1],
        'os' => $uos,
        'referred' => (isset($_SERVER['HTTP_REFERER']) && !preg_match('|^' . Config::getValue('site', 'url') . '|', $_SERVER['HTTP_REFERER']) ? $_SERVER['HTTP_REFERER'] : ''),
    );

    $this->table->insert($values);

and the insert function code:

public function insert($table, $data) {
    ksort($data);

    $fieldNames = implode('`, `', array_keys($data));
    $fieldValues = ':' . implode(', :', array_keys($data));

    $sth = $this->prepare('INSERT INTO ' . $table . '(`' . $fieldNames . '`) VALUES (' . $fieldValues . ');');

    foreach ($data as $key => $value) {
        $sth->bindValue(':' . $key, $value);
    }

    $sth->execute();
}
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

4条回答

  • douping1825 douping1825 9年前

    A minor optimization. The way you are doing your binding you can get rid of the foreach statement and pass the array directly to execute. PDO will map the arrays key to the :binding names you created in the prepare statement.

    $sth->execute($data);
    

    That said, you should look at your slow query log in mysql to see what is going on. Even though you are using Innodb, there are scenarios where Innodb will lock the table. That could be why inserts are taking a long time sometimes. You can reduce your slow query threshold to something like 3 seconds to capture the queries. The log will tell you how much time was spent in each stage (i.e. locked).

    点赞 评论 复制链接分享
  • dongyou7292 dongyou7292 9年前

    Is there a reason you're using prepare instead of just calling pdo -> exec()?

    Searching around shows many people bench marking prepare as being much slower and more intensive than the using exec() especially when you aren't expecting any values back (also see the doc for Prepare - it notes being designed to optimize calling the same query multiple times).

    Instead of binding your values you could do something like this -

    function insert($table, $data) {
        ksort($data);
    
        $fieldNames = implode("`, `", array_keys($data));
        $fieldValues = implode("', '", array_values($data));
    
        $this->exec("INSERT INTO $table (`{$fieldNames}`) VALUES ('{$fieldValues}')");
    }
    
    点赞 评论 复制链接分享
  • dsc862009 dsc862009 9年前

    "On my site I have coded a function that shows you how many unique visitors and how many pageviews I get per day."

    Are you executing a live query for every page request? That might well be a performance problem of its own. It won't kill anyone if you'd query those figures every hour or so, and cache the value. Subsequent page views can then use the cached value instead of a live one.

    "The problem is that sometimes the insert query takes forever and in InnoDB there's no DELAYED INSERT function."

    It could be that its not the INSERT itself that's slowing you down, but that your MySQL instance is simply busy all the time calculating page views. There's a chance this problem will go away once you start serving cached page view figures.

    点赞 评论 复制链接分享
  • doubi7496 doubi7496 9年前

    As deceze said, it may need more details. But I guess it might get stuck in "Config::getValue('site', 'url')". Btw, is your mysql a remote one?

    点赞 评论 复制链接分享

相关推荐