Как я избегаю избыточных полей данных в наборе результатов при использовании СОЕДИНЕНИЙ?
Следующее соединение, как предполагается, получает информацию о пользователе наряду с их сообщениями для пользователей с определенным состоянием:
SELECT * FROM user, message WHERE message.user_id=user.id AND user.status=1
Проблема состоит в том, что все строки об определенном пользователе в наборе результатов содержат избыточные столбцы, которые повторяют те же данные о том пользователе (те поля, полученные от пользовательской таблицы), только поля из таблицы сообщений содержат нерезервированную информацию. Что-то вроде этого:
user.id username email message.id subject
1 jane jane@gmail.com 120 Notification
1 jane jane@gmail.com 122 Re:Hello
1 jane jane@gmail.com 125 Quotation
2 john john@yahoo.com 127 Hi jane
2 john john@yahoo.com 128 Fix thiss
2 john john@yahoo.com 129 Ok
3 jim jim@msn.com 140 Re:Re:Quotation
Поскольку Вы видите, что много данных избыточны, и мы не хотим сначала находить пользователей и затем идти об их сообщениях в цикле как структуры или чем-то как этот. Циклов, которые вызывают микрозапросы, нужно избежать любой ценой.
Я не обеспокоен выводом своей программы, которая хорошо обрабатывается в UI. Я думаю, возможно, что сетевой трафик, произведенный путем возврата результата этого запроса, мог быть существенно уменьшен, если так или иначе мне может удаться устранить повторение пользовательских данных во всех строках, имеющих отношение к тому пользователю.
4 ответа
В стандарте SQL вы должны использовать NATURAL JOIN; это объединяет общие имена столбцов и сохраняет только одну копию этих общих имен.
На практике вы тщательно перечисляете нужные столбцы, вместо того, чтобы прибегать к сокращенной нотации "*".
В прямом запросе sql нет, если вы сохраняете их как один запрос. Если вы программно распечатываете это, вы должны заказывать по идентификатору пользователя и перепечатывать эту информацию только при изменении идентификатора пользователя.
Есть несколько вещей, которые вы должны знать.
Первый заключается в том, что конструкция SQL JOIN по умолчанию является по сути перекрестным произведением множеств, ограниченным предложением WHERE. Это означает, что она мультипликативна - вы получаете дублирующиеся результаты, которые затем отсеиваете. Также нужно быть осторожным при наличии NULL-полей.
Второе - это наличие ключевого слова 'DISTINCT'. Когда вы добавляете его к столбцу в выборке, вы получите в результатах не более одного экземпляра определенного значения для этого столбца. Таким образом, в соответствии с вашим запросом, 'SELECT DISTINCT user.id FROM' устранит избыточность на стороне сервера.
Третье - правильный способ решения этой проблемы, скорее всего, не использует оператор *. Я предлагаю:
SELECT user.id,username,email,subject FROM message m,user WHERE m.user_id=user.id AND user.status=1
Здесь используется простой и понятный синтаксис неявного соединения, и это должен быть правильный SQL на любом сервере. Я могу поручиться, что он работает, по крайней мере, на MySQL. Он также называет таблицу 'message' сокращенно 'm'.
Как вы догадываетесь, это уменьшит трафик от SQL-сервера к вашей базе данных.
правка: если вы хотите удалить "избыточную" информацию об электронной почте, вы не можете - вы должны сделать два разных запроса. Результаты SQL являются таблицами и должны быть прямоугольными, с заполнением всех известных значений. Не существует записи 'ditto'.
правка 2: Вы должны сделать только два запроса. Например:
SELECT subject FROM message WHERE message.id IN (SELECT user.id FROM user WHERE status=1)
Это один запрос, который содержит вложенный запрос, так что на самом деле он делает два обращения к базе данных. Но в нем нет никаких программных циклов.
Предполагая, что вы можете использовать хранимую процедуру, вы можете написать ее для выполнения вышеуказанного запроса, а затем использовать курсор для хранения нулей для «избыточной информации», чтобы получить что-то вроде
user.id имя пользователя email message.id тема 1 джейн (скрыто) 120 Уведомление null null null 122 Re: Здравствуйте null null null 125 Цитата 2 Иоанна (скрыто) 127 Привет, Джейн null null null 128 Исправить null null null 129 Хорошо 3 джим (скрыто) 140 Re: Re: Цитата
, а затем вернуть этот набор результатов во временную таблицу. но хотя это может уменьшить сетевой трафик, это добавит накладных расходов на обработку
Другой способ - запустить 2 запроса, один для получения информации о пользователе, а другой для получения информации о сообщении только с привязанным идентификатором пользователя, а затем выполнить «присоединиться» с помощью кода на стороне сервера приложений. что-то вроде
SELECT DISTINCT user.* FROM user, message WHERE message.user_id=user.id AND user.status=1
и
SELECT user.id, message.* FROM user, message WHERE message.user_id=user.id AND user.status=1
, что приведет к 2 обращениям к базе данных вместо 1, что в конечном итоге может быть медленнее, даже если сетевой трафик уменьшится.
И еще один способ - объединить эти 2 в один набор результатов с чем-то вроде
SELECT user.* FROM user, message WHERE message.user_id=user.id AND user.status=1
UNION ALL
SELECT user.id, message.* FROM user, message WHERE message.user_id=user.id AND user.status=1
, чтобы получить что-то вроде
user.id username / message.id email / subject 1 джейн (скрыто) 2 Иоанна (скрыто) 3 джим (скрыто) 1120 Уведомление 1 122 Re: Здравствуйте 1 125 Цитата 2 127 Привет Джейн 2 128 Исправить 2 129 Хорошо 3 140 Re: Re: Цитата
, а затем использовать логику сервера приложений, чтобы отделить его. уменьшение сетевого трафика, но большая нагрузка на сервер приложений / незначительно большая нагрузка на сервер базы данных.
Но сэкономленный сетевой трафик редко стоит дополнительных сложностей.