Инженер, PHP, Go, Linux
13,7
рейтинг
21 октября 2011 в 14:46

Разработка → Стратегия оптимизации веб-проекта с использованием MySQL

Введение


В жизни любого крупного веб-проекта, особенно на PHP, но, в целом, это касается любого серверного ЯП, пригодного для веб-разработки, обычно наступает понимание, что «так дальше жить нельзя», и что настал момент, когда нужно провести оптимизацию работы сайта, чтобы он перестал тормозить (хотя бы на production).

Интересно, что, как правило, даже тяжелые фреймворки (вроде Symfony или RoR) на «медленных» языках, в production-окружении работают достаточно сносно по скорости, а основные «тормоза» вызываются SQL-запросами и неграмотным кешированием (к примеру, инициализация достаточно сложной и большой конфигурации проекта на Symfony занимает около 80 мс, а времена исполнения страницы, при этом, иногда достигают секунды и более).

Если вы смогли определить, что это — ваш случай, и ваш проект на MySQL, то эта статья может вам помочь принять конкретные меры и исправлению ситуации с закреплением результата и предотвращением возникновения откровенных проблем с СУБД впоследствии.

Выявление узких мест


Для начала, хотелось бы сказать, что реальная польза от оптимизации работы с MySQL будет ощущаться далеко не всегда, поэтому перед этим желательно убедиться, что проблемы действительно вызваны работой с MySQL. Здесь общие советы дать довольно сложно, но, в любом случае, стоит сначала каким-либо образом измерить, к примеру, общий процент времени исполнения запросов по отношению к времени исполнения страницы (в production окружении) в вашем драйвере работы с СУБД. Если этот процент — порядка 50 и выше, то оптимизация работы с MySQL более, чем обоснована.

Что и как измерять


Как правило, даже в самых «плохих» (в плане производительности) фреймворках и/или CMS есть некоторые средства для отслеживания проблем производительности СУБД — обычно всегда подсчитывается, хотя бы, количество запросов на странице. На всякий случай скажу, что количества запросов на страницу больше 100 — это плохо :), и начать стоит с этого.

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

Измерение времен исполнения запросов


Конечно же, самый простой и в меру эффективный способ отслеживания «медленных» запросов — это подсчет времени исполнения каждого запроса и вывод времен исполнения запросов на экран (на основании чего делать вывод, что какие-то запросы «тормозят»). Проблема заключается в том, что этот способ более-менее хорошо годится только для production окружения, когда СУБД хорошо настроена и у MySQL-сервера достаточное количество памяти, чтобы I/O был минимальным и т.д., то есть, когда время исполнения запроса будет зависеть действительно только от его сложности для исполнения и времени CPU, которое на это требуется, и будет минимум сторонних факторов, влияющих на производительность. А если включен query cache, то простое повторение одного и того же запроса будет давать совершенно различное время исполнения (если только не используются ф-ции из стоп-листа).

Если же использовать этот подход в dev-окружении, то, зачастую, запросы «тормозят» не из-за того, что они сложные, а просто из-за случайной посторонней нагрузки или банального I/O. Можно, конечно, просто сразу после запроса обновлять страницу и выполнять те же самые запросы заново, но даже это не гарантирует отсутствия побочных эффектов (например, некоторые включают query cache и на dev-сервере MySQL — если так, то его нужно, конечно же, отключить немедленно).

Поскольку, зачастую, времена исполнения запросов на dev-базе сильно «скачут», порог для slow-запросов ставят в очень большие величины (в Propel с Symfony это, по умолчанию, 100 ms) или вообще не обращают внимания на запросы, которые медленно исполняются, списывая всё на I/O.

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

Автоматический EXPLAIN для запросов


Вы можете поддаться соблазну делать для всех SELECT-запросов ещё один запрос с префиксом «EXPLAIN » и считать произведение поля «rows» для всех уникальных id запросов в EXPLAIN для оценки сложности запроса. Идея хорошая, но, зачастую, трудно реализуемая из-за сложностей с выполнением того же prepared-запроса, к примеру. Но это не самая большая проблема (проблему с prepared statements ещё, в принципе, можно как-то решить). Самая большая проблема заключается в том, что EXPLAIN в MySQL зачастую нагло врёт.

