ការប្រើប្រាស់លក្ខណៈពិសេសទាំងអស់នៃលិបិក្រមនៅក្នុង PostgreSQL

នៅក្នុងពិភពលោក Postgres សន្ទស្សន៍មានសារៈសំខាន់សម្រាប់ការរុករកប្រកបដោយប្រសិទ្ធភាពនៃឃ្លាំងផ្ទុកទិន្នន័យ (ហៅថា "ហ៊ាប")។ Postgres មិនគាំទ្រការដាក់ចង្កោមសម្រាប់វាទេ ហើយស្ថាបត្យកម្ម MVCC បណ្តាលឱ្យអ្នកបញ្ចប់ជាមួយនឹងកំណែជាច្រើននៃ tuple ដូចគ្នា។ ដូច្នេះ វាមានសារៈសំខាន់ខ្លាំងណាស់ក្នុងការបង្កើត និងរក្សាលិបិក្រមប្រកបដោយប្រសិទ្ធភាព ដើម្បីគាំទ្រកម្មវិធី។

នេះគឺជាគន្លឹះមួយចំនួនសម្រាប់បង្កើនប្រសិទ្ធភាព និងកែលម្អការប្រើប្រាស់លិបិក្រម។

ចំណាំ៖ សំណួរដែលបង្ហាញខាងក្រោមដំណើរការលើការមិនកែប្រែ មូលដ្ឋានទិន្នន័យគំរូ pagila.

ការប្រើប្រាស់សន្ទស្សន៍គ្របដណ្តប់

សូមក្រឡេកមើលសំណើដកអាសយដ្ឋានអ៊ីមែលសម្រាប់អ្នកប្រើប្រាស់អសកម្ម។ តុ customer មានជួរឈរមួយ។ activeហើយសំណួរគឺសាមញ្ញ៖

pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
                        QUERY PLAN
-----------------------------------------------------------
 Seq Scan on customer  (cost=0.00..16.49 rows=15 width=32)
   Filter: (active = 0)
(2 rows)

សំណួរ​ហៅ​លំដាប់​ស្កេន​តារាង​ពេញ customer. តោះបង្កើតលិបិក្រមនៅលើជួរឈរ active:

pagila=# CREATE INDEX idx_cust1 ON customer(active);
CREATE INDEX
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Index Scan using idx_cust1 on customer  (cost=0.28..12.29 rows=15 width=32)
   Index Cond: (active = 0)
(2 rows)

វាបានជួយ ការស្កេនជាបន្តបន្ទាប់ប្រែទៅជា "index scan"។ នេះមានន័យថា Postgres នឹងស្កេនសន្ទស្សន៍ "idx_cust1" ហើយ​បន្ទាប់​មក​បន្ត​ការ​ស្វែង​រក​ជួរ​តារាង​ដើម្បី​អាន​តម្លៃ​នៃ​ជួរ​ឈរ​ផ្សេង​ទៀត (ក្នុង​ករណី​នេះ ជួរ​ឈរ email) ដែលសំណួរត្រូវការ។

សន្ទស្សន៍គ្របដណ្តប់ត្រូវបានណែនាំនៅក្នុង PostgreSQL 11 ។ ពួកគេអនុញ្ញាតឱ្យអ្នករួមបញ្ចូលជួរឈរបន្ថែមមួយឬច្រើននៅក្នុងលិបិក្រមខ្លួនវា - តម្លៃរបស់ពួកគេត្រូវបានរក្សាទុកនៅក្នុងឃ្លាំងទិន្នន័យលិបិក្រម។

ប្រសិនបើយើងទាញយកអត្ថប្រយោជន៍ពីមុខងារនេះ ហើយបន្ថែមតម្លៃអ៊ីមែលនៅខាងក្នុងលិបិក្រម នោះ Postgres នឹងមិនចាំបាច់ស្វែងរកតារាងតម្លៃទេ។ email. តោះ​មើល​ថា​តើ​វា​នឹង​ដំណើរការ​ឬ​អត់៖

pagila=# CREATE INDEX idx_cust2 ON customer(active) INCLUDE (email);
CREATE INDEX
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Index Only Scan using idx_cust2 on customer  (cost=0.28..12.29 rows=15 width=32)
   Index Cond: (active = 0)
(2 rows)

«Index Only Scan' ប្រាប់​យើង​ថា​សំណួរ​ឥឡូវ​នេះ​ត្រូវការ​តែ​លិបិក្រម​ប៉ុណ្ណោះ ដែល​ជួយ​ជៀសវាង​រាល់​ថាស I/O ដើម្បី​អាន​តារាង។

សន្ទស្សន៍គ្របដណ្តប់បច្ចុប្បន្នមានសម្រាប់តែដើមឈើ B ប៉ុណ្ណោះ។ ទោះជាយ៉ាងណាក៏ដោយក្នុងករណីនេះការខិតខំប្រឹងប្រែងថែទាំនឹងខ្ពស់ជាង។

ការប្រើប្រាស់សន្ទស្សន៍ផ្នែក

លិបិក្រមផ្នែកខ្លះធ្វើលិបិក្រមតែសំណុំរងនៃជួរដេកក្នុងតារាងប៉ុណ្ណោះ។ វារក្សាទុកទំហំនៃលិបិក្រម និងធ្វើឱ្យការស្កេនលឿនជាងមុន។

ចូរនិយាយថាយើងចង់ទទួលបានបញ្ជីអាសយដ្ឋានអ៊ីមែលរបស់អតិថិជនរបស់យើងនៅក្នុងរដ្ឋកាលីហ្វ័រញ៉ា។ ពាក្យសុំនឹងមានដូចនេះ៖

SELECT c.email FROM customer c
JOIN address a ON c.address_id = a.address_id
WHERE a.district = 'California';
which has a query plan that involves scanning both the tables that are joined:
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
                              QUERY PLAN
----------------------------------------------------------------------
 Hash Join  (cost=15.65..32.22 rows=9 width=32)
   Hash Cond: (c.address_id = a.address_id)
   ->  Seq Scan on customer c  (cost=0.00..14.99 rows=599 width=34)
   ->  Hash  (cost=15.54..15.54 rows=9 width=4)
         ->  Seq Scan on address a  (cost=0.00..15.54 rows=9 width=4)
               Filter: (district = 'California'::text)
(6 rows)

អ្វីដែលសន្ទស្សន៍ធម្មតានឹងផ្តល់ឱ្យយើង:

pagila=# CREATE INDEX idx_address1 ON address(district);
CREATE INDEX
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Hash Join  (cost=12.98..29.55 rows=9 width=32)
   Hash Cond: (c.address_id = a.address_id)
   ->  Seq Scan on customer c  (cost=0.00..14.99 rows=599 width=34)
   ->  Hash  (cost=12.87..12.87 rows=9 width=4)
         ->  Bitmap Heap Scan on address a  (cost=4.34..12.87 rows=9 width=4)
               Recheck Cond: (district = 'California'::text)
               ->  Bitmap Index Scan on idx_address1  (cost=0.00..4.34 rows=9 width=0)
                     Index Cond: (district = 'California'::text)
(8 rows)

ស្កេន address ត្រូវបានជំនួសដោយការស្កេនសន្ទស្សន៍ idx_address1ហើយបន្ទាប់មកបានស្កេនគំនរ address.

ដោយសារនេះជាសំណួរញឹកញាប់ ហើយត្រូវការធ្វើឱ្យប្រសើរ យើងអាចប្រើលិបិក្រមមួយផ្នែក ដែលធ្វើលិបិក្រមតែជួរទាំងនោះដែលមានអាសយដ្ឋាននៅក្នុងស្រុក។ ‘California’:

pagila=# CREATE INDEX idx_address2 ON address(address_id) WHERE district='California';
CREATE INDEX
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Hash Join  (cost=12.38..28.96 rows=9 width=32)
   Hash Cond: (c.address_id = a.address_id)
   ->  Seq Scan on customer c  (cost=0.00..14.99 rows=599 width=34)
   ->  Hash  (cost=12.27..12.27 rows=9 width=4)
         ->  Index Only Scan using idx_address2 on address a  (cost=0.14..12.27 rows=9 width=4)
(5 rows)

ឥឡូវនេះសំណួរគ្រាន់តែអាន idx_address2 និងមិនប៉ះតុ address.

ការប្រើលិបិក្រមពហុតម្លៃ

ជួរ​ឈរ​មួយ​ចំនួន​ដែល​ត្រូវ​ធ្វើ​លិបិក្រម​អាច​នឹង​មិន​មាន​ប្រភេទ​ទិន្នន័យ​មាត្រដ្ឋាន​ទេ។ ប្រភេទជួរឈរដូចជា jsonb, arrays и tsvector មានសមាសធាតុ ឬតម្លៃច្រើន។ ប្រសិនបើអ្នកត្រូវការធ្វើលិបិក្រមជួរឈរបែបនេះ ជាធម្មតាអ្នកត្រូវស្វែងរកតាមតម្លៃនីមួយៗនៅក្នុងជួរឈរទាំងនោះ។

ចូរយើងព្យាយាមស្វែងរកចំណងជើងនៃខ្សែភាពយន្តទាំងអស់ដែលមានការកាត់ចេញពីការថតដែលមិនជោគជ័យ។ តុ film មានជួរឈរអត្ថបទមួយហៅថា special_features. ប្រសិនបើភាពយន្តមាន "លក្ខណៈសម្បត្តិពិសេស" នេះ នោះជួរឈរមានធាតុជាអារេអត្ថបទ Behind The Scenes. ដើម្បីស្វែងរកខ្សែភាពយន្តបែបនេះទាំងអស់ យើងត្រូវជ្រើសរើសជួរទាំងអស់ដែលមាន "Behind The Scenes" នៅពេលនោះ។ ណាមួយ តម្លៃអារេ special_features:

SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';

ប្រតិបត្តិករសំបុក @> ពិនិត្យមើលថាតើផ្នែកខាងស្តាំគឺជាផ្នែករងនៃផ្នែកខាងឆ្វេង។

ស្នើសុំគម្រោង៖

pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
                           QUERY PLAN
-----------------------------------------------------------------
 Seq Scan on film  (cost=0.00..67.50 rows=5 width=15)
   Filter: (special_features @> '{"Behind The Scenes"}'::text[])
(2 rows)

ដែលស្នើសុំការស្កេនពេញដោយចំណាយ 67 ។

តោះមើលថាតើសន្ទស្សន៍ B-tree ធម្មតាជួយយើងទេ៖

pagila=# CREATE INDEX idx_film1 ON film(special_features);
CREATE INDEX
pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
                           QUERY PLAN
-----------------------------------------------------------------
 Seq Scan on film  (cost=0.00..67.50 rows=5 width=15)
   Filter: (special_features @> '{"Behind The Scenes"}'::text[])
(2 rows)

លិបិក្រមមិនត្រូវបានគេគិតសូម្បីតែ។ សន្ទស្សន៍ B-tree មិនដឹងពីអត្ថិភាពនៃធាតុនីមួយៗនៅក្នុងតម្លៃដែលបានធ្វើលិបិក្រមទេ។

យើងត្រូវការសន្ទស្សន៍ GIN ។

pagila=# CREATE INDEX idx_film2 ON film USING GIN(special_features);
CREATE INDEX
pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
                                QUERY PLAN
---------------------------------------------------------------------------
 Bitmap Heap Scan on film  (cost=8.04..23.58 rows=5 width=15)
   Recheck Cond: (special_features @> '{"Behind The Scenes"}'::text[])
   ->  Bitmap Index Scan on idx_film2  (cost=0.00..8.04 rows=5 width=0)
         Index Cond: (special_features @> '{"Behind The Scenes"}'::text[])
(4 rows)

សន្ទស្សន៍ GIN គាំទ្រការគូសផែនទីតម្លៃតែមួយប្រឆាំងនឹងតម្លៃសមាសធាតុដែលបានធ្វើលិបិក្រម ដែលបណ្តាលឱ្យមានការចំណាយលើគម្រោងសំណួរដែលមានច្រើនជាងពាក់កណ្តាល។

កម្ចាត់លិបិក្រមស្ទួន

សន្ទស្សន៍​កកកុញ​តាម​ពេល​វេលា ហើយ​ពេល​ខ្លះ​សន្ទស្សន៍​ថ្មី​អាច​មាន​និយមន័យ​ដូច​គ្នា​នឹង​សន្ទស្សន៍​មួយ​ក្នុង​ចំណោម​សន្ទស្សន៍​មុនៗ។ អ្នកអាចប្រើទិដ្ឋភាពកាតាឡុកដើម្បីទទួលបាននិយមន័យ SQL ដែលអាចអានបានរបស់មនុស្សនៃលិបិក្រម។ pg_indexes. អ្នកក៏អាចស្វែងរកនិយមន័យដូចគ្នាបានយ៉ាងងាយស្រួលផងដែរ៖

 SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
    FROM pg_indexes
GROUP BY defn
  HAVING count(*) > 1;
And here’s the result when run on the stock pagila database:
pagila=#   SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
pagila-#     FROM pg_indexes
pagila-# GROUP BY defn
pagila-#   HAVING count(*) > 1;
                                indexes                                 |                                defn
------------------------------------------------------------------------+------------------------------------------------------------------
 {payment_p2017_01_customer_id_idx,idx_fk_payment_p2017_01_customer_id} | CREATE INDEX  ON public.payment_p2017_01 USING btree (customer_id
 {payment_p2017_02_customer_id_idx,idx_fk_payment_p2017_02_customer_id} | CREATE INDEX  ON public.payment_p2017_02 USING btree (customer_id
 {payment_p2017_03_customer_id_idx,idx_fk_payment_p2017_03_customer_id} | CREATE INDEX  ON public.payment_p2017_03 USING btree (customer_id
 {idx_fk_payment_p2017_04_customer_id,payment_p2017_04_customer_id_idx} | CREATE INDEX  ON public.payment_p2017_04 USING btree (customer_id
 {payment_p2017_05_customer_id_idx,idx_fk_payment_p2017_05_customer_id} | CREATE INDEX  ON public.payment_p2017_05 USING btree (customer_id
 {idx_fk_payment_p2017_06_customer_id,payment_p2017_06_customer_id_idx} | CREATE INDEX  ON public.payment_p2017_06 USING btree (customer_id
(6 rows)

Superset Index

វាអាចកើតឡើងដែលអ្នកបញ្ចប់ដោយលិបិក្រមជាច្រើន ដែលមួយក្នុងចំណោមនោះធ្វើលិបិក្រមសំណុំលើសនៃជួរឈរដែលធ្វើលិបិក្រមផ្សេងទៀត។ នេះអាចឬប្រហែលជាមិនចង់បាន - សំណុំខាងលើអាចបណ្តាលឱ្យមានការស្កេនសម្រាប់តែលិបិក្រម ដែលជាការល្អ ប៉ុន្តែវាប្រហែលជាត្រូវការទំហំផ្ទុកច្រើនពេក ឬសំណួរដែលសំណុំខាងលើមានបំណងធ្វើឱ្យប្រសើរមិនត្រូវបានប្រើប្រាស់ទៀតទេ។

ប្រសិនបើអ្នកត្រូវការស្វ័យប្រវត្តិកម្មនិយមន័យនៃលិបិក្រមបែបនេះ អ្នកអាចចាប់ផ្តើមជាមួយ pg_index ពីតុ pg_catalog.

លិបិក្រមដែលមិនប្រើ

ដោយសារកម្មវិធីដែលប្រើមូលដ្ឋានទិន្នន័យវិវឌ្ឍន៍ ដូច្នេះសូមធ្វើសំណួរដែលពួកគេប្រើ។ សន្ទស្សន៍​ដែល​បាន​បន្ថែម​មុន​អាច​នឹង​មិន​ត្រូវ​បាន​ប្រើ​ដោយ​សំណួរ​ណា​មួយ​ទៀត​ទេ។ រាល់ពេលដែលសន្ទស្សន៍ត្រូវបានស្កេន វាត្រូវបានសម្គាល់ដោយអ្នកគ្រប់គ្រងស្ថិតិ និងនៅក្នុងទិដ្ឋភាពកាតាឡុកប្រព័ន្ធ pg_stat_user_indexes អ្នកអាចឃើញតម្លៃ idx_scanដែល​ជា​ការ​រាប់​បន្ថែម។ ការតាមដានតម្លៃនេះក្នុងរយៈពេលមួយ (និយាយមួយខែ) នឹងផ្តល់គំនិតល្អអំពីសន្ទស្សន៍ណាមួយដែលមិនត្រូវបានគេប្រើប្រាស់ ហើយអាចត្រូវបានទម្លាក់។

នេះគឺជាសំណួរដើម្បីទទួលបានចំនួនស្កេនបច្ចុប្បន្ននៃសន្ទស្សន៍ទាំងអស់នៅក្នុងគ្រោងការណ៍ ‘public’:

SELECT relname, indexrelname, idx_scan
FROM   pg_catalog.pg_stat_user_indexes
WHERE  schemaname = 'public';
with output like this:
pagila=# SELECT relname, indexrelname, idx_scan
pagila-# FROM   pg_catalog.pg_stat_user_indexes
pagila-# WHERE  schemaname = 'public'
pagila-# LIMIT  10;
    relname    |    indexrelname    | idx_scan
---------------+--------------------+----------
 customer      | customer_pkey      |    32093
 actor         | actor_pkey         |     5462
 address       | address_pkey       |      660
 category      | category_pkey      |     1000
 city          | city_pkey          |      609
 country       | country_pkey       |      604
 film_actor    | film_actor_pkey    |        0
 film_category | film_category_pkey |        0
 film          | film_pkey          |    11043
 inventory     | inventory_pkey     |    16048
(10 rows)

ការបង្កើតលិបិក្រមឡើងវិញជាមួយនឹងការចាក់សោតិចជាង

ជារឿយៗសន្ទស្សន៍ត្រូវបង្កើតឡើងវិញ ឧទាហរណ៍នៅពេលដែលវាហើមពោះ ហើយការស្ថាបនាឡើងវិញអាចបង្កើនល្បឿនស្កេន។ សន្ទស្សន៍ក៏អាចខូចផងដែរ។ ការផ្លាស់ប្តូរប៉ារ៉ាម៉ែត្រលិបិក្រមក៏អាចតម្រូវឱ្យមានការកសាងវាឡើងវិញផងដែរ។

បើកការបង្កើតលិបិក្រមប៉ារ៉ាឡែល

នៅក្នុង PostgreSQL 11 ការបង្កើតសន្ទស្សន៍ B-Tree គឺស្របគ្នា។ ដើម្បីបង្កើនល្បឿនដំណើរការបង្កើត កម្មករប៉ារ៉ាឡែលជាច្រើនអាចត្រូវបានប្រើ។ ទោះយ៉ាងណាក៏ដោយ ត្រូវប្រាកដថាជម្រើសនៃការកំណត់រចនាសម្ព័ន្ធទាំងនេះត្រូវបានកំណត់យ៉ាងត្រឹមត្រូវ៖

SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;

តម្លៃលំនាំដើមគឺតូចពេក។ តាមឧត្ដមគតិ លេខទាំងនេះគួរតែកើនឡើង រួមជាមួយនឹងចំនួនស្នូលដំណើរការ។ អានបន្ថែមនៅក្នុង ឯកសារ.

ការបង្កើតលិបិក្រមផ្ទៃខាងក្រោយ

អ្នកអាចបង្កើតលិបិក្រមក្នុងផ្ទៃខាងក្រោយដោយប្រើជម្រើស CONCURRENTLY ពាក្យបញ្ជា CREATE INDEX:

pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEX

ដំណើរការបង្កើតលិបិក្រមនេះខុសពីធម្មតា ដែលវាមិនតម្រូវឱ្យមានការចាក់សោរនៅលើតុ ហើយដូច្នេះវាមិនរារាំងប្រតិបត្តិការសរសេរទេ។ ម្យ៉ាងវិញទៀត វាត្រូវការពេលវេលា និងប្រើប្រាស់ធនធានកាន់តែច្រើន។

Postgres ផ្តល់នូវភាពបត់បែនជាច្រើនសម្រាប់ការបង្កើតលិបិក្រម និងវិធីដើម្បីដោះស្រាយករណីពិសេសណាមួយ ក៏ដូចជាវិធីដើម្បីគ្រប់គ្រងមូលដ្ឋានទិន្នន័យក្នុងករណីដែលកម្មវិធីរបស់អ្នករីកចម្រើនខ្លាំង។ យើងសង្ឃឹមថាគន្លឹះទាំងនេះនឹងជួយអ្នកឱ្យទទួលបានសំណួររបស់អ្នកយ៉ាងឆាប់រហ័ស ហើយមូលដ្ឋានទិន្នន័យរបស់អ្នករួចរាល់ដើម្បីធ្វើមាត្រដ្ឋាន។

ប្រភព: www.habr.com