Cockroach Design 翻译 ( 十八) SQL、参考文献（END）

1  SQL

Each node ina cluster can accept SQL client connections. CockroachDB supports the PostgreSQLwire protocol, to enable reuse of native PostgreSQL client drivers. Connectionsusing SSL and authenticated using client certificates are supported and evenencouraged over unencrypted (insecure) and password-based connections.

集群中的每个节点都可以接受SQL客户端连接。CockroachDB支持PostgreSQL有线协议，以重用本地PostgreSQL客户端驱动。支持使用SSL连接和使用客户端证书认证，甚至鼓励不加密（不安全）的基于口令的连接。

Eachconnection is associated with a SQL session which holds the server-side stateof the connection. Over the lifespan of a session the client can send SQL to open/closetransactions, issue statements or queries or configure session parameters, muchlike with any other SQL database.

每个连接与一个SQL会话关联，该会话保持连接的server一边的状态。在会话的整个生命周期，客户端可以发送SQL来打开/关闭事务、发送语句、查询，或者设置会话参数，像其他SQL数据库一样。

1.1 Language support 语言支持

CockroachDB alsoattempts to emulate the flavor of SQL supported by PostgreSQL, although it alsodiverges in significant ways:

CockroachDB也努力仿效PostgreSQL支持的SQL，尽管它在如下重要方式上有所不同：

·        CockroachDB exclusivelyimplements MVCC-based consistency for transactions, and thus only supportsSQL's isolation levels SNAPSHOT and SERIALIZABLE. The other traditional SQLisolation levels are internally mapped to either SNAPSHOT or SERIALIZABLE.

·        对于事务而言，CockroachDB仅实现了基于MVCC的一致性，这样事务就仅支持快照和序列化两种SQL隔离级别。其他传统SQL隔离级别在CoackroachDB内部被映射成快照或者序列化隔离级别。

·        CockroachDB implements itsown SQL type system which only supports alimited form ofimplicit coercions between typescompared to PostgreSQL. The rationale is to keep the implementation simple andefficient, capitalizing on the observation that 1) most SQL code in clients isautomatically generated with coherent typing already and 2) existing SQL codefor other databases will need to be massaged for CockroachDBanyways.

·        CockroachDB实现了它自己的SQL类型系统，与PostgreSQL相比，仅支持类型间隐式强制转换的一种有限形式。这样做的理论基础依据是保持实现简单且高效，这利用了观察所得：1）客户端中大多数SQL代码已经自动产生并与所键入一致；2）访问其他数据库的现存SQL代码移到Cockroach无论如何也要做修改。

1.2 SQL architecture （SQL结构）

Clientconnections over the network are handled in each node by a pgwire serverprocess (goroutine). This handles the stream of incoming commands and sendsback responses including query/statement results. The pgwire server alsohandles pgwire-level preparedstatements, binding preparedstatements to arguments and looking up prepared statements for execution.

网络上的客户端连接由每个节点上的pgwireserver进程（goroutine）管理。它处理进来的命令流并发回包含查询/语句结果集的响应。Pgwire server也管理pgwire层预处理语句，绑定预处理语句到参数并且为执行查找预处理语句。

Meanwhilethe state of a SQL connection is maintainedby a Session object and a monolithic planner object(one per connection) which coordinates execution between the session, thecurrent SQL transaction state and the underlying KV store.

期间，SQL连接的状态由一个会话对象和一个整体计划者对象（一个连接一个）来维护，计划者对象协调本会话、当前SQL事务状态和下层KV store来完成命令的执行。

Uponreceiving a query/statement (either directly or via an execute command for apreviously prepared statement) the pgwire server forwards the SQL text tothe planner associated with the connection. The SQL code is thentransformed into a SQL query plan. The query plan is implemented as a tree ofobjects which describe the high-level data operations needed to resolve thequery, for example "join", "index join", "scan","group", etc.

