Пользователь
0,1
рейтинг
4 сентября 2008 в 13:24

Разработка → MySQL Performance real life Tips and Tricks

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

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

В данной статье опишу несколько приемов, которые были использованы для приложения с 4млн+ пользователей и которое имея порядка 100млн+ хитов в сутки, а в конце опишу задачу, которая решалась недавно и может быть многоуважаемое сообщество предложит мне решения этой задачи более эффективное нежели то, к которому пришел я.


Для больших таблиц в MySQL, которые содержат сотни тысяч, миллионы записей часто бывают критичными запросы на GROUP BY. Т.к. в большинстве случаев если мы посмотрим в explain этого запроса, то увидим в поле Extra — Using temporary; Using filesort
Например:

explain
select
    *
from
    `tags`
group by
   tag_text;


* This source code was highlighted with Source Code Highlighter.


Extra — Using index; Using temporary; Using filesort
Т.е. для группировки используется временная таблица, которая потом еще и сортируется, причем сортировка происходит без использования каких-либо индексов.

Вообще если в запросе присутствует GROUP BY, то MySQL всегда будет сортировать результаты. Если порядок выдаваемых результатов нам не важен, то лучше избавиться от данной опперации (сортировки). Сделать это можно при помощи добавления — «order by null» к запросу. Итого получаем

explain
select
    *
from
    `tags`
group by
   tag_text
order by null;


* This source code was highlighted with Source Code Highlighter.


Extra — Using index; Using temporary;

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

Например такой запрос будет выполняться значительно быстрей предыдущего
select
    *
from
    `tags`
group by
   crc32(tag_text)
order by null;

* This source code was highlighted with Source Code Highlighter.


Инногда достаточно большой проблемой является LIMIT в запросах, я не буду тут говорить, что некоторые вытягивают 100 записей, а иногда и 1000 если реально используют 10; скажу следующее — польза от лимита есть только тогда когда в запросе используется индекс по полю, которое сортируем, т.к. в противном случае Using temporary; Using filesort нивелируют всю пользу от лимита. Также стоит избегать лимитов следующего вида LIMIT 1000000, 25 т.к. выбраны все равно будут 1000025 записей, и только потом 1000000 отброшен. Такое часто используется для pagination, и многие программисты часто оправдываются тем, что пользователи все равно в основном ходят на новые страницы (последние в хронологическом порядке), т.е. запросы с такими лимитами выполняются достаточно редко… Да, пользователи заходят на страницы годичной, двухгодичной давности не часто, но если на сайт зайдет поисковый бот, то он зайдет на все страницы, и этот бот, индексирующий контент сайта, положит нам сервер БД.
Решением такой проблемы, как правило, является превычисление результатов на предыдущей странице, например последний id записи на прошлой странице, и лимит в запросе будет выглядеть как-то так
WHERE bla-bla AND NODE_ID > id_from_previous_page ORDER BY NODE_ID LIMIT 25
ну или как-то так
WHERE Position BETWEEN 1000 and 1025
эти конструкции гораздо быстрей чем LIMIT 1000, 25

Решение данной проблемы предложенное %rumkin% относительно данной проблемы.
Вопрос с постраничной навигацией с учётом современной не любви к лишним GET-запросам можно решить так:

SELECT
    *
FROM
   `table`
WHERE
   id>X*Y-1
LIMIT
    X;
* This source code was highlighted with Source Code Highlighter.


Где X — количество результатов на странице, Y — номер текущей страницы. Так мы обойдёмся без предзапросов, GET-запросов и прочих усложнений.

Некоторые предлагают для решения подобных задач использовать горизонтальный
sharding(partition) Но для решения проблемы с pagination это ИМХО изврат тот еще… Хотя о шардинге позже…

Также некоторые думают, что конструкция SQL_NO_CACHE SQL_CALC_FOUND_ROWS быстрей 2х запросов первый c LIMIT, а второй select count(*)
Здесь вы прочтете разоблачение этой легенды

http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-o-sql_calc_found_rows/

Про расстановку индексов писать не буду. Об этом много было сказано, напишу только о том, что пока не нужно полагаться на алгоритм index merge и по возможности его заменять композитными(составными индексами по нескольким полям), об этом вы можете прочитать здесь подробней

http://www.mysqlperformanceblog.com/2008/08/22/multiple-column-index-vs-multiple-indexes/

http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.html

Либо иногда уместно заменить на запрос с UNION (а точней с UNION ALL, т.к. UNION это сокращение от UNION DISTINCT и поэтому когда мы результаты двух запросов объединяем с помощью UNION, данное объединение будет работать медленней, происходит это не потому что UNION ALL в отличие от UNION не использует временную таблицу. На самом деле использует, только не признается (в смысле в explain не показывает), это можно увидеть только с помощью show status. Дело в том что UNION ALL создает временную таблицу без UNIQUE KEY, а UNION DISTINCT с — отсюда и разница) Поэтому иногда запрос подобного вида может стать оптимальным вариантом

select
   *
from
   `table`
where
   first = 'A'

