PostgreSQL 9.4 中使用 jsonb

gameFu發表於2015-06-16
SQLJSON

轉載翻譯自http://nandovieira.com/using-postgresql-and-jsonb-with-ruby-on-rails

PostgreSQL 9.4 引入了jsonb，一個新的列型別用於儲存文件到你的關聯式資料庫中。jsonb和json在更高的層面上看起來幾乎是一樣的，但在儲存實現上是不同的。

使用jsonb的優勢在於你可以輕易的整合關係型資料和非關係型資料，在效能方面，可以比大多數類似於MongoDB這樣的非關聯式資料庫更好

理解json和jsonb之間的不同

因此，兩種列型別之間的區別是什麼？當我們比較寫入資料速度時，由於資料儲存的方式的原因，jsonb會比json稍微的慢一點。

  • json儲存完整複製過來的文字輸入，必須一遍又一遍的解析在你呼叫任何函式的時候。它不支援索引，但你可以為查詢建立表示式索引。

  • jsonb儲存的二進位制格式，避免了重新解析資料結構。它支援索引，這意味著你可以不使用指定的索引就能查詢任何路徑。

其他的不同包括，json列會每次都解析儲存的值，這意味著鍵的順序要和輸入的時候一樣。但jsonb不同，以二進位制格式儲存且不保證鍵的順序。因此，如果你有軟體需要依賴鍵的順序，jsonb可能不是你的應用的最佳選擇。

讓我們執行一個簡單的基準測試。在這個例子中，我使用下面這樣一個json資料結構：

json{
  "twitter": "johndoe1",
  "github": "johndoe1",
  "bio": "Lorem ipsum dolor sit amet, consectetur adipisicing elit. Labore impedit 
          aliquam sapiente dolore magni aliquid ipsa ad, enim, esse ut reprehenderit 
          quaerat deleniti fugit eaque. Vero eligendi voluptatibus atque, asperiores.",
  "blog": "http://johndoe1.example.com",
  "interests": [
    "music",
    "movies",
    "programming"
  ],
  "age": 42,
  "newsletter": true
}

插入30000條完全一樣的記錄，我相信jsonb在插入複雜結構時會慢一些。

Rehearsal ------------------------------------------------
insert jsonb   2.690000   0.590000   3.280000 ( >12.572343)
insert json    2.690000   0.590000   3.280000 ( 12.766534)
--------------------------------------- total: 6.560000sec

-----------------------------------------user     system      total        real
insert jsonb   2.680000   0.590000   3.270000 ( 13.206602)
insert json    2.650000   0.580000   3.230000 ( 12.577138)

真正的差距在查詢json/jsonb列的時候。首先讓我們看看這張表和索引。

sql
CREATE TABLE users (
  id serial not null,
  settings jsonb not null default `{}`,
  preferences json not null default `{}`
);

CREATE INDEX settings_index ON users USING gin (settings);
CREATE INDEX twitter_settings_index ON users ((settings->>`github`));
CREATE INDEX preferences_index ON users ((preferences->>`github`));

注意我們有一個GIN索引在settings列上，兩個給出的路徑（github）表示式索引。在30000條資料中搜尋Github使用者名稱為john30000的記錄（最後一個插入的記錄），會給出以下數字：

Rehearsal -----------------------------------------------------------------
read jsonb (index column)       0.030000   0.030000   0.060000 (  3.673465)
read jsonb (expression index)   0.010000   0.010000   0.020000 (  0.087105)
read json (expression index)    0.010000   0.020000   0.030000 (  0.080121)
read json (no index)            0.060000   0.030000   0.090000 (113.206747)
-------------------------------------------------------- total: 0.200000sec

-----------------------------------------user     system      total        real
read jsonb (index column)       0.010000   0.020000   0.030000 (  0.092476)
read jsonb (expression index)   0.010000   0.010000   0.020000 (  0.078916)
read json (expression index)    0.010000   0.010000   0.020000 (  0.081908)
read json (no index)            0.050000   0.040000   0.090000 (110.761944)

和你看到的那樣，表示式索引在兩種資料型別中的效能幾乎完全一樣，所以它們在這裡並沒有實際的意義。剩下的兩列不同的地方在於在查詢列時有沒有索引；jsonb能在整列建立GIN/GIST索引，而json不能建立這樣的索引。這也是為什麼這json查詢速度這麼慢的原因。

讓我們檢查下在沒有索引的情況下查詢分析器查詢資料。

sql
EXPLAIN
SELECT *
FROM users
WHERE settings @> `{"twitter": "john30000"}` LIMIT 1;

