MySQL Json類型字段IN查詢分組優(yōu)化

更新時間：2023年08月20日 10:59:52 作者：北橋蘇

這篇文章主要為大家介紹了MySQL Json類型字段IN查詢分組優(yōu)化,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進步,早日升職加薪

前言

MySQL從5.7的版本開始支持Json后，我時常在設計表格時習慣性地添加一個Json類型字段，用做列的冗余。畢竟Json的非結構性，存儲數(shù)據(jù)更靈活，比如接口請求記錄用于存儲請求參數(shù)，因為每個接口入?yún)⒉灰恢?，也有不傳和空傳的等等?/p>

然而在一些特定場景下，需要用Json字段里的某個鍵用來In查詢，并且需要保證不會造成慢查詢的前提下，用該鍵對整個查詢結果分組。因為這張表屬于是高頻儲存的表，數(shù)據(jù)相對龐大，下面先看看SQL查詢和放到業(yè)務里的查詢時間。

場景介紹

數(shù)據(jù)表主要存儲來自客戶端的請求信息，如客戶端標識，接口名，渠道，來源，IP，入?yún)⒌鹊?。而場景是需要對某個頁面下某個物品的請求總數(shù)和請求用戶數(shù)，也就是要將訪問數(shù)和訪問用戶數(shù)作為字段字段方式拼接到物品上。到這里可能很多人會說，在指定頁埋點計數(shù)式更新物品兩個字段就可以了，干嘛這么麻煩去明細表里統(tǒng)計。

如此的做法，就真的是因為懶，畢竟有時功能不是很重要就沒必要為此多創(chuàng)建一張與庫里有重疊性質(zhì)的表，下次去掉這部分時，多一張給后來者新增一份負擔，看著沒用的表又不敢刪。好了扯遠了，下面就開始用SQL和業(yè)務代碼測試查詢效果和后面優(yōu)化方法吧。

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')

當前數(shù)據(jù)量不多的情況下，查詢時間0.56秒，針對條件我先對其中一個字段添加了NORMAL類型索引后，查詢時間在0.07和0.19間跳動。雖然速度提升了一點，但是這里還有一個關鍵的查詢，就是Json里的item_id的鍵，既作為條件又作為分組參。

但是索引只能使用字段，Json字段里的鍵是不可能加進去的。雖然但是有一種曲線設置的方式，就是提取Json里的item_id為一個虛擬字段，然后將該虛擬字段設置為索引，于是就開始操作了。

優(yōu)化方法

1. 圖形創(chuàng)建虛擬字段

以下用Navicat for MySQL為例，新建字段，勾選 “虛擬”，虛擬類型 “VIRTUAL”, 表達式 cast(json_extract(params,'$.item_id') as signed)，也就是從Json提取“item_id”。

2. 命令創(chuàng)建虛擬字段

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. 優(yōu)化查詢結果

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')

修改后，查詢時間穩(wěn)定在0.05秒上下一點，可以說相較之前是快了10倍，分組中其實也是可以改成item，但是數(shù)據(jù)里有字符串的item_id索引為了兼容這種類型，分組還是用的JSON取值方式，速度影響不大。

PHP代碼

1. 統(tǒng)計(僅作參考)

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;
}

以上就是MySQL Json類型字段IN查詢分組優(yōu)化的詳細內(nèi)容，更多關于MySQL Json字段IN查詢分組的資料請關注腳本之家其它相關文章！

您可能感興趣的文章: