В этой статье рассматриваются идеи по улучшению производительности базы данных MySQL и предлагаются конкретные решения по 8 аспектам.
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 имеет значение NULL.
Установите путь к данным и поместите файлы данных базы данных в общий каталог NFS (сервер NAS).
Файлы PID и innioDB должны быть размещены в локальном каталоге сервера для нормального запуска и остановки службы:
1125. vi /etc/ my.cnf
[mysqld]
#Где установить базу данных
datadir=/data/mysqldata
#Где установить движок innoDB
innodb_data_home_dir = /usr/local/mysql/data
innodb_log_group_home_dir= /usr/local/mysql/data
innodb_data_file_path=ibdata1: 50M;ibdata2:50M:autoextend
1106 cp ./support-files/mysql.server /etc/rc.d/init.d/vi
/etc/rc.d/init.d/mysql.server
Связанные с компиляцией элементы, начиная с 222-го. В двух строках поместите файл PID в локальный каталог сервера:
pid_file=/usr/local/mysql/data/mysqlmanager-`/bin/hostname`.pid
server_pid_file=/usr/local/mysql /data/`/bin/hostname`.pid
устанавливает базовую базу данных MySQL:
1123 mount 10.4.66.251:/data /data
1124 mkdir /data/mysqldata
1127 ./scripts/mysql_install_db --user=mysql
1145 chown -R mysql .mysql /data/mysqldata/
если все в порядке, вы можете видеть, что mysql запускается нормально
1146 /etc/rc.d/init.d/mysql.server start
1146 /etc/rc.d/init.d/mysql.server; прекратите
настройку высокой доступности HA, не устанавливайте монтирование NFS и автоматический запуск службы mysql при запуске сервера.
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
) ТИП = ИННОДБ;
Обратите внимание на параметр «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() для поля типа ДАТА индекс не будет работать должным образом. Таким образом, хотя следующие два запроса возвращают одинаковые результаты, последний выполняется намного быстрее, чем первый.
ВЫБРАТЬ * ИЗ заказа ГДЕ ГОД(ДатаЗаказа)<2001;
SELECT * FROM order WHERE OrderDate<"2001-01-01"
Такая же ситуация произойдет и при расчете числовых полей:
SELECT * FROM Inventory WHERE Amount/7<24;
SELECT * FROM Inventory WHERE Amount<24*7;
Два приведенных выше запроса также возвращают одинаковые результаты, но последний запрос будет намного быстрее, чем предыдущий. В-третьих, при поиске символьных полей мы иногда используем ключевые слова LIKE и подстановочные знаки. Хотя этот подход прост, он также снижает производительность системы. Например, следующий запрос сравнит каждую запись в таблице.
ВЫБРАТЬ * ИЗ книг
ГДЕ имя типа «MySQL%».
Но если вы используете следующий запрос, результаты будут такими же, но скорость будет намного быстрее: ..
SELECT * FROM book
WHERE name>="MySQL" и name<"MySQM"
Наконец, вы должны быть осторожны и не позволять MySQL выполнять автоматическое преобразование типов в запросе, поскольку процесс преобразования также сделает индекс неэффективным.