في عالم 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. للبحث عن كل هذه الأفلام ، نحتاج إلى تحديد جميع الصفوف مع "ما وراء الكواليس" متى أي قيم الصفيف 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)
فهارس مجموعة كبيرة
يمكن أن ينتهي بك الأمر بالعديد من الفهارس ، أحدها يفهرس مجموعة شاملة من الأعمدة التي تفهرس الفهارس الأخرى. قد يكون هذا مرغوبًا أو غير مرغوب فيه - فقد ينتج عن المجموعة الشاملة عمليات مسح للفهرس فقط ، وهو أمر جيد ، ولكن قد يستغرق مساحة كبيرة جدًا ، أو لم يعد يتم استخدام الاستعلام الذي كان الغرض من المجموعة الشاملة تحسينه.
إذا كنت بحاجة إلى أتمتة تعريف هذه الفهارس ، فيمكنك البدء بـ من الطاولة 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