1. Выберите наиболее подходящие атрибуты полей.
MySQL вполне может поддерживать доступ к большим объемам данных, но, вообще говоря, чем меньше таблица в базе данных, тем быстрее будут выполняться запросы. Поэтому при создании таблицы, чтобы получить лучшую производительность, мы можем установить как можно меньшую ширину полей в таблице. Например, если при определении поля почтового индекса вы установите для него значение CHAR(255), это, очевидно, добавит ненужное пространство в базу данных. Даже использование типа VARCHAR является избыточным, поскольку CHAR(6) вполне подходит. Аналогично, если возможно, мы должны использовать MEDIUMINT вместо BIGIN для определения целочисленных полей.
Еще один способ повышения эффективности — по возможности устанавливать полям значение NOT NULL, чтобы базе данных не приходилось сравнивать значения NULL при выполнении запросов в будущем.
Для некоторых текстовых полей, таких как «провинция» или «пол», мы можем определить их как типы ENUM. Потому что в MySQL тип ENUM обрабатывается как числовые данные, а числовые данные обрабатываются гораздо быстрее, чем текстовые типы. Таким образом, мы можем улучшить производительность базы данных.
2. Используйте соединения (JOIN) вместо подзапросов (Sub-Queries).
MySQL поддерживает подзапросы SQL, начиная с версии 4.1. Этот метод позволяет использовать инструкцию SELECT для создания одного столбца результатов запроса, а затем использовать этот результат в качестве условия фильтра в другом запросе. Например, если мы хотим удалить клиентов, у которых нет заказов в таблице основной информации о клиентах, мы можем использовать подзапрос, чтобы сначала получить идентификаторы всех клиентов, которые оформили заказы, из таблицы информации о продажах, а затем передать результаты в основной запрос, как показано ниже:
DELETE FROM customerinfo
ГДЕ CustomerID НЕ в (SELECT CustomerID FROM salesinfo).
Использование подзапросов позволяет выполнять многие операции SQL, которые логически требуют одновременного выполнения нескольких шагов. Это также позволяет избежать блокировок транзакций или таблиц, а также легко писать. Однако в некоторых случаях подзапросы можно заменить более эффективными соединениями (JOIN). Например, предположим, что мы хотим получить всех пользователей, у которых нет записей о заказах, мы можем использовать следующий запрос:
SELECT * FROM customerinfo.
ГДЕ CustomerID НЕ указан (ВЫБЕРИТЕ CustomerID ИЗ информации о продажах).
Если вы используете соединение (JOIN).. для выполнения этого запроса, скорость будет намного выше. Особенно если в таблице salesinfo есть индекс CustomerID, производительность будет выше. Запрос выглядит следующим образом:
SELECT * FROM customerinfo.
СЛЕВА ПРИСОЕДИНЯЙТЕСЬ к salesinfoON customerinfo.CustomerID=salesinfo.
идентификатор клиента
ГДЕ salesinfo.CustomerID IS NULL
соединение (JOIN). Причина, по которой оно более эффективно, заключается в том, что MySQL не нужно создавать временную таблицу в памяти для выполнения этого логического двухэтапного запроса.
3. Используйте объединение (UNION) для замены созданных вручную временных таблиц.
MySQL поддерживает запросы UNION, начиная с версии 4.0, которые могут объединять два или более запросов SELECT, требующих использования временных таблиц, в один запрос. Когда сеанс запроса клиента завершится, временная таблица будет автоматически удалена, чтобы обеспечить порядок и эффективность базы данных. При использовании UNION для создания запроса нам нужно использовать UNION только в качестве ключевого слова для соединения нескольких операторов SELECT. Следует отметить, что количество полей во всех операторах SELECT должно быть одинаковым. В следующем примере демонстрируется запрос с использованием UNION.
ВЫБЕРИТЕ Имя, Телефон ОТ клиента
СОЮЗ
ВЫБЕРИТЕ имя, дату рождения ОТ автора
СОЮЗ
ВЫБРАТЬ имя, поставщика ИЗ продукта
4. Транзакции
Хотя мы можем использовать подзапросы, соединения (JOIN) и объединения (UNION) для создания множества запросов, не все операции с базой данных могут быть выполнены с помощью только одного. несколько операторов SQL. Чаще всего для выполнения определенного рода работы необходима серия заявлений. Но в этом случае, когда определенный оператор в этом блоке операторов выполняется неправильно, работа всего блока операторов станет неопределенной. Представьте, что вы хотите вставить определенные данные в две связанные таблицы одновременно. Такое может случиться: после успешного обновления первой таблицы в базе данных возникает непредвиденная ситуация, из-за которой операция во второй таблице не может быть завершена. В этом случае данные будут неполными и даже данные в базе данных будут уничтожены. Чтобы избежать этой ситуации, вам следует использовать транзакции. Его функция: либо каждый оператор в блоке операторов завершается успешно, либо завершается неудачно. Другими словами, можно поддерживать согласованность и целостность данных в базе данных. Все начинается с ключевого слова BEGIN и заканчивается ключевым словом COMMIT. Если в течение этого периода операция SQL завершается неудачно, команда ROLLBACK может восстановить базу данных до состояния, существовавшего до запуска BEGIN.
НАЧИНАТЬ;
INSERT INTO salesinfo SET CustomerID=14;
ОБНОВИТЬ инвентарь НАБОР Количество = 11
ГДЕ item='книга';
COMMIT
Другая важная роль транзакций заключается в том, что когда несколько пользователей одновременно используют один и тот же источник данных, он может использовать метод блокировки базы данных, чтобы предоставить пользователям метод безопасного доступа, который может гарантировать, что операции пользователя не будут заблокированы; мешали другие пользователи.
5. Блокировка таблиц
Хотя транзакции являются очень хорошим способом поддержания целостности базы данных, из-за своей эксклюзивности они иногда влияют на производительность базы данных, особенно в больших прикладных системах. Поскольку база данных будет заблокирована во время выполнения транзакции, другие запросы пользователя могут только ждать завершения транзакции. Если систему базы данных используют лишь несколько пользователей, влияние транзакций не станет большой проблемой, но если тысячи пользователей одновременно обращаются к системе базы данных, например, при доступе к веб-сайту электронной коммерции, это приведет к серьезной проблеме; задержка ответа.
Фактически, в некоторых случаях мы можем добиться большей производительности, заблокировав таблицу. В следующем примере используется метод таблицы блокировки для выполнения функции транзакции из предыдущего примера.
ЗАБЛОКИРОВАТЬ ТАБЛИЦУ инвентаря ЗАПИСАТЬ
ВЫБЕРИТЕ количество ИЗ инвентаря
WHEREItem='книга';
...
ОБНОВИТЬ инвентарь НАБОР Количество = 11
WHEREItem='книга';
РАЗБЛОКИРОВАТЬ ТАБЛИЦЫ
Здесь мы используем оператор SELECT для получения исходных данных и посредством некоторых вычислений используем оператор UPDATE для обновления новых значений в таблице. Оператор LOCK TABLE, содержащий ключевое слово WRITE, гарантирует, что до выполнения команды UNLOCK TABLES не будет другого доступа к инвентарю для вставки, обновления или удаления.
6.
Метод использования внешних ключей для блокировки таблицы позволяет сохранить целостность данных, но не может гарантировать актуальность данных. На данный момент мы можем использовать внешние ключи. Например, внешний ключ может гарантировать, что каждая запись о продажах указывает на существующего клиента. Здесь внешний ключ может сопоставить CustomerID в таблице customerinfo с CustomerID в таблице salesinfo. Любая запись без действительного CustomerID не будет обновляться или вставляться в salesinfo.
СОЗДАТЬ ТАБЛИЦУинформация о клиенте
(
CustomerID INT NOT NULL ,
ПЕРВИЧНЫЙ КЛЮЧ (ID клиента)
) TYPE = INNODB
СОЗДАТЬ ТАБЛИЦУ salesinfo;
(
SalesID INT NOT NULL,
CustomerID INT NOT NULL,
ПЕРВИЧНЫЙ КЛЮЧ(CustomerID, SalesID),
ВНЕШНИЙ КЛЮЧ (CustomerID) ССЫЛКИ Информация о клиенте
(CustomerID) НА DELETECASCADE
) TYPE = INNODB;
Обратите внимание на параметр «ON DELETE CASCADE» в примере. Этот параметр гарантирует, что при удалении записи клиента в таблице customerinfo все записи, связанные с клиентом в таблице salesinfo, также будут автоматически удалены. Если вы хотите использовать внешние ключи в MySQL, вы должны не забыть определить тип таблицы как безопасный для транзакций тип InnoDB при создании таблицы. Этот тип не является типом по умолчанию для таблиц MySQL. Определенный метод заключается в добавлении TYPE=INNODB к оператору CREATE TABLE. Как показано в примере.
7. Использование индексов
— распространенный метод повышения производительности базы данных. Он позволяет серверу базы данных получать определенные строки гораздо быстрее, чем без индекса, особенно если оператор запроса содержит MAX(), MIN() и ORDERBY. улучшение производительности более очевидно. Итак, какие поля следует индексировать? Вообще говоря, индексы должны создаваться на основе полей, которые будут использоваться для сортировки JOIN, WHERE и ORDER BY. Старайтесь не индексировать поле в базе данных, содержащее большое количество повторяющихся значений. Для поля типа ENUM вполне возможно наличие большого количества повторяющихся значений, например, поля «провинция».. в customerinfo. Создание индекса для такого поля не будет полезным, наоборот, это возможно; Уменьшите производительность базы данных. Мы можем создать соответствующие индексы одновременно с созданием таблицы или использовать ALTER TABLE или CREATE INDEX для создания индексов позже. Кроме того, MySQL поддерживает полнотекстовую индексацию и поиск, начиная с версии 3.23.23. Полнотекстовый индекс представляет собой индекс типа FULLTEXT в MySQL, но его можно использовать только для таблиц типов MyISAM. Для большой базы данных будет очень быстро загрузить данные в таблицу без индекса FULLTEXT, а затем использовать ALTER TABLE или CREATE INDEX для создания индекса. Но если вы загрузите данные в таблицу, у которой уже есть индекс FULLTEXT, процесс выполнения будет очень медленным.
8. Оптимизированные операторы запроса
. В большинстве случаев использование индексов может повысить скорость выполнения запросов, но если операторы SQL используются неправильно, индекс не сможет играть свою должную роль. Ниже приведены несколько аспектов, на которые следует обратить внимание. Во-первых, лучше всего выполнять операции сравнения между полями одного типа. До версии MySQL 3.23 это было даже обязательным условием. Например, индексированное поле INT нельзя сравнивать с полем BIGINT, но в особом случае, когда поле типа CHAR и поле типа VARCHAR имеют одинаковый размер, их можно сравнивать; Во-вторых, старайтесь не использовать функции для работы с индексированными полями.
Например, при использовании функции YEAE() для поля типа ДАТА индекс не будет работать должным образом. Таким образом, хотя следующие два запроса возвращают одинаковые результаты, последний выполняется намного быстрее, чем первый.
SELECT * FROM order WHERE YEAR(OrderDate)＜2001;
SELECT * FROM order WHERE OrderDate＜"2001-01-01"
Такая же ситуация произойдет и при расчете числовых полей:
SELECT * FROM Inventory WHERE Amount/ 7<24
; * ИЗ инвентаря WHERE Amount<24*7;
Два приведенных выше запроса также возвращают одинаковые результаты, но последний запрос будет намного быстрее предыдущего. В-третьих, при поиске символьных полей мы иногда используем ключевые слова LIKE и подстановочные знаки. Хотя этот подход прост, он также снижает производительность системы. Например, следующий запрос сравнит каждую запись в таблице.
ВЫБРАТЬ * ИЗ книг
ГДЕ имя типа «MySQL%».
Но если вы используете следующий запрос, результаты будут такими же, но скорость будет намного выше:
SELECT * FROM book
WHERE name>="MySQL" и name<"MySQM"
Наконец, вы должны быть осторожны и не позволять MySQL выполнять автоматическое преобразование типов в запросе, поскольку процесс преобразования также сделает индекс неэффективным.