PostgreSQL / Индекс дэвшилтэт

Индекс дэвшилтэт

Өмнөх хичээлд энгийн B-tree индексийг сурлаа. Гэвч бодит системд өгөгдөл, асуулгын хэв маяг олон янз байдаг — бүгдэд нэг төрлийн индекс тохиромжгүй. PostgreSQL нь тусгай тохиолдолд зориулсан хэд хэдэн дэвшилтэт индексийн арга санал болгодог. Эдгээрийг ойлгосноор дискний зайг хэмнэж, асуулгыг улам хурдасгах боломжтой.

Partial index — хэсэгчилсэн индекс

Partial index нь хүснэгтийн зарим мөрт л индекс тавьдаг. WHERE нөхцөлийг хангасан мөрүүдт л хамаарна. Хэрэв та зөвхөн тодорхой нөхцөлтэй мөрүүдийг байнга хайдаг бол энэ нь жирийн индексээс хамаагүй жижиг, хурдан байна.

sql

-- Зөвхөн "active" захиалгуудад индекс тавих
-- (хаагдсан захиалгыг хэзээ ч хайдаггүй тул тэдэнд индекс хэрэггүй)
CREATE INDEX idx_orders_active
    ON orders (created_at)
    WHERE status = 'active';

-- Энэ асуулга дээрх partial index-г ашиглана
SELECT * FROM orders
WHERE status = 'active' AND created_at > NOW() - INTERVAL '7 days';

-- NULL биш утгуудад partial index
CREATE INDEX idx_employees_manager
    ON employees (manager_id)
    WHERE manager_id IS NOT NULL;

Бүх захиалгын 5% нь active байвал индекс 95% жижиг болно — энэ нь ихээхэн зайн хэмнэлт юм.

Expression index — илэрхийлэлт индекс

Талбарын утгыг функцоор боловсруулсан үр дүнд индекс тавих боломжтой. Жишээлбэл, LOWER() функцоор жижиг үсгэнд хөрвүүлэн хайх нь нийтлэг хэрэглээ:

sql

-- Том/жижиг үсгийг үл харгалзан email хайхын тулд
CREATE INDEX idx_employees_email_lower
    ON employees (LOWER(email));

-- Энэ асуулга expression index-г ашиглана
SELECT * FROM employees
WHERE LOWER(email) = LOWER('Bold@Example.COM');

-- Огноогийн сараар индексжүүлэх
CREATE INDEX idx_orders_month
    ON orders (DATE_TRUNC('month', created_at));

SELECT * FROM orders
WHERE DATE_TRUNC('month', created_at) = '2024-01-01';

Expression index ашиглахын гол дүрэм: WHERE нөхцөлд бичсэн илэрхийлэл нь индекс үүсгэхэд ашигласан илэрхийлэлтэй яг адилхан байх ёстой.

Composite index — олон талбарын индекс

Олон талбарт нэгэн зэрэг индекс тавихад баганын дараалал маш чухал:

sql

-- (department, salary) дараалалтай composite index
CREATE INDEX idx_emp_dept_salary
    ON employees (department, salary);

-- ✅ Индексийг БҮРЭН ашиглана (эхний талбараар хайж байна)
SELECT * FROM employees WHERE department = 'Инженер';

-- ✅ Индексийг ашиглана (хоёр талбараар хайж байна)
SELECT * FROM employees WHERE department = 'Инженер' AND salary > 1500000;

-- ❌ Индексийг ашиглахгүй (эхний талбарыг алгасаж байна)
SELECT * FROM employees WHERE salary > 1500000;

Composite index нь зүүн талаасаа нэг нэгээр ашиглагддаг. Хайлтад хамгийн олон удаа ашиглагддаг талбарыг эхэнд тавих хэрэгтэй.

Covering index — INCLUDE

PostgreSQL 11-ээс INCLUDE гэсэн боломж нэмэгдсэн. Индексийн "навч" хэсэгт нэмэлт талбар хадгалж, хүснэгтийн мөрт огт хандалгүйгээр (index-only scan) асуулгыг биелүүлж болно:

sql

-- department-аар хайхад salary-г индексээс шууд авна
CREATE INDEX idx_emp_dept_covering
    ON employees (department)
    INCLUDE (salary, last_name);

-- Хүснэгтэд огт хандахгүйгээр index-only scan хийнэ
SELECT last_name, salary
FROM employees
WHERE department = 'Инженер';

Индексийн эрүүл байдлыг шалгах

sql

-- Ашиглагдаагүй индексүүдийг олох
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY tablename;

-- Хүснэгт дэх индексүүдийн хэмжээг харах
SELECT indexname,
       pg_size_pretty(pg_relation_size(indexname::regclass)) AS хэмжээ
FROM pg_indexes
WHERE tablename = 'employees';

Ашиглагдаагүй индекс нь дискний зай дэмий эзэлж, INSERT/UPDATE удаашруулдаг тул устгах нь дээр.

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

Байнга ашиглагддаг нарийн төвөгтэй асуулгуудыг view болгон хадгалж, хялбар нэрээр дуудаж сурна.

← Өмнөх хичээлИндекс үндэс Дараах хичээл →View үүсгэх ба ашиглах