โปรโฮสต์ > บล็อก > การบริหาร > การแทนที่ EAV ด้วย JSONB ใน PostgreSQL

การแทนที่ EAV ด้วย JSONB ใน PostgreSQL

ทีแอล; DR: JSONB ช่วยให้การพัฒนาสคีมาฐานข้อมูลง่ายขึ้นอย่างมาก โดยไม่กระทบต่อประสิทธิภาพการสืบค้น

การแนะนำ

เราจะยกตัวอย่างคลาสสิกของกรณีการใช้งานที่เก่าแก่ที่สุดแห่งหนึ่งในโลกของฐานข้อมูลเชิงสัมพันธ์ (ฐานข้อมูล) เรามีเอนทิตี และเราจำเป็นต้องบันทึกคุณสมบัติบางอย่าง (แอตทริบิวต์) ของเอนทิตีนี้ แต่ไม่ใช่ทุกอินสแตนซ์อาจมีชุดคุณสมบัติที่เหมือนกัน และอาจมีการเพิ่มคุณสมบัติเพิ่มเติมในอนาคต

วิธีที่ง่ายที่สุดในการแก้ปัญหานี้คือการสร้างคอลัมน์ในตารางฐานข้อมูลสำหรับแต่ละค่าคุณสมบัติ และเพียงกรอกข้อมูลที่จำเป็นสำหรับอินสแตนซ์เอนทิตีเฉพาะ ยอดเยี่ยม! แก้ไขปัญหา... จนกว่าตารางของคุณจะมีบันทึกนับล้านรายการ และคุณต้องเพิ่มบันทึกใหม่

พิจารณารูปแบบ EAV (เอนทิตี-แอตทริบิวต์-มูลค่า) มันเกิดขึ้นค่อนข้างบ่อย ตารางหนึ่งประกอบด้วยเอนทิตี (บันทึก) อีกตารางหนึ่งมีชื่อคุณสมบัติ (แอตทริบิวต์) และตารางที่สามเชื่อมโยงเอนทิตีกับแอตทริบิวต์และมีค่าของแอตทริบิวต์เหล่านั้นสำหรับเอนทิตีปัจจุบัน สิ่งนี้ทำให้คุณสามารถมีชุดคุณสมบัติที่แตกต่างกันสำหรับออบเจ็กต์ที่แตกต่างกัน และยังเพิ่มคุณสมบัติได้ทันทีโดยไม่ต้องเปลี่ยนโครงสร้างฐานข้อมูล

อย่างไรก็ตาม ฉันจะไม่เขียนโพสต์นี้หากไม่มีข้อเสียของแนวทาง EVA ตัวอย่างเช่นหากต้องการรับเอนทิตีตั้งแต่หนึ่งรายการขึ้นไปซึ่งมี 1 แอตทริบิวต์ในแต่ละแบบสอบถามจำเป็นต้องมี 2 การรวมในแบบสอบถาม: อันแรกคือการรวมกับตารางแอตทริบิวต์ส่วนที่สองคือการรวมกับตารางค่า หากเอนทิตีมี 2 คุณลักษณะ ก็จำเป็นต้องมีการรวม 4 รายการ! นอกจากนี้ โดยทั่วไปแล้วแอ็ตทริบิวต์ทั้งหมดจะถูกจัดเก็บเป็นสตริง ซึ่งส่งผลให้เกิดการพิมพ์แบบหล่อสำหรับทั้งผลลัพธ์และส่วนคำสั่ง WHERE หากคุณเขียนแบบสอบถามจำนวนมาก ถือว่าสิ้นเปลืองทรัพยากรอย่างมาก

