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

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

 更新時間:2023年08月20日 10:59:52   作者:北橋蘇  
這篇文章主要為大家介紹了MySQL Json類型字段IN查詢分組優(yōu)化,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進(jìn)步,早日升職加薪

前言

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

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

場景介紹

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

如此的做法,就真的是因為懶,畢竟有時功能不是很重要就沒必要為此多創(chuàng)建一張與庫里有重疊性質(zhì)的表,下次去掉這部分時,多一張給后來者新增一份負(fù)擔(dān),看著沒用的表又不敢刪。好了扯遠(yuǎn)了,下面就開始用SQL和業(yè)務(wù)代碼測試查詢效果和后面優(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ù)量不多的情況下,查詢時間0.56秒,針對條件我先對其中一個字段添加了NORMAL類型索引后,查詢時間在0.07和0.19間跳動。雖然速度提升了一點,但是這里還有一個關(guān)鍵的查詢,就是Json里的item_id的鍵,既作為條件又作為分組參。

但是索引只能使用字段,Json字段里的鍵是不可能加進(jìn)去的。雖然但是有一種曲線設(shè)置的方式,就是提取Json里的item_id為一個虛擬字段,然后將該虛擬字段設(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')

修改后,查詢時間穩(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. 明細(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查詢分組的資料請關(guān)注腳本之家其它相關(guān)文章!

相關(guān)文章

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

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

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

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

    這篇文章主要介紹了MySQL針對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)資料,具有一定的參考價值,感興趣的小伙伴們可以參考一下
    2017-01-01
  • MySQL表內(nèi)連和外連的具體使用

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

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

    用MyEclipse配置DataBase Explorer(圖示)

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

    MySQL如何修改密碼過期時間

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

    mysql增量備份及斷點恢復(fù)腳本實例

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

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

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

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

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

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

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

最新評論