Вы только что написали новую функцию для вашего замечательного приложения, которое просматривает вашу базу данных, чтобы найти ‘x’, и запускаете несколько тестов, чтобы проверить, что возвращаемый результат верен.
К сожалению, ваш тест не пройден, поскольку он возвращает больше результатов, чем ожидалось. Сначала это не очевидно, потому что прошло много времени с тех пор, как вы работали с приложением, но в конечном итоге вы понимаете, что каждый поиск должен автоматически отфильтровываться по логическому полю.
Возможно, вы столкнулись с подобной ситуацией, когда вы или кто-то другой забыл фильтровать по значению в поле x, и теперь он потенциально может показывать неверные данные, что наносит ущерб бизнесу. К счастью, есть некоторые тесты, чтобы поймать это.
Давайте работать через пример. У вас есть пользовательская таблица, и по уважительной причине вы используете логическое поле для представления состояния пользователя, возможно, чтобы определить, отключено ли оно. Вы можете скопировать данные из «живой таблицы» в «отключенную таблицу», но вы чувствуете, что сложность и накладные расходы для реализации этого слишком велики из-за других таблиц, связанных с пользователем.
Учитывая вышеприведенный сценарий, я думаю, что MySQL Views может улучшить это решение. Взгляды довольно простые вещи. Они представляют таблицу на основе определенного запроса SELECT. Мы можем создать представление, которое будет возвращать только тех пользователей, которые включены в систему и которые не позволят будущим функциям включать отключенных пользователей.
Я объясню, как создать это представление и как использовать его в вашем приложении. Стоит отметить, что представления MySQL доступны только с версии 5.
Вот основная пользовательская таблица, на которой будет основано представление:
CREATE TABLE `users` ( `user_id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `first_name` VARCHAR (100) NULL, `last_name` VARCHAR (100) NULL, `username` VARCHAR (100) NULL, `dob` DATETIME NULL, `disabled` BIT NULL DEFAULT 0, PRIMARY KEY (`user_id`));
Ниже приведен запрос, который создаст представление MySQL для указанной выше пользовательской таблицы.
СОЗДАТЬ ИЛИ ЗАМЕНИТЬ АЛГОРИТМ = ПРОСМОТР СЛИЯНИЯ `v_users_enabled` (`firstName`,` lastName`, `username`,` dob`) В КАЧЕСТВЕ ВЫБЕРИТЕ `first_name`,` last_name`, `username`,` dob` ОТ `пользователей` ГДЕ `отключено` = 1;
Как это работает?
Вот разбивка ключевых слов, использованных для создания представления.
СОЗДАТЬ ИЛИ ЗАМЕНИТЬ
Это создает новый вид и является обязательным. При желании вы можете добавить ИЛИ ЗАМЕНИТЬ, если хотите убедиться, что оно создано, но если вы знаете, что представление уже существует, вы можете использовать ALTER для внесения изменений в существующее представление.
АЛГОРИТМ = МЕРЖЕ
Это необязательный оператор, и, не определяя одно или явно указав ALGORITHM = UNDEFINED, MySQL будет выбирать между двумя вариантами, которые лучше всего подходят для оператора SELECT, т.е. MERGE или TEMPTABLE. MySQL будет пытаться выбрать MERGE вместо TEMPTABLE, где это возможно, потому что это более эффективно. Я расскажу больше об алгоритме после прохождения этих основных моментов.
ПОСМОТРЕТЬ
Является обязательным утверждением и используется для присвоения имени представлению. Важно отметить, что представление не может иметь то же имя, что и таблица, потому что они используют одно и то же пространство имен.
Часть списка столбцов является необязательной, и по умолчанию используются имена столбцов в операторе SELECT. Если вы решите определить имена столбцов, они должны быть разделены запятыми, должны быть уникальными и соответствовать количеству столбцов в SELECT.
В КАЧЕСТВЕ
Требуется и где вы определяете запрос SELECT.
Запрос к представлению такой же, как при запросе к таблице. Вот простой пример:
SELECT * FROM v_users_enabled
Подробнее об алгоритме
При определении алгоритма у вас есть три варианта на выбор: MERGE, TEMPTABLE или UNDEFINED.
Теперь мы знаем, что UNDEFINED позволяет MySQL выбирать соответствующую опцию, но что означают две другие опции?
MERGE — самый быстрый из двух вариантов. Список столбцов представления заменяет то, что содержится в операторе SELECT, по существу объединяясь быстрее, чем TEMPTABLE, поскольку он генерирует новую временную таблицу, к которой запрашиваются и которая не имеет индексов . MySQL предупредит вас, если вы попытаетесь использовать MERGE, когда следует использовать TEMPTABLE, и изменит его на TEMPTABLE.
Когда будет использоваться опция TEMPTABLE ? Если вы используете какую-либо функцию агрегирования, DISTINCT, LIMIT, GROUP BY, HAVING, подзапрос или литеральные значения (т. Е. Нет таблицы).
Незначительный компромисс производительности
К сожалению, представления MySQL будут скорее мешать производительности, чем улучшать ее. Важно думать о своих взглядах и использовать MERGE, где это возможно, чтобы минимизировать снижение производительности.
При условии, что ваши бюджеты производительности могут принять удар, чтобы лучше управлять сложностью и создать полезное разделение, это, на мой взгляд, является достойным компромиссом, потому что вы берете запрос с определенными пунктами where, которые, возможно, придется применить к другим запросам. Это хорошо содержится в представлении, и пользователи уровня базы данных могут запрашивать это, возвращая им правильные результаты, без необходимости помнить, что им нужны эти дополнительные предложения where.
Недостатком этой локализации является то, что было бы возможно написать сложный запрос в представлении, которое теперь скрыто. Запросы с дополнительным предложением where в этом представлении сделают общий запрос огромным и потенциально неэффективным. Нужно сделать суждение о том, насколько сложным может быть запрос SELECT для представления без ущерба для производительности.
Чтобы лучше оптимизировать производительность при использовании представления, используйте алгоритм MERGE, а при создании индекса добавьте поля в предложениях WHERE, которые сначала существуют в представлении. Важно, чтобы они были расположены в правильном порядке, как обычно при создании индексов для ожидаемых запросов. Вы можете использовать EXPLAIN, чтобы проверить ваш запрос и убедиться, что он работает так, как вы ожидаете.
Преимущества и недостатки
Преимущества использования представления:
- Обеспечьте полезное разделение данных от приложения, указав необходимость запоминания определенных полей, по которым должен фильтроваться запрос для всех операторов SELECT в базе данных.
- Таблицы могут со временем меняться, и вам нужно добавить несколько новых полей, по которым вы собираетесь фильтровать большинство запросов SELECT. Все, что нужно изменить, — это оператор SELECT с новыми полями после изменения таблиц. Тогда обновление приложения будет проще, так как вам, возможно, придется помнить только о том, чтобы изменить его в нескольких местах.
- Они могут сделать ваши запросы SELECT более читабельными.
Негативы использования вида:
- Когда используется алгоритм TEMPTABLE, индекс не используется.
- Они могут скрыть сложный запрос, и с помощью запроса представление может превратить его в вялый запрос.
- Использование алгоритма MERGE ограничивает ваш оператор SELECT представления только базовыми запросами.
- Вы можете выбрать алгоритм MERGE, но если MySQL считает, что он должен использовать TEMPTABLE, он изменит его.
- Если ваше представление не имеет отношения один к одному с таблицей, оно не может быть обновлено.
- Когда вы добавите или измените таблицу, вам все равно нужно будет обновить операторы CUD в вашем приложении.
- Вы не можете связать триггер с представлением.
Примечательные причуды
Если вы хотите использовать ORDER BY в вашем операторе SELECT представления, стоит отметить, что это не может быть переопределено, когда вы запрашиваете представление, используя другой порядок.
Если вы решили поместить LIMIT в ваше представление, а затем использовать другой LIMIT при запросе представления, то не определено, какое LIMIT применяется.
Если вы заинтересованы в обновлении представления, посетите веб-сайт MySQL .
Вывод
Представления MySQL являются полезным инструментом и обеспечивают хорошее решение в определенных сценариях. Ясно, что взгляды имеют свои пределы, и важно знать о них. При правильном использовании ваше следующее приложение может извлечь выгоду из гибкой локализации и контроля доступа к данным.