Статьи

Ограничения оптимизатора MySQL 5.6.10: индексное условие Pushdown

Ноябрь 15, 2018

 Этот пост принадлежит Хайме Креспо из MySQL Performance Blog.

При подготовке вебинара, который я проведу в эту пятницу , я столкнулся с довольно интересной (хотя и не очень влиятельной) проблемой оптимизатора: « SELECT *», выполняющий половину времени, чем тот же « SELECT one_indexed_column» запрос в MySQL 5.6.10.

Это стало очень хорошим упражнением для проверки производительности и внутренней работы одной из самых замечательных функций нового оптимизатора MySQL: оптимизации условий индексации или ICP, о которой мы ранее говорили в нашем блоге .

В частности, следующий запрос дал такой удивительный результат:

mysql> SELECT * FROM cast_info WHERE role_id = 1 and note like '%Jaime%';

На таком столе:

CREATE TABLE `cast_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `person_id` int(11) NOT NULL,
  `movie_id` int(11) NOT NULL,
  `person_role_id` int(11) DEFAULT NULL,
  `note` varchar(250),
  `nr_order` int(11) DEFAULT NULL,
  `role_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `role_id_note` (`role_id`,`note`)
) ENGINE=InnoDB AUTO_INCREMENT=22187769 DEFAULT CHARSET=utf8;

В таблице было 22 миллиона строк, из которых примерно 8 миллионов имели role_id = 1, а 266 имели role_id = 1 и содержали слово «Jaime» где-то в примечании к полю.

Исходный запрос имел стабильное время выполнения 1,09 с , в то время как следующий, который выбирает меньшее количество данных (всего один столбец) и может использовать преимущества метода индекса покрытия, на самом деле занимал больше времени :

mysql> SELECT role_id FROM cast_info WHERE role_id = 1 and note like '%Jaime%'\G
266 rows in set (1.82 sec)

Обратите внимание, что время было очень стабильным, и содержимое пула буферов не влияло на результаты.

Что происходило? Ну, чтобы понять это, я должен предоставить вам дополнительную справочную информацию. Мой буферный пул был достаточно большим, чтобы вместить всю базу данных (данные и индексы полностью помещались в памяти). Кроме того, я тестировал, как я уже говорил, условие индексации. Давайте посмотрим на вывод EXPLAIN:

mysql> EXPLAIN SELECT * FROM cast_info WHERE role_id = 1 and note like '%Jaime%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: cast_info
         type: ref
possible_keys: role_id_note
          key: role_id_note
      key_len: 4
          ref: const
         rows: 10259274
        Extra: Using index condition
1 row in set (0.00 sec)

В случае ICP фактическое число строк, считываемых на уровне SQL, фактически сильно отличается от значения « rows», указанного выше. Это потому, что вторая часть условия — на note like '%Jaime%'самом деле проверяется на уровне двигателя, а не на уровне обработчика.

Условие pushdown является одной из новых функций MySQL 5.6, и на самом деле является большим улучшением по сравнению с MySQL 5.5. Например, в этом случае фактическое количество Handler_read_nextвызовов « » было уменьшено с 8346769 (5,5) до 266 (5,6), что сократило время выполнения почти в 5 раз. Совет для профессионалов: всегда проверяйте переменные состояния обработчика для анализа после выполнения.

Так почему же «SELECT note» на самом деле медленнее? Кажется, что всякий раз, когда доступна методика индекса покрытия, это всегда предпочтительнее, чем оптимизация ICP:

mysql> EXPLAIN SELECT role_id FROM cast_info WHERE role_id = 1 and note like '%Jaime%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: cast_info
         type: ref
possible_keys: role_id_note
          key: role_id_note
      key_len: 4
          ref: const
         rows: 10259274
        Extra: Using where; Using index
1 row in set (0.00 sec)

Я сообщил об этой проблеме в Oracle, и они подтвердили, что это предполагаемый / текущий статус оптимизатора MySQL 5.6.10. Другие интересные вещи, на которые стоит обратить внимание:

  • ICP — отличная новая функция, которая уже сэкономила нам много времени на выполнение, вероятно, ее стоимость должна быть лучше настроена в этой функции. Есть больше способов сделать запрос быстрее, а значит, вам нужно больше ручного ухода и настройки сейчас.
  • MySQL консервативен в отношении «использования индекса» — в большинстве случаев это будет правильным решением, потому что наш SELECT будет быстрее, только когда условие очень избирательно и буферный пул эффективен.
  • Обходного пути нет, используя FORCE-подобные команды или флажки optimizer_switch — мы можем отключить ICP, но не «используя index».

Таким образом, я бы не назвал это ошибкой, и меня это не особо беспокоит — это может быть, а может и не считать это крайним случаем, — но я бы назвал это ограничением текущего планировщика запросов. Однако я хотел бы видеть в будущем более эффективные алгоритмы, статистические вычисления и переменные мониторинга использования индексов, теперь, когда у нас есть более сложные стратегии оптимизации. Даже счетчиков операций на уровне строк иногда недостаточно.

Хотите узнать больше об улучшениях оптимизации запросов MySQL 5.6 на практическом уровне, с реальными примерами? Хотите узнать трехстороннее, независимое и техническое мнение о новых функциях планировщика запросов MySQL? Вы еще не знакомы с такими терминами, как MRR, BKA или ICP? Вы разработчик или администратор баз данных и хотите быть готовым к выпуску MySQL 5.6 и получить преимущества новейших интегрированных инструментов, которые MySQL предоставляет в своем последнем выпуске GA? Затем я приглашаю вас присоединиться ко мне на вебинаре, который я подготовил для этой пятницы, 15 марта: «Узнайте, как MySQL 5.6 упрощает оптимизацию запросов»

35
Share: