Индексы и транзакции

Индекс — упорядоченная структура из значений столбца и ссылок на строки, которая ускоряет поиск. Транзакция — группа команд, выполняемая целиком или никак.

Почему запрос бывает медленным

Когда вы просите базу найти все услуги категории 7, она по умолчанию делает самое простое: читает таблицу подряд, строка за строкой, и проверяет каждую. Это называется полным просмотром (в плане запроса его зовут Seq Scan, от sequential scan). На сотне строк вы этого не заметите, на миллионе заметите очень: база прочтёт миллион строк ради десяти нужных.

Индекс: указатель в конце книги

В конце хорошей книги есть алфавитный указатель: «договор: 14, 87, 203». Вы не листаете всю книгу ради слова «договор», а смотрите в указатель и идёте на нужные страницы. Индекс в базе устроен так же: это отдельная упорядоченная структура из значений столбца и ссылок на строки.

Чаще всего это дерево: на каждом уровне значения разложены по порядку, и один шаг отбрасывает почти всё. Дерево с ветвлением в сотни ветвей находит одну строку из миллионов за три-четыре шага. Поэтому поиск по индексу почти не замедляется, когда таблица растёт в тысячу раз.

Подвигайте схему. Слайдер размера таблицы и выбор «сколько строк подходит» показывают, сколько страниц прочитает база с индексом и без него. Попробуйте самые маленькие таблицы и самые частые значения.

Индекс Транзакция

Запрос: SELECT * FROM items WHERE category_id = 7. Это модель, а не замер: на странице помещается 100 строк, а дерево индекса ветвится на 200.

Строк в таблице: 100 000 Сколько строк подходит: одна строка0,1% строккаждая десятая (10%)половина (50%) Лишних индексов: 0

• Без индекса: читать всё подряд 1 000
• С индексом: дерево (3) + страницы строк (100) 103

Планировщик возьмёт индекс. Строк подходит мало, а таблица велика: идти по дереву быстрее, чем читать все 1 000 страниц. В 10 раз меньше работы.

Цена за запись: вставка одной строки пишет в таблицу, в индекс первичного ключа и ещё в дополнительные индексы, которых сейчас 0. Всего записей: 2. Лишние индексы замедляют запись и занимают место.

Текстовая альтернатива: на большой таблице с редким значением индекс читает в десятки и сотни раз меньше страниц, чем просмотр всей таблицы. На маленькой таблице или при частом значении индекс не помогает, и база читает таблицу целиком. Каждый дополнительный индекс удорожает запись.

Что вы должны были заметить.

• Выигрыш растёт вместе с таблицей. На таблице из ста строк разницы нет. На миллионе строк при редком значении индекс читает в сотни раз меньше.
• Индекс не всегда нужен. Если подходит половина таблицы (пол, да/нет, «активен»), проще прочитать её всю: ходить по индексу за каждой строкой дороже. Планировщик (часть базы, которая выбирает способ выполнения) сам это решает.
• Каждый индекс стоит денег. Вставка и изменение должны обновить и таблицу, и каждый индекс. Пять индексов это пять дополнительных записей при каждой заявке. Они же занимают место.

Правила, которые из этого следуют:

1. Индекс строят под конкретный частый запрос, который тормозит, а не «на всякий случай» по каждому столбцу.
2. Первичный ключ и столбцы с UNIQUE получают индекс сами. А вот по внешним ключам (category_id) PostgreSQL индекс сам не создаёт, и соединения по ним часто становятся быстрее после ручного индекса.
3. Индекс может быть составным: ON items (category_id, day) помогает запросам, которые фильтруют по категории (и ещё и по дате). Порядок столбцов важен: такой индекс служит запросам по category_id и по паре, но не запросам только по day.

Проверьте себя: стоит ли строить индекс по столбцу «пол» с двумя значениями?

Почти никогда. Условие «пол = ж» подходит примерно половине таблицы, и проще прочитать таблицу подряд, чем ходить по индексу за каждой строкой.