Приведу простой пример (допустим, в таблице 100 000 записей со случайными значениями some_field):

Табличка
CREATE TABLE some_table(id INT PRIMARY KEY AUTO_INCREMENT, some_field INT)

Исполняем простой запрос, который просмотрит 100 000 строк и не найдет ничего
SELECT * FROM some_table WHERE some_field = 100500 ORDER BY id LIMIT 100

Смотрим EXPLAIN на MySQL 5.1.49
EXPLAIN SELECT * FROM some_table WHERE some_field = 100500 ORDER BY id LIMIT 100

EXPLAIN говорит: насяльнике, 100 строк мне просмотреть нада, буду индекс праймаре использовать, насяльнике, всё хорошо будит, мамой клянусь!

Исполняем простой запрос, который просмотрит 100 строк (или немногим больше) и сразу же их вернет
SELECT * FROM some_table WHERE some_field <> 0 ORDER BY id LIMIT 100

Смотрим EXPLAIN на MySQL 5.0.51
EXPLAIN SELECT * FROM some_table WHERE some_field <> 0 ORDER BY id LIMIT 100

EXPLAIN говорит: насяльнике, 100 000 строк мне просмотреть нада, буду индекс праймаре использовать, осинь долго будит, насяльнике! (да, именно так и говорит, даю слово :))

Это были очень простые примеры, чтобы можно было легко понять, что к количеству строк в EXPLAIN стоит относиться с осторожностью — ведь EXPLAIN не исполняет запрос, а значит, он не может знать, сколько на самом деле ему нужно будет прочитать строк, он лишь делает оценку (причём часто ошибается раз в 10 :))

Считаем количества прочитанных строк


Если вы думали, что в MySQL для измерения производительности самый продвинутый способ — это EXPLAIN, то вы ошибаетесь (не так давно, я тоже так думал :)).

На самом деле, выполнение следующих запросов:

SET query_cache_type = OFF; -- если query cache включен, выключаем его
FLUSH STATUS; -- скоро поймете :)

SELECT * FROM some_table WHERE some_field = 100500 ORDER BY id LIMIT 100; -- наш запрос

SHOW SESSION STATUS LIKE 'handler_%' -- магия ;)!

Выдаст что-то похожее на следующую картинку:



Чтобы лучше понять, что такое Handler в MySQL, можете почитать, например, описание команд HANDLER в MySQL. Одним словом, Handler — это общий интерфейс для чтения/записи строк (и не только) в MySQL для разных типов таблиц. Название HandlerSocket для соответствующего интерфейса в MySQL тоже должно вам намекнуть на то, что это за зверь такой.

Таким образом, выполнив в самом начале страницы первые 2 запроса (отключение query cache и сброс статистики), а в самом конце страницы — последнего запроса, можно получить общее количество прочитанных/записанных строк у MySQL. Для проекта, для которого я занимался подобной оптимизацией, я добавил alert() на странице в dev-версии, если в результате выполнения страницы прочитано/записано больше 10 000 строк.

Конечно, это не панацея, но может вам серьезно помочь найти и «обезвредить» медленные страницы на сайте и принять соответствующие меры. Этому методу абсолютно наплевать на то, что это за запрос (оно работает даже для INSERT INTO… SELECT ...) и он всегда выдает точную статистику по совершенным действиям в результате запроса, даже если EXPLAIN ничего путного сказать не может.

Количество присланной информации сервером


В SESSION STATUS, на самом деле, содержится гораздо больше информации, например информация по трафику между клиентом и SQL-сервером (Bytes_%) — если у вас есть «широкие» таблицы, то это может быть тоже актуальным (особенно при работе с ORM, которые любят выбирать все столбцы из таблиц, даже если они там не нужны)

Собственно, оптимизация запросов


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