--                                      QUERY PLAN
-- -------------------------------------------------------------------------------------
--  Limit  (cost=28.23..31.96 rows=1 width=468)
--    ->  Bitmap Heap Scan on users  (cost=28.23..140.07 rows=30 width=468)
--          Recheck Cond: (settings @> `{"twitter": "john30000"}`::jsonb)
--          ->  Bitmap Index Scan on settings_index  (cost=0.00..28.23 rows=30 width=0)
--                Index Cond: (settings @> `{"twitter": "john30000"}`::jsonb)

EXPLAIN
SELECT *
FROM users
WHERE preferences->>`twitter` = `john30000` LIMIT 1;

--                                QUERY PLAN
-- -------------------------------------------------------------------------
--  Limit  (cost=0.00..25.23 rows=1 width=468)
--    ->  Seq Scan on users  (cost=0.00..3784.00 rows=150 width=468)
--          Filter: ((preferences ->> `twitter`::text) = `john30000`::text)

最重要的是，json做的是順序掃描，這意味著PostgreSQL將根據順序一條一條往下找，直到找到符合條件的資料，同時記住查詢這些資料時，每條記錄中的JSON內容都會被解析，這將導致在複雜結構中查詢速度變慢。

但這些不會發生jsonb列中，這種查詢使用了索引，卻並沒有像使用表示式索引那樣將速度優化的很好。

jsonb有一個需要注意的點是，jsonb會一直順序檢索如果你使用->>操作符在一個沒有表示式索引的路徑上。

sql
EXPLAIN
SELECT *
FROM users
WHERE settings->>`twitter` = `johndoe30000` LIMIT 1;

--                                QUERY PLAN
-- -------------------------------------------------------------------------
--  Limit  (cost=0.00..25.23 rows=1 width=468)
--    ->  Seq Scan on users  (cost=0.00..3784.00 rows=150 width=468)
--          Filter: ((settings ->> `twitter`::text) = `johndoe30000`::text)
-- (3 rows)

因此，在你不提前知道查詢哪個json資料中的鍵或者查詢所有json路徑的情況下，請確保你定義了GIN/GIST索引和使用@>（或者其他有利於索引的操作符）

json轉化為jsonb

如果你已經使用了json格式或者text格式的列儲存JSON資料，你可以將他們轉化為jsonb，因而你可以依靠列索引。

sql
BEGIN;
ALTER TABLE users ADD COLUMN preferences_jsonb jsonb DEFAULT `{}`;
UPDATE users set preferences_jsonb = preferences::jsonb;
ALTER TABLE users ALTER COLUMN preferences_jsonb SET NOT NULL;
ALTER TABLE users RENAME COLUMN preferences TO preferences_json;
ALTER TABLE users RENAME COLUMN preferences_jsonb TO preferences;

-- Don`t remove the column until you`re sure everything is working.
-- ALTER TABLE users DROP COLUMN preferences_json;

COMMIT;

現在你已經知道了json是如何工作的，讓我們看看在Ruby on Rails中是怎麼使用的。

在Ruby on Rails中使用jsonb

Rails從4.2版本開始支援jsonb，使用他跟使用string或text型別的列一樣簡單，在下面的程式碼中，你將看到如何新增jsonb型別的列到已經存在的表中。

ruby
# db/migrate/*_create_users.rb
class CreateUsers < ActiveRecord::Migration
  def change
    enable_extension `citext`

    create_table :users do |t|
      t.text :name, null: false
      t.citext :username, null: false
      t.jsonb :preferences, null: false, default: `{}`
    end

    add_index  :users, :preferences, using: :gin
  end
end

# db/migrate/*_add_jsonb_column_to_users.rb
class AddJsonbColumnToUsers < ActiveRecord::Migration
  def change
    add_column :users, :preferences, :jsonb, null: false, default: `{}`
    add_index  :users, :preferences, using: :gin
  end
end

注意，我們已經定義了GIN型別的索引，如果你想對給出的路徑建立表示式索引，你必須使用execute。在這個例子中，Rails不知道怎麼使用ruby來轉化這個索引，所以你最好選擇將格式轉為SQL。

ruby
# config/initializers/active_record.rb
Rails.application.config.active_record.schema_format = :sql

# db/migrate/*_add_index_to_preferences_path_on_users.rb
class AddIndexToPreferencesPathOnUsers < ActiveRecord::Migration
  def change
    execute <<-SQL
      CREATE INDEX user_prefs_newsletter_index ON users ((preferences->>`newsletter`))
    SQL
  end
