mysql JSON Notes(json列索引)使用验证

xyc1211

已于 2022-02-10 14:16:17 修改

阅读量1.3k

点赞数

分类专栏： DB 文章标签： json mysql 数据库

于 2022-01-28 18:24:05 首次发布

本文链接：https://blog.csdn.net/xyc1211/article/details/122734958

版权

DB 专栏收录该内容

20 篇文章 0 订阅

订阅专栏

文章目录

简介
结论
测试过程：

简介

之前对json列创建索引的方式是基于json列创建虚拟列，然后对虚拟列创建索引。
现在 mysql 8.0.21 开始支持 对json列添加 JSON_VALUE索引
对索引使用情况进行测试如下

官方文档
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-21.html#mysqld-8-0-21-json

结论

只有查询语句的JSON_VALUE 与索引的JSON_VALUE完全相同，且查询的值符合JSON_VALUE的[RETURNING type]，索引才生效

测试过程：

json列创建JSON_VALUE索引

JSON_VALUE(json_doc, path [RETURNING type] [on_empty] [on_error])

建表，加JSON_VALUE索引

CREATE TABLE inventory(
    items JSON,
    INDEX i1 ( (JSON_VALUE(items, '$.name' RETURNING CHAR(50))) ),
    INDEX i2 ( (JSON_VALUE(items, '$.price' RETURNING DECIMAL(5,2))) ),
    INDEX i3 ( (JSON_VALUE(items, '$.quantity' RETURNING UNSIGNED)) )
);

