Index និង Query Performance

មាតិកា

  1. ហេតុអ្វី Query ក្លាយជាយឺត
  2. Scratch Table ដែលមាន Row កន្លះលាន
  3. ការអាន EXPLAIN
  4. EXPLAIN ANALYZE៖ ពេលវេលាពិត
  5. របៀបដែល B-Tree Index ដំណើរការ
  6. ការបង្កើត Index
  7. Unique Index និង Primary Key
  8. Composite Index និងលំដាប់ Column
  9. Partial Index
  10. Foreign Key មិនត្រូវបាន Index ដោយស្វ័យប្រវត្តិទេ
  11. ពេលដែល Index មិនត្រូវបានប្រើ
  12. Index មិនមែនឥតគិតថ្លៃទេ
  13. បញ្ហា N+1 Query
  14. សាកល្បងដោយខ្លួនឯង

1. ហេតុអ្វី Query ក្លាយជាយឺត

អ្វីៗទាំងអស់មកដល់ពេលនេះដំណើរការភ្លាមៗ ព្រោះ shopdb មាន order ប្រាំ។ query យឺតមើលមិនឃើញលើទិន្នន័យតូច។ table ដែលមានមួយលាន row គឺជារឿងផ្សេងទាំងស្រុង។

នៅពេលអ្នកសួរ WHERE customer_id = 42 តើ PostgreSQL រក row ដែលត្រូវគ្នាដោយរបៀបណា? ដោយគ្មានព័ត៌មានផ្សេងទៀត វាមានជម្រើសតែមួយគត់៖ អាន គ្រប់ row ក្នុង table ហើយពិនិត្យនីមួយៗ។ នេះគឺជា sequential scan (seq scan)។ ប្រសិនបើ table មាន n row នោះគឺជាការងារ O(n) ពិតជាការលូតលាស់ linear ដូចក្នុង សញ្ញា Big-O។ row ច្រើនជាងដប់ដង មានន័យថាពេលវេលាច្រើនជាងដប់ដង។

Index គឺជារចនាសម្ព័ន្ធដាច់ដោយឡែកមួយដែលបានតម្រៀប ដែលអនុញ្ញាតឱ្យមូលដ្ឋានទិន្នន័យលោតត្រង់ទៅ row ដែលត្រូវគ្នា។ ជំនួសឱ្យ O(n) ការរកមើលប្រើជំហានប្រហែល O(log n) ដែលជាល្បិចដូចគ្នានឹង binary search។ សម្រាប់មួយលាន row នោះគឺប្រហែល 20 ជំហានជំនួសឱ្យមួយលាន។

ជំពូកនេះបង្ហាញភាពខុសគ្នាជាមួយទិន្នន័យពិត ដើម្បីឱ្យអ្នកឃើញដោយខ្លួនឯង ជំនួសឱ្យការជឿពាក្យរបស់យើង។

2. Scratch Table ដែលមាន Row កន្លះលាន

កុំពិសោធលើ shopdb ផ្ទាល់។ បង្កើត scratch table ដាច់ដោយឡែក ដែលបំពេញដោយ order ក្លែងក្លាយ។ PostgreSQL មាន function ក្នុងខ្លួន generate_series ដែលបង្កើតលំដាប់លេខ។ ផ្សំជាមួយ random() វាអាចបង្កើតទិន្នន័យបានច្រើនតាមដែលអ្នកចង់បាន៖

CREATE TABLE scratch_orders AS
SELECT
  g                                         AS id,
  1 + floor(random() * 10000)::int          AS customer_id,
  (ARRAY['pending','paid','shipped','cancelled'])
    [1 + floor(random() * 4)::int]          AS status,
  now() - random() * 365 * interval '1 day' AS ordered_at,
  round((random() * 500)::numeric, 2)       AS total
FROM generate_series(1, 500000) AS g;

ANALYZE scratch_orders;

