php - 如何优化查询以更快地获得结果？

Question

我正在扫描许多网页上的单词，然后将它们存储在 MySQL 数据库中。

我有三个表:

• 单词:(单词 ID、单词)
• 页面:(页面 ID、页面)
• map :(wordid、pageid、freq)

freq 字段用于保存页面中单词的出现次数。

然后使用 PHPExcel 创建一个工作表，其中包含单词、页面和频率值。

该sheet的基本方案如下:

|   A   |   B   |   C   | ....
|       |page1  |page2  | ....
|word1  |     10|      7| ....
|word2  |      2|      1| ....
...
...

因此，我有以下代码用于获取 Excel 工作表，但所需的工作时间太长，并且浏览器停止工作，表示服务器响应太晚。所以我的工作还没有完成。我也尝试过添加

ini_set('max_execution_time', 0);
set_time_limit(36000); 

但是添加上面的代码前后没有任何变化。

所以我认为现在优化查询可能会更快。

function write2excel($config)
{
    include 'PHPExcel_1.8.0_doc/Classes/PHPExcel.php';
    include 'PHPExcel_1.8.0_doc/Classes/PHPExcel/Writer/Excel2007.php';

    $objPHPExcel = new PHPExcel();

     //retrieve page names from DB in a associative array
     $pages = getPages($config);

     //retrieve word names from DB in a associative array
     $words = getWords($config); 

    $r = 1;
    $c = 0;

     //Write each word into the first column in each row.
    foreach ($words as $w)
    {
        $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValueByColumnAndRow(1, $r, $w['word']);
        $r++;
    }
     //Write page names into the first rows of each column
    foreach ($pages as $p)
    {
        $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValueByColumnAndRow($c, 2, $p['page']);
        $c++;
    }

     $c = 1;
    foreach ($words as $w)
    {
        $r = 2;
        foreach ($pages as $p)
        {
            $freq = getFrequency($p['page'], $w['word'], $config);
            $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValueByColumnAndRow($c, $r, $freq);

             $r++;
        }
        $c++;
    }

    $objPHPExcel->getActiveSheet()->setTitle('mySheet');

    $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);

    $objWriter->save('mySheet.xlsx');
 }

function getWords($config)
{
    require_once $config . '.php';

    $conn = new Connection();

    $query = $conn->db->query('SELECT word FROM words');

    $query->execute();

    return $query->FetchAll(PDO::FETCH_ASSOC);
}

function getPages($config)
{
    require_once $config . '.php';

    $conn = new Connection();

    $query = $conn->db->query('SELECT page FROM pages');

    $query->execute();

    return $query->FetchAll(PDO::FETCH_ASSOC);
}

function getFrequency($page, $word, $config)
{
    require_once $config . '.php';

    $conn = new Connection();

    //find frequency value of the given word for the given page
    $query = $conn->db->prepare('SELECT freq FROM map WHERE pageid IN '
            . '(SELECT pageid FROM pages WHERE page = :page) '
            . 'AND wordid IN (SELECT wordid FROM words WHERE word = :word) LIMIT 1');

    $query->bindValue(':page', $page, PDO::PARAM_STR);
    $query->bindValue(':word', $word, PDO::PARAM_STR);

    $query->execute();

    $row = $query->FetchAll(PDO::FETCH_ASSOC);
    if ($query->rowCount() > 0)
    {
        $freq = $row[0]['freq'];
    }
    else
    {
        $freq = 0;
    }
    return $freq;
}

我的表格单词中有超过10000行，表格页面中有超过1000行

编辑

如果我想为所有页面运行 100×100 行的脚本，该怎么办？我的意思是，将获取前 100 个单词，然后获取 101-200、201-300，...直到最后。

Answer 1

您应该检查确切的问题出在哪里，但您已经可以改进一些事情:

• 仅使用一个连接并传递该连接，这样您就不会在每次调用某个函数时都打开一个新的数据库连接；
• 准备一次语句，然后多次执行它，而不是在调用该函数时每次都在该函数中准备它。

除此之外，在循环中(或在本例中是在嵌套循环中)执行查询始终是一个问题，因此尝试减少查询数量并使用 EXPLAIN优化您的指数可能会带来最大的 yield 。