三千字教你使用MOT

上一期我们介绍了MOT特性及部署。说了为什么要用，接下来就是如何使用MOT。使用MOT非常简单，以下几个小节将会进行描述。

openGauss允许应用程序使用MOT和基于标准磁盘的表。MOT适用于最活跃、高竞争和对吞吐量敏感的应用程序表，也可用于所有应用程序的表。

以下命令介绍如何创建MOT，以及如何将现有的基于磁盘的表转换为MOT，以加速应用程序的数据库相关性能。MOT尤其有利于已证明是瓶颈的表。

工作流程概述

以下是与使用MOT相关的任务的简单概述：

本小节还介绍了如何执行各种与MOT相关的附加任务，以及MOT SQL覆盖和限制。

目录

1.授予用户权限

2.创建/删除MOT

3.为MOT创建索引

4.将磁盘表转换为MOT

4.1前置条件检查

4.2转换

4.3转换示例

5.查询原生编译

6.查询编译：PREPARE语句

6.1 运行命令

6.2 轻量执行支持的查询

6.3 轻量执行不支持的查询

7. 重试中止事务

8. MOT外部支持工具

9. MOT SQL覆盖和限制

9.1 不支持的特性

9.2 MOT限制

9.3 不支持的DDL *** 作

9.4 不支持的数据类型

9.5 不支持的索引DDL和索引

9.6 不支持的DML

9.7 原生编译和轻量执行不支持的查询

---

1.授予用户权限

以授予数据库用户对MOT存储引擎的访问权限为例。每个数据库用户仅执行一次，通常在初始配置阶段完成。

 说明： MOT通过外部数据封装器（Foreign Data Wrapper，FDW）机制与openGauss数据库集成，所以需要授权用户权限。

要使特定用户能够创建和访问MOT（DDL、DML、SELECT），以下语句只执行一次：

GRANT USAGE ON FOREIGN SERVER mot_server TO ;

所有关键字不区分大小写。

2.创建/删除MOT

创建MOT非常简单。只有MOT中的创建和删除表语句与openGauss中基于磁盘的表的语句不同。SELECT、DML和DDL的所有其他命令的语法对于MOT表和openGauss基于磁盘的表是一样的。

创建MOT：

create FOREIGN table test(x int) [server mot_server];

以上语句中：

始终使用FOREIGN关键字引用MOT。在创建MOT表时，[server mot_server]部分是可选的，因为MOT是一个集成的引擎，而不是一个独立的服务器。上文以创建一个名为test的内存表（表中有一个名为x的整数列）为例。在下一节（创建索引）中将提供一个更现实的例子。如果postgresql.conf中开启了增量检查点，则无法创建MOT。因此请在创建MOT前将enable_incremental_checkpoint设置为off。

删除名为test的MOT：

drop FOREIGN table test;

3.为MOT创建索引

支持标准的PostgreSQL创建和删除索引语句。

例如：

create index text_index1 on test(x) ;

创建一个用于TPC-C的ORDER表，并创建索引：
create FOREIGN table bmsql_oorder ( 
  o_w_id       integer      not null, 
  o_d_id       integer      not null, 
  o_id         integer      not null, 
  o_c_id       integer not null, 
  o_carrier_id integer,          
  o_ol_cnt     integer, 
  o_all_local  integer, 
  o_entry_d    timestamp, 
  primary key (o_w_id, o_d_id, o_id) 
); 
create index  bmsql_oorder_index1 on bmsql_oorder(o_w_id, o_d_id, o_c_id, o_id) ;

 说明： 在MOT名字之前不需要指定FOREIGN关键字，因为它仅用于创建和删除表的命令。

有关MOT索引限制，请参见“MOT SQL覆盖和限制”的索引部分内容。

4.将磁盘表转换为MOT

磁盘表直接转换为MOT尚不能实现，这意味着尚不存在将基于磁盘的表转换为MOT的ALTER TABLE语句。

下面介绍如何手动将基于磁盘的表转换为MOT，如何使用gs_dump工具导出数据，以及如何使用gs_restore工具导入数据。

4.1前置条件检查

检查待转换为MOT的磁盘表的模式是否包含所有需要的列。