แม้จะมีข้อบกพร่องที่ชัดเจนเหล่านี้ แต่ EAV ก็ถูกนำมาใช้เพื่อแก้ไขปัญหาประเภทนี้มานานแล้ว สิ่งเหล่านี้เป็นข้อบกพร่องที่หลีกเลี่ยงไม่ได้ และไม่มีทางเลือกอื่นที่ดีกว่านี้แล้ว
แต่แล้ว “เทคโนโลยี” ใหม่ก็ปรากฏขึ้นใน PostgreSQL...

ตั้งแต่ PostgreSQL 9.4 เป็นต้นไป ประเภทข้อมูล JSONB จะถูกเพิ่มเพื่อจัดเก็บข้อมูลไบนารี่ของ JSON แม้ว่าโดยทั่วไปการจัดเก็บ JSON ในรูปแบบนี้จะใช้พื้นที่และเวลามากกว่า JSON แบบข้อความธรรมดาเล็กน้อย แต่การดำเนินการกับ JSON นั้นเร็วกว่ามาก JSONB ยังรองรับการจัดทำดัชนี ซึ่งทำให้การสืบค้นเร็วยิ่งขึ้นไปอีก

ชนิดข้อมูล JSONB ช่วยให้เราสามารถแทนที่รูปแบบ EAV ที่ยุ่งยากได้โดยการเพิ่มคอลัมน์ JSONB เพียงคอลัมน์เดียวลงในตารางเอนทิตีของเรา ซึ่งทำให้การออกแบบฐานข้อมูลง่ายขึ้นมาก แต่หลายคนโต้แย้งว่าสิ่งนี้ควรมาพร้อมกับประสิทธิภาพการทำงานที่ลดลง... นั่นคือเหตุผลที่ฉันเขียนบทความนี้

การตั้งค่าฐานข้อมูลทดสอบ

สำหรับการเปรียบเทียบนี้ ฉันได้สร้างฐานข้อมูลบนการติดตั้ง PostgreSQL 9.5 ใหม่บนบิลด์ 80 ดอลลาร์ DigitalOcean อูบุนตู 14.04 หลังจากตั้งค่าพารามิเตอร์บางอย่างใน postgresql.conf ฉันก็วิ่ง นี้ สคริปต์โดยใช้ psql ตารางต่อไปนี้ถูกสร้างขึ้นเพื่อนำเสนอข้อมูลในรูปแบบ EAV:

CREATE TABLE entity ( 
  id           SERIAL PRIMARY KEY, 
  name         TEXT, 
  description  TEXT
);
CREATE TABLE entity_attribute (
  id          SERIAL PRIMARY KEY, 
  name        TEXT
);
CREATE TABLE entity_attribute_value (
  id                  SERIAL PRIMARY KEY, 
  entity_id           INT    REFERENCES entity(id), 
  entity_attribute_id INT    REFERENCES entity_attribute(id), 
  value               TEXT
);

ด้านล่างนี้เป็นตารางที่จะจัดเก็บข้อมูลเดียวกัน แต่มีแอตทริบิวต์ในคอลัมน์ประเภท JSONB – คุณสมบัติ.

CREATE TABLE entity_jsonb (
  id          SERIAL PRIMARY KEY, 
  name        TEXT, 
  description TEXT,
  properties  JSONB
);

ดูง่ายกว่ามากใช่ไหม? จากนั้นมันถูกเพิ่มลงในตารางเอนทิตี (เอกลักษณ์ & Entity_jsonb) 10 ล้านบันทึก ดังนั้นตารางจึงเต็มไปด้วยข้อมูลเดียวกันโดยใช้รูปแบบ EAV และวิธีการด้วยคอลัมน์ JSONB - Entity_jsonb.คุณสมบัติ. ดังนั้นเราจึงได้รับข้อมูลหลายประเภทจากชุดคุณสมบัติทั้งหมด ข้อมูลตัวอย่าง:

{
  id:          1
  name:        "Entity1"
  description: "Test entity no. 1"
  properties:  {
    color:        "red"
    lenght:       120
    width:        3.1882420
    hassomething: true
    country:      "Belgium"
  } 
}