Надеюсь, информация из этого топика будет для вас полезной :). С помощью этой методики мне удалось выявить узкие места и снизить времена исполнения большинства страниц на сайте на Symfony с ~1000 ms до где-то 200-300 ms и добавить в дев-версию инструмент для автоматического предупреждения подобных проблем в будущем. Всё это заняло у меня где-то 3 дня для сайта с ~20 Мб активного «своего» (всего кода около 100 Мб) кода на PHP :). Много это или мало — думайте сами :)
Юрий Насретдинов @youROCK
карма
240,1
рейтинг 13,7
Инженер, PHP, Go, Linux
Реклама помогает поддерживать и развивать наши сервисы

Подробнее
Спецпроект

Самое читаемое Разработка

Комментарии (81)

  • +7
    100 мегабайт кода? О_о
    • +1
      Ну да, а в чём проблема :)? За несколько лет работы 4-7 человек можно и больше написать :). Активного кода — где-то 20 Мб
      • 0
        Пардон, показалось сначала, что 100 мегабайт кода занял инструмент для автоматического предупреждения подобных проблем :-)
      • +2
        получается 3.75 млн строк примерно, за 4 года работы каждому кодеру из 7 человек нужно писать по 466 строк кода каждый день. учитывая выходные и праздники. дайте мне таких кодеров, которые могут 24/7 писать 500 строк нужного рабочего кода в день :) я им буду платить ооочень много
        • +1
          Symfony — нефиговый кодогенератор, так что почему бы и нет.
          • 0
            дык тут же человек говорит, что все руками написано :)
            • 0
              Symfony действительно помогает писать код во многом, как и Propel, генерируя «каркас», поэтому, конечно, действительно вручную написанного кода будет немного меньше. Но несколько лет — это не 4 года, а больше :). Ну и там не весь код на Symfony, большАя часть кода на обычном PHP
    • 0
      У нас проект примерно на 300МБ кода
      • 0
        Исключая внешние библиотеки?..
        • +1
          Так точно. Без Пейпала, картинок интерфейса (css, js и шаблоны будем считать за код — они же написаны вот этими мозолистыми руками :) ) и т.п.
          • 0
            А у вас пейпал (PayPal) какой-то большой?
            • 0
              8 МБ с доками и примерами
          • 0
            хотел бы я поглядеть на проект в 300 МБ чистого пхп кода. если в среднем одна строка занимает 80 символов, то это получается 3.75 млн строк кода. вы что амазон там написали? чтобы обслуживать такой воистину громадный проект нужен как минимум кластер. а там уже совсем другие методики оптимизации используются в дополнение к сказанному вами.
            • 0
              Ну, вот цифры для проекта, о котором шла речь:

              # количество строк
              $ cat `find . | grep .php | grep -v 'cache/'` 2>/dev/null | wc -l
              2066539
              # количество байт
              $ cat `find . | grep .php | grep -v 'cache/'` 2>/dev/null | wc -c
              74121750


              Думайте сами :))
              • 0
                ну тут же 75 мегабайт кода ;) а не 300. и сдается мне это со всеми библиотеками.
                • 0
                  Я говорил про проект в статье :). И это один проект, а их у нас есть ещё другие, поменьше :)
                  • 0
                    а какой тематики эти проекты, если не секрет, что в них столько логики?
                    • 0
                      Туризм :). Это не так важно, в любом более-менее крупном бизнесе будет немало внутренней логики, которой извне может быть вообще не видно
                • +1
                  Тащемта это моя была реплика про 300МБ и youROCK не имеет никакого отношения к нему. Вот моя статистика:

                  $ find. \( -name "*.php" -o -name "*.js" -o -name "*.css" \) -exec cat {} \; | wc -l
                  7400511
                  $ find. \( -name "*.php" -o -name "*.js" -o -name "*.css" \) -exec cat {} \; | wc -c
                  283243229

                  Поводов для гордости не так уж и много — куча легаси, написанного не за один год. Уууу.
                  • +5
                    Серьёзные размерчики. Я стараюсь держать проекты в рамках 40K строк (без учёта фреймворка). Если проект становится больше — рефакторю, улучшаю архитектуру и в итоге минус несколько тыс. Строк кода. Так уже года три — функционал растёт, а размер не меняется!
                  • 0
                    наверняка есть много дублирующего кода
                    • 0
                      есть. Не сильно дублирущего, не так-то и много тупого копи-паста, но он есть. Работаем над этим :)
                      • 0
                        Простая арифметика:
                        Если простой вывод из Бд занимает пять строк (строим массив) и он повторяется 300 раз, то уже экономим 1450 строк (1500 — 50 на формирование класса)
                        • 0
                          Добро пожаловать в мемкеш/редис. Объекты и так кешируются, опкод кешируется, вывод веб-сервера — и тот кешируется (который нужен). Поэтому проблема 1450 строк хоть и присутствует, но не так актуальна.
      • 0
        что-то уж очень много.
    • +2
      Вот и меня заинтересовало, что за проект с кодом в таком объеме.
      Искусственный интеллект с памятью на MySQL?
      • –1
        Вот вам смешно, а я ведь действительно отчасти похожими вещами и занимаюсь :(. До искусственного интеллекта, конечно, не дотягивает, но логика порой бывает оооочень сложная, и да, как ни странно, память именно на MySQL, ибо больше никто так компактно хранить информацию не умеет (кроме других реляционных СУБД, конечно же)
    • 0
      а что здесь странного?
    • 0
      Поправил статью, написав чуть более ясно, что под этим подразумевается
  • +3
    Огромное спасибо!
    SHOW SESSION STATUS LIKE 'handler_%' -- магия ;)!
    
  • 0
    Ставим Percona Server, включаем handler socket и радуемся жизни :)
    p.s. Percona Server — это форк MySQL, со всякими оптимизациями.
    • +1
      От запросов, которые требуют FULL SCAN на 100 000 строк (потому что индексов соответствующих нет) даже Percona не спасет, я думаю ;)
      • 0
        Хм… а как потестить производительность, если нет такой объёмной БД?
        • +1
          Если такой объемной БД нет, то и проблем у вас, скорее всего, тоже не будет с этим :). Ну попробуйте сделать JOIN для всех таблиц, что у вас есть по условию 1=1 — я думаю, на 100 000 строк сможете набрать даже с 3 таблицами
          • 0
            Нагуглил пакет sysbench и с его помощью потестировал :)

            pastebin.com/eHXRbaTb

            Правда сравнить не с чем. Тестировалось на виртуалке с ubuntu server 11.10. 70% от 2 ядер E8400@3.33Ghz, 512mb ram.
        • 0
          Сделать тестовое наполнение БД. Не зря при разработке высоконагруженных систем выделяют даже команды подготовителей данных.
  • 0
    про EXPLAIN интересно…
  • 0
    Рискну посоветовать еще один полезный инструмент для профилирования работы бд: SHOW PROFILES
    Более детально тут:
    dev.mysql.com/doc/refman/5.0/en/show-profiles.html
    • 0
      Да, profiling тоже бывает очень полезен, но как раз проблему с количеством прочитанных строк он решает далеко не всегда :). И когда используются подзапросы, SHOW PROFILE генерит ооочень много строк
  • +1
    По-моему автор изобрёл внутреннюю стату Percona
  • +2
    Про оптимизацию запросок как-то мало, я бы еще добавил денормализацию, избавление от групбаев, джойнов. Да и о кешировании как-то глухо.
    • +1
      Да, про собственно оптимизацию запросов я специально ничего не писал, потому что статей на эту тему не просто много, а очень много, и здесь, как раз, серебряной пули нет, к сожалению
    • 0
      А джойны чем плохи? Иногда без них никак.
      • +1
        обойтись можно всегда, а плохи тем, что масштабируются плохо, и кешировать без них проще
  • +1
    > EXPLAIN в MySQL зачастую нагло врёт.

    С чего это он врёт? Конечно, никто 100% точности от него не ожидает, но тут он, скорее всего, говорит правду.
    • 0
      Ну, статистика по количеству просмотренных строк (SHOW STATUS LIKE 'handler_%') говорит об обратном — в том-то и дело :)
      • 0
        Ой, простите, я перепутала запросы в блоках: у вас же разные в 5.1 и 5.0
  • 0
    С помощью этой методики мне удалось выявить узкие места и снизить времена исполнения большинства страниц на сайте на Symfony с ~1000 ms до где-то 200-300 ms и добавить в дев-версию инструмент для автоматического предупреждения подобных проблем в будущем.
    Это еще раз доказывает, что если хочешь написать Высокопроизводительный порект, или с проект с притензией на Hiloa, то забей на всякие там Симфони и Друпалы и тому подобное…
    Решил как-то наш IT начальник использовать ОперКарт. В результате пришлось переписать весь фронтэнд (в том числе и из-за нового интерфейса). По кучи запросов на страницу — это жесь. По одному селекту на вывод одной ветки дерева — это вообще песня…
    В результатеот ОпенКарта осталась одна админка, которая использовалась и то на 10%, так как пришлось добавлять кучу своего функционала.
    • 0
      200-300 ms — тоже жесть, у меня в проекте по 20-40 ms на полное формирование стр.
    • +1
      Полностью согласен, что для хайлоада симфони или drupal — не лучший выбор. Но, к сожалению, этот выбор был сделан до меня, а для себя лишь поставил задачу заставить это чудо хотя бы как-то шевелиться. С 200-300 ms на страницу уже можно жить, в принципе
      • 0
        +1 в карму
    • 0
      10 000 000 хитов в сутки это уже хайлоад или нет?
      Я про sportbox.ru на Drupal, если что
      • 0
        А сколько серверов, и каких, если не секрет, обеспечивают работу сайта?
        • +1
          6 фронтэндов для веба, не забываем про трансляции, когда до 45000 онлайн
          1 сервер для спортивной статистики, пропущенные/забитые голы и всё такое.
          1 сервер БД
          3 мемкеша
          + девелоперское.
          Но могу ошибаться, некоторые машины являются виртуальными машинами.
          • 0
            Спасибо, неплохо, 3 мемкеша панацея:) У нас аналогичная нагрузка на чуть меньшем кол-ве серверов работала, там самописный мвц был, к сожалению, не могу раскрывать подробности, коммерческая тайна и всё такое.
  • +1
    Долго искал в статье хоть какой-то намек на использование log-slow-queries и log-queries-not-using-indexes. Не нашел.

    Как же это вы забыли про штатный способ поиска узких мест?
    • 0
      Не то, чтобы я про него забыл, я его просто пропустил, как и много чего другого. slow log — это хорошо, но его нужно смотреть отдельно, и польза от него далеко не однозначная, ибо в slow log часто попадают вполне «легальные» запросы. По сути, предложенный мной метод почти полностью повторяет информацию, доступную в slow log, только без, собственно, использования slow log
      • 0
        Под «легальными» запросами я подразумевал различные статистические выборки или выборки, которые исполняются редко, или же, скажем, по крону. В теории, можно всё это настроить и отфильтровать, но мой способ, как мне кажется, намного проще и удобнее в использовании
      • 0
        Я ответил, но почему-то вот здесь, извините
  • 0
    То есть MySQL предоставляет вам надежный способ учесть:
    а) чистое время исполнения запроса, без учета кэширования и времени построения плана и
    бэ) количество реально просканированных записей
    и даже построить по ним отчет (mysqldumpslow), но вы его пропускаете, потому что в него надо «смотреть отдельно»?
    • 0
      Ладно, давайте поставим вопрос по-другому. Была задача — снизить времена исполнения страниц на сайте. Что дает нам slow log, в самом лучшем случае? Он дает нам непосредственно SQL-запросы, которые были исполнены. По этим SQL-запросам ещё нужно найти кусок кода, в котором происходит вызов этого запроса (что далеко не всегда очевидно, если используется ORM, а у нас она используется очень конкретно) и только потом его исследовать.

      Итого, действия при работе со slow log'ом:
      1) нужно туда не забывать смотреть (причём для того, чтобы был доступ к slow log'у, по сути, нужен доступ к контейнеру с СУБД)
      2) нужно (вручную?) отсеять запросы, которые не исполняются на страницах, а исполняются где-либо ещё (например, в кроне)
      3) отсеять «тормозные» запросы, вызванные случайными всплесками активности / длительными блокировками при дампе таблиц и тд
      4) по найденному запросу догадаться, на какой части сайта он используется и найти соответствующий кусок кода
      5) начать действовать

      Конечно, slow log хорош, но он реально полезен только тогда, когда есть откровенно «тормозящие» запросы. Даже ситуацию с тем, что запросов очень много (и они относительно простые), и поэтому не попадают в slow log этот подход уже не решает (при этом, скажем, этот запрос прекрасно виден в SHOW FULL PROCESSLIST через раз).

      В моей статье я пытался описать действия, которые можно ещё попробовать, когда стандартные и известные средства плохо помогают (счетчик числа/времен запросов на странице, slow log, авто-explain, авто-show profile, анализ с помощью SHOW FULL PROCESSLIST и наблюдения, «за что глаз зацепится», ...). Понятно же, что есть стандартные средства для диагностики запросов, просто их часто бывает недостаточно, и приходится выдумывать такие фокусы, которые предоставляют, по сути «живой» slow log прямо на странице.

      Для более-сложных запросов, из моего опыта, временами блокировок/кеширования/построения плана запроса/оптимизации запроса можно пренебречь, хотя их в любом случае нужно тоже считать вместе с временем исполнения запроса, чтобы получить полную картину. В общем, slow log — это хорошо, но не всегда это позволяет решить конкретную задачу — ускорить исполнение страницы на сервере, а не просто найти все «медленные» запросы.
      • 0
        Аргументация понятна. Но, согласитесь, место ей в начале статьи, а не в подвале коментов.
        • 0
          Соглашаюсь, из моей статьи это могло быть не совсем ясно, что все «стандартные» способы тоже существуют, и что нужно пробовать именно их в первую очередь, а потом уже, если не получилось (а вероятность этого весьма ненулевая), читать мою статью :). Я просто сделал предположение, что люди, которые будут на хабре читать эту статью, уже всё, что знали, перепробовали, и надеятся здесь увидеть что-нибудь ещё, чего они не знали до этого. И, судя по количеству добавлений в избранное, я действительно смог рассказать что-то новое для большого количества людей, которые используют MySQL :)
      • 0
        Для поиска кода, из которого пришел запрос, есть очень простой прием: где–нибудь на нижнем уровне вашего ОРМ добавляете код, который будет автоматически комментировать запросы. В комментарии можно добавить все что угодно: название метода, глобальный счетчик запросов, кусок стектрейса.
        Конечно, это не очень хорошо повлияет на QC (одинаковые данные могут кешироваться для разных запросов), но и никто не говорит, что это надо делать постоянно. Можно периодически включать в дебаг режиме для одного из слейвов.
  • 0
    EXPLAIN вроде как ни разу мне не врал. Единственное это то, что по какой-то причине индексы странно себя ведут порой. То есть, например, делаешь EXPLAIN и он показывает что-то похожее на ваши результаты, потом делаешь OPTIMIZE TABLE и он начинает показывать ожидаемо вменяемый результат. Ну и до того как делаешь OPTIMIZE, Cardinality у индексов выдаёт неверное значение. Отчего не знаю, как избавиться тем более, поэтому просто периодически делаю OPTIMIZE.
    • 0
      Это связано с тем, что в InnoDB статистика перестраивается при OPTIMIZE TABLE, а до этого момента она может быть сильно испорченной частыми апдейтами.
  • +2
    Есть несколько простых советов, которые подходят для большинства проектов: как достичь высокой производительности. Скорее всего, чтоб их раскрыть — это тема отдельной статьи.
    Первое, надо по возможности исключить все JOIN, используем денормализацию.
    Во вторых, кешируем все справочники и неизменные таблицы, не надеемся на Кеш Запросов. Эти данные вытеснятся другими пользовательскими данными. Склейку данных делаем на клиенте.
    Третье — предварительная подготовка данных. Используем очереди и бэкграундовские процессы.
    Четвертое — большие порции данных бьем на маленькие части (иногда это называют шардинг).
    Пятое — для доступа к данным на чтение используем HandlerSocket, так же можно использовать на обновление данных, на вставку не советую. Не забываем, что Autoincrement в HS работает не так как бы нам хотелось.

    Используя эти пять простых принципов, у меня ни когда БД не была узким местом.
  • +3
    Не сочтите за рекламу:
    много полезного можно найти в этой книге
    цена в books.ru 90 руб
  • 0
    Все это, конечно, хорошо, но магия работает только в том случае если на сервере вообще больше ничего не исполняется. Т.е. на продакшне да с более-менее интенсивной загрузкой этот метод зачастую будет показывать сколько лет бабушке Пушкина вместо реальных данных. Хотя метод, конечно же, интересный.
  • +1
    Метод был хорош — но уже морально устарел (для иннодб таблиц удобнее смотреть значение innodb переменных). Если граммотно настроить performance_schema то можно снимать статистику по запросу в режиме рельного времени и, если понимать какие строчки кода что обозначают, можно докапаться не только до истины, что у вас слишком активно читается табличка 1...0 раз, но и понять почему её нет предположим в кэше, а так же что более важно обнаружить другие более узкие места, которые не показываются в сессионных переменных.
    • 0
      вот про это бы и отдельную статью!
    • 0
      Кстати, забыл ответить — performance_schema ведь появилась только начиная с MySQL 5.5, а она используется далеко не везде и далеко не всеми (причём по вполне понятным причинам, ибо в ней довольно много багов)
  • +1
    Странно что вы обращаете внимание сразу на количество строк. По-моему, первостепенным является нахождение запросов у которых происходит using filesort, using temporary table и using fullscan (в порядке уменьшения приоритетов) по мнению неточного Explain-а и преобразование их в такой вид, чтобы было using index. Это, ИМХО, решает примерно 70% потери производительности. Дальше уже можно делать профайлинг, изучать количество строк, убирать из выборки лишние столбцы, кешировать, денормализовать структуру и т.д.

    Тем не менее за ваш магический метод спасибо, не знал про него. Изучу обязательно.

    P.S. Я обычно выдаю предупреждения на dev-сервере как раз если в Explain-е встречается filesort и temporary table. Когда одновременно, предупреждение жирное :)
    • –1
      Я рад, что смог помочь самим (хоть и бывшим) разработчикам Хабра :). Очень приятно, правда
  • +1
    Запросы

    SELECT * FROM some_table WHERE some_field = 100500 ORDER BY id LIMIT 100

    и

    SELECT * FROM some_table WHERE some_field <> 0 ORDER BY id LIMIT 100

    принципиально разные. И если у нас есть индекс по полю some_field, то как раз в первом запросе не надо прочитывать 100500 строк, во втором — надо, так как условия там заданы разные. Или я чего-то крупно не понимаю?
    • –1
      Вы правы, в первом случае при наличии индекса запрос не займет значительного времени, даже при сотнях миллионов записей(ох, был у меня проект со статистикой...). Индексы — наше все!:)
    • –1
      Товарищи, которые плюсуют этот комментарий, прочитайте описание структуры таблицы. Где вы там увидели индекс на some_field?
      • 0
        Хм, действительно, в описании указан только индекс первичного ключа. В таком случае непонятно, почему «буду индекс праймаре использовать», так как он для поиска по some_field совершенно не нужен.
        • –1
          Потому что стоит ORDER BY id
          • 0
            В таком случае, лучше бы Вы реальный вывод EXPLAIN привели, а не свою интерпретацию. Потому что есть поле possible_keys, и есть key, и они имеют разный смысл.
            • 0
              Вывод будет такой (за исключением названия таблицы):


              Предполагается, что читателю очевидно, какой будет EXPLAIN, либо ему будет интересно проверить это самому (ибо утверждения действительно не совсем тривиальны). В обоих случаях реальный EXPLAIN не является необходимым

Только зарегистрированные пользователи могут оставлять комментарии. Войдите, пожалуйста.