Блог

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

Реализация довольно проста. В таблице просто хранится текстовое имя системы и поля (VARCHAR64) вместе со значением (SMALLINT). По сути, это таблица поиска жестко закодированных значений, которыми мы делимся с командой разработчиков JavaScript, поэтому данные могут быть:

 CUSTOMER_RECORD_STATE, ACTIVE, 1 CUSTOMER_RECORD_STATE, INACTIVE, 2 CUSTOMER_RECORD_STATE, DELETED, 3  

Функция используется для поиска этих значений, как показано ниже.

 SELECT tab.name_first FROM our_customers ourc WHERE ourc.record_state = get_gcs('CUSTOMER_RECORD_STATE','ACTIVE');  

Вот код создания функции:

 SET NAMES 'utf8mb4'; DELIMITER $ CREATE DEFINER = 'root'@'localhost' FUNCTION get_gcs (in_system varchar(128), in_field varchar(128), in_value varchar(128)) RETURNS smallint(6) DETERMINISTIC READS SQL DATA BEGIN  DECLARE var_value smallint DEFAULT -1;   DECLARE out_result_value smallint;   DECLARE debug_definition json DEFAULT JSON_OBJECT('function', 'get_gcs');  DECLARE debug_details json DEFAULT JSON_OBJECT('source', debug_definition, 'parameters',  JSON_OBJECT('in_system', in_system, 'in_field', in_field, 'in_value', in_value));   SELECT  custom_value INTO var_value  FROM global_custom_setting  WHERE in_system = name_system  AND in_field = name_field  AND in_value = name_value;   RETURN var_value; END $  DELIMITER ;  

character_set_client: utf8mb4

сопоставление_соединение: utf8mb4_0900_ai_ci

Параметры сортировки базы данных: utf8mb4_unicode_ci

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

Проблема в том, что, хотя функция ДЕТЕРМИНИРОВАНА, она, по-видимому, значительно замедляет сложные вызовы SQL, в которые встроена функция. Я был удивлен этим, так как у меня сложилось впечатление, что ДЕТЕРМИНИРОВАННЫЕ функции будут обрабатываться по-другому; как если бы они были статическим значением (в вызове SQL).

Итак, вопросы:

1. Является ли это лучшим подходом к совместному использованию статических значений между двумя платформами (базой данных и серверной частью)?
2. Почему движок MySQL не рассматривает вызовы как ДЕТЕРМИНИРОВАННЫЕ и разрешает значение только один раз, а не с помощью того, что кажется с каждым итеративным вызовом?

Спасибо!

Одна вещь, которую следует (и можно) исправить — UCASE(in_system) = UCASE(name_system)

Если оба столбца имеют одинаковую кодировку и параметры сортировки, UCASE в этом нет необходимости. Кроме того, параметры сортировки ..._ci должны быть «Без учета регистра».

Любой вызов функции скрывает столбцы от использования в любом индексе. Следовательно WHERE , предложение в функции не может использовать индекс.

global_custom_setting потребности INDEX(name_system, name_field, name_value) . (Столбцы могут располагаться в любом порядке.)

По-видимому, FUNCTION используется collation_connection: utf8mb4_0900_ai_ci , что может отличаться от параметров сортировки соединения.

Предлагаю попробовать это после удаления UPPERs и добавления INDEX :

 SET NAMES utf8mb4, COLLATE utf8mb4_0900_ai_ci; DROP FUNCTION get_gcs; DELIMITER $ ((recreate the function)) DELIMITER ; ((similarly for get_gss))  

По-видимому, это относится к строкам в функции:

• Аргументы функции: collation_database
• Литеральные строки в функции: collation_connection
• Столбцы таблицы: сопоставление столбцов

Функции никогда не могут быть саргируемыми (насколько я знаю). Некоторые выражения включают «неявные» функции, такие как преобразование типов данных и несоответствие параметров сортировки.