Индекси в SQL

Проблем

Наскоро разглеждани

Магазинът е пораснал. Всеки път, когато някой отвори страница на продукт, в таблицата visits се записва ред: кой клиент (customer_id), кой продукт (product_id) и кога (seen_at). Тази таблица е само за този модул: в нея има 5 000 клиенти и 300 000 посещения.

SQLбаза: магазин

SELECT * FROM visits LIMIT 5;

SQLбаза: магазин

SELECT COUNT(*) FROM visits;

Страницата „Наскоро разглеждани“ пита базата за посещенията на един клиент. Пусни заявката и виж времето под резултата:

SQLбаза: магазин

SELECT COUNT(*) FROM visits WHERE customer_id = 1234;

Няколко милисекунди изглеждат малко. Но страницата се отваря от хиляди хора, а таблицата расте всеки ден.

Идеята

Базата чете всичко

Редовете в таблицата не са подредени по клиент. За да намери 60-те посещения на клиент 1234, базата чете всички 300 000 реда и проверява всеки.

Представи си, че търсиш думата „индекс“ в учебник без показалец. Трябва да прелистиш всяка страница. Затова в края на книгата има азбучен показалец: думите са подредени, а до всяка пише на кои страници е.

Индексът (index) на таблицата е същото. Това е отделно, подредено копие на една колона, в което до всяка стойност е записано къде е редът. Понеже е подредено, базата търси с разполовяване: поглежда в средата, разбира в коя половина е 1234, после в средата на нея и така нататък. За 300 000 стойности стигат около 19 стъпки.

Ето го на малка таблица с шест посещения, първо без индекс, после с индекс:

SELECT * FROM visits WHERE customer_id = 3;
CREATE INDEX idx_visits_customer ON visits(customer_id);
SELECT * FROM visits WHERE customer_id = 3;

Стъпка по стъпка, с думи

6 посещения. Редовете не са подредени по customer_id: 5, 3, 8, 3, 1, 6.
Без индекс базата прави SCAN: чете ред по ред и сравнява customer_id с 3.
Прочетени са 6 реда от 6, а съвпадат 2: редовете с id 2 и 4.
CREATE INDEX прави подредено копие на customer_id: 1, 3, 3, 5, 6, 8. До всяка стойност пише кой е редът.
С индекса е SEARCH. В средата е 5, повече от 3: значи 3 е в половината с по-малките стойности, над 5.
В средата на тази половина е 3. Над него е 1, по-малко: това е първото 3.
До 3 пише ред 2, до следващото 3 ред 4. После е 5: край. От таблицата се четат само 2 реда.

Индексът се прави с CREATE INDEX име ON таблица(колона). Пусни полето два пъти и сравни времената:

SQLбаза: магазин

CREATE INDEX IF NOT EXISTS idx_visits_customer ON visits(customer_id);
SELECT COUNT(*) FROM visits WHERE customer_id = 1234;

Първото пускане строи индекса, затова е бавно. При второто индексът вече го има (IF NOT EXISTS пропуска създаването, както при CREATE TABLE), и остава само търсенето. Сравни го с времето на заявката без индекс в предишната карта: разликата е стотици пъти.

По-точно, за любопитните

Индексите в SQLite, PostgreSQL, MySQL и SQL Server по подразбиране са B-дървета (B-tree). Това не е списък, в който се разполовява, а дърво от страници: всяка страница пази стотици подредени стойности и сочи към следващото ниво. Затова дори за милиони редове стигат три-четири прочетени страници. Идеята е същата: подредено, и търсенето всеки път изхвърля почти всичко, което не е нужно.

Пробвай

Колко пъти е разглеждан продуктът

Страницата на всеки продукт ще показва колко пъти е разглеждан. Направи индекс, който ускорява търсенето по продукт, и после преброй посещенията на продукт 7.

SQLбаза: магазин

SELECT COUNT(*) FROM visits WHERE product_id = 7;

Решение

CREATE INDEX idx_visits_product ON visits(product_id);
SELECT COUNT(*) FROM visits WHERE product_id = 7;

Идеята

Индекси, които вече имаш

Търсенето по първичен ключ е бързо и без CREATE INDEX:

SQLбаза: магазин

SELECT * FROM visits WHERE id = 150000;

В SQLite колона INTEGER PRIMARY KEY е самата подредба на таблицата: редовете са записани по id. Затова отделен индекс не трябва.

За UNIQUE базата прави индекс сама. Без него при всеки нов имейл щеше да чете всички клиенти, за да провери дали го има. SQLite пази описанието на базата в таблицата sqlite_master. Виж кои индекси има в магазина:

SQLбаза: магазин

SELECT name, tbl_name FROM sqlite_master WHERE type = 'index';

Първият е за email в customers. Вторият е за първичния ключ на order_items, който е от две колони (order_id, product_id) и не е INTEGER PRIMARY KEY.

Виж го

DROP INDEX

Индекс се маха с DROP INDEX име. Данните не се променят: махат се само показалецът и бързото търсене. В това поле индексът по клиент вече е направен. Пусни заявката и виж времето:

SQLбаза: магазин

DROP INDEX idx_visits_customer;
SELECT COUNT(*) FROM visits WHERE customer_id = 1234;

Времето е пак като без индекс. Ако пуснеш полето втори път, SQLite ще каже, че такъв индекс няма. Бутонът „Нова база“ връща началното състояние.

Упражнение

Две задачи

1. Отчетът „посещения по дни“ се пуска всяка сутрин. Направи индекс, който го ускорява, и с него преброй посещенията на 1 март 2026 г.

Индекс за търсене по дата и броят посещения на 2026-03-01:

SQLбаза: магазин

SELECT COUNT(*) FROM visits WHERE seen_at = '2026-03-01';

Решение

CREATE INDEX idx_visits_seen ON visits(seen_at);
SELECT COUNT(*) FROM visits WHERE seen_at = '2026-03-01';

2. Страницата с броя разглеждания на продукт е спряна. В базата има два индекса: idx_visits_customer и idx_visits_product. Махни този, който вече не е нужен.

Махни индекса, който никой не ползва:

SQLбаза: магазин

SELECT name FROM sqlite_master WHERE type = 'index';

Решение

DROP INDEX idx_visits_product;

Тест

Провери се

1. Таблицата няма индекс по customer_id. Какво прави базата за WHERE customer_id = 1234?Отива направо на правилните редовеВръща грешкаЧете всички редове и проверява всеки

2. Защо в индекса се търси бързо?Защото е в паметтаЗащото стойностите са подредени и търсенето разполовява оставащата частЗащото е по-малък от таблицата

3. Таблицата customers има email TEXT NOT NULL UNIQUE и id INTEGER PRIMARY KEY. Кое търсене ще е бързо и без нов индекс?Само по emailСамо по idПо id и по email

4. Какво става с данните, когато пуснеш DROP INDEX?Таблицата се изпразваНищо: махат се само показалецът и бързото търсенеИзтриват се редовете, които индексът сочи

Индекси

Бисквитки