-- DDL
CREATE TABLE `inventory` (
  `items` json DEFAULT NULL,
  `id` int DEFAULT NULL,
  KEY `i1` ((json_value(`items`, _utf8mb4'$.name' returning char(50)))),
  KEY `i2` ((json_value(`items`, _utf8mb4'$.price' returning decimal(5, 2)))),
  KEY `i3` ((json_value(`items`, _utf8mb4'$.quantity' returning unsigned))),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

不指定[RETURNING type] 则默认为 char(512)

ALTER TABLE inventory ADD INDEX i6 ( (JSON_VALUE(items, '$.name')) );

---DDL
KEY `i6` ((json_value(`items`, _utf8mb4'$.name' returning char(512))))

测试sql

select * from table where json_value(columns, path,retruning ) = value

json_value(columns, path,retrun )

1. json_value完全相同

EXPLAIN SELECT	* FROM	inventory WHERE
	JSON_VALUE ( items, '$.name' RETURNING CHAR ( 50 ) ) = "hat";

命中i1
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| :— | :—: | —: |
| 1 | SIMPLE | inventory | ref | i1 | i1 | 203 | const | 3 | 100.00|

2. path不同

EXPLAIN SELECT	* FROM	inventory WHERE
	JSON_VALUE ( items, '$.test'  ) = "111";

未命中
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| :— | :—: | —: |
|1 |SIMPLE |inventory| |ALL | | | | |5| 100.00| Using where|

3. 不指定[RETURNING type]

EXPLAIN SELECT	* FROM	inventory WHERE
	JSON_VALUE ( items, '$.name' ) = "hat";

命中i6 ，相当于returning char(512)
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| :— | :—: | —: |
| 1 | SIMPLE | inventory | ref | i6 | i6 | 2051 | const | 3 | 100.00|

EXPLAIN SELECT * FROM inventory
    WHERE JSON_VALUE(items, '$.price') <= 100.01;

未命中i2

4. RETURNING不同

---test1
EXPLAIN SELECT	* FROM	inventory WHERE
	JSON_VALUE ( items, '$.name' RETURNING CHAR ( 100 ) ) = "hat";
	
---test2
EXPLAIN SELECT	* FROM	inventory WHERE
	JSON_VALUE ( items, '$.name' RETURNING unsigned) = "hat";

都未命中
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| :— | :—: | —: |
|1 |SIMPLE |inventory| |ALL | | | | |5| 100.00| Using where|

value

retruning类型为DECIMAL(5,2)

EXPLAIN SELECT * FROM inventory
    WHERE JSON_VALUE(items, '$.price' RETURNING DECIMAL(5,2)) <= "100.01";
-- value为字符串，未命中

EXPLAIN SELECT * FROM inventory
    WHERE JSON_VALUE(items, '$.price' RETURNING DECIMAL(5,2)) <= 100.01;
-- value为正小数，命中

EXPLAIN SELECT * FROM inventory
    WHERE JSON_VALUE ( items, '$.price' returning DECIMAL ( 5, 2 ) ) > -50.0;
-- value为负小数，命中

EXPLAIN SELECT * FROM inventory
    WHERE JSON_VALUE(items, '$.price' RETURNING DECIMAL(5,2)) <= 100;
-- value为正整数，未命中

EXPLAIN SELECT * FROM inventory
  WHERE JSON_VALUE ( items, '$.price' returning DECIMAL ( 5, 2 ) ) > -50;
-- value为负整数，未命中

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| :— | :—: | —: |
|1 |SIMPLE| inventory| |range| i2| i2| 4| |3| 100.00 |Using where|

retruning类型为unsigned

EXPLAIN SELECT * FROM inventory
	WHERE JSON_VALUE ( items, '$.quantity' returning UNSIGNED ) > "18" ;
-- 字符，未命中

EXPLAIN SELECT * FROM inventory
	WHERE JSON_VALUE ( items, '$.quantity' returning UNSIGNED ) > 18.0 ;
-- 小数，未命中

EXPLAIN SELECT * FROM inventory
	WHERE JSON_VALUE ( items, '$.quantity' returning UNSIGNED ) > 18 ;
-- 正整数，命中

EXPLAIN SELECT * FROM inventory
	WHERE JSON_VALUE ( items, '$.quantity' returning UNSIGNED ) > -18 ;
-- 负整数，为命中

多索引匹配

EXPLAIN SELECT * FROM inventory
	JSON_VALUE ( items, '$.name' RETURNING CHAR ( 50 ) ) = "hat" 
	AND JSON_VALUE ( items, '$.price' returning DECIMAL ( 5, 2 ) ) < 50.0 
	AND JSON_VALUE ( items, '$.quantity' returning UNSIGNED ) < 18;

都能匹配到，只有一个索引生效
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| :— | :—: | —: |
|1 |SIMPLE |inventory| |ref| i1,i2,i3| i1| 203| const| 3| 24.00| Using where|

mybatis-plus 使用测试

代码
https://github.com/xu1211/mysql-jsonNotes

在where条件中使用JSON_VALUE索引条件可以生效

遇到的问题

where里的sql参数要符合JSON_VALUE的RETURNING，字符，无符号数，小数等格式
代码、插件中如果用到sqlparser sql解析器，可能会没有适配最新sql语法，不识别JSON_VALUE里的RETURNING语法 而报错

例： BlockAttackSqlParser解析器不识别sql报错。

### Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, please exclude the tableName or statementId.

 Error SQL: SELECT  id,items  FROM inventory 
 WHERE (JSON_VALUE ( items, '$.name' RETURNING CHAR ( 50 ) ) = ? AND JSON_VALUE(items, '$.price' RETURNING DECIMAL(5,2)) < ?)

	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:92)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)
	at com.sun.proxy.$Proxy70.selectList(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:223)
	at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForMany(MybatisMapperMethod.java:177)
	at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:78)
	at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:96)
	at com.sun.proxy.$Proxy75.selectList(Unknown Source)
	at com.example.jsonnode.test.selectONE(test.java:47)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:59)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:56)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
	at org.springframework.test.context.junit4.statements.RunBeforeTestExecutionCallbacks.evaluate(RunBeforeTestExecutionCallbacks.java:74)
	at org.springframework.test.context.junit4.statements.RunAfterTestExecutionCallbacks.evaluate(RunAfterTestExecutionCallbacks.java:84)
	at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
	at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
	at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:366)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:251)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:97)
	at org.junit.runners.ParentRunner$4.run(ParentRunner.java:331)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:79)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:329)
	at org.junit.runners.ParentRunner.access$100(ParentRunner.java:66)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:293)
	at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
	at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
	at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:413)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:190)
	at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
	at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69)
	at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:33)
	at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:235)
	at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:54)
Caused by: org.apache.ibatis.exceptions.PersistenceException: