欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

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)⒌鹊取6鴪?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)文章!

相關(guān)文章

  • 數(shù)據(jù)庫(kù)sql語(yǔ)句優(yōu)化

    數(shù)據(jù)庫(kù)sql語(yǔ)句優(yōu)化

    今天小編就為大家分享一篇關(guān)于數(shù)據(jù)庫(kù)sql語(yǔ)句優(yōu)化,小編覺得內(nèi)容挺不錯(cuò)的,現(xiàn)在分享給大家,具有很好的參考價(jià)值,需要的朋友一起跟隨小編來(lái)看看吧
    2019-01-01
  • MySQL針對(duì)Discuz論壇程序的基本優(yōu)化教程

    MySQL針對(duì)Discuz論壇程序的基本優(yōu)化教程

    這篇文章主要介紹了MySQL針對(duì)Discuz論壇程序的基本優(yōu)化教程,包括在緩存和索引等方面的優(yōu)化方法,需要的朋友可以參考下
    2015-11-11
  • CentOS 7下使用RPM安裝mysql5.7.13

    CentOS 7下使用RPM安裝mysql5.7.13

    這篇文章主要為大家詳細(xì)介紹了CentOS 7下使用RPM安裝mysql5.7.13的相關(guān)資料,具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下
    2017-01-01
  • MySQL表內(nèi)連和外連的具體使用

    MySQL表內(nèi)連和外連的具體使用

    我們?cè)谑褂肕ySQL的時(shí)候,經(jīng)常涉及到內(nèi)連接和外連接的應(yīng)用,本文就來(lái)詳細(xì)的介紹一下MySQL表內(nèi)連和外連的具體使用,感興趣的可以了解一下
    2023-10-10
  • 用MyEclipse配置DataBase Explorer(圖示)

    用MyEclipse配置DataBase Explorer(圖示)

    本文介紹了,用MyEclipse配置DataBase Explorer的圖片示例。需要的朋友參考下
    2013-04-04
  • MySQL如何修改密碼過(guò)期時(shí)間

    MySQL如何修改密碼過(guò)期時(shí)間

    MySQL修改密碼過(guò)期時(shí)間的方法,包括修改my.ini文件、設(shè)置賬號(hào)過(guò)期時(shí)間、查看過(guò)期時(shí)間和賬號(hào)狀態(tài),并在修改后重啟服務(wù)和密碼
    2025-02-02
  • mysql增量備份及斷點(diǎn)恢復(fù)腳本實(shí)例

    mysql增量備份及斷點(diǎn)恢復(fù)腳本實(shí)例

    生產(chǎn)環(huán)境中在mysql中誤操作是非常正常的,所以就需要用到mysql的增量備份恢復(fù)。增量備份是我們經(jīng)常用到的,它可以指定某個(gè)誤操作的時(shí)間以及位置點(diǎn)進(jìn)行數(shù)據(jù)恢復(fù),更加準(zhǔn)確的恢復(fù)我們想要還原的數(shù)據(jù)。
    2018-09-09
  • MySQL串行化隔離級(jí)別(間隙鎖實(shí)現(xiàn))

    MySQL串行化隔離級(jí)別(間隙鎖實(shí)現(xiàn))

    本文主要介紹了MySQL串行化隔離級(jí)別(間隙鎖實(shí)現(xiàn)),文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧
    2022-06-06
  • mysql根據(jù)逗號(hào)將一行數(shù)據(jù)拆分成多行數(shù)據(jù)

    mysql根據(jù)逗號(hào)將一行數(shù)據(jù)拆分成多行數(shù)據(jù)

    本文主要介紹了mysql根據(jù)逗號(hào)將一行數(shù)據(jù)拆分成多行數(shù)據(jù),文中通過(guò)示例代碼介紹的非常詳細(xì),具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下
    2021-12-12
  • 深入理解MySQL數(shù)據(jù)類型的選擇優(yōu)化

    深入理解MySQL數(shù)據(jù)類型的選擇優(yōu)化

    這篇文章主要介紹了深入理解MySQL數(shù)據(jù)類型的選擇優(yōu)化,MySQL數(shù)據(jù)類型是定義列中可以存儲(chǔ)什么數(shù)據(jù)以及該數(shù)據(jù)實(shí)際怎樣存儲(chǔ)的基本規(guī)則,正確的選擇數(shù)據(jù)庫(kù)字段的字段類型對(duì)于數(shù)據(jù)庫(kù)性能有很大的影響
    2022-08-08

最新評(píng)論