فهرس PostgreSQL

الفهرس هو استعلام خاص للجدول لتحسين بحث محرك البحث. بمعنى آخر، الفهرس هو مؤشر إلى بيانات الجدول. يشبه فهرس قاعدة البيانات فهرس كتاب.

بالمقارنة مع صفحات المحتويات (الفهرس) في قاموس الصينية، يمكننا البحث بسرعة عن الحروف باستخدام الفهرس المرتب حسب الأبجدية، أو الخطوط، أو الأجزاء المكونة للحروف.

يساعد الفهرس في تسريع استعلامات SELECT وWHERE، لكنه يبطئ إدخال البيانات باستخدام جمل UPDATE وINSERT. يمكن إنشاء أو حذف الفهرس، لكنه لا يؤثر على البيانات.

يُستخدم جملة CREATE INDEX لإنشاء الفهرس، مما يسمح بتسمية الفهرس وتحديد الجدول والأعمدة التي سيتم فهرستها، ويحدد ما إذا كان الفهرس مرتبًا تصاعديًا أم تنازليًا.

يمكن أن يكون الفهرس متميزًا أيضًا، يشبه قيود UNIQUE، ويمنع التكرار في الأعمدة أو مجموعات الأعمدة.

أمر CREATE INDEX

CREATE INDEX (إنشاء فهرس) بناءً على الجملة التالية:

CREATE INDEX index_name ON table_name;

索引类型

单列索引

单列索引是一个只基于表的一个列上创建的索引，基本语法如下：

CREATE INDEX index_name
ON table_name (column_name);

组合索引

组合索引是基于表的多列上创建的索引，基本语法如下：

CREATE INDEX index_name
ON table_name (column1_name, column2_name);

不管是单列索引还是组合索引，该索引必须是在 WHERE 子句的过滤条件中使用非常频繁的列。

如果只有一列被使用到，就选择单列索引，如果有多列就使用组合索引。

唯一索引

使用唯一索引不仅是为了性能，同时也为了数据的完整性。唯一索引不允许任何重复的值插入到表中。基本语法如下：

CREATE UNIQUE INDEX index_name
on table_name (column_name);

局部索引

局部索引是在表的子集上构建的索引；子集由一个条件表达式上定义。索引只包含满足条件的行。基础语法如下：

CREATE INDEX index_name
on table_name (conditional_expression);

隐式索引

隐式索引是在创建对象时，由数据库服务器自动创建的索引。索引自动创建为主键约束和唯一约束。

在线示例

下面示例将在 COMPANY 表的 SALARY 列上创建索引：

w3codeboxdb=# CREATE INDEX salary_index ON COMPANY (salary);

现在，用 　\d company 命令列出 COMPANY 表的所有索引：

w3codeboxdb=# \d company

得到的结果如下，company_pkey 是隐式索引，是表创建表时创建的：

w3codeboxdb=# \d company
　　　　　　　　　　　　　　　　　　Table "public.company"
　Column | Type | Collation | Nullable | Default　
---------+---------------+-----------+----------+---------
　id | integer | | not null |　
　name | text | | not null |　
　age | integer | | not null |　
　address | character(50) | | |　
　salary | real | | |　
indexes:
　　　　"company_pkey" PRIMARY KEY, btree (id)
　　　　"salary_index" btree (salary)

يمكنك استخدام أمر \di لقائمة جميع المؤشرات في قاعدة البيانات:

w3codeboxdb=# \di
　　　　　　　　　　　　　　　　　　　　قائمة العلاقات
　نموذج | Name | Type | Owner | Table　　　　
--------+-----------------+-------+----------+------------
　public | company_pkey | index | postgres | company
　public | department_pkey | index | postgres | department
　public | salary_index | index | postgres | company
(3 rows)

DROP INDEX (حذف المؤشر)

يمكن حذف المؤشر باستخدام أمر DROP في PostgreSQL.

DROP INDEX index_name;

يمكنك استخدام الجملة التالية لحذف المؤشرات التي تم إنشاؤها مسبقًا:

# DROP INDEX salary_index;

بعد الحذف، يمكنك رؤية أن salary_index قد تم حذفه من قائمة المؤشرات:

w3codeboxdb=# \di
　　　　　　　　　　　　　　　　　　　　قائمة العلاقات
　نموذج | Name | Type | Owner | Table　　　　
--------+-----------------+-------+----------+------------
　public | company_pkey | index | postgres | company
　public | department_pkey | index | postgres | department
(2 rows)

في أي حال يجب تجنب استخدام المؤشرات؟

على الرغم من أن غرض المؤشر هو تحسين أداء قاعدة البيانات، إلا أن هناك بعض الحالات يجب تجنب استخدام المؤشرات فيها.

عند استخدام المؤشرات، يجب النظر في القواعد التالية:

• لا يجب استخدام المؤشرات في الجداول الصغيرة.

• لا يجب استخدام المؤشرات في الجداول التي يتم فيها تحديث أو إدراج كتل كبيرة بشكل متكرر.

• لا يجب استخدام المؤشرات في الأعمدة التي تحتوي على عدد كبير من القيم NULL.

• لا يجب استخدام المؤشرات في الأعمدة التي يتم فيها التشغيل بشكل متكرر.