Блог

У меня есть следующая таблица ниже: у меня есть 3,5 миллиона записей.

 CREATE TABLE `video_downloads` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `video_id` bigint(20) NOT NULL,
  `download_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3573041 DEFAULT CHARSET=latin1
  

Только id и user_id являются indexed .

Вот мой запрос:

 select max(video_id), user_id
from video_downloads
group by user_id
  

При такой текущей настройке таблицы выполнение этого запроса заняло у меня примерно более 10 минут.
Итак, вот explain

 | id | select_type | table           | type  | possible_keys | key     | key_len | ref | rows    | Extra |
|----|-------------|-----------------|-------|---------------|---------|---------|-----|---------|-------|
| 1  | SIMPLE      | video_downloads | index |               | user_id | 8       |     | 3562709 |       |
  

 {
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "video_downloads",
      "access_type": "index",
      "key": "user_id",
      "key_length": "8",
      "used_key_parts": ["user_id"],
      "rows": 3562709,
      "filtered": 100
    }
  }
}
  

А затем я удалил индекс для user_id выполнения того же запроса, и это заняло у меня около 1.5 s .

Вот explain без user_id индекса

 | id | select_type | table           | type | possible_keys | key | key_len | ref | rows    | Extra                           |
|----|-------------|-----------------|------|---------------|-----|---------|-----|---------|---------------------------------|
| 1  | SIMPLE      | video_downloads | ALL  |               |     |         |     | 3562709 | Using temporary; Using filesort |
  

 {
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "video_downloads.user_id",
      "temporary_table": {
        "table": {
          "table_name": "video_downloads",
          "access_type": "ALL",
          "rows": 3562709,
          "filtered": 100
        }
      }
    }
  }
}
  

Я думаю, что мой главный вопрос заключается в том, почему существует такая огромная разница во времени с включенным индексом и без него user_id . И когда есть индекс на user_id type is index , что означает, что он использует индекс, но запрос выполняется очень медленно.

Я немного смущен результатом, я не думаю, что понимаю, почему это происходит, я проверяю официальный документ, который все еще не до конца понимает это.

обновление Я думаю, что основной причиной может быть использование index данных для извлечения строки с диска, и он собирается делать это один за другим и случайным образом. Итак, это 3,5 миллиона раз случайного чтения с диска. Это единственная причина, о которой я могу думать. Однако, это будет так медленно? (больше, чем 10 mins против 1.5 s ???).

Однако из документа MySQL

Иногда MySQL не использует индекс, даже если он доступен. Одно из обстоятельств, при котором это происходит, заключается в том, что оптимизатор оценивает, что использование индекса потребует от MySQL доступа к очень большому проценту строк в таблице.

В моем случае MySQL, похоже, не принимает правильного решения. Я вижу, что possible_keys есть null , но key все еще использует индекс, почему? это из-за group by ?

«Статистика», которую использует Оптимизатор, не всегда идеальна. Однако «10 мин» против «1,5 сек» довольно впечатляюще. Интересно, было ли вмешательство извне. О, какой Двигатель используется?

Когда он использовал индекс с одним столбцом, ему, вероятно, приходилось переключаться между индексом и данными, извлекая 3,5 млн строк по одной за раз, но случайным образом.

Когда он выполнял сканирование таблицы («Все»), он также считывал 3,5 млн строк, но последовательно. Но затем он должен был выполнить сортировку.

Buffer_pool

Проблема в 16M для innodb_buffer_pool_size . Установите это значение примерно на 70% от размера оперативной памяти, если у вас нет особенно маленькой машины.

10-минутный запрос, вероятно, состоял из сплошного ввода-вывода, случайного чтения и перечитывания данных из таблицы.

На вращающемся диске (HDD, а не SDD) 3,5 М операций чтения со скоростью 100 блоков в секунду составляет несколько часов. Итак, вам повезло, что вы закончили всего за 10 минут. В 1.5 s говорится, насколько полезен достаточно большой кэш оперативной памяти.

1,5 с может быть достаточно, чтобы прочитать прямую (не случайным образом) всю таблицу один раз.