主页 / dba / 问题 / 16086
Accepted
hexparrot
Asked: 2012-04-05 09:23:17 +0800 CST

索引基于网格的数据的最有效（或有意义）方式

  • 772

我有一个 MySQL 表，我希望它有几百万行和 99% 的选择语句。我遇到的问题是想出一种有意义的方法来确定主键。（我在底部提供了一个表格转储以供参考）

对于某些背景，我正在使用二维网格，其范围在每个方向上从大约 -800000 到 +800000 不等。每行由其 X/Z 坐标标识，每个坐标可能有 1-30 个关联的位串（类型）。

我的担忧是：

1）我缺少一个有意义的方法来主键这个表。虽然我知道我可以使用 auto_increment 创建一个id字段，但我知道在实践中永远不会使用这个键，因为100%的 SELECTS 将采用以下形式：

SELECT `type`, `offset`, `bitstring` WHERE `x` = 0 AND `z` = 0;

2) 我打算通过以下方式在多列（逻辑 x/z）上建立索引：

CREATE INDEX coordinate ON bitstrings(x, z)

虽然我觉得这恰当地解决了我在现实世界中的选择，但我一直担心它的索引不足，因为有许多帖子说“是的，你需要一个 PK”。这是可以忽略 PK 的情况，还是任意性id最终仍会提供一些非常值得额外的表大小和列的幕后优化？

作为旁注，如果有任何更实用、经过验证的方法来存储此类数据，我绝对不会限制完全重构此表。

CREATE TABLE `bitstrings` (
  `x` int(11) NOT NULL COMMENT 'roughly +/- 10^6 range',
  `z` int(11) NOT NULL COMMENT 'roughly +/- 10^6 range',
  `type` smallint(6) NOT NULL COMMENT 'range: 1-4096',
  `offset` smallint(6) NOT NULL COMMENT 'range: 1-65535',
  `bitstring` blob NOT NULL COMMENT 'binary data len: 1-8192'
) ENGINE=MyISAM DEFAULT CHARSET=utf-8;

--
-- Dumping data for table `bitstrings`
--

INSERT INTO `bitstrings` (`x`, `z`, `type`, `offset`, `bitstring`) VALUES
(0, 0, 1, 0, 0x52),
(0, 0, 2, 1878, 0x52);
mysql primary-key

3 个回答

  1. Best Answer

    两件事情

    • 由于 x 和 z 的范围是 +/- 1000000，使用meduimint（meduimint 的范围是 +/- 8388608）
    • 由于偏移量和类型的范围是 1-65535，使用smallint unsigned

    做出这些改变

    CREATE TABLE `bitstrings` ( 
  `x` meduimint(11) NOT NULL COMMENT 'roughly +/- 10^6 range', 
  `z` meduimint(11) NOT NULL COMMENT 'roughly +/- 10^6 range', 
  `type` smallint(6) UNSIGNED NOT NULL COMMENT 'range: 1-4096', 
  `offset` smallint(6) UNSIGNED NOT NULL COMMENT 'range: 1-65535', 
  `bitstring` blob NOT NULL COMMENT 'binary data len: 1-8192'
) ENGINE=MyISAM DEFAULT CHARSET=utf-8;

    说到索引，你可能有一个选择

    选择 #1：使用 ID 主键

    像这样设置表

    CREATE TABLE `bitstrings` ( 
  `id` int unsigned not null auto_incrmenet,
  `x` meduimint(11) NOT NULL COMMENT 'roughly +/- 10^6 range', 
  `z` meduimint(11) NOT NULL COMMENT 'roughly +/- 10^6 range', 
  `type` smallint(6) UNSIGNED NOT NULL COMMENT 'range: 1-4096', 
  `offset` smallint(6) UNSIGNED NOT NULL COMMENT 'range: 1-65535', 
  `bitstring` blob NOT NULL COMMENT 'binary data len: 1-8192',
  primary key (id),
  key xztype (x,z,type)
) ENGINE=MyISAM DEFAULT CHARSET=utf-8;

    这可以让您使用 x 和 z 进行查询，同时检索所有 ID 以快速引用回该行：

    SELECT id FROM bitstrings WHERE x=0 and z=0;

    如果您必须检索给定 ID 的数据，您可以选择它作为

    SELECT * FROM bitstrings WHERE id = 12;

    这将检索任何 x、z 和类型的特定信息。

    选择 #2：使用 x,z,type 作为 PRIMARY KEY

    运行此查询

    SELECT COUNT(1) rcount,x,z,type FROM bitstrings GROUP BY x,z,type HAVING COUNT(1) > 1;

    如果此查询返回时根本没有任何行，则这可能是您的主键

    CREATE TABLE `bitstrings` ( 
  `x` meduimint(11) NOT NULL COMMENT 'roughly +/- 10^6 range', 
  `z` meduimint(11) NOT NULL COMMENT 'roughly +/- 10^6 range', 
  `type` smallint(6) UNSIGNED NOT NULL COMMENT 'range: 1-4096', 
  `offset` smallint(6) UNSIGNED NOT NULL COMMENT 'range: 1-65535', 
  `bitstring` blob NOT NULL COMMENT 'binary data len: 1-8192',
  primary key (x,z,type)
) ENGINE=MyISAM DEFAULT CHARSET=utf-8;

    结论

    由于所有查询主要基于 x 和 z，因此任何一个选择都可以

    • 第一个选择会产生更大的 .MYI 文件，但有两种收集数据的方法（按 x、z 分组或按 id 粒度分组）。
    • 第二种选择仅按 x 和 z 收集，但较小的 .MYI 文件。
    • 5

  2. 一种替代方法可能是将 X 和 Z 组合成一个值。这会将您限制为仅精确匹配一行的查询，因此它可能不适合您。

    SELECT `type`, `offset`, `bitstring` WHERE `xz` = 250 + (800 * 1000000);

    这也将对网格的最大大小（在我的示例中为百万 x 百万）设置一个非常严格的限制，并且您需要比 mediumint 更大的类型。

    不太可能值得麻烦，但它可以在这里和那里节省一些字节和周期。

    • 1

  3. (x,y,type) 是唯一的吗？（如果不是，那么建议的 PK 将不起作用。）

    1. 使用 InnoDB 使 PK“集群”。
    2. 使用 PARTITION 获得两个聚簇索引的提示。
    3. PK(x,z,...) PARTITION by RANGE(z)——是的，特别是第二个字段
    4. 如果您需要 AUTO_INCREMENT id，那么还有 INDEX(id)——这足以支持 auto-inc。（更一般地说，任何以 id开头的索引。）

    根据 x 和 z 的属性，这可能更好：PK(z,x,...), RANGE(x)。

    • 1

相关问题