UNION ALL

select
   *
from
   `table`
where
   second = 'B' AND first != 'A'

UNION ALL

select
   *
from
   `table`
where
   third = 'C' and second != 'B' AND first != 'A'

* This source code was highlighted with Source Code Highlighter.


нежели

select
   *
from
   `table`
where
    third = 'C' OR second = 'B' OR first = 'A'

* This source code was highlighted with Source Code Highlighter.


Да, еще о чем хотел написать, так это о покрывающих индексах(covering indexes), точней о запросах, которые их используют.
Вкратце в чем суть — мы работаем в запросе, т.е. используем в качестве условия (WHERE) и возвращаем (SELECT) только поля, которые входят в один составной индекс. Итого — все что мускулу нужно пробежаться по дереву индексов и вернуть результат. Индексы лежат в памяти, в данные на диск мы не лезем, все здорово и быстро.
Классический пример этого это запрос вида

SELECT user_password FROM `users` WHERE user_name = 'maghamed';
имея индекс по полям (user_name, user_password)

Приведу более нестандартный пример на эту тему для закрепления материала :-)

   SELECT
      `log`.visitor_id, `url`.url
   FROM (
      SELECT
         id
      FROM
         log
      WHERE
         ip=”127.0.0.1”
      ORDER BY
         ts DESC
      LIMIT 50,10
   ) l
   JOIN log
      ON (l.id=log.id)
   JOIN url
      ON (url.id=log.url_id)
   ORDER BY
      TS DESC;

* This source code was highlighted with Source Code Highlighter.


Хоть этот запрос и выглядит ужасающе, но если у нас есть covering index по полям (IP,TS,ID), подзапрос derived query будет использовать именно его и выполнится очень быстро, включая лимит в нем, проход для лимита будет осуществлятся тоже используя только индекс. После этого делаем self-join чтобы подключить оставшиеся поля таблицы (visitor_id)



Ну и напоследок опишу один случай, который не так давно представилось решать.
Вобщем есть сайт-блоггер, где пользователи пишут статьи, новости на разные темы, вобщем что-то вроде хабра, только более гражданской ориентации :-)
Нужно было создать систему треккинга для авторов, что-то вроде google analytics. Чтобы авторы статей могли видеть кто, откуда и когда переходит на их статьи. Т.е. статистика должна быть по общему кол-ву просмотров за определенное время, статистика рефереров с разных сайтов, и статистика наиболее популярных запросов по которым пришли пользователи из поисковиков на данный пост.

Вся эта статистика собирается в лог и каждый час по крону переносится из лога в БД, после чего создается новый файл лога и статистика за следующий час пишется туда.

Итого что есть:
статьи (назовем их entries, имеют свой ИД)
собираются и обрабатываются реферреры на каждую статью + фразы, по которым люди приходят из поисковиков
какие отчеты надо формировать:
просмотры за последний час, два, 6, 12, сутки, неделю
поисковые фразы на каждую статью за те же интервалы
реферреры на каждую статью за те же интервалы
самые «популярные» поисковые фразы за те же интервалы
самые «популярные» реферреры за те же интервалы

Существовало решение, которое работало исправно до определенного момента, но после того как кол-во посещений сайта резко возросло — работать стало медленно

Здесь приведена структура таблицы для отчета по общей статистике, приблизительно такие же таблицы существуют для статистики рефереров и поисковых фраз

CREATE TABLE `mt_daily_entry_stats` (
 `daily_entry_stats_entry_id` INTEGER(11) UNSIGNED NOT NULL,
 `daily_entry_stats_views` INTEGER(11) UNSIGNED NOT NULL,
 `daily_entry_stats_date` DATETIME NOT NULL,
 PRIMARY KEY (`daily_entry_stats_entry_id`, `daily_entry_stats_date`),
 KEY `daily_entry_stats_date` (`daily_entry_stats_date`)
)ENGINE=InnoDB
* This source code was highlighted with Source Code Highlighter.


Ну и соответственно к данной таблице в зависимости от выбранной статистики выполнялись запросы вида:

SELECT
  `stats`.`daily_entry_stats_entry_id`,
  SUM(`stats`.`daily_entry_stats_views`) as `entry_stats_views`
 FROM
  `mt_daily_entry_stats` as `stats`
 WHERE
  `stats`.`daily_entry_stats_date` > NOW() — INTERVAL 24 HOUR
 GROUP BY
  `stats`.`daily_entry_stats_entry_id`
 HAVING
  `entry_stats_views` > 1000
* This source code was highlighted with Source Code Highlighter.


Это решение стало работать медленно и нужно было разбираться почему.

вот Explain этого запроса

select_type: SIMPLE
table: stats
type: range
possible_keys: daily_entry_stats_date
key: daily_entry_stats_date
key_len: 8
ref: NULL
rows: 97644
Extra: Using where; Using temporary; Using filesort

