PostgreSQL Antipatterns៖ ឆ្លងកាត់សំណុំ និងជ្រើសរើសទៅ SQL

ពីពេលមួយទៅពេលមួយអ្នកអភិវឌ្ឍន៍ត្រូវការ ឆ្លងកាត់សំណុំនៃប៉ារ៉ាម៉ែត្រ ឬសូម្បីតែការជ្រើសរើសទាំងមូលទៅនឹងសំណើ "នៅច្រកចូល" ។ ពេលខ្លះមានដំណោះស្រាយចំលែកចំពោះបញ្ហានេះ។

ចូរយើងទៅ "ពីភាពផ្ទុយគ្នា" ហើយមើលពីរបៀបមិនធ្វើវា ហេតុអ្វី និងរបៀបដែលអ្នកអាចធ្វើវាបានប្រសើរជាងមុន។

"ការបញ្ចូល" ដោយផ្ទាល់នៃតម្លៃនៅក្នុងតួសំណើ

ជាធម្មតាវាមើលទៅដូចនេះ៖

query = "SELECT * FROM tbl WHERE id = " + value

... ឬដូចនេះ៖

query = "SELECT * FROM tbl WHERE id = :param".format(param=value)

អំពីវិធីសាស្រ្តនេះវាត្រូវបាននិយាយសរសេរនិង សូម្បីតែគូរ គ្រប់គ្រាន់:

ស្ទើរតែជានិច្ចកាល ផ្លូវផ្ទាល់ទៅកាន់ការចាក់ SQL និងបន្ទុកបន្ថែមលើតក្កវិជ្ជាអាជីវកម្ម ដែលត្រូវបានបង្ខំឱ្យ "បិទភ្ជាប់" ខ្សែអក្សរសំណួររបស់អ្នក។

វិធីសាស្រ្តនេះអាចត្រូវបានរាប់ជាសុចរិតដោយផ្នែកលុះត្រាតែចាំបាច់។ ប្រើការបែងចែក នៅក្នុង PostgreSQL កំណែ 10 និងខាងក្រោមសម្រាប់ផែនការដែលមានប្រសិទ្ធភាពជាងមុន។ នៅក្នុងកំណែទាំងនេះ បញ្ជីនៃផ្នែកដែលបានស្កេនត្រូវបានកំណត់ដោយមិនគិតពីប៉ារ៉ាម៉ែត្រដែលបានបញ្ជូន មានតែនៅលើមូលដ្ឋាននៃផ្នែកសំណើប៉ុណ្ណោះ។

$n អាគុយម៉ង់

ប្រើ កន្លែងដាក់ ប៉ារ៉ាម៉ែត្រគឺល្អវាអនុញ្ញាតឱ្យអ្នកប្រើ សេចក្តីថ្លែងការណ៍ដែលបានរៀបចំកាត់បន្ថយការផ្ទុកទាំងនៅលើតក្កវិជ្ជាអាជីវកម្ម (ខ្សែអក្សរសំណួរត្រូវបានបង្កើតឡើង និងបញ្ជូនតែម្តងប៉ុណ្ណោះ) និងនៅលើម៉ាស៊ីនមេមូលដ្ឋានទិន្នន័យ (ការញែកឡើងវិញ និងការធ្វើផែនការមិនត្រូវបានទាមទារសម្រាប់ឧទាហរណ៍នីមួយៗនៃសំណើ)។

ចំនួនអថេរនៃអាគុយម៉ង់

បញ្ហានឹងរង់ចាំយើង នៅពេលដែលយើងចង់ឆ្លងកាត់អំណះអំណាងដែលមិនស្គាល់ចំនួនជាមុន៖

... id IN ($1, $2, $3, ...) -- $1 : 2, $2 : 3, $3 : 5, ...

ប្រសិនបើអ្នកទុកសំណើក្នុងទម្រង់នេះ ទោះបីវានឹងជួយសង្រ្គោះយើងពីការចាក់ដែលមានសក្តានុពលក៏ដោយ វានឹងនៅតែនាំទៅរកតម្រូវការក្នុងការបិទ/ញែកសំណើ សម្រាប់ជម្រើសនីមួយៗពីចំនួនអាគុយម៉ង់. ប្រសើរជាងធ្វើវារាល់ដង ប៉ុន្តែអ្នកអាចធ្វើបានដោយគ្មានវា។

វាគ្រប់គ្រាន់ហើយក្នុងការឆ្លងកាត់ប៉ារ៉ាម៉ែត្រតែមួយគត់ដែលមាន តំណាង​សៀរៀល​នៃ​អារេ:

... id = ANY($1::integer[]) -- $1 : '{2,3,5,8,13}'

ភាពខុសគ្នាតែមួយគត់គឺតម្រូវការក្នុងការបំប្លែងអាគុយម៉ង់យ៉ាងច្បាស់ទៅជាប្រភេទអារេដែលចង់បាន។ ប៉ុន្តែ​នេះ​មិន​បណ្តាល​ឱ្យ​មាន​បញ្ហា​ទេ ព្រោះ​យើង​ដឹង​ជា​មុន​ថា​យើង​កំពុង​ដោះស្រាយ​នៅ​កន្លែង​ណា។

ការផ្ទេរគំរូ (ម៉ាទ្រីស)

ជាធម្មតាទាំងនេះគឺជាជម្រើសគ្រប់ប្រភេទសម្រាប់ការផ្ទេរសំណុំទិន្នន័យសម្រាប់ការបញ្ចូលទៅក្នុងមូលដ្ឋានទិន្នន័យ “ក្នុងសំណើមួយ”៖

INSERT INTO tbl(k, v) VALUES($1,$2),($3,$4),...

បន្ថែមពីលើបញ្ហាដែលបានពិពណ៌នាខាងលើជាមួយនឹងការ "បិទភ្ជាប់ឡើងវិញ" នៃសំណើនេះក៏អាចនាំយើងទៅ ចេញពីការចងចាំ និងការគាំងម៉ាស៊ីនមេ។ ហេតុផលគឺសាមញ្ញ - PG រក្សាទុកអង្គចងចាំបន្ថែមសម្រាប់អាគុយម៉ង់ ហើយចំនួនកំណត់ត្រាក្នុងសំណុំត្រូវបានកំណត់ត្រឹមកម្មវិធីតក្កវិជ្ជាអាជីវកម្ម Wishlist ប៉ុណ្ណោះ។ ជាពិសេសក្នុងករណីគ្លីនិក ចាំបាច់ត្រូវពិនិត្យ អាគុយម៉ង់ "លេខ" ធំជាង $9000 - កុំធ្វើបែបនេះ។

តោះសរសេរសំណួរឡើងវិញ ដាក់ពាក្យរួចហើយ សៀរៀល "ពីរកម្រិត":

INSERT INTO tbl
SELECT
  unnest[1]::text k
, unnest[2]::integer v
FROM (
  SELECT
    unnest($1::text[])::text[] -- $1 : '{"{a,1}","{b,2}","{c,3}","{d,4}"}'
) T;

បាទ/ចាស ក្នុងករណីតម្លៃ "ស្មុគ្រស្មាញ" នៅខាងក្នុងអារេមួយ ពួកវាត្រូវដាក់ស៊ុមជាមួយសម្រង់។
វាច្បាស់ណាស់ថាតាមរបៀបនេះអ្នកអាច "ពង្រីក" ការជ្រើសរើសជាមួយនឹងចំនួនវាលដែលបំពាន។

unnest, unnest,…

ពីពេលមួយទៅពេលមួយមានជម្រើសសម្រាប់ការឆ្លងកាត់ជំនួសឱ្យ "អារេនៃអារេ" "អារេនៃជួរឈរ" ជាច្រើនដែលខ្ញុំបានលើកឡើង នៅក្នុងអត្ថបទចុងក្រោយ:

SELECT
  unnest($1::text[]) k
, unnest($2::integer[]) v;

ជាមួយនឹងវិធីសាស្រ្តនេះ ប្រសិនបើអ្នកមានកំហុសនៅពេលបង្កើតបញ្ជីតម្លៃសម្រាប់ជួរឈរផ្សេងៗគ្នា វាងាយស្រួលណាស់ក្នុងការទទួលបានទាំងស្រុង។ លទ្ធផលដែលមិនបានរំពឹងទុកដែលអាស្រ័យលើកំណែម៉ាស៊ីនមេ៖

-- $1 : '{a,b,c}', $2 : '{1,2}'
-- PostgreSQL 9.4
k | v
-----
a | 1
b | 2
c | 1
a | 2
b | 1
c | 2
-- PostgreSQL 11
k | v
-----
a | 1
b | 2
c |

JSON

ចាប់ផ្តើមពីកំណែ 9.3 PostgreSQL មានមុខងារពេញលេញសម្រាប់ធ្វើការជាមួយប្រភេទ json ។ ដូច្នេះ ប្រសិនបើ​ប៉ារ៉ាម៉ែត្រ​បញ្ចូល​របស់អ្នក​ត្រូវ​បានកំណត់​ក្នុង​កម្មវិធីរុករកតាមអ៊ីនធឺណិត អ្នកអាច​នៅទីនោះ ហើយ​បង្កើត​ទម្រង់ វត្ថុ json សម្រាប់សំណួរ SQL:

SELECT
  key k
, value v
FROM
  json_each($1::json); -- '{"a":1,"b":2,"c":3,"d":4}'

សម្រាប់កំណែមុន វិធីសាស្ត្រដូចគ្នាអាចត្រូវបានប្រើសម្រាប់ នីមួយៗ (hstore)ប៉ុន្តែ "បត់" ត្រឹមត្រូវជាមួយនឹងការគេចចេញពីវត្ថុស្មុគស្មាញនៅក្នុង hstore អាចបណ្តាលឱ្យមានបញ្ហា។

json_populate_recordset

ប្រសិនបើអ្នកដឹងជាមុនថាទិន្នន័យពី "input" json array នឹងទៅបំពេញក្នុងតារាងមួយចំនួន អ្នកអាចសន្សំបានច្រើនក្នុង "dereferencing" fields ហើយបញ្ជូនទៅកាន់ប្រភេទដែលចង់បានដោយប្រើមុខងារ json_populate_recordset៖

SELECT
  *
FROM
  json_populate_recordset(
    NULL::pg_class
  , $1::json -- $1 : '[{"relname":"pg_class","oid":1262},{"relname":"pg_namespace","oid":2615}]'
  );

json_to_recordset

ហើយមុខងារនេះនឹងគ្រាន់តែ "ពង្រីក" អារេនៃវត្ថុដែលបានឆ្លងកាត់ទៅជាជម្រើសមួយ ដោយមិនពឹងផ្អែកលើទម្រង់តារាង៖

SELECT
  *
FROM
  json_to_recordset($1::json) T(k text, v integer);
-- $1 : '[{"k":"a","v":1},{"k":"b","v":2}]'
k | v
-----
a | 1
b | 2

តារាងបណ្តោះអាសន្ន

ប៉ុន្តែប្រសិនបើចំនួនទិន្នន័យនៅក្នុងគំរូដែលបានបញ្ជូនគឺធំណាស់នោះ ការបោះវាទៅក្នុងប៉ារ៉ាម៉ែត្រសៀរៀលតែមួយគឺពិបាក ហើយពេលខ្លះមិនអាចទៅរួច ព្រោះវាទាមទារតែម្តង។ ការបែងចែកអង្គចងចាំធំ. ឧទាហរណ៍ អ្នកត្រូវប្រមូលទិន្នន័យព្រឹត្តិការណ៍ធំមួយពីប្រព័ន្ធខាងក្រៅសម្រាប់រយៈពេលដ៏យូរមួយ ហើយបន្ទាប់មកអ្នកចង់ដំណើរការវាតែម្តងនៅលើផ្នែកមូលដ្ឋានទិន្នន័យ។

ក្នុងករណីនេះដំណោះស្រាយល្អបំផុតគឺត្រូវប្រើ តារាងបណ្តោះអាសន្ន:

CREATE TEMPORARY TABLE tbl(k text, v integer);
...
INSERT INTO tbl(k, v) VALUES($1, $2); -- повторить много-много раз
...
-- тут делаем что-то полезное со всей этой таблицей целиком

វិធីសាស្រ្តគឺល្អ។ សម្រាប់ការបញ្ជូនមិនញឹកញាប់នៃបរិមាណធំ ទិន្នន័យ។
តាមទស្សនៈនៃការពិពណ៌នាអំពីរចនាសម្ព័ន្ធនៃទិន្នន័យរបស់វា តារាងបណ្តោះអាសន្នខុសពីតារាង "ធម្មតា" ក្នុងលក្ខណៈតែមួយប៉ុណ្ណោះ។ នៅក្នុងតារាងប្រព័ន្ធ pg_classនិងក្នុង។ pg_type, pg_depend, pg_attribute, pg_attrdef, ... - ហើយគ្មានអ្វីទាំងអស់។

ដូច្នេះនៅក្នុងប្រព័ន្ធបណ្តាញដែលមានចំនួនដ៏ច្រើននៃការតភ្ជាប់រយៈពេលខ្លីសម្រាប់ពួកវានីមួយៗ តារាងបែបនេះនឹងបង្កើតកំណត់ត្រាប្រព័ន្ធថ្មីរាល់ពេលដែលត្រូវបានលុបនៅពេលដែលការតភ្ជាប់ទៅមូលដ្ឋានទិន្នន័យត្រូវបានបិទ។ នៅទីបំផុត ការប្រើប្រាស់ TEMP TABLE ដែលមិនអាចគ្រប់គ្រងបាននាំឱ្យតារាង "ហើម" នៅក្នុង pg_catalog និងបន្ថយប្រតិបត្តិការជាច្រើនដែលប្រើវា។
ជាការពិតណាស់នេះអាចត្រូវបានប្រយុទ្ធជាមួយ ឆ្លងកាត់តាមកាលកំណត់ VACUUM ពេញ នេះបើយោងតាមតារាងនៃកាតាឡុកប្រព័ន្ធ។

អថេរសម័យ

ឧបមាថាការដំណើរការទិន្នន័យពីករណីមុនគឺស្មុគស្មាញសម្រាប់សំណួរ SQL តែមួយ ប៉ុន្តែអ្នកចង់ធ្វើវាញឹកញាប់។ នោះគឺយើងចង់ប្រើដំណើរការនីតិវិធីនៅក្នុង ធ្វើប្លុកប៉ុន្តែការប្រើការផ្ទេរទិន្នន័យតាមរយៈតារាងបណ្តោះអាសន្ននឹងមានតម្លៃថ្លៃពេក។

យើងក៏មិនអាចប្រើ $n-parameters ដើម្បីបញ្ជូនទៅកាន់ប្លុកអនាមិកដែរ។ អថេរសម័យ និងមុខងារនឹងជួយយើងចេញពីស្ថានភាព។ ការកំណត់បច្ចុប្បន្ន.

មុនពេលកំណែ 9.2 អ្នកត្រូវតែកំណត់រចនាសម្ព័ន្ធជាមុន ចន្លោះឈ្មោះពិសេស custom_variable_classes សម្រាប់អថេរសម័យ "របស់ពួកគេ" ។ នៅលើកំណែបច្ចុប្បន្ន អ្នកអាចសរសេរអ្វីមួយដូចនេះ៖

SET my.val = '{1,2,3}';
DO $$
DECLARE
  id integer;
BEGIN
  FOR id IN (SELECT unnest(current_setting('my.val')::integer[])) LOOP
    RAISE NOTICE 'id : %', id;
  END LOOP;
END;
$$ LANGUAGE plpgsql;
-- NOTICE:  id : 1
-- NOTICE:  id : 2
-- NOTICE:  id : 3

មានដំណោះស្រាយផ្សេងទៀតដែលមានជាភាសានីតិវិធីដែលគាំទ្រផ្សេងទៀត។

ដឹងវិធីច្រើនទៀតទេ? ចែករំលែកនៅក្នុងមតិយោបល់!

ប្រភព: www.habr.com