主页 / dba / 问题 / 166815
Accepted
BernardV
Asked: 2017-03-11 02:18:52 +0800 CST

存储 0 到 100 之间数值的列的最有效数据类型

  • 772

在我的 MySQL 数据库上，我正在考虑使用TINYINT (Unsigned)?

你会Byte在 SQL Server 上使用吗？

sql-server mysql

2 个回答

  1. Best Answer

    此答案仅涵盖 SQL Server。答案取决于您如何定义高效：是空间还是 CPU？两者之间可以进行权衡。

    让我们首先检查存储整数数据的数据类型的文档：

    ╔═══════════╦══════════════════════════════════════════════════════════════════════════╦═════════╗
║ Data type ║                                  Range                                   ║ Storage ║
╠═══════════╬══════════════════════════════════════════════════════════════════════════╬═════════╣
║ bigint    ║ -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807) ║ 8 Bytes ║
║ int       ║ -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)                         ║ 4 Bytes ║
║ smallint  ║ -2^15 (-32,768) to 2^15-1 (32,767)                                       ║ 2 Bytes ║
║ tinyint   ║ 0 to 255                                                                 ║ 1 Byte  ║
╚═══════════╩══════════════════════════════════════════════════════════════════════════╩═════════╝

    对于您的数据，您可以使用TINYINT，因为您的所有数据都在 0 到 255 之间，并且使用的空间最少。让我们通过将 1000 万行插入到值在 0 到 100 之间均匀分布的表中来进行快速测试。请注意，我们对所有示例表使用十列，因为行存储表的最小行大小为 9 字节。如果我们创建一个只有一列的表，我们会得到误导性的结果。我正在针对 SQL Server 2016 SP1 进行测试：

    DROP TABLE IF EXISTS dbo.X_TINYINT;

CREATE TABLE dbo.X_TINYINT (
    NUM1 TINYINT NOT NULL,
    NUM2 TINYINT NOT NULL,
    NUM3 TINYINT NOT NULL,
    NUM4 TINYINT NOT NULL,
    NUM5 TINYINT NOT NULL,
    NUM6 TINYINT NOT NULL,
    NUM7 TINYINT NOT NULL,
    NUM8 TINYINT NOT NULL,
    NUM9 TINYINT NOT NULL,
    NUM10 TINYINT NOT NULL
);

INSERT INTO dbo.X_TINYINT WITH (TABLOCK)
SELECT TOP (10000000) 
  n.n, n.n, n.n, n.n, n.n
, n.n, n.n, n.n, n.n, n.n
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
CROSS JOIN master..spt_values t3
CROSS APPLY
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 101
) n (n);

EXEC sp_spaceused 'dbo.X_TINYINT'; -- data size is 198216 KB

    如果我对一个有 10SMALLINT列的表运行相同的代码，我得到 297416 KB 的数据大小。根据引用的数据类型的大小差异，我预计会有大约 1 的差异，10000000 * 10 * (2 - 1) / 1024 = 97656 KB因此这非常接近预期的大小增加。

    根据您的 SQL Server 版本和版本，您可以进一步减少空间使用量。行压缩首先在 SQL Server 2008 Enterprise 中可用，并在 2016 SP1 的所有版本的 SQL Server 中可用。根据算法的描述，我们可能不会通过列的行压缩获得很多节省TINYINT。他们已经使用了最小的一个字节，但我们应该为数据减少 1% 的空间，因为 0 被优化为不占用字节。数据类型的元数据开销也可能有所减少。

    应用于DATA_COMPRESSION = ROW表后，我得到 187808 KB 的数据大小。

    页面压缩在与行压缩相同的版本中可用。页面压缩算法在行压缩之上以其他几种方式压缩数据。如果页面上有很多重复值，我们可能会看到显着的存储收益。

    应用于DATA_COMPRESSION = PAGE表后，我得到 109024 KB 的数据大小，这是一个相当可观的减少。

    只是为了好玩，我们可以检查数据为列存储格式时的空间使用情况。列存储索引在 SQL Server 2012 中引入，并在 2014 年和 2016 年得到进一步改进。不应仅出于节省空间的目的使用这些索引。在使用它们之前，您需要仔细研究和测试。根据您的 SQL Server 版本和版本，使用它们也有一些限制。

    DROP TABLE IF EXISTS dbo.X_TINYINT_CCI;

CREATE TABLE dbo.X_TINYINT_CCI (
    NUM1 TINYINT NOT NULL,
    NUM2 TINYINT NOT NULL,
    NUM3 TINYINT NOT NULL,
    NUM4 TINYINT NOT NULL,
    NUM5 TINYINT NOT NULL,
    NUM6 TINYINT NOT NULL,
    NUM7 TINYINT NOT NULL,
    NUM8 TINYINT NOT NULL,
    NUM9 TINYINT NOT NULL,
    NUM10 TINYINT NOT NULL
);

CREATE CLUSTERED COLUMNSTORE INDEX CCI_X_TINYINT_CCI ON dbo.X_TINYINT_CCI;

INSERT INTO dbo.X_TINYINT_CCI WITH (TABLOCK)
SELECT TOP (10000000) 
  n.n, n.n, n.n, n.n, n.n
, n.n, n.n, n.n, n.n, n.n
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
CROSS JOIN master..spt_values t3
CROSS APPLY
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 101
) n (n)
OPTION (MAXDOP 1);

EXEC sp_spaceused 'dbo.X_TINYINT_CCI'; -- data size is 160 KB

    我还可以使用压缩 CCI COLUMNSTORE_ARCHIVE，我认为它是为不会更改或经常读取的历史数据而设计的。通过将压缩选项应用于整个表，数据大小进一步减少到 88 KB。

    行存储压缩选项会增加查询的 CPU 开销。开销量将取决于您的工作负载和数据，但我们可以使用一个简单的查询来说明基本概念：

    SELECT MAX(NUM1), MIN(NUM1)
FROM dbo.X_TINYINT
OPTION (MAXDOP 1);

    一次测试后，我得到的 CPU 时间测量值是：未压缩数据为 1469 毫秒，行压缩数据为 1687 毫秒，页面压缩数据为 2000 毫秒。我没有对列存储数据进行测试，只是因为它们的工作方式如此不同。MIN在某些情况下，MAX查询可以在本地聚合，甚至可以通过元数据操作来满足。

    以下是测试表和查询的结果摘要：

    ╔═══════════════════╦══════════════════╦══════════════════════╗
║ Table Compression ║ Data Space in KB ║ Query CPU Time in ms ║
╠═══════════════════╬══════════════════╬══════════════════════╣
║ NONE              ║           198216 ║ 1469                 ║
║ ROW               ║           187808 ║ 1687                 ║
║ PAGE              ║           109024 ║ 2000                 ║
║ CCI               ║              160 ║ N/A                  ║
║ CCI ARCHIVE       ║               88 ║ N/A                  ║
╚═══════════════════╩══════════════════╩══════════════════════╝

    您将看到的确切结果取决于您的表结构、数据和工作负载。

    • 6

  2. tinyint，byte 不是一种数据类型，更多的是一种存储表达。Tinyint 存储为单个字节。

    https://technet.microsoft.com/en-us/library/ms172424(v=sql.110).aspx

    • 2

相关问题