Мобильная версияМы используем API разбиения на страницы Java/Spring GET/POST getAll поверх MySQL в AWS RDS и нам нужна помощь в оптимизации точного отфильтрованного количества
Текущее состояние:
Основной набор данных — курсы с реляционными столбцами, такими как id, tenant_id, созданный_by, созданный_at, обновленный_at, удаленный_at плюс документ значений_json, содержащий такие ключи, как имя, псевдоним, уровень, страна, валюта, символ_валюты, название отдела, международный_студент, внутренний_студент, код_курса, экстракция_дата и институты;
В нашем потоке getAll (/models/get/courses) мы всегда выполняем как countQuery, так и selectQuery (для строгих метаданных Spring с разбивкой на страницы), с динамическими фильтрами, разрешенными для столбцов таблицы и произвольными Ключи JSON
Причина:
Текущим узким местом задержки является запрос подсчета, который может занять ~35–39 секунд для некоторых фильтров (особенно предикатов типа LIKE/contains над выражениями JSON), тогда как выбор выполняется сравнительно быстро;
Размер таблицы составляет около 327 406 строк;
Как выглядят запросы на подсчет и выбор:
Запрос на подсчет:
SELECT count(*) FROM devdts.courses s WHERE (s.tenant_id IS NULL OR s.tenant_id = 'abcd') AND s.deleted_at IS NULL AND ((((LOWER(CAST(JSON_UNQUOTE(COALESCE(JSON_EXTRACT(s.values_json, '$.institutes.id'), JSON_EXTRACT(s.values_json, '$.institutes'), JSON_EXTRACT(s.values_json, '$.institutes'))) AS CHAR)) LIKE :institutes_is_any_of_0_96c54c95_c730_4bec_b63d_08d98f643553))))
Выберите запрос:
SELECT s.*, JSON_SET(s.values_json, '$.institute_rank', IFNULL(JSON_MERGE_PATCH(JSON_OBJECT('id', u_institute_rank.id), IFNULL(u_institute_rank.values_json, JSON_OBJECT())), JSON_OBJECT('id', u_institute_rank.id)), '$.institutes', IFNULL(JSON_MERGE_PATCH(JSON_OBJECT('id', u_institutes.id), IFNULL(u_institutes.values_json, JSON_OBJECT())), JSON_OBJECT('id', u_institutes.id)), '$.created_by', JSON_OBJECT('id', u_created_by.id, 'name', u_created_by.name)) AS courses FROM devdts.courses s LEFT JOIN devdts.institutes u_institute_rank ON u_institute_rank.id = JSON_UNQUOTE(JSON_EXTRACT(s.values_json, '$.institute_rank')) LEFT JOIN devdts.institutes u_institutes ON u_institutes.id = JSON_UNQUOTE(JSON_EXTRACT(s.values_json, '$.institutes')) LEFT JOIN devdts.users u_created_by ON u_created_by.id = s.created_by WHERE (s.tenant_id IS NULL OR s.tenant_id = 'abcd') AND s.deleted_at IS NULL
Это запросы с минимальными фильтрами, их может быть несколько как для ключей, так и для столбцов JSON.
Что мы пробовали:
Я уже пробовал инструменты SQL/синхронизации с идентификаторами трассировки, удаляя ненужные LEFT JOIN из подсчета курсов, добавляя составной индекс (deleted_at, tenant_id) и ужесточая некоторые фильтры на основе идентификаторов, которые частично помогли, но не помогли. Задержка подсчета LIKE-on-JSON;
Я попробовал удалить левые соединения, и запрос стал таким:
SELECT count(*) FROM devdts.courses s WHERE (s.tenant_id IS NULL OR s.tenant_id = 'abcd') AND s.deleted_at IS NULL AND ((((LOWER(CAST(JSON_UNQUOTE(COALESCE(JSON_EXTRACT(s.values_json, '$.institutes.id'), JSON_EXTRACT(s.values_json, '$.institutes'), JSON_EXTRACT(s.values_json, '$.institutes'))) AS CHAR)) LIKE '%96c54c95-c730-4bec-b63d-08d98f643553%'))))
Все еще требуется время
Требуется помощь в отношении:
Я знаю, что мы пытаемся использовать функции NoSql в пространстве MySql, но у нас много таблиц, около 30, и все они имеют одну и ту же структуру столбцов таблицы, чтобы сделать API универсальными, поэтому необходимо решение относительно этой архитектуры.
Редактирование:
Хотелось бы добавить более подробную информацию о таблица, ее индексы и то, как выглядит строка данных о единицах.
Это все столбцы таблицы курса:
Это текущие значения:
Вот как выглядят данные:
# id values_json created_at created_by updated_at deleted_at university_id description name university course_fee_virtual institutes tenant_id alias_virtual country_virtual course_name_virtual institutes_virtual
00003b98-1f43-4603-beae-773780fc1405 {"name":"Doctor of Philosophy in Classics","about":"At Parturient Porta Vestibulum Integer Sociosqu Vitae Aptent Aptent Praesent Rhoncus Urna","alias":"doctor-of-philosophy-in-classics","level":"postgraduate","country":"united_states","currency":"USD","currency_symbol":"$","department_name":"historical_philosophical_and_religious_studies","international_student":"false","domestic_student":"false","extraction_date":"2025-08-07T06:30:00.000Z","institutes":"af7cf533-8ad7-4f68-9f26-c0dd7fd5052e","course_link":"https://www.classics.pitt.edu/graduate/ ... philosophy"} 2025-09-29 14:23:04 2025-12-28 09:35:58 af7cf533-8ad7-4f68-9f26-c0dd7fd5052e af7cf533-8ad7-4f68-9f26-c0dd7fd5052e doctor-of-philosophy-in-classics united_states doctor of philosophy in classics af7cf533-8ad7-4f68-9f26-c0dd7fd5052e
Подробнее здесь: https://stackoverflow.com/questions/798 ... namic-json