检查架构是否包含任何不支持的列数据类型，具体参见“不支持的数据类型”章节。

如果不支持特定列，则建议首先创建一个更新了模式的备磁盘表。此模式与原始表相同，只是所有不支持的类型都已转换为支持的类型。

使用以下脚本导出该备磁盘表，然后导入到MOT中。

4.2转换

要将基于磁盘的表转换为MOT，请执行以下步骤：

暂停应用程序活动。使用gs_dump工具将表数据转储到磁盘的物理文件中。请确保使用data only。重命名原始基于磁盘的表。创建同名同模式的MOT。请确保使用创建FOREIGN关键字指定该表为MOT。使用gs_restore将磁盘文件的数据加载/恢复到数据库表中。浏览或手动验证所有原始数据是否正确导入到新的MOT中。下面将举例说明。恢复应用程序活动。

 须知： 由于表名称保持不变，应用程序查询和相关数据库存储过程将能够无缝访问新的MOT，而无需更改代码。请注意，MOT目前不支持跨引擎多表查询（如使用Join、Union和子查询）和跨引擎多表事务。因此，如果在多表查询、存储过程或事务中访问原始表，则必须将所有相关的磁盘表转换为MOT，或者更改应用程序或数据库中的相关代码。

4.3转换示例

假设要将数据库benchmarksql中一个基于磁盘的表customer迁移到MOT中。

将customer表迁移到MOT， *** 作步骤如下：

检查源表列类型。验证MOT支持所有类型，详情请参阅“不支持的数据类型”章节。

benchmarksql-# \d+ customer 
                       Table "public.customer" 
 Column |  Type   | Modifiers | Storage | Stats target | Description 
--------+---------+-----------+---------+--------------+------------- 
 x      | integer |           | plain   |              | 
 y      | integer |           | plain   |              | 
Has OIDs: no 
Options: orientation=row, compression=no

请检查源表数据。

benchmarksql=# select * from customer; 
 x | y 
---+--- 
 1 | 2 
 3 | 4 
(2 rows)

只能使用gs_dump转储表数据。

$ gs_dump -Fc benchmarksql -a --table customer -f customer.dump -p 16000
gs_dump[port='15500'][benchmarksql][2020-06-04 16:45:38]: dump database benchmarksql successfully 
gs_dump[port='15500'][benchmarksql][2020-06-04 16:45:38]: total time: 332  ms

重命名源表。

benchmarksql=# alter table customer rename to customer_bk; 
ALTER TABLE

创建与源表完全相同的MOT。

benchmarksql=# create foreign table customer (x int, y int); 
CREATE FOREIGN TABLE 
benchmarksql=# select * from customer; 
 x | y 
---+--- 
(0 rows)

将源转储数据导入到新MOT中。

$ gs_restore -C -d benchmarksql customer.dump -p 16000
restore operation successful 
total time: 24  ms 
Check that the data was imported successfully. 
benchmarksql=# select * from customer; 
 x | y 
---+--- 
 1 | 2 
 3 | 4 
(2 rows) 
      
benchmarksql=# \d 
                                List of relations 
 Schema |    Name     |     Type      | Owner  |             Storage 
--------+-------------+---------------+--------+---------------------------------- 
 public | customer    | foreign table | aharon | 
 public | customer_bk | table         | aharon | {orientation=row,compression=no} 
(2 rows)

5.查询原生编译

MOT的另一个特性是，在预编译的完整查询需要执行之前，能够以原生格式（使用PREPARE语句）准备并解析这些查询。

这种原生格式方便后续更有效地执行（使用EXECUTE命令）。这种执行类型速度要快得多，因为原生格式在执行期间绕过多个数据库处理层，从而获得更好的性能。

这种分工避免了重复的解析分析 *** 作。查询和事务语句可以交互执行。此功能有时称为即时（Just-In-Time，JIT）查询编译。

5.1 查询编译：PREPARE语句

若要使用MOT的原生查询编译，请在执行查询之前调用PREPARE客户端语句。MOT将预编译查询和（或）从缓存预加载先前预编译的代码。

下面是SQL中PREPARE语法的示例：

PREPARE name [ ( data_type [, ...] ) ] AS statement 

