Почему удаление этого индекса в MySQL ускоряет мой запрос 100x?
У меня есть следующая таблица MySQL (упрощенная):
CREATE TABLE `track` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(256) NOT NULL,
`is_active` tinyint(1) NOT NULL,
PRIMARY KEY (`id`),
KEY `is_active` (`is_active`, `id`)
) ENGINE=MyISAM AUTO_INCREMENT=7495088 DEFAULT CHARSET=utf8
Столбец 'is_active' помещает строки, которые я хочу игнорировать, в большинстве, но не всех, из моих запросов. У меня есть несколько запросов, которые периодически читают фрагменты из этой таблицы. Один из них выглядит следующим образом:
SELECT id,title from track where (track.is_active=1 and track.id > 5580702) ORDER BY id ASC LIMIT 10;
Этот запрос занимает более минуты, чтобы выполнить его. Здесь план выполнения:
> EXPLAIN SELECT id,title from track where (track.is_active=1 and track.id > 5580702) ORDER BY id ASC LIMIT 10;
+----+-------------+-------+------+----------------+--------+---------+-------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+----------------+--------+---------+-------+---------+-------------+
| 1 | SIMPLE | t | ref | PRIMARY,is_active | is_active | 1 | const | 3747543 | Using where |
+----+-------------+-------+------+----------------+--------+---------+-------+---------+-------------+
Теперь, если я говорю MySQL игнорировать индекс 'is_active', запрос выполняется мгновенно.
> EXPLAIN SELECT id,title from track IGNORE INDEX(is_active) WHERE (track.is_active=1 AND track.id > 5580702) ORDER BY id ASC LIMIT 10;
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
| 1 | SIMPLE | t | range | PRIMARY | PRIMARY | 4 | NULL | 1597518 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
Теперь, что действительно странно, что если я FORCE MySQL использовать индекс is_active, запрос снова произойдет мгновенно!
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
| 1 | SIMPLE | t | range | is_active |is_active| 5 | NULL | 1866730 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
Я просто не понимаю этого поведения. В индексе 'is_active' строки должны быть отсортированы с помощью is_active, за которым следует id. Я использую столбцы 'is_active' и 'id' в моем запросе, поэтому кажется, что для поиска идентификаторов нужно всего лишь несколько переходов по дереву, а затем использовать эти идентификаторы для извлечения заголовков из таблицы.
Что происходит?
EDIT: больше информации о том, что я делаю:
- Кэш запросов отключен.
- Запуск таблицы OPTIMIZE TABLE и ANALYZE TABLE не имел эффекта
- 6,620,372 строк имеют значение 'is_active' равным True. 874,714 строк имеют значение 'is_active' равным False.
- Использование FORCE INDEX (is_active) еще раз ускоряет запрос.
- Версия MySQL 5.1.54
Ответы
Ответ 1
Похоже, MySQL делает плохое решение о том, как использовать индекс.
Из этого плана запроса он показывает, что он мог использовать либо PRIMARY, либо is_active index, и он выбрал is_active, чтобы сначала сузить по track.is_active. Однако он использует только первый столбец индекса (track.is_active). Это получает 3747543 результатов, которые затем должны быть отфильтрованы и отсортированы.
Если бы он выбрал индекс PRIMARY, он мог бы сузить до 1597518 строк с помощью индекса, и они будут получены в порядке track.id уже, что не требует дальнейшей сортировки. Это будет быстрее.
Новая информация:
В третьем случае, когда вы используете FORCE INDEX, MySQL использует индекс is_active, но теперь вместо того, чтобы использовать только первый столбец, он использует оба столбца (см. key_len). Таким образом, теперь он может сужаться с помощью is_active и сортировать и фильтровать по id с использованием одного и того же индекса, а так как is_active - это единственная константа, ORDER BY удовлетворяется вторым столбцом (т.е. Строки из одной ветки индекса уже в отсортированном порядке). Это, по-видимому, еще лучший результат, чем использование PRIMARY - и, вероятно, то, что вы намеревались в первую очередь, правильно?
Я не знаю, почему он не использовал оба столбца этого индекса без FORCE INDEX, если только запрос не изменился тонким образом между ними. Если бы я не поместил его в MySQL, приняв плохие решения.
Ответ 2
Я думаю, что ускорение связано с вашим предложением where. Я предполагаю, что он только извлекает небольшое подмножество строк во всей большой таблице. Быстрее выполнять сканирование таблицы извлеченных данных для is_active на маленьком подмножестве, чем выполнять фильтрацию через большой файл индекса. Перемещение индекса столбца намного быстрее, чем перемещение комбинированного индекса.
Ответ 3
Несколько вещей, которые вы могли бы попробовать:
