文件和数据库存储数据的速度比较
来源:互联网 发布:java web listview 编辑:程序博客网 时间:2024/05/18 20:52
While writing Inklog, I’ve been debating with myself regarding my use of the filesystem as a datastore. While the filesystem certainly makes creating, updating, visualizing, backing up, and restoring data much easier than it would be in a database, it adds many hardships. First of all, the convenience of SQL is thrown out the window. While it is nice that using the filesystem doesn’t require a database server, not being able to use a database server means that more programming is involved. Additionally, things like searching through all the entries in the system become difficult, not to mention slow. Another downfall is that the filesystem limits the amount of metadata for each entry that can be kept in a simple fashion.
However, the biggest question on my mind was whether or not using a database server would be faster or slower when performing the most commonly requested actions: getting a list of recent items from the entire system, getting a list of items from a category, and getting one item. I decided to write a test case.
I created 6000 empty files in 20 directories. I also created a table in a mysql database that simulated the filesystem: name, mtime, dir, data. I added indexes on dir, name, and mtime. Then I started testing. In each case the test is run 10 times. Then the average is displayed. For the database tests, mysql_connect is called each time.
Getting the filenames of the 10 most recent entries from the entire system.
FILESYSTEM
TIME: 1.7814919948578TIME: 1.7425200939178TIME: 1.8071219921112TIME: 1.6778069734573TIME: 1.6711789369583TIME: 1.7414019107819TIME: 1.6959699392319TIME: 1.6531630754471TIME: 1.7546479701996TIME: 1.6758890151978TOT TIME: 17.201191902161AVG TIME: 1.5637447183782DATABASE
TIME: 0.0039100646972656TIME: 0.001039981842041TIME: 0.00095093250274658TIME: 0.00096702575683594TIME: 0.00095295906066895TIME: 0.00098395347595215TIME: 0.0009620189666748TIME: 0.0009760856628418TIME: 0.00094294548034668TIME: 0.00095808506011963TOT TIME: 0.012644052505493AVG TIME: 0.0011494593186812
Getting the filenames of the 10 most recent files in a single directory.
FILESYSTEM
TIME: 0.055459976196289TIME: 0.053847074508667TIME: 0.044721961021423TIME: 0.043873071670532TIME: 0.043742060661316TIME: 0.043787956237793TIME: 0.043717980384827TIME: 0.04374098777771TIME: 0.043833017349243TIME: 0.04370105266571TOT TIME: 0.46042513847351AVG TIME: 0.041856830770319DATABASE
TIME: 0.0095839500427246TIME: 0.0055500268936157TIME: 0.005547046661377TIME: 0.0055389404296875TIME: 0.0056079626083374TIME: 0.00553297996521TIME: 0.005499005317688TIME: 0.0055099725723267TIME: 0.0053470134735107TIME: 0.0053049325942993TOT TIME: 0.059021830558777AVG TIME: 0.0053656209598888
Getting one item.
FILESYSTEM
TIME: 0.00032293796539307TIME: 0.00021898746490479TIME: 0.00017297267913818TIME: 0.00016999244689941TIME: 0.00027298927307129TIME: 0.00017201900482178TIME: 0.00016689300537109TIME: 0.00016403198242188TIME: 0.0001760721206665TIME: 0.00017201900482178TOT TIME: 0.0020089149475098AVG TIME: 0.0001826286315918DATABASE
TIME: 0.0042519569396973TIME: 0.0011199712753296TIME: 0.0010420083999634TIME: 0.0010360479354858TIME: 0.0010439157485962TIME: 0.0010349750518799TIME: 0.001041054725647TIME: 0.0010310411453247TIME: 0.0010330677032471TIME: 0.0064520835876465TOT TIME: 0.019086122512817AVG TIME: 0.0017351020466198
The database was 1360 times faster than the filesystem when looking for the 10 most recent items in the entire system. The database was 7.8 times faster when looking for the 10 most recent items in a single directory. However, the filesystem was 9.5 times faster at getting a single file.
These numbers skew greater and greater towards the database as the number of items increases. And, in the one place that the filesystem wins, the operation being performed is so un-time-consuming in general, that the increase in the speed of the filesystem doesn’t amount to much.
These tests were performed with the database being on the same server as the running script. Additionally, the server performing these actions was, basically, not performing anything else at the time. If your database server is only accessible over a 2400bps modem link, your results will differ greatly. Additionally, if your database server is heavily loaded, while your web server isn’t, you may also see very different results.
Benchmarks are crap, for the most part. They don’t really mean a whole lot, unless they represent the exact cases in which you will be using the functions being tested. However, in this case, they DO represent exactly what I will be doing.
What does this mean? Inklog will no longer use the file system as its main method of data storage.
- 文件和数据库存储数据的速度比较
- 数据写入文件的几种方法速度比较
- 关于数据库读取速度与文件IO读取速度的比较分析
- 指令和数据混合存储与分开存储的比较
- 指令和数据混合存储与分开存储的比较
- 指令和数据混合存储与分开存储的比较
- Scanner和BufferedReader读取文件速度比较
- DataTable的数据批量写入SQL SERVER数据库方法速度比较[转]
- 各种存储设备的数据传送速度
- MySQL数据库MyISAM和InnoDB存储引擎的比较
- MySQL数据库MyISAM和InnoDB存储引擎的比较
- MySQL数据库MyISAM和InnoDB存储引擎的比较
- MySQL数据库MyISAM和InnoDB存储引擎的比较
- MySQL数据库MyISAM和InnoDB存储引擎的比较
- MySQL数据库MyISAM和InnoDB存储引擎的比较
- MySQL数据库MyISAM和InnoDB存储引擎的比较
- MySQL数据库MyISAM和InnoDB存储引擎的比较
- MySQL数据库MyISAM和InnoDB存储引擎的比较
- Jode 反编译专家
- Oracle 9.2下的“System.Exception: System.Data.OracleClient requires Oracle client software version 8.1.7 or greater
- .NET Framework 中的加密解密
- 使用 UML 为 Web 应用程序构架建模
- Struts+Spring+Hibernate组装web应用
- 文件和数据库存储数据的速度比较
- XMLHttpRequest对象
- 附属于商业企业的社区应该如何发展?
- 我们如何拿到投资?(转载于李善友的BLOG)
- 基于UML和ASP.NET实现三层B/S结构系统开发
- .net 2.0 访问Oracle --与Sql Server的差异,注意事项,常见异常
- hook(钩子程序)(转载)
- IPD基本常识
- ASCII 非打印控制字符