Задачка по БД (на написание запроса sql): подсчет заблокированных пользователей

Давненько не разбирал задачки…. Что ж, продолжим, итак … задачка на написание оптимального SQL запроса.

Задача

У вас есть таблица пользователей — 20 млн строк
Каждый день по 20 тыс регистраций.
В таблице есть колонки:

«`
CREATE TABLE users (
id INT UNSIGNED AUTO_INCREMENT
PRIMARY KEY,
name VARCHAR(50) NOT NULL,
nickname VARCHAR(40),
email VARCHAR(60),
created_at TIMESTAMP DEFAULT
CURRENT_TIMESTAMP(),
banned_at TIMESTAMP DEFAULT NULL,
status TINYINT(1) DEFAULT 1
);
«`

— При регистрации пользователь активен
— Есть дата регистрации в поле created_at
— если пользователь заблокирован мы записываем метку времени в banned_at и меняем status на 0
— Блокируется не более 1% от всех зарегистрированных за день пользователей

Вопрос:
1. как написать запрос, чтобы посчитать кол-во пользователей, которые были заблокированы и зарегистрированы сегодня на чистом SQL

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

Решаем

Итак, нужны пользователи, которые:
— зарегистрированы сегодня (created_at >= текущая дата и меньше завтрашней)
— и при этом заблокированы (banned_at IS NOT NULL)

Причем статус status = 0 — это, судя по описанию, просто дублирование banned_at. Потому что если заблокирован, то banned_at проставляется И status меняется на 0.
Значит для подсчёта нам достаточно banned_at, status можно игнорировать. Или наоборот — но один из них лишний для этого запроса.

Самый прямой вариант запроса:

SELECT COUNT(*) 
FROM users 
WHERE created_at >= CURDATE() 
  AND created_at < CURDATE() + INTERVAL 1 DAY
  AND banned_at IS NOT NULL;

Либо если использовать DATE() — но на больших таблицах DATE(created_at) убьёт индексы, потому что применит функцию к колонке.
Так что лучше как выше — диапазоном.

Если в проекте используют UTC и часовые пояса, то CURDATE() может дать не тот день.
Тогда надо либо приводить к нужной таймзоне, либо передавать конкретную дату из приложения.
Но в условиях про часовые пояса ничего нет — оставим CURDATE().

Какой нам понадобится индекс…

Тут надо подумать, как MySQL будет выполнять запрос.

У нас есть два условия:
— фильтр по created_at (диапазон)
— фильтр по banned_at (IS NOT NULL)

Создадим индекс на (created_at, banned_at):
— created_at идёт первым — потому что диапазон. MySQL сможет быстро найти все записи за сегодня, а потом уже внутри них отфильтровать по banned_at.
— Если поставить banned_at первым — индексу придётся перебирать все записи с banned_at IS NOT NULL, а это, судя по условию «блокируется не более 1% от зарегистрированных за день», будет примерно 200 человек в день. Но за всё время — на 20 млн строк это может быть всё ещё много. А главное — created_at останется не проиндексированным нормально.

CREATE INDEX idx_created_banned ON users (created_at, banned_at);

Дополнительно: если в таблице много разных запросов, возможно имеет смысл добавить покрывающий индекс (covering index), где включить и другие поля, но для этого конкретного запроса достаточно двух полей.

Ещё один момент (про статус)

В условии сказано, что при блокировке status меняется на 0. Тогда можно было бы написать запрос через status = 0 AND created_at >= CURDATE() … — это даже чуть проще, потому что banned_at можно вообще не трогать. Но тогда непонятно, зачем вообще banned_at. Скорее всего, его используют для истории или для разблокировки. Я бы уточнил у заказчика, что правильнее использовать для подсчёта blocked — banned_at или status. Но если исходить из чистого SQL и условия задачи, я взял бы banned_at.

Итого:

1. Запрос:

SELECT COUNT(*) FROM users WHERE created_at >= CURDATE() AND created_at < CURDATE() + INTERVAL 1 DAY AND banned_at IS NOT NULL;

2. Индекс:

CREATE INDEX idx_created_banned ON users (created_at, banned_at);