php - 为什么 Zend_Db_Adapter 比 mysql_query 慢很多

Question

我在我的项目中使用 Zend Framework。我需要插入多条记录，我发现 Zend_Db 比 my_sql 查询慢得惊人(好几次)，这让我觉得我做错了什么。这里有两个例子。

Zend_Db_Adapter:

        $startTime = microtime(true);
        $db = Zend_Db_Table::getDefaultAdapter();
        $db->beginTransaction();

        $dateAdded = date('Y-m-d H:i:s');
        $lastChanged = $dateAdded;                  

        foreach ($importDataNamespace->data as $subscriberNum => $subscriber)
        {
            foreach ($fieldsMap as $fieldNumber => $fieldTag) {
                if (isset($subscriber[$fieldNumber])) {
                    $subscriberData[$fieldTag] = $subscriber[$fieldNumber]; 
                } else {
                    $subscriberData[$fieldTag] = '';
                }
            }
            $query = 'INSERT INTO subscribers (list_id, account_id, email_address, first_name, last_name, date_added, last_changed) ' .
                     'VALUES (' . 52 . ', ' . 29 . ', ' .  $db->quote($subscriberData['EMAIL']) . ', ' . $db->quote($subscriberData['FNAME']) .
                     ', ' . $db->quote($subscriberData['LNAME']) . ', ' . $db->quote($dateAdded) . ', ' . $db->quote($lastChanged) . ')';
            $db->query($query);                                                                 
        }
        $db->commit();

        $this->view->time = microtime(true) - $startTime;

mysql_query 示例:

        $startTime = microtime(true);

        $user = 'root';
        $password = 'password';
        $db = 'database';
        $connect = @mysql_connect('localhost',$user,$password) or die("Failed to connect database");
        @mysql_select_db($db) or die("Failed to select database");          

        $dateAdded = date('Y-m-d H:i:s');
        $lastChanged = $dateAdded;      

        $result = mysql_query('SET autocommit = 0');            

        foreach ($importDataNamespace->data as $subscriberNum => $subscriber)
        {
            foreach ($fieldsMap as $fieldNumber => $fieldTag) {
                if (isset($subscriber[$fieldNumber])) {
                    $subscriberData[$fieldTag] = $subscriber[$fieldNumber]; 
                } else {
                    $subscriberData[$fieldTag] = '';
                }
            }
            $query = 'INSERT INTO subscribers (list_id, account_id, email_address, first_name, last_name, date_added, last_changed) ' .
                'VALUES (' . 52 . ', ' . 29 . ', \'' .  mysql_real_escape_string($subscriberData['EMAIL']) . '\', \'' . mysql_real_escape_string($subscriberData['FNAME']) .
                '\', \'' . mysql_real_escape_string($subscriberData['LNAME']) . '\', \'' . $dateAdded . '\', \'' . $lastChanged . '\')';                    
            mysql_query($query);                                    
        }
        $result = mysql_query('SET autocommit = 1');
        $result = mysql_query('COMMIT;');

        $this->view->time = microtime(true) - $startTime;           

在第一种情况下需要 14.8 秒，在第二种情况下需要 3.7 秒。你能告诉我为什么会这样吗？你做错了什么？

如果我删除 Zend_Db 的任何引号，从带引号的 14 秒开始需要 12 秒，但它仍然比使用 mysql_query 慢得多:

        $startTime = microtime(true);
        $db = Zend_Db_Table::getDefaultAdapter();
        $db->beginTransaction();

        $dateAdded = date('Y-m-d H:i:s');
        $lastChanged = $dateAdded;                  

        foreach ($importDataNamespace->data as $subscriberNum => $subscriber)
        {
            foreach ($fieldsMap as $fieldNumber => $fieldTag) {
                if (isset($subscriber[$fieldNumber])) {
                    $subscriberData[$fieldTag] = $subscriber[$fieldNumber]; 
                } else {
                    $subscriberData[$fieldTag] = '';
                }
            }
            $query = 'INSERT INTO subscribers (list_id, account_id, email_address, first_name, last_name, date_added, last_changed) ' .
                     'VALUES (' . 52 . ', ' . 29 . ', \'' .  $subscriberData['EMAIL'] . '\', \'' . $subscriberData['FNAME'] .
                     '\', \'' . $subscriberData['LNAME'] . '\', \'' . $dateAdded . '\', \'' . $lastChanged . '\')';
            $db->query($query);                                                         
        }
        $db->commit();

        $this->view->time = microtime(true) - $startTime;

感谢您提供有关此问题的任何信息。

