如何在Postgres SQL中高效地存储大量包含字符串的数据？

Question

我有两张表：

Table 1: Date (String: 'YYYYMMDD'), 
         Path (String: '/user/tom/', a directory path), 
         Size (Bigint: 1293232943, size of directory)

Table 2: Date (String: 'YYYYMMDD'), 
         Path (String: '/user/tom/logs/file.txt', a file path), 
         Count (Bigint: 282, number of times file has been opened)

我需要进行几个不同的查询，收集目录下的总访问计数，这可以通过查询表2并查找所有类似concat(t1.Path, '%')的路径来找到。有没有一种更好的方法来构造这个表，以便像这样的查询是有效的，最重要的是，数据库不会占用太多空间。

因此，我尝试创建第三个表来存储每个路径的id，虽然查询比较复杂，但它存储的数据较少，但由于id没有结构，因此仍然意义不大。它会为每个路径分配一个数字，只要它不在其中。

我正在努力寻找最有效的方法，这样可以节省存储这些数据的空间。任何帮助都将不胜感激。

Answer 1

我正在努力寻找最有效的方法，这样可以节省存储这些数据的空间。

CREATE TABLE table1 (
  table1_id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
, some_date date NOT NULL         -- 'YYYYMMDD' 
, path      text UNIQUE NOT NULL  -- 'user/tom'
, size      bigint                -- 1293232943
);

CREATE TABLE table2 (
  table1_id    int  NOT NULL REFERENCES table1  -- 12345
, some_date    date NOT NULL                    -- 'YYYYMMDD' 
, sub_path     text NOT NULL                    -- 'logs/file.txt'
, count_opened int                              -- 282
, PRIMARY KEY (table1_id, sub_path)
);

向table1添加一个代理PK，并在table2中引用它，以最小化存储空间。(无论如何，您通常都需要table1的PK。)路径往往会变得很长，所以这应该是值得的。

不要存储像前导和尾随'/‘这样的噪音，如果可以假设它总是存在的话。您可以将其添加到显示中，以降低显示成本。

对日期使用适当的date类型(4字节)。

假设count_opened不超出int范围(?)。否则，请继续使用bigint。

什么可以或需要是NOT NULL取决于用例的未知细节。

为了便于查看，您可以添加一个视图：

CREATE VIEW table2_full AS
SELECT table1_id
     , '\' || t1.path || '\' || t2.sub_path AS total_path
     , t2.some_date, t2.sub_path, t2.count_opened
FROM   table1 t1
JOIN   table2 t2 USING (table1_id)

...收集某个目录下的总访问次数...

有没有一种更好的方法来构造这个表，以便像这样的查询是有效的，最重要的是，数据库不会占用太多空间。

SELECT sum(count_opened) AS total_access_count)
FROM   table2
WHERE  table1_id = 12345;

这是非常快的，由主键的索引支持。比为此目的匹配前导字符串要快得多(即使支持最优索引)。

为了从仅索引扫描中获得最佳读取性能，您可以在table2 (table1_id, count_opened)上添加另一个多列索引。

如果您只有查询的路径，请用子查询查找ID：

SELECT sum(count_opened) AS total_access_count)
FROM   table2
WHERE  table1_id = (SELECT table1_id FROM table1 WHERE path = 'user/tom');

同样，为了获得最佳读取性能，请在table1 (path, table_id)上添加多列索引。

Answer 2

你有两张桌子吗，或者你希望一旦你设计好了就有两张桌子？我将假定您正在设计表。

看看https://www.postgresql.org/docs/9.5/datatype.html..。

您的日期可以存储为文本(9字节)、日期(8字节)或小整数(2字节)。

您的路径可能应该存储为文本(内容+1字节)。如果您可以更具体地说明您的最大大小要求，varchar(n)将改善数据库设计的“自我文档化”(并占用与文本相同的空间量)。

您的大小和计数可以存储为smallint (2字节)、int (4字节)或bigint (8字节)，具体取决于该列中可能存在的最大值。根据您提供的值，size将是一个整数，count将是一个小整数。

根据我的经验，数据库处理整数的速度真的很快，尽管索引也会影响这一点。如果yyyymmdd是存储日期值所需的格式，我会将其存储为一个小整数，而不是字符类型。

所以如果你选择...

date     smallint
path     text
sub-path text
size     int
count    smallint

...and给定您提供的值，table1中的每行将为15字节，table2中的每行将为32字节。

至于让查询更快，这是一个查询是如何编写的问题，以及其他因素，如可用的服务器资源和索引。

您可以在表中添加代理键，并根据需要设置主键。我将使用int作为代理键，但这取决于表中的最大值(行数)。在索引字段(如主键)上连接是很快的。请记住，另一列意味着更多的存储需求。但是存储是便宜的。除非你有特别的空间限制，否则我会选择这个选项。

如果不使用代理键，可以使用table2.sub_path like concat(table1.path, '%')以外的连接来试验查询性能。喜欢是很慢的。您可以尝试像table1.path = substring(table2.sub_path from 1 for char_length(table1.path))这样的东西，尽管在连接表达式中抛出多个计算可能会使情况变得更糟。