SQLite 存储类和检索效​​率 [英] SQLite storage class And retrieval efficiency

问题描述

说我有一张表，是一个参考表(该表只被查阅，没有更新).

Say I have one table, which is a reference table (the table is only been consulted, not update).

例如，表架构:

CREATE TABLE countries(
               zip_code INTEGER PRIMARY KEY,
               name TEXT)

我想提高数据的检索速度.现在，我在 name 列上使用 index，但我想更进一步...

I want to improve the retrieval speed of data. Now, I'm using index on the namecolumn, but I want to go further...

更改表架构:

CREATE TABLE countries(
                   zip_code INT PRIMARY KEY,
                   name CHAR(200))

请注意，我已经修改了列的数据类型，以便为每列指定一个固定大小，因此整行的大小都是固定的.

remarque that I've modified the data type of the columns in order to specify a fixed size for each column, hence a fixed size for the entire row.

这是一个正确的假设吗?认为固定大小的行比SQLite DBMS中的动态大小行可以更快地检索.

Is it a correct assumption ? Thinking that fixed size rows are retrievable more quickly than dynamic size rows in SQLite DBMS.

我受到其他 DBMS (MySQL) 的启发，它们检索静态大小的行比动态大小的行更快

I'm inspiring from other DBMS (MySQL) which retrieve static size rows more quickly than dynamic size rows

推荐答案

我正在修正这个与 SQLite 相关的答案.它被接受了，所以我不能删除它.

I'm fixing this answer to be relevant to SQLite. It is accepted, so I can't delete it.

我不认为为每个名称存储 200 个字节会加速您的查询.在大多数数据库中，它所做的只是使您存储的数据量成倍增加——因为名称较短.在 SQLite 中，它是空操作.SQLite 将所有字符串存储为文本；它不像其他数据库那样用空格填充值.

I don't think that storing 200 bytes for each name is going to speed your query. In most databases, all it will do is multiply the volume of data you are storing -- because names are shorter. In SQLite, it is a no-op. SQLite stores all character strings as text; it does not pad the values with spaces as other databases do.

我对 SQLite 的内部结构并不十分熟悉.但是数据库(包括 SQLite)将记录存储在数据页上.这些由缓存机制管理.索引通过它所在的页面识别记录.整个数据页被读入.

I am not intimately familiar with the internals of SQLite. But databases (including SQLite) store records on data pages. These are managed by a caching mechanism. And indexes identify a record by the page it is on. The entire data page is read in.

在大多数数据库中，将列声明为 name(200) 可能会浪费大量空间.它会增加记录的大小，并具有以下影响:

In most databases, declaring a column as name(200) would probably waste a lot of space. It would Increase the size of the record and that has the following effects:

• 单个页面上可以存储的记录更少.
• 后续检索不太可能在缓存中找到该记录的页面.
• 读取整个表可能会占用更多的内存空间.
• 读取一行需要读取和处理更多字节.

在 SQLite 中，这些都不适用，因为 SQLite 只是忽略长度并且不会用空格填充值.

In SQLite, none of this applies, because SQLite just ignores the length and doesn't pad the value with spaces.

你想做的事情在大多数数据库中都不是一个好主意，它在 SQLite 中没有任何影响.我建议避免这种方法.索引应该足够了.

What you want to do isn't a good idea in most databases, and it has no effect in SQLite. I would recommend avoiding such an approach. The index should be sufficient.

这篇关于SQLite 存储类和检索效​​率的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！