此代码使用 mysql_query 大约需要 0.065 秒:

    $dateAdded = date('Y-m-d H:i:s');
    $lastChanged = $dateAdded;      

    $startTime = microtime(true);       
    $result = mysql_query('BEGIN');
    for ($i = 0; $i < 100; $i++) {
        $email = 'test_ ' . $i . '@gmail.com';
        $query = 'INSERT INTO subscribers (list_id, account_id, email_address, first_name, last_name, date_added, last_changed) ' .
                'VALUES (' . 52 . ', ' . 29 . ', \'' .  mysql_real_escape_string($email) . '\', \'' . mysql_real_escape_string($firstName) .
                '\', \'' . mysql_real_escape_string($lastName) . '\', \'' . mysql_real_escape_string($dateAdded) . '\', \'' . mysql_real_escape_string($lastChanged) . '\')';                                                                   
        mysql_query($query);            
    }               
    $result = mysql_query('COMMIT');
    $time = microtime(true) - $startTime;                           
    echo 'Using mysql_query: ' . $time  . '<br />';
    exit(); 

使用 Zend_Db_Adapter 基准的代码(在这种情况下我什至没有使用引号):

    $db = Zend_Db_Table::getDefaultAdapter();
    $db->getProfiler()->setEnabled(true);
    $profiler = $db->getProfiler();          

    $startTime = microtime(true);
    $db->beginTransaction();        
    for ($i = 0; $i < 100; $i++)
    {

        $email = 'test_' . $i . '@gmail.com';
        $query = 'INSERT INTO subscribers (list_id, account_id, email_address, first_name, last_name, date_added, last_changed) ' .
             'VALUES (' . 52 . ', ' . 29 . ', \'' . $email . '\', \'' . $firstName .
             '\', \'' . $lastName . '\', \'' . $dateAdded . '\', \'' . $lastChanged . '\')';
        $db->query($query);                                 
    }           
    $db->commit();
    $time = microtime(true) - $startTime;                       
    echo 'Time of transaction Zend_Db_Adapter query: ' . $time . '<br />';

    echo 'Total time ' . $profiler->getTotalElapsedSecs() . '<br />';           
    $count = 0;
    $totalTime = 0; 
    foreach ($profiler->getQueryProfiles() as $query) {
        $count++;
        $elapsedTime = $query->getElapsedSecs();
        $totalTime += $elapsedTime;
        echo $count . ' ' . $elapsedTime  . ' ' . $query->getQuery() . '<br />';
    }
    echo 'Sum time: ' . $totalTime . '<br />'; 

下面是一些结果:

事务Zend_Db_Adapter查询时间:0.23094701767总时间 0.09492349624631 0.00199699401855 连接2 0.000336885452271 开始3 0.000540018081665 INSERT INTO subscribers (list_id, account_id, email_address, first_name, last_name, date_added, last_changed) VALUES (52, 29, 'test_0@gmail.com', 'John', 'Clinton', '2011-01-28 15: 25:21', '2011-01-28 15:25:21')4 0.000504016876221 INSERT INTO subscribers (list_id, account_id, email_address, first_name, last_name, date_added, last_changed) VALUES (52, 29, 'test_1@gmail.com', 'John', 'Clinton', '2011-01-28 15: 25:21', '2011-01-28 15:25:21')

这很奇怪。插入 100 条记录的事务时间是执行所有查询的 2.5 倍。如果我尝试计算在循环中形成字符串的时间，它(如果我们删除查询)不会花费太多时间。

Answer 1

我认为原因之一是您执行 $db->quote() 的次数过多，这是不必要的。你知道吗 $db->quote() 可以接受一个数组作为它的参数，你基本上可以减少对 $db->quote() 的调用到一个。此外，在您的 mysql_query 版本中，您不会转义 $dateAdded 和 $lastChanged，而在 zend_db 版本中，您会转义。

编辑:在下面添加了一个例子

    $db = Zend_Db_Table::getDefaultAdapter();

    $input = array(
        'a' => "asd'lfksd",
        'b' => "asdlfk'sdfasdf",
        'c' => "asd fds f saf'sdfsd",
        'd' => "asd fds f saf'sdfsd"
    );


    // separate calls to quote
    $startTime = microtime(true);
    $db->quote($input['a']);
    $db->quote($input['b']);
    $db->quote($input['c']);
    $db->quote($input['d']);
    $totalTime1 = microtime(true) - $startTime;



    // one call to quote
    $startTime = microtime(true);
    $db->quote($input);
    $totalTime2 = microtime(true) - $startTime;

    // show results
    var_dump("Sperate calls are ". $totalTime1/$totalTime2 . " times slower");
    //output: string 'Sperate calls are 3.0875831485588 times slower' (length=46)