Thuta Learning
Search

Slow SQL Query တစ်ခုကို Optimize လုပ်နည်း

EXPLAIN ANALYZE ဖြင့် query plan ကို ဖတ်တတ်ခြင်းမှ index မှန်ကန်စွာ ထည့်ခြင်း၊ N+1 query ရှောင်ခြင်းအထိ — production database ထဲက နှေးနေတဲ့ SQL query တစ်ခုကို စနစ်တကျ ရှာဖွေ optimize လုပ်ပါမယ်။

Slow SQL Query တစ်ခုကို Optimize လုပ်နည်း cover illustration

Problem

Production database ထဲက query တစ်ခုက ရုတ်တရက် ဒါမှမဟုတ် data volume ကြီးလာတာနဲ့အမျှ နှေးလာပြီး page load/API response time ကို ထိခိုက်စေနေသည်၊ ဒါပေမယ့် ဘယ်နေရာက ပြဿနာလဲ ခန့်မှန်းရုံနဲ့ ဖြေရှင်းလို့ မရပါ။

Requirements

  • SQL query ရေးတတ်ခြင်း (SELECT, JOIN, WHERE အခြေခံ)
  • PostgreSQL သို့မဟုတ် MySQL database တစ်ခုသို့ ဝင်ရောက်ခွင့်
  • Query တစ်ခု run ဖို့ psql, pgAdmin, ဒါမှမဟုတ် ကွန်ရက်ချိတ်ထားသော client တစ်ခု

EXPLAIN ANALYZE ဖြင့် Query Plan ကို ဖတ်ပါ

Query တစ်ခုကို optimize မလုပ်ခင် ဒါဟာ တကယ် ဘာကြောင့် နှေးနေတာလဲဆိုတာ သိရပါမယ်။ `EXPLAIN ANALYZE` ကို query ရှေ့မှာ ထည့်လိုက်ရင် database က query ကို တကယ် run ပြီး ဘယ် step (Seq Scan, Index Scan, Nested Loop) တွေကို ဘယ်လောက် ကြာချိန်နဲ့ လုပ်ခဲ့လဲဆိုတာ ပြန်ပြောပြပါတယ်။ `Seq Scan` ဆိုတာ table တစ်ခုလုံးကို row တစ်ခုချင်းစီ scan လုပ်နေတယ်လို့ ဆိုလိုပြီး၊ row အရေအတွက်များတဲ့ table မှာ ဒါက ပြဿနာအကြီးဆုံး signal ပါ။

Terminal
$ psql -d mydb -c "EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;"

WHERE/JOIN column များပေါ်တွင် Index ထည့်ပါ

Seq Scan ပေါ်လာတဲ့ column ဟာ `WHERE` clause ဒါမှမဟုတ် `JOIN ... ON` မှာ သုံးနေတဲ့ column ဆိုရင် index တစ်ခု ထည့်ခြင်းက ပုံမှန်အားဖြင့် အထိရောက်ဆုံး fix ဖြစ်ပါတယ် — database က table တစ်ခုလုံး scan လုပ်မယ့်အစား index ကို tree-search (B-tree) နဲ့ ရှာနိုင်သွားပါတယ်။ ဒါပေမယ့် index တိုင်းက အခမဲ့ မဟုတ်ပါ — write (INSERT/UPDATE/DELETE) တိုင်းမှာ index ကိုပါ update ရတာမို့ index များလွန်းရင် write performance ကျနိုင်ပါတယ်။

add_index.sql
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

-- Multiple column ကို WHERE + ORDER BY အတူတူ သုံးနေရင် composite index စဉ်းစားပါ
CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at DESC);

Index တိုင်း အလုပ်မဖြစ်ပါ

Column ပေါ်မှာ function (`WHERE LOWER(email) = ...`) ခေါ်ထားရင် ရိုးရိုး index က အလုပ်မလုပ်ပါဘူး — expression index (`CREATE INDEX ON table (LOWER(email))`) လိုအပ်ပါတယ်။

N+1 Query Pattern ကို ရှောင်ပါ

