Оптимизация MySQL: переход с MyISAM на InnoDB и кэширование через Memcached

В начале 2011 года у нас упал продакшен-сервер под нагрузкой. Интернет-магазин на PHP 5.3 и MySQL 5.1 работал нормально при 50 одновременных пользователях, но при 200+ сервер переставал отвечать. SHOW PROCESSLIST показывал десятки запросов в состоянии Waiting for table lock.

Причина оказалась в MyISAM.

---

Почему MyISAM виновен

MyISAM - движок по умолчанию в MySQL 5.1 - использует блокировку на уровне таблицы. Это значит: пока идёт один UPDATE, все SELECT на эту таблицу ждут.

-- Такой запрос блокировал всю таблицу orders на 2-5 секунд
UPDATE orders SET status = 'processing' WHERE id = 12345;

-- Все эти запросы висели в очереди:
SELECT * FROM orders WHERE user_id = 678;
SELECT COUNT(*) FROM orders WHERE status = 'new';

При 200 пользователях очередь блокировок росла быстрее, чем разбиралась.

InnoDB использует блокировку на уровне строки. UPDATE одной записи не мешает SELECT других записей той же таблицы.

---

Миграция на InnoDB

-- Проверить текущие движки таблиц
SELECT table_name, engine 
FROM information_schema.tables 
WHERE table_schema = 'myshop';

-- Конвертировать таблицу (занимает время на больших таблицах - делать в ночное время)
ALTER TABLE orders ENGINE = InnoDB;
ALTER TABLE products ENGINE = InnoDB;
ALTER TABLE users ENGINE = InnoDB;

-- Проверить результат
SHOW TABLE STATUS FROM myshop;

Конвертация таблицы на 2 миллиона строк заняла около 8 минут. Делали поочерёдно, без остановки сервиса - MyISAM и InnoDB таблицы спокойно сосуществуют в одной базе.

---

Настройка InnoDB Buffer Pool

Самая важная настройка InnoDB - innodb_buffer_pool_size. Это объём RAM, который InnoDB использует для кэширования данных и индексов. Чем больше - тем меньше обращений к диску.

# /etc/mysql/my.cnf

[mysqld]
# На сервере с 4GB RAM - выделяем 70-80% под буферный пул
innodb_buffer_pool_size = 3G

# Включить несколько пулов (уменьшает конкуренцию за мьютексы при высокой нагрузке)
innodb_buffer_pool_instances = 4

# Размер лога транзакций - увеличить для write-heavy нагрузки
innodb_log_file_size = 256M

# Синхронизация на диск: 2 = быстрее, но теряем ~1 секунду данных при краше
# 1 = безопасно, но медленнее. Для магазина выбрали 1.
innodb_flush_log_at_trx_commit = 1

После перезапуска MySQL и прогрева кэша (несколько минут под нагрузкой) количество обращений к диску упало на 80%.

---

EXPLAIN: находим медленные запросы

Включили slow query log для запросов дольше 1 секунды:

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

Нашли виновника - запрос без индекса:

-- Этот запрос делал полный скан 2M строк
SELECT * FROM orders 
WHERE status = 'new' AND created_at > '2011-01-01'
ORDER BY created_at DESC;

-- EXPLAIN показал: type = ALL, rows = 1987432
EXPLAIN SELECT * FROM orders 
WHERE status = 'new' AND created_at > '2011-01-01';

Добавили составной индекс:

-- Составной индекс: сначала по status (низкая кардинальность), потом по дате
CREATE INDEX idx_status_created ON orders (status, created_at);

-- После индекса: type = range, rows = 847 (сканируем только нужные строки)

Время запроса: с 4.2 секунды до 12 миллисекунд.

---

Memcached: кэшируем тяжёлые запросы

Некоторые данные меняются редко, но запрашиваются часто. Список категорий, топ товаров, настройки магазина - идеальные кандидаты для кэша.

# Установка на Ubuntu
apt-get install memcached php5-memcache

Паттерн "cache-aside" на PHP 5.3:

<?php

class Cache {
    private $memcache;
    
    public function __construct() {
        $this->memcache = new Memcache();
        $this->memcache->connect('localhost', 11211);
    }
    
    public function get($key) {
        return $this->memcache->get($key);
    }
    
    public function set($key, $value, $ttl = 300) {
        $this->memcache->set($key, $value, 0, $ttl);
    }
    
    public function delete($key) {
        $this->memcache->delete($key);
    }
}

// Использование
function getCategories(Cache $cache, PDO $db) {
    $cacheKey = 'categories:all';
    
    // Пробуем кэш
    $categories = $cache->get($cacheKey);
    if ($categories !== false) {
        return $categories;  // Возвращаем из кэша
    }
    
    // Промах - идём в базу
    $stmt = $db->query('SELECT * FROM categories WHERE active = 1 ORDER BY sort_order');
    $categories = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Сохраняем в кэш на 10 минут
    $cache->set($cacheKey, $categories, 600);
    
    return $categories;
}

// При изменении категории - инвалидируем кэш
function updateCategory(Cache $cache, PDO $db, $id, $data) {
    $stmt = $db->prepare('UPDATE categories SET name = ? WHERE id = ?');
    $stmt->execute([$data['name'], $id]);
    
    // Сбрасываем кэш
    $cache->delete('categories:all');
}

---

Результат

Метрика	До	После
Запросы в состоянии Waiting for lock	30-50	0-2
Среднее время ответа страницы	1.8 сек	0.4 сек
Максимальная нагрузка без деградации	200 RPS	800 RPS
Обращений к MySQL	100%	~20% (остальное из Memcached)

Сервер перестал падать. При этом железо не меняли - только настройки и архитектура.

---

Что мы вынесли

InnoDB - это не просто замена MyISAM. Это другая парадигма: транзакции, внешние ключи, row-level locking. Если ваш MySQL ещё на MyISAM - миграция обязательна.

Memcached решает проблему повторяющихся тяжёлых запросов. Но кэш - это не замена оптимизации запросов. Сначала индексы и EXPLAIN, потом кэш.

В 2013 году MySQL 5.6 принёс значительные улучшения в InnoDB. В 2015-м появился Percona Server с лучшей статистикой. Сегодня PostgreSQL занял нишу для сложных аналитических запросов. Но базовые принципы - индексы, правильный движок, кэширование - не изменились.