mysql - 如何在mysql中定义一个100亿行的key/value表？

Question

k  | v

k是一个唯一的字符串(varchar 32)，v也是一个字符串(varchar 50)，

我只会做这些查询:

insert into
select v from TABLE where k = "${k}"

我的建表sql应该怎么写？

像mysql中的分区？我应该使用它吗？

比如我应该用myisam还是innodb？

我有很多读取，很少插入，所以行级锁 InnoDB 可能适合吗？

Answer 1

这是一道试题吗？假设我们不允许 NULL 值，并且不允许重复，我会选择索引组织表:

CREATE TABLE mytable 
( k  VARCHAR(32) NOT NULL COMMENT 'pk (cluster key), ...'
, v  VARCHAR(50) NOT NULL COMMENT 'pk (cluster key), ...'
, PRIMARY KEY (k,v)
) ENGINE=InnoDB

最重要的是簇键，由于 WHERE 子句中的相等谓词，“k”成为前导列。

如果'k'保证是唯一的，那么它就可以作为主键。

CREATE TABLE mytable 
( k  VARCHAR(32) NOT NULL COMMENT 'pk (cluster key), ...'
, v  VARCHAR(50) NOT NULL COMMENT '...'
, PRIMARY KEY (k)
) ENGINE=InnoDB

这将防止 INSERT 创建具有重复值“k”的行。

在最坏的情况下，如果关于可空性和唯一性的假设无效，那么我们在提供集群键方面就会受到伤害。我们可以让 InnoDB 使用一个内部标识符作为集群键，并为我们的查询创建一个覆盖索引，由于内部标识符和单独索引的开销，需要大约两倍的空间:

CREATE TABLE mytable 
( k  VARCHAR(32) COMMENT ''
, v  VARCHAR(50) COMMENT ''
, KEY mytable_IX1 (k,v)
) ENGINE=InnoDB

这几乎没有效率，但它确实允许重复和 NULL。同样，我们想要一个前导列为 k 的索引(因为 WHERE 子句中的相等谓词)，并且还包括 v(使其成为覆盖索引)所以SELECT查询可以从索引页面中得到满足，而不需要在底层数据表中查找页面。

MySQL 支持 InnoDB 以外的存储引擎。这是我们最好的猜测，没有关于集群、复制等的任何其他规范。

假设启用了innodb_file_per_table，我会考虑分区。这不会改变查询性能，但这可以提高表的可管理性，例如，如果我们想要或需要重组

  PARTITION BY RANGE (k) 
  ( PARTITION ke VALUES LESS THAN ('e')
  , PARTITION ki VALUES LESS THAN ('i')
  , PARTITION ko VALUES LESS THAN ('o')
  , PARTITION ku VALUES LESS THAN ('u')
  , PARTITION kz VALUES LESS THAN ('z')
  , PARTITION px VALUES LESS MAXVALUE
  )

然后我们可以一次重组一个分区:

  ALTER TABLE mytable REBUILD PARTITION ko;