Оптимизация работы с базой данных MySQL

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

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

SQL запросы в цикле

Очень часто можно встретить код на подобие следующего:

foreach ($items as $item) {
	$item['image'] = loadImage($item['id']);
	$item['comments'] = loadComments($item['id']);
	$item['something_else'] = loadSomethingElse($item['id']);
	...
}

Мне встречались случаи, когда подобный код генерировал 500-1000 SQL запросов к базе данных при каждой загрузке страницы! В таком случае, можно реализовать методы, которые будут извлекать данных сразу для всего массива $items, и не отдельно для каждого элемента.

Выборка всех полей из таблицы, когда этого не требуется

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

$stmt = $pdo->query('SELECT * FROM items WHERE active=1');
while ($row = $stmt->fetch()) {
	$id = $row['id'];
	...
}

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

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

$stmt = $pdo->query('SELECT * FROM items WHERE active=1');
$rowsCount = count($stmt->fetchAll());

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

$stmt = $pdo->query('SELECT COUNT(*) FROM items WHERE active=1');
$rowsCount = $stmt->fetchColumn();

Использование конструкции LIMIT .. OFFSET для пагинации

Данный способ является стандартным для постраничной выборки записей из таблицы. Многие считают, что т.к. LIMIT обычно небольшой (10-30 записей), то это должно работать быстро. Пока общее количество строк в таблице невелико, это действительно так. Как только количество записей увеличивается до десятков и сотен тысяч, OFFSET может работать очень медленно при больших значениях OFFSET. Происходит это из-за того, что базе данных необходимо прочитать все строки до заданного OFFSET, чтобы извлечь нужные строки. Таким образом, если в запросе указано: LIMIT 10 OFFSET 100000, база данных прочитает 100010 строк, а не 10, как может показаться на первый взгляд. Простых решений этой проблемы не существует, возможно я разберу примеры таких решений в одной из следующих статей.

ORDER BY RAND()

Данный вид сортировки работает очень медленно. Его не следует применять для таблиц, которые содержат больше нескольких десятков или сотен строк. Для выполнения такой сортировки базе данных требуется прочитать все записи, которые удовлетворяют условию WHERE, и только затем будет выбрано количество строк, заданных условием LIMIT. Представим, что вам нужно получить 10 случайных записей из таблицы со 100000 записей, и вы пишете следующий запрос:

SELECT * FROM items ORDER BY RAND() LIMIT 10

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

Заключение

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