MySQLi查询与PHP Array，哪个更快? [英] MySQLi query vs PHP Array, which is faster?

问题描述

我正在开发一种算法，用于在多个巨大数组上进行密集计算.现在，我已经使用PHP数组来完成这项工作，但是它似乎比我需要的慢.我当时正在考虑使用MySQLi表并将php数组转换为数据库行，然后开始计算以解决速度问题.

第一步，当我将一个20 * 10的PHP数组转换为200个包含零的数据库行时，它花费了很长时间.这是代码:(基本上，以下代码会生成一个零矩阵，如果您想知道的话)

$stmt = $mysqli->prepare("INSERT INTO `table` (`Row`, `Col`, `Value`) VALUES (?, ?, '0')"); 
for($i=0;$i<$rowsNo;$i++){
    for($j=0;$j<$colsNo;$j++){
        //$myArray[$j]=array_fill(0,$colsNo,0);
        $stmt->bind_param("ii", $i, $j); 
        $stmt->execute(); 
    }
}
$stmt->close();

已注释掉的行"$ myArray [$ j] = array_fill(0，$ colsNo，0);"会在接下来的两行中填写表格时非常快速地生成数组，需要花费更长的时间.

排列时间:0.00068秒

MySQLi时间:25.76秒

还有更多的计算余量，即使修改了很多部分，我也感到担心，它可能会变得更糟.我搜索了很多，但找不到关于数组是更好的选择还是mysql表的任何答案?有没有人做过或知道任何基准测试?

我非常感谢您的帮助.

预先感谢

---

更新:

我对273 * 273矩阵进行了以下测试.我为相同的数据创建了两个版本.第一个是二维PHP数组，第二个是具有273 * 273 = 74529行的表，都包含相同的数据.以下是从两者中检索相似数据的速度测试结果[在这里，找出某行的哪一列的值等于1-其他列为零]:

• 该阵列花费了 0.00021秒.
• mysqli表花了 0.0026秒. (慢10倍以上)

我的结论是坚持使用数组，而不是将其转换为数据库表.

最后要说的是，如果首先将提到的数据存储在数据库表中，则生成一个数组，然后使用它会变得很慢，如下所示(由于从数据库中检索数据而导致的速度变慢): /p>

• 该阵列花费了0.9秒. (速度要慢400倍以上)
• mysqli表花费了0.0021秒.

解决方案

就我而言，如问题的更新部分所示，我认为数组比mysql数据库具有更好的性能.

即使我连续搜索单元格以找到所需的值，数组使用也显示出10倍的响应速度.甚至表的良好索引也无法击败数组的功能和速度.

I'm developing an algorithm for intense calculations on multiple huge arrays. Right now I have used PHP arrays to do the job but, it seems slower than what I needed it to be. I was thinking on using MySQLi tables and convert the php arrays into database rows and then start the calculations to solve the speed issue.

At the very first step, when I was converting a 20*10 PHP array into 200 rows of database containing zeros, it took a long time. Here is the code: (Basically the following code is generating a zero matrix, if you're interested to know)

$stmt = $mysqli->prepare("INSERT INTO `table` (`Row`, `Col`, `Value`) VALUES (?, ?, '0')"); 
for($i=0;$i<$rowsNo;$i++){
    for($j=0;$j<$colsNo;$j++){
        //$myArray[$j]=array_fill(0,$colsNo,0);
        $stmt->bind_param("ii", $i, $j); 
        $stmt->execute(); 
    }
}
$stmt->close();

The commented-out line "$myArray[$j]=array_fill(0,$colsNo,0);" would generate the array very fast while filling out the table in next two lines, took a very longer time.

Array time: 0.00068 seconds

MySQLi time: 25.76 seconds

There is a lot more calculating remaining and I got worried even after modifying numerous parts it may get worse. I searched a lot but I couldn't find any answer on whether the array is a better choice or mysql tables? Has anybody done or know about any benchmarking test on this?

I really appreciate any help.

Thanks in advance

---

UPDATE:

I did the following test for a 273*273 matrix. I created two versions for the same data. First one, a two-dimension PHP array and the second one, a table with 273*273=74529 rows, both containing the same data. The followings are the speed test results for retrieving similar data from both [in here, finding out which column(s) of a certain row has a value equal to 1 - the other columns are zero]:

• It took 0.00021 seconds for the array.
• It took 0.0026 seconds for mysqli table. (more than 10 times slower)

My conclusion is sticking to the arrays instead of converting them into database tables.

Last thing to say, in case the mentioned data is stored in the database table in the first place, generating an array and then using it would be much much slower as shown below (slower due to data retrieval from database):

• It took 0.9 seconds for the array. (more than 400 times slower)
• It took 0.0021 seconds for mysqli table.

解决方案

In my case, as shown on the update part of the question, I think arrays have better performance than mysql databases.

Array usage showed 10 times faster response even when I search through the cells to find desired values in a row. Even good indexing of the table couldn't beat the array functionality and speed.

这篇关于MySQLi查询与PHP Array，哪个更快?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！