Интерактивен учебник по информатика

Защо заявката е бавна

Върху учебната база с 9 ученици всяка заявка е мигновена и не личи дали е написана добре. Реалните бази са други: дневникът на цяло училище за десет години, продажбите на верига магазини, логовете на един сайт — милиони редове. Там заявка, която „работи“, може да отнема минути, а същата заявка с един добавен индекс — милисекунди.

Помислете как търсите дума в речник. Не четете от първата страница — отваряте по средата, гледате буквата, отваряте пак по средата на нужната половина. При 1000 страници стигате за около 10 отваряния. Речникът може така, защото е подреден. Таблицата в базата не е — редовете стоят по реда на вмъкване. За да намери WHERE grad = 'Варна', СУБД трябва да прочете всеки ред. Това се нарича пълно сканиране (full scan).

Какво е индекс

Индекс

Отделна структура, която пази стойностите на една или няколко колони подредени, а до всяка стойност — указател към реда в таблицата. Като азбучника в края на учебник: подреден списък от думи с номера на страниците.

Индексът е подредено копие на колоната с указатели. Таблицата остава каквато е.

Реалният индекс не е плосък списък, а Б-дърво (B-tree): на всяко ниво стойността се сравнява с няколко граници и търсенето продължава само в един клон. При милион реда листата се достигат за 3–4 стъпки. Дървото се преподрежда само при всяко вмъкване и изтриване — това е и цената му.

Б-дърво: всяко ниво отсича по-голямата част от редовете, докато се стигне до листото със стойността.

-- обикновен индекс по една колона
CREATE INDEX idx_prodazhbi_grad ON prodazhbi(grad);

-- съставен индекс: първо по клас, после по успех
CREATE INDEX idx_students_class_grade ON students(class_id, grade);

-- уникален индекс: и ускорява, и забранява повторения
CREATE UNIQUE INDEX ux_potrebiteli_email ON potrebiteli(email);

-- премахване
DROP INDEX idx_prodazhbi_grad;                 -- SQLite
DROP INDEX idx_prodazhbi_grad ON prodazhbi;    -- MySQL

Кога помага и кога пречи

Помага

WHERE grad = 'Варна', WHERE suma BETWEEN 100 AND 200, WHERE ime LIKE 'Ив%' — търсене и диапазон по индексираната колона.
JOIN … ON m.student_id = s.id — колоните на съединението, особено външните ключове.
ORDER BY grad — редовете вече са подредени, не се сортират наново.
Колоната е селективна: стойността отсича повечето редове. Индекс по ЕГН отсича всичко освен един ред; индекс по колона „пол“ отсича половината — почти без полза.

Пречи или не помага

Малки таблици — пълното сканиране на 9 реда е по-бързо от обхождане на дърво.
Таблици с много INSERT/UPDATE/DELETE — всеки запис обновява и всеки индекс. Пет индекса = шест записа.
Функция върху колоната: WHERE UPPER(grad) = 'ВАРНА' или WHERE YEAR(data) = 2026 — индексът пази grad, не UPPER(grad), и не се ползва.
LIKE '%ов' — търсене отзад; азбучникът е подреден отпред.
Заема място на диска — при големи таблици колкото самата таблица.

	С индекс	Без индекс
Търсене по колоната	Б-дърво, няколко стъпки	Пълно сканиране на всеки ред
Вмъкване и промяна	По-бавно — дървото се обновява	По-бързо
Място	Отделна структура на диска	Нищо допълнително
Резултат от заявката	Същият	Същият

Как да видим дали индексът се ползва

Не гадаем — питаме СУБД за плана на изпълнение. В MySQL пред заявката се пише EXPLAIN, в SQLite — EXPLAIN QUERY PLAN. Резултатът не е данните, а описание как ще се стигне до тях.

-- SQLite (в приложението): добавете пред SELECT
EXPLAIN QUERY PLAN
SELECT grad, COUNT(*) FROM prodazhbi WHERE grad = 'Варна';
-- без индекс:  SCAN prodazhbi
-- с индекс:    SEARCH prodazhbi USING INDEX idx_prodazhbi_grad (grad=?)

-- MySQL (в phpMyAdmin):
EXPLAIN SELECT grad, COUNT(*) FROM prodazhbi WHERE grad = 'Варна';
-- гледайте колоните type, key и rows:
--   type = ALL, key = NULL, rows = 200000   -> пълно сканиране
--   type = ref, key = idx_prodazhbi_grad, rows = 40000 -> индексът работи

В час: измерване в phpMyAdmin

В приложението 5000 реда се обработват за милисекунди и разлика не се вижда. За да я усетите, трябва таблица с поне 100 000 реда в MySQL. phpMyAdmin изписва под всеки резултат „Заявката отне X секунди“ — това е вашият хронометър. Изпълнете в раздела SQL:

-- 1) таблица и 200 000 реда (MySQL 8 / MariaDB 10.3+; отнема няколко секунди)
CREATE TABLE prodazhbi (
  id INT AUTO_INCREMENT PRIMARY KEY,
  grad VARCHAR(20) NOT NULL,
  suma INT NOT NULL
);
-- MySQL 8 ограничава дълбочината на рекурсията до 1000. В MariaDB този ред
-- дава "Unknown system variable" - прескочете го, там ограничение няма
SET SESSION cte_max_recursion_depth = 1000000;
INSERT INTO prodazhbi (grad, suma)
WITH RECURSIVE seq(n) AS (
  SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 200000
)
SELECT ELT(n % 5 + 1, 'София', 'Пловдив', 'Варна', 'Бургас', 'Русе'),
       (n * 37) % 500 + 1
FROM seq;

-- 2) заявката, която ще измерваме - изпълнете я 3 пъти, запишете времената
SELECT grad, COUNT(*) AS broy, SUM(suma) AS obshto
FROM prodazhbi WHERE grad = 'Варна';

-- 3) планът преди индекса
EXPLAIN SELECT grad, COUNT(*) FROM prodazhbi WHERE grad = 'Варна';

-- 4) индексът, после пак 2) и 3)
CREATE INDEX idx_prodazhbi_grad ON prodazhbi(grad);

Запишете в тетрадката: време преди индекса (три измервания), стойността на type/key/rows от EXPLAIN, време след индекса, новите type/key/rows.
Резултатът (broy, obshto) еднакъв ли е преди и след? Трябва да е.
Пуснете WHERE suma = 250 — колоната без индекс. Колко време отнема? Помага ли индексът по grad?
Пуснете WHERE UPPER(grad) = 'ВАРНА'. Ползва ли се индексът според EXPLAIN? Защо?
Ако остане време: измерете INSERT на още 50 000 реда с индекса и без него (DROP INDEX … ON prodazhbi).

Какво трябва да запомниш

Индекс = подредено копие на колона с указатели към редовете; вътрешно е Б-дърво.
Помага при WHERE, JOIN и ORDER BY по индексираната колона, когато колоната е селективна.
Пречи при малки таблици и при много записи; не се ползва при функция върху колоната и при LIKE '%…'.
Съставният индекс работи от първата колона нататък — редът на колоните има значение.
Индексът не променя резултата на заявката, само времето — EXPLAIN показва дали се ползва.
Първичният ключ винаги е индексиран; уникалният индекс освен това забранява повторения.