乐趣区

关于后端:MySQL的Json类型字段IN查询分组和优化方法

jiezi

前言

MySQL 从 5.7 的版本开始反对 Json 后，我时常在设计表格时习惯性地增加一个 Json 类型字段，用做列的冗余。毕竟 Json 的非结构性，存储数据更灵便，比方接口申请记录用于存储申请参数，因为每个接口入参不统一，也有不传和空传的等等。

然而在一些特定场景下，须要用 Json 字段里的某个键用来 In 查问，并且须要保障不会造成慢查问的前提下，用该键对整个查问后果分组。因为这张表属于是高频贮存的表，数据绝对宏大，上面先看看 SQL 查问和放到业务里的查问工夫。

场景介绍

数据表次要存储来自客户端的申请信息，如客户端标识，接口名，渠道，起源，IP，入参等等。而场景是须要对某个页面下某个物品的申请总数和申请用户数，也就是要将拜访数和拜访用户数作为字段字段形式拼接到物品上。到这里可能很多人会说，在指定页埋点计数式更新物品两个字段就能够了，干嘛这么麻烦去明细表里统计。

如此的做法，就真的是因为懒，毕竟有时性能不是很重要就没必要为此多创立一张与库里有重叠性质的表，下次去掉这部分时，多一张给后来者新增一份累赘，看着没用的表又不敢删。好了扯远了，上面就开始用 SQL 和业务代码测试查问成果和前面优化办法吧。

SQL 查问 

SELECT json_extract(params,'$.item_id') as item_id, count(id), page_name, params, COUNT(DISTINCT cookie_md5) FROM `temp_record` WHERE  `page_name` IN ('api/GoodsItem/read','api/GoodsItem/readnew','api/GoodsItem/details')  AND (params->'$.item_id' in (40349,40348,40347,40346,40345,40342,40341,40340,40334,40333,40332,40331,40330,40328,40327,40326,40325,40324,40323,40322,40321,40320,40319,40318,40317,40316,40315,40314,40313,40312,40311,40310,40309,40308,40307,40306,40305,40304,40303,40302,40298,40297,40296,40295,40294,40293,40292,40291,40290,40289) )
GROUP BY (params->'$.item_id')

以后数据量不多的状况下，查问工夫 0.56 秒，针对条件我先对其中一个字段增加了 NORMAL 类型索引后，查问工夫在 0.07 和 0.19 间跳动。尽管速度晋升了一点，然而这里还有一个要害的查问，就是 Json 里的 item_id 的键，既作为条件又作为分组参。

然而索引只能应用字段，Json 字段里的键是不可能加进去的。尽管然而有一种曲线设置的形式，就是提取 Json 里的 item_id 为一个虚构字段，而后将该虚构字段设置为索引，于是就开始操作了。

优化办法

1. 图形创立虚构字段

以下用 Navicat for MySQL 为例，新建字段，勾选“虚构”，虚构类型“VIRTUAL”, 表达式 cast(json_extract(params,’$.item_id’) as signed)，也就是从 Json 提取“item_id”。

2. 命令创立虚构字段 

ALTER TABLE `temp_record`
    ADD COLUMN `item_id` int(11) GENERATED ALWAYS AS (cast(json_extract(`params`,'$.item_id') as signed));

3. 设置索引

进入设置，像增加一般字段的形式将 item_id 设置为一般索引。

4. 优化查问后果 

SELECT item_id, count(id), page_name, params, COUNT(DISTINCT cookie_md5) FROM `temp_record` WHERE  `page_name` IN ('api/GoodsItem/read','api/GoodsItem/readnew','api/GoodsItem/details')  AND (item_id in (40349,40348,40347,40346,40345,40342,40341,40340,40334,40333,40332,40331,40330,40328,40327,40326,40325,40324,40323,40322,40321,40320,40319,40318,40317,40316,40315,40314,40313,40312,40311,40310,40309,40308,40307,40306,40305,40304,40303,40302,40298,40297,40296,40295,40294,40293,40292,40291,40290,40289) )
GROUP BY (params->'$.item_id')

批改后，查问工夫稳固在 0.05 秒高低一点，能够说相较之前是快了 10 倍，分组中其实也是能够改成 item，然而数据里有字符串的 item_id 索引为了兼容这种类型，分组还是用的 JSON 取值形式，速度影响不大。

PHP 代码

1. 统计 (仅作参考)

public static function clickCount($goodsItemIds = [])
{
    $pageName = [
        'api/GoodsItem/read',
        'api/GoodsItem/readnew',
        'api/GoodsItem/details'
    ];
    $goodsItemIds = implode(",", $goodsItemIds);

    $where[] = ['page_name', 'in', $pageName];
    //$where[] = ['params->item_id', 'in', $goodsItemIds];

    $data = Db::name('temp_record')->field("item_id,count(id) as pv, count(DISTINCT cookie_md5) as uv")
        ->where($where)->whereRaw("params->'$.item_id'in ($goodsItemIds)")->group("params->item_id")
        ->select();

    $data && $data = array_column($data, null, 'item_id');

    return $data;
}

2. 明细 (仅作参考)

public static function clickRecord($itemId = 0, $page = 1, $size = 20)
{$result['count'] = 0;
    $result['list'] = [];

    $pageName = [
        'api/GoodsItem/read',
        'api/GoodsItem/readnew',
        'api/GoodsItem/details'
    ];

    $where[] = ['page_name', 'in', $pageName];

    $field = ["from_unixtime(day_time,'%Y-%m-%d') as day_time, count(id) as clicks,
    count(DISTINCT cookie_md5) as user_clicks"];

    $result['list'] = Db::name('temp_record')
        ->field($field)
        ->where($where)->whereRaw("params->'$.item_id'= $itemId")
        ->group("day_time")
        ->page($page, $size)
        ->order('day_time desc')
        ->select();

    $result['count'] = Db::name('temp_record')->field($field)->where($where)
        ->whereRaw("params->'$.item_id'= $itemId")->group("day_time")->count();

    return $result;
}
退出移动版