ตอนนี้เรามีข้อมูลเดียวกันสำหรับทั้งสองตัวเลือก มาเริ่มเปรียบเทียบการใช้งานในที่ทำงานกันดีกว่า!

ลดความซับซ้อนของการออกแบบของคุณ

ก่อนหน้านี้มีการระบุไว้ว่าการออกแบบฐานข้อมูลนั้นง่ายขึ้นอย่างมาก: หนึ่งตารางโดยใช้คอลัมน์ JSONB สำหรับคุณสมบัติ แทนที่จะใช้สามตารางสำหรับ EAV แต่สิ่งนี้สะท้อนให้เห็นในคำขออย่างไร การอัปเดตคุณสมบัติเอนทิตีหนึ่งมีลักษณะดังนี้:

-- EAV
UPDATE entity_attribute_value 
SET value = 'blue' 
WHERE entity_attribute_id = 1 
  AND entity_id = 120;

-- JSONB
UPDATE entity_jsonb 
SET properties = jsonb_set(properties, '{"color"}', '"blue"') 
WHERE id = 120;

อย่างที่คุณเห็นคำขอสุดท้ายไม่ได้ดูง่ายกว่านี้ ในการอัปเดตค่าของคุณสมบัติในวัตถุ JSONB เราต้องใช้ฟังก์ชัน jsonb_set()และควรส่งผ่านค่าใหม่ของเราเป็นวัตถุ JSONB อย่างไรก็ตาม เราไม่จำเป็นต้องทราบตัวระบุใดๆ ล่วงหน้า เมื่อดูตัวอย่าง EAV เราจำเป็นต้องรู้ทั้ง enter_id และ entert_attribute_id เพื่อดำเนินการอัปเดต หากคุณต้องการอัปเดตคุณสมบัติในคอลัมน์ JSONB ตามชื่อออบเจ็กต์ ทั้งหมดนี้ทำได้ในบรรทัดเดียว

ตอนนี้ เรามาเลือกเอนทิตีที่เราเพิ่งอัปเดตตามสีใหม่:

-- EAV
SELECT e.name 
FROM entity e 
  INNER JOIN entity_attribute_value eav ON e.id = eav.entity_id
  INNER JOIN entity_attribute ea ON eav.entity_attribute_id = ea.id
WHERE ea.name = 'color' AND eav.value = 'blue';

-- JSONB
SELECT name 
FROM entity_jsonb 
WHERE properties ->> 'color' = 'blue';

ฉันคิดว่าเราสามารถตกลงกันว่าอันที่สองนั้นสั้นกว่า (ไม่ต้องเข้าร่วม!) ดังนั้นจึงอ่านง่ายกว่า JSONB ชนะที่นี่! เราใช้ตัวดำเนินการ JSON ->> เพื่อรับสีเป็นค่าข้อความจากวัตถุ JSONB ยังมีวิธีที่สองเพื่อให้ได้ผลลัพธ์เดียวกันในโมเดล JSONB โดยใช้ตัวดำเนินการ @>:

-- JSONB 
SELECT name 
FROM entity_jsonb 
WHERE properties @> '{"color": "blue"}';

สิ่งนี้ซับซ้อนกว่าเล็กน้อย: เราจะตรวจสอบเพื่อดูว่าออบเจ็กต์ JSON ในคอลัมน์คุณสมบัติมีออบเจ็กต์ที่อยู่ทางด้านขวาของตัวดำเนินการ @> หรือไม่ อ่านน้อยลง มีประสิทธิภาพมากขึ้น (ดูด้านล่าง)

มาทำให้การใช้ JSONB ง่ายยิ่งขึ้นเมื่อคุณต้องการเลือกคุณสมบัติหลายรายการพร้อมกัน นี่คือที่มาของแนวทาง JSONB: เราเพียงเลือกคุณสมบัติเป็นคอลัมน์เพิ่มเติมในชุดผลลัพธ์ของเราโดยไม่จำเป็นต้องรวม:

