如果我使用或不使用索引，为什么在MySQL查询中得到不同的命中次数？

Question

我是MySQL的新手，我不明白，为什么如果我在JSON列上使用索引，结果集与没有索引不同。

我有张简单的桌子：

CREATE TABLE jsontest (
jsondata JSON
);

表中填充了50000个json，其中一个在json中存档：

 "allowedNfTypes": ["aaa", "bbb", "ccc"]}

在某些情况下，这个字段的数组中有1或2或3个值(但是大约有10个字符串选项--比方说从"aaa“到"iii")。在某些情况下，这一档案根本不存在。

如果我执行：

mysql> SELECT * FROM jsontest WHERE "AMF" MEMBER OF(jsondata->'$.allowedNfTypes')

我得到：

10045 rows in set (0.21 sec)

然后我创建了一个索引：

CREATE INDEX allowedNfTypes_index ON jsontest((CAST(jsondata->'$.allowedNfTypes' AS CHAR(128) ARRAY)))

和相同的查询

SELECT * FROM jsontest WHERE "AMF" MEMBER OF(jsondata->'$.allowedNfTypes');

返回的点击次数要少得多：

1402 rows in set (0.03 sec) 

知道为什么吗？

Answer 1

我已经下载了你的数据文件。

CLI输出副本：

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE jsontest (
    -> jsondata JSON
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> LOAD DATA INFILE 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\test_data.json' INTO TABLE jsontest;
Query OK, 50000 rows affected (15.50 sec)
Records: 50000  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT COUNT(*) FROM jsontest WHERE "AMF" MEMBER OF(jsondata->'$.allowedNfTypes');
+----------+
| COUNT(*) |
+----------+
|    10045 |
+----------+
1 row in set (0.32 sec)

mysql> CREATE INDEX allowedNfTypes_index ON jsontest((CAST(jsondata->'$.allowedNfTypes' AS CHAR(128) ARRAY)));
Query OK, 0 rows affected (0.88 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT COUNT(*) FROM jsontest WHERE "AMF" MEMBER OF(jsondata->'$.allowedNfTypes');
+----------+
| COUNT(*) |
+----------+
|    10045 |
+----------+
1 row in set (0.28 sec)

还测试了原始查询(不是SELECT COUNT(*) FROM ..，而是SELECT * FROM ..) --结果是一样的。

这一问题没有转载。

在版本更新到实际8.0.28之前，将复制该问题。与索引一起选择的行数与- 1402相同。

在添加自动递增的主键ALTER TABLE jsontest ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY;后，使用索引选择的行数将更改为1448。

通过比较有索引和没有索引的行，我发现：

在jsondata->'$.allowedNfTypes'.

• For中有许多重复的

• 值--每个唯一的jsondata->'$.allowedNfTypes'值--只有创建了小于6800的行才由jsondata->'$.allowedNfTypes'与索引一起选择，所有具有重复值的其他行都不是jsondata->'$.allowedNfTypes'--具有id值高于N的行减少了查询在没有索引的情况下返回的行数，并且没有用索引来改变查询返回的行数(测试N= 40000、25000、10000、7500、7000、6800)。当N为6797或更少时，所选行数变为相等。

• I尝试添加已生成的列，该列使用aLTER TABLE jsontest ADD COLUMN allowedNfTypes JSON AS (CAST(jsondata->'$.allowedNfTypes' AS JSON));将此属性提取为JSON数组，通过该列创建多值索引，并使用该列对整个JSON进行索引。使用索引选择的行数将增加到1498。N的值为5097。然后，

• 添加了OP描述的索引(表包含两个索引--按原始列和生成的索引)--查询返回10045！我根据它- 10045删除生成的列和索引。我按原始列删除索引，然后重新创建它-再次创建1498.

有许多额外的实验，但他们的结果不那么有趣。

看上去是个虫子。可繁殖的窃听器。我认为您可以重复我的体验(和/或执行您自己的)，并向MySQL错误报告。