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.
Ещё по теме:
Написано Март 12, 2008
Комментарии
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
Автору удаленного комментария рекомендую прочесть небольшой сравнительный тестик
***
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 и все пройдет нормально.
|