1. так используется Engine=InnoDB, значит используются сурогатные ключи, которые хранятся непосредственно в одном файле с данными (в отличие от MyISAM где индексы хранятся в отдельном файле), более того данные отсортированы по этому сурогатному ключу и он входит в состав остальных ключей, поэтому очень важно, чтобы PRIMARY KEY был как можно меньше и поэтому запросы к таблицам на InnoDB с использованием PRIMARY KEY выполняются очень быстро.

Что получается у нас: есть составной ключ PRIMARY KEY (`daily_entry_stats_entry_id`, `daily_entry_stats_date`)
который занимает 4 байта (инт) + 8 байт (дэйттайм) = 12 байт

2. Т.к тип данных DATETIME занимает достаточно много места (8 байт), то вероятно более целесообразно даты, которые попадают в интервал 1970 — 2038 лучше представлять в TIMESTAMP. Но исходя из строчки в мануале
+ TIMESTAMP values are converted from the current time zone to UTC for storage, and converted back from UTC to the current time zone for retrieval. Т.е. выполняются 2 дополнительные опперации при сохранении и извлечении даты. То лучше всего в данном случае дату хранить в INT

Во время решения было предложено много вариантов решения проблеммы. Различное кеширование, создание временной инмемори таблицы содержащей результаты, чтобы кверили именно её.

Даже экспериментировал с шардингом. Но шардинг в этом случае оказался тоже малоэффективен, т.к. времена отчетов перекрываются, например отчет за 12 часов включает в себя данные отчета за 6 часов. Поэтому отчеты за большие периоды также требуют данных из нескольких шардов, причем возможность партишига (вырожденного шардинга, исправлено по просьбе %andry% ) поддерживается в версии 5.1, которая еще не релиз, поэтому все осталось только попытками.

После проб различных вариантов пришел к варианту с денормализацией. Т.е. было решено создать несколько таблиц, для каждого из интервалов статистики — stats_hour, stats_2hour, stats_6hour, stats_12hour, stats_day
тогда ты избавиться от GROUP BY ...HAVING = WHERE.

И соответственно, когда нужно получить статистику по какому-то интервалу времени кверим нужную нам таблицу.
Правда возросло кол-во таблиц для статистики. И данные хранятся избыточно. Но вместе с кешированием (memcached) вполне работоспособно.

Причем добавить/изменить существующие интервалы достаточно просто, нужно лишь внести изменения в крон-скрипт, который заполняет эти таблицы.
Мое решение не претендует на самое лучшее, было бы интересно как подобную задачу решали бы Вы.
Надеюсь прочитать это в комментах :-)

