Comparison and sharing of the efficiency of reading and writing files and reading and writing databases in PHP

Comparison and sharing of the efficiency of reading and writing files and reading and writing databases in PHP_PHP Tutorial

WBOY

Release： 2016-07-13 10:25:57

Original

861 people have browsed it

This question also came to my mind recently. Is it faster to read files or databases? How much faster can it be? Tianyuan has also searched and found no netizens have answered this question. It may be because it is too simple. , let’s test it in this article. Due to time constraints, VC has not been installed yet. Tianyuan tested it with PHP first. Next time I have time, I will add tests on C/C++ to this article, because the underlying analysis of PHP should also be based on C. , so it is estimated that the test results of the two environments are similar. Small problems lead to big gains. Now let’s take a look at the test process and results.

The test procedure is as follows:

Note 1: Since the read database statement calls the simple packet function twice, the read file is also changed to two consecutive calls. The database record ID is 1, which is the first one and has a unique index.
Note 2: Tested twice, once with 4K data and once with reshaped data

Copy code The code is as follows:

set_time_limit(0);
function fnGet($filename)
{
$content = file_get_contents($filename);
return $content;
}
function fnGetContent($filename)
{
$content = fnGet($filename);
return $content;
}
$times=100000;
echo 'Database query results:
';
//---------- -----------------------
$begin=fnGetMicroTime();
for($i=0;$i<$times;$i++ )
{
$res=$dbcon->mydb_query("SELECT log_Content FROM blog WHERE log_ID='1'");
$row=$dbcon->mydb_fetch_row($res);
$content=$row[0];
}
echo 'fetch_row '.$times.' Times: '.(fnGetMicroTime()-$begin).'< ;/font>Seconds
';
//-------------------------------- -
$begin=fnGetMicroTime();
for($i=0;$i<$times;$i++)
{
$res=$dbcon->mydb_query("SELECT log_Content FROM blog WHERE log_ID='1'");
$row=$dbcon->mydb_fetch_array($res);
$content=$row['log_Content'];
}
echo 'fetch_array '.$times.' times: '.(fnGetMicroTime()-$begin).'seconds
';
//- --------------------------------
$begin=fnGetMicroTime();
for($i= 0;$i<$times;$i++)
{
$res=$dbcon->mydb_query("SELECT log_Content FROM blog WHERE log_ID='1'");
$row=$dbcon ->mydb_fetch_object($res);
$content=$row->log_Content;
}
echo 'fetch_object '.$times.' times: '. (fnGetMicroTime()-$begin).'seconds
';
//--------------------- ------------
$dbcon->mydb_free_results();
$dbcon->mydb_disconnect();
fnWriteCache('test.txt',$content) ;
echo 'Directly read file test results:
';
//------------------------ --------
$begin=fnGetMicroTime();
for($i=0;$i<$times;$i++)
{
$content = fnGetContent(' test.txt');
}
echo 'file_get_contents read directly'.$times.' times: '.(fnGetMicroTime()-$begin).'';
//---------------------------------
$begin=fnGetMicroTime();
for($i=0;$i<$times;$i++)
{
$fname = 'test.txt';
if(file_exists( $fname))
{
$fp=fopen($fname,"r");//flock($fp,LOCK_EX);
$file_data=fread($fp, filesize($fname) );//rewind($fp);
fclose($fp);
}
$content = fnGetContent('test.txt');
}
echo 'fopen read directly '.$times.'Times:'.(fnGetMicroTime()-$begin).'seconds
';

Query results for 4K size data:
fetch_row 100000 times: 16.737720012665 seconds
fetch_array 100000 times: 16.661195993423 seconds
fetch_object 100000 times: 16.775065898895 seconds
Directly read file test results:
File_get_contents read directly 100000 times time: 5.4631857872009 seconds
fopen directly read 100000 times time: 11.463611125946 seconds
Plastic ID query result:
fetch_row 100000 times time: 12.812072038651 seconds
fetch_array 100000 times time: 12.667390108109 Seconds
fetch_object 100000 times time: 12.988099098206 seconds
Direct file reading test results:
file_get_contents direct reading 100000 times time: 5.6616430282593 seconds
fopen direct reading 100000 times time: 11.542816877365 seconds

Test conclusion:

1. Reading files directly is more efficient than database query, and the connection and disconnection time is not included in the article.
2. The larger the content read at one time, the more obvious the advantage of reading the file directly (the file reading time increases slightly, which is related to the continuity of file storage and cluster size). This result is exactly related to the sky. It is the opposite of what was expected, indicating that MYSQL may have additional operations for reading larger files (the time has increased by nearly 30% twice). If it is just a simple assignment conversion, the difference should be small.
3. It can be inferred that the database efficiency will only get worse without testing when writing files and INSERT.
4. If a small configuration file does not need to use database features, it is more suitable to be stored in a separate file. There is no need to create a separate data table or record. Large files such as pictures, music, etc. are more suitable for file storage. For convenience, it is more reasonable to only put index information such as paths or thumbnails into the database.
5. If you only read files on PHP, file_get_contents is more efficient than fopen and fclose, excluding the time required to determine the existence of this function, which will be about 3 seconds shorter.
6. fetch_row and fetch_object should be converted from fetch_array. I have not seen the source code of PHP. From the execution alone, it can be seen that fetch_array is more efficient. This seems to be contrary to what is said on the Internet.
In fact, before doing this test, I had a rough idea of the results based on my personal experience. After the test was completed, I felt a sense of enlightenment. Assuming that the program efficiency is equivalent to that of key processes and does not include caching and other measures, reading and writing any type of data is not as fast as directly operating files. Regardless of the MSYQL process, the "file" must be read from the disk in the end. (Equivalent to the record store), so of course the premise of all this is that the content is read-only and has nothing to do with any sorting or search operations.