ជំហានម្តងមួយៗ៖

  • generate_series(1, 500000) ផ្តល់ row 500,000 មួយ row ក្នុងមួយលេខ។
  • floor(random() * 10000) បង្កើតអតិថិជនចៃដន្យពី 1 ដល់ 10,000 ដូច្នេះអតិថិជននីមួយៗមាន order ប្រហែល 50។
  • ល្បិច ARRAY[...] ជ្រើសស្ថានភាពមួយក្នុងចំណោមបួនដោយចៃដន្យ។
  • random() * 365 * interval '1 day' ថយក្រោយក្នុងពេលវេលាបានរហូតដល់មួយឆ្នាំ។
  • ANALYZE ប្រាប់ PostgreSQL ឱ្យប្រមូលស្ថិតិអំពី table ដើម្បីឱ្យ planner របស់វាធ្វើការសម្រេចចិត្តបានល្អ។

វាចំណាយពេលមួយទៅពីរវិនាទី។ ពិនិត្យវា៖

SELECT count(*) FROM scratch_orders;

 count
--------
 500000
(1 row)

ដោយសារទិន្នន័យចៃដន្យ លេខពិតប្រាកដរបស់អ្នកខាងក្រោមនឹងខុសពីរបស់យើង។ រូបរាង នៃលទ្ធផលគឺជាអ្វីដែលសំខាន់។

3. ការអាន EXPLAIN

ដាក់ EXPLAIN នៅមុខ query ណាមួយ ហើយ PostgreSQL បង្ហាញ plan របស់វា ជាជំហានដែលវាមានបំណងធ្វើ ដោយមិនដំណើរការវាទេ៖

EXPLAIN
SELECT * FROM scratch_orders WHERE customer_id = 4242;

លទ្ធផលធម្មតា (លេខនឹងប្រែប្រួល)៖

                          QUERY PLAN
--------------------------------------------------------------
 Gather  (cost=1000.00..8200.00 rows=50 width=33)
   Workers Planned: 2
   ->  Parallel Seq Scan on scratch_orders
         (cost=0.00..7195.00 rows=21 width=33)
         Filter: (customer_id = 4242)

របៀបអាន៖

ផ្នែកអត្ថន័យ
Seq Scanអាន table ទាំងមូលម្តងមួយ row
Parallel / GatherPostgres បានបំបែកការ scan ទៅ worker process ជាច្រើន។ ធម្មតាសម្រាប់ table ធំ
cost=0.00..7195.00ការប៉ាន់ស្មានរបស់ planner ជាឯកតាអរូបី៖ startup cost បន្ទាប់មក total cost។ ប្រើវាដើម្បីប្រៀបធៀប plan កុំអានវាជាមីលីវិនាទី
rows=21ចំនួន row ប៉ាន់ស្មានដែលជំហាននេះត្រឡប់
width=33ទំហំ row ជាមធ្យមប៉ាន់ស្មានជា byte
Filter:លក្ខខណ្ឌ WHERE ដែលពិនិត្យលើគ្រប់ row

Plan អានពីបន្ទាត់ ក្នុងបំផុត ដែលចូលបន្ទាត់ច្រើនបំផុត ទៅខាងក្រៅ។ នៅទីនេះ ការ scan គឺជាការងារពិត ហើយ Gather ប្រមូលលទ្ធផល។

4. EXPLAIN ANALYZE៖ ពេលវេលាពិត

EXPLAIN គ្រាន់តែប៉ាន់ស្មាន។ EXPLAIN ANALYZE ដំណើរការ query ពិតៗ ហើយរាយការណ៍អ្វីដែលបានកើតឡើងពិតប្រាកដ៖

EXPLAIN ANALYZE
SELECT * FROM scratch_orders WHERE customer_id = 4242;

លទ្ធផលធម្មតា៖

 Gather  (cost=1000.00..8200.00 rows=50 width=33)
         (actual time=1.8..38.5 rows=48 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on scratch_orders
         (cost=0.00..7195.00 rows=21 width=33)
         (actual time=4.0..34.2 rows=16 loops=3)
         Filter: (customer_id = 4242)
         Rows Removed by Filter: 166651
 Planning Time: 0.1 ms
 Execution Time: 39.0 ms

ប្រហែល 40 ms លើកុំព្យូទ័រយួរដៃធម្មតា (របស់អ្នកអាចជា 15 ឬ 100)។ បន្ទាត់សំខាន់ៗ៖

  • actual time=... ជាមីលីវិនាទីពិត។
  • rows=48 ជាចំនួនពិត នៅក្បែរការប៉ាន់ស្មាន 50។ នៅពេលការប៉ាន់ស្មាន និងការពិតខុសគ្នាខ្លាំង សូមដំណើរការ ANALYZE លើ table។
  • Rows Removed by Filter: 166651 គឺជាសញ្ញាអាក្រក់។ PostgreSQL បានអានប្រហែលកន្លះលាន row (នេះគឺក្នុងមួយ worker ឆ្លងកាត់បី loop) ដើម្បីត្រឡប់ 48។ ការងារស្ទើរតែទាំងអស់ត្រូវខ្ជះខ្ជាយ។

ការព្រមានមួយ៖ EXPLAIN ANALYZE ដំណើរការ statement ពិតៗ។ សម្រាប់ SELECT នោះគ្មានគ្រោះថ្នាក់ទេ។ សម្រាប់ UPDATE ឬ DELETE វាផ្លាស់ប្តូរទិន្នន័យ ដូច្នេះរុំវាក្នុង transaction ហើយ rollback (ជំពូកទី 12) ឬគ្រាន់តែប្រើ EXPLAIN ធម្មតា។

5. របៀបដែល B-Tree Index ដំណើរការ

Index លំនាំដើមក្នុង PostgreSQL គឺ B-tree។ គិតអំពី index នៅខាងក្រោយសៀវភៅសិក្សា៖ ពាក្យដែលបានតម្រៀប ដែលនីមួយៗមានលេខទំព័រ។ អ្នកមិនអានសៀវភៅទាំងមូលដើម្បីរក "foreign key" ទេ។ អ្នកបើកទៅ index ដែលបានតម្រៀប រកវាបានលឿន ហើយលោតទៅទំព័រនោះ។

B-tree គឺជាគំនិតនោះ ដែលរៀបចំជាដើមឈើនៃទំព័រដែលបានតម្រៀប៖

                    [ 2500 | 5000 | 7500 ]            <- root page
                   /        |        |       \
         [ ... ]       [ ... ]   [ ... ]    [ ... ]   <- middle pages
          /   \          ...
   [leaf: 4240 -> row pointers]
   [leaf: 4241 -> row pointers]
   [leaf: 4242 -> (row 17), (row 903), (row 12044)...] <- leaf pages

ដើម្បីរក 4242 ចាប់ផ្តើមពី root តាមមែកមួយឆ្ពោះទៅជួរត្រឹមត្រូវ បន្ទាប់មកមួយទៀត ហើយទៅដល់ leaf ដែលផ្ទុក key ជាមួយ pointer ទៅ row ក្នុង table ដែលត្រូវគ្នា។ ទំព័រនីមួយៗផ្ទុក key រាប់រយ ដូច្នេះដើមឈើរាបស្មើណាស់៖ ជាធម្មតា 3 ឬ 4 កម្រិត សូម្បីតែសម្រាប់ row រាប់រយលាន។ នោះគឺជា O(log n) ពី Big-O ជាមួយមូលដ្ឋាន (base) ដ៏ធំនៃ logarithm។

ដោយសារ leaf ត្រូវបានតម្រៀប B-tree ក៏គ្រប់គ្រង <, >, BETWEEN និង ORDER BY បានល្អដែរ មិនមែនតែ = ទេ។

6. ការបង្កើត Index

CREATE INDEX idx_scratch_orders_customer_id
  ON scratch_orders (customer_id);

លើ row កន្លះលាន វាចំណាយពេលបន្តិច។ ឥឡូវដំណើរការ query ដដែលម្តងទៀត៖

EXPLAIN ANALYZE
SELECT * FROM scratch_orders WHERE customer_id = 4242;

លទ្ធផលធម្មតា៖

 Bitmap Heap Scan on scratch_orders
     (cost=5.9..200.0 rows=50 width=33)
     (actual time=0.05..0.20 rows=48 loops=1)
   Recheck Cond: (customer_id = 4242)
   Heap Blocks: exact=47
   ->  Bitmap Index Scan on idx_scratch_orders_customer_id
         (cost=0.00..5.9 rows=50 width=0)
         (actual time=0.03..0.03 rows=48 loops=1)
         Index Cond: (customer_id = 4242)
 Planning Time: 0.2 ms
 Execution Time: 0.3 ms

ប្រហែល 39 ms ក្លាយជាប្រហែល 0.3 ms លឿនជាងមួយរយដង ហើយគម្លាតកើនឡើងជាមួយ table។ ឥឡូវ plan និយាយថា Index Cond ជំនួសឱ្យ Filter៖ PostgreSQL បានប្រើ index ដើម្បីរក row ជំនួសឱ្យការសាកល្បងទាំងអស់។ អ្នកអាចឃើញ Index Scan ជំនួសឱ្យ Bitmap Heap Scan ដោយអាស្រ័យលើចំនួន row ដែលត្រូវគ្នា។ ទាំងពីរមានន័យថា index ត្រូវបានប្រើ។

planner សម្រេចថាពេលណាត្រូវប្រើ index។ អ្នកមិនសរសេរ "ប្រើ index នេះ" ក្នុង query ទេ។ អ្នកបង្កើត index ហើយ planner ជ្រើសវានៅពេលវាថោកជាង seq scan។

ឈ្មោះ idx_<table>_<column> គឺជាអនុសញ្ញាដែលធ្វើឱ្យលទ្ធផល \d ងាយអាន។

7. Unique Index និង Primary Key

អ្នកកំពុងបង្កើត index ពេញពេលដោយមិនដឹង។ PRIMARY KEY និង UNIQUE constraint នីមួយៗបង្កើត unique B-tree index ដោយស្វ័យប្រវត្តិ។ នោះជារបៀបដែល PostgreSQL ពិនិត្យបានលឿនថាគ្មានស្ទួននៅពេលអ្នក insert។ មើល customers៖

shopdb=# \d customers
...
Indexes:
    "customers_pkey" PRIMARY KEY, btree (id)
    "customers_email_key" UNIQUE CONSTRAINT, btree (email)

ដូច្នេះ WHERE id = 3 និង WHERE email = 'linda@example.com' លឿនរួចហើយ។ បន្ថែម primary key ទៅ scratch table ដើម្បីមើលវា៖

ALTER TABLE scratch_orders ADD PRIMARY KEY (id);

អ្នកក៏អាចបង្កើត unique index ដោយផ្ទាល់ (CREATE UNIQUE INDEX ...)។ វាស្មើនឹង UNIQUE constraint សម្រាប់ការប្រើប្រាស់ភាគច្រើន។

8. Composite Index និងលំដាប់ Column

Index អាចគ្របដណ្តប់ច្រើនជាងមួយ column។ ឧបមាថាទំព័រមួយសួរញឹកញាប់ថា៖ "order ដែលបានបង់ប្រាក់របស់អតិថិជន 4242 ថ្មីបំផុតមុន"។

CREATE INDEX idx_scratch_orders_customer_status_date
  ON scratch_orders (customer_id, status, ordered_at);

composite B-tree ត្រូវបានតម្រៀបតាម column ទីមួយ បន្ទាប់មកតាមទីពីរក្នុងចំណោមតម្លៃទីមួយដូចគ្នា បន្ទាប់មកទីបី។ គិតអំពីសៀវភៅទូរស័ព្ទដែលតម្រៀបតាមនាមត្រកូល បន្ទាប់មកនាមខ្លួន។ លទ្ធផលគឺ ច្បាប់ leftmost prefix៖

Query filter លើតើអាចប្រើ index បានទេ?
customer_idបាន
customer_id និង statusបាន
customer_id, status, ordered_atបាន ករណីល្អបំផុត
status តែឯងខ្សោយ។ មិនមែន column ឆ្វេងបំផុត
ordered_at តែឯងខ្សោយ។ ហេតុផលដូចគ្នា

សៀវភៅទូរស័ព្ទជួយអ្នករក "Chan" ឬ "Chan, Sokha"។ វាមិនជួយអ្នករកអ្នករាល់គ្នាដែលឈ្មោះ "Sokha" នៅក្នុងត្រកូលណាក៏បានទេ។ ដូច្នេះ លំដាប់ column សំខាន់៖ ដាក់ column ដែលអ្នក filter ដោយ equality ញឹកញាប់បំផុតមុន ហើយ column ជួរ (range) ឬតម្រៀបនៅចុងក្រោយ។

composite index ជារឿយៗអាចជំនួស index column តែមួយបាន។ index នេះក៏បម្រើ WHERE customer_id = ... ដែរ ដូច្នេះ index column តែមួយមុនឥឡូវនេះលែងចាំបាច់ ហើយអ្នកអាចលុបវាដើម្បីសន្សំទំហំ។

9. Partial Index

ពេលខ្លះអ្នក query តែចំណែកតូចមួយនៃទិន្នន័យប៉ុណ្ណោះ។ Partial index រួមបញ្ចូលតែ row ដែលត្រូវនឹងលក្ខខណ្ឌមួយ ដែលធ្វើឱ្យវាតូចជាង និងលឿនជាង៖

CREATE INDEX idx_scratch_orders_pending
  ON scratch_orders (ordered_at)
  WHERE status = 'pending';

index នេះមានតែ order ដែលកំពុងរង់ចាំ (pending) ប៉ុណ្ណោះ (ប្រហែលមួយភាគបួននៃ row នៅទីនេះ តិចជាងនេះទៅទៀតក្នុងហាងពិតដែល order ភាគច្រើនត្រូវបានដឹកជញ្ជូន)។ វាបម្រើ query ដូចជា "order pending ចាស់បំផុត" ប៉ុន្តែលុះត្រាតែ query រួមបញ្ចូលលក្ខខណ្ឌដូចគ្នា៖

SELECT id, ordered_at
FROM scratch_orders
WHERE status = 'pending'
ORDER BY ordered_at
LIMIT 10;

Partial index ក៏ដោះស្រាយបញ្ហា soft-delete ពីជំពូកមុនដែរ៖ UNIQUE index WHERE deleted_at IS NULL អនុវត្តអ៊ីមែលតែមួយគត់ក្នុងចំណោមអ្នកប្រើសកម្មប៉ុណ្ណោះ។

10. Foreign Key មិនត្រូវបាន Index ដោយស្វ័យប្រវត្តិទេ

នេះគឺជាការភ្ញាក់ផ្អើលដែលធ្វើឱ្យអ្នកចាប់ផ្តើមជាច្រើនជាន់ជើង។ នៅពេលអ្នកប្រកាស customer_id REFERENCES customers(id) PostgreSQL ធ្វើ index លើ column ដែល ត្រូវបានយោង (customers.id វាជា primary key)។ វា មិន ធ្វើ index លើ column ដែលយោង (orders.customer_id) ទេ។

ប៉ុន្តែអ្នកនឹង filter និង JOIN លើ foreign key ជាបន្តបន្ទាប់៖ "order ទាំងអស់របស់អតិថិជននេះ" ឬ JOIN orders o ON o.customer_id = c.id។ ដោយគ្មាន index នីមួយៗនៃទាំងនោះ scan table orders ទាំងមូល។ ការលុបអតិថិជនក៏យឺតដែរ ព្រោះ PostgreSQL ត្រូវពិនិត្យគ្រប់ order សម្រាប់ការយោង។

ដូច្នេះក្នុង shopdb សូមបន្ថែម index ដោយខ្លួនឯង។ ដាក់វាក្នុងឯកសារ migration ថ្មី migrations/004_add_fk_indexes.sql៖

CREATE INDEX idx_orders_customer_id      ON orders (customer_id);
CREATE INDEX idx_order_items_product_id  ON order_items (product_id);
CREATE INDEX idx_products_category_id    ON products (category_id);

អ្នកមិនត្រូវការមួយសម្រាប់ order_items.order_id ទេ។ composite primary key (order_id, product_id) ចាប់ផ្តើមដោយ order_id រួចហើយ ដូច្នេះវាគ្របដណ្តប់ការរកមើលតាម order។ អនុវត្តវា៖

docker exec -i shopdb-pg psql -U postgres -d shopdb \
  < migrations/004_add_fk_indexes.sql

ជាមួយ order ប្រាំ planner នឹងបន្តប្រើ seq scan ដដែល ព្រោះការអានទំព័រតូចមួយថោកជាងការដើរកាត់ index។ index ទទួលបានផលនៅពេលទិន្នន័យលូតលាស់។ បន្ថែមវាឥឡូវនេះ ហើយភ្លេចវាទៅ។

11. ពេលដែល Index មិនត្រូវបានប្រើ

ការបង្កើត index មិនធានាថាវាត្រូវបានប្រើទេ។ ទាំងនេះគឺជាមូលហេតុទូទៅ ហើយនីមួយៗសក្តិសមនឹងដឹង។

1. Function លើ column។ index រក្សាតម្លៃ email ដើម។ query នេះមិនអាចប្រើវាបានទេ៖

SELECT * FROM customers WHERE lower(email) = 'dara@example.com';

planner នឹងត្រូវគណនា lower() សម្រាប់គ្រប់ row។ ទាំងប្រៀបធៀប column ដើម ឬសាងសង់ expression index ដែលរក្សាលទ្ធផល៖

CREATE INDEX idx_customers_email_lower ON customers (lower(email));

ដូចគ្នាសម្រាប់ការគណនាលើ column៖ WHERE total * 2 > 100 មិនអាចប្រើ index លើ total បានទេ ប៉ុន្តែ WHERE total > 50 អាច។ ផ្លាស់ទីការគណនាទៅម្ខាងទៀត។

2. Wildcard នៅខាងមុខ។ LIKE 'Sok%' អាចប្រើ B-tree index (វាជាជួរនៃតម្លៃដែលបានតម្រៀប) ប៉ុន្តែ LIKE '%ha' មិនអាចទេ ព្រោះការចាប់ផ្តើមមិនស្គាល់។ ការស្វែងរកអត្ថបទលឿនត្រូវការ tool ផ្សេង (full-text search ឬ extension pg_trgm) មិនមែន B-tree ធម្មតាទេ។

3. Selectivity ទាប។ ប្រសិនបើលក្ខខណ្ឌត្រូវនឹងចំណែកធំនៃ table នោះ seq scan ពិតជាលឿនជាង។ WHERE status = 'paid' ត្រឡប់មួយភាគបួននៃ row ដូច្នេះ planner អាចចូលចិត្តអានអ្វីៗទាំងអស់ជាងការលោតទៅមករវាង index និង table។ នេះគឺជាឥរិយាបថត្រឹមត្រូវ មិនមែន bug ទេ។ Index គឺសម្រាប់លក្ខខណ្ឌ selective ដែលត្រឡប់ row តិច។

4. Table តូច។ សម្រាប់ table ដែលសមក្នុងមួយឬពីរទំព័រ seq scan គឺជា plan ថោកបំផុត។ កុំព្រួយបារម្ភនៅពេល EXPLAIN លើ shopdb មិនអើពើ index របស់អ្នក។

5. ស្ថិតិហួសសម័យ។ បន្ទាប់ពីផ្ទុកទិន្នន័យច្រើន សូមដំណើរការ ANALYZE ដើម្បីឱ្យ planner ដឹងទំហំពិតរបស់ table។ (Autovacuum ធ្វើរឿងនេះនៅផ្ទៃខាងក្រោយ ជាធម្មតា ប៉ុន្តែមិនភ្លាមៗទេ។)

6. Type មិនត្រូវគ្នា រវាង column និងតម្លៃ ឬ implicit cast ក៏អាចរារាំងការប្រើ index ដែរ។ រក្សា type ឱ្យស្របគ្នា។

នៅពេលមានការសង្ស័យ សួរមូលដ្ឋានទិន្នន័យ៖ ដំណើរការ EXPLAIN ហើយអានថាវានិយាយ Seq Scan ឬ Index Scan។

12. Index មិនមែនឥតគិតថ្លៃទេ

ប្រសិនបើ index ធ្វើឱ្យការអានលឿន ហេតុអ្វីមិនធ្វើ index គ្រប់ column? ព្រោះនីមួយៗមានថ្លៃដើម៖

ថ្លៃដើមលម្អិត
ការសរសេរយឺតជាងគ្រប់ INSERT, UPDATE និង DELETE ត្រូវ update គ្រប់ index លើ table ផងដែរ
ទំហំថាសIndex គឺជាច្បាប់ចម្លងនៃទិន្នន័យ column ក្នុងទម្រង់តម្រៀប។ ច្រើនអាចលើសទំហំ table
ការថែទាំការងារច្រើនជាងសម្រាប់ vacuum, backup និង restore
ការខិតខំរបស់ plannerជម្រើសច្រើនជាងដែលត្រូវពិចារណា

មើលទំហំដោយខ្លួនឯង៖

SELECT pg_size_pretty(pg_relation_size('scratch_orders'))  AS table_size,
       pg_size_pretty(pg_relation_size(
         'idx_scratch_orders_customer_id'))                AS index_size;

 table_size | index_size
------------+------------
 29 MB      | 11 MB
(1 row)

(តួលេខធម្មតា របស់អ្នកនឹងខុស។) index នៅទីនេះគឺមួយភាគបីនៃ table សម្រាប់តែមួយ column។

វិធីសាស្ត្រសមហេតុផល៖

  1. Index primary key (ស្វ័យប្រវត្តិ) និង foreign key។
  2. Index column ដែលលេចឡើងក្នុង WHERE, JOIN និង ORDER BY នៃ query ដែលអ្នកដំណើរការញឹកញាប់ ហើយ ត្រឡប់ row តិច។
  3. កុំស្មាន។ រក query យឺត ដំណើរការ EXPLAIN ANALYZE បន្ថែម index មួយ វាស់ម្តងទៀត។
  4. លុប index ដែលគ្មាននរណាប្រើ។ PostgreSQL តាមដានរឿងនេះក្នុង view pg_stat_user_indexes (idx_scan = 0 មានន័យថាមិនដែលប្រើ)។

វាស់ កុំស្មាន។ នេះគឺជាច្បាប់ដូចគ្នានឹងក្នុង Big-O៖ យល់ពីរូបរាង បន្ទាប់មកពិនិត្យជាមួយលេខពិត។

13. បញ្ហា N+1 Query

អន្ទាក់ដំណើរការមួយទៀតមិនទាក់ទងនឹង index ទាល់តែសោះ។ វាលេចឡើងនៅពេលកូដកម្មវិធីនិយាយជាមួយមូលដ្ឋានទិន្នន័យ។ ស្រមៃទំព័រមួយដែលរាយអតិថិជនជាមួយ order របស់ពួកគេ។ កម្មវិធីឆោតល្ងង់ធ្វើដូចនេះ៖

const customers = await db.query("SELECT * FROM customers");   // 1 query

for (const c of customers.rows) {
  const orders = await db.query(
    "SELECT * FROM orders WHERE customer_id = $1", [c.id]      // N query
  );
  console.log(c.name, orders.rows.length);
}

query មួយដើម្បីទទួលអតិថិជន N នាក់ បន្ទាប់មកមួយទៀតក្នុងមួយអតិថិជន៖ N + 1 query។ ជាមួយអតិថិជន 5 នាក់ នោះគឺជា round trip 6 ហើយគ្មាននរណាកត់សម្គាល់ទេ។ ជាមួយអតិថិជន 5,000 នាក់ វាគឺជាដំណើរ 5,001 ឆ្លងកាត់បណ្តាញ ដែលនីមួយៗមាន overhead ផ្ទាល់ខ្លួន ហើយទំព័រចំណាយពេលរាប់វិនាទី។ ORM ធ្វើឱ្យកំហុសនេះងាយសរសេរដោយចៃដន្យ។

ដំណោះស្រាយគឺឱ្យមូលដ្ឋានទិន្នន័យធ្វើការងារ ដោយ JOIN និង aggregate ក្នុង query មួយ៖

SELECT c.name, count(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id;

    name    | orders
------------+--------
 Sokha Chan |      2
 Dara Kim   |      2
 Linda Vong |      1
 Vuthy Sok  |      0
 Maly Ros   |      0
(5 rows)

នៅពេលណាអ្នកឃើញ query នៅក្នុង loop សូមសួរថា JOIN ឬបញ្ជី IN (...) អាចទាញយកអ្វីៗទាំងអស់ក្នុងពេលតែមួយបានឬទេ។

ចុងក្រោយ សម្អាត scratch table ដើម្បីកុំឱ្យវាកាន់កាប់ទំហំ៖

DROP TABLE scratch_orders;

14. សាកល្បងដោយខ្លួនឯង

  1. មានអារម្មណ៍ពីភាពខុសគ្នា។ សាងសង់ scratch_orders ឡើងវិញ (លុបវាមុន) ដំណើរការ lookup ជាមួយ EXPLAIN ANALYZE មុន និងក្រោយការបង្កើត index លើ customer_id។ កត់ត្រាពេលវេលា execution ទាំងពីរ និងឈ្មោះ plan node សម្រាប់នីមួយៗ។
  2. ធ្វើមាត្រដ្ឋាន។ សាងសង់ឡើងវិញជាមួយ 100,000 row បន្ទាប់មក 2,000,000។ វាស់ query ដែលគ្មាន index ម្តងៗ។ តើពេលវេលាកើនឡើងប្រហែលសមាមាត្រនឹងទំហំ ដូចដែល O(n) ទស្សន៍ទាយឬទេ? ចុះ query ដែលមាន index?
  3. លំដាប់ Composite។ បង្កើត index លើ (status, customer_id) និងមួយទៀតលើ (customer_id, status)។ សម្រាប់ WHERE customer_id = 4242 AND status = 'paid' ហើយបន្ទាប់មកសម្រាប់ WHERE status = 'paid' តែឯង តើ EXPLAIN ជ្រើស index មួយណា? ពន្យល់ហេតុអ្វី។
  4. បំផ្លាញ index។ ធ្វើ index លើ ordered_at។ បង្ហាញ query លើជួរកាលបរិច្ឆេទដែលប្រើវា បន្ទាប់មកសរសេរ filter ឡើងវិញដោយមាន function លើ column (ឧទាហរណ៍ WHERE date_trunc('day', ordered_at) = '2026-01-05') ហើយមើល plan ផ្លាស់ប្តូរ។ តើអ្នកអាចសរសេរវាឡើងវិញដើម្បីប្រើ index ម្តងទៀតដោយរបៀបណា?
  5. ពិនិត្យ foreign key។ អនុវត្ត 004_add_fk_indexes.sql លើ shopdb បន្ទាប់មកដំណើរការ \d orders និង \d order_items។ រាយ index ទាំងអស់ដែលបង្ហាញ និង constraint ឬ migration ណាបានបង្កើតវា។
  6. រក index ដែលខ្ជះខ្ជាយ។ បង្កើត index គ្មានប្រយោជន៍ដោយចេតនាលើ status ក្នុង scratch table។ ដំណើរការ query ពីរបីដង បន្ទាប់មក query pg_stat_user_indexes សម្រាប់ចំនួន idx_scan របស់វា។ តើអ្នកនឹងរក្សាវាទុកឬទេ?

ទៅទីណាបន្ត។ query របស់អ្នកត្រឹមត្រូវ ស្អាត និងលឿន។ ប៉ុន្តែត្រឹមត្រូវសម្រាប់អ្នកប្រើម្នាក់ម្តងមិនគ្រប់គ្រាន់ទេ។ ចុះបើ order ត្រូវបានបង្កើត ការ update ស្តុកជោគជ័យ ហើយបន្ទាប់មក server ដាច់ មុនពេល order item ត្រូវបានរក្សាទុក? ជំពូកចុងក្រោយ Transaction និង Safe SQL បង្ហាញពីរបៀបធ្វើឱ្យការផ្លាស់ប្តូរច្រើនជំហានជា all-or-nothing របៀបរួចផុតពីពេលមនុស្សពីរនាក់ទិញធាតុចុងក្រោយក្នុងពេលតែមួយ និងរបៀបរារាំង SQL injection ពីកម្មវិធីរបស់អ្នក។

មុនNormalization និងការរចនា Schemaបន្ទាប់Transaction និង Safe SQL

ទិញកាហ្វេជូនខ្ញុំ

ចូលចិត្តមេរៀននេះមែនទេ? សូមពិចារណាទិញកាហ្វេជូនខ្ញុំមួយកែវ ដើម្បីគាំទ្រការបង្កើតមាតិកាឥតគិតថ្លៃបន្ថែមទៀត!