mysql - 为什么 MEMBER OF() 比 JSON_CONTAINS() 快？

Question

我正在使用 MySQL 8 的新 JSON 功能，特别是多值索引。

我注意到有两种方法可以检查 JSON 数组是否包含给定值:MEMBER OF() 运算符和 JSON_CONTAINS() 函数。

对于我进行的每个查询，它们都返回相同的结果集，但令人惊讶的是， MEMBER OF 似乎比 JSON_CONTAINS 快 3 倍。

具有 200,000 条记录的表的示例，catIds 字段中总共有大约 700,000 个值:

CREATE TABLE test (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  catIds JSON NOT NULL,
  PRIMARY KEY (id),
  KEY test_categories ((CAST(catIds AS UNSIGNED ARRAY)))
);

INSERT INTO test (catIds) VALUES('[123, 456]');
...

...稍后插入大约 200,000 条记录:

mysql> SELECT count(*) FROM test WHERE 51 member of (catIds);
+----------+
| count(*) |
+----------+
|     7287 |
+----------+
1 row in set (0.11 sec)

mysql> SELECT count(*) FROM test WHERE JSON_CONTAINS(catIds, '51');
+----------+
| count(*) |
+----------+
|     7287 |
+----------+
1 row in set (0.36 sec)

如果首先想到这是因为字符串化的 JSON 值 '51' 可能在每次迭代中都被转换，所以我尝试先将它分配给一个变量；但这并没有让它更快:

mysql> SET @value = CAST(51 as JSON);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT count(*) FROM test WHERE JSON_CONTAINS(catIds, @value);
+----------+
| count(*) |
+----------+
|     7287 |
+----------+
1 row in set (0.38 sec)

除非我弄错了，否则 MEMBER OF() 和 JSON_CONTAINS() 在功能方面是等效的。在这种情况下， 为什么其中一个比另一个快？

Answer 1

JSON_CONTAINS()做比 MEMBER OF 更复杂的工作.
JSON_CONTAINS()必须解析它的第二个参数，即您在存储的 JSON 文档中搜索的候选 JSON 文档。

候选人可能不是您在上面的示例中搜索的简单标量。它可能是一个更复杂的文档，有自己的嵌套数组和对象。

因此，将候选与存储的文档进行比较可能必须以更复杂的方式进行比较，不仅仅是搜索单个标量值，而是递归地比较所有嵌套元素。

即使您的示例搜索是针对简单的标量值，它仍然会调用可能需要搜索复杂文档的相同代码路径。根据您的时间测量，该代码路径似乎有更多开销。

而MEMBER OF只搜索标量值，只搜索数组。它还可以通过使用缓存的预排序数组进行优化。

见 https://github.com/mysql/mysql-server/blob/8.0/sql/item_json_func.cc#L3852对于代码。