Блог

У меня есть следующая таблица 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 |
 ---- ------------- ------- ------- --------------- --------- --------- ------ --------- ------------- 
  

Теперь, что действительно странно, так это то, что если я ЗАСТАВЛЯЮ 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’ в своем запросе, поэтому кажется, что нужно всего лишь сделать несколько переходов по дереву, чтобы найти идентификаторы, а затем использовать эти идентификаторы для извлечения заголовков из таблицы.

Что происходит?

РЕДАКТИРОВАТЬ: больше информации о том, что я делаю:

• Кэш запросов отключен
• Запуск OPTIMIZE TABLE и ANALYZE TABLE не оказал никакого эффекта
• для 6 620 372 строк ‘is_active’ установлено значение True. для 874 714 строк ‘is_active’ установлено значение False.
• Использование ПРИНУДИТЕЛЬНОГО ИНДЕКСА (is_active) снова ускоряет запрос.
• Версия MySQL 5.1.54

Похоже, MySQL принимает неправильное решение о том, как использовать индекс.

Из этого плана запроса видно, что он мог использовать либо ОСНОВНОЙ, либо индекс is_active , и он выбрал is_active, чтобы сначала сузить его по track.is_active . Однако он использует только первый столбец индекса (track.is_active). Это дает 3747543 результатов, которые затем должны быть отфильтрованы и отсортированы.

Если бы он выбрал ОСНОВНОЙ индекс, он смог бы сузить до 1597518 строк, используя индекс, и они были бы извлечены в порядке track.id уже, что не должно требовать дальнейшей сортировки. Это было бы быстрее.

Новая информация:

В третьем случае, когда вы используете ПРИНУДИТЕЛЬНЫЙ ИНДЕКС, MySQL использует индекс is_active, но теперь вместо того, чтобы использовать только первый столбец, он использует оба столбца (см. key_len ). Поэтому теперь он может сужаться по is_active и сортировать и фильтровать по идентификатору, используя тот же индекс, а поскольку is_active является единственной константой, порядок ПО выполняется вторым столбцом (т. Е. Строки из одной ветви индекса уже отсортированы). Кажется, это даже лучший результат, чем использование PRIMARY — и, вероятно, то, что вы предполагали в первую очередь, верно?

Я не знаю, почему он не использовал оба столбца этого индекса без ПРИНУДИТЕЛЬНОГО ИНДЕКСА, если только запрос не изменился незначительным образом между ними. Если нет, я бы списал это на то, что MySQL принимает неправильные решения.

Я думаю, что ускорение связано с вашим предложением where. Я предполагаю, что он извлекает только небольшое подмножество строк во всей большой таблице. Быстрее выполнить сканирование таблицы полученных данных для is_active в небольшом подмножестве, чем выполнять фильтрацию через большой индексный файл. Обход индекса с одним столбцом намного быстрее, чем обход комбинированного индекса.

Несколько вещей, которые вы могли бы попробовать:

• Оптимизируйте и ПРОВЕРЬТЕ свою таблицу, чтобы mysql пересчитал значения индекса
• взгляните на http://dev.mysql.com/doc/refman/5.1/en/index-hints.html — вы можете указать mysql выбрать правильный индекс в разных случаях