-- JSONB 
SELECT name
  , properties ->> 'color'
  , properties ->> 'country'
FROM entity_jsonb 
WHERE id = 120;

ด้วย EAV คุณจะต้องมี 2 การรวมสำหรับแต่ละคุณสมบัติที่คุณต้องการสืบค้น ในความคิดของฉัน ข้อความค้นหาข้างต้นแสดงให้เห็นถึงความเรียบง่ายอย่างมากในการออกแบบฐานข้อมูล ดูตัวอย่างเพิ่มเติมของวิธีเขียนคำสั่ง JSONB ได้ที่ นี้ โพสต์.
ตอนนี้ได้เวลาพูดคุยเกี่ยวกับประสิทธิภาพแล้ว

การปฏิบัติ

เพื่อเปรียบเทียบประสิทธิภาพที่ผมใช้ อธิบายการวิเคราะห์ ในการสอบถามเพื่อคำนวณเวลาดำเนินการ แต่ละแบบสอบถามถูกดำเนินการอย่างน้อยสามครั้ง เนื่องจากตัววางแผนแบบสอบถามใช้เวลานานกว่าในครั้งแรก ก่อนอื่น ฉันรันการสืบค้นโดยไม่มีดัชนีใดๆ แน่นอนว่านี่เป็นข้อได้เปรียบของ JSONB เนื่องจากการรวมที่จำเป็นสำหรับ EAV ไม่สามารถใช้ดัชนีได้ (ฟิลด์คีย์ต่างประเทศไม่ได้รับการจัดทำดัชนี) หลังจากนั้น ฉันได้สร้างดัชนีบนคอลัมน์คีย์ต่างประเทศ 2 คอลัมน์ของตารางค่า EAV รวมถึงดัชนีด้วย GIN สำหรับคอลัมน์ JSONB

การอัปเดตข้อมูลแสดงผลลัพธ์ต่อไปนี้ในแง่ของเวลา (เป็นมิลลิวินาที) โปรดทราบว่ามาตราส่วนเป็นลอการิทึม:

การแทนที่ EAV ด้วย JSONB ใน PostgreSQL

เราจะเห็นว่า JSONB นั้นเร็วกว่า EAV มาก (> 50000-x) หากคุณไม่ได้ใช้ดัชนี ด้วยเหตุผลที่ระบุไว้ข้างต้น เมื่อเราจัดทำดัชนีคอลัมน์ด้วยคีย์หลัก ความแตกต่างเกือบจะหายไป แต่ JSONB ยังคงเร็วกว่า EAV ถึง 1,3 เท่า โปรดทราบว่าดัชนีในคอลัมน์ JSONB ไม่มีผลกระทบที่นี่ เนื่องจากเราไม่ได้ใช้คอลัมน์คุณสมบัติในเกณฑ์การประเมิน

สำหรับการเลือกข้อมูลตามมูลค่าทรัพย์สิน เราจะได้ผลลัพธ์ดังต่อไปนี้ (มาตราส่วนปกติ):

การแทนที่ EAV ด้วย JSONB ใน PostgreSQL

คุณจะสังเกตได้ว่า JSONB ทำงานได้เร็วกว่า EAV อีกครั้งโดยไม่มีดัชนี แต่เมื่อ EAV ที่มีดัชนี ก็จะยังคงทำงานได้เร็วกว่า JSONB แต่แล้วฉันก็เห็นว่าเวลาสำหรับการสืบค้น JSONB นั้นเท่ากัน สิ่งนี้ทำให้ฉันทราบว่าดัชนี GIN ไม่ทำงาน เห็นได้ชัดว่าเมื่อคุณใช้ดัชนี GIN ในคอลัมน์ที่มีคุณสมบัติที่มีการเติมข้อมูล จะมีผลเฉพาะเมื่อใช้ตัวดำเนินการรวม @> เท่านั้น ฉันใช้สิ่งนี้ในการทดสอบใหม่และมีผลกระทบอย่างมากต่อเวลา: เพียง 0,153 มิลลิวินาที! ซึ่งเร็วกว่า EAV ถึง 15000 เท่า และเร็วกว่าตัวดำเนินการ ->> ถึง 25000 เท่า

