Проблема с производительностью таблицы oauth2_authorization в масштабе — столбец BLOB без индекса ⇐ MySql

[Anonymous]

Я использую Spring Authorization Server с MySQL 8.4 на AWS RDS. В нашей таблице oauth2_authorization накопилось около 600 000 записей, и мы испытываем серьезные проблемы с производительностью при поиске кода авторизации.
Проблема
Столбец авторизации_код_значение определен как BLOB без индекса. Когда выполняется JdbcOAuth2AuthorizationService:

Код: Выделить всё

SELECT * FROM oauth2_authorization WHERE authorization_code_value = ?

Это приводит к полному сканированию таблицы, что приводит к:

• Время запроса: 1–2 секунды на каждый поиск кода авторизации
• Скачки ЦП во время пикового трафика
• Случайные ошибки таймаута авторизации

Текущая схема таблицы
/>

Код: Выделить всё

CREATE TABLE oauth2_authorization (
id varchar(100) NOT NULL,
registered_client_id varchar(100) NOT NULL,
principal_name varchar(200) NOT NULL,
authorization_grant_type varchar(100) NOT NULL,
...
authorization_code_value blob DEFAULT NULL,
authorization_code_issued_at timestamp DEFAULT NULL,
authorization_code_expires_at timestamp DEFAULT NULL,
...
PRIMARY KEY (id)
);

Производительность запросов

Код: Выделить всё

EXPLAIN SELECT * FROM oauth2_authorization
WHERE authorization_code_value = 'xxx';

-- Result:
-- type: ALL (full table scan)
-- rows: ~600000
-- Extra: Using where

Мое предлагаемое решение
Добавление индекса на основе хеша:

Код: Выделить всё

ALTER TABLE oauth2_authorization
ADD COLUMN authorization_code_hash VARCHAR(64)
AS (SHA2(authorization_code_value, 256)) STORED,
ADD INDEX idx_authorization_code_hash (authorization_code_hash);

Затем настраиваем JdbcOAuth2AuthorizationService для использования:

Код: Выделить всё

WHERE authorization_code_hash = SHA2(?, 256)
AND authorization_code_value = ?

Этот подход аналогичен тому, что Spring Session JDBC использует для поиска сеансов.
Вопросы

• Известна ли это проблема при крупномасштабном развертывании?
• Каков рекомендуемый подход для обработки таблицы oauth2_authorization в масштаб?
• Должны ли мы внедрить механизм автоматической очистки для кодов авторизации с истекшим сроком действия?
• Будет ли мое решение с индексированием на основе хэша считаться лучшей практикой или есть лучший подход?

Дополнительный контекст

• Коды авторизации имеют 10 минут истечение срока действия
• Высокий объем трафика приводит к быстрому накоплению записей.
• В настоящее время стратегия очистки отсутствует — записи накапливаются бесконечно.
• Рассматриваем возможность реализации запланированного задания очистки, но сначала хотим понять лучшие практики.

Любые рекомендации или опыт других пользователей, использующих Spring Authorization Server в больших масштабах, будем очень признательны!
Среда

• Сервер авторизации Spring: 1.x
• Spring Boot: 3.x
• База данных: MySQL 8.4 (AWS RDS)
• Записи: ~600 000 в таблице oauth2_authorization

Подробнее здесь: https://stackoverflow.com/questions/798 ... mn-without