php - 大型mysql数据库查询

Question

我有一个脚本，该脚本应该通过 mysql 数据库运行并对案例执行某种“测试”。简化后的数据库包含代表人们所进行的旅行的记录。每条记录都是一次单独的旅行。但我只想使用往返行程。所以我需要搜索数据库并将两个行程相互匹配；前往和离开某个地点的行程。

脚本运行良好。问题是数据库包含超过 600,000 个案例。我知道如果可能的话应该避免这种情况。但出于此脚本的目的以及稍后使用数据库记录，所有内容都必须粘在一起。

使用 MAMP 在我的 iMac 上执行时，执行脚本现在需要几个小时。当然，我确保它可以使用大量内存等。

我的问题是如何加快速度，最好的方法是什么？

这是我现在的脚本:

$table          = $_GET['table'];                  
$output = '';                  
//Select all cases that has not been marked as invalid in previous test         
$query = "SELECT persid, ritid, vertpc, aankpc, jaar, maand, dag FROM MON.$table WHERE reasonInvalid != '1' OR reasonInvalid IS NULL";         
$result = mysql_query($query)or die($output .= mysql_error());                    
$totalCountValid = '';         
$totalCountInvalid = '';         
$totalCount = '';                  
//For each record:         
while($row = mysql_fetch_array($result)){                 
    $totalCount += 1;                 
    //Do another query, get all the rows for this persons ID and that share postal codes. Postal codes revert between the two trips                 
   $persid                 = $row['persid'];                 
   $ritid                  = $row['ritid'];                 
   $pcD                    = $row['vertpc'];                 
   $pcA                    = $row['aankpc'];                 
   $jaar                   = $row['jaar'];                 
   $maand                  = $row['maand'];                 
   $dag                    = $row['dag'];         
   $thecountquery  = "SELECT * FROM MON.$table WHERE persid=$persid AND vertpc=$pcA AND aankpc=$pcD AND jaar = $jaar AND maand = $maand AND dag = $dag";                  
   $thecount               = mysql_num_rows(mysql_query($thecountquery));                 
   if($thecount >= 1){                         
      //No worries, this person ID has multiple trips attached                            
      $totalCountValid += 1;                 
   }else{                         
      //Ow my, the case is invalid!                         
     $totalCountInvalid += 1;                         
     //Call the markInvalid from functions.php                          
     $totalCountValid += 1;                          
     markInvalid($table, '2', 'ritid', $ritid);                 
   }         
}                  
//Echo the result         
$output .= 'Total cases: '.$totalCount.'<br>Valid: '.$totalCountValid.'<br>Invalid: '.$totalCountInvalid;                  echo $output; 

Answer 1

您的基本问题是您正在执行以下操作。

1) 获取所有未标记为无效的案例。
2) 循环遍历步骤1)中获得的案例。

您可以轻松执行的操作是将针对 1) 和 2) 编写的查询合并到单个查询中并循环数据。这会稍微加快速度。

另请记住以下提示。

1) 选择所有列根本不是一件好事。数据在网络上传输需要大量时间。我建议将通配符替换为您真正需要的所有列。

SELECT * <ALL_COlumns>

2) 使用索引——谨慎、高效且适当。了解何时使用它们，何时不使用它们。

3) 如果可以的话，使用 View 。
4) 启用MySQL slow query log了解您需要处理和优化哪些查询。

log_slow_queries  = /var/log/mysql/mysql-slow.log
long_query_time  = 1
log-queries-not-using-indexes 

5)使用正确的MySQL字段类型和存储引擎(非常非常重要)
6) 使用 EXPLAIN 来分析你的查询 - EXPLAIN 是 MySQL 中一个有用的命令，它可以为你提供一些关于查询如何运行、使用什么索引、需要检查多少行以及是否需要执行文件操作的详细信息排序、临时表和其他你想避免的讨厌的事情。

祝你好运。