database - SQLAlchemy、UUID、Sharding 和 AUTO_INCREMENT 主键……如何让它们协同工作？

Question

我有一个关于 SQLAlchemy、数据库分片和 UUID 的问题要问你们这些好人。

我目前正在使用 MySQL，其中有一个表格:

CREATE TABLE foo (
    added_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    id BINARY(16) NOT NULL,
    ... other stuff ...
    UNIQUE KEY(id)
);

这张 table 的一些背景。我从不关心'added_id'，我只是用来确保插入的项目在磁盘上聚集在一起(因为用于索引 MySQL 中的表的 B-Tree 使用主键作为集群索引)。 “id”列包含 UUID 的二进制表示形式——这是我真正关心的列，所有其他内容都引用此 ID。同样，我不希望 UUID 成为主键，因为 UUID 是随机的，因此创建用于索引表的 B-Tree 具有可怕的 IO 特性(至少其他地方是这么说的)。此外，尽管 UUID1 包含时间戳以确保按“顺序”顺​​序生成 ID，但在 ID 中包含 MAC 地址使它成为我宁愿避免的东西。因此，我想使用 UUID4。

好的，现在转到 SQLAlchemy 部分。在 SQLAlchemy 中，可以使用 ORM 为上表定义一个模型，方法如下:

# The SQL Alchemy ORM base class
Base = declerative_base()

# The model for table 'foo'
class Foo(Base):
    __table__ = 'foo'
    add_id = Column(Integer, primary_key=True, nullable=False)
    id = Column(Binary, index=True, unique=True, nullable=False)
    ...

同样，这与上面的 SQL 基本相同。

现在开始这个问题。假设这个数据库将被分片(水平分区)为 2 个(或更多)独立的数据库。现在，(假设没有删除)这些数据库中的每一个都将在表 foo 中具有 added_id 为 1、2、3 等的记录。由于 SQLAlchemy 使用 session 来管理正在处理的对象，以便每个对象仅由其主键标识，因此似乎有可能出现我可以结束尝试从两个 Foo 对象访问两个对象的情况具有相同 added_id 的分片会导致托管 session 中出现一些冲突。

有人遇到过这个问题吗？你做了什么来解决它？或者，更有可能的是，我是否遗漏了 SQLAlchemy 文档中的某些内容以确保不会发生这种情况。然而，查看 SQLAlchemy 下载提供的分片示例(examples/sharding/attribute_shard.py)，他们似乎通过将其中一个数据库分片指定为 ID 生成器来回避这个问题......创建一个隐式瓶颈作为所有INSERTS 必须针对该单个数据库才能获得 ID。 (他们还提到使用 UUID，但显然这会导致索引的性能问题。)

或者，有没有办法将 UUID 设置为主键，并使用 added_id 将数据聚集在磁盘上？如果在 MySQL 中不可行，是否可以在其他数据库(如 Postgres)中实现？

在此先感谢您的所有意见!

---更新----我只想添加一个我收到的关于这个问题的带外答案。下面的文字不是我写的，我只是想把它放在这里以防有人觉得它有用。

The easiest way to avoid that situation with MySQL and auto increment keys is to use different auto increment offsets for each database, e.g.:

ALTER TABLE foo AUTO_INCREMENT=100000;

The downside is that you need to take care in terms of how you configure each shard, and you need to plan a bit wrt the total number of shards you use.

There isn't any way to convince MySQL to use a non-primary key for the clustered index. If you don't care about using SQLAlchemy to manage your database schema (although, you probably should), you can simply set the UUID as the primary key in the SQLAlchemy schema and leave the add_id as the pk in the actual table.

I've also seen alternate solutions that simply use an external server (e.g. redis) to maintain the row id.

Answer 1

是的，您可以使用“primary_key”映射器参数将表的任何列指定为用于映射目的的主键，它是列对象的列表或单个列:

Base = declarative_base()

# The model for table 'foo'
class Foo(Base):
    __table__ = 'foo'
    add_id = Column(Integer, primary_key=True, nullable=False)
    id = Column(Binary, index=True, unique=True, nullable=False)

    __mapper_args__ = {'primary_key': id}

上面，虽然 SQLAlchemy Core 会将“add_id”视为“自动增量”列，但映射器对它基本上不感兴趣，而是在考虑对象的“身份”时使用“id”作为它关心的列.

参见 documentation for mapper()更多描述。