为什么()或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说：

在多值关键部分中唯一允许的表达式类型是JSON路径。路径不需要指向插入到索引列中的JSON文档中的现有元素，但其本身必须是语法上有效的。

我测试了这个：

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 |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+

我毫不怀疑，使用此功能将增加代码评审期间每分钟WTF。

还请记住，如果优化器认为没有帮助，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 |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

Answer 2

MySQL中的多值索引速度如此缓慢的原因很少：*)这些都是次要索引，因此与InnoDB *中的主要索引相比有一定的损失)与常规索引所指向的记录相同，后者包含的JSON必须解压缩才能被处理。*)与常规索引不同，多值索引不能覆盖，它们总是必须从表中获取行。

所有这些都缩小了多值索引有益的条件。它们的最佳条件是：*)索引的高选择性，越高越好*)表*)大json文档中的许多行--这并不能使索引扫描更快，但是由于innodb中的行blob存储，普通扫描比较慢，因此允许索引扫描变得更漂亮。