MySQL Forums
Forum List  »  Chinese

json结合生成列索引,查询结果不一致,是否正确?
Posted by: Chen WeiXin
Date: May 05, 2022 03:32AM

1.两种表现
1.1 这是能够查询到结果的SQL:
drop table jemp;
create table jemp(c json, g int generated always as (c->"$.id")) ;
create index idx1 on jemp(g);
insert into jemp(c) values('{"id":"1", "name":"Fred"}');
select c from jemp where c->'$.id' = 1;
mysql> explain select c from jemp where c->'$.id' = 1;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | jemp | NULL | ref | idx1 | idx1 | 5 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+----------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select `mysql`.`jemp`.`c` AS `c` from `mysql`.`jemp` where (`mysql`.`jemp`.`g` = 1) |
+-------+------+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

1.2 这是查询不到结果的SQL
drop table jemp;
create table jemp(c json, g int generated always as (c->"$.id")) ;
insert into jemp(c) values('{"id":"1", "name":"Fred"}');
select c from jemp where c->'$.id' = 1;
mysql> explain select c from jemp where c->'$.id' = 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | jemp | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select `mysql`.`jemp`.`c` AS `c` from `mysql`.`jemp` where (json_extract(`mysql`.`jemp`.`c`,'$.id') = 1) |
+-------+------+-------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

2.原因
在有生成列索引的时候,优化器将where条件后的语句识别成生成列,然后走生成列索引查询;在没有生成列索引的时候,将整个where条件下压过滤,由于c->'$.id'查询到的是json,json string和1比较,将1 cast成json int,json string 大于json int,被过滤掉。

3.疑问
同一条SQL语句,有索引和没有索引查询结果不一致,这合理吗?
如果用户开始没有建立生成列索引,开始都是查询不到,某一天建立了生成列索引,突然查询到了结果,那不是很大可能影响生产?

Options: ReplyQuote


Subject
Views
Written By
Posted
json结合生成列索引,查询结果不一致,是否正确?
274
May 05, 2022 03:32AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.