PREPARE在数据库服务器中创建一个预处理语句，该语句是一个可用于优化性能的服务器端对象。

5.2 运行命令

发出EXECUTE命令时，将解析、分析、重写和执行预处理语句。这种分工避免了重复的解析分析 *** 作，同时使执行计划依赖于特定的设置值。

下面是在Java应用程序中调用PREPARE和EXECUTE语句的示例。

conn = DriverManager.getConnection(connectionUrl, connectionUser, connectionPassword); 

// Example 1: PREPARE without bind settings 
String query = "SELECT * FROM getusers";  
PreparedStatement prepStmt1 = conn.prepareStatement(query); 
ResultSet rs1 = pstatement.executeQuery()) 
while (rs1.next()) {…} 

// Example 2: PREPARE with bind settings 
String sqlStmt = "SELECT * FROM employees where first_name=? and last_name like ?"; 
PreparedStatement prepStmt2 = conn.prepareStatement(sqlStmt); 
prepStmt2.setString(1, "Mark"); // first name “Mark” 
prepStmt2.setString(2, "%n%"); // last name contains a letter “n” 
ResultSet rs2 = prepStmt2.executeQuery()) 
while (rs2.next()) {…}

MOT编译支持的特性和不支持的特性见下文。

5.3 轻量执行支持的查询

以下查询类型适合轻量执行：

简单点查询

SELECT (including SELECT for UPDATE)UPDATEDELETE

INSERT查询

引用主键的完整前缀的范围UPDATE查询

引用主键的完整前缀的范围SELECT查询

JOIN查询，其中一部分或两部分重叠为点查询

引用每个连接表中主键的完整前缀的JOIN查询

5.4 轻量执行不支持的查询

任何特殊的查询属性都不适用于轻量执行。特别是如果以下条件中的任何一项适用，则该查询不适合轻量执行。有关更多信息，请参阅“原生编译和轻量执行不支持的查询”。

需要强调一点，如果查询语句不适用原生编译和轻量执行，不向客户端报告错误，查询仍以正常和规范的方式执行。

有关MOT原生编译功能的详细信息，请参阅 “查询原生编译”或“查询原生编译（JIT）”的有关内容。

6. 重试中止事务

在乐观并发控制（OCC）中，在COMMIT阶段前的事务期间（使用任何隔离级别）不会对记录进行锁定。这是一个能显著提高性能的强大优势。它的缺点是，如果另一个会话尝试更新相同的记录，则更新可能会失败。所以必须中止整个事务。这些所谓的更新冲突是由MOT在提交时通过版本检查机制检测到的。

 说明： 使用悲观并发控制的引擎，如标准Postgres和openGauss基于磁盘的表，当使用SERIALIZABLE或REPEATABLE-READ隔离级别时，也会发生类似的异常中止。

这种更新冲突在常见的OLTP场景中非常少见，在使用MOT时尤其少见。但是，由于仍有可能发生这种情况，开发人员应该考虑使用事务重试代码来解决此问题。

下面以多个会话同时尝试更新同一个表为例，说明如何重试表命令。有关更多详细信息，请参阅“OCC与2PL的区别举例”部分。下面以TPC-C支付事务为例。

int commitAborts = 0; 

while (commitAborts < RETRY_LIMIT) { 

    try {                         
        stmt =db.stmtPaymentUpdateDistrict; 
        stmt.setDouble(1, 100); 
        stmt.setInt(2, 1); 
        stmt.setInt(3, 1); 
        stmt.executeUpdate(); 

        db.commit();                      

        break; 
    }               
    catch (SQLException se) { 
        if(se != null && se.getMessage().contains("could not serialize access due to concurrent update")) { 
            log.error("commmit abort = " + se.getMessage()); 
            commitAborts++; 
            continue; 
        }else { 
            db.rollback(); 
        } 

        break; 
    } 
}

7. MOT外部支持工具

为了支持MOT，修改了以下外部openGauss工具。请确保使用的工具是最新版本。下面将介绍与MOT相关的用法。有关这些工具及其使用方法的完整说明，请参阅openGauss工具参考。

gs_ctl（全量和增量）

此工具用于从主服务器创建备服务器，以及当服务器的时间线偏离后，将服务器与其副本进行同步。

