PostgresQL 中的 MySQL 处理程序

Question

是否可以在PostgreSQL中实现“MySQL Handler”？

使用光标几乎是我搜索的工具。它允许快速前进和后退，逐行移动。无需获取大结果集。

DECLARE mycursor CURSOR FOR SELECT * FROM mytable ORDER BY "name";

但是如何将光标设置在某一行的开头呢？例如，从第一个“M”名称开始列表。

如果我使用这个光标:

DECLARE mycursor CURSOR FOR 
SELECT * 
FROM mytable 
WHERE "name" LIKE 'M%' ORDER BY "name";

我只能在“M”记录中向前和向后移动，但不能再向后移动到“A”或向前移动到“Z”记录。

我只找到了一个解决方案，通过整个排序结果集的 ROW_NUMBER() 和 OVER() 获取带有绝对行号的“第一个 M-Record”。然后在整个结果集(A 到 Z)上创建光标，并将光标移动到第一个 M 出现处

MOVE FORWARD nr_of_first_m FROM mycursor;

有更好的解决办法吗？因为执行这些查询需要超过 1000 毫秒。

Answer 1

根据您的链接， HANDLER seems to be a MySQL extension that exposes low level ISAM-like access to applications。我过去曾在旧的直接访问共享文件 ISAM 数据库产品中使用过类似的接口(interface)，并且很高兴在客户端/服务器 SQL 数据库中看到它。 (如果我四年前就知道这一点，那么为我之前用于业务应用程序的 1983 4GL 编写一个替代解释器就会容易得多)。

PostgreSQL 没有在 SQL 级别公开任何等效功能。最接近的是 a scrollable cursor - 但由于可见性规则和事务隔离，这可能需要具体化数据集的排序副本(尽管通常不适用于来自没有聚合、窗口等的单个表中的 SELECT 上的简单光标，这不是 with hold )。然而，正如您已经注意到的，PostgreSQL 的 FETCH 和 MOVE 不支持基于值的滚动，仅支持基于行数的滚动，这似乎使它们不适合您的要求。

PostgreSQL 中“获取前一行”或“获取下一行”的常用解决方案是在 SNAPSHOT 或 SERIALIZABLE 隔离中工作，并使用如下查询:

SELECT * FROM my_table WHERE the_key > 'last_seen_key_value' ORDER BY the_key ASC LIMIT 1;

例如如果您上次看到“Matthew”并想要下一个名字:

SELECT * FROM my_table WHERE "name" > 'Matthew' ORDER BY "name" ASC LIMIT 1;

或以前的名字:

SELECT * FROM my_table WHERE "name" < 'Matthew' ORDER BY "name" DESC LIMIT 1;

只要您在键上有合适的索引，此策略就非常有效 - 对于 utf-8 数据库，在本例中，您将需要 text_pattern_ops 上的 "name" b 树索引。它仍然远不及 ISAM 表(如 MyISAM)的原始访问速度，但它可能与 MySQL 在内部使用 InnoDB 表句柄时所做的非常相似，因为它必须解决与 PostgreSQL 类似的问题。虽然存在一些解析和规划开销，但您可以通过保留一对准备好的语句并重新使用它们来消除其中的一些开销。

您可以使用低级 C 代码在 PostgreSQL 中实现类似 HANDLE 的内容来访问堆和索引，但在并发事件、真空等情况下正确实现它会具有挑战性。需要对 PostgreSQL 内部结构有丰富的经验，特别是在使用索引访问方法方面。如果您准备投入几个月的时间来学习和实现它，您可以研究源代码，然后在 pgsql-hackers 上发布初步建议。或者，如果这是业务关键功能，并且您需要 PostgreSQL 用于其他目的，则可以使用 contact someone who does professional PostgreSQL development - 但不要指望这样的报价会很低。

否则，如果您需要这种直接的低级访问，最好坚持使用直接支持您所需的数据库产品。