Ускорение работы базы данных вашего веб-сайта

Однако основное внимание в обсуждении, как правило, уделяется минимизации размеров файлов, улучшению настроек сервера и оптимизации CSS и Javascript.

Обсуждение затушевывает еще один важный фактор: скорость, с которой ваши страницы на самом деле воедино на вашем сервере. Большинство крупных современных веб-сайтов хранят свою информацию в базе данных и используют такой язык, как PHP или ASP, чтобы извлечь ее, превратить ее в HTML и отправить в веб-браузер.

Дальнейшее чтение на SmashingMag:

• Расширенный WordPress управления с WP-CLI
• Почему статические генераторы веб-сайта являются следующая большая вещь
• WordPress Улучшения производительности, которые могут пойти не так
• Ускорьте свой мобильный веб-сайт с помощью лака

Таким образом, даже если вы получите вашу домашнюю страницу до 1,5 секунд (порог Google для того, чтобы считаться «быстрый» веб-сайт), вы все равно можете сорвать клиентов, если ваша страница поиска занимает слишком много времени, чтобы ответить, или если страницы продукта загружаются быстро, но «Отзывы клиентов» задержки в течение нескольких секунд.

Порог Google для быстрой загрузки веб-сайта составляет около 1,5 секунды. Этот скриншот исходит от Google Webmaster Tools (перейдите на «доменное имя» — Диагностика и Производительность сайта).

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

Что такое база данных? Что такое СЗЛ?

База данных — это в основном набор таблиц информации, таких как список клиентов и их заказов. Это может быть картотека, куча электронных таблиц, файл Microsoft Access или 40 терабайт книги и данных о клиентах Amazon.

Типичная база данных для блога имеет таблицы для пользователей, категорий, сообщений и комментариев. WordPress включает в себя эти и несколько других стартовых столов. Типичная база данных для веб-сайта электронной коммерции имеет таблицы для клиентов, продуктов, категорий, заказов и товаров заказа (для содержимого корзин ы). Программное обеспечение электронной коммерции с открытым исходным кодом Magento включает в себя эти и многие другие. Базы данных имеют много других применений — например, для управления контентом, отношений с клиентами, учетных записей и счетов- и событий, — но эти два распространенных типа (т.е. для блога и веб-сайта электронной коммерции) будут ссылаться на протяжении всей этой статьи.

Некоторые таблицы в базе данных подключены к другим таблицам. Например, в блоге может быть много комментариев, а клиент может сделать несколько заказов (это отношения от одного к многим). Наиболее сложным типом отношений с базами данных является многодоимка. Одна взаимосвязь лежит в основе всех баз данных электронной коммерции: заказ может содержать много продуктов, и один продукт может быть добавлен к различным заказам. Это где «заказ пунктов» таблица приходит в: он сидит между продуктами и заказами, и он записывает каждый раз, когда продукт добавляется к заказу. Это будет актуально позже в статье, когда мы посмотрим, почему некоторые запросы базы данных медленно.

База данных слов также относится к программному обеспечению, которое содержит все эти данные, как в «Моя база данных разбился, когда я завтракал», или «Мне действительно нужно обновить мою базу данных». Популярное программное обеспечение для баз данных включает в себя Microsoft Access 2010, Microsoft S’L Server, MyS’L, PostgreS’L и Oracle Database 11g.

Аббревиатуру S’L появляется много при работе с базами данных. Он означает «структурированный язык запросов» и произносится как «сиквел» или «es-cue-el». Это язык, используемый, чтобы спросить и рассказать базу данных вещи — захватывающие вещи, как SELECT lastname FROM customers WHERE city=‘Brighton’ . Это называется запросом базы данных, поскольку он запрашивает базу данных для данных. Есть и другие типы заявлений о базах данных: INSERT для ввода новых данных, UPDATE для обновления существующих данных, для DELETE удаляния вещей, CREATE TABLE для создания таблиц и многое ALTER TABLE другое.

Как база данных может замедлить работу веб-сайта?

Совершенно новый пустой веб-сайт будет работать очень быстро, но, как он растет и возрастов, вы можете заметить некоторую вялость на определенных страницах, особенно страниц со сложными бит функциональности. Предположим, вы хотели показать «Клиенты, которые купили этот продукт также купил …» в нижней части страницы продуктов. Чтобы извлечь эту информацию из базы данных, необходимо сделать следующее:

1. Начните с текущего продукта,
2. Узнайте, сколько раз продукт недавно был добавлен в корзину для покупок (таблица «заказ товаров» сверху),
3. Посмотрите на заказы, связанные с этими корзинами (только для выполненных заказов),
4. Найти клиентов, которые сделали эти заказы,
5. Посмотрите на другие заказы, сделанные этими клиентами,
6. Посмотрите на содержимое корзин этих заказов (снова «заказать товары»),
7. Посмотрите детали этих продуктов,
8. Определите продукты, которые появляются чаще всего, и отобразите их.

Можно, по сути, сделать все это в одном массивном запросе базы данных, или вы можете разделить его на несколько различных запросов. В любом случае, он может работать очень быстро, когда ваша база данных имеет 20 продуктов, 12 клиентов, 18 заказов и 67 пунктов заказа (т.е. элементы в корзинах покупок). Но если он не написан и не запрограммирован эффективно, то это будет намного медленнее с 500 продуктов, 10000 клиентов, 14000 заказов и 100000 пунктов заказа, и это замедлит страницу.

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

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

Это моя база данных?

Есть в настоящее время несколько способов, чтобы проанализировать скорость вашего сайта, в том числе Плагин Firebug для Firefox, разработчик инструментов в Google Chrome (пресс Shift — Контроль i, а затем перейти к ресурсам и включить отслеживание ресурсов) и Yahoo YSlow. Есть также веб-сайты, такие как WebPagetest, где вы можете ввести URL, и это будет время его из выбранного местоположения.

Все эти инструменты покажут вам диаграмму всех различных ресурсов (HTML, изображения, CSS и JavaScript файлы), используемые вашей страницей, а также сколько времени каждый взял для загрузки. Они также будут ломать время, затрачиваемое на выполнение DNS поиска (т.е. для преобразования доменного имени в IP-адрес), время, затраченное на подключение к серверу, время, затраченное на ожидание вашего сервера, чтобы ответить (ака «время для первого байт»), и время, затраченное на получение (т.е. делать wnloading) данные.

Многие веб-страницы строятся в полном объеме веб-сервером, в том числе PHP, который получает доступ к базе данных, а затем отправляется в браузер сразу, так что любые задержки базы данных приведут к длительному времени ожидания, а время получения/загрузки будет пропорциональным к объему отправляемых данных. Таким образом, если ваша страница HTML 20 кБ имеет быстрое подключение, время ожидания 5 секунд и время загрузки 0,05 секунды, то задержка будет происходить на сервере, так как страница строится.

Однако не все веб-страницы подобны этому. Функция PHP flush заставляет сервер отправлять HTML, который он уже построил, в браузер сразу. Любые дальнейшие задержки будут в момент получения, а не время ожидания.

В любом случае, вы можете сравнить время ожидания/получения вашей предполагаемой медленной и сложной веб-страницы с временем ожидания для аналогичного размера HTML-страницы (или изображения или другого статического ресурса) на том же сервере в то же время. Это исключило бы возможность медленного подключения к Интернету или перегруженного сервера (оба из которых могут привести к задержкам) и позволило бы сравнить время, заданное для построения страниц. Это не точная наука, но она должна дать вам некоторое представление о том, где вещи в настоящее время сдерживается.

На скриншотах ниже показан анализ, присваиваемый Google Chrome Developer Tools веб-страницы 20 кБ против изображения 20 кБ. Веб-страница ждала 130 миллисекунд (мс) и загружена на 22 ms. Изображение ждали 51 мс и загружены на 11 ms. Время загрузки / получения примерно то же самое, как ожидалось, но сервер тратит около 80 мс дополнительно на обработку и строительство веб-страницы , что влечет за собой выполнение PHP и вызов базы данных.

При выполнении этих тестов, анализировать статический ресурс сам по себе и нажмите кнопку «Обновить», так что вы не получаете быстрый кэшированный вариант. Кроме того, запустить каждый несколько раз, чтобы убедиться, что вы не смотрите на статистическую аномалию. Третий скриншот ниже показывает, что WebPagetest указывает почти в два раза время Google для той же страницы в то же время, демонстрируя, что использование той же среды для всех тестов имеет важное значение.

Анализ ресурсов с помощью инструментов разработчика Google Chrome, показывающий время ожидания веб-страницы в 130 мс.

Тот же инструмент, показывающий время ожидания 51 мс для изображения примерно такого же размера.

Анализ ресурсов той же страницы из WebPagetest, с 296-мс время ожидания и 417-мс общее время.

Как приурочить запрос базы данных в PHP и MyS’l

Подход, приведенный выше, был общим; теперь мы можем получить очень конкретные. Если вы подозреваете, что ваша база данных может замедлять ваш сайт, то вам нужно выяснить, где задержка исходит от. Я определю несколько функций синхронизации, а затем использую их для того, чтобы каждый запрос базы данных, который выполняется страницей. Приведенный ниже код специфичен для PHP и MyS’L, но метод может быть использован на любом веб-сайте, управляемом базой данных:

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, но запрос базы данных только в нескольких местах. Поиск через все файлы PHP для вашего сайта mysql_db_query для или mysql_query . Многие пакеты разработки программного обеспечения, такие как BBEdit имеют функции для выполнения поисков, как это; или, если вы знакомы с командной строкой Linux, попробуйте это:grep mysql_query find . -name *php

Вы можете найти что-то вроде этого:

mysql_query ($sql);

Для WordPress 3.0.4, это на линии 1112 файла wp-включает/wp-db.php. Вы можете скопировать и вставить вышеперечисленные функции в верхнюю часть этого файла (или в любой файл PHP, который включен на каждой странице), а затем добавить таймер до и после mysql_query строки. Он будет выглядеть следующим образом:

StartTimer ($query);
$this->result = @mysql_query( $query, $dbh );
StopTimer();

Ниже частично скриншот этого делается на совершенно новой установки WordPress. Он работает около 15 запросов базы данных в общей сложности, каждый из которых занимает около 0,0003 секунд (0,3 мс); таким образом, менее 5 мс в общей сложности, что следует ожидать для пустой базы данных.

Это показывает и времена все запросы базы данных, что WordPress работает.

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

Вы также можете сделать другие интересные вещи с ним: вы можете увидеть, как быстро ваш компьютер по сравнению с моим. Подсчет до 10 миллионов занимает мой компьютер 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, как «Не может изменить информацию заголовок. Заголовки, уже отправленные…» Это связано с тем, что сообщения о времени мешают файлам cookie и заголовкам сеансов. До тех пор, пока страница по-прежнему отображается ниже предупреждений, вы можете игнорировать их. Если страница не отображается вообще, то вам может понадобиться поставить StartTimer и StopTimer вокруг конкретных блоков кода, а не вокруг mysql_query .

Этот метод по существу быстрый хак, чтобы показать некоторые грубые результаты. Она не должна быть оставлена на веб-сайте в прямом эфире.

Что еще это может быть?

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

Профилирования

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

Таблицы индексирования баз данных

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

Давайте посмотрим сейчас на некоторые простые улучшения, чтобы ускорить вещи. Для этого вам понадобится способ выполнения запросов базы данных в базе данных. Многие пакеты администрирования серверов (например, cPanel или Plesk) предоставляют phpMyAdmin для этой задачи. Кроме того, вы можете загрузить что-то вроде phpMiniAdmin на ваш сайт; этот единственный файл PHP позволяет заглянуть в базу данных и выполнить запросы. Вам нужно будет ввести имя базы данных, имя пользователя и пароль. Если вы не знаете, вы обычно можете найти их в конфигурации файла вашего сайта, если он имеет один (в WordPress, это wp-config.php).

Среди запросов базы данных, которые выполняет ваша страница, вы, вероятно, видели несколько WHERE условий. Это способ отфильтровывания результатов. Например, если вы просматриваете страницу «История счета» на вашем сайте, вероятно, есть такой запрос, чтобы найти все заказы, которые кто-то разместил. Что-то вроде этого:

SELECT * FROM orders WHERE customerid = 2;

При этом извлекается все заказы, размещенные клиентом с идентификатором базы данных 2. На моем компьютере, с 100000 заказов в базе данных, работает это заняло 0,2158 секунд.

Колонки, как customerid — которые имеют дело с большим количеством WHERE условий или и имеют = много < > возможных значений, должны быть проиндексированы. Это как индекс в задней части книги: он помогает базе данных быстро получить индексированные данные. Это один из самых быстрых способов ускорения запросов базы данных.

Что индексировать

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

SELECT * FROM categories WHERE name = 'Books';
SELECT * FROM events WHERE startdate >= '2011-02-07';

Каждая из таблиц базы данных уже должна иметь идентификатор столбец (часто id называется, но иногда или или ID articleid как), который указан как , PRIMARY KEY как на wp_posts скриншоте ниже. Эти PRIMARY KEY с автоматически индексируются. Но следует также индексировать любые столбцы, отсылающие к идентификационным номерам в других таблицах, например customerid в приведенном выше примере. Иногда их называют FOREIGN KEY s.

