Как жить, если у тебя не highload?

Почему я?

• Работаю с БД с 96-го
• Пробовал dBase IV (во времена Turbo C++ и Clipper)
• Перешёл на SQL (во времена Borland Interbase/Firebird)
• Завалил кучу секций по БД на собеседованиях
• Писал под кучу движков БД и в голове каша

Одинаково плохо пишу код на всём

Обо мне

Меня зовут Валерий Горбачев
Мне 41 год
Работаю программистом
Сейчас использую: PHP, JS, MSSQL

В профессии давно

Каково работать в веб-студии

В маленькой веб-студии ты можешь проявить себя в любой роли:

Но обычно — ты человек-оркестр

Все истории происходили в маленькой веб-студии, но иногда происходят и на больших проектах...

Условия, когда БД не будет использовать индекс?

Первое что приходит на ум:
Под критерий отбора попадает большой процент строк таблицы

Что можно узнать всего за неделю

Третья нормальная форма

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

Определение нормализации с сайта - Википедии

Структура базы данных должна быть обязательно нормализованной.

Преподаватель моего ВУЗа

Пишем учётную систему для бизнеса

Задача
Необходимо веб-приложение, чтобы регистрировать корреспонденцию, выдавать поручения сотрудникам (и многое другое)

Сроки - вчера

Что спроектировали

Таблицы представлены в урезанном и очень упрощённом виде

Что спроектировали

Таблицы представлены в урезанном и очень упрощённом виде

Что хотел заказчик

Если у вас MVP - не стоит тратить кучу времени на выработку супер-нормализованной структуры.

Хранение заказов

Типовая схема структуры хранения заказов в интернет-магазине. Всё отлично?
 

Хранение заказов

Типовая схема структуры хранения заказов в интернет-магазине. Всё отлично?
Да, но нет (c) Саша

Хранение заказов. Денормализация

Хранение заказов. Денормализация

Денормализация обусловленная доменной логикой.

Не нормализация, но полезно

Не надо усложнять

Задача на собеседовании:

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

Ну и чего думать? Тут же всё просто.

Не надо усложнять

select * from `banners` b
where `banner_id` not in (select `ub`.`banner_id` from `user_banners` `ub` where `ub`.`user_id` = 1)
order by rand() limit 1

А если баннеров несколько миллионов и надо выбирать быстрее?

Решение на поверхности

Не надо усложнять

select `b`.* from `banners` b
where `b`.`id`>(select `u`.`current_banner_id` from `users` `u` where `u`.`id` = 1)
order by `b`.`id` limit 1

-- Это храним в кэше
select max(`b`.`id`) as maxIndex from `banners` b

Не используйте * в запросах

Вроде бы всё нормально

select * from `banners` `b`
join `user_banners` `ub` on `ub`.`banner_id` = `b`.`id`

Не используйте * в запросах

А если так?

select * from `banners` `b`
join `user_banners` `ub` on `ub`.`banner_id` = `b`.`id`
join `users` `u` on `u`.`id` = `ub`.`user_id`

Не используйте * в запросах (иногда можно)

select * from `banners` `b`
join `user_banners` `ub` on `ub`.`banner_id` = `b`.`banner_id`
join `users` `u` on `u`.`user_id` = `ub`.`user_id`

Не используйте * в запросах (иногда можно)

Стратегия именования полей с добавлением имени таблицы к имени поля - поможет вам
Но я бы порекомендовал указывать необходимые поля, или хотя бы использовать `alias`.*

Мы чинили магазин. Часть первая: Сортировка

В результатах поиска на первой странице последний товар с грушами.
Если мы переключимся на вторую страницу результатов поиска, то первым товаром увидим те же груши с тем же артикулом.
Ситуация рандомная, не всегда один и тот же товар задваивается.

Из описания задачи

Мы чинили магазин. Часть первая: Сортировка

SELECT "products"."id","prices"."value" FROM "products"
LEFT JOIN "prices" ON ("products"."id" = "prices"."products_id") AND ("prices"."pricetypes_id"=3)
WHERE ("products"."deleted" IS NULL) AND ("products"."published"=1)
ORDER BY "products"."is_avail" DESC, "prices"."value" DESC
OFFSET 0 LIMIT 20

Мы чинили магазин. Часть первая: Сортировка

OFFSET 0
LIMIT 20

OFFSET 20
LIMIT 20

Мы чинили магазин. Часть первая: Сортировка

И снова всё просто - уточняйте сортировку

ORDER BY "products"."is_avail" DESC, "prices"."value" DESC, "products"."id" ASC

После того как запрос выдал таблицу результатов (после обработки списка выборки), её можно отсортировать. Если сортировка не задана, строки возвращаются в неопределённом порядке. Фактический порядок строк в этом случае будет зависеть от плана соединения и сканирования, а также от порядка данных на диске, поэтому полагаться на него нельзя. Определённый порядок выводимых строк гарантируется, только если этап сортировки задан явно.

Цитата из документации, но кто же её читает.

Индексы никто не любит?

Мы чинили магазин. Часть вторая: индексы

Очевидные факты

• Лишние индексы замедляют при частом обновлении
  
• Индексы на каждом поле - не заменят составные
• Индексы по функциям не работают

Очевидные действия: оптимизируем

Мы чинили магазин. Часть вторая: индексы

Что мы сделали:

1. Проверили анализ текущего состояния с использованием pg_stat_*

   select * from pg_stat_user_indexes order by idx_scan asc
   

   Например вот так, можно найти неиспользуемые индексы
   Админкой сайта никто не пользуется для управления товарами. И мы пожертвовали этими индексами

2. Проанализировали частые запросы
   

   Добавили составных индексов, с учётом их порядка в запросах (WHERE + ORDER)

Мы чинили магазин. Часть вторая: индексы

И еще:

1. Избавились от LIKE.
   

   Теперь мы используем Sphinx

2. Добавили триггеров и полей, чтобы заменить некоторые вычисления в запросах

   ORDER BY ("products"."available">0) DESC, "prices"."value" DESC, "products"."id" ASC
   

3. Переписали кучу запросов начитавшись советов в интернетах

Давайте повторим (только не ругайтесь)

• На маленьких таблицах можно обойтись без индексов (только PRIMARY)
• Не забывайте про FOREIGN KEY
• Не добавляйте индексы на все поля
• Лучше не использовать функции во WHERE и ORDER
• Порядок ключей в составных индексах имеет значение (не во всех БД)
• Самый эффективный индекс - для полей с редкими значениями
• Делайте EXPLAIN

Используем Sphinx вместо LIKE

• Товар с поисковым словом в названии, должен быть выше, чем если это слово в описании товара
• Желательно игнорировать окончания и склонения слов

Используем Sphinx вместо LIKE

$products = $sphinxSearch->search($query);
$ids = ArrayHelper::getColumn($products, 'id');

$productsDetail = Product::find()->where(['in','id',$students_ids])
            ->indexBy('id')->all();

$result = [];
foreach ($products as $product) {
   ...
}

Я подумал, что мы не можем забрасывать проблемы железом.

Используем Sphinx вместо LIKE

А если по другому? И не перебирать данные в PHP?

Мы сделали вот так:

SELECT "products".* FROM "products"
WHERE "products"."id" IN (28, 12, 53, 2)
ORDER BY ARRAY_POSITION(ARRAY[28, 12, 53, 2],"products"."id")

А можно было сделать лучше?
Не используя функцию в ORDER BY и без огроменного IN(...)

Что удалось нагуглить

IN () + FIELD( `products`.`id`, 28, 12, 53, 2)

SELECT "p".* FROM "products" "p"
JOIN (VALUES (0, 20296), (1, 20308), (2, 20333) ) AS spv(ind, pid) ON "spv"."pid" = "p"."id"
ORDER BY "spv"."ind"

Решение подсказанное старшими коллегами

PostgreSQL с использованием JSON

SELECT "p".* FROM "products" "p"
JOIN json_to_recordset('[{"ind": 0, "pid": 20296}, {"ind": 1, "pid": 20308}, {"ind": 2, "pid": 20333}]') AS spv(ind int, pid int) ON "spv"."pid" = "p"."id"
ORDER BY "spv"."ind"

И снова - надо было читать документацию

CTE - обобщённые табличные выражения

Используйте CTE не только для рекурсии, но и к примеру, для построения отчётов. Это удобно.

WITH "sales" (minsum, maxsum, avgsum, uid) AS (
    SELECT MIN("itemsSum"), MAX("itemsSum"), AVG("itemsSum"), "uid"
    FROM "orders"
    GROUP BY "orders"."uid"
)

SELECT "sales".*,"user"."username" FROM "sales"
JOIN "user" ON "user"."id" = "sales"."uid"

Учётная система для бизнеса. Подзапросы

Исходные данные: Давайте к списку задач, выведем сколько у нас не прочитанных комментариев

SELECT `task`.*, (SELECT COUNT(*) FROM `comment` `cm`
   LEFT JOIN `lastvisit` `lv` ON `cm`.`tid` = `lv`.`docid`
   WHERE `cm`.`tid` = `docs`.`id` AND `cm`.`date` > `lv`.`dt` AND `lv`.`uid` = :uid) AS notreadCommentCount ...

А давайте еще добавим текст, автора и дату последнего комментария.

И нам на помощь приходят CTE, OUTER APPLY, LATERAL JOIN
А нет, их же нет в MySQL 5

Но задачу нужно решить к завтрашнему дню...

Учётная система для бизнеса. Подзапросы

Как мы решили задачу в 2014 году - понятно, просто добавили идентификатор последнего комментария в таблицу задач и сделали LEFT JOIN

Но как можно решить задачу в 2020? (Или просто используя другой SQL диалект)

SQL Server

SELECT [task].*, cm.*
FROM [task]
OUTER APPLY (
   SELECT TOP 1 cm.uid, cm.text, cm.created
   FROM [comment] cm
   WHERE cm.tid = task.tid
   ORDER BY cm.created DESC
) cm

PostgreSQL

SELECT "task".*, "cm".*
FROM "task"
LEFT JOIN LATERAL (
   SELECT "cm"."uid", "cm"."text", "cm"."date"
   FROM "comment" "cm"
   WHERE "cm"."tid" = "task"."tid"
   ORDER BY "cm"."created" DESC LIMIT 1
) "cm" ON "cm"."tid" = "task"."tid"