依据收到的查询/语句（直接的命令或者由预处理语句生成的执行命令），pgwire server将SQL文本推到与本连接相关联的计划者。然后SQL代码被转成SQL查询计划。查询计划被实现为一个对象树，该对象树描述了查询解析后所需的高层数据操作，如：“join”、“index join”、“scan”、“group”，等等。

The queryplan objects currently also embed the run-time state needed for the executionof the query plan. Once the SQL query plan is ready, methods on these objectsthen carry the execution out in the fashion of "generators" in otherprogramming languages: each node starts its children nodes andfrom that point forward each child node serves as a generator fora stream of result rows, which the parent node can consume and transformincrementally and present to its own parent node also as a generator.

查询计划对象目前也嵌入了查询计划执行时所需的运行时状态。一旦查询计划准备好，这些对象上的方法就会以“生成器”的方式用其他语言被执行：每个节点启动它的孩子节点，从那时起每个孩子节点作为一个结果行集流的“生成器”提供服务，其父节点会消费这个流并且进一步转换，同时呈现给它的父节点时，自己也是一个“生成器”。

The top-levelplanner consumes the data produced by the top node of the query plan andreturns it to the client via pgwire.

顶层计划者消费查询计划顶层节点产生的数据并且通过pgwire协议返回给客户端。

1.3 Data mapping between the SQLmodel and KV （SQL模型和KV之间数据映射）

Every SQL tablehas a primary key in CockroachDB. (If a table is created without one, animplicit primary key is provided automatically.) The table identifier, followedby the value of the primary key for each row, are encoded as the prefix ofa key in the underlying KV store.

Eachremaining column or column family in the table is then encodedas a value in the underlying KV store, and the column/family identifier isappended as suffix to the KV key.

在CockroachDB中的每个SQL表都有一个主键（如果一个表没有主键，会自动生成一个隐式主键）。表的标识+每行主键的值，被编码后作为底层KV store中的key的前缀。表中其余的列或者列族被编码为底层KVstore中的一个值，该列/族标识作为后缀追加到KV key中。

For example:

例如：

·        after table customers is created in a database mydb witha primary key column name and normalcolumns address and URL, the KV pairs to store theschema would be:

·        在mydb库中创建带有一个主键和两个正常列（地址和URL）的customer表后，存储schema的KV键对见下表：

Key

Values

/system/databases/mydb/id

51

/system/tables/customer/id

42

/system/desc/51/42/address

69

/system/desc/51/42/url

66

(The numericvalues on the right are chosen arbitrarily for the example; the structure ofthe schema keys on the left is simplified for the example and subject tochange.) Each database/table/column name is mapped to a spontaneously generatedidentifier, so as to simplify renames.

（右侧的数字值是为举例子随意取的；左侧schemakey的结构为举例方便进行了简化并且可能随时变化。）每个database/table/column名称被映射到自然生成的标识，这是为了简化重命名。

·        Then for a single row in thistable:

然后是该表中的单行：

Key

Values

/51/42/Apple/69

1 Infinite Loop, Cupertino, CA

/51/42/Apple/66

http://apple.com/

Each key hasthe table prefix /51/42 followed by theprimary key prefix /Apple followed by thecolumn/family suffix (/66,/69). TheKV value is directly encoded from the SQL value.

Efficientstorage for the keys is guaranteed by the underlying RocksDB engine by means ofprefix compression.

每个key有表前缀/51/42，紧跟主键前缀/Apple，然后是列/族后缀（/66、/69）。KV值从SQL值直接编码。Key的高效存储由底层RocksDB引擎通过前缀压缩方式来保障。

Finally, forSQL indexes, the KV key is formed using the SQL value of the indexed columns,and the KV value is the KV key prefix of the rest oftheindexed row.

最终，对于SQL索引，使用索引列的SQL值形成KV的key，KV的value是该被索引行其余的KVkey前缀。（译注：因为一行有多个字段值，在底层存储为KV时，每个字段及其值都存储成一个KV对，所以此处会有很多的KV key前缀作为索引的）

2  References 参考文献

无。