MySQL Json類型字段IN查詢分組優(yōu)化
前言
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)化
今天小編就為大家分享一篇關(guān)于數(shù)據(jù)庫(kù)sql語(yǔ)句優(yōu)化,小編覺得內(nèi)容挺不錯(cuò)的,現(xiàn)在分享給大家,具有很好的參考價(jià)值,需要的朋友一起跟隨小編來(lái)看看吧2019-01-01MySQL針對(duì)Discuz論壇程序的基本優(yōu)化教程
這篇文章主要介紹了MySQL針對(duì)Discuz論壇程序的基本優(yōu)化教程,包括在緩存和索引等方面的優(yōu)化方法,需要的朋友可以參考下2015-11-11用MyEclipse配置DataBase Explorer(圖示)
本文介紹了,用MyEclipse配置DataBase Explorer的圖片示例。需要的朋友參考下2013-04-04mysql增量備份及斷點(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-09MySQL串行化隔離級(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-06mysql根據(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ù)類型是定義列中可以存儲(chǔ)什么數(shù)據(jù)以及該數(shù)據(jù)實(shí)際怎樣存儲(chǔ)的基本規(guī)則,正確的選擇數(shù)據(jù)庫(kù)字段的字段類型對(duì)于數(shù)據(jù)庫(kù)性能有很大的影響2022-08-08