【Mariadb/Mysql】利用JSON函数巧妙实现行列置换提高系统效率

在现实生活中，往往我们需要创建一些One to Many或Many to Many的关系数据表。例如：按照Configuration Solution的设计，产品与产品参数表的关系，往往一种产品对应多个参数。

如上PRODUCT 和 PARAMETER 表的数据显示，每个PRODUCT 都对应两个参数startDate和endDate。 根据这样的数据结构，一般情况下我们会把我们的实体Java Bean 设计成一下情况：每个ProductEntity下都对应一组 productParameterEntityList。

import lombok.Builder;
import lombok.Data;

import java.util.List;

@Data
@Builder
public class ProductEntity {
    private int id;
    private String name;
    private String description;
    private List<ProductParameterEntity> productParameterEntityList;
}

package test;

import lombok.Builder;
import lombok.Data;

@Builder
@Data
public class ProductParameterEntity {
    private int id;
    private int productId;
    private String paraName;
    private String paraValue;
}

根据以上的Enity设计，每次我们需要获取产品参数时，

• 系统的前端：需要遍历检索productParameterEntityList，然后根据需要paramName来获取相依的paraValue.
• 系统后端：系统需要根据产品返回的记录数，分别调用ProductParameter的查询来封装productParameterEntityList. 

很明显，这种Entity设计有一个弊端，就是在数据量比较少记录的情况下，系统一切正常。但是如果数据量比较大，比如几万，几十万，甚至几百万时，在没有使用任何额外缓存时，每次我们想要获取产品的资料，系统都需要花费大量的时间来获取产品参数，然后封装成所需要的列表productParameterEntityList。这样既增加了系统查询时间，也增加了系统后端与数据库的读取次数。

那有没有在不使用任何缓存时，解决这种问题的方法呢？

答案是有的。撇除使用外挂的缓存，本人至少可以提供两种解决方案。

• 第一种：现有的数据库设计不变，改动后端系统Entity的机构来达到目的
• 第二种：在这里，先卖个关子，详情下回分解。。。

先说第一种：解决思路就是希望能够通过一次数据查询，封装我们需要的信息，一次提出所有的产品信息。

一般情况下，我们的产品都会有相应固定的配置。这样的我们就能够把ProductEntity设计成相应固定的结构，然后封装所有的产品信息，就是把产品参数当作ProductEntity的属性。如下：

import lombok.Builder;
import lombok.Data;


@Data
@Builder
public class ProductEntity {
    private int id;
    private String name;
    private String description;
    private String startDate;
    private String endDate
}

更改过后的ProductEntity相对前端来说，

• 有了固定的接口
• 快速访问每个产品的属性，省了对产品参数的遍历

接下来我们需要对后端查询进行改进。

想要获取产品参数，我们少不了把PRODUCT和PARAMETER两个表链接起来。我们先来看看如果把他们连在一起会发生什么：

select * from PRODUCT pd, PARAMETER param where pd.id =param.product_id;

+----+----------+-------------------------+----+------------+---------------+-------------+
| id | name     | description             | id | product_id | param_name    | param_value |
+----+----------+-------------------------+----+------------+---------------+-------------+
|  1 | Product1 | Description of Product1 |  1 |          1 | startDate     | 2022-03-01  |
|  1 | Product1 | Description of Product1 |  2 |          1 | endDate       | 2023-03-31  |
|  2 | Product2 | Description of Product2 |  3 |          2 | startDate     | 2022-03-01  |
|  2 | Product2 | Description of Product2 |  4 |          2 | endDate       | 2023-03-31  |
|  3 | Product2 | Description of Product2 |  5 |          3 | startDate     | 2022-03-01  |
|  3 | Product2 | Description of Product2 |  6 |          3 | endDate       | 2023-03-31  |
|  1 | Product1 | Description of Product1 |  7 |          1 | Company       | DEMO        |
|  1 | Product1 | Description of Product1 |  8 |          1 | Business Unit | IT          |
+----+----------+-------------------------+----+------------+---------------+-------------+