Application code (ORM အများစုအပါအဝင်) က list တစ်ခု fetch လုပ်ပြီးမှ item တစ်ခုချင်းစီအတွက် related data ကို loop ထဲမှာ သီးခြား query ခေါ်နေရင် N+1 query problem ဖြစ်ပါတယ် — order 100 ခုအတွက် customer name ကို ရှာမယ်ဆိုရင် query 1 ခု (order စုစုပေါင်း) ပြီးရင် customer query 100 ခု ထပ်ခေါ်နေတာမျိုး ဖြစ်ပါတယ်။ ဒါကို `JOIN` တစ်ခုတည်း (သို့) ORM ရဲ့ eager-loading feature (`.include()`, `select_related()`) နဲ့ query တစ်ခုတည်းအဖြစ် ပေါင်းစည်းလိုက်ရင် database round-trip အများကြီးကို ချက်ချင်း ဖယ်ရှားနိုင်ပါတယ်။

fix_n_plus_1.sql
-- N+1 (application code loop ထဲမှာ order တစ်ခုချင်းစီအတွက် ထပ်ခေါ်နေသလို)
-- SELECT * FROM orders;
-- SELECT * FROM customers WHERE id = ?;  (order 100 ခုအတွက် 100 ကြိမ်)

-- JOIN တစ်ခုတည်းနဲ့ ပေါင်းစည်းလိုက်ခြင်း
SELECT orders.*, customers.name
FROM orders
JOIN customers ON customers.id = orders.customer_id;

Fix ပြီးနောက် ပြန်စစ်ဆေးပါ

Index ထည့်ပြီး/query ပြင်ပြီးနောက် `EXPLAIN ANALYZE` ကို ထပ် run ပြီး `Seq Scan` က `Index Scan` (သို့) `Index Only Scan` ဖြစ်လာမလား၊ `actual time` က တကယ် လျော့ကျလာမလား စစ်ဆေးပါ။ Staging environment မှာ production data volume နဲ့ close တဲ့ dataset (synthetic data ဖြစ်စေ) ပေါ်မှာ စစ်ဆေးမှသာ production ပေါ် deploy လုပ်ချိန် ယုံကြည်စိတ်ချရပါတယ်။

Expected result

Query plan ထဲက Seq Scan များ Index Scan အဖြစ် ပြောင်းသွားပြီး၊ query ရဲ့ execution time သိသိသာသာ လျော့ကျသွားမည်ဖြစ်ပြီး (row အရေအတွက်ပေါ်မူတည်၍ ဆယ်ဆမှ ရာနှင့်ချီ) N+1 query pattern ကို JOIN/eager-loading နဲ့ ဖယ်ရှားလိုက်ခြင်းအားဖြင့် database round-trip အရေအတွက် သိသိသာသာ လျော့ကျသွားမည်။

Troubleshooting

  • Index ထည့်ပြီးနောက်တောင် EXPLAIN မှာ Seq Scan ဆက်တွေ့ရင် table ရဲ့ statistics အဟောင်းဖြစ်နေနိုင်ပါတယ် — `ANALYZE table_name;` ကို run ပြီး planner statistics ကို refresh လုပ်ပါ။
  • Query fast ဖြစ်သွားပေမယ့် write operation (INSERT/UPDATE) တွေ ရုတ်တရက် နှေးလာရင် index အသစ်များနေတာ ဖြစ်နိုင်ပါတယ် — မလိုအပ်တဲ့ duplicate/unused index များကို `pg_stat_user_indexes` နဲ့ စစ်ပြီး ဖယ်ရှားပါ။
  • Composite index ထည့်ထားပေမယ့် planner က မသုံးဘူးဆိုရင် index ထဲက column order ကို query ရဲ့ WHERE/ORDER BY order နဲ့ ကိုက်ညီအောင် ပြန်စီစဉ်ကြည့်ပါ — leftmost-prefix rule အရ column အစီအစဉ် အရေးကြီးပါတယ်။
  • Local မှာ fast ဖြစ်ပေမယ့် production မှာ ဆက်နေးနေရင် production data volume/distribution က local dataset နှင့် သိသိသာသာ ကွာနေနိုင်ပါတယ် — production-scale data (synthetic ဖြစ်စေ) ဖြင့် staging မှာ ပြန်စစ်ပါ။

Related tutorials