مقدمة
تهتم هذه السلسلة من المقالات إلى حد كبير بفهارس PostgreSQL.
يمكن النظر في أي موضوع من وجهات نظر مختلفة. سنناقش الأمور التي يجب أن تهم مطور التطبيقات الذي يستخدم قواعد بيانات إدارة قواعد البيانات: ما هي الفهارس المتاحة ، ولماذا يوجد العديد من أنواعها المختلفة ، وكيفية استخدامها لتسريع الاستعلامات. من المحتمل أن يتم تغطية الموضوع ببضع كلمات ، ولكن في سرية نأمل أن يكون هناك مطور فضولي ، مهتم أيضًا بتفاصيل الأجزاء الداخلية ، خاصة وأن فهم مثل هذه التفاصيل لا يسمح لك بالرجوع إلى حكم الآخرين فحسب ، بل أيضًا تقديم استنتاجات خاصة بك.
تطوير أنواع جديدة من الفهارس خارج النطاق. هذا يتطلب معرفة لغة البرمجة C ويتعلق بخبرة مبرمج النظام بدلاً من مطور التطبيقات. للسبب نفسه ، لن نناقش واجهات البرمجة تقريبًا ، ولكننا سنركز فقط على الأمور المهمة للعمل مع فهارس جاهزة للاستخدام.
سنناقش في هذه المقالة توزيع المسؤوليات بين
محرك الفهرسة العام المرتبط بأساليب DBMS الأساسية وطرق الوصول إلى الفهرس الفردي ، والتي تمكننا PostgreSQL من إضافتها كملحقات. في المقالة التالية سنناقش
واجهة طريقة الوصول والمفاهيم الأساسية مثل الطبقات وعائلات المشغلين. بعد هذه المقدمة الطويلة ولكن الضرورية ، سننظر في تفاصيل هيكل وتطبيق أنواع مختلفة من الفهارس:
Hash و
B-tree و
GiST و
SP- GiST و
GIN و
RUM و
BRIN و
Bloom .
قبل أن نبدأ ، أود أن أشكر Elena Indrupskaya على ترجمة المقالات إلى اللغة الإنجليزية.
لقد تغيرت الأمور قليلاً منذ النشر الأصلي. يشار إلى تعليقاتي على الوضع الحالي مثل هذا.
الفهارس
في PostgreSQL ، تعد الفهارس كائنات قاعدة بيانات خاصة مصممة بشكل أساسي لتسريع الوصول إلى البيانات. إنها هياكل مساعدة: يمكن حذف كل فهرس وإعادة إنشائه من المعلومات الموجودة في الجدول. قد يحدث أحيانًا أن تسمع أن نظام إدارة قواعد البيانات (DBMS) يمكن أن يعمل بدون فهارس على الرغم من ببطء. ومع ذلك ، ليس هذا هو الحال ، لأن الفهارس تعمل أيضًا على فرض بعض قيود النزاهة.
في الوقت الحالي ، تم دمج ستة أنواع مختلفة من الفهارس في PostgreSQL 9.6 ، ويتوفر فهرس آخر كملحق - بفضل التغييرات المهمة في الإصدار 9.6. لذلك نتوقع أنواع جديدة من الفهارس في المستقبل القريب.
على الرغم من جميع الاختلافات بين أنواع الفهارس (وتسمى أيضًا طرق الوصول) ، يربط كل منها في النهاية مفتاحًا (على سبيل المثال ، قيمة العمود المفهرسة) بصفوف الجدول التي تحتوي على هذا المفتاح. يتم تحديد كل صف بواسطة TID (معرف tuple) ، والذي يتكون من عدد الكتلة في الملف وموضع الصف داخل الكتلة. ومع ذلك ، باستخدام المفتاح المعروف أو بعض المعلومات حوله ، يمكننا بسرعة قراءة تلك الصفوف التي قد تحتوي على المعلومات التي تهمنا دون مسح الجدول بأكمله.
من المهم أن نفهم أن الفهرس يسرع الوصول إلى البيانات بتكلفة صيانة معينة. لكل عملية على البيانات المفهرسة ، سواء كانت الإدراج أو الحذف أو تحديث صفوف الجدول ، يجب تحديث فهارس ذلك الجدول أيضًا ، وفي نفس المعاملة. لاحظ أن تحديث حقول الجدول التي لم يتم إنشاء فهارس لها لا يؤدي إلى تحديث الفهرس ؛ وتسمى هذه التقنية HOT (كومة فقط Tuples).
التمدد ينطوي على بعض الآثار. لتمكين إضافة طريقة وصول جديدة بسهولة إلى النظام ، تم تنفيذ واجهة لمحرك الفهرسة العام. وتتمثل مهمتها الرئيسية في الحصول على TIDs من طريقة الوصول والعمل معهم:
- قراءة البيانات من الإصدارات المقابلة من صفوف الجدول.
- إحضار إصدارات الصف TID بواسطة TID أو في مجموعة باستخدام صورة نقطية تم إنشاؤها مسبقًا.
- تحقق من ظهور إصدارات الصف للمعاملات الحالية مع مراعاة مستوى العزل الخاص بها.
محرك الفهرسة متورط في أداء الاستعلامات. يطلق عليه وفقًا لخطة تم إنشاؤها في مرحلة التحسين. يجب أن يفهم المحسن ، الذي يقوم بفرز وتقييم طرق مختلفة لتنفيذ الاستعلام ، قدرات كل طرق الوصول المحتملة. هل ستكون الطريقة قادرة على إرجاع البيانات بالترتيب المطلوب أم أننا يجب أن نتوقع الفرز؟ هل يمكننا استخدام هذه الطريقة للبحث عن NULL؟ هذه هي المشاكل التي يقوم المحسن بحلها بانتظام.
لا يحتاج المُحسّن فقط إلى معلومات حول طريقة الوصول. عند إنشاء فهرس ، يجب أن يقرر النظام ما إذا كان يمكن بناء الفهرس على عدة أعمدة وما إذا كان هذا الفهرس يضمن التفرد أم لا.
لذلك ، يجب أن توفر كل طريقة وصول جميع المعلومات اللازمة عن نفسها. تستخدم الإصدارات الأقل من 9.6 جدول "pg_am" لهذا ، بينما تبدأ بالإصدار 9.6 نقل البيانات إلى مستويات أعمق ، داخل وظائف خاصة. سنتعرف على هذه الواجهة أكثر قليلاً.
كل ما تبقى هو مهمة طريقة الوصول:
- قم بتطبيق خوارزمية لإنشاء الفهرس وتعيين البيانات في صفحات (لمدير ذاكرة التخزين المؤقت المؤقت لمعالجة كل فهرس بشكل موحد).
- البحث في المعلومات في الفهرس بواسطة المسند في النموذج " تعبير عامل الحقل المفهرس ".
- تقييم تكلفة استخدام الفهرس.
- معالجة الأقفال المطلوبة للمعالجة المتوازية الصحيحة.
- إنشاء سجلات سجل (WAL) للكتابة المسبقة.
سننظر أولاً في إمكانيات محرك الفهرسة العامة ثم ننتقل إلى دراسة طرق الوصول المختلفة.
محرك الفهرسة
يُمكن محرك الفهرسة PostgreSQL من العمل مع أساليب وصول مختلفة بشكل موحد ، ولكن مع مراعاة ميزاتها.
تقنيات المسح الرئيسية
مسح مؤشر
يمكننا العمل بشكل مختلف مع TIDs المقدمة بواسطة فهرس. لننظر كمثال:
postgres=# create table t(a integer, b text, c boolean); postgres=# insert into t(a,b,c) select s.id, chr((32+random()*94)::integer), random() < 0.01 from generate_series(1,100000) as s(id) order by random(); postgres=# create index on t(a); postgres=# analyze t;
أنشأنا جدول ثلاثة حقول. يحتوي الحقل الأول على أرقام من 1 إلى 100.000 ، ويتم إنشاء فهرس (بغض النظر عن نوعه) في هذا الحقل. يحتوي الحقل الثاني على العديد من أحرف ASCII باستثناء الأحرف غير القابلة للطباعة. أخيرًا ، يحتوي الحقل الثالث على قيمة منطقية صحيحة لحوالي 1٪ من الصفوف وخطأ للباقي. يتم إدراج الصفوف في الجدول بترتيب عشوائي.
دعنا نحاول تحديد قيمة حسب الحالة "a = 1". لاحظ أن الشرط يشبه "
تعبير عامل الحقل المفهرس " ، حيث يكون
العامل "يساوي"
والتعبير (مفتاح البحث) هو "1". في معظم الحالات ، يجب أن يكون الشرط كهذا ليتم استخدامه في الفهرس.
postgres=# explain (costs off) select * from t where a = 1;
QUERY PLAN ------------------------------- Index Scan using t_a_idx on t Index Cond: (a = 1) (2 rows)
في هذه الحالة ، قرر محسن استخدام
المسح الضوئي الفهرس . مع المسح الضوئي للفهرس ، تقوم طريقة الوصول بإرجاع قيم TID واحدة تلو الأخرى حتى يتم الوصول إلى آخر صف مطابق. يصل محرك الفهرسة إلى صفوف الجدول المشار إليها بواسطة TID بدورها ، ويحصل على إصدار الصف ، ويتحقق من رؤيته مقابل قواعد التزامن المتعددة ، ويعيد البيانات التي تم الحصول عليها.
صورة نقطية المسح
تفحص الفهرس يعمل بشكل جيد عندما نتعامل مع قيم قليلة فقط. ومع ذلك ، مع زيادة عدد الصفوف التي تم استردادها ، فمن الأرجح أن تعود إلى صفحة الجدول نفسها عدة مرات. لذلك ، يقوم المحسن بالتبديل إلى
صورة نقطية .
postgres=# explain (costs off) select * from t where a <= 100;
QUERY PLAN ------------------------------------ Bitmap Heap Scan on t Recheck Cond: (a <= 100) -> Bitmap Index Scan on t_a_idx Index Cond: (a <= 100) (4 rows)
تقوم طريقة الوصول أولاً بإرجاع كافة TIDs التي تتطابق مع الشرط (عقدة فهرس صورة نقطية) ، ويتم إنشاء الصورة النقطية لإصدارات الصفوف من هذه الأرقام. ثم تتم قراءة إصدارات الصفوف من الجدول (صورة نقطية Heap Scan) ، تتم قراءة كل صفحة مرة واحدة فقط.
لاحظ أنه في الخطوة الثانية ، يمكن إعادة فحص الحالة (إعادة التحقق من كوند). يمكن أن يكون عدد الصفوف التي تم استردادها كبيرًا جدًا بحيث لا يمكن للصورة النقطية لإصدارات الصف احتواء ذاكرة الوصول العشوائي (بشكل محدود بواسطة المعلمة "work_mem"). في هذه الحالة ، تم تصميم الصورة النقطية فقط للصفحات التي تحتوي على إصدار صف مطابق واحد على الأقل. تتطلب هذه الصورة النقطية "المفقودة" مساحة أقل ، ولكن عند قراءة صفحة ، نحتاج إلى إعادة التحقق من شروط كل صف موجود هناك. لاحظ أنه حتى بالنسبة لعدد صغير من الصفوف التي تم استردادها وبالتالي الصورة النقطية "الدقيقة" (كما في مثالنا) ، يتم تمثيل الخطوة "إعادة التحقق من كون" في الخطة على أي حال ، على الرغم من عدم تنفيذها فعليًا.
إذا تم فرض شروط على العديد من حقول الجدول وتم فهرسة هذه الحقول ، فإن فحص الصور النقطية يمكّن من استخدام عدة فهارس في وقت واحد (إذا اعتبر المحسن هذا فعالاً). لكل فهرس ، يتم إنشاء الصور النقطية لإصدارات الصف ، والتي يتم من خلالها تنفيذ الضرب المنطقي للبت (إذا تم ربط التعبيرات بـ AND) أو الإضافة المنطقية (إذا تم ربط التعبيرات ب OR). على سبيل المثال:
postgres=# create index on t(b); postgres=# analyze t; postgres=# explain (costs off) select * from t where a <= 100 and b = 'a';
QUERY PLAN -------------------------------------------------- Bitmap Heap Scan on t Recheck Cond: ((a <= 100) AND (b = 'a'::text)) -> BitmapAnd -> Bitmap Index Scan on t_a_idx Index Cond: (a <= 100) -> Bitmap Index Scan on t_b_idx Index Cond: (b = 'a'::text) (7 rows)
هنا العقدة BitmapAnd ينضم اثنين من الصور النقطية بواسطة bitwise "و" العملية.
يمكّنك مسح الصور النقطية من تجنب الوصول المتكرر إلى صفحة البيانات نفسها. ولكن ماذا لو كانت البيانات الموجودة في صفحات الجدول مرتبة فعليًا بنفس الطريقة تمامًا مثل سجلات الفهرس؟ مما لا شك فيه أننا لا نستطيع الاعتماد بشكل كامل على الترتيب الفعلي للبيانات في الصفحات. إذا كانت هناك حاجة إلى بيانات مصنفة ، يجب أن نحدد بشكل صريح جملة ORDER BY في الاستعلام. لكن من المحتمل أن تكون المواقف حيث يتم طلب بيانات "جميعها" تقريبًا: على سبيل المثال ، إذا تمت إضافة صفوف بالترتيب المطلوب ولا تتغير بعد ذلك أو بعد تنفيذ أمر CLUSTER. في مثل هذه الحالات ، يعد إنشاء صورة نقطية خطوة مفرطة ، وسيكون إجراء مسح فهرس عادي بنفس جودة (ما لم نأخذ في الاعتبار إمكانية الانضمام إلى عدة فهارس). لذلك ، عند اختيار طريقة الوصول ، يبحث المخطط في إحصائية خاصة تُظهر العلاقة بين ترتيب الصف الفعلي والترتيب المنطقي لقيم الأعمدة:
postgres=# select attname, correlation from pg_stats where tablename = 't';
attname | correlation ---------+------------- b | 0.533512 c | 0.942365 a | -0.00768816 (3 rows)
تشير القيم المطلقة القريبة من واحدة إلى وجود علاقة ارتباط عالية (كما هو الحال مع العمود "c") ، بينما تشير القيم القريبة من الصفر ، على العكس ، إلى توزيع فوضوي (العمود "a").
المسح المتسلسل
لإكمال الصورة ، تجدر الإشارة إلى أنه في حالة غير انتقائية ، سيكون المحسن على حق في تفضيل المسح المتسلسل للجدول بأكمله على استخدام الفهرس:
postgres=# explain (costs off) select * from t where a <= 40000;
QUERY PLAN ------------------------ Seq Scan on t Filter: (a <= 40000) (2 rows)
الشيء هو أن الفهارس تعمل بشكل أفضل ، كلما كانت انتقائية الحالة أعلى ، كلما قل عدد الصفوف التي تطابقها. نمو عدد الصفوف التي تم استردادها يزيد من التكاليف العامة لقراءة صفحات الفهرس.
المسح التسلسلي يجري أسرع أن عمليات المسح العشوائي يضاعف الموقف. هذا ينطبق بشكل خاص على الأقراص الصلبة ، حيث تستغرق العملية الميكانيكية لجلب رأس مغناطيسي إلى المسار وقتًا أطول بكثير من قراءة البيانات نفسها. هذا التأثير هو أقل وضوحا بالنسبة SSD. تتوفر معلمتان لتأخذ في الاعتبار الاختلافات في تكاليف الوصول ، "seq_page_cost" و "random_page_cost" ، والتي يمكننا تعيينها ليس فقط على المستوى العالمي ، ولكن على مستوى مساحات الجداول ، وبهذه الطريقة يتم ضبط خصائص النظام الفرعي للقرص المختلفة.
تغطي الفهارس
كقاعدة عامة ، تتمثل المهمة الرئيسية لطريقة الوصول في إرجاع معرفات مطابقة صفوف الجدول لمحرك الفهرسة لقراءة البيانات اللازمة من هذه الصفوف. ولكن ماذا لو كان الفهرس يحتوي بالفعل على جميع البيانات اللازمة للاستعلام؟ يسمى هذا الفهرس
بالغطاء ، وفي هذه الحالة ، يمكن للمحسن تطبيق
المسح الضوئي فقط :
postgres=# vacuum t; postgres=# explain (costs off) select a from t where a < 100;
QUERY PLAN ------------------------------------ Index Only Scan using t_a_idx on t Index Cond: (a < 100) (2 rows)
قد يعطي هذا الاسم فكرة أن مشغل الفهرسة لا يصل إلى الجدول على الإطلاق ويحصل على جميع المعلومات اللازمة من طريقة الوصول وحدها. ولكن هذا ليس هو الحال بالضبط لأن الفهارس في PostgreSQL لا تخزن المعلومات التي تمكننا من الحكم على رؤية الصف. لذلك ، تقوم طريقة الوصول بإرجاع إصدارات الصفوف التي تطابق شرط البحث بغض النظر عن مدى رؤيتها في المعاملة الحالية.
ومع ذلك ، إذا احتاج محرك الفهرسة إلى النظر إلى الجدول من أجل الرؤية في كل مرة ، فلن تكون طريقة المسح هذه مختلفة عن طريقة المسح الضوئي المنتظم.
لحل المشكلة ، بالنسبة للجداول ، يحتفظ PostgreSQL بما يسمى
بخريطة الرؤية التي يحدد فيها الفراغ الصفحات التي لم يتم تغيير البيانات فيها لفترة كافية بحيث تكون هذه البيانات مرئية بواسطة جميع المعاملات بغض النظر عن وقت البدء ومستوى العزل. إذا كان معرف الصف الذي تم إرجاعه بواسطة الفهرس يتعلق بهذه الصفحة ، فيمكن تجنب التحقق من الرؤية.
لذلك ، يزيد الكنس العادي من كفاءة تغطية الفهارس. علاوة على ذلك ، يأخذ المُحسِّن في الاعتبار عدد التلاميذ الميتة ويمكنه أن يقرر عدم استخدام الفهرسة فقط إذا كان يتوقع ارتفاع التكاليف العامة لفحص الرؤية.
يمكننا معرفة عدد مرات الوصول القسري إلى جدول باستخدام أمر EXPLAIN ANALYZE:
postgres=# explain (analyze, costs off) select a from t where a < 100;
QUERY PLAN ------------------------------------------------------------------------------- Index Only Scan using t_a_idx on t (actual time=0.025..0.036 rows=99 loops=1) Index Cond: (a < 100) Heap Fetches: 0 Planning time: 0.092 ms Execution time: 0.059 ms (5 rows)
في هذه الحالة ، لم تكن هناك حاجة للوصول إلى الجدول (Het Fetches: 0) ، نظرًا لأن التنظيف بالمكنسة الكهربائية قد تم للتو. بشكل عام ، كلما كان هذا الرقم أقرب إلى الصفر كلما كان ذلك أفضل.
لا تقوم كل الفهارس بتخزين القيم المفهرسة إلى جانب معرفات الصف. إذا لم تتمكن طريقة الوصول من إرجاع البيانات ، فلا يمكن استخدامها لفحص الفهرس فقط.
لقد قدم PostgreSQL 11 ميزة جديدة: تشمل الفهارس. ماذا لو كان هناك فهرس فريد يفتقر إلى بعض الأعمدة ليتم استخدامه كفهرس تغطية لبعض الاستعلامات؟ لا يمكنك ببساطة إضافة الأعمدة إلى الفهرس لأنه سينفرد بتفرده. تسمح هذه الميزة بتضمين أعمدة غير أساسية لا تؤثر على التفرد ولا يمكن استخدامها في تقديرات البحث ، ولكن لا يزال بإمكانها تقديم عمليات مسح للفهرس فقط. تم تطوير التصحيح من قبل زميلي أناستازيا لوبنيكوفا.
لاغية
تلعب القيم الخالية دورًا مهمًا في قواعد البيانات العلائقية كوسيلة ملائمة لتمثيل قيمة غير موجودة أو غير معروفة.
ولكن قيمة خاصة هي خاصة للتعامل معها. جبر منطقي منتظم يصبح ثلاثية. من غير الواضح ما إذا كان يجب أن يكون NULL أصغر أو أكبر من القيم العادية (وهذا يتطلب إنشاءات خاصة للفرز ، NULLS FIRST و NULLS LAST) ؛ ليس من الواضح ما إذا كانت الدوال التجميعية يجب أن تراعي القيم الفارغة أم لا ؛ هناك حاجة إلى إحصائية خاصة للمخطط ...
من منظور دعم الفهرس ، من غير الواضح أيضًا ما إذا كنا بحاجة إلى فهرسة هذه القيم أم لا. إذا لم يتم فهرسة NULLs ، فقد يكون الفهرس أكثر ضغطًا. ولكن إذا تمت فهرسة NULLs ، فسنكون قادرين على استخدام الفهرس لشروط مثل "
الحقل المفهرس IS [NOT] NULL" وأيضًا باعتباره فهرس تغطية عندما لا يتم تحديد شروط على الإطلاق للجدول (حيث في هذه الحالة ، يجب أن يعرض الفهرس البيانات الخاصة بجميع صفوف الجدول ، بما في ذلك تلك الصفوف الفارغة).
لكل طريقة وصول ، يتخذ المطورون قرارًا فرديًا بفهرسة القيم الفارغة أم لا. لكن كقاعدة عامة ، يتم فهرستها.
فهارس في عدة مجالات
لدعم شروط العديد من الحقول ، يمكن استخدام
فهارس متعددة الأعمدة . على سبيل المثال ، قد نبني فهرسًا في حقلين من جدولنا:
postgres=# create index on t(a,b); postgres=# analyze t;
على الأرجح سيفضل المُحسّن هذا الفهرس للانضمام إلى الصور النقطية لأننا هنا نحصل على أرقام TID المطلوبة دون أي عمليات مساعدة:
postgres=# explain (costs off) select * from t where a <= 100 and b = 'a';
QUERY PLAN ------------------------------------------------ Index Scan using t_a_b_idx on t Index Cond: ((a <= 100) AND (b = 'a'::text)) (2 rows)
يمكن أيضًا استخدام فهرس متعدد الأعمدة لتسريع استعادة البيانات من خلال شرط لبعض الحقول ، بدءًا من الأول:
postgres=# explain (costs off) select * from t where a <= 100;
QUERY PLAN -------------------------------------- Bitmap Heap Scan on t Recheck Cond: (a <= 100) -> Bitmap Index Scan on t_a_b_idx Index Cond: (a <= 100) (4 rows)
بشكل عام ، إذا لم يتم فرض الشرط على الحقل الأول ، فلن يتم استخدام الفهرس. لكن في بعض الأحيان قد يعتبر المحسن استخدام الفهرس أكثر كفاءة من المسح المتسلسل. سنتوسع في هذا الموضوع عند النظر في فهارس "btree".
لا تدعم كل طرق الوصول فهارس البناء في عدة أعمدة.
فهارس على التعبيرات
لقد ذكرنا بالفعل أن شرط البحث يجب أن يبدو "
تعبير عامل الحقل المفهرس ". في المثال أدناه ، لن يتم استخدام الفهرس لأنه يتم استخدام تعبير يحتوي على اسم الحقل بدلاً من اسم الحقل نفسه:
postgres=# explain (costs off) select * from t where lower(b) = 'a';
QUERY PLAN ------------------------------------------ Seq Scan on t Filter: (lower((b)::text) = 'a'::text) (2 rows)
لا يتطلب الأمر إعادة كتابة هذا الاستعلام المحدد كثيرًا حتى تتم كتابة اسم الحقل فقط على يسار المشغل. ولكن إذا لم يكن ذلك ممكنًا ، فستساعد فهارس التعبيرات (الفهارس الوظيفية) على:
postgres=# create index on t(lower(b)); postgres=# analyze t; postgres=# explain (costs off) select * from t where lower(b) = 'a';
QUERY PLAN ---------------------------------------------------- Bitmap Heap Scan on t Recheck Cond: (lower((b)::text) = 'a'::text) -> Bitmap Index Scan on t_lower_idx Index Cond: (lower((b)::text) = 'a'::text) (4 rows)
الفهرس الوظيفي مبني ليس على حقل جدول ، ولكن على تعبير تعسفي. سينظر المُحسّن في هذا الفهرس لظروف مثل "
تعبير عامل التعبير المفهرسة ". إذا كان حساب التعبير المراد فهرسته عملية مكلفة ، فسيتطلب تحديث الفهرس أيضًا موارد حساب كبيرة.
يرجى أيضًا مراعاة أنه يتم جمع إحصائيات فردية للتعبير المفهرس. يمكننا التعرف على هذه الإحصائية في عرض "pg_stats" حسب اسم الفهرس:
postgres=# \dt
Table "public.t" Column | Type | Modifiers --------+---------+----------- a | integer | b | text | c | boolean | Indexes: "t_a_b_idx" btree (a, b) "t_a_idx" btree (a) "t_b_idx" btree (b) "t_lower_idx" btree (lower(b))
postgres=# select * from pg_stats where tablename = 't_lower_idx';
من الممكن ، إذا لزم الأمر ، التحكم في عدد سلال الرسم البياني بنفس الطريقة المتبعة في حقول البيانات العادية (مع ملاحظة أن اسم العمود يمكن أن يختلف وفقًا للتعبير المفهرس):
postgres=# \d t_lower_idx
Index "public.t_lower_idx" Column | Type | Definition --------+------+------------ lower | text | lower(b) btree, for table "public.t"
postgres=# alter index t_lower_idx alter column "lower" set statistics 69;
يقدم PostgreSQL 11 طريقة أنظف للتحكم في هدف الإحصائيات للفهارس عن طريق تحديد رقم العمود في أمر ALTER INDEX ... SET STATISTICS. تم تطوير الرقعة بواسطة زميلي ألكسندر كوروتكوف ، وأدريان نايرات.
فهارس جزئية
في بعض الأحيان تنشأ حاجة لفهرسة جزء فقط من صفوف الجدول. يرتبط هذا عادةً بتوزيع غير منتظم للغاية: من المنطقي البحث عن قيمة نادرة بواسطة فهرس ، ولكن من الأسهل العثور على قيمة متكررة عن طريق المسح الكامل للجدول.
يمكننا بالتأكيد إنشاء فهرس منتظم على العمود "c" ، والذي سوف يعمل بالطريقة التي نتوقعها:
postgres=# create index on t(c); postgres=# analyze t; postgres=# explain (costs off) select * from t where c;
QUERY PLAN ------------------------------- Index Scan using t_c_idx on t Index Cond: (c = true) Filter: c (3 rows)
postgres=# explain (costs off) select * from t where not c;
QUERY PLAN ------------------- Seq Scan on t Filter: (NOT c) (2 rows)
وحجم الفهرس هو 276 صفحة:
postgres=# select relpages from pg_class where relname='t_c_idx';
relpages ---------- 276 (1 row)
ولكن نظرًا لأن العمود "c" له قيمة صواب فقط لـ 1٪ من الصفوف ، فإن 99٪ من الفهرس لا يتم استخدامه فعليًا على الإطلاق. في هذه الحالة ، يمكننا بناء فهرس جزئي:
postgres=# create index on t(c) where c; postgres=# analyze t;
يتم تقليل حجم الفهرس إلى 5 صفحات:
postgres=# select relpages from pg_class where relname='t_c_idx1';
relpages ---------- 5 (1 row)
في بعض الأحيان قد يكون الفرق في الحجم والأداء كبيرًا جدًا.
الفرز
إذا كانت طريقة الوصول تقوم بإرجاع معرّفات الصفوف في ترتيب معين ، فإن ذلك يوفر للمحسن خيارات إضافية لتنفيذ الاستعلام.
يمكننا مسح الجدول ثم فرز البيانات:
postgres=# set enable_indexscan=off; postgres=# explain (costs off) select * from t order by a;
QUERY PLAN --------------------- Sort Sort Key: a -> Seq Scan on t (3 rows)
ولكن يمكننا قراءة البيانات باستخدام الفهرس بسهولة بالترتيب المطلوب:
postgres=# set enable_indexscan=on; postgres=# explain (costs off) select * from t order by a;
QUERY PLAN ------------------------------- Index Scan using t_a_idx on t (1 row)
فقط "btree" من كل طرق الوصول يمكنها إرجاع البيانات المصنفة ، لذلك دعونا نؤجل مناقشة أكثر تفصيلاً حتى النظر في هذا النوع من الفهرس.
بناء متزامن
يكتسب بناء الفهرس عادةً قفل SHARE للجدول. يسمح هذا القفل بقراءة البيانات من الجدول ، ولكنه يمنع أي تغييرات أثناء إنشاء الفهرس.
يمكننا التأكد من ذلك إذا ، على سبيل المثال ، أثناء إنشاء فهرس على الجدول "t" ، قمنا بإجراء الاستعلام أدناه في جلسة أخرى:
postgres=# select mode, granted from pg_locks where relation = 't'::regclass;
mode | granted -----------+--------- ShareLock | t (1 row)
إذا كان الجدول كبيرًا بدرجة كافية واستخدم على نطاق واسع للإدخال أو التحديث أو الحذف ، فقد يبدو هذا غير مقبول نظرًا لأن عمليات التعديل ستنتظر إصدار قفل لفترة طويلة.
في هذه الحالة ، يمكننا استخدام البناء المتزامن للفهرس.
postgres=# create index concurrently on t(a);
يقوم هذا الأمر بإغلاق الجدول في وضع SHARE UPDATE EXCLUSIVE ، والذي يسمح بكل من القراءة والتحديث (يُحظر فقط تغيير بنية الجدول ، بالإضافة إلى الخلاء المتزامن أو التحليل أو إنشاء فهرس آخر في هذا الجدول).
ومع ذلك ، هناك أيضا الجانب الآخر. أولاً ، سيتم بناء الفهرس بشكل أبطأ من المعتاد ، حيث يتم المرور عبر الجدول بدلاً من واحد ، ومن الضروري أيضًا انتظار استكمال المعاملات الموازية التي تعدل البيانات.
ثانياً ، مع البناء المتزامن للمؤشر ، يمكن أن تحدث حالة توقف تام أو يمكن انتهاك قيود فريدة. ومع ذلك ، سيتم بناء الفهرس ، على الرغم من عدم التشغيل. يجب حذف هذا الفهرس وإعادة بنائه. يتم وضع علامة الفهارس غير العاملة بكلمة INVALID في إخراج الأمر psql \ d ، ويقوم الاستعلام أدناه بإرجاع قائمة كاملة بتلك:
postgres=# select indexrelid::regclass index_name, indrelid::regclass table_name from pg_index where not indisvalid;
index_name | table_name ------------+------------ t_a_idx | t (1 row)
اقرأ على .