در دنیای Postgres، ایندکس ها برای پیمایش کارآمد در فضای ذخیره سازی پایگاه داده ضروری هستند (به نام "هیپ"). Postgres از خوشه بندی برای آن پشتیبانی نمی کند، و معماری MVCC باعث می شود که در نهایت نسخه های زیادی از همان تاپل را دریافت کنید. بنابراین، توانایی ایجاد و حفظ شاخص های کارآمد برای پشتیبانی از برنامه ها بسیار مهم است.
در اینجا چند نکته برای بهینه سازی و بهبود استفاده از ایندکس ها آورده شده است.
توجه: پرس و جوهای نشان داده شده در زیر روی پرس و جوهای اصلاح نشده کار می کنند .
استفاده از شاخص های پوششی
بیایید به درخواست استخراج آدرس ایمیل برای کاربران غیرفعال نگاه کنیم. جدول 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' به ما می گوید که پرس و جو اکنون فقط به یک نمایه نیاز دارد، که به جلوگیری از ورود/خروجی تمام دیسک برای خواندن پشته جدول کمک می کند.
شاخص های پوشش در حال حاضر فقط برای درختان 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
این ممکن است اتفاق بیفتد که شما با نمایه های زیادی مواجه شوید که یکی از آنها ابرمجموعه ای از ستون ها را نمایه می کند که شاخص های دیگر را ایندکس می کند. این ممکن است مطلوب باشد یا نباشد - سوپرست ممکن است منجر به اسکن فقط شاخص شود، که خوب است، اما ممکن است فضای زیادی را اشغال کند، یا درخواستی که سوپرست برای بهینه سازی در نظر گرفته شده است دیگر استفاده نمی شود.
اگر نیاز به خودکارسازی تعریف چنین شاخص هایی دارید، می توانید با آن شروع کنید از روی میز 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