循环查询越来越慢 [英] Query with in a loop getting slower and slower

问题描述

我有一个过程，它逐行读取数据馈送，解析并将数据插入MyISAM表中.第一次启动时，它运行起来非常快，大约每秒大约有1000条记录.随着时间的流逝，它变得越来越慢，现在每180秒大约有1行.

I have a process that reads a data feed line by line, parses and inserts the data into a MyISAM table. When it first start, it goes really quick, probably around 1000 records a second. As time progresses it gets slower and slower, right now we're at about 1 rows every 180 seconds.

该函数的常规语法为:

function parse($file) {
  $handle = fopen($file, 'r');
  while (!feof($handle)) {
    $line = fgets($fileHandle, 1000);
    switch (substr($line, 0, 2)) { //gets record type
      case '01' :
        //parse the record
        //escapes some strings with mysql_real_escape_string()
        mysql_query('INSERT INTO table VALUES ($a, $b, $c...');
      case '02' :
        ...
    }
  }
}

当前正在解析的文件有几百万条记录.服务器似乎没有丢失内存空间.有人知道是什么原因导致进程变慢吗?

The current file being parsed has a few million records. The server doesn't appear to be losing memory space. Does anybody know what could be causing the process to slow down?

推荐答案

它可能与必须如此频繁地写入索引有关.您已经在使用MyISAM，这将是我的第一个建议.

It probably has to do with having to write so frequently to your indexes. You are already using MyISAM which would be my first suggestion.

一些建议

1. 每100行批量插入
2. 使用INSERT DELAYED允许MySQL允许插入快速完成排队，而MySQL将在资源允许的情况下插入记录.
3. 让脚本创建一个SQL转储/分隔文件，您可以使用IN FILE导入功能加载该文件，请参见

1. Batch insert every 100 rows
2. Use INSERT DELAYED to allow MySQL to allow inserts to quickly finish queuing, and MySQL will insert the record when resources are more permitting.
3. Have your script create an SQL dump/delimited file which you can load using IN FILE importing functions, see http://dev.mysql.com/doc/refman/5.1/en/load-data.html which will be MUCH FASTER than batch inserts

这篇关于循环查询越来越慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！