Индекс үндэс

Та номын сангийн бүх номыг нэг нэгээр нь үзэж хайх уу, эсвэл лавлах дэвтрийг ашиглах уу? Өгөгдлийн сан дахь индекс яг тийм лавлах дэвтэр юм — тодорхой талбарын утгуудыг урьдчилан эрэмбэлж хадгалдаг тул PostgreSQL нь олон сая мөрийн дундаас хайж буй бичлэгийг маш хурдан олдог. Индексгүй тохиолдолд PostgreSQL хүснэгтийн бүх мөрийг нэг нэгээр уншдаг — үүнийг sequential scan гэнэ.

Индекс яагаад хэрэгтэй вэ?

Жижиг хүснэгтэд хурдны ялгаа мэдэгдэхгүй. Гэвч өгөгдөл олшрох тусам индексгүй асуулга хурдан удааширдаг:

| Мөрийн тоо | Индексгүй | Индекстэй | |------------|-----------|-----------| | 1,000 | ~1ms | ~1ms | | 100,000 | ~50ms | ~1ms | | 10,000,000 | ~5000ms | ~2ms |

Индекс нь дискний зай болон INSERT/UPDATE гүйцэтгэлийн бага зэрэг зардлаар ирдэг. Тиймээс бүх талбарт индекс тавих биш, байнга хайгддаг талбарт л тавих нь зөв.

-- Жишээ хүснэгт
CREATE TABLE employees (
    id         SERIAL PRIMARY KEY,
    email      TEXT NOT NULL,
    last_name  TEXT NOT NULL,
    department TEXT NOT NULL,
    salary     NUMERIC NOT NULL
);

PRIMARY KEY тодорхойлсон үед PostgreSQL автоматаар индекс үүсгэдэг. Гэхдээ бусад талбарт гараар үүсгэх шаардлагатай.

Индекс үүсгэх ба устгах

CREATE INDEX командаар индекс үүсгэнэ:

-- email талбарт энгийн индекс
CREATE INDEX idx_employees_email
    ON employees (email);

-- last_name талбарт индекс
CREATE INDEX idx_employees_last_name
    ON employees (last_name);

-- Олон талбарт нэгэн зэрэг индекс (composite index)
CREATE INDEX idx_employees_dept_salary
    ON employees (department, salary);

Индексийн нэрлэлтийн заншил: idx_хүснэгт_талбар — энэ нь нийтлэг хэлбэр бөгөөд хожим удирдахад тустай.

-- Индексийн жагсаалтыг харах
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'employees';

-- Индекс устгах
DROP INDEX idx_employees_last_name;

EXPLAIN ANALYZE — индекс ашиглагдаж байна уу?

EXPLAIN ANALYZE нь PostgreSQL-н асуулга хэрхэн гүйцэтгэж байгааг харуулдаг — индекс ашиглагдаж байгаа эсэхийг энэ командаар шалгаж болно:

-- Индексгүй үед sequential scan хийнэ
EXPLAIN ANALYZE
SELECT * FROM employees WHERE department = 'Инженер';

-- Индекс үүсгэсний дараа index scan ашиглана
CREATE INDEX idx_employees_department ON employees (department);

EXPLAIN ANALYZE
SELECT * FROM employees WHERE department = 'Инженер';

Гаралтад Seq Scan (бүх мөр уншсан) эсвэл Index Scan (индексээр хайсан) гэж харагдана. Index Scan нь ихэнх тохиолдолд хамаагүй хурдан байдаг.

UNIQUE индекс

UNIQUE хязгаарлалт тавихад PostgreSQL автоматаар unique индекс үүсгэдэг. Гараар бас үүсгэж болно:

-- email давхардахгүй байх unique индекс
CREATE UNIQUE INDEX idx_employees_email_unique
    ON employees (email);

-- Давхардсан утга оруулахыг хориглоно
INSERT INTO employees (email, last_name, department, salary)
VALUES ('test@example.com', 'Болд', 'Инженер', 1500000);

-- Дахин ижил email оруулбал алдаа гарна
INSERT INTO employees (email, last_name, department, salary)
VALUES ('test@example.com', 'Сарнай', 'Санхүү', 1200000);
-- ERROR: duplicate key value violates unique constraint

Дараагийн хичээлд:

Partial index, expression index зэрэг дэвшилтэт индексийн төрлүүд болон индексийг хэзээ ашиглах, хэзээ ашиглахгүй байх стратегийг судална.