MySQL MyISAM медленный запрос count() несмотря на индекс покрытия

Я дергаю себя за волосы, пытаясь понять, что я делаю неправильно. Таблица очень проста:

CREATE TABLE `icd_index` (
  `icd` char(5) NOT NULL,
  `core_id` int(11) NOT NULL,
  `dx_order` tinyint(4) NOT NULL,
  PRIMARY KEY (`icd`,`dx_order`,`core_id`),
  KEY `core` (`core_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Как вы можете видеть, я создал покрывающий индекс, который охватывает все три столбца таблицы, плюс дополнительный индекс на core_id для потенциальных объединений. Это таблица ссылок "один ко многим" с каждым core_id сопоставлены с одним или несколькими icd, Таблица содержит 65 миллионов строк.

Итак, вот в чем проблема. Допустим, я хочу знать, сколько людей имеют код icd 25000. [Это диабет, если вам интересно]. Я пишу запрос, который выглядит так:

SELECT COUNT(core_id) FROM icd_index WHERE icd='25000'

Это займет более 60 секунд. Я думал, что, поскольку столбец icd был первым в покрытом индексе, подсчет будет быстрым.

Еще более запутанно, когда я однажды выполнил запрос, теперь он выполняется очень быстро. Я предположил, что это потому, что запрос был кэширован, но даже если я RESET QUERY CACHEзапрос теперь выполняется за доли секунды. Но, если я подожду достаточно долго, это снова замедлится - и я не могу понять, почему.

Я упускаю что-то очевидное. Нужен ли мне индекс на icd в одиночестве? Это лучшая производительность, которую я могу получить с 65M строк? Почему выполнение запроса и сброс кеша влияют на скорость? Сохраняются ли результаты в индексе?

РЕДАКТИРОВАТЬ: я использую MySQL 5.6 (в случае, если это имеет значение).

Вот EXPLAIN запроса:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  icd_index   ref PRIMARY PRIMARY 15  const   910104  Using where; Using index
Источник

3 ответа

Решение

Вот что происходит.

The SELECT COUNT (...) icd_index where icd='25000'

будет использовать индекс, который является BTree отдельно от данных. Но он сканирует это следующим образом:

  1. Найдите первую запись, имеющую icd='25000'. Это почти мгновенно.
  2. Сканирование вперед до тех пор, пока не найдет изменение в icd. Это будет сканировать только в индексе, не касаясь данных. Согласно EXPLAIN, будет около 910 104 индексных записей для сканирования.

Теперь давайте посмотрим на BTree для этого индекса. Исходя из полей в индексе, каждая строка будет ровно 22 байта, плюс будут некоторые издержки (оценка 40%). Индексный блок MyISAM составляет 1 КБ (см. InnoDB 16 КБ). Я бы оценил 33 строки на блок. 910,104/33 говорит о том, что для выполнения COUNT необходимо прочитать 27K блоков. (Заметка COUNT(core_id) нужно проверить core_id для того, чтобы быть нулевым, COUNT(*) не; это небольшая разница.) Чтение 27K блоков на обычном жестком диске занимает около 270 секунд. Вам повезло сделать это за 60 секунд.

Второй запуск обнаружил все эти блоки в key_buffer (предполагая, что key_buffer_size составляет не менее 27 МБ), поэтому ему не пришлось ждать диск. Следовательно, это было намного быстрее. (Это игнорирование кеша запросов, который у вас хватило на то, чтобы очистить или использовать SQL_NO_CACHE.)

5.6 оказывается неуместным (но спасибо за упоминание этого), поскольку этот процесс не изменился с 4.0 или ранее (за исключением того, что utf8 не существовало; подробнее об этом ниже).

Переключение на InnoDB помогло бы несколькими способами. ПЕРВИЧНЫЙ КЛЮЧ будет "кластеризован" с данными, а не хранится как отдельный BTree. Следовательно, как только данные или PK кэшируются, другой сразу становится доступным. Количество блоков будет больше похоже на 5 КБ, но они будут 16 КБ. Они могут загружаться быстрее, если кеш холодный.

Вы спрашиваете: "Нужен ли мне индекс только для icd? ". Что ж, это уменьшит размер BTIS MyISAM примерно до 21 байта на строку, поэтому размер BTree будет примерно 21/27, не так уж много улучшений (по крайней мере, для ситуация с холодным кэшем).

Другая мысль, если icd всегда числовой и всегда числовой, чтобы использовать MEDIUMINT UNSIGNEDи придерживаться ZEROFILL если он может иметь ведущие нули.

К сожалению, я не смог заметить набор символов. (Я исправил цифры выше, но позвольте мне уточнить.)

  • CHAR (5) допускает 5 символов.
  • Ascii занимает 1 байт на символ.
  • utf8 занимает до 3 байтов на символы.
  • Итак, CHAR(5) CHARACTER SET utf8 всегда занимает 15байтов.

Изменение столбца на CHAR(5) CHARACTER SET ascii сократил бы его до 5 байтов.

Изменение его на MEDIUMINT UNSIGNED ZEROFILL уменьшит его до 3 байтов.

Сжатие данных ускорило бы ввод-вывод примерно на пропорциональную величину (после разрешения еще 6 байтов для двух других полей.

Источник

Спасибо всем выше за вашу помощь. Учитывая приведенный выше совет, я полностью перестроил базу данных примерно так:

  1. Я убедил администратора сервера увеличить мою оперативную память до 6G.
  2. Я переключил все таблицы на InnoDB с набором символов ASCII.
  3. Когда я переместил данные из MyISAM в InnoDB, я отсортировал все данные в порядке индекса покрытия, прежде чем вставить их в новую таблицу, поэтому новая таблица будет полностью отсортирована правильно. Не знаю, действительно ли это помогает, но казалось, что это не повредит.
  4. Я возился с настройками БД, в частности с размером InnoDB Buffer Pool, и увеличил его до 256M.

Святая Матерь Божья, сейчас действительно быстро. Простой подсчет запросов теперь выполняется менее чем за 2 секунды. Не уверен, что из вышеперечисленного было наиболее эффективным (но запрос был быстрым до увеличения размера пула буферов)

Источник

У меня было то же самое, что и с одним из моих запросов. Таблица MyISAM использовала сортировку файлов для выполнения простого оператора SELECT.

Я закончил тем, что переключился на InnoDB, и проблема исчезла. Я не знаю почему.

Источник
Другие вопросы по тегам