mysql - 为什么 MEMBER OF() 或 JSON_CONTAINS() 不使用多值索引？

Question

我有下表，其中在 JSON 整数数组上设置了多值索引:

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)))
);

我已插入 200,000 条记录，例如:

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

问题是，使用或不使用索引在 catIds 字段上查询此表不会改变执行速度。我尝试过使用 MEMBER OF() 和 JSON_CONTAINS() 进行查询，无论是否有索引；速度是相同的。

事实上，EXPLAIN 显示这些查询不使用索引:

mysql> EXPLAIN SELECT count(*) FROM test WHERE 51 MEMBER OF (catIds);
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 201416 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+-------+------+----------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                              |
+-------+------+----------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select count(0) AS `count(*)` from `test`.`test` where <cache>(51) member of (`test`.`test`.`catIds`) |
+-------+------+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT count(*) FROM test WHERE JSON_CONTAINS(catIds, '51');
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 201416 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+-------+------+---------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                   |
+-------+------+---------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select count(0) AS `count(*)` from `test`.`test` where json_contains(`test`.`test`.`catIds`,<cache>('51')) |
+-------+------+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

为什么这些查询没有使用 catIds 上的索引？我错过了什么？

Answer 1

您必须在查询中使用索引定义和谓词的 JSON 路径。

https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-multi-valued说:

The only type of expression that is permitted in a multi-valued key part is a JSON path. The path need not point to an existing element in a JSON document inserted into the indexed column, but must itself be syntactically valid.

我测试了这个:

mysql> alter table test add key bk1 ((cast(catIds->'$[*]' as unsigned array)));
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain SELECT count(*) FROM test WHERE 903 MEMBER OF (catIds->'$[*]');
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ref  | bk1           | bk1  | 9       | const |    8 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+

我毫不怀疑，使用此功能会增加WTFs per minute during code reviews .

还要记住，如果优化器认为索引没有帮助，MySQL 将跳过使用索引。例如，如果表只有几行，或者您正在搜索的值出现在大多数行中。这并不是多值索引所特有的，多年来它一直是 MySQL 普通索引优化器行为的一部分。

这是一个示例:我的表中有 4096 行，但它们都是相同的。即使我搜索表中出现的值，MySQL 也会检测到它将匹配大多数行(在本例中为所有行)并避免索引。

mysql> select distinct catIds from test;
+--------------+
| catIds       |
+--------------+
| [258.0, 7.0] |
+--------------+
1 row in set (0.00 sec)

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|     4096 |
+----------+
1 row in set (0.01 sec)

mysql> explain SELECT count(*) FROM test WHERE 258 MEMBER OF (catIds);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4096 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+