Тесты производительности 467

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

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

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

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

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

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

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

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

Иногда создают не просто итоговые столбцы, а целые таблицы. Например, можно сохранять результаты ключевых запросов в таблице, которая обновляется раз в день. Это избавит сервер от необходимости все время выполнять одни и те же трудоемкие запросы, хотя и повысит риск получения пользователями неактуальных данных.

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

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

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

Монт дениус рассказал мне правило определения того, когда следует использовать столбцы типа VARCHAR, а когда - CHAR. Если в таблице есть столбцы типа BLOB или TEXT, то предпочтение отдается типу VARCHAR, потому что все записи таблицы будут динамическими. То же самое справедливо для случая, когда средняя размерность значений столбца не превышает половины его размерности. Например, столбец типа VARCHAR (80), средняя размерность которого равна 10символам, определен правильно. Если же средняя размерность превышает 40 символов, нужно поменять

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

тип столбца на CHA О). Данное правило направлено на оптимизацию скорости работы с таблицами. Когда более важным фактором является экономия дискового пространства, то в большинстве случаев следует пользоваться типом VARCHAR.

Для таблиц MylSAM поддерживается опция DELAy KEY WRlTE. Она заставляет программу хранить изменения табличных индексов в памяти, пока таблица не будет закрыта. Это сокращает время записи на диск измененных табличных данных, но также повышает риск повреждения таблицы в случае сбоя сервера. Если используется данная опция, то при каждом перезапуске сервера необходимо проверять таблицы на предмет повреждений.

Процедура 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 тоже применяется технология кэширования соединений. Она особенно удобна, когда приложение создает большое число соединений за короткий промежуток времени.

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