尤川豪   ·  5月前
Exp. 1,630  ·  212 貼文  ·  145 留言

MySQL 傳統欄位、json 欄位,與 EAV (Entity–attribute–value) model 設計的效能比較實驗

RDBMS 在處理對象的資料結構不確定的時候 會遇上問題:table schema 開不出來

傳統上直覺的作法有兩種 一是以 text 類型硬存 json

但是這麼做很難寫 sql query,除非寫一堆 regular expression

二是所謂 EAV model,例如 Wordpress 資料庫中的 user meta 資料表、post meta 資料表

但是這麼做在 query 時需要寫很多 join,速度很慢 很多 wordpress 擴充後的應用會慢也是因為這個原因

MySQL 5.7 之後支援 json 類型,下 sql 也方便多了

那麼唯一的問題就剩下... 效能了... 會不會很慢呢?有必要實驗確認一下

實驗:1千筆資料,無 index 的效能比較

跑五次分別紀錄

name 1 2 3 4 5
以傳統 SQL 方式存 21.36ms 20.56ms 27.6ms 14.94ms 12.21ms
以 EAV model 存 130ms 130.57ms 130.43ms 126.85ms 120.3ms
以 json 存 20ms 32.22ms 25.62ms 18.42ms 27.75ms

實驗:5千筆資料,無 index 的效能比較

name 1 2 3 4 5
以傳統 SQL 方式存 22.5ms 70.98ms 31.28ms 27.2ms 58.5ms
以 EAV model 存 2.56s 2.59s 2.65s 2.58s 2.62s
以 json 存 33.14ms 31.67ms 65.67ms 37.77ms 26.03ms

實驗:1萬筆資料,有無 index 的效能比較

無 index 時

name 1 2 3 4 5
以傳統 SQL 方式存 56.8ms 52.86ms 39.24ms 38.27ms 34.1ms
以 EAV model 存 10.19s 10.29s 10.26s 10.39s 10.25s
以 json 存 45.66ms 90.5ms 41.56ms 49.62ms 57.29ms

有 index 時

name 1 2 3 4 5
以傳統 SQL 方式存
以 EAV model 存
以 json 存

實驗中使用的 Query 如下

        DB::table('entities')
            ->where('age', '<', $age)
            ->where('height', '<', $height)
            ->where('weight', '>', $weight)
            ->get();

        DB::table('entities')
            ->join('meta_cols as m1', function($join) use ($age) {
                $join->on('m1.entity_id', '=', 'entities.id');
                $join->where('m1.meta_key', '=', 'age');
                $join->where('m1.meta_value', '<', $age);
             })
             ->join('meta_cols as m2', function($join) use ($height) {
                 $join->on('m2.entity_id', '=', 'entities.id');
                 $join->where('m2.meta_key', '=', 'height');
                 $join->where('m2.meta_value', '<', $height);
             })
             ->join('meta_cols as m3', function($join) use ($weight) {
                $join->on('m3.entity_id', '=', 'entities.id');
                $join->where('m3.meta_key', '=', 'weight');
                $join->where('m3.meta_value', '>', $weight);
            })
            ->get();

        DB::table('entities')
            ->where('json_info->age', '<', $age)
            ->where('json_info->height', '<', $height)
            ->where('json_info->weight', '>', $weight)
            ->get();

source code: https://github.com/howtomakeaturn/database-experiment

  分享   共 557 次點閱
共有 1 則留言
尤川豪   ·  5月前
Exp. 1,630  ·  212 貼文  ·  145 留言

實驗之後發現 ... json 類型的欄位,query 起來速度還不錯

不過還沒比較過加上 index 之後的影響

待完成實驗之後再補上

 
您的留言
尤川豪
Exp. 1,630  ·  212 貼文  ·  145 留言

關於作者

Devs.tw 作者,喜歡分享&建造新東西的工程師。

歡迎在 Facebook 追蹤我!不定期分享有趣技術文章!

  查看個人檔案