COUNT(*) для таблиц InnoDB — перевод с MySQL Performance Blog

Меня давно интересовало, что за откровения таятся в блоге Петра Зайцева и Вадима Ткаченко посвященному оптимизации работы с MySQL. Сложность в том, что он на английском, а времени на то, чтобы не просто прочитать, а понять все тонкости нет.

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

----------

Оригинал: COUNT(*) for Innodb Tables (MySQL Performance Blog).
Автор: Петр Зайцев.

----------

Я считаю, самое важное замечание при переходе с MyISAM на Innodb — предупреждение о том, что Innodb очень медлителен при обработке запросов COUNT(*). Но есть одна деталь, которую часто упускают из виду — это относится только к запросам COUNT(*) без оператора WHERE.

Таким образом, если ваш запрос выглядит как SELECT COUNT(*) FROM USER, он будет обрабатываться быстрее в таблицах MyISAM (MEMORY и некоторых других), потому что будет просто прочитано сохраненное в таблице значение количества строк. В Innodb при этом необходимо выполнить полное сканирование таблицы или индекса, потому что в этом типе таблиц не предусмотрен такой счётчик. Это так же не может быть решено заведением простого счётчика, так как разные транзакции могут видеть разное количество записей в таблице.

Если запрос выглядит так: SELECT COUNT(*) FROM IMAGE WHERE USER_ID=5, то он одинаково будет выполнятся над таблицами MyISAM и Innodb — путем сканированием индексного ряда. Он может быть выполнен как быстрее, так и медленнее как для MyISAM, так и для Innodb. Все зависит от структуры таблицы.

На практике чаще всего приходится иметь дело с запросами второго типа, поэтому это не такая страшная проблема, как кажется на первый взгляд. Обычно счётчик числа строк нужен для программ администрирования, которые могут отображать его в статистике таблицы, также его можно использовать в статистике приложения, чтобы показывать сообщения вида «У нас 123345 пользователей, которые закачали 1344656 изображений», но это можно легко устранить.

Так что помните — Innodb медлителен не для ВСЕХ зопросов COUNT(*), а только для очень специфических запросов COUNT(*) без оператора WHERE. Хотя это не значит, что я не хочу, чтобы это исправили — на самом деле это очень раздражает.

----------

Стили CSS — Создаем растяжимый по вертикали «Post-IT». В комментариях там есть альтернативный способ.
Отличная подборка постов про модули для Drupal. Андрей, спасибо, читаю с удовольствием.
Всплывающие подсказки при помощи CSS.

Теги: mysql, MySQL Performance Blog, perfomance, переводы

Еще по теме

Posted on марта 12, 2008
Filed Under Проектирование и разработка сайтов

Понравилось?

FX Poster - марта 12, 2008 17:00

Хехе. У меня такие же проблемы — вижу, что новый пост, интересно, но быстренько «просмотреть» его не получается, т.к. он на английском, нужно вчитываться. Так что начинание поддерживаю полностью! Спасибо!
Артём Майнас - марта 12, 2008 18:24

У меня вообще какой-то страх перед MySQL и всем, что с ним связано. Сейчас пытаюсь его преодалеть и научиться нормально работать с такими базами.
FX Poster - марта 12, 2008 18:26

Зря. Ничего сложного там нет, если особо не лезть в дебри оптимизации работы БД.
Денис Болтиков - марта 12, 2008 18:57

Артём Майнас, основы и правда просты. Я их тоже раньше не понимал, потом пришлось делать проект и разобрался за пару дней. Попробуй посмотреть соответствующий онлайн-курс на INTUIT.
Вопрос в другом, а оно тебе надо? У тебя много других достоинств.
Денис Болтиков - марта 12, 2008 19:00

Случайно удалил комметарий по делу от одного из читателей :( Извиняюсь, не туда нажал мышкой.
Было про то, что запрос с COUNT(id) ложит базу с таблицей «news» за 7 секунд. Если ты еще читаешь, то повтори его пожалуйста.
Ну и сразу советы. Замени COUNT(id) на COUNT(*). Проверь есть ли индекс по полю по которому идет выборка и используется ли этот индекс в запросе.
Мысли про мемкеш тоже должна помочь :)
kikaha - марта 12, 2008 19:25

Кармический батончик тебе в панамку за перевод! У меня с этим блогом та же ситуация — никак время не выберу сесть и вкусно прочитать , а в ридере только глазами пробегаю ... спасибо за перевод еще раз, ждем остальных статей!
dkrnl - марта 12, 2008 19:29

SELECT COUNT(`id`) FROM `news` WHERE `type`=1;
новостей ~18 000, архив за 6 лет.
Все индексы есть, Работает иногда 7> секунд.
Канешно мемкэшед выход, но не совсем, ведь какотой запрос всеравно будет считать за тоже не приемлимое время. И хорошо если этот запрос от бота, а не от человека. (:
Да и динамика новостей такая что кеш «протухает» в среднем через час.
Думаю передалать на inodb + доп. таблица со счетчиками для каждего type и тригеры.
Вот только не ясный момент — как долго будут работать тригеры, и в какое время. Если после коммита то получится большой лаг — для редактора новостей. Или же по инициативие бд... надо пробывать.
Vadim Voituk - марта 12, 2008 21:32

Автору удаленного комментария рекомендую прочесть небольшой сравнительный тестик
voituk.kiev.ua/2006/07/27...count-statement/
Vadim Voituk - марта 13, 2008 00:37

@dkrnl: А можно explain запроса (дабы не захламлять ветку) выслать на емайл vadim.voituk на gmail.com?
Какой средний размер строки таблицы news?
Когда последний раз был optimize table?
Я конечно ни на что не намекаю, но у меня подобный запрос редко 7 секунд выполняется на 18 млн записей :)
Денис Болтиков - марта 13, 2008 13:11

Да, для 18 тысяч записей такой запрос выполняется непозволительно долго.
Дима - марта 18, 2008 00:16

недавно столкнулся с такой же ситуацией
Eugene - марта 28, 2008 16:43

Vadim Voituk, спасибо за тестик. Наглядно и понятно все.
Всем юзать count(*) :)
TYUS - июля 21, 2008 09:26

Денис, а еще будут посты об оптимизации мускул?
Сам вот недавно столкнулся с такой проблемой.
Ванько - августа 13, 2008 04:10

Читал в оригинале этот пост, и блог в целом почитываю :) Полезностей куча.
MLMовка - декабря 22, 2008 23:02

Может быть немного не в тему, но по MySQL у меня проблема перевода базы с одного хостинга на другой. Делая бэкап/рестор — получаются одни вопросики :(
Кто-нибудь может помочь?
Денис Болтиков - декабря 23, 2008 00:01

Проблема в кодировках. посмотри в какой кодировке данные лежат в таблицах, в какой ты сохраняешь, в какой кодировке новая базы и в какой ты туда загружаешь данные.
MLMовка - января 6, 2009 00:30

оригинал в koi8, база в utf8
Денис Болтиков - января 6, 2009 00:59

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