PostgreSQL ці MySQL для JSON: адзін індэксуе дакумент, другі — выраз

Для JSON з частымі пошукамі па розных ключах і ўкладзеных значэннях разумная адпраўная кропка — PostgreSQL: яго GIN-індэкс для jsonb падтрымлівае ўмовы на ўваходжанне фрагмента, існаванне ключа і пэўныя запыты jsonpath. Калі ж асноўныя фільтры вядомыя загадзя і звернутыя да некалькіх скалярных значэнняў, MySQL дазваляе індэксаваць вынятыя з JSON палі. Выбар вызначае форма запытаў, а не сам факт захоўвання дакумента.

У MySQL слупок тыпу JSON непасрэдна не індэксуецца: для скалярнага пошуку можна стварыць згенераваны слупок з асобным індэксам. У PostgreSQL GIN ахоплівае набор аператараў над jsonb, але не паскарае аўтаматычна кожнае параўнанне вынятага тэксту. Таму для фільтра па статусе абедзве базы могуць даць індэксаваны доступ, аднак патрабуюць рознай формы SQL і рознага абслугоўвання схемы.

Адна мадэль даных і дзве формы пошуку

Возьмем умоўную табліцу events: звычайныя слупкі id і account_id захоўваюць ідэнтыфікатары, а payload — дакумент {"status":"paid","region":"BY","tags":["priority"]}. У PostgreSQL тып payload — jsonb, у MySQL — JSON. Гэта аднолькавыя ўваходныя даныя для параўнання, а не вынік вымярэння хуткасці. Статус патрэбны для частага фільтра, region — для другой умовы, tags — для пошуку па масіве.

Рэальны набор запытаў можа складацца з роўнасці status, спалучэння status з account_id, пошуку тэга і праверкі наяўнасці ключа. Апошнія дзве ўмовы паказваюць, дзе просты скалярны індэкс ужо не замяняе пошук па іншых частках дакумента. Калі account_id удзельнічае ў сувязях з іншымі табліцамі, яго варта пакінуць асобным тыпізаваным слупком; тады аптымізацыя JSON не змешваецца з умовамі злучэння і абмежаваннямі цэласнасці.

PostgreSQL: што сапраўды знаходзіць GIN

Для ўмоў на ўвесь payload прыклад індэкса выглядае так: CREATE INDEX events_payload_gin ON events USING GIN (payload). Пасля гэтага запыт SELECT id FROM events WHERE payload @> '{"status":"paid"}' можа выкарыстоўваць GIN, бо аператар @> ужыты непасрэдна да індэксаванага слупка. Аналагічны пошук па масіве можна запісаць як payload @> '{"tags":["priority"]}'. Аднак рашэнне сканаваць індэкс усё роўна прымае планавальнік з улікам даных і кошту доступу.

Клас аператараў jsonb_ops, які выкарыстоўваецца па змаўчанні, падтрымлівае @>, праверку ключа праз ?, групавыя праверкі ключоў і аператары jsonpath @? ды @@. Альтэрнатыўны jsonb_path_ops таксама працуе з @>, @? і @@, але не з аператарамі існавання ключоў; для ўмоў на ўваходжанне ён звычайна дае меншы і больш спецыфічны індэкс. Такім чынам, нават у межах PostgreSQL выбар класа аператараў змяняе, якія запыты атрымаюць індэксаваны шлях.

Іншая форма таго ж фільтра — SELECT id FROM events WHERE payload->>'status' = 'paid'. Яна параўноўвае выняты тэкст, таму GIN на payload сам па сабе не з'яўляецца індэксам такога выразу. Для яе падыходзіць CREATE INDEX events_status_idx ON events ((payload->>'status')). Гэта істотная мяжа параўнання: PostgreSQL таксама можа індэксаваць асобны выраз, калі прыкладанне ўвесь час пытаецца пра адно поле.

MySQL: скалярнае поле як частка схемы

Для той жа ўмовы ў MySQL можна вызначыць status VARCHAR(32) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(payload, '$.status'))) VIRTUAL і стварыць INDEX events_status_idx (status). Тады SELECT id FROM events WHERE status = 'paid' звяртаецца да індэксаванага скалярнага слупка. JSON застаецца ў payload, а вынятае значэнне атрымлівае тып, даўжыню і правілы параўнання радкоў. Гэтыя ўласцівасці трэба выбіраць паводле рэальных значэнняў status.

Аптымізатар можа скарыстацца такім індэксам і пры звароце да самога JSON-выразу, але правілы супадзення выразаў MySQL патрабуюць адпаведнасці выразу і выніковага тыпу. JSON_UNQUOTE() важны для радкоў: ён здымае двукоссі са значэння JSON перад параўнаннем праз індэкс. Запыт па назве status выразней паказвае сувязь з індэксам; адвольна перастаўленыя функцыі або іншы тып выніку не варта лічыць эквівалентнымі для аптымізатара.

Калі часта запытваюцца яшчэ region і іншае скалярнае поле, пад іх патрэбны адпаведныя індэксаваныя выразы ці слупкі. Гэта кіраваная схема пры стабільным наборы фільтраў, але кожны новы шлях у JSON становіцца асобным рашэннем у схеме. Калі статус часам адсутнічае або змяняе тып, трэба вызначыць паводзіны вынятага слупка да параўнання прадукцыйнасці: інакш дзве рэалізацыі могуць вяртаць розныя радкі.

Масівы патрабуюць асобнага рашэння

Індэкс status не дапаможа знайсці элемент масіву tags. У MySQL шматзначны індэкс InnoDB стварае запісы для элементаў JSON-масіву праз CAST(... AS ... ARRAY); з ім могуць працаваць MEMBER OF(), JSON_CONTAINS() і JSON_OVERLAPS(). Гэта асобны механізм для масіваў, а не ўласцівасць скалярнага індэкса згенераванага status.

Для ўмоў накшталт «ёсць priority у tags» розніца ўжо не проста ў назве індэкса: PostgreSQL запытвае ўваходжанне фрагмента ў дакумент, MySQL — элемент пэўнага масіву. Пры стварэнні шматзначнага індэкса трэба задаць SQL-тып элементаў; змяшаныя або нестабільныя тыпы ў масіве ўскладняюць гэты выбар. Сэнс параўноўваць планы ўзнікае толькі пасля таго, як абедзве ўмовы вяртаюць аднолькавыя радкі.

Абнаўленні, схема і сувязі

GIN на ўвесь дакумент захоўвае індэксныя элементы для розных ключоў і значэнняў, таму можа абслугоўваць разнастайныя ўмовы без асобнага слупка для кожнага шляху. Набор індэксаў згенераваных слупкоў вузейшы па ахопе, затое выразна адпавядае вядомым фільтрам; даданне новага фільтра азначае змяненне схемы. Пры частым абнаўленні payload кошт запісу і памер індэксаў варта мераць разам з часам пошуку.

Змена значэння, якое трапляе ў індэкс, патрабуе абслугоўвання гэтага індэкса незалежна ад абранай СКБД. Таму нагрузка з частымі запісамі можа даць іншы архітэктурны адказ, чым нагрузка пераважна з чытанняў. Калі account_id звязвае events з рахункамі ці карыстальнікамі, яго месца ў звычайным слупку дазваляе асобна праектаваць ключы і транзакцыйныя сувязі ў абедзвюх СКБД.

Што можа вырашыць замер

Агульны вынік OLTP-тэсту не мерае хуткасць гэтых JSON-індэксаў. Напрыклад, бенчмарк SQLpipe з Reserva мадэлюе грашовыя пераводы, аўтарызацыю і абнаўленне рахункаў у сваёй схеме і канфігурацыі. Яго вынік апісвае гэтую плацежную нагрузку; фільтры па status, tags і змены payload трэба вымяраць асобна.

Для ўласнага параўнання патрэбны аднолькавыя дакументы, аднолькавы чаканы набор радкоў і рэальная прапорцыя чытанняў ды абнаўленняў. Уключыце роўнасць па status, уваходжанне па tags, спалучэнне з account_id і тыя ўмовы на ключы, якія сапраўды выкарыстоўвае сэрвіс. EXPLAIN пакажа, ці разглядае аптымізатар індэкс, а замеры на рэальным размеркаванні значэнняў — ці дае ён выйгрыш з улікам запісу.

Калі запыты часта мяняюць шляхі, шукаюць ключы і ўкладзеныя значэнні, адпраўная кропка — PostgreSQL jsonb з класам GIN паводле патрэбных аператараў. Калі фільтры стабільныя і скалярныя, MySQL са згенераванымі слупкамі робіць кожны індэкс прывязкай да дакладнага поля; для масіваў патрэбен асобны шматзначны індэкс. Пры вялікай долі абнаўленняў або складаных злучэннях перавагу вызначаць ужо планы і кошт аперацый на канкрэтнай схеме.

Чытайце таксама:

• PostgreSQL ці MySQL: адзін бенчмарк не выбірае базу на гады
• MCP ці A2A: адзін дае агенту інструменты, другі — партнёраў
• Weaviate ці Milvus: адзін бенчмарк не выбірае вектарную базу