我们发现，每个product都返回多个记录，而已startDate, endDate的name, value是分开存取。这样的返回记录，并不能够直接封装成改进后的ProductEntity.

 有大神们可能会出主意，通过子查询，我们能够获得我们想要的， 例如：

select pd.*, 
(select param_value from PARAMETER where product_id = pd.id and param_name='startDate') as startDate, 
(select param_value from PARAMETER where product_id = pd.id and param_name='endDate') as endDate 
from PRODUCT pd

+----+----------+-------------------------+------------+------------+
| id | name     | description             | startDate  | endDate    |
+----+----------+-------------------------+------------+------------+
|  1 | Product1 | Description of Product1 | 2022-03-01 | 2023-03-31 |
|  2 | Product2 | Description of Product2 | 2022-03-01 | 2023-03-31 |
|  3 | Product2 | Description of Product2 | 2022-03-01 | 2023-03-31 |
+----+----------+-------------------------+------------+------------+

 以上子查询确实是一种解决办法，而且其返回结果可以映射成我们想要的ProductEntity。但是这种子查询方式，对数据库其实是额外的开销。视参数个数而定，如果参数几十个，我们估计要定义几十个子查询，如此一来，每次数据库获取param_value，数据库都需要额外遍历一下PARAMETER table. 对数据确确实实是不少的开销。

下面我给大家介绍另一种更快捷的方法。

首先我们把PRODUCT都Group起来，然后利用Mysql/Mariadb的group_concat函数，这样我们得到一下的数据：

select pd.*, group_concat(param.param_name, ':',param.param_value) as params 
from PRODUCT pd, PARAMETER param 
where param.product_id=pd.id group by pd.id, pd.name, pd.description;

+----+----------+-------------------------+-----------------------------------------+
| id | name     | description             | params                                  |
+----+----------+-------------------------+-----------------------------------------+
|  1 | Product1 | Description of Product1 | startDate:2022-03-01,endDate:2023-03-31 |
|  2 | Product2 | Description of Product2 | startDate:2022-03-01,endDate:2023-03-31 |
|  3 | Product2 | Description of Product2 | startDate:2022-03-01,endDate:2023-03-31 |
+----+----------+-------------------------+-----------------------------------------+

通过观察，我们发现group_concat帮我们把所有的parameter提取出来，而且存放在一个params的列下。这时候，如果我们对product1加入额外的parameter，我们也不需要更改我们的SQL；如下：

加入新的parameter后：

select * from PARAMETER;

+----+------------+---------------+-------------+
| id | product_id | param_name    | param_value |
+----+------------+---------------+-------------+
|  1 |          1 | startDate     | 2022-03-01  |
|  2 |          1 | endDate       | 2023-03-31  |
|  3 |          2 | startDate     | 2022-03-01  |
|  4 |          2 | endDate       | 2023-03-31  |
|  5 |          3 | startDate     | 2022-03-01  |
|  6 |          3 | endDate       | 2023-03-31  |
|  7 |          1 | Company       | DEMO        |
|  8 |          1 | Business Unit | IT          |
+----+------------+---------------+-------------+

 在我们的SQL基础上查询：我们看到Company和Business Unit已经自动显示出来。

select pd.*, group_concat(param.param_name, ':',param.param_value) as params 
from PRODUCT pd, PARAMETER param 
where param.product_id=pd.id group by pd.id, pd.name, pd.description;
+----+----------+-------------------------+-----------------------------------------------------------------------+
| id | name     | description             | params                                                                |
+----+----------+-------------------------+-----------------------------------------------------------------------+
|  1 | Product1 | Description of Product1 | startDate:2022-03-01,endDate:2023-03-31,Company:DEMO,Business Unit:IT |
|  2 | Product2 | Description of Product2 | startDate:2022-03-01,endDate:2023-03-31                               |
|  3 | Product2 | Description of Product2 | startDate:2022-03-01,endDate:2023-03-31                               |
+----+----------+-------------------------+-----------------------------------------------------------------------+

但是，仔细观察以上结果，我们发现params列其实就是一个string，它还不能直接Map到我们想要的Java Fields。进一步优化：首先我们需要吧params string转换成JSON

