TL; DR: JSONB нь асуулгын гүйцэтгэлийг алдагдуулахгүйгээр өгөгдлийн сангийн схем боловсруулалтыг ихээхэн хялбарчилж чадна.
Танилцуулга
Харьцааны мэдээллийн сан (мэдээллийн сан) дэлхийн хамгийн эртний хэрэглээний жишээнүүдийн нэг болох сонгодог жишээг өгье: бидэнд аж ахуйн нэгж байгаа бөгөөд бид энэ аж ахуйн нэгжийн тодорхой шинж чанаруудыг (шинж чанаруудыг) хадгалах хэрэгтэй. Гэхдээ бүх тохиолдлууд ижил шинж чанаруудтай байж болохгүй бөгөөд ирээдүйд илүү олон шинж чанарыг нэмж болно.
Энэ асуудлыг шийдэх хамгийн хялбар арга бол өгөгдлийн сангийн хүснэгтэд үл хөдлөх хөрөнгийн үнэ цэнэ тус бүрийн багана үүсгэж, тухайн аж ахуйн нэгжийн жишээнд шаардлагатайг нь бөглөх явдал юм. Агуу их! Асуудал шийдэгдсэн... таны хүснэгтэд олон сая бичлэг байгаа тул та шинэ бичлэг нэмэх хэрэгтэй.
EAV загварыг авч үзье (), энэ нь ихэвчлэн тохиолддог. Нэг хүснэгтэд аж ахуйн нэгжүүд (бичлэгүүд), нөгөө хүснэгтэд өмчийн нэр (атрибутууд), гуравдахь хүснэгт нь аж ахуйн нэгжүүдийг тэдгээрийн шинж чанаруудтай холбож, одоогийн объектын шинж чанаруудын утгыг агуулдаг. Энэ нь өөр өөр объектын шинж чанаруудын өөр багцтай байх, мөн мэдээллийн сангийн бүтцийг өөрчлөхгүйгээр шууд шинж чанаруудыг нэмэх боломжийг танд олгоно.
Гэсэн хэдий ч, хэрэв EVA аргын зарим сул тал байхгүй байсан бол би энэ нийтлэлийг бичихгүй байх байсан. Жишээлбэл, тус бүр нь 1 шинж чанартай нэг буюу хэд хэдэн нэгжийг авахын тулд асуулгад 2 холболт шаардлагатай: эхнийх нь шинж чанарын хүснэгттэй нэгдэх, хоёр дахь нь утгуудын хүснэгттэй нэгдэх явдал юм. Хэрэв байгууллага нь 2 шинж чанартай бол 4 нэгдэл шаардлагатай! Нэмж дурдахад, бүх шинж чанарууд нь ихэвчлэн мөр хэлбэрээр хадгалагддаг бөгөөд энэ нь үр дүн болон WHERE заалтын төрлийг дамжуулдаг. Хэрэв та маш их асуулга бичдэг бол энэ нь нөөцийн ашиглалтын хувьд ихээхэн үрэлгэн болно.
Эдгээр илэрхий дутагдалтай хэдий ч EAV нь эдгээр төрлийн асуудлыг шийдвэрлэхэд удаан хугацаагаар ашиглагдаж ирсэн. Эдгээр нь зайлшгүй дутагдал байсан бөгөөд үүнээс илүү сайн сонголт байсангүй.
Гэвч дараа нь PostgreSQL дээр шинэ "технологи" гарч ирэв ...
PostgreSQL 9.4-ээс эхлэн JSON хоёртын өгөгдлийг хадгалахын тулд JSONB өгөгдлийн төрлийг нэмсэн. Хэдийгээр JSON-г энэ форматаар хадгалах нь энгийн текст JSON-ээс бага зэрэг илүү зай, цаг зарцуулдаг ч үүн дээр үйлдлүүдийг гүйцэтгэх нь илүү хурдан байдаг. JSONB нь индексжүүлэлтийг дэмждэг бөгөөд энэ нь асуулга илүү хурдан болгодог.
JSONB өгөгдлийн төрөл нь манай аж ахуйн нэгжийн хүснэгтэд зөвхөн нэг JSONB багана нэмж, өгөгдлийн сангийн дизайныг ихээхэн хялбаршуулж, төвөгтэй EAV загварыг солих боломжийг олгодог. Гэхдээ үүнийг дагаад бүтээмж буурах ёстой гэж олон хүн маргаж байна... Тийм учраас би энэ нийтлэлийг бичсэн.
Туршилтын мэдээллийн санг бий болгож байна
Энэ харьцуулахын тулд би 9.5 долларын өртөгтэй PostgreSQL 80-ийн шинэ суулгац дээр мэдээллийн санг үүсгэсэн. Ubuntu 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
);
Илүү энгийн харагдаж байна, тийм үү? Дараа нь үүнийг аж ахуйн нэгжийн хүснэгтэд нэмсэн (аж ахуйн нэгж & аж ахуйн нэгж_jsonb) 10 сая бичлэг, үүний дагуу хүснэгтийг EAV загвар болон JSONB баганын аргыг ашиглан ижил өгөгдлөөр дүүргэсэн - entity_jsonb.properties. Тиймээс бид бүх шинж чанаруудын дотроос хэд хэдэн өөр төрлийн өгөгдлийн төрлийг хүлээн авсан. Жишээ өгөгдөл:
{
id: 1
name: "Entity1"
description: "Test entity no. 1"
properties: {
color: "red"
lenght: 120
width: 3.1882420
hassomething: true
country: "Belgium"
}
}Тиймээс одоо бид хоёр сонголтын хувьд ижил өгөгдөлтэй байна. Ажил дээрээ хэрэгжилтийг харьцуулж эхэлцгээе!
Загвараа хялбарчлаарай
Өгөгдлийн сангийн дизайныг маш хялбаршуулсан гэж өмнө нь хэлсэн: EAV-д гурван хүснэгт ашиглахын оронд шинж чанарт JSONB багана ашиглан нэг хүснэгт. Гэхдээ энэ нь хүсэлтэд хэрхэн тусгагдсан бэ? Нэг аж ахуйн нэгжийн өмчийг шинэчлэх нь дараах байдалтай байна.
-- 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 объект болгон дамжуулах ёстой. Гэсэн хэдий ч бид ямар нэгэн танигчийг урьдчилан мэдэх шаардлагагүй. EAV жишээг харвал бид шинэчлэлтийг хийхийн тулд entity_id болон entity_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 энд ялна! Бид JSONB объектоос өнгийг текстийн утга болгон авахын тулд JSON ->> операторыг ашигладаг. @> операторыг ашиглан 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-д шаардлагатай холболтууд нь индексийг ашиглах боломжгүй байсан (гадаад түлхүүр талбаруудыг индексжүүлээгүй). Үүний дараа би EAV утгын хүснэгтийн 2 гадаад түлхүүр баганад индекс, мөн индекс үүсгэсэн. JSONB баганын хувьд.
Мэдээллийн шинэчлэлт нь цаг хугацааны хувьд (ms-ээр) дараах үр дүнг харуулав. Хуваарь нь логарифм гэдгийг анхаарна уу:
Хэрэв та дээр дурдсан шалтгааны улмаас индекс ашиглахгүй бол JSONB нь EAV-ээс хамаагүй (> 50000-x) хурдан болохыг бид харж байна. Бид багануудыг үндсэн түлхүүрээр индексжүүлэхэд ялгаа бараг алга болох боловч JSONB нь EAV-аас 1,3 дахин хурдан хэвээр байна. Үнэлгээний шалгуурт өмчийн баганыг ашиглаагүй тул JSONB баганад байгаа индекс энд ямар ч нөлөө үзүүлэхгүй гэдгийг анхаарна уу.
Өгөгдлийг үл хөдлөх хөрөнгийн үнэ цэнэд үндэслэн сонгохын тулд бид дараах үр дүнг авна (хэвийн масштаб):
Та JSONB нь индексгүй EAV-аас илүү хурдан ажиллаж байгааг анзаарч болно, гэхдээ EAV индекстэй байх үед JSONB-ээс хурдан ажилласаар байна. Гэвч дараа нь би JSONB асуулгын цаг ижил байгааг харсан нь GIN индекс ажиллахгүй байгааг надад сануулсан. Та GIN индексийг хүн амтай шинж чанаруудтай баганад ашиглах үед зөвхөн @> оруулах операторыг ашиглах үед л хүчин төгөлдөр болно. Би үүнийг шинэ туршилтанд ашигласан бөгөөд энэ нь цаг хугацааны хувьд асар их нөлөө үзүүлсэн: ердөө 0,153ms! Энэ нь EAV-аас 15000 дахин, ->> оператороос 25000 дахин хурдан юм.
Энэ нь хангалттай хурдан байсан гэж би бодож байна!
Өгөгдлийн сангийн хүснэгтийн хэмжээ
Хоёр аргын хувьд хүснэгтийн хэмжээг харьцуулж үзье. psql дээр бид бүх хүснэгт, индексийн хэмжээг командыг ашиглан харуулах боломжтой dti+
EAV аргын хувьд хүснэгтийн хэмжээ ойролцоогоор 3068 МБ, индекс нь 3427 МБ хүртэл нийт 6,43 ГБ байна. JSONB арга нь хүснэгтэд 1817 МБ, индекст 318 МБ ашигладаг бөгөөд энэ нь 2,08 ГБ юм. Энэ нь 3 дахин бага болж байна! Бид JSONB объект бүрт өмчийн нэрийг хадгалдаг тул энэ баримт намайг бага зэрэг гайхшруулсан.
Гэсэн хэдий ч тоонууд өөрсдөө ярьдаг: EAV-д бид атрибутын утга бүрт 2 бүхэл гадаад түлхүүр хадгалдаг бөгөөд ингэснээр 8 байт нэмэлт өгөгдөл бий болно. Нэмж дурдахад, EAV бүх өмчийн утгыг текст хэлбэрээр хадгалдаг бол JSONB нь тоон болон логик утгыг боломжтой бол дотооддоо ашиглах бөгөөд ингэснээр жижиг хэмжээтэй болно.
Үр дүн
Ерөнхийдөө, аж ахуйн нэгжийн шинж чанарыг JSONB форматаар хадгалах нь таны мэдээллийн санг зохион бүтээх, хадгалахад илүү хялбар болно гэж би бодож байна. Хэрэв та олон асуулга ажиллуулж байгаа бол бүх зүйлийг тухайн байгууллагатай нэг хүснэгтэд хадгалах нь илүү үр дүнтэй ажиллах болно. Энэ нь өгөгдөл хоорондын харилцан үйлчлэлийг хялбаршуулж байгаа нь аль хэдийн давуу талтай боловч үр дүнд нь өгөгдлийн сан 3 дахин бага хэмжээтэй байна.
Мөн хийсэн туршилтын үр дүнд бид гүйцэтгэлийн алдагдал маш бага байна гэж дүгнэж болно. Зарим тохиолдолд JSONB нь EAV-аас ч илүү хурдан байдаг тул үүнийг илүү сайн болгодог. Гэсэн хэдий ч, энэ жишиг нь мэдээжийн хэрэг бүх талыг хамардаггүй (жишээлбэл, маш олон тооны өмчтэй аж ахуйн нэгжүүд, одоо байгаа өгөгдлийн шинж чанаруудын тоо мэдэгдэхүйц нэмэгдсэн, ...), тиймээс танд тэдгээрийг хэрхэн сайжруулах талаар санал байвал , коммент хэсэгт чөлөөтэй үлдээгээрэй!
Эх сурвалж: www.habr.com
