如何提升sqlite中blob数据的查询性能

- 日理万妓 2022-08-18 12:30 608阅读 0赞

关于sqlite的blob数据存储，由于暂时没有时间深入到代码内部去进行详细分析。

看到sqlite官网上有类似的测试数据和对比。

结论是：正常情况下，如果blob的数据量超过100kB，那么最好使用分开单独的文件来存储blob数据性能会好一点。如果非要讲Blob数据存储在一个数据库文件中，那么增加Page Size可以在一般情况下提升性能。当然文章了说了，具体还是与硬件、文件系统、操作系统有关系。

# Internal Versus External BLOBs in SQLite #

If you have a database of large BLOBs, do you get better read performance when you store the complete BLOB content directly in the database or is it faster to store each BLOB in a separate file and store just the corresponding filename in the database?

To try to answer this, we ran 49 test cases with various BLOB sizes and SQLite page sizes on a Linux workstation (Ubuntu circa 2011 with the Ext4 filesystem on a fast SATA disk). For each test case, a database was created that contains 100MB of BLOB content. The sizes of the BLOBs ranged from 10KB to 1MB. The number of BLOBs varied in order to keep the total BLOB content at about 100MB. (Hence, 100 BLOBs for the 1MB size and 10000 BLOBs for the 10K size and so forth.) SQLite version 3.7.8 was used.

The matrix below shows the time needed to read BLOBs stored in separate files divided by the time needed to read BLOBs stored entirely in the database. Hence, for numbers larger than 1.0, it is faster to store the BLOBs directly in the database. For numbers smaller than 1.0, it is faster to store the BLOBs in separate files.

In every case, the pager cache size was adjusted to keep the amount of cache memory at about 2MB. For example, a 2000 page cache was used for 1024 byte pages and a 31 page cache was used for 65536 byte pages. The BLOB values were read in a random order.

<table style="color:rgb(0,0,0); font-family:Verdana,sans-serif"> 
 <tbody> 
  <tr> 
   <th>Database Page Size</th> 
   <th colspan="7">BLOB size</th> 
  </tr> 
  <tr> 
   <th>10k</th> 
   <th>20k</th> 
   <th>50k</th> 
   <th>100k</th> 
   <th>200k</th> 
   <th>500k</th> 
   <th>1m</th> 
  </tr> 
  <tr> 
   <td>1024</td> 
   <td>1.535</td> 
   <td>1.020</td> 
   <td>0.608</td> 
   <td>0.456</td> 
   <td>0.330</td> 
   <td>0.247</td> 
   <td>0.233</td> 
  </tr> 
  <tr> 
   <td>2048</td> 
   <td>2.004</td> 
   <td>1.437</td> 
   <td>0.870</td> 
   <td>0.636</td> 
   <td>0.483</td> 
   <td>0.372</td> 
   <td>0.340</td> 
  </tr> 
  <tr> 
   <td>4096</td> 
   <td>2.261</td> 
   <td>1.886</td> 
   <td>1.173</td> 
   <td>0.890</td> 
   <td>0.701</td> 
   <td>0.526</td> 
   <td>0.487</td> 
  </tr> 
  <tr> 
   <td>8192</td> 
   <td>2.240</td> 
   <td>1.866</td> 
   <td>1.334</td> 
   <td>1.035</td> 
   <td>0.830</td> 
   <td>0.625</td> 
   <td>0.720</td> 
  </tr> 
  <tr> 
   <td>16384</td> 
   <td>2.439</td> 
   <td>1.757</td> 
   <td>1.292</td> 
   <td>1.023</td> 
   <td>0.829</td> 
   <td>0.820</td> 
   <td>0.598</td> 
  </tr> 
  <tr> 
   <td>32768</td> 
   <td>1.878</td> 
   <td>1.843</td> 
   <td>1.296</td> 
   <td>0.981</td> 
   <td>0.976</td> 
   <td>0.675</td> 
   <td>0.613</td> 
  </tr> 
  <tr> 
   <td>65536</td> 
   <td>1.256</td> 
   <td>1.255</td> 
   <td>1.339</td> 
   <td>0.983</td> 
   <td>0.769</td> 
   <td>0.687</td> 
   <td>0.609</td> 
  </tr> 
 </tbody> 
</table>

**We deduce the following rules of thumb from the matrix above:**

*  **A database page size of 8192 or 16384 gives the best performance for large BLOB I/O.**
 *  **For BLOBs smaller than 100KB, reads are faster when the BLOBs are stored directly in the database file. For BLOBs larger than 100KB, reads from a separate file are faster.**

Of course, your mileage may vary depending on hardware, filesystem, and operating system. Double-check these figures on target hardware before committing to a particular design.

#     #