باستخدام جميع ميزات الفهارس في PostgreSQL

في عالم 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 ) الذي يحتاجه الاستعلام.

قدم بوستجرس 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 لقراءة كومة الجدول.

اليوم ، فهارس التغطية متاحة فقط للأشجار ب. ومع ذلك ، في هذه الحالة ، ستكون جهود المرافقة أعلى.

باستخدام فهارس جزئية

الفهارس الجزئية فقط مجموعة فرعية من الصفوف في جدول. هذا يحفظ حجم الفهارس ومسح أسرع.

لنفترض أننا بحاجة إلى الحصول على قائمة بعناوين البريد الإلكتروني من عملائنا في كاليفورنيا. سيكون الطلب مثل هذا:

 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 أنواع jsonb مثل 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 وجود عناصر فردية في القيم المفهرسة.

نحن بحاجة إلى مؤشر الجن.

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