Игорь Миняйло @maghamed
карма
237,4
рейтинг 0,1
Пользователь
Реклама помогает поддерживать и развивать наши сервисы

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

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

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

  • +4
    Могу добавить что от использования GROUP BY в mysql можно и нужно отказываться.
    Так же надо строить систему так, чтобы БД надо было просматривать ровно столько рядов, сколько она будет возвращать при запросе. Это можно сделать всегда, кроме поиска по LIKE с начальным %.
    Ну и так же заметил несколько неточностей, например фраза «Индексы лежат в памяти, в данные на диск мы не лезем» — неправда, ибо индексы также лежат на диске. То есть я понимаю, что хотел сказать автор. Он прав, но лучше не смущать новичков неточностями :)
    • +7
      По поводу запроса LIKE с начальным %. Есть случаи, когда мы, например, записывает в БД почтовые ящики клиентов. И тогда вместо запроса

      WHERE mail LIKE '%@gmail.com'

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

      WHERE mail LIKE 'moc.liamg@%'

      • +5
        Проще разбивать ящик на два поля — домен и юзернейм, и индексировать по домену.
        • 0
          Еще шустрее будет работать данный запрос, если сделать отдельную таблицу для доменов, а в таблице пользователей E-mail хранить в виде двух полей mail_username и mail_domain_id.

          Тогда вместо WHERE mail LIKE '%@gmail.com', WHERE mail LIKE 'moc.liamg@%' или WHERE mail_domain = 'gmail.com' будет очень быстрый поиск по целочисленному индексируемому полю, типа WHERE mail_domain_id = 23 (конечно, придется получить этот mail_domain_id из таблицы доменов, но это будет разовый очень быстрый поиск по уникальному ключу-доменному имени).

          Конечно, строить такую сложную структуру имеет смысл только в том случае, если пользователей — миллионы. Но если это так, то данная схема оптимальна IMHO.
      • +1
        Я на самом деле говорил про то, что это иногда нельзя сделать. В некоторых конкретных ситуациях действительно можно сделать хорошо.
        Кстати в данном случае можно не делать отдельного поля и его индексировать, а просто создать индекс по тому же полю, только DESC.
        • –8
          А еще быстрее и универсальнее хранить все мыла в отдельных строчках в текстовом виде в памяти, затем искать там вхождение подстроки функцией наподобие strpos(), а затем найденную строку подставлять в запрос where mail='asd@gmail.com' )

          strpos работает напорядок быстрее чем like '%qwe%'
          • 0
            Это где вы такое взяли, прочли?
            Если по полям считать — забудьте про индексы!
            • 0
              да не, считать по полям не надо. как то у меня была такая задача сделать быстрый SELECT WHERE row like '%str%', решил так — сделал раздел на memoryfs, столбец row выкинул в файл примерно такой:
              — qqwwee
              aassddffggh
              hsfgfgfgfg

              drtgfdgdfg
              — теперь like '%ddf%' можно сделать так:

              while not eof
              {
              readln();
              if (strpos($line, $ddf)) then $result[]=$line;
              }

              затем заменить like на WHERE row='$result[1]' OR row='$result[2]'. Тут понятно что на row висит normal индекс.
    • +1
      Вы совершенно правы и насчет group by тоже.
      Спокойно можно сделать архитектуру не используя group by.
      А фраза трактовалась скорее всего так: если первый запрос воспользовался индексами n..., то они теперь в кеше :)
      Кстати действительно, если идут много запросов друг за другом, лучше всего оптимизировать так, чтобы использовались одни и те же индексы.

      P.S. Не по теме…
      Да, реально вас в минус запустили, хоть в комментах одни плюсы…
      Тролли вам войну обьявили…
      • 0
        Да, именно так и трактовалась

        >А фраза трактовалась скорее всего так: если первый запрос воспользовался индексами n..., то они >теперь в кеше :)

        я думал это понятно :-) Впредь буду осторожней выбирать выражения.

        И в остальном с Вами согласен.
  • –1
    Спасибо, познавательно ;)
  • +3
    Надеюсь вы продолжите цикл статей этой тематики. Ждем продолжений.
    • +31
      Честно-говоря планировал завтра уйти в отпуск. Но когда вернусь, обещаю написать еще что-нибудь по данной тематике, если это, конечно, будет интересно сообществу.
      • +14
        По-моему, это мой начальник меня заминусовал за этот коммент :-))) Но я все равно уйду! :-)
      • 0
        Будет интересно!
      • 0
        Пишите ещё, интересно.
  • +2
    Отличная статья! Респект и уважуха.
    Проверил тему с SQL_CALC_FOUND_ROWS 2х милионной таблице.
    Разница между одним и 2мя запросами действительно есть, но не очень большая.
    про покрывающие индексы — оч круто. Не знал.
    Сортировка по null — тоже красиво.
    Про юнионы — как-то не очень.
    Мысль про избыточные таблица интересна.
    В общем ещё раз спасибо :)
    • +1
      Спасибо, рад стараться, когда это кому-то полезно.
  • +1
    У нас сделано примерно также: есть таблицы с чистыми данными, периодически вызываются хранимки, которые их агрегируют по разному, считают, и уже подсчитанные репорты кладутся в таблицы daily_stats_by_* и другие.

    Т.к. в основном все упирается в различные условия для GROUP BY есть идея такого варианта с денормализацией. Например, есть в таблице с сырыми данными поле date. Добавляем еще несколько полей, которые будут содержать значения от различных функций для группировки: hour, week, month и т.д. Создаем для каждого из таких полей индексы, можно покрывающие. И после этого, мы можем делать не GROUP BY MONTH(date), а GROUP BY month.

    Но я еще не тестировал, может и фигня получится.

    PS: Кстати, судя по документации, в PostgreSQL можно создавать индексы не только на поля, но и на значения выражений. Здесь можно и без доп. полей обойтись, просто сделав соответствующие индексы.
    • 0
      >Кстати, судя по документации, в PostgreSQL можно создавать индексы не только на поля, но и на значения выражений. Здесь можно и без доп. полей обойтись, просто сделав соответствующие индексы.

      Не знаю как в PostgreSQL, но в Oracle точно можно.
  • 0
    Сейчас быстро нахожу и исправляю грамматические и пунктуационные ошибки у себя в статье. Просто написал, и самому стыдно :-( Спасибо, людям, которые мне в этом периодически помогают в скайпе и аське :-)
  • НЛО прилетело и опубликовало эту надпись здесь
    • +4
      может они так сервер разогревают? типа warmup :-)
    • +1
      всё зависит от задачи. Если задача «одноразовая», а запросы простенькие, то можно и в цикле замутить, чем геморно это обходить =)
  • 0
    хорошее начало — имху стоит продолжать столь интересную и нужную тему.
  • 0
    отличная статья, спасибо. Последнюю часть правда я так не понял… получается 5 таблиц в которых надо добавлять данные и прибивать старые данные каждый час + избыточность… не проще было мемкешить данные по более простым выборкам?

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

    select article_id from articleы where author_id = 1 (эту часть можно держать в мемкеше уже)

    foreach (......) {

    select count(*) from article_hits where article_id =… (а в таблице индекс по полю article_id)

    }

    по идее это должно работть очень быстро как только индекс попадет в кеш.

    ась?
    • 0
      то что в foreach соотв тоже можно засунуть в мемкеш… хотя получается избыточное дергание базы конечно…
    • 0
      Да, первая часть, как Вы написали действительно кешируется в мемкеш

      select article_id from articleы where author_id = 1 (эту часть можно держать в мемкеше уже)

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

  • 0
    Может стоит вам как специалисту написать пару статей «best practice» MySQL, как нужно делать правильно и не делать неправильно )
    • 0
      Честно-говоря, я не считаю большим специалистом, и считаю, что есть много людей, которые разбираются лучше меня — Питер Зайцев, например :-)
      • 0
        Ну судя по статье у вас есть некоторый опыт, чтобы советовать как нужно делать то или иное для увеличения производительности.
      • –1
        А почему он не Пётр? :)
        • –1
          Ну когда приезжает на конференции в Россию — тогда он Пётр, а когда на Google talks выступает, тогда Питер :-)
          Все зависит от того на какой манер произносить :-)
          • 0
            Вы же писали по русски :)
            • 0
              Вы меня поймали :-) Признаю свою ошибку :-)
      • 0
        Есть ли кто-то, разбирающийся в теме лучше чем вы — это другой вопрос.
        Я же вижу, что вы разбираетесь гораздо лучше, чем я :) Поэтому ваш опыт мне интересен…
        • +1
          Договорились, значит я продолжу писать на эту тему. Уже и идея есть о чем писать. И код, просмотренный за последнее время, дает много поводов и примеров написать о том как писать не следует :-) Да и с отпуском у меня все таки не сложилось как оказалось :-(
  • +4
    Вот такие статьи и должны быть на хабре, вместо кучи новостей обнообразных и часто фанатичных. Плюсанул везде где смог =)
  • +1
    Вы уверены, насчет crc32?
    запрос select * from `tags`group by crc32(tag_text) order by null;
    не будет использовать индекс по tag_text (правда я не понял что вы имели ввиду — что в этом поле — text или например varchar(1000)?)

    В любом случае правильнее сделать еще одну колонку с crc32, заполнять ее при вставке и группировать по ней, а не выполнять это при SELECT. (разумеется если приоритет у выборок, а не вставок)
    • +1
      >В любом случае правильнее сделать еще одну колонку с crc32, заполнять ее при вставке и группировать по >ней, а не выполнять это при SELECT. (разумеется если приоритет у выборок, а не вставок)

      Да, иногда это удобно, особенно вместо того чтобы создавать составной индекс по нескольким полям (который будет достаточно большой), можно создать по одному полю, например используя ф-ию MD5 (128 бит, запись состоит из 32 символов) или если мы хотим подстраховаться больше то лучше использовать Sha1 (160 бит, 40 символов) нежели CRC32 (32 бита, беззнаковое число)

      И соответственно запросы примут вид

      SELECT
          *
      FROM
         tbl_name
      WHERE
        hash_col=MD5(CONCAT(col1, col2))
        AND col1='constant'
        AND col2='constant'; * This source code was highlighted with Source Code Highlighter.


      так просто большая вероятность того, что значения CRC32 совпадут, особенно если в таблице много записей.
      Ну и соответственно индекс поставить не на все поле, а на какую-то его часть.

      >правда я не понял что вы имели ввиду — что в этом поле — text или например varchar(1000)?)
      например URI (varchar(100))

      >Вы уверены, насчет crc32?
      Да :-)
      • 0
        Проверю, так как теоретически должно быть наоборот :)
        Вы на каких объемах/типах данных сей эффект наблюдали в реальности? и на каком железе (CPU, Memory)
        у меня есть пару баз по 3-4 гига text-полей, там такого не наблюдалось, но может чего не учел…
        • 0
          Наблюдал его на объемах данных порядка 2 млн. записей. Тип — варчар(255). Выделенный сервер БД, 16 ГБ оперативной памяти. Про процессор врать не буду — не помню.

          Это решение у нас тогда не прижилось т.к. в некоторых значения был замечен очень высокий уровень коллизий. После исследования было обнаружено, что GROUP BY берет 2^32 как максимальное число для группировки. Не помню какая тогда стояла версия мускула на продакшене, т.к. было это пол года назад. 5.0*. Но потом оказалось, что если приводить тип к BINARY, то и это можно обойти

          как-то так
          mysql> SELECT tag, COUNT(*) AS count, crc32(tag), BINARY crc32(tag)
          -> FROM tags
          -> GROUP BY BINARY crc32(tag)
          -> ORDER BY count DESC
          -> LIMIT 10
          ->;
          +————+——-+————+——————-+
          | tag | count | crc32(tag) | BINARY crc32(tag) |
          +————+——-+————+——————-+
          | spanish | 4576 | 874050868 | 874050868 |
          | vocab | 4103 | 1178479308 | 1178479308 |
          | vocabulary | 2786 | 2147483647 | 2425997691 |
          | french | 2247 | 2147483647 | 2943733342 |
          | english | 2087 | 746783232 | 746783232 |
          | science | 1957 | 1729573288 | 1729573288 |
          | latin | 1411 | 1421320458 | 1421320458 |
          | chapter | 1274 | 2147483647 | 4186027310 |
          | history | 1171 | 666529867 | 666529867 |
          | words | 939 | 1904025228 | 1904025228 |
          +————+——-+————+——————-+

          но было уже поздно, и мы воспользовались другим решением.

          кстати, заодно и ссылку нашел, которая косвенно подтверждает мои слова.
          www.mysqlperformanceblog.com/2008/03/07/speeding-up-group-by-if-you-want-aproximate-results/#comment-272067
    • +1
      а разве varchar(1000) допустимое значение? я всю жизнь думал, что только varchar(255)…
      • +2
        в MySQL 5.0.3+ — да varchar может быть длиной до 65,535
        • 0
          я 5-ю версию не изучал :(, только с 4-кой работаю. спасибо, за информацию. как всегда, узнаю на хабре много нового.
          • 0
            Уже давно пора начать изучать :)
      • +1
        Вы правильно думали, дальше идет tiny text. Это не я про varchar(1000) написал, но думаю, что
        * maxshopen просто описался в лишнем нуле :-)
        • +1
          как оказалось не описался :-) Спасибо, я лично не знал :-)
  • 0
    а кто нить пробовал компилировать mysql с использованием pgcc? Действительно ли производительность повышается?
    • 0
      хотите поэксперементировать — попробуйте Bazaar
      dev.mysql.com/tech-resources/articles/getting-started-with-bazaar-for-mysql.html

      А потом не забудьте написать об этом статью :-)

      Но зачастую все такие эксперименты остаются экспериментами и на продакшн сервер их не поставишь :-(
      • 0
        Не совсем понял причем тут сие. В некоторых статьях говорилось о том, что используя pgcc можно добится прироста производительности до 30%.
        • 0
          Последнее обновление pgcc было 2000-12-27, не думаю, что его разумно использовать. Основной прирост дают использование UNIX-сокетов, вырезание ненужных кодировок, оптимизация под архитектуру процессора (march)
  • +1
    Интересно а как быть с ORM системами?
    Они точно не в состоянии так тщательно и специфично оптимизировать запросы. С другой стороны я не представляю как работать со сложной системой без ORM, а только запросами… Неужели для систем с такими нагрузками обязательно приходится изобретать свои «велосипеды», отказываясь к примеру от Hibernate?
    Автор, Вы используете ORM? И что вы о них думаете?
    • 0
      в active record можно реализовать кое что из описанного в статье. на счет всего не уверен, в частности насчет union. Но вот order by null точно =)
    • 0
      Использование Hibernate не исключает возможности использовать «tips and tricks» описанные в данной статье.
      Просто когда пишешь на HQL надо хорошо понимать в какой SQL это превратится.
      Соответственно, и архитектуру БД и маппинг тоже надо делать с пониманием.
    • +1
      Наконец вернулся домой. И могу Вам ответить. На самом деле я работаю PHP программистом, а не Java. И использую другие ORM нежели Hibernate. Я использовал Propel, Doctrine и т.д.
      Не в этом дело. Просто не всегда следует использовать продукт как есть as is. Yahoo, например, использует symfony PHP framework, который юзает ORM Propel (кстати, мне нравится как Symfony так и Propel :-)) для своей answers.yahoo.com/ со 135 миллионами пользователей habrahabr.ru/blogs/symfony/25040/. Но они переписывали какие-то части под себя, оптимизировали. На самом деле ОРМ нужен не во всех проэктах, например, в небольших точно не нужен, лишние трудности. А большой проект, который будет писаться и поддерживаться несколько лет разными программистами, возможно в разных странах… Тут гораздо удобней поддерживать код, написанный с помошью ОРМ.
      • 0
        Спасибо за ответ и за статейку

        я тоже считаю что ORM нужен в больших проектах, где не три таблицы, но с другой стороны именно в таких проектах нужна бОльшая эффективность так как нагрузки там соответствующие. А в ORM out-of-the-box с оптимизацией такого специфического уровня туговато.
  • 0
    а может стоит сделать поля час, день, месяц, год?
    тогда выборка будет происходить быстрее чем с полями времени.
  • +1
    Вопрос с постраничной навигацией с учётом современной не любви к лишним GET-запросам можно решить так:

    SELECT *
    FROM `table`
    WHERE id>X*Y-1
    LIMIT by X;

    Где X — количество результатов на странице, Y — номер текущей страницы. Так мы обойдёмся без предзапросов, GET-запросов и прочих усложнений. Если не трудно, добавьте в текст. Заранее спасибо.
    • +1
      Да, добавил, конечно не сложно, спасибо
    • +1
      наверно, все-таки имелось в виду
      WHERE id>X*(Y-1)

      PS плюс такая штука не правильно будет работать, если записи в таблице могут удаляться.
      • 0
        Ага. Что бы работала такая конструкция нужно в отдельную таблицу вынести индекс записей с порядковыми номерами, но это будет умножением сущностей…
    • +1
      Такой финт не будет работать, если есть дополнительные фильтры на таблицу, тогда id не будет порядковым номером. А дополнительные условия-фильтры на таблицу есть в большинстве случаев.
  • +2
    Извините, все кому не получается сразу ответить. Я когда домой прийду обязательно напишу свое мнение по поводу ORM и больших проектов. По поводу Ваших предложений относительно возможных решений, поставленной задачи.
    Просто последний рабочий день перед отпуском, а работы выше крыши :-(
    И надо все успеть именно сегодня…
  • 0
    Категорически полезно. Страницу в закладки
  • +2
    Самое интересное было бы послушать специалистов по oracle или PostgreSQL с их примерами решения этой же проблемы.
    • +1
      Да, абсолютно с Вами согласен, постгрес особенно интересует.
    • 0
      Насчет оракла не скажу, а вот в постгресе эти трюки никому на фиг не нужны. Там используются генетические алгоритмы оптимизации запросов, которые дают если не самый лучший сценарий выполнения запроса, то один из.
      Если делать анализ таблиц время от времени.
      Хотя, может какие то из советов пригодятся и там…
  • +2
    Не проверял на практике, но мне кажется что предложенное решение для постраничного вывода
    SELECT *
    FROM `table`
    WHERE id>X*Y-1
    LIMIT by X;
    уместно лишь при отсутствии пропусков в значениях поля id, что редко имеет место быть на практике
    и условие вроде бы должно быть id > X*(Y-1) и указать что тип у id должен быть unsigned
  • 0
    а я бы еще про ms sql послушал :)
  • 0
    Отличный пост, спасибо!
  • НЛО прилетело и опубликовало эту надпись здесь
  • 0
    Автор поправьте!

    «причем возможность шардинга поддерживается в версии 5.1»

    это не совсем правда. Partitioning да, это так сказать вырожденный случай шардинга. С помощью первого можно раскидать по физическим дискам в пределах одной машины, а шардинг обычно предполагает, что данные попилены по разным машинкам. Не зря ведь придумал именно этот термин!
    • 0
      Извиняюсь, что только сейчас исправил, да я с Вами тут соглашусь.
  • 0
    Если приходилось самому реализовывать «group by», то утверждение, что добавление «order by null» к запросу с group by для увеличение скорости выглядит странным, т.к. быстрей групировать по построенному индексу, читай — отсортированному списку. На всякий случай спросил друга, который пишет ядро MySQL и он прокомментировал:
    — Ха! Он же всегда сортирует! А константное выражение в order вообще игнорируется. Единственное может есть баг, но это поправимо ;-) Но то, что будет работать быстре одно значно — нет.
    • 0
      Да, в мускуле порой творят странные вещи, порой даже такие, о которых не знают и его «авторы». Но я показал вам то, что я вижу когда делаю EXPLAIN запроса. И этот эксплейн показывает мне, что с добавлением «order by null» — Using filesort ушел. Честно говоря в этом случае я больше верю своим глазам. Когда прийду домой поищу в интернете есть ли ссылки в Интернете на что-то подобное.
      • +3
        Есть записьв мануале
        — … By default, MySQL sorts all GROUP BY col1, col2,… queries as if you specified ORDER BY col1, col2,… in the query as well. If you include an ORDER BY clause explicitly that contains the same column list, MySQL optimizes it away without any speed penalty, although the sorting still occurs. If a query includes GROUP BY but you want to avoid the overhead of sorting the result, you can suppress sorting by specifying ORDER BY NULL…
        dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html
        • 0
          Благодарю Вас, Вы подтвердили мои слова, так сказать, документально, плюс Вас за это :-)
        • 0
          М-да, за такие хаки
        • 0
          Спасибо за ссыку. М-да, за такие хаки и умолчания разработчикам по рукам нужно дать! Почему нужно делать то, что пользователь не заказывал?! Завтра же пожалуюсь ;-)
          Вообще, это довольно распространенная проблема а-ля Microsoft: Мы знаем лучше, что Вам нужно. Когда перед началом использования приложения приходится сначала все выключить, что не будешь использовать, вместо того, чтобы включить/добавить, если тебе когда-нибудь понадобится.
          • 0
            Пожалуйста.
            А сейчас похоже это модно, так строить логику приложений. Недавно приобрёл ноут, так там категорически не очевидно как отключить макафи. При этом програмка упорно лезет в интернет как только обнаруживает соединение, начинает что-то выкачивать и вообще не предлагает никаких вариантов натему отмены её действий, только убийство процесса помогает.
            «Жираф большой — ему видней.» (с) =)
      • +1
        Сортировка в GROUP BY нужна для того, чтобы использовался бинарный поиск в уже выбранных в процессе группировки данных, вместо их полного перебора. То есть, на больших объемах данных эта сортировка позволяет значительно ускорять группировку.

        Конечно, если в результате группировки возвращается малое количество записей, то в сортировке смысла мало. Но в любом случае, использовать хак ORDER BY NULL я бы не стал, если, конечно, он значительно не ускоряет выборку (а в этом я очень сомневаюсь что ускоряет, нужно проверять отдельно на каждом наборе данных).
  • НЛО прилетело и опубликовало эту надпись здесь
    • 0
      Спасибо, исправил
  • +2
    По поводу последнего запроса. Ускорить его можно, если первичный ключ в таблице поменять с:
    PRIMARY KEY (`daily_entry_stats_entry_id`, `daily_entry_stats_date`) 
    на
    PRIMARY KEY (`daily_entry_stats_date`, `daily_entry_stats_entry_id`) 
    В этом случае действительно будет использовано преимущество кластерного ключа, так как вся таблица будет упорядочена в первую очередь по дате и запрос будет сканировать индекс в одном «направлении». Я протестировал на таблице с 500k записей, если все нужные страницы находятся в памяти, разница в скорости не очень заметна, но если большинство страниц должно быть считано с диска, то запрос использующий новый первичный ключ отрабатывает примерно в 2.5 раза быстрее использующего просто индекс по дате. У меня получились следующие цифры:
    Использует новый первичный ключ, сканирует около 230732 строк — 0.6 секунд
    Использует индекс по дате — 1.7 секунд
    Но если периоды, по которым нужно собирать данные фиксированы, то агрегированные таблицы — то, что нужно.
    • 0
      Да, это, пожалуй, интересное решение по поводу смены PRIMARY KEY, нужно обязательно будет попробовать. После таких решений, порой, еще долго винишь себя, почему сам не предложил этого. Но Вам большое спасибо! И плюсы естественно :-)
  • 0
    Кстати насчет paging-a и LIMIT 10000000,10

    Совет, при большой нагрузке не заморачивайтесь с «полным» выводом кол-вом страниц…
    посмотрите на всего нагруженные сайты — все делают paging максимум на 10 страниц (оконным методом paging-a), для чего — как раз для того чтобы боты не положили БД и не кто не нагружал БД.

    А если хотите старые страницы — идите, извините в… поиск ;)

    Это касается для тех сайтов у кого кол-во топиков превышает 100`000.
    • 0
      По поводу того, что многие сайты показывают в пэйджинге не много страниц, чтобы боты особо не лазили.
      Я с Вами согласен

      >Совет, при большой нагрузке не заморачивайтесь с «полным» выводом кол-вом страниц…

      на самом деле, это вообще не проблема. Т.е. для мускула выполнить запрос count(*) особенно для таблиц MyISAM, HEAP и без условия WHERE

      т.е. запрос

      SELECT count(*) FROM `topic` — выполнится молниеносно.

      #
      mysql> SELECT count(*) FROM `topic`;
      #
      +----------+
      #
      | count(*) |
      #
      +----------+
      #
      | 8343532 |
      #
      +----------+
      #
      1 row IN SET (0.00 sec)

      Все потому, что данные энжины не поддерживают транзакций, и имеют table level для блокирования. И такие данные как колличество записей в таблице хранится в структуре-метаинформаци, которая всегда в кеше и возвращается оттуда очень быстро.

      В InnoDB такой запрос выполняется мендленей, т.к. нужно выполнить роускан или индексскан, это происходит от того, что в InnoDB row level locking

      запросы на count(*) c присутствием WHERE выполняются приблизительно одинаково в зависимости от данных, кол-ва, и условия

      • 0
        Это конечно все прекрасно при простом count, здесь я не спорю,
        но вот count с limit и условиями помощнее — ставят БД на колени.
  • +1
    Простой способ немного ускорить выполнение запросов с большими лимитами:

    Сначала найдем общее количество результатов:
    SELECT COUNT(*) FROM item WHERE [тут какие-то наши условия]

    Допустим, получилось 1015.

    Теперь, вместо:
    SELECT item_id FROM item WHERE… ORDER BY item_id LIMIT 1000, 15
    можно написать:
    SELECT item_id FROM item WHERE… ORDER BY item_id DESC LIMIT 0, 15

    И потом уже выбрать данные:
    SELECT… FROM item WHERE item_id IN ([массив из предыдущего запроса])

    Запросы из первой половины будут обычные:
    SELECT item_id FROM item WHERE… ORDER BY item_id LIMIT 200, 15

    Самые медленные запросы получатся посередине, зато чем ближе к концу — тем быстрее.
    • 0
      В данном случае происходит трижды запрос например со стороны php (хостинг пакупной — многое урезано в mysql — хранимки например — где это можно было бы завернуть в хранимку). Время вызванное этими отдельными запросами даст всё равно выйгрышь в производительности?
      • 0
        Если страниц много, то да, скорее всего. Но вообще конечно надо пробовать.
  • 0
    > Также некоторые думают, что конструкция SQL_NO_CACHE SQL_CALC_FOUND_ROWS быстрей 2х запросов первый c LIMIT, а второй select count(*). Здесь вы прочтете разоблачение этой легенды…
    Заметьте, что в тех примерах используется покрывающий индекс, а на практике такое бывает далеко не всегда и SQL_CALC_FOUND_ROWS бывает очень даже полезен. Он будет работать быстрее в 2 раза, чем вариант с COUNT(*) в запросах, в которых используется full table scan или группировки. В остальных случаях он тоже может быть полезен — тестируйте!

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