Блог

У меня есть несколько сложный запрос с несколькими (вложенными) подзапросами, который я хочу сделать доступным для разработчиков приложений. Запрос является общим и генерирует представление с вычисленными значениями по набору наборов данных, и ожидается, что разработчику понадобятся только некоторые записи из того, что возвращает запрос (т. Е. Они ограничат результат для идентификатора некоторого объекта или диапазона дат или чего-то подобного).

Я вижу 3 способа реализовать это:

1. Позвольте разработчикам встраивать запрос в каждое приложение и добавлять свои собственные WHERE предложения по мере необходимости.
2. Создайте хранимую процедуру, которая принимает в качестве параметров все условия, которые, как я ожидаю, понадобятся разработчикам (ради аргумента допустим, что я могу предсказать, что потребуется в обозримом будущем), и процедура выполнит сложный запрос и отфильтрует его в соответствии с переданными параметрами.
3. Реализуйте запрос как представление с несколькими вложенными представлениями (поскольку MySQL не разрешает вложенные запросы в представлениях), и пусть разработчики используют это как таблицу и используют WHERE , чтобы каждое приложение применяло нужные им фильтры. В настоящее время я рассматриваю 3 дополнительных вложенных представления, в основном потому, что некоторые вложенные запросы используются несколько раз, и выполнение их как вложенных представлений предотвращает дублирование — иначе могло быть хуже ;-).

Что будет лучше с точки зрения производительности? (предполагая, что все индексации эквивалентны во всех случаях), если можно, используйте наихудшие сценарии.

как вы думаете, что будет лучше с точки зрения обслуживания кода?

Мне нравятся вопросы, которые определяют «хорошо» — вы специально спросили о производительности и ремонтопригодности, что позволяет в ответах говорить об этом компромиссе.

С точки зрения производительности, я не думаю, что между этими 3 вариантами может быть какая-либо разница, если запросы и данные соответствуют вашим ожидаемым сценариям. Я бы протестировал в 100 раз больше данных и, возможно, расширил предложение «where», Чтобы посмотреть, что произойдет, Но структура индексации и т. Д. С большей вероятностью повлияют на производительность, Чем выполнение того же SQL из сохраненной процедуры, через представление или из клиентского приложения.

Лучший способ ответить на этот вопрос — протестировать его — конечно, есть много конкретных деталей, которые могут сделать недействительными общие ответы типа «я бы ожидал x, y или z», которые мы можем дать overflowers. Если производительность является критической проблемой, используйте инструмент для заполнения базы данных (Redgate make on, я использовал DBMonster в прошлом) и попробуйте все 3 варианта.

С точки зрения обслуживания, я бы предложил вариант 4, который, на мой взгляд, на сегодняшний день является лучшим.

Вариант 4. создайте библиотеку доступа к данным, которая инкапсулирует доступ к вашим данным. Предоставьте библиотеке методы и параметры для уточнения выбора записей. Рассмотрите возможность использования шаблона спецификации (http://en.wikipedia.org/wiki/Specification_pattern ). Используйте любые запросы, которые лучше всего подходят для библиотеки, и не беспокоите разработчиков деталями реализации.

Если это не сработает — гетерогенный код приложения, слишком много изменений для простого требования — я бы оценил варианты следующим образом:

1. Встроенный SQL: в зависимости от того, сколько раз этот SQL используется повторно, это может быть нормально. Если есть только одна часть кода, которая запускает SQL, она логически похожа на библиотеку доступа к данным. Однако, если один и тот же фрагмент необходимо повторно использовать во многих местах, это вероятный источник ошибок — небольшое изменение в SQL необходимо будет повторить в нескольких местах.

2. Хранимая процедура: мне обычно не нравятся хранимые процедуры по причинам обслуживания — они, как правило, становятся хрупкими из-за перегрузки и создают процедурный способ мышления. Например, если у вас есть другие требования для использования этого вычисления SQL в отдельной хранимой процедуре, очень быстро вы получите процедурную модель программирования, в которой хранимые процедуры вызывают друг друга.

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

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

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

Для решения manteinance я бы предпочел 1-е и 2-е решения, потому что вы можете вносить любые изменения в запрос, не внося никаких изменений в базу данных. Если вы выберете 1-е решение, я бы перенес вызов запроса в функцию, чтобы у вас было только одно место для внесения изменений.

С точки зрения разработчика, я бы выбрал решение view, потому что оно является наиболее прозрачным, я имею в виду, что это похоже на запрос обычной таблицы, вы можете проверить структуру таблицы с помощью команды describe или просто выбрать поля и условия, которые вам нужно запросить, или присоединиться к другой таблице и т. Д…

Что касается гибкости предложения where, вы можете достичь ее с помощью любого из предложенных решений. Вы можете добавить параметр where в свою функцию переноса (1), вы можете добавить параметр where в хранимую процедуру, но будьте осторожны с инъекциями (2), или разработчик может добавить предложение where, как обычно, с представлением (3)

Имея в виду, что представления MySQL не являются временными таблицами, если запрос очень сложный, это решение было бы не лучшим, если запрос используется часто и по-разному (отключение повышения производительности кэша). Я бы рассмотрел временное табличное решение (таблицу счетчиков), которое обновляет каждый период времени запрограммированной задачей / cron (например, день, неделю, когда это необходимо) или обновляется путем установки триггеров propper. Это решение может значительно повысить производительность.

Надеюсь, это поможет, мне больше всего нравится решение view, но, возможно, его сложнее разработать с точки зрения базы данных.