快捷導(dǎo)航

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

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

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

前言

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

然而在一些特定場(chǎng)景下，需要用Json字段里的某個(gè)鍵用來(lái)In查詢，并且需要保證不會(huì)造成慢查詢的前提下，用該鍵對(duì)整個(gè)查詢結(jié)果分組。因?yàn)檫@張表屬于是高頻儲(chǔ)存的表，數(shù)據(jù)相對(duì)龐大，下面先看看SQL查詢和放到業(yè)務(wù)里的查詢時(shí)間。

場(chǎng)景介紹

數(shù)據(jù)表主要存儲(chǔ)來(lái)自客戶端的請(qǐng)求信息，如客戶端標(biāo)識(shí)，接口名，渠道，來(lái)源，IP，入?yún)⒌鹊取６鴪?chǎng)景是需要對(duì)某個(gè)頁(yè)面下某個(gè)物品的請(qǐng)求總數(shù)和請(qǐng)求用戶數(shù)，也就是要將訪問數(shù)和訪問用戶數(shù)作為字段字段方式拼接到物品上。到這里可能很多人會(huì)說(shuō)，在指定頁(yè)埋點(diǎn)計(jì)數(shù)式更新物品兩個(gè)字段就可以了，干嘛這么麻煩去明細(xì)表里統(tǒng)計(jì)。

如此的做法，就真的是因?yàn)閼?，畢竟有時(shí)功能不是很重要就沒必要為此多創(chuàng)建一張與庫(kù)里有重疊性質(zhì)的表，下次去掉這部分時(shí)，多一張給后來(lái)者新增一份負(fù)擔(dān)，看著沒用的表又不敢刪。好了扯遠(yuǎn)了，下面就開始用SQL和業(yè)務(wù)代碼測(cè)試查詢效果和后面優(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')

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

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

優(yōu)化方法

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

以下用Navicat for MySQL為例，新建字段，勾選 “虛擬”，虛擬類型 “VIRTUAL”, 表達(dá)式 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. 設(shè)置索引

進(jìn)入設(shè)置，像添加普通字段的方式將item_id設(shè)置為普通索引。

4. 優(yōu)化查詢結(jié)果

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

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

PHP代碼

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

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. 明細(xì)(僅作參考)

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)化的詳細(xì)內(nèi)容，更多關(guān)于MySQL Json字段IN查詢分組的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: