Один из видов оптимизации, который мы часто используем, это расширение индекса. Он подходит, если есть запросы, использующие другие части составного ключа. И обычно это безопасно, но есть некоторые случаи, где производительность катастрофически падает.
Рассмотрим пример.
У нас есть таблица
CREATE TABLE `idxitest` ( `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, `a` int(11) NOT NULL, `b` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `a` (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=6029313 DEFAULT CHARSET=latin1 mysql> SELECT count(*) FROM idxitest WHERE a=5 AND b=5; +----------+ | count(*) | +----------+ | 60434 | +----------+ 1 row IN SET (0.69 sec) mysql> EXPLAIN SELECT count(*) FROM idxitest WHERE a=5 AND b=5; +----+-------------+----------+------+---------------+------+---------+-------+--------+-------------+ | id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+-------+--------+-------------+ | 1 | SIMPLE | idxitest | ref | a | a | 4 | const | 707820 | USING WHERE | +----+-------------+----------+------+---------------+------+---------+-------+--------+-------------+ 1 row IN SET (0.00 sec)
Самое очевидное решение — это расширить вправо ключ с колонки «a» на две колонки (a, b). Вроде бы это не должно никак отрицательно повлиять на другие запросы.
mysql> ALTER TABLE idxitest DROP KEY a,ADD KEY(a,b); Query OK, 0 rows affected (24.84 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SELECT count(*) FROM idxitest WHERE a=5 AND b=5; +----------+ | count(*) | +----------+ | 60434 | +----------+ 1 row IN SET (0.02 sec) mysql> EXPLAIN SELECT count(*) FROM idxitest WHERE a=5 AND b=5; +----+-------------+----------+------+---------------+------+---------+-------------+--------+-------------+ | id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+-------------+--------+-------------+ | 1 | SIMPLE | idxitest | ref | a | a | 8 | const,const | 120640 | USING INDEX | +----+-------------+----------+------+---------------+------+---------+-------------+--------+-------------+ 1 row IN SET (0.00 sec)
Круто. Запрос стал быстрее в 30 раз. Во-первых, он сканирует меньше строк, а во-вторый, использует покрывающий индекс, то есть все нужные данные извлекает прямо из индекса.
Однако рано радоваться. В приложении есть ещё один запрос, который до этого был настолько быстр, что его трудно было заметить. Теперь он стал намного медленее.
Было
mysql> SELECT * FROM idxitest WHERE a=100 ORDER BY id DESC LIMIT 1; +---------+-----+---+ | id | a | b +---------+-----+---+ | 3000000 | 100 | 7 +---------+-----+---+ 1 row IN SET (0.00 sec) mysql> EXPLAIN SELECT * FROM idxitest WHERE a=100 ORDER BY id DESC LIMIT 1; +----+-------------+----------+------+---------------+------+---------+-------+--------+-------------+ | id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra +----+-------------+----------+------+---------------+------+---------+-------+--------+-------------+ | 1 | SIMPLE | idxitest | ref | a | a | 4 | const | 126074 | USING WHERE +----+-------------+----------+------+---------------+------+---------+-------+--------+-------------+ 1 row IN SET (0.00 sec)
Стало
mysql> SELECT * FROM idxitest WHERE a=100 ORDER BY id DESC LIMIT 1; +---------+-----+---+ | id | a | b +---------+-----+---+ | 3000000 | 100 | 7 +---------+-----+---+ 1 row IN SET (1.01 sec) mysql> EXPLAIN SELECT * FROM idxitest WHERE a=100 ORDER BY id DESC LIMIT 1; +----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra +----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | idxitest | INDEX | a | PRIMARY | 4 | NULL | 36 | USING WHERE +----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+ 1 row IN SET (0.00 sec) # The plan also can look something like this: mysql> EXPLAIN SELECT * FROM idxitest WHERE a=100 ORDER BY id DESC LIMIT 1; +----+-------------+----------+------+---------------+------+---------+-------+------+------------------------------------------+ | id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra +----+-------------+----------+------+---------------+------+---------+-------+------+------------------------------------------+ | 1 | SIMPLE | idxitest | ref | a | a | 4 | const | 1 | USING WHERE; USING INDEX; USING filesort +----+-------------+----------+------+---------------+------+---------+-------+------+------------------------------------------+ 1 row IN SET (0.01 sec)
Почему этот запрос стал медленее? Причина в том, что его план выполнения выигрывал от одной особенности InnoDB — все индексные записи всегда отсортированы по первичному ключу. То есть когда у вас есть индекс (a) и первичный ключ id, то реальный ключ у вас (a, id). Когда же мы расширяем ключ до (a, b), то фактически получаем (a, b, id). Наш запрос использовал обе части исходного ключа «a» и «id» (для сортировки). Теперь же он не сможет полностью использовать новый индекс.
Какое решение проблемы? Придётся создать избыточный индекс и держать одновременно (a) и (a, b). Обычно это должно сработать, хотя не всегда.
CREATE TABLE `idxitest` ( `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, `a` int(11) NOT NULL, `b` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `a` (`a`), KEY `a_2` (`a`,`b`) ) ENGINE=InnoDB AUTO_INCREMENT=6029313 DEFAULT CHARSET=latin1 mysql> SELECT * FROM idxitest WHERE a=100 ORDER BY id DESC LIMIT 1; +---------+-----+---+ | id | a | b +---------+-----+---+ | 3000000 | 100 | 7 +---------+-----+---+ 1 row IN SET (1.03 sec) mysql> EXPLAIN SELECT * FROM idxitest WHERE a=100 ORDER BY id DESC LIMIT 1; +----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra +----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | idxitest | INDEX | a,a_2 | PRIMARY | 4 | NULL | 2247 | USING WHERE +----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+ 1 row IN SET (0.00 sec)
Оптимизатор запросов рассматривает возможность использования обоих индексов и в конце концов отвергает оба, делая полное сканирование индекса до того, как найдёт a=100. Это выглядит так, будто оптимизатор глючит. Он собирается просканировать 2247 строк, тогда как достаточно одной строки, используя индекс (a).
Поэтому вам придётся заставить оптимизатор выбрать правильный план выполнения запроса, добавив FORCE INDEX(a).
Как показал данный пример, надо быть очень аккуратным, играясь с индексами. Если у вас есть запросы, план выполнения которых выигрывает от сортировки InnoDB по первичному ключу, то они могут быть значительно затронуты.
По-разному может вести себя оптимизатор в разных версиях MySQL. Мы проверили в версии 5.1.45, хотя такое же поведение было обнаружено и в 5.0.