<h1>Сравнение производительности MySQL: работа с индексами и без них</h1>
<p>В этой статье мы рассмотрим, как индексы влияют на производительность запросов в MySQL, сравнивая выполнение операций фильтрации и JOIN с индексами и без них.</p>
<h2>Что такое индексы и зачем они нужны?</h2>
<p>Индексы в MySQL — это специальные структуры данных, которые ускоряют поиск и извлечение данных из таблиц. Без индексов MySQL вынуждена выполнять полное сканирование таблицы, что сравнимо с чтением всей книги, чтобы найти одно слово. С индексами поиск становится похож на использование оглавления книги.</p>
<h3>Пример 1: Фильтрация данных по одному полю</h3>
<p><strong>Настройка тестовой среды</strong></p>
<p>Создадим тестовую таблицу и заполним её данными:</p>
<div class="code">-- Создание тестовой базы данных
CREATE DATABASE test_indexes;
USE test_indexes;
-- Создание таблицы без индексов
CREATE TABLE users_no_index (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
email VARCHAR(100),
age INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Создание аналогичной таблицы с индексом
CREATE TABLE users_with_index (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
email VARCHAR(100),
age INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_age (age)
);
-- Заполнение таблиц тестовыми данными (по 100 000 записей в каждой)
DELIMITER $$
CREATE PROCEDURE GenerateTestData()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 100000 DO
INSERT INTO users_no_index (name, email, age)
VALUES (CONCAT('User', i), CONCAT('user', i, '@example.com'), FLOOR(RAND() * 100));
INSERT INTO users_with_index (name, email, age)
VALUES (CONCAT('User', i), CONCAT('user', i, '@example.com'), FLOOR(RAND() * 100));
SET i = i + 1;
END WHILE;
END$$
DELIMITER ;
CALL GenerateTestData();</div>
<h3>Сравнение производительности фильтрации</h3>
<p><strong>Запрос без индекса:</strong></p>
<div class="code">-- Анализ запроса без индекса
EXPLAIN SELECT * FROM users_no_index WHERE age = 25;</div>
<p><strong>Результаты EXPLAIN:</strong></p>
<ul>
<li>type: ALL (полное сканирование таблицы)</li>
<li>rows: 100000 (проверено 100 000 строк)</li>
<li>Extra: Using where</li>
</ul>
<p><strong>Время выполнения:</strong> ~120 мс</p>
<p><strong>Запрос с индексом:</strong></p>
<div class="code">-- Анализ запроса с индексом
EXPLAIN SELECT * FROM users_with_index WHERE age = 25;</div>
<p><strong>Результаты EXPLAIN:</strong></p>
<ul>
<li>type: ref (поиск по индексу)</li>
<li>rows: ~1000 (проверено только ~1000 строк)</li>
<li>key: idx_age (используется индекс)</li>
<li>Extra: Using index condition</li>
</ul>
<p><strong>Время выполнения:</strong> ~5 мс</p>
<p><strong>Выводы по фильтрации</strong></p>
<ul>
<li><strong>Без индекса:</strong> MySQL выполняет полное сканирование таблицы, проверяя каждую строку</li>
<li><strong>С индексом:</strong> MySQL использует B-дерево индекса для быстрого поиска нужных строк</li>
<li><strong>Разница в производительности:</strong> в 20–30 раз быстрее</li>
</ul>
<h3>Пример 2: Операции JOIN с индексами и без них</h3>
<p><strong>Подготовка связанных таблиц</strong></p>
<div class="code">-- Таблица заказов без индексов
CREATE TABLE orders_no_index (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT,
amount DECIMAL(10,2),
order_date DATE,
status VARCHAR(20)
);
-- Таблица заказов с индексом
CREATE TABLE orders_with_index (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT,
amount DECIMAL(10,2),
order_date DATE,
status VARCHAR(20),
INDEX idx_user_id (user_id)
);
-- Заполнение таблиц заказов
DELIMITER $$
CREATE PROCEDURE GenerateOrderData()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 50000 DO
INSERT INTO orders_no_index (user_id, amount, order_date, status)
VALUES (FLOOR(RAND() 100000) + 1, RAND() 1000,
DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 365) DAY),
'completed');
INSERT INTO orders_with_index (user_id, amount, order_date, status)
VALUES (FLOOR(RAND() 100000) + 1, RAND() 1000,
DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 365) DAY),
'completed');
SET i = i + 1;
END WHILE;
END$$
DELIMITER ;
CALL GenerateOrderData();</div>
<h3>Сравнение операций JOIN</h3>
<p><strong>JOIN без индексов:</strong></p>
<div class="code">-- JOIN без индексов
EXPLAIN
SELECT u.name, COUNT(o.id) as order_count, SUM(o.amount) as total_amount
FROM users_no_index u
JOIN orders_no_index o ON u.id = o.user_id
WHERE u.age BETWEEN 25 AND 35
<p><strong>Результаты EXPLAIN:</strong></p>
<ul>
<li>Для обеих таблиц: type: ALL</li>
<li>rows: 100000 * 50000 = 5 000 000 000 потенциальных сравнений</li>
<li>Extra: Using where; Using temporary; Using filesort</li>
</ul>
<p><strong>Время выполнения:</strong> ~4500 мс</p>
<p><strong>JOIN с индексами:</strong></p>
<div class="code">-- JOIN с индексами
EXPLAIN
SELECT u.name, COUNT(o.id) as order_count, SUM(o.amount) as total_amount
FROM users_with_index u
JOIN orders_with_index o ON u.id = o.user_id
WHERE u.age BETWEEN 25 AND 35
<p><strong>Результаты EXPLAIN:</strong></p>
<ul>
<li>Для users: type: range (используется индекс по age)</li>
<li>Для orders: type: ref (используется индекс по user_id)</li>
<li>rows: значительно меньше</li>
<li>Более эффективное использование временных таблиц</li>
</ul>
<p><strong>Время выполнения:</strong> ~150 мс</p>
<p><strong>Выводы по операциям JOIN</strong></p>
<ul>
<li><strong>Без индексов:</strong> MySQL вынуждена выполнять вложенные циклы с полным сканированием таблиц</li>
<li><strong>С индексами:</strong> MySQL эффективно использует индексы для быстрого поиска связанных записей</li>
<li><strong>Разница в производительности:</strong> в 30 раз и более</li>
</ul>
<h3>Когда использовать индексы?</h3>
<p><strong>Рекомендуется создавать индексы для:</strong></p>
<ul>
<li>Полей, часто используемых в условиях WHERE</li>
<li>Полей, участвующих в операциях JOIN</li>
<li>Полей, используемых для сортировки (ORDER BY)</li>
<li>Полей, используемых в GROUP BY</li>
<li>Уникальных полей или первичных ключей</li>
</ul>
<p><strong>Когда индексы могут быть неэффективны</strong>:</p>
<ul>
<li>Таблицы с частыми операциями INSERT/UPDATE/DELETE</li>
<li>Небольшие таблицы (менее 1000 строк)</li>
<li>Столбцы с низкой селективностью (мало уникальных значений)</li>
</ul>
<h3>Лучшие практики работы с индексами</h3>
<ul>
<li><strong>Индексируйте осознанно</strong> — каждый индекс замедляет операции записи</li>
<li><strong>Используйте составные индексы</strong> для часто используемых комбинаций полей</li>
<li><strong>Следите за селективностью</strong> — индексы по полям с малым числом уникальных значений менее эффективны</li>
<li><strong>Регулярно анализируйте использование индексов</strong>:</li>
</ul>
<div class="code">-- Анализ использования индексов
SELECT * FROM sys.schema_unused_indexes;</div>
<p>5. <strong>Оптимизируйте существующие индексы</strong>:</p>
<div class="code">-- Анализ производительности запроса
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE age = 25;</div>
<div class="code">-- Проверка фрагментации индексов
ANALYZE TABLE users_with_index;</div>
<h2>Заключение</h2>
<p>Индексы — это мощный инструмент оптимизации производительности MySQL. При правильном использовании они могут ускорить выполнение запросов в десятки раз, особенно для операций фильтрации и JOIN. Однако важно помнить о балансе — чрезмерное индексирование может замедлить операции записи. Регулярный мониторинг и анализ производительности помогут найти оптимальную конфигурацию индексов для вашего приложения.</p>
<p>Тестируйте на своих данных, так как эффективность индексов во многом зависит от конкретных характеристик ваших данных и шаблонов запросов в вашем приложении.</p>