Ответ
При работе с большими таблицами ключевая задача — избежать перегрузки памяти и блокировок БД. Вот основные стратегии, которые я применяю:
1. Пагинация (для постраничного вывода пользователю):
// Используя LIMIT/OFFSET (подходит для таблиц до ~1M записей)
$page = (int) ($_GET['page'] ?? 1);
$perPage = 100;
$offset = ($page - 1) * $perPage;
$stmt = $pdo->prepare(
"SELECT id, name, created_at FROM large_table ORDER BY id LIMIT :limit OFFSET :offset"
);
$stmt->bindValue(':limit', $perPage, PDO::PARAM_INT);
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();2. Keyset Pagination (курсорная пагинация) — более эффективна для очень больших данных:
// Вместо OFFSET используем условие WHERE по последнему полученному ID
$lastId = (int) ($_GET['last_id'] ?? 0);
$sql = "SELECT id, name FROM large_table WHERE id > :last_id ORDER BY id LIMIT 1000";
$stmt = $pdo->prepare($sql);
$stmt->execute([':last_id' => $lastId]);3. Чанкование (Chunking) для фоновой обработки всех записей:
// В Laravel Eloquent
LargeModel::chunk(1000, function ($records) {
foreach ($records as $record) {
// Обработка каждой записи
}
});
// На чистом PDO с использованием небуферизованных запросов
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
$stmt = $pdo->query("SELECT * FROM large_table");
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
// Обработка строки по одной
}4. Выборка только необходимых колонок:
Всегда избегайте SELECT *. Явно перечисляйте нужные поля:
-- Вместо этого:
SELECT * FROM large_table;
-- Делайте так:
SELECT id, name, status FROM large_table;5. Использование индексов:
Перед написанием запроса анализируйте его с помощью EXPLAIN. Убедитесь, что условия в WHERE, ORDER BY и JOIN используют индексы.
6. Оптимизация для отчетов: Для сложных агрегирующих запросов по историческим данным часто эффективнее использовать материализованные представления (materialized views) или отдельную таблицу с предрасчитанными данными, которая обновляется по расписанию.
