Скорость загрузки веб-сайтов всегда была важной темой, но стала еще более актуальной с апреля 2010 года, когда Google стал учитывать её при ранжировании сайтов в поисковой выдаче. Однако, основной уклон, как правило, делался на уменьшении размера файлов, оптимизации параметров сервера, CSS и Javascript.
Также имеет место еще один немаловажный фактор. Это скорость, с которой веб-страницы формируются на сервере. Большинство крупных современных сайтов хранит всю информацию в базах данных и используют различные языки (например, PHP или ASP) для её извлечения, формирования HTML-страницы и отправки её браузеру.
Таким образом, если страницы вашего сайта отдаются более чем за 1,5 секунды (пороговое значение в Google, при котором сайт считается быстрым), вы можете разочаровать ваших посетителей, аналогично, если ваша страница с результатами поиска очень долго загружается или если, например, страницы с описаниями товаров загружаются быстро, но «Отзывы пользователей», грузятся несколько секунд.
В этой статье рассматриваются подобные вопросы и описывается несколько способов ускорить ваш сайт путем оптимизации базы данных. Она начинается с общеизвестных фактов, включая описание более сложных методов, и содержит список ресурсов для дальнейшего ознакомления. Статья предназначена для бесстрашных новичков и для разработчиков, совершающих необдуманные поступки.
База данных в общем представляет собой набор таблиц с информацией, такой как, например, список клиентов и их заказов. Это может быть картотека, куча электронных таблиц, файл Microsoft Access либо 40 терабайт данных Amazon о книгах и клиентах.
Типичная база данных для блога содержит таблицы, содержащие информацию о пользователях, категориях, постах и комментариях. Wordpress изначально имеет эти и еще несколько других таблиц. База данных для системы электронной коммерции содержит таблицы с информацией о клиентах, товарах, категориях, заказах, товарах, содержащихся в «корзине». Открытый движок Magento содержит эти и еще множество других таблиц. У базы данных много возможностей применения — для управления контентом, хранения отношений с клиентами, счетов и счетов-фактур, событий — оба этих типа (запись ля блогов и сайтов электронной коммерции) будут фигурировать в данной статье.
Некоторые таблицы в базе банных связаны с другими таблицами. Например, пост в блоге может содержать несколько комментариев, клиент может делать несколько заказов (это отношения вида «один-ко-многим»). Наиболее сложный вид отношений в базе данных — «многие-ко-многим». Такой вид отношений является является ядром баз данных систем электронной коммерции: один заказ может содержать различные товары, как и какой-либо товар может содержаться в различных заказах. Вот, где и появляется таблица «содержимое заказа», которая «находится» между заказами и товарами, и в неё производится запись каждый раз, как только пользователь добавляет товар в заказ. Насколько это важно мы поймём позже, когда будем рассматривать, почему долго выполняются некоторые запросы.
Под базой данных понимается также программное обеспечение, которое содержит все эти данные. ПО имеется ввиду, когда говорят «Пока завтракал сегодня, у меня база данных „упала“» или «Мне нужно базу данных обновить». («Здесь имеется в виду, скорее всего, СУБД, а не база данных», — сказал переводчик (:). Распространенные системы — Microsoft Access 2010, Microsoft SQL Server, MySQL, PostgreSQL и Oracle Database 11g.
Сокращение SQL часто упоминается, когда речь идет о базах данных. Оно происходит он словосочетания " Structured Query Language" (язык структурированных запросов) и произносится как «Эс-Кю-Эль» («ЯСЗ, если бы разрабатывали его в СССР», — сказал переводчик (:). Этот язык позволяет нам «общаться» с базой данных удивительным образом:
Новый пустой сайт будет работать очень быстро, но по мере развития проекта вы можете заметить некоторые «тормоза» на определенных страницах, в частности, на страницах со сложными элементами функциональности. Предположим, вы бы хотели отображать «Что еще покупают клиенты совместно с этим товаром...» в нижней части страницы со списком товаров. Для получения этой информации из базы данных необходимо выполнить следующие шаги:
Вы могли бы уместить всё в одном сложном запросе или разбить на несколько более простых. В любом случае, он может выполниться очень быстро, если в вашей базе данных 20 товаров, 12 клиентов, 18 заказов и 67 купленных товаров (общее число товаров в «корзинах»). Но если всё сделано неэффективно, выполнение подобной операции будет очень медленным на большом объеме данных. Например, обработка 500 товаров, 10000 клиентов, 14000 заказов и 100000 купленных товаров будут тормозить загрузку страницы.
Это очень замысловатый пример, но он дает возможность представить, что творится «за кулисами», и почему на вид безобидный элемент функциональности может «положить» сайт.
Замедление работы сайта может быть вызвано и другими причинами: серверу работает с недостаточным объемом памяти или дискового пространства; другой сайт на этом сервере потребляет много ресурсов; сервер ведёт email-рассылку либо занят какой-то другой «тяжелой» задачей; программные или аппаратные ошибки; неправильная конфигурация. Может, внезапно возросла популярность и, следовательно, посещаемость сайта? В следующих двух разделах скорость работы рассмотрим более детально.
В настоящее время существует несколько способов анализа скорости вашего сайта, в том числе плагин Firebug для Firefox, инструменты для разработчиков в Google Chrome (Shift+Ctrl+I, далее Ресурсы → Включить отслеживание ресурсов), Yahoo YSlow. Существуют также специальные сайты по типу WebPagetest, на котором вы вводите URL и он будет измерять скорость с указанного места.
Эти инструменты выводят диаграмму всех ресурсов (HTML, изображения, CSS и Javascript-файлы), имеющихся на странице, указывая время загрузки каждого из них. Эти инструменты также определят время, затраченное на:
Многие веб-страницы собираются полностью на сервере, в том числе и на PHP, который обращается к базе данных, затем отправляет всё сразу в браузер, поэтому любые задержки базы данных приведут к большому времени ожидания, а время приёма/загрузки данных будет пропорционально их объему. Таким образом, при быстром соединении веб-страница объемом 20 Кб, формирующаяся за 5 секунд (хоть и загружающаяся за 0,05 секунды) создаст большую задержку именно на сервере.
Но всё же не все страницы такие. PHP-функция flush() отправляет браузеру уже уже сформированные HTML-данные. Любые дальнейшие задержки уже будут связаны с загрузкой этих данных, а не с их ожиданием.
В любом случае, вы можете сравнить время ожидания/загрузки предположительно медленных и сложных веб-страниц с временем ожидания схожей по размеру HTML-страницы (или изображения, или другого статического элемента) на этом же сервере в это же время. Это исключит влияние, возможно, медленного подключения к Интернету, или загруженность сервера (оба этих варианта будут вызывать задержки) и позволит сравнить эти периоды времени, затраченного на формирование страниц. Это, конечно, не точная наука, всё же даст некоторое представление о том, что и где тормозит.
На скриншотах ниже представлены результаты анализа инструментом разработчика в Google Chrome веб-страницы и изображения одинакового размера — 20 Кб. Время ожидания веб-страницы составило 130 мс, время загрузки — 22 мс. Время для изображения 51 мс и 11 мс соответственно. Время загрузки у них приблизительно одинаковое, но серверу потребовалось дополнительных 80 мс на обработку и формирование веб-страницы, что является следствием выполнения PHP-кода и взаимодействия с базой данных.
При выполнении этих тестов, анализируя статический контент, обновляйте страницу, чтобы не получить её кешированную версию. Кроме того, проводите тест несколько раз, чтобы убедиться, что вы не столкнулись со статистическим отклонением. Третий скриншот снизу показывает, что WebPagetest показывает почти вдвое большее время, нежели инструмент от Google, на одной и той же странице, в одно и то же время. Это говорит о том, что нужно использовать при проведении тестов какой-либо один инструмент.
Получив общее представление, будем более подробно разбираться. Если вы подозреваете, что, возможно, база данных тормозит ваш сайт, нужно выяснить, что конкретно является причиной задержки. Я определю пару функций, которые будут вычислять время выполнения каждого запроса к базе данных. Этот код для PHP/MySQL, но метод может быть использован на любом использующем базы данных сайте:
StartTimer запускает таймер, а также выводит всё, что вы измеряете. Вторая строка — проверка вашего IP-адреса. Это может оказаться полезным, если вы (временно) проводите измерения на работающем сайте и не хотите, чтобы каждый смог смотреть подобную статистику. Раскомментируйте строку, удалив начальные // и замените 127.0.0.1 на ваш IP-адрес. StopTimer останавливает таймер и выводит затраченное время.
Большинство современных сайтов (особенно хорошо сделанные открытые проекты) имеют множество PHP-файлов, но запросы к базе данных выполняются только в некоторых из них. Поищите в этих файлах строки mysql_db_query или mysql_query. Многие программные разработки, такие как BBEdit, имеют функции, позволяющие выполнить подобный поиск. Если вы знакомы с консолью Linux, попробуйте выполнить следующую команду:
В результате получите что-то вроде этого:
Для WordPress 3.0.4 это будет строка 1112 файла wp-includes/wp-db.php. Вы можете скопировать функции, описанные выше, в начало файла (или в файл, который подключается к каждой странице), а затем дописать функции StartTimer и StopTimer до и после строки mysql_query, чтобы получилось так:
На скриншоте ниже показан результат добавления нашего кода сразу после установки WordPress. Всего обрабатывается 15 запросов, каждый занимает около 0.0003 сек. (0.3 мс), что ожидаемо от пустой базы данных.
Если вы нашли эту строку в других широко используемых системах, пожалуйста, поделитесь этой информацией, добавив комментарий к этой статье.
Вы можете делать и другие интересные вещи: вы можете увидеть, насколько ваш компьютер быстрее по сравнению с моим. «Отсчет» до 1.000.000 занимает на моем компьютере 2.9420 сек, мой сервер немного быстрее — 2.0726 сек.
Этот метод дает только сравнительные результаты. Если ваш сервер был занят в этот момент, то все запросы выполнялись медленнее, чем обычно. Но должны были по крайней мере в состоянии определить, сколько времени выполняется «быстрый» запрос (возможно, 1-5 мс), более медленные (более 200 мс) и самые «тяжелые» (более 1 секунды). Вы можете запускать этот тест несколько раз на протяжении часа или дня (но не сразу после предыдущего испытания — смотрите раздел о кеше базы данных), чтобы убедиться, что это не счастливая случайность.
Это также, скорее всего, серьезно испортит оформление веб-страницы. Также могут выдаваться различные предупреждения от PHP типа «Cannot modify header information. Headers already sent by…». Так происходит, потому что сообщения об измерениях опережают заголовки cookies и сессии. Если основное содержимое страницы отображается, можно игнорировать эти сообщения. Если страница получается пустой, то вам может понадобиться объявить функции StartTimer и StopTimer вокруг определенных блоков кода, а не вокруг mysql_query.
Этот метод является довольно-таки быстрым способом получения грубых результатов, его не стоит оставлять на работающем веб-сайте.
Если запросы к базе данных не такие уж и медленные, но страница по прежнему долго формируется, то причиной, скорее всего, является плохо написанный код. Вы можете добавить функции таймера вокруг больших участков кода, чтобы посмотреть, возможно, там задержка? Быть может причиной является то, что вы пробегаете по 10.000 строк информации, даже если вы отображаете всего 20 наименований?
Если вы всё ещё сбиты с толку и/или хотите получить более подробную информацию о том, что же творится в вашем коде, попробуйте различные средства отладки и профилирования, такие как Xdebug, который анализирует локальную копию сайта. Он может даже визуально отобразить все узкие места.
Эксперимент выше, возможно, вас удивил, показав, как много запросов к базе данных страницы на вашем сайте, и, надеюсь, помог вам выявить медленные запросы.
Чтобы ускорить процесс, давайте посмотрим теперь на некоторые простые усовершенствования. Для этого вам необходимо как-то посылать запросы непосредственно к базе данных. Многие пакеты для администрирования сервера (такие как CPanel или Plesk) поставляются вместе с PhpMyAdmin для выполнения подобных задач. Кроме того, можно загрузить на сайт что-то вроде phpMiniAdmin, всего один PHP-файл, который позволит вам просматривать базу данных и выполнять запросы. Вам нужно будет ввести имя базы данных, имя пользователя и пароль. Если вы не знаете их, вы легко можете найти их в файле конфигурации вашего сайта, если он есть (в WordPress, например, это WP-config.php).
Среди запросов к базе данных, которые содержат страницы сайта, вы, вероятно, видели условия с WHERE. Это способ фильтрации результатов при помощи SQL. Например, если вы просматриваете на сайте страницу «История покупок», есть, вероятно, запросы, определяющие того, кто разместил заказы. Что-то вроде этого:
Этот запрос извлекает все заказы, размещенные клиентом с id 2. На моем компьютере с 100.000 записей о заказах выполняется за 0,2158 сек.
Столбцы, такие как CustomerID, содержащие много возможных значений, которые используются в условиях с WHERE, в сочетании с = или <, или >, должны быть проиндексированы. Это как содержание в конце книги: оно помогает базе данных быстро извлекать индексированные данные. Это один из самых быстрых способов для ускорения запросов к базе данных.
Для того чтобы узнать, какие столбцы нужно индексировать, нужно иметь представление о том, как вообще используется база данных. Например, если Ваш сайт часто используется для поиска категории по названию или событий по дате, то эти столбцы должны быть проиндексированы:
Каждая из таблиц базы данных должна иметь столбец идентификатора (обычно id, но иногда ID или ArticleID и т.п.), указанный в качестве первичного ключа, как на скриншоте таблицы wp_posts ниже. Эти первичные ключи автоматически индексируются. Но вы также должны индексировать столбцы, которые ссылаются на идентификаторы в других таблицах, таких как CustomerID в примере выше. В этом случае они будут являться внешними ключами.
Если нужно производить поиск по большому объему текстовых данных, например, описанию товаров или содержимому статей, вы можете добавить другой вид индекса — FULL TEXT. Запросы, использующие индексы типа FULL TEXT, могут охватывать несколько столбцов и изначально настроены на учет слов длиной от 4 символов. Также исключаются "стоп-слова" и слова, встречающиеся в более 50% индексируемых записях. Однако, чтобы использовать этот тип индексов, вам необходимо изменить SQL-запрос. Ниже представлены запросы, без и с использованием FULL TEXT индекса:
Может показаться, что таким образом нужно всё проиндексировать. Но, хоть индексирования и ускоряет выборку, оно замедляет операции вставки, обновления и удаления. Так, если у вас есть таблица с описанием товаров, которая вряд ли часто меняется, вы можете её проиндексировать. Но таблица с заказами, вероятно, будет постоянно меняться — в этом случае нужно быть осторожнее с индексацией.
Также нужно помнить о случаях, в которых индексирование не поможет. Например, если большинство значений в каком-либо столбце имели бы одинаковое значение. Если бы в столбце статус товара использовалось значение «1», означающее «есть в наличии», и 95% всех товаров были бы «в наличии», индекс бы не помог при поиске товаров, которые имеются в наличии. Представьте, если бы на какой-нибудь предлог нужно было сделать указатель в конце книги, то ссылки бы шли на каждую её страницу.
Используя PhpMyAdmin или phpMiniAdmin, вы можете посмотреть на структуру каждой таблицы и увидеть, проиндексированы ли нужные столбцы. В PhpMyAdmin, выберите имя таблицы, в конце структуры будет перечисление индексов; в phpMiniAdmin, в верхней части кликниите на «Show tables», затем «sct» (show create table) напротив нужной вам таблицы; В результате этих действий выведется запрос, необходимый для создания таблицы, в конце которого также будет приведен список индексов, что-то вроде:
Если индексов не существует, вы можете самостоятельно из создать. В PhpMyAdmin в разделе «Индексы» укажите количество столбцов, по которым нужно построить индекс и нажмите «Go». Введите название индекса, выберите нужные столбцы и нажмите «Сохранить», как показано на скриншоте ниже:
В PhpMiniAdmin вам нужно будет выполнить следующий запрос, вставив его соответсующее поле вверху страницы:
Выполнение поискового запроса после создания индекса потребовало 0.0019 сек на моем компьютере, что в 113 раз быстрее.
Добавление FULL TEXT индекса делается аналогично. Индекс должен быть составлен по тем столбцам, собственно, по которым вы ищите:
Перед тем как производить какие-либо изменения в таблицах сделайте резервную копию всей базы данных. Вы можете сделать это с помощью PhpMyAdmin и PhpMiniAdmin, нажав кнопку «Экспорт». Если ваша база данных содержит важную информацию, например, о клиентах, сохраняйте резервные копии в безопасном месте. Вы также можете использовать команду mysqldump для резервного копирования базы данных через SSH:
Подобные сценарии также представляют риск для безопасности, т.к. предоставляют злоумышленнику более простой способ добраться до ваших данных. В отличие от PhpMyAdmin, который в какой-то степени защищен средствами управления сервером, phpMiniAdmin представляет собой один файл, который недолго загрузить и забыть о нём. Будет лучше защитить доступ к нему паролем либо удалить сразу после использования.
MySQL и другие видов программного обеспечения баз данных имеют встроенные инструменты для оптимизации. Если данные в ваших таблицах часто меняются, то вы можете использовать подобные инструменты регулярно, чтобы таблицы базы данных занимали меньше места и были более эффективными. Но подобные процедуры занимают определенное время (от нескольких секунд до нескольких минут или больше, в зависимости от размера таблицы), и они могут блокировать другие запросы, так что делать оптимизацию лучше в период наименьших нагрузок. Не утихают споры о необходимой периодичности оптимизаций.
Чтобы запустить процедуру оптимизации (для таблицы orders), выполните следующую команду:
Моя неоптимизированная таблица orders со 100000 записей занимала 31,2 МБ, а запрос вида SELECT * FROM orders выполнялся за 0.2676 сек. После первой оптимизации размер сократился до 30,8 МБ, да и запрос выполняется за 0.0595 сек.
Следующая PHP-функция запустит оптимизацию всех таблиц в базе данных:
Перед запуском этой функции следует подключиться к базе данных. Большинство современных сайтов делают это автоматически, но для полноты картины приведём соответствующий код:
Так же как браузер кэширует посещаемые веб-страницы, могут индексироваться и часто выполняемые запросы к базе данных. На выполнение упомянутого выше запроса потребовалось 0.0019 сек. с использованием индекса:
Повторный выполнение этого же запроса требует всего лишь 0.0004 сек., т.к. MySQL запомнил результаты выполнения и может вывести их, не выполняя запрос повторно полностью.
Тем не менее, многие новостные сайты и блоги, возможно, используют подобные запросы, чтобы убедиться, что статьи выводятся только после даты «публикации»:
Подобные запросы не могут кэшироваться, т.к. они зависят от текущего времени и даты. В таблице со 100000 записей запрос по типу одно из приведенных выше выполнялся на моём компьютере около 0.38 сек. на неиндексированном столбце.
Если подобные запросы выполняются на каждой странице вашего сайта сотни раз минуту, то как раз-таки кэширование позволит существенно увеличить производительность. «Заставить» запросы использовать кэширование можно путем замены NOW и CURDATE фактическим временем, например, вот так:
Можно использовать PHP, чтобы убедиться, что временные интервалы составляют 5 минут или около того:
Выражение % 300 округляет время с точностью до 300 секунд (5 минут).
В MySQL есть также и другие некэшируемые функции, например, RAND.
Увеличивающийся объем кэшируемых данных, также может и замедлить работу веб-сайта. Чем больше сообщений, страниц, категорий, товаров, статей и других элементов будет на вашем сайте, тем запросы должны быть более связанные. Взгляните на пример:
Быть может, если бы на вашем сайте было 500 категорий, подобные запросы помещались в кэш, и результат возвращался бы миллисекунды. А что если будет 1000 постоянно просматриваемых категорий? Они будут вытеснять друг друга из кэша и выполняться гораздо медленнее. В данном случае может помочь увеличение размера кэша. Но выделение больше памяти под кэш может негативно сказаться при выполнении других задач, так что будьте внимательны. Можно найти множество советов о включении и повышении эффективности кэша путем изменения серверных переменных.
В случае изменений в таблице кэш становится бессильным. При вставке, обновлении, удалении строки из таблицы все запросы, относящиеся к этой таблице, удаляются из кэша. Так что если, таблица 'articles' обновляется каждый раз при просмотре какой-либо статьи (например, в этой таблице есть поле со счетчиком просмотров), то улучшения, описанные выше, могут и не помочь.
В таких случаях вам, возможно, захочется изучить какие-либо средства, реализующие кэш на уровне приложения, например, Memcached. Также можете прочитать следующий раздел для получения дополнительной информации, касающейся создания собственной системы кэширования. Оба этих варианта требуют гораздо больших программных изменений нежели мы обсуждали.
Если особенно тяжелые запросы к базе данных долго выполняются, и данные меняются не часто, можно самостоятельно кэшировать результаты.
Допустим, вы хотите показать 20 популярных записей на сайте за последнюю неделю, используя формулу, которая учитывает поисковые запросы, просмотры, добавление в «избранное», «Отправить другу». И вы хотите отображать этот перечень в виде маркированного списка на главной странице.
Самым простым способом будет, например, используя PHP, сделать запрос к базе данных с периодичностью один раз в час или в день и сохранять результаты в отдельный файл, который потом просто подключать на странице сайта.
Написав PHP-код, который генерирует файл со списком, можете использовать несколько методов для его запуска по расписанию. Можете использовать серверный планировщик (в Plesk 8: Server → Scheduled Tasks) для запуска этого скрипта ежечасно таким образом:
Кроме того, вы можете использовать тот же PHP для проверки времени создания файла. Если файл был создан хотя бы час назад, то выполнять запрос. 3600 в данном случае это количество секунд в часе:
Возвращаясь к описанному выше примеру «Что еще покупают клиенты совместно с этим товаром...», вы также можете кэшировать данные в новом столбце или вообще таблице. Раз в неделю можно будет запускать большой набор сложных запросов для каждого товара, чтобы определить, какие товары покупаются совместно с ним.
Затем можно хранить результирующие идентификаторы товаров в новом столбце в виде множества элементов, разделенных запятыми. В дальнейшем, чтобы получить список товаров, которые приобретаются совместно с товаром с id = 12 нужно будет выполнить такой зарос:
В каком-либо из разделов вашего сайта, возможно в административном разделе, выводится список пользователей с совершенными ими заказами.
При этом используется запрос наподобие приведенного ниже (для выборки по значению, означающему, что заказ выполнен):
И для каждого заказа нужно еще найти клиента, который его оформил:
Если на странице выводится сразу информация о 100 заказах, нужно будет делать уже 101 запрос. А если нужно еще выводить информацию об адресе доставки из другой таблицы, общую стоимость всех заказов, скорость создания страницы будет падать, падать… Всё можно сделать гораздо быстрее, объединив запросы через JOIN. Далее пример объединения вышеописанных запросов:
Есть также и другой способ записать эти запросы, но без JOIN:
Перевод запросов на использование JOIN может вызвать сложности, т.к. придется менять и PHP-код. Но если на «медленной» странице выполняется тысячи запросов, то, может быть, стоит обратить внимание на описанный метод? За дополнительной информации можете обратиться к Википедии, в которой подробно рассказывается про JOIN. Столбцы, которые используются в JOIN (в примере — customerid), должны быть проиндексированы.
Вы также можете попросить MySQL "объяснить", как она выполняет ваш запрос. В результате вы увидите, как и какие таблицы используются при выполнении запроса, и можете что-то оптимизировать. На скриншоте ниже приведен результат выполнения одного из сложных запросов в WordPress с использованием EXPLAIN:
На скриншоте видно, какие таблицы и индексы используются, тип JOIN, количество проанализированных строк и другая информация. На официальном сайте MySQL описывается, что именно объясняет EXPLAIN, и немного говорится о том, как использовать эту информацию для оптимизации запросов (например, добавлением индексов).
Наконец, возвращаясь опять к примеру про «Что еще покупают клиенты совместно с этим товаром...», вы можете его упростить. Переименуйте его в«Рекомендуемые товары», например, и выводите несколько других товаров их этой же категории или указанные вручную.
В этой статье показано несколько методов для повышения производительности базы данных от простых до довольно-таки сложных. Хотя большинство грамотно разработанных сайтов уже должны включать подобные методы (использование JOIN'ов и индексов).
Ведётся много споров на счет эффективности и надежности использования некоторых из описанных методов (измерение скорости, индексирование, оптимизация, кэширование и т.д.), так что окончательное решение за вами. В любом случае, теперь у вас есть варианты, над которыми стоит подумать.
Если ваш сайт начал медленнее работать после нескольких месяцев или лет нормальной работы, у вас есть, от чего отталкиваться, чтобы найти причину.
P.S. Замечания по поводу, пожалуйста, в личку.
Также имеет место еще один немаловажный фактор. Это скорость, с которой веб-страницы формируются на сервере. Большинство крупных современных сайтов хранит всю информацию в базах данных и используют различные языки (например, PHP или ASP) для её извлечения, формирования HTML-страницы и отправки её браузеру.
Таким образом, если страницы вашего сайта отдаются более чем за 1,5 секунды (пороговое значение в Google, при котором сайт считается быстрым), вы можете разочаровать ваших посетителей, аналогично, если ваша страница с результатами поиска очень долго загружается или если, например, страницы с описаниями товаров загружаются быстро, но «Отзывы пользователей», грузятся несколько секунд.
Порог, установленный Google для определения «быстрых» веб-сайтов, составляет ~1,5 секунды. Подобный график можно получить «Инструментах для веб-мастеров» (перейдите в [домен] → Диагностика → Производительность).
В этой статье рассматриваются подобные вопросы и описывается несколько способов ускорить ваш сайт путем оптимизации базы данных. Она начинается с общеизвестных фактов, включая описание более сложных методов, и содержит список ресурсов для дальнейшего ознакомления. Статья предназначена для бесстрашных новичков и для разработчиков, совершающих необдуманные поступки.
Что такое база данных? Что такое SQL?
База данных в общем представляет собой набор таблиц с информацией, такой как, например, список клиентов и их заказов. Это может быть картотека, куча электронных таблиц, файл Microsoft Access либо 40 терабайт данных Amazon о книгах и клиентах.
Типичная база данных для блога содержит таблицы, содержащие информацию о пользователях, категориях, постах и комментариях. Wordpress изначально имеет эти и еще несколько других таблиц. База данных для системы электронной коммерции содержит таблицы с информацией о клиентах, товарах, категориях, заказах, товарах, содержащихся в «корзине». Открытый движок Magento содержит эти и еще множество других таблиц. У базы данных много возможностей применения — для управления контентом, хранения отношений с клиентами, счетов и счетов-фактур, событий — оба этих типа (запись ля блогов и сайтов электронной коммерции) будут фигурировать в данной статье.
Некоторые таблицы в базе банных связаны с другими таблицами. Например, пост в блоге может содержать несколько комментариев, клиент может делать несколько заказов (это отношения вида «один-ко-многим»). Наиболее сложный вид отношений в базе данных — «многие-ко-многим». Такой вид отношений является является ядром баз данных систем электронной коммерции: один заказ может содержать различные товары, как и какой-либо товар может содержаться в различных заказах. Вот, где и появляется таблица «содержимое заказа», которая «находится» между заказами и товарами, и в неё производится запись каждый раз, как только пользователь добавляет товар в заказ. Насколько это важно мы поймём позже, когда будем рассматривать, почему долго выполняются некоторые запросы.
Под базой данных понимается также программное обеспечение, которое содержит все эти данные. ПО имеется ввиду, когда говорят «Пока завтракал сегодня, у меня база данных „упала“» или «Мне нужно базу данных обновить». («Здесь имеется в виду, скорее всего, СУБД, а не база данных», — сказал переводчик (:). Распространенные системы — Microsoft Access 2010, Microsoft SQL Server, MySQL, PostgreSQL и Oracle Database 11g.
Сокращение SQL часто упоминается, когда речь идет о базах данных. Оно происходит он словосочетания " Structured Query Language" (язык структурированных запросов) и произносится как «Эс-Кю-Эль» («ЯСЗ, если бы разрабатывали его в СССР», — сказал переводчик (:). Этот язык позволяет нам «общаться» с базой данных удивительным образом:
SELECT lastname FROM customers WHERE city='Brighton';Как же база данных может тормозить сайт?
Новый пустой сайт будет работать очень быстро, но по мере развития проекта вы можете заметить некоторые «тормоза» на определенных страницах, в частности, на страницах со сложными элементами функциональности. Предположим, вы бы хотели отображать «Что еще покупают клиенты совместно с этим товаром...» в нижней части страницы со списком товаров. Для получения этой информации из базы данных необходимо выполнить следующие шаги:
- Определить товар, с которым будем «работать».
- Определить, сколько раз за последнее время этот товар добавлялся клиентами в «корзину» (таблица «содержимое заказа» выше по тексту).
- Получить список товаров, которые также добавлялись в «корзину» при том же заказе (только подтвержденные заказы).
- Выбрать покупателей, которые делали эти заказы.
- Выбрать заказы, которые сделали пользователи из пункта выше.
- Посмотреть на содержимое их заказов (та же таблица «содержимое заказа»).
- Получить информацию об этих товарах.
- Выявить товары, которые встречаются в этих заказах чаще, и вывести этот список.
Вы могли бы уместить всё в одном сложном запросе или разбить на несколько более простых. В любом случае, он может выполниться очень быстро, если в вашей базе данных 20 товаров, 12 клиентов, 18 заказов и 67 купленных товаров (общее число товаров в «корзинах»). Но если всё сделано неэффективно, выполнение подобной операции будет очень медленным на большом объеме данных. Например, обработка 500 товаров, 10000 клиентов, 14000 заказов и 100000 купленных товаров будут тормозить загрузку страницы.
Это очень замысловатый пример, но он дает возможность представить, что творится «за кулисами», и почему на вид безобидный элемент функциональности может «положить» сайт.
Замедление работы сайта может быть вызвано и другими причинами: серверу работает с недостаточным объемом памяти или дискового пространства; другой сайт на этом сервере потребляет много ресурсов; сервер ведёт email-рассылку либо занят какой-то другой «тяжелой» задачей; программные или аппаратные ошибки; неправильная конфигурация. Может, внезапно возросла популярность и, следовательно, посещаемость сайта? В следующих двух разделах скорость работы рассмотрим более детально.
Дело в базе данных?
В настоящее время существует несколько способов анализа скорости вашего сайта, в том числе плагин Firebug для Firefox, инструменты для разработчиков в Google Chrome (Shift+Ctrl+I, далее Ресурсы → Включить отслеживание ресурсов), Yahoo YSlow. Существуют также специальные сайты по типу WebPagetest, на котором вы вводите URL и он будет измерять скорость с указанного места.
Эти инструменты выводят диаграмму всех ресурсов (HTML, изображения, CSS и Javascript-файлы), имеющихся на странице, указывая время загрузки каждого из них. Эти инструменты также определят время, затраченное на:
- выполнение DNS-запроса (преобразование доменных имен в IP-адрес),
- соединение с сервером,
- ожидание ответа от сервера («Time to first byte» aka TTFB — время до первого байта (прим. переводчика)),
- получение (загрузку) данных от сервера.
Многие веб-страницы собираются полностью на сервере, в том числе и на PHP, который обращается к базе данных, затем отправляет всё сразу в браузер, поэтому любые задержки базы данных приведут к большому времени ожидания, а время приёма/загрузки данных будет пропорционально их объему. Таким образом, при быстром соединении веб-страница объемом 20 Кб, формирующаяся за 5 секунд (хоть и загружающаяся за 0,05 секунды) создаст большую задержку именно на сервере.
Но всё же не все страницы такие. PHP-функция flush() отправляет браузеру уже уже сформированные HTML-данные. Любые дальнейшие задержки уже будут связаны с загрузкой этих данных, а не с их ожиданием.
В любом случае, вы можете сравнить время ожидания/загрузки предположительно медленных и сложных веб-страниц с временем ожидания схожей по размеру HTML-страницы (или изображения, или другого статического элемента) на этом же сервере в это же время. Это исключит влияние, возможно, медленного подключения к Интернету, или загруженность сервера (оба этих варианта будут вызывать задержки) и позволит сравнить эти периоды времени, затраченного на формирование страниц. Это, конечно, не точная наука, всё же даст некоторое представление о том, что и где тормозит.
На скриншотах ниже представлены результаты анализа инструментом разработчика в Google Chrome веб-страницы и изображения одинакового размера — 20 Кб. Время ожидания веб-страницы составило 130 мс, время загрузки — 22 мс. Время для изображения 51 мс и 11 мс соответственно. Время загрузки у них приблизительно одинаковое, но серверу потребовалось дополнительных 80 мс на обработку и формирование веб-страницы, что является следствием выполнения PHP-кода и взаимодействия с базой данных.
При выполнении этих тестов, анализируя статический контент, обновляйте страницу, чтобы не получить её кешированную версию. Кроме того, проводите тест несколько раз, чтобы убедиться, что вы не столкнулись со статистическим отклонением. Третий скриншот снизу показывает, что WebPagetest показывает почти вдвое большее время, нежели инструмент от Google, на одной и той же странице, в одно и то же время. Это говорит о том, что нужно использовать при проведении тестов какой-либо один инструмент.
Используя инструментарий Google Chrome, получаем 130 мс при загрузке веб-страницы
Тот же инструмент. Имеем 51 мс при загрузке изображения схожего размера
Анализируя с помощью WebPagetest ту же страницу, получаем 296 мс на ожидание и 417 мс общее время загрузки
Измеряем запросы в MySQL/PHP?
Получив общее представление, будем более подробно разбираться. Если вы подозреваете, что, возможно, база данных тормозит ваш сайт, нужно выяснить, что конкретно является причиной задержки. Я определю пару функций, которые будут вычислять время выполнения каждого запроса к базе данных. Этот код для PHP/MySQL, но метод может быть использован на любом использующем базы данных сайте:
function StartTimer ($what='') {
global $MYTIMER; $MYTIMER=0; //global variable to store time
//if ($_SERVER['REMOTE_ADDR'] != '127.0.0.1') return; //only show for my IP address
echo '<p style="border:1px solid black; color: black; background: yellow;">';
echo "About to run <i>$what</i>. "; flush(); //output this to the browser
//$MYTIMER = microtime (true); //in PHP5 you need only this line to get the time
list ($usec, $sec) = explode (' ', microtime());
$MYTIMER = ((float) $usec + (float) $sec); //set the timer
}
function StopTimer() {
global $MYTIMER; if (!$MYTIMER) return; //no timer has been started
list ($usec, $sec) = explode (' ', microtime()); //get the current time
$MYTIMER = ((float) $usec + (float) $sec) - $MYTIMER; //the time taken in milliseconds
echo 'Took ' . number_format ($MYTIMER, 4) . ' seconds.</p>'; flush();
}
StartTimer запускает таймер, а также выводит всё, что вы измеряете. Вторая строка — проверка вашего IP-адреса. Это может оказаться полезным, если вы (временно) проводите измерения на работающем сайте и не хотите, чтобы каждый смог смотреть подобную статистику. Раскомментируйте строку, удалив начальные // и замените 127.0.0.1 на ваш IP-адрес. StopTimer останавливает таймер и выводит затраченное время.
Большинство современных сайтов (особенно хорошо сделанные открытые проекты) имеют множество PHP-файлов, но запросы к базе данных выполняются только в некоторых из них. Поищите в этих файлах строки mysql_db_query или mysql_query. Многие программные разработки, такие как BBEdit, имеют функции, позволяющие выполнить подобный поиск. Если вы знакомы с консолью Linux, попробуйте выполнить следующую команду:
grep mysql_query `find . -name \*php`В результате получите что-то вроде этого:
mysql_query ($sql);Для WordPress 3.0.4 это будет строка 1112 файла wp-includes/wp-db.php. Вы можете скопировать функции, описанные выше, в начало файла (или в файл, который подключается к каждой странице), а затем дописать функции StartTimer и StopTimer до и после строки mysql_query, чтобы получилось так:
StartTimer ($query);
$this->result = @mysql_query( $query, $dbh );
StopTimer();
На скриншоте ниже показан результат добавления нашего кода сразу после установки WordPress. Всего обрабатывается 15 запросов, каждый занимает около 0.0003 сек. (0.3 мс), что ожидаемо от пустой базы данных.
Здесь отображаются и измеряются все запросы WordPress
Если вы нашли эту строку в других широко используемых системах, пожалуйста, поделитесь этой информацией, добавив комментарий к этой статье.
Вы можете делать и другие интересные вещи: вы можете увидеть, насколько ваш компьютер быстрее по сравнению с моим. «Отсчет» до 1.000.000 занимает на моем компьютере 2.9420 сек, мой сервер немного быстрее — 2.0726 сек.
StartTimer ('counting to 10000000');
for ($i=0; $i<10000000; $i++); //count to a high number
StopTimer();
Кое-что о результатах
Этот метод дает только сравнительные результаты. Если ваш сервер был занят в этот момент, то все запросы выполнялись медленнее, чем обычно. Но должны были по крайней мере в состоянии определить, сколько времени выполняется «быстрый» запрос (возможно, 1-5 мс), более медленные (более 200 мс) и самые «тяжелые» (более 1 секунды). Вы можете запускать этот тест несколько раз на протяжении часа или дня (но не сразу после предыдущего испытания — смотрите раздел о кеше базы данных), чтобы убедиться, что это не счастливая случайность.
Это также, скорее всего, серьезно испортит оформление веб-страницы. Также могут выдаваться различные предупреждения от PHP типа «Cannot modify header information. Headers already sent by…». Так происходит, потому что сообщения об измерениях опережают заголовки cookies и сессии. Если основное содержимое страницы отображается, можно игнорировать эти сообщения. Если страница получается пустой, то вам может понадобиться объявить функции StartTimer и StopTimer вокруг определенных блоков кода, а не вокруг mysql_query.
Этот метод является довольно-таки быстрым способом получения грубых результатов, его не стоит оставлять на работающем веб-сайте.
В чём ещё может быть причина?
Если запросы к базе данных не такие уж и медленные, но страница по прежнему долго формируется, то причиной, скорее всего, является плохо написанный код. Вы можете добавить функции таймера вокруг больших участков кода, чтобы посмотреть, возможно, там задержка? Быть может причиной является то, что вы пробегаете по 10.000 строк информации, даже если вы отображаете всего 20 наименований?
Профилирование
Если вы всё ещё сбиты с толку и/или хотите получить более подробную информацию о том, что же творится в вашем коде, попробуйте различные средства отладки и профилирования, такие как Xdebug, который анализирует локальную копию сайта. Он может даже визуально отобразить все узкие места.
Индексирование таблиц
Эксперимент выше, возможно, вас удивил, показав, как много запросов к базе данных страницы на вашем сайте, и, надеюсь, помог вам выявить медленные запросы.
Чтобы ускорить процесс, давайте посмотрим теперь на некоторые простые усовершенствования. Для этого вам необходимо как-то посылать запросы непосредственно к базе данных. Многие пакеты для администрирования сервера (такие как CPanel или Plesk) поставляются вместе с PhpMyAdmin для выполнения подобных задач. Кроме того, можно загрузить на сайт что-то вроде phpMiniAdmin, всего один PHP-файл, который позволит вам просматривать базу данных и выполнять запросы. Вам нужно будет ввести имя базы данных, имя пользователя и пароль. Если вы не знаете их, вы легко можете найти их в файле конфигурации вашего сайта, если он есть (в WordPress, например, это WP-config.php).
Среди запросов к базе данных, которые содержат страницы сайта, вы, вероятно, видели условия с WHERE. Это способ фильтрации результатов при помощи SQL. Например, если вы просматриваете на сайте страницу «История покупок», есть, вероятно, запросы, определяющие того, кто разместил заказы. Что-то вроде этого:
SELECT * FROM orders WHERE customerid = 2;
Этот запрос извлекает все заказы, размещенные клиентом с id 2. На моем компьютере с 100.000 записей о заказах выполняется за 0,2158 сек.
Столбцы, такие как CustomerID, содержащие много возможных значений, которые используются в условиях с WHERE, в сочетании с = или <, или >, должны быть проиндексированы. Это как содержание в конце книги: оно помогает базе данных быстро извлекать индексированные данные. Это один из самых быстрых способов для ускорения запросов к базе данных.
Что индексировать?
Для того чтобы узнать, какие столбцы нужно индексировать, нужно иметь представление о том, как вообще используется база данных. Например, если Ваш сайт часто используется для поиска категории по названию или событий по дате, то эти столбцы должны быть проиндексированы:
SELECT * FROM categories WHERE name = 'Books';
SELECT * FROM events WHERE startdate >= '2011-02-07';
Каждая из таблиц базы данных должна иметь столбец идентификатора (обычно id, но иногда ID или ArticleID и т.п.), указанный в качестве первичного ключа, как на скриншоте таблицы wp_posts ниже. Эти первичные ключи автоматически индексируются. Но вы также должны индексировать столбцы, которые ссылаются на идентификаторы в других таблицах, таких как CustomerID в примере выше. В этом случае они будут являться внешними ключами.
SELECT * FROM orders WHERE customerid = 2;
SELECT * FROM orderitems WHERE orderid = 231;
Если нужно производить поиск по большому объему текстовых данных, например, описанию товаров или содержимому статей, вы можете добавить другой вид индекса — FULL TEXT. Запросы, использующие индексы типа FULL TEXT, могут охватывать несколько столбцов и изначально настроены на учет слов длиной от 4 символов. Также исключаются "стоп-слова" и слова, встречающиеся в более 50% индексируемых записях. Однако, чтобы использовать этот тип индексов, вам необходимо изменить SQL-запрос. Ниже представлены запросы, без и с использованием FULL TEXT индекса:
SELECT * FROM products WHERE name LIKE '%shoe%' OR description LIKE '%shoe%';
SELECT * FROM products WHERE MATCH(name,description) AGAINST ('shoe');
Может показаться, что таким образом нужно всё проиндексировать. Но, хоть индексирования и ускоряет выборку, оно замедляет операции вставки, обновления и удаления. Так, если у вас есть таблица с описанием товаров, которая вряд ли часто меняется, вы можете её проиндексировать. Но таблица с заказами, вероятно, будет постоянно меняться — в этом случае нужно быть осторожнее с индексацией.
Также нужно помнить о случаях, в которых индексирование не поможет. Например, если большинство значений в каком-либо столбце имели бы одинаковое значение. Если бы в столбце статус товара использовалось значение «1», означающее «есть в наличии», и 95% всех товаров были бы «в наличии», индекс бы не помог при поиске товаров, которые имеются в наличии. Представьте, если бы на какой-нибудь предлог нужно было сделать указатель в конце книги, то ссылки бы шли на каждую её страницу.
SELECT * FROM products WHERE stock_status = 1;Как индексировать?
Используя PhpMyAdmin или phpMiniAdmin, вы можете посмотреть на структуру каждой таблицы и увидеть, проиндексированы ли нужные столбцы. В PhpMyAdmin, выберите имя таблицы, в конце структуры будет перечисление индексов; в phpMiniAdmin, в верхней части кликниите на «Show tables», затем «sct» (show create table) напротив нужной вам таблицы; В результате этих действий выведется запрос, необходимый для создания таблицы, в конце которого также будет приведен список индексов, что-то вроде:
orderidindex" KEY ("orderid");
Используем PhpMiniAdmin для просмотра списка индексов в таблице wp_posts в WordPress
Если индексов не существует, вы можете самостоятельно из создать. В PhpMyAdmin в разделе «Индексы» укажите количество столбцов, по которым нужно построить индекс и нажмите «Go». Введите название индекса, выберите нужные столбцы и нажмите «Сохранить», как показано на скриншоте ниже:
Создание индекса средствами PhpMyAdmin
В PhpMiniAdmin вам нужно будет выполнить следующий запрос, вставив его соответсующее поле вверху страницы:
ALTER TABLE orders ADD INDEX customeridindex (customerid);Выполнение поискового запроса после создания индекса потребовало 0.0019 сек на моем компьютере, что в 113 раз быстрее.
Добавление FULL TEXT индекса делается аналогично. Индекс должен быть составлен по тем столбцам, собственно, по которым вы ищите:
ALTER TABLE articles ADD FULLTEXT(title,author,articletext);
SELECT * FROM articles WHERE MATCH(title,author,articletext) AGAINST ('mysql');
Резервные копии и безопасность
Перед тем как производить какие-либо изменения в таблицах сделайте резервную копию всей базы данных. Вы можете сделать это с помощью PhpMyAdmin и PhpMiniAdmin, нажав кнопку «Экспорт». Если ваша база данных содержит важную информацию, например, о клиентах, сохраняйте резервные копии в безопасном месте. Вы также можете использовать команду mysqldump для резервного копирования базы данных через SSH:
mysqldump --user=myuser --password=mypassword
--single-transaction --add-drop-table mydatabase
> backup`date +%Y%e%d`.sql
Подобные сценарии также представляют риск для безопасности, т.к. предоставляют злоумышленнику более простой способ добраться до ваших данных. В отличие от PhpMyAdmin, который в какой-то степени защищен средствами управления сервером, phpMiniAdmin представляет собой один файл, который недолго загрузить и забыть о нём. Будет лучше защитить доступ к нему паролем либо удалить сразу после использования.
Оптимизируем таблицы
MySQL и другие видов программного обеспечения баз данных имеют встроенные инструменты для оптимизации. Если данные в ваших таблицах часто меняются, то вы можете использовать подобные инструменты регулярно, чтобы таблицы базы данных занимали меньше места и были более эффективными. Но подобные процедуры занимают определенное время (от нескольких секунд до нескольких минут или больше, в зависимости от размера таблицы), и они могут блокировать другие запросы, так что делать оптимизацию лучше в период наименьших нагрузок. Не утихают споры о необходимой периодичности оптимизаций.
Чтобы запустить процедуру оптимизации (для таблицы orders), выполните следующую команду:
OPTIMIZE TABLE orders;
Моя неоптимизированная таблица orders со 100000 записей занимала 31,2 МБ, а запрос вида SELECT * FROM orders выполнялся за 0.2676 сек. После первой оптимизации размер сократился до 30,8 МБ, да и запрос выполняется за 0.0595 сек.
Следующая PHP-функция запустит оптимизацию всех таблиц в базе данных:
function OptimizeAllTables() {
$tables = mysql_query ('SHOW TABLES'); //get all the tables
while ($table = mysql_fetch_array ($tables))
mysql_query ('OPTIMIZE TABLE ' . $table[0]); //optimize them
}
Перед запуском этой функции следует подключиться к базе данных. Большинство современных сайтов делают это автоматически, но для полноты картины приведём соответствующий код:
mysql_connect (DB_HOST, DB_USER, DB_PASSWORD);
mysql_select_db (DB_NAME);
OptimizeAllTables();
Используем кэш
Так же как браузер кэширует посещаемые веб-страницы, могут индексироваться и часто выполняемые запросы к базе данных. На выполнение упомянутого выше запроса потребовалось 0.0019 сек. с использованием индекса:
SELECT * FROM orders WHERE customerid=2;
Повторный выполнение этого же запроса требует всего лишь 0.0004 сек., т.к. MySQL запомнил результаты выполнения и может вывести их, не выполняя запрос повторно полностью.
Тем не менее, многие новостные сайты и блоги, возможно, используют подобные запросы, чтобы убедиться, что статьи выводятся только после даты «публикации»:
SELECT * FROM posts WHERE publisheddate <= CURDATE();
SELECT * FROM articles WHERE publisheddate <= NOW();
Подобные запросы не могут кэшироваться, т.к. они зависят от текущего времени и даты. В таблице со 100000 записей запрос по типу одно из приведенных выше выполнялся на моём компьютере около 0.38 сек. на неиндексированном столбце.
Если подобные запросы выполняются на каждой странице вашего сайта сотни раз минуту, то как раз-таки кэширование позволит существенно увеличить производительность. «Заставить» запросы использовать кэширование можно путем замены NOW и CURDATE фактическим временем, например, вот так:
SELECT * FROM articles WHERE publisheddate <= '2011-01-17 17:00';
Можно использовать PHP, чтобы убедиться, что временные интервалы составляют 5 минут или около того:
$time = time();
$currenttime = date ('Y-m-d H:i', $time - ($time % 300));
mysql_query (“SELECT * FROM articles WHERE publisheddate <= '$currenttime'”);
Выражение % 300 округляет время с точностью до 300 секунд (5 минут).
В MySQL есть также и другие некэшируемые функции, например, RAND.
А кэш-то растёт...
Увеличивающийся объем кэшируемых данных, также может и замедлить работу веб-сайта. Чем больше сообщений, страниц, категорий, товаров, статей и других элементов будет на вашем сайте, тем запросы должны быть более связанные. Взгляните на пример:
SELECT * FROM articles WHERE publisheddate <= '2011-01-17 17:00' AND categoryid=12;
Быть может, если бы на вашем сайте было 500 категорий, подобные запросы помещались в кэш, и результат возвращался бы миллисекунды. А что если будет 1000 постоянно просматриваемых категорий? Они будут вытеснять друг друга из кэша и выполняться гораздо медленнее. В данном случае может помочь увеличение размера кэша. Но выделение больше памяти под кэш может негативно сказаться при выполнении других задач, так что будьте внимательны. Можно найти множество советов о включении и повышении эффективности кэша путем изменения серверных переменных.
Когда кэширование бессильно
В случае изменений в таблице кэш становится бессильным. При вставке, обновлении, удалении строки из таблицы все запросы, относящиеся к этой таблице, удаляются из кэша. Так что если, таблица 'articles' обновляется каждый раз при просмотре какой-либо статьи (например, в этой таблице есть поле со счетчиком просмотров), то улучшения, описанные выше, могут и не помочь.
В таких случаях вам, возможно, захочется изучить какие-либо средства, реализующие кэш на уровне приложения, например, Memcached. Также можете прочитать следующий раздел для получения дополнительной информации, касающейся создания собственной системы кэширования. Оба этих варианта требуют гораздо больших программных изменений нежели мы обсуждали.
Собственный кэш
Если особенно тяжелые запросы к базе данных долго выполняются, и данные меняются не часто, можно самостоятельно кэшировать результаты.
Допустим, вы хотите показать 20 популярных записей на сайте за последнюю неделю, используя формулу, которая учитывает поисковые запросы, просмотры, добавление в «избранное», «Отправить другу». И вы хотите отображать этот перечень в виде маркированного списка на главной странице.
Самым простым способом будет, например, используя PHP, сделать запрос к базе данных с периодичностью один раз в час или в день и сохранять результаты в отдельный файл, который потом просто подключать на странице сайта.
Написав PHP-код, который генерирует файл со списком, можете использовать несколько методов для его запуска по расписанию. Можете использовать серверный планировщик (в Plesk 8: Server → Scheduled Tasks) для запуска этого скрипта ежечасно таким образом:
wget -O /dev/null -q http://www.mywebsite.co.uk/runhourly.php
Кроме того, вы можете использовать тот же PHP для проверки времени создания файла. Если файл был создан хотя бы час назад, то выполнять запрос. 3600 в данном случае это количество секунд в часе:
$filestat = stat ('includes/complicatedfile.html');
//look up information about the file
if ($filestat['mtime'] < time()-3600) RecreateComplicatedIncludeFile();
//over 1 hour
readfile ('includes/complicatedfile.html');
//include the file into the page
Возвращаясь к описанному выше примеру «Что еще покупают клиенты совместно с этим товаром...», вы также можете кэшировать данные в новом столбце или вообще таблице. Раз в неделю можно будет запускать большой набор сложных запросов для каждого товара, чтобы определить, какие товары покупаются совместно с ним.
Затем можно хранить результирующие идентификаторы товаров в новом столбце в виде множества элементов, разделенных запятыми. В дальнейшем, чтобы получить список товаров, которые приобретаются совместно с товаром с id = 12 нужно будет выполнить такой зарос:
SELECT * FROM products WHERE FIND_IN_SET(12,otherproductids);
Сокращаем количество запросов, используя JOIN
В каком-либо из разделов вашего сайта, возможно в административном разделе, выводится список пользователей с совершенными ими заказами.
При этом используется запрос наподобие приведенного ниже (для выборки по значению, означающему, что заказ выполнен):
SELECT * FROM orders WHERE status>1;
И для каждого заказа нужно еще найти клиента, который его оформил:
SELECT * FROM customers WHERE id=1;
SELECT * FROM customers WHERE id=2;
SELECT * FROM customers WHERE id=3;
etc
Если на странице выводится сразу информация о 100 заказах, нужно будет делать уже 101 запрос. А если нужно еще выводить информацию об адресе доставки из другой таблицы, общую стоимость всех заказов, скорость создания страницы будет падать, падать… Всё можно сделать гораздо быстрее, объединив запросы через JOIN. Далее пример объединения вышеописанных запросов:
SELECT * FROM orders INNER JOIN customers
ON orders.customerid = customers.id WHERE orders.status>=1;
Есть также и другой способ записать эти запросы, но без JOIN:
SELECT * FROM orders, customers
WHERE orders.customerid = customers.id AND orders.status>=1;
Перевод запросов на использование JOIN может вызвать сложности, т.к. придется менять и PHP-код. Но если на «медленной» странице выполняется тысячи запросов, то, может быть, стоит обратить внимание на описанный метод? За дополнительной информации можете обратиться к Википедии, в которой подробно рассказывается про JOIN. Столбцы, которые используются в JOIN (в примере — customerid), должны быть проиндексированы.
Вы также можете попросить MySQL "объяснить", как она выполняет ваш запрос. В результате вы увидите, как и какие таблицы используются при выполнении запроса, и можете что-то оптимизировать. На скриншоте ниже приведен результат выполнения одного из сложных запросов в WordPress с использованием EXPLAIN:
Используя EXPLAIN, узнаем, как MySQL «воспринимает» сложные запросы
На скриншоте видно, какие таблицы и индексы используются, тип JOIN, количество проанализированных строк и другая информация. На официальном сайте MySQL описывается, что именно объясняет EXPLAIN, и немного говорится о том, как использовать эту информацию для оптимизации запросов (например, добавлением индексов).
Схитрите
Наконец, возвращаясь опять к примеру про «Что еще покупают клиенты совместно с этим товаром...», вы можете его упростить. Переименуйте его в«Рекомендуемые товары», например, и выводите несколько других товаров их этой же категории или указанные вручную.
Заключение
В этой статье показано несколько методов для повышения производительности базы данных от простых до довольно-таки сложных. Хотя большинство грамотно разработанных сайтов уже должны включать подобные методы (использование JOIN'ов и индексов).
Ведётся много споров на счет эффективности и надежности использования некоторых из описанных методов (измерение скорости, индексирование, оптимизация, кэширование и т.д.), так что окончательное решение за вами. В любом случае, теперь у вас есть варианты, над которыми стоит подумать.
Если ваш сайт начал медленнее работать после нескольких месяцев или лет нормальной работы, у вас есть, от чего отталкиваться, чтобы найти причину.
