ресурс для начинающих веб-разработчиков
комплексные веб-услуги по созданию сайтов

В дополнение к ранее предложенным рекомендациям по оптимизации запросов, можно предпринять и дополнительные действия для повышения общей производительности базы данных MySQL.

Оптимизация проекта

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

По возможности следует избегать использования столбцов переменной длины (таких как VARCHAR, TEXT, BLOB). Таблицы с полями фиксированной длины будут обрабатываться быстрее, но они могут занимать несколько большее дисковое пространство.

Разрешения

Помимо использования рекомендации, которые приведены в предыдущем разделе, посвященном оператору EXPLAIN, скорость выполнения запросов можно увеличить за счет упрощения разрешений. Ранее мы уже рассматривали способ проверки запросов системой разрешений перед их выполнением. Чем проще этот процесс, тем быстрее будет выполняться запрос.

Оптимизация таблиц

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

OPTIMIZE TABLE имя_таблицы;

или команды, которая должна вводиться в командной строке:

mysamchk -r имя_таблицы

Можно также с помощью улиты mysamchk отсортировать индекс таблицы и данные в соответствии с этим индексом:

mysamchk --sort-index --sort- records=1 путь_к_каталогу_данных_MySQL / * / * .MYI

Использование индексов

Когда это требуется, для ускорения выполнения запросов следует пользоваться индексами. Индексы должны быть максимально простыми. Не создавайте индексы, которые не будут задействаны в запросах. Для проверки того, какие индексы используются в запросе, служит оператор EXPLAIN, как описано в предыдущих разделах.

Использование значений, заданных по умолчанию

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

Резервное копирование базы данных MySQL

MySQL предоставляет несколько способов резервного копирования. Первый из них предполагает блокировку таблиц на время копирования физических файлов с помощью команды LOCK TABLES, которая имеет следующий синтаксис:

LOCK TABLES таблица тип_блокировки [ , таблица тип_блокировки . . . ]

Каждый параметр таблица должен быть именем таблицы, а значением параметра тип_блокировки должен быть либо READ либо WRITE. Для резервного копирования необходима блокировка чтения (READ). Перед выполнением резервного копирования потребуется выполнить команду FLUSH TABLES, для гарантированной записи на диск любых изменений в индексах.

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

Второй и более совершенный способ состоит в использовании команды mysql_dump. Она запускается из командной строки и, как правило, выглядит так:

mysqldump --opt --all-databases > all.sql

Эта команда записывает все SQL-команды, необходимые для восстановления базы данных, в файл all.sql.

Затем потребуется временно остановить процесс mysqld и перезапустить его с параметром --log-bin [ = файл_журнала ]. Обновления, хранящиеся в файле журнала, позволяют получить данные изменений, выполненных с момента резервного копирования. Понятно, что в ходе каждого обычного резервного копирования необходимо выполнять резервное копирование и файлов журнала.

Третий метод предполагает использование сценария mysqlhotcopy. Он вызывается следующим образом:

mysqlhotcopy база_данных / путь / к / резервной_копии

Далее необходимо выполнить действия по запуску и останову базы данных, которые были описаны выше.

Последний (и наиболее надежный) метод выполнения резервного копирования состоит в поддержании реплицированной копии базы данных. Создание реплицированных копий мы рассмотрим далее.

Восстановление базы данных MySQL

Для восстановления базы данных MySQL также существует несколько подходов. Если проблема связана с поврежденной таблицей, можно запустить команду myisamchk с параметром -r (repair —восстановление).

Если для создания резервной копии использовался первый из описанных методов, файлы данных можно скопировать в те же каталоги новой установки MySQL.

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

mysqlbinlog имя_хоста-bin. [0-9] * | mysql