Pull to refresh

MySQL Performance real life Tips and Tricks

Reading time9 min
Views37K
Пообещал вчера написать статью о реальных случаях оптимизации БД 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) вполне работоспособно.

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

Tags:
Hubs:
Total votes 143: ↑139 and ↓4+135
Comments93

Articles