Как посмотреть, что делает база: EXPLAIN

Угадывать не нужно: перед любым запросом можно написать слово EXPLAIN, и база покажет план, как она собирается его выполнять.

EXPLAIN SELECT * FROM items WHERE category_id = 7;

В плане настоящего PostgreSQL вы увидите Seq Scan on items (читает всё) или Index Scan using … on items (идёт по индексу), а ещё оценку стоимости в условных единицах. Версия EXPLAIN ANALYZE ещё и выполняет запрос и измеряет настоящее время. В практике ниже песочница выводит упрощённую модель плана: сколько строк она прочитала. Это учебный разборщик, а не настоящий PostgreSQL, и числа в его плане другие, но видна та же разница между просмотром всей таблицы и индексом.

Транзакция: всё или ничего

Теперь вторая тема, про сбои. Покупка билета это две записи: списать место и записать заказ. Если сервер упадёт между ними, мы получим списанное место без заказа. Билет пропал, и никто не узнает. Нужно, чтобы две записи проходили вместе или никак.

Это делает транзакция: группа команд, которую база считает одним неделимым действием.

BEGIN;
UPDATE seats SET free = free - 1;
INSERT INTO orders (name, phone) VALUES ('…', '…');
COMMIT;   -- всё стало окончательным

BEGIN открывает транзакцию, COMMIT подтверждает все изменения разом, ROLLBACK отменяет все. Если сервер упал посреди, то до COMMIT всё откатится само. Посмотрите на вкладку «Транзакция» в схеме: режим без транзакции оставляет данные в сломанном состоянии, режим с транзакцией нет.

Свойства транзакций собирают в слово ACID:

• Atomicity (атомарность): всё или ничего.
• Consistency (согласованность): после транзакции правила данных соблюдены, внешние ключи ведут куда надо, ограничения не нарушены.
• Isolation (изолированность): две одновременные транзакции не видят чужих незавершённых правок. Это то, что в первом уроке не давало двум редакторам затереть друг друга.
• Durability (долговечность): то, что подтверждено COMMIT, не пропадёт, даже если сразу после этого отключится питание.

У изолированности есть уровни: чем строже, тем безопаснее и тем чаще транзакциям приходится ждать друг друга. PostgreSQL по умолчанию выбирает умеренный (READ COMMITTED), а самый строгий называется SERIALIZABLE. На вашем сайте почти всегда хватает умеренного.

Последняя мелочь, но она спасает в магазинах и в записи на приём. Два покупателя нажали «купить» на последнее место одновременно. Нельзя сначала прочитать число мест, а потом записать «число минус один»: между этими шагами второй покупатель прочитает то же число. Надёжнее одной командой: UPDATE seats SET free = free - 1 WHERE free > 0, и затем проверить, изменилась ли строка. Условие и изменение выполняются неделимо, и второй покупатель получит отказ.

Практика

Вы в эпохе 2022 года: сайты теперь живут на облачных базах, а проектировщики думают про нагрузку заранее. Возьмите таблицу из двух тысяч услуг и ускорьте запрос по категории, а потом запишите заявку так, чтобы сбой её не испортил.

В странице три готовые таблицы и пять полей. В первом спросите план до индекса, во втором создайте индекс и спросите ещё раз (сколько строк просмотрено, впишите числа в строку под полем). Затем в третьем напишите транзакцию из двух вставок, в четвёртом сымитируйте сбой (без COMMIT), в пятом откатите и убедитесь, что новых заявок нет.

Что было не так: MySQL без транзакций

Долгие годы самым популярным движком таблиц в MySQL был MyISAM: быстрый, простой и без транзакций и внешних ключей. На нём работали тысячи сайтов на PHP, и сбой посреди двух записей оставлял в базе полусобранные заказы. Движок InnoDB, где всё это есть, стал стандартным только в MySQL 5.5, в конце 2010 года. Сегодня считается нормой, что база умеет транзакции, и никто не хочет объяснять покупателю, куда делся билет.