end

你的模型不需要做任何配置。你只需要建立支援json序列化的記錄來提供物件。

ruby
user = User.create!({
  name: `John Doe`,
  username: `johndoe`,
  preferences: {
    twitter: `johndoe`,
    github: `johndoe`,
    blog: `http://example.com`
  }
})

# Reload record from database to enforce serialization.
user.reload

# Show preferences.
user.preferences
#=> {"blog"=>"http://example.com", "github"=>"johndoe", "twitter"=>"johndoe"}

# Get blog.
user.preferences[`blog`]
#=> http://example.com

可以看到所有的鍵都是以string形式返回。你也可以使用通用的序列化方式，你就可以通過符號來訪問JSON物件。

ruby
# app/models/user.rb
class User < ActiveRecord::Base
  serialize :preferences, HashSerializer
end

# app/serializers/hash_serializer.rb
class HashSerializer
  def self.dump(hash)
    hash.to_json
  end

  def self.load(hash)
    (hash || {}).with_indifferent_access
  end
end

另一個比較有意思的是ActiveRecord特性就是store_accessor。如果你更改一些屬性比較頻繁，你可以建立accessor，這樣你可以賦值給屬性來代替JSON傳值。這也使得資料驗證和建立表單更加簡單。因此，如果我們建立一個表單來儲存部落格url、Github和Twitter賬戶，你可以像下面這樣使用：

ruby
class User < ActiveRecord::Base
  serialize :preferences, HashSerializer
  store_accessor :preferences, :blog, :github, :twitter
end

現在你可以簡單的賦值給這些屬性了。

ruby
user = User.new(blog: `http://example.org`, github: `johndoe`)

user.preferences
#=> {"blog"=>"http://example.org", "github"=>"johndoe"}

user.blog
#=> http://example.org

user.preferences[:github]
#=> johndoe

user.preferences[`github`]
#=> johndoe

定義了 store accessors 後，你可以像正常其他屬性一樣，定義資料驗證和建立表單

查詢jsonb列

現在是時候使用一些查詢操作。關於PostgreSQL的更多操作，請閱讀完整的文件列表

同時，記得使用註釋你執行的查詢語句；這有助於你更好的去做索引優化。

訂閱新聞郵件的使用者

ruby
# preferences->newsletter = true
User.where(`preferences @> ?`, {newsletter: true}.to_json)

對Ruby感興趣的使用者

ruby
# preferences->interests = [`ruby`, `javascript`, `python`]
User.where("preferences -> `interests` ? :language", language: `ruby`)

這個查詢不會用到列索引；如果你想查詢陣列，請確保你建立了表示式索引。

ruby
CREATE INDEX preferences_interests_on_users ON users USING GIN ((preferences->`interests`))

設定了Twitter和Github賬號的使用者

ruby
# preferences->twitter AND preferences->github
User.where(`preferences ?& array[:keys]`, keys: [`twitter`, `github`])

設定Twitter或Github賬號的使用者

ruby
# preferences->twitter OR preferences->github
User.where(`preferences ?| array[:keys]`, keys: [`twitter`, `github`])

住在洛杉磯/加利福尼亞的使用者

ruby
# preferences->state = `SP` AND preferences->city = `São Paulo`
User.where(`preferences @> ?`, {city: `San Francisco`, state: `CA`}.to_json)

關於hstore

hstore列不允許巢狀的結構，它將所有的值以字串形式儲存，所以必須要在資料庫層或者應用程式層將資料強制轉化為字串型別。而在json/jsonb型別的列上不會遇到這個問題，數值型別(integers/float)，布林型別，陣列，字串和空型別都可以接受，甚至你想的任何方式的資料巢狀。

因此推薦你儘早放棄hstore而去使用jsonb，但要記住的是你必須使用PostgreSQL 9.4以上版本才行。

我以前寫的hstore，想知道更多相關的內容就點選檢視。

總結

PostgreSQL是一個非常強大的資料庫，幸運的是ActiveRecord能跟上PostgreSQL的更新，為jsonb和hstore特性引入了內建支援。

而像表示式索引這樣的支援也在不斷的改善。將ActiveRecord的序列化改為SQL沒什麼大不了的，但卻使的索引變得更加簡單。

ruby
# This doesn`t exist, but it would be nice to have it!
add_index :users, "(settings->>`github`)", raw: true

在每一個新版本中，使用Rails和PostgreSQL都比過去更加容易，變得更加出色。因此，嘗試使用最新的Rails版本，付出總是會很快得到回報的。

相關文章