将数据库类型优化为压缩数据库（SQLite） [英] Optimizing database types to compact database (SQLite)

问题描述

我正在致力于压缩数据库（ SQLite ），我正在尝试优化我在模式中分配的数据类型的大小。数据我可以大致分为3种类型的数字。它们是：

1. 101010000 - 9位数字


2. 0 或 1 ，只有一个或零


3. 13.4567 - 浮点类型。




所有的数字将用于程序中的数学操作。我使用此网站作为参考： http://www.sqlite.org/datatype3.html





我想知道哪个数据类型亲和性会给我最紧凑的数据库。我目前的数据库大小接近40 GB！





感谢您的帮助。

解决方案

在SQLite中，整数数据类型可以占用1,2,4或8个字节，具体取决于数据值有多大。
布尔类型存储为整数0或1，并且将始终占用1个字节。
REAL（float）数据需要8个字节来存储。如果你愿意将你的数据舍入到2个十进制数字，你可能需要将你的浮点数据乘以100，并将其存储为4字节整数值（并保存每个值4个字节）。
您提到的9个二进制数字应该需要2个字节来存储。





如何优化数据？有很多策略。
如果你有很多布尔字段，你可以使用一个字节存储多达8位。这应该允许您提高存储效率的因素为8.
另一个好的策略是规范化您的数据：将其拆分为多于一个表。但是，这只有在你的数据模式允许这种拆分时才有效。





通常，如果索引太多，它们可能会显着增加磁盘上的数据库大小。正确的解决方案是修改软件使用EXPLAIN实际使用的SQL查询，找出并不真正需要的索引并丢弃它们。





最后，不要忘记使用VACUUM FULL你会使数据库占用磁盘上的尽可能小的空间，但运行它可能需要一段时间，并需要独占数据库锁定。
祝你好运！




I am working on compacting a database (SQLite) and I am trying to optimize the size of the data types that I am assigning in the schema. The data I have can be broadly divided into 3 types of numbers. They are:

1. 101010000 - 9 digits
2. 0 or 1, - Just one or zero
3. 13.4567 - Float type. I would like to just store 13.45 here.

All the numbers will be used for mathematical manipulation within a program. I am using this website as a reference: http://www.sqlite.org/datatype3.html

I would like to know which data type affinity will give me the most compact database. I am currently getting a database size close to 40 GB!

Thanks for the help.

解决方案

In SQLite, integer data types can occupy 1,2,4 or 8 bytes, depending on how big is the data value. Boolean type is stored as integer 0 or 1 and will always occupy 1 byte. REAL (float) data needs 8 bytes to store. If you are willing to round your data to 2 decimal digits, you may want to multiply your float data by 100 and store it as 4 byte integer value (and save 4 bytes per value). 9 binary digits that you mentioned should need 2 bytes to store.

How can you optimize your data? There are many strategies. If you have a lot of boolean fields, you might be able to use one byte to store up to 8 bits. This should allow you to improve storage efficiency by factor of 8. Another good strategy is to normalize your data: split it into more than 1 table. However, this only works if your data schema can permit such split.

Often, if you have too many indexes, they may significantly increase size of database on disk. Right solution is to revise actual SQL queries are used by software using EXPLAIN, find out indexes which are not really needed and drop them.

Finally, don't forget that using VACUUM FULL you will make database occupy as small space on disk as possible, but running it could take a while and requires exclusive database lock. Good luck!

这篇关于将数据库类型优化为压缩数据库（SQLite）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！