select pd.*, concat('{', group_concat( JSON_QUOTE(param.param_name), 
':',JSON_QUOTE(param.param_value)),'}') as params_json
from PRODUCT pd, PARAMETER param where param.product_id=pd.id group by pd.id, pd.name, pd.description;

注意：如果我们的参数param_value只是basic type，比如text, 我们可以使用JSON_OBJECTAGG函数一次过生成我们的param_json 

select pd.*, JSON_OBJECTAGG(param.param_name,param.param_value) as params_json
from PRODUCT pd, PARAMETER param where param.product_id=pd.id group by pd.id, pd.name, pd.description;

两种查询都能够获得一下结果： 

+----+----------+-------------------------+-----------------------------------------------------------------------------------------+
| id | name     | description             | params_json                                                                             |
+----+----------+-------------------------+-----------------------------------------------------------------------------------------+
|  1 | Product1 | Description of Product1 | {"startDate":"2022-03-01","endDate":"2023-03-31","Company":"DEMO","Business Unit":"IT"} |
|  2 | Product2 | Description of Product2 | {"startDate":"2022-03-01","endDate":"2023-03-31"}                                       |
|  3 | Product2 | Description of Product2 | {"startDate":"2022-03-01","endDate":"2023-03-31"}                                       |
+----+----------+-------------------------+-----------------------------------------------------------------------------------------+

通过函数“concat('{', group_concat( JSON_QUOTE(param.param_name), ':',JSON_QUOTE(param.param_value)),'}')” 我们成功变成一个JSON stirng

接下来我们需要拆分我们的JSON，使其properties变成我们想要的列：

我们可以使用JSON_TABLE 函数或JSON_VALUE来获得我们想要的结果：

JSON_TABLE 的方法：

select result.id, result.name, result.description, jtable.* from (
select pd.*, concat('{', group_concat( JSON_QUOTE(param.param_name), ':',JSON_QUOTE(param.param_value)),'}') as params_json
from PRODUCT pd, PARAMETER param where param.product_id=pd.id group by pd.id, pd.name, pd.description
) as result, json_table(result.params_json, '$' columns(
startDate varchar(255) path '$.startDate',
  endDate varchar(255) path '$.endDate',
  Company varchar(255) path '$.Company',
  BusinessUnit varchar(255) path '$.Business Unit'

)) as jtable

JSON_VALUE 的方法：

JSON_VALUE，JSON_QUERY，JSON_EXTRACT 各自的区别请访问各个数据库的文档。

select result.id, result.name, result.description, 
json_value(result.params_json, '$.startDate') as startDate,
json_value(result.params_json, '$.endDate') as endDate,
json_value(result.params_json, '$.Company') as Company,
json_value(result.params_json, '$."Business Unit"') as BusinessUnit
from (
select pd.*, concat('{', group_concat( JSON_QUOTE(param.param_name), ':',JSON_QUOTE(param.param_value)),'}') as params_json
from PRODUCT pd, PARAMETER param where param.product_id=pd.id group by pd.id, pd.name, pd.description
) as result

以上JSON_TABLE，JSON_VALUE SQL我们都可以获得一下的结果：

+----+----------+-------------------------+------------+------------+---------+--------------+
| id | name     | description             | startDate  | endDate    | Company | BusinessUnit |
+----+----------+-------------------------+------------+------------+---------+--------------+
|  1 | Product1 | Description of Product1 | 2022-03-01 | 2023-03-31 | DEMO    | IT           |
|  2 | Product2 | Description of Product2 | 2022-03-01 | 2023-03-31 | NULL    | NULL         |
|  3 | Product2 | Description of Product2 | 2022-03-01 | 2023-03-31 | NULL    | NULL         |
+----+----------+-------------------------+------------+------------+---------+--------------+

至此，我们可以采用java的Mybatis，Hibernate Native SQL获得相应的JAVA Object， 而且我们只需要查询一遍就能获得我们想要的结果。对于几十万，甚至几百万的数据，我们可以轻松应付。

下回我给大家介绍另一种更快速解决以上问题的方法。