Planetscale中使用Deferred JOIN报错：无法处理无权威表的JOIN USING

问题背景 #

尝试在Planetscale平台实现Deferred JOIN优化技术，但执行查询时持续报错：can't handle JOIN USING without authoritative tables，本人对MySQL了解有限，无法定位问题根源。

示例查询语句 #

SELECT * FROM Articles
INNER JOIN (
  SELECT id FROM Story ORDER BY createdAt,id LIMIT 20 OFFSET xxx
) dummy USING (id)
ORDER BY
  createdAt,id;

表结构信息 #

表字段详情 #

+-----------+-----------------+------+-----+----------------------+-------------------+
| Field     | Type            | Null | Key | Default              | Extra             |
+-----------+-----------------+------+-----+----------------------+-------------------+
| id        | bigint unsigned | NO   | PRI |                      | auto_increment    |
| title     | varchar(255)    | NO   | MUL |                      |                   |
| synopsis  | varchar(2048)   | NO   |     |                      |                   |
| createdAt | datetime(3)     | NO   | MUL | CURRENT_TIMESTAMP(3) | DEFAULT_GENERATED |
| updatedAt | datetime(3)     | YES  |     |                      |                   |
| content   | mediumtext      | NO   | MUL |                      |                   |
| wordcount | int             | NO   |     |                      |                   |
| authorId  | int             | NO   | MUL |                      |                   |
+-----------+-----------------+------+-----+----------------------+-------------------+

建表语句（补充） #

CREATE TABLE `Articles` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `synopsis` varchar(2048) COLLATE utf8mb4_unicode_ci NOT NULL,
  `createdAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt` datetime(3) DEFAULT NULL,
  `content` varchar(4) COLLATE utf8mb4_unicode_ci NOT NULL,
  `wordcount` tinyint(1) NOT NULL DEFAULT '0',
  `authorId` int DEFAULT NULL, PRIMARY KEY(`id`),
  KEY `Story_authorId_idx`(`authorId`), 
  KEY `Story_createdAt_idx`(`createdAt`), 
  KEY `createdAt`(`createdAt`), 
  FULLTEXT KEY `Story_title_content_synopsis_idx`(`title`, `content`, `synopsis`), 
  FULLTEXT KEY `Story_title_idx`(`title`), 
  FULLTEXT KEY `Story_content_idx`(`content`)
)
ENGINE = InnoDB AUTO_INCREMENT = 7892518284260113982 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci

补充说明：最初通过Prisma生成数据库Schema，后因性能问题改用原生SQL查询。

解决方案 #

1. 替换JOIN USING为JOIN ON #

报错根源是Planetscale底层基于Vitess，Vitess对JOIN USING有限制：USING要求关联的双方都是权威表（即物理存在的实体表），而子查询生成的临时结果集dummy不被视为权威表。

修改查询语句，将USING (id)替换为ON关联条件：

SELECT * FROM Articles
INNER JOIN (
  SELECT id FROM Story ORDER BY createdAt,id LIMIT 20 OFFSET xxx
) dummy ON Articles.id = dummy.id
ORDER BY
  createdAt,id;

2. 检查表名一致性 #

注意到查询中子查询从Story表取数据，但提供的建表语句是Articles表，若Story是笔误（应为Articles），需修正子查询的表名，否则会因表不存在或关联逻辑错误导致其他问题：

SELECT * FROM Articles
INNER JOIN (
  SELECT id FROM Articles ORDER BY createdAt,id LIMIT 20 OFFSET xxx
) dummy ON Articles.id = dummy.id
ORDER BY
  createdAt,id;

3. 确认索引有效性 #

你的Articles表已创建createdAt索引，子查询中的ORDER BY createdAt,id可以利用该索引，确保分页查询的性能，这点无需额外调整。

内容的提问来源于stack exchange，提问作者wes