ฉันคิดว่ามันเร็วพอ!

ขนาดตารางฐานข้อมูล

ลองเปรียบเทียบขนาดตารางสำหรับทั้งสองวิธี ใน psql เราสามารถแสดงขนาดของตารางและดัชนีทั้งหมดได้โดยใช้คำสั่ง ดีทีไอ+

การแทนที่ EAV ด้วย JSONB ใน PostgreSQL

สำหรับแนวทาง EAV ขนาดตารางจะอยู่ที่ประมาณ 3068 MB และจัดทำดัชนีได้สูงสุด 3427 MB รวมเป็น 6,43 GB วิธี JSONB ใช้ 1817 MB สำหรับตาราง และ 318 MB สำหรับดัชนี ซึ่งก็คือ 2,08 GB น้อยลง 3 เท่า! ข้อเท็จจริงนี้ทำให้ฉันประหลาดใจเล็กน้อยเนื่องจากเราจัดเก็บชื่อคุณสมบัติไว้ในออบเจ็กต์ JSONB ทุกอัน

แต่ถึงกระนั้น ตัวเลขก็พูดได้ด้วยตัวเอง: ใน EAV เราเก็บคีย์ต่างประเทศจำนวนเต็ม 2 อันต่อค่าแอตทริบิวต์ ส่งผลให้มีข้อมูลเพิ่มเติม 8 ไบต์ นอกจากนี้ EAV ยังเก็บค่าคุณสมบัติทั้งหมดเป็นข้อความ ในขณะที่ JSONB จะใช้ค่าตัวเลขและค่าบูลีนภายในเมื่อเป็นไปได้ ส่งผลให้มีขนาดเล็กลง

ผลของการ

โดยรวมแล้ว ฉันคิดว่าการบันทึกคุณสมบัติเอนทิตีในรูปแบบ JSONB ช่วยให้การออกแบบและบำรุงรักษาฐานข้อมูลของคุณง่ายขึ้นมาก หากคุณกำลังเรียกใช้แบบสอบถามจำนวนมาก การเก็บทุกอย่างไว้ในตารางเดียวกันเนื่องจากเอนทิตีจะทำงานได้อย่างมีประสิทธิภาพมากขึ้น และความจริงที่ว่าสิ่งนี้ทำให้การโต้ตอบระหว่างข้อมูลง่ายขึ้นนั้นเป็นข้อดีอยู่แล้ว แต่ฐานข้อมูลผลลัพธ์จะมีปริมาณน้อยกว่า 3 เท่า

นอกจากนี้ จากการทดสอบที่ดำเนินการ เราสามารถสรุปได้ว่าการสูญเสียประสิทธิภาพไม่มีนัยสำคัญมาก ในบางกรณี JSONB ยังเร็วกว่า EAV อีกด้วย ทำให้ดียิ่งขึ้นไปอีก อย่างไรก็ตาม เกณฑ์มาตรฐานนี้ไม่ครอบคลุมทุกด้าน (เช่น เอนทิตีที่มีคุณสมบัติจำนวนมาก การเพิ่มขึ้นอย่างมากในจำนวนคุณสมบัติของข้อมูลที่มีอยู่...) ดังนั้นหากคุณมีข้อเสนอแนะใดๆ เกี่ยวกับวิธีการปรับปรุงคุณสมบัติเหล่านั้น โปรดอย่าลังเลที่จะแสดงความคิดเห็น!

ที่มา: will.com

เพิ่มความคิดเห็น