Оптимизация

Сценарий compare results суммирует и сравнивает результаты тестов. В листинге 10.1 приведен лишь один набор результатов. В действительности я немного сократил выходные данные, удалив ряд малозначащих пояснений. В первом блоке чисел указано время выполнения каждого из восьми тестов в секундах. Во втором блоке отображается статистика отдельных операций по всем тестам. Числа со знаком "плюс" — это приблизительные оценки, полученные для тестов, время выполнения которых превысило максимум.

Результаты тестов, предоставляемые разработчиками MySQL, можно использовать для выбора аппаратной платформы и операционной системы. На Web узле MySQL (http://www.mysql.com) постоянно публикуются обновляемые результаты и графики сравнения показателей MySQL с показателями других СУБД, работающих на идентичном оборудовании. Приводятся также данные, касающиеся работы MySQL на разных платформах.

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

Представим себе, к примеру, объединение таблицы, содержащей 15000 слов, с таблицей, содержащей 100000 слов. В худшем случае программе MySQL придется просмотреть все записи обеих таблиц. Сначала выбирается первая запись первой таблицы, а затем начинается просмотр записей второй таблицы до тех пор, пока не будет найдено совпадение. Умножив 15000 на 100000, получим 1,5 миллиарда операций чтения. На практике это число оказывается немного меньшим, но и его достаточно, чтобы получить представление о скорости запроса. Далее будет рассказываться о том, как с помощью индексов уменьшить количество записей, читаемых в процессе объединения таблиц.

С помощью журнала медленных запросов, описанного в "лекции 8" , можно легко найти наименее эффективные запросы. В дистрибутив MySQL входит сценарий mysqldumpslow предназначенный для упорядочения записей этого журнала по указанному в них времени выполнения запроса.

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

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

Наиболее эффективный тип денормализации включает создание итоговых данных. Под этим может подразумеваться добавление к таблице столбца, хранящего результаты вычислений по другим столбцам. Например, если в таблице накапливаются данные о прохождении грузов, то в ней будут столбцы с указанием времени прибытия и отбытия груза. Чтобы не вычислять каждый раз время стоянки, можно посчитать его один раз и занести результат в отдельный столбец. Управлять подобной избыточностью несложно.

Иногда создают не просто итоговые столбцы, а целые таблицы. Например, можно сохранять результаты ключевых запросов в таблице, которая обновляется раз в день. Это избавит сервер от необходимости все время выполнять одни и те же трудоемкие запросы, хотя и повысит риск получения пользователями неактуальных данных. Если таблицы содержат часто изменяемую информацию, лучше делать их резидентными. Такие таблицы хранятся в памяти и уничтожаются при перезагрузке сервера. Приложение должно быть готово к возможному отсутствию таблицы и должно уметь воссоздавать ее в случае необходимости. Хороший пример — Web-приложение, хранящее параметры сеанса в базе данных.

Реляционные базы данных хорошо работают с типизированными значениями фиксированного размера. В MySQL поддерживаются типы переменной длины, например BLOB и TEXT, но управлять ими сложнее. Такого рода информацию лучше хранить в файлах, а в базе данных достаточно запоминать путевые имена этих файлов в столбцах типа CHAR. Если база данных используется в Web-приложениях, помните о том, что у Web-сервера есть кеш-буфер загружаемых файлов изображений и аудиоклипов, поэтому он будет работать с такими файлами быстрее, чем MySQL.

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

Иногда возникает проблема — определить, когда стоит использовать столбцы типа CHAR, а когда VARCHAR. Если в таблице есть столбцы типа BLOB или TEXT, то предпочтение отдается типу VARCHAR, потому что все записи таблицы будут динамическими. То же самое справедливо для случая, когда средняя размерность значений столбца не превышает половины его размерности. Например, столбец типа VARCHAR (80) средняя размерность которого равна 10 символам, определен правильно. Если же средняя размерность превышает 40 символов, нужно поменять тип столбца на CHAR (80) Данное правило направлено на оптимизацию скорости работы с таблицами. Когда более важным фактором является экономия дискового пространства, то в большинстве случаев следует пользоваться типом VARCHAR. Для таблиц MyISAM поддерживается опция DELAY_KEY_WRITE. Она заставляет программу хранить изменения табличных индексов в памяти, пока таблица не будет закрыта. Это сокращает время записи на диск измененных табличных данных, но также повышает риск повреждения таблицы в случае сбоя сервера. Если используется данная опция, то при каждом перезапуске сервера необходимо проверять таблицы на предмет повреждений.

Процедура analyse() представляет собой удобное средство проверки таблицы после вставки данных, так как она определяет диапазон значений каждого столбца в полученном наборе записей. Ее нужно применять в инструкции SELECT, которая извлекает все записи отдельной таблицы. На основании анализа таблицы процедура analyse() предложит оптимальный тип данных для каждого столбца.

В некоторых случаях процедура analyse() сообщает о том, что вместо типа CHAR должен применяться тип ENUM. Это происходит, когда столбец содержит небольшое число повторяющихся значений. Столбец типа ENUM занимает гораздо меньше места, поскольку в действительности он хранит лишь номера элементов перечисления. Многие типы данных допускают регулирование своей размерности. Например, в столбце типа CHAR может храниться столько уникальных значений, что приводить его к типу ENUM нет никакого смысла, и все равно формальная размерность оказывается избыточной. То же самое касается типа INT, у которого существуют более "короткие" эквиваленты: MEDIUMINT, SMALLINT и TINYINT. Но не забудьте учесть будущее пополнение таблицы. Например, если в таблице 16000 записей, то для первичного ключа вполне подойдет тип SMALLINT. Если же предполагается, что в таблице будет более 65535 записей, следует остановиться на типе INT.

Обратите внимание на столбцы, в которых не могут присутствовать значения NULL. Для экономии места такие столбцы нужно объявлять со спецификатором NOT NULL. Числовые столбцы, в которых не могут храниться отрицательные числа, должны иметь спецификатор UNSIGNED.

Оптимизация приложений

Подключение к базе данных MySQL происходит относительно быстро в сравнении с другими СУБД, но это время можно еще уменьшить за счет кэширования соединений. Требуется лишь прикладная среда, позволяющая хранить идентификаторы соединений в памяти во время работы сервера. Например, модуль РНР непрерывно работает на Web-сервере. Он поддерживает функцию mysql pconnect(), которая создает постоянные соединения. Получив запрос на подключение к серверу, модуль РНР попытается использовать существующее соединение, если это возможно. В протоколах JDBC и ODBC тоже применяется технология кэширования соединений. Она особенно удобна, когда приложение создает большое число соединений за короткий промежуток времени. К примеру, если приложение вставляет данные в таблицу, можно предварительно помещать данные в буфер, с тем чтобы позднее занести их в таблицу в пакетном режиме. В этом случае лучше сразу же заблокировать таблицу, чтобы не пришлось многократно обновлять табличные индексы.

Приложение может дотировать информацию, извлекаемую из базы данных. Это выгодно, если данные меняются нечасто. Когда изменение данных все же происходит, приложение запрашивает принудительную очистку буфера. Предположим, что в Internet магазине имеется каталог продаваемых товаров. Этот каталог пополняется или обновляется раз в неделю, а то и меньше. Когда приложение отправляет клиенту html-страницу с описанием товара, оно вполне может взять информацию из кэша.

Если администратор захочет воспользоваться приложением для обновления цены товара, он должен будет очистить кэш. То же самое применимо и к программным блокам. Если нужно узнать название, цену и категорию товара, введите один запрос и сохраните полученные значения в программных переменных. Основная работа по выборке данных заключается в поиске нужной записи. Не имеет особого значения, 100 или 1000 байтов извлекаются из нее.