在 *** 作结束时，工具将获取最新的MOT检查点，同时考虑checkpoint_dir配置值。

检查点从源服务器的checkpoint_dir读取到目标服务器的checkpoint_dir。

目前MOT不支持增量检查点。因此，gs_ctl增量构建对于MOT来说不是以增量方式工作，而是以全量方式工作。Postgres磁盘表仍然可以增量构建。

gs_basebackup

gs_basebackup用于准备运行中服务器的基础备份，不影响其他数据库客户端。

MOT检查点也会在 *** 作结束时获取。但是，检查点的位置是从源服务器中的checkpoint_dir获取的，并传输到源数据目录中，以便正确备份。

gs_dump

gs_dump用于将数据库模式和数据导出到文件中。支持MOT。

gs_restore

gs_restore用于从文件中导入数据库模式和数据。支持MOT。

8. MOT SQL覆盖和限制

MOT设计几乎能够覆盖SQL和未来特性集。例如，大多数支持标准的Postgres SQL，也支持常见的数据库特性，如存储过程、自定义函数等。

下面介绍各种SQL覆盖和限制。

8.1 不支持的特性

MOT不支持以下特性：

跨引擎 *** 作：不支持跨引擎（磁盘+MOT）的查询、视图或事务。计划于2021年实现该特性。MVCC、隔离：不支持没有快照/可序列化隔离。计划于2021年实现该特性。原生编译（JIT）：SQL覆盖有限。此外，不支持存储过程的JIT编译。本地内存限制为1GB。一个事务只能更改小于1GB的数据。容量（数据+索引）受限于可用内存。未来将提供Anti-caching和数据分层功能。不支持全文检索索引。不支持逻辑复制特性。

此外，下面详细列出了MOT、MOT索引、查询和DML语法的各种通用限制，以及查询原生编译的特点和限制。

8.2 MOT限制

MOT功能限制：

按范围分区AES加密流 *** 作自定义类型子事务DML触发器DDL触发器“C”或“POSIX”以外的排序规则 8.3 不支持的DDL *** 作 修改表结构创建including表创建as select表按范围分区创建无日志记录子句（no-logging clause）的表创建可延迟约束主键（DEFERRABLE）重建索引表空间使用子命令创建架构 8.4 不支持的数据类型 UUIDUser-Defined Type (UDF)Array data typeNVARCHAR2(n)NVARCHAR(n)ClobNameBlobRawPathCircleReltimeBit varying(10)TsvectorTsqueryJSONHSTOREBoxTextLinePointLSEGPOLYGONINETCIDRMACADDRSmalldatetimeBYTEABitVarbitOIDMoney无限制的varchar/character varyingHSTOREXML 8.5 不支持的索引DDL和索引

在小数和数值类型上创建索引

在可空列上创建索引

单表创建索引总数>9

在键大小>256的表上创建索引

键大小包括以字节为单位的列大小+列附加大小，这是维护索引所需的开销。下表列出了不同列类型的列附加大小。

此外，如果索引不是唯一的，额外需要8字节。

下面是伪代码计算键大小：

keySize =0; 
      
for each (column in index){ 
      keySize += (columnSize + columnAddSize); 
} 
if (index is non_unique) { 
      keySize += 8; 
}

列类型	列大小	列附加大小
varchar	N	4
tinyint	1	1
smallint	2	1
int	4	1
longint	8	1
float	4	2
double	8	3

上表中未指定的类型，列附加大小为零（例如时间戳）。

8.6 不支持的DML Merge intoSelect intoLock tableCopy from table 8.7 原生编译和轻量执行不支持的查询 查询涉及两个以上的表查询有以下任何一个情况： 非原生类型的聚合窗口功能子查询子链接Distinct-ON修饰语（distinct子句来自DISTINCT ON）递归（已指定WITH RECURSIVE）修改CTE（WITH中有INSERT/UPDATE/DELETE）

以下子句不支持轻量执行：

Returning listGroup By clauseGrouping setsHaving clauseWindows clauseDistinct clauseSort clause that does not conform to native index orderSet operationsConstraint dependencies

好了，今天的文章就到这里。你学会了吗？