SELECT * FROM orders WHERE customerid = 2;
SELECT * FROM orderitems WHERE orderid = 231;

Если много текстовых поисков делается, возможно, для описания продуктов или содержания статьи, то вы можете добавить другой тип индекса называется полный индекс TEXT. Запросы с использованием FULL TEXT индекса могут быть выполнены в течение нескольких столбцов и первоначально настроены для работы только со словами из четырех или более букв. Они также исключают некоторые общие слова, как о и слова, которые появляются в более чем 50% строк поиск. Однако для использования этого типа индекса необходимо изменить свои запросы на S-L. Вот типичный поиск текста, первый без и второй с FULL TEXT индексом:

SELECT * FROM products WHERE name LIKE '%shoe%' OR description LIKE '%shoe%';
SELECT * FROM products WHERE MATCH(name,description) AGAINST ('shoe');

Может показаться, что вы должны идти вперед и индексировать все. Однако, в то время как индексация ускоряется SELECT с, она замедляет INSERT s, UPDATE s и DELETE s. Таким образом, если у вас есть таблица продуктов, которые вряд ли когда-либо изменения, вы можете быть более либеральным с индексацией. Но ваши заказы и заказы таблицы, вероятно, постоянно изменяется, так что вы должны быть более щадящие с ними.

Есть также случаи, когда индексация не может помочь; например, если большинство записей в столбце имеют одинаковое значение. Если у вас есть stock_status колонка, которая хранит значение 1 для «на складе», и 95% вашей продукции на складе, то индекс не поможет кому-то искать в наличии продуктов. Представьте себе, если слово было проиндексировано в задней части справочника: индекс будет перечислять почти каждую страницу в книге.

SELECT * FROM products WHERE stock_status = 1;

Как индексировать

Используя phpMyAdmin или phpMiniAdmin, вы можете посмотреть на структуру каждой таблицы баз данных и посмотреть, уже ли индексируются соответствующие столбцы. В phpMyAdmin щелкните название таблицы и просмотрите внизу, где она перечисляет «Индексы». В phpMiniAdmin щелкните «Показать столы» в верхней части, а затем «sct» для таблицы в вопросе; это покажет запрос базы данных, необходимый для воссоздания таблицы, которая будет включать любые индексы внизу — что-то вроде KEY ‘orderidindex’ (‘orderid’) .

Использование phpMiniAdmin для проверки индексов в wp_posts таблице WordPress.

Если индекса не существует, его можно добавить. В phpMyAdmin, ниже индекса, он говорит: «Создать индекс на 1 столбцов»; нажмите «Go» здесь, введите полезное имя для индекса customeridindex (например), выберите столбец на следующей странице, и нажмите «Сохранить», как видно на этом скриншоте:

Индексирование столбца с помощью phpMyAdmin.

В phpMiniAdmin вам придется запускать следующее заявление базы данных непосредственно в большом поле запроса S’L в верхней части:

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 является одним файлом, который очень легко загрузить и забыть о. Таким образом, вы можете защитить его паролем или удалить его после использования.

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

MyS’L и другие виды программного обеспечения для баз данных имеют встроенные инструменты для оптимизации своих данных. Если ваши таблицы сильно изменяются, то вы можете регулярно запускать инструменты, чтобы сделать таблицы баз данных меньше и эффективнее. Но для запуска у них уходит некоторое время (от нескольких секунд до нескольких минут или более, в зависимости от размера таблиц), и они могут блокировать запуск других запросов на столе во время оптимизации, поэтому делать это в незагруженное время лучше всего. Там также некоторые дебаты о том, как часто оптимизировать, с мнениями, начиная от никогда раз в то время как в неделю.

Чтобы оптимизировать таблицу, запустите заявления базы данных, такие как следующие в phpMyAdmin или phpMiniAdmin:

OPTIMIZE TABLE orders;

Например, прежде чем оптимизировать таблицу заказов с 100 000 заказов, он был 31,2 МБ в размере и взял 0,2676 секунд для запуска SELECT * FROM orders . После первой в истории оптимизации он сократился до 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
}

Прежде чем вызвать эту функцию, необходимо подключиться к базе данных. Большинство современных веб-сайтов будет подключиться для вас, так что вам не нужно беспокоиться об этом, но соответствующие звонки MyS’L показаны здесь ради полноты:

mysql_connect (DB_HOST, DB_USER, DB_PASSWORD);
mysql_select_db (DB_NAME);
OptimizeAllTables();

Убедившись в использовании кэша

Подобно тайче веб-браузера копии страниц, которые вы посещаете, база данных программного обеспечения кэширует популярные запросы. Как указано выше, ниже приведенный ниже запрос занял 0,0019 секунды, когда я запустил его в первый раз с индексом:

SELECT * FROM orders WHERE customerid=2;

Запуск того же запроса сразу же занимает всего 0,0004 секунды. Это потому, что MyS’L вспомнил результаты и может вернуть их во второй раз, не глядя их снова.

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

SELECT * FROM posts WHERE publisheddate <= CURDATE();
SELECT * FROM articles WHERE publisheddate <= NOW();

Эти запросы не могут быть кэшированы, поскольку они зависят от текущего времени или даты. В таблице с 100 000 строк запрос, подобный выше, займет около 0,38 секунды каждый раз, когда я запускаю его против неиндексированной колонки на моем компьютере.

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

SELECT * FROM articles WHERE publisheddate <= '2011-01-17 17:00';

Вы можете использовать PHP, чтобы убедиться, что время меняется каждые пять минут или около того:

$time = time();
$currenttime = date ('Y-m-d H:i', $time - ($time % 300));
mysql_query (“SELECT * FROM articles WHERE publisheddate <= '$currenttime'”);

Процентный знак является оператором модуля. % 300округляет время до последних 300 секунд или 5 минут.

Есть и другие некужестомые функции MyS’L,также, как RAND.

Перерастание кэша

Перерастание кэша MyS’L также может затруднить работу вашего веб-сайта. Чем больше сообщений, страниц, категорий, продуктов, статей и так далее, что у вас есть на вашем сайте, тем больше связанных запросов будет. Взгляните на этот пример:

SELECT * FROM articles WHERE publisheddate <= '2011-01-17 17:00' AND categoryid=12

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

Когда кэширование не помогает

Кэш аннулируется при изменении таблицы. Когда строка вставляется, обновляется или удаляется, все запросы, опирающиеся на эту таблицу, эффективно очищаются от кэша. Таким образом, если таблица статей обновляется каждый раз, когда кто-то просматривает статью (возможно, для подсчета количества представлений), то улучшение, предложенное выше, может не очень помочь.

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

Создание собственного кэша

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

Допустим, вы хотите показать 20 самых популярных статей на вашем сайте на прошлой неделе, используя расширенную формулу, которая учитывает поиск, просмотры, сохраняет и «Отправить другу» хитов. И вы хотите, чтобы показать их на домашней странице в неупорядоченном ( <ul> ) HTML список.

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

После того как вы написали PHP для создания файла включить, вы можете принять один из нескольких подходов к планированию его. Планировщик сервера (в Plesk 8, перейдите на Сервер и запланированные задачи) для вызова страницы PHP каждый час с такой командой:

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 . Вот как 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 s может быть сложной, поскольку она включает в себя изменение сопроводительного кода PHP. Но если ваша медленная страница выполняет тысячи заявлений базы данных, то, возможно, стоит посмотреть. Для получения дополнительной информации, Википедия предлагает хорошее объяснение JOINs. Столбцы, с которыми вы используете JOIN customerid (в данном случае) также являются основными кандидатами для INDEX ed.

Вы также можете попросить MyS’L сделать запрос базы данных EXPLAIN. Это говорит вам, какие таблицы он будет использовать и предоставляет «план выполнения». Ниже приводится скриншот, показывающий EXPLAIN заявление, используемое на одном из более сложных запросов WordPress сверху:

Используя EXPLAIN заявление, чтобы объяснить, как MyS’L планирует иметь дело со сложным запросом.

На скриншоте показано, какие таблицы и индексы используются, JOIN типы, количество проанализированных строк и намного больше информации. Всеобъемлющая страница на веб-сайте MyS’L объясняет, что объясняет EXPLAIN,и другая страница гораздо короче переходит, как использовать эту информацию для оптимизации запросов (например, путем добавления индексов).

… Или просто обмануть

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

Заключение

В этой статье показан ряд методов повышения производительности базы данных, начиная от простых и довольно сложных. Хотя все хорошо сложенные веб-сайты уже должны включать большинство из этих методов (в частности, индексы базы данных и JOIN s), методы действительно упускать из виду.

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

Если ваш сайт начинает таинственно замедлить через несколько месяцев или лет, вы, по крайней мере, отправной точкой для выяснения того, что случилось.