Решил продолжить цикл заметок по данной тематике. В данной статье особое место хотел уделить профайлингу MySQL запросов. Описать средства, которые предоставляются MySQL для профайлинга, и что нужно делать для определения узких мест запроса.
Также, после опубликования первых двух статей я получил пару отзывов и вопросов, связанных с проектированием БД / расстановкой индексов / составлением запросов. На многие вопросы старался отвечать. С некоторыми из них поделюсь и в этой статье.
Итак, начну со стандартных средств профайлинга запросов средствами MySQL
С помощью команды EXPLAIN MySQL показывает ориентировочный execution plan запроса (Описание всех возвращаемых полей командой EXPLAIN можно посмотреть по адресу — dev.mysql.com/doc/refman/5.0/en/using-explain.html ). На самом деле в этом определении ключевое слово — ориентировочный, т.к. EXPLAIN может ошибаться :-) Причиной данных ошибок есть то, что генерация execution plan является частью выполнения запроса (query execution), иногда построение execution plan происходит динамически в зависимости от данных. С помощью команды EXPLAIN MySQL пытается симулировать выполнение запроса, но не «трогает» при этом данные, соответственно не имеет доступа к этому динамическому компоненту. Такую оценку MySQL делает в основном полагаясь на статистику индексов. Которую в свою очередь следует держать всегда в актуальном состоянии, и в зависимости от интенсивности запросов к БД на добавление/изменение/удаление данных проводить с определенной переодичность по cron-у (скажем, каждую ночь) выполнять запросы — ANALYZE TABLE (перестраивает дерево индексов, не позволяя выраждаться ему в список, такое может происходить если мы, скажем, вставляем упорядоченные данные, тогда эффективность операции поиска записи составит не O(logn), а O(n). Замечу также, что не всегда следует выполнять команду ANALYZE TABLE на production сервере, т.к. на время её выполнения MySQL лочит таблицы MyISAM (read lock), InnoDB (write lock) ), OPTIMIZE TABLE (дефрагментация файла данных. Полезно при большом удалении записей из таблицы).
Также EXPLAIN дает далеко не всю информацию для оценки скорости выполнения запроса. Например, если в ходе запроса будут использоватся temporary table, то MySQL не покажет будут ли это in-memory или in-disk temporary tables. Также MySQL в эксплейне не покажет цену операций обращения к записи или выполнения функций используемых в запросе.
Также в этом абзаце хотел отметить главное — что производительность запроса зависит от количества записей, которые были проверены (исследованы) MySQL, а не от количества возвращенных запросом записей.
MySQL издавна предоставляет мониторинг системных переменных по средствам команды — SHOW STATUS. Ранее (до версии 4.1.) данная команда показывала статус глобальных переменных, но с версии 4.1. появилось возможность показывать состояние сессионных переменных — SHOW SESSION STATUS. (Мануал по данной команде — dev.mysql.com/doc/refman/5.0/en/show-status.html)
Скажу лишь, что данная команда в отличие от EXPLAIN, который базируется на эвристических оценках, показывает непосредственно ЧТО произошло после выполнения запроса, т.е. количество записей, к которым MySQL физически обратился (притом посредством данной команды можно узнать сколько из них было получено из памяти, а сколько посредством обращения к диску) хочу также отметить, что это число представляет из себя не оценочный результат, а реальное число (фактически MySQL все время инкрементит переменную, когда обращается к каждой следующей строке). В общем скажу, что SHOW STATUS возвращает огромное кол-во статистики, всю её естественно описывать не буду. Покажу основные моменты, которые следует замечать при её(статистике) анализе.
Для того чтобы получить статистику относительно интересующего нас запроса вначале нужно выполнить команду:
FLUSH STATUS — данная каманда сбросит(обнулит) все сесионные переменные, глобальные переменные не будут затронуты.
После этого выполняем интересующий нас запрос
SELECT bla-bla-bla FROM test_table as tt WHERE tt.bla = 'test' LIMIT 10
mysql> SHOW STATUS LIKE 'Key_read%';
+--------------------+----------+
| Variable_name | Value |
+--------------------+----------+
| Key_read_requests | 96882 |
| Key_reads | 36200 |
+--------------------+----------+
Что может дать подобная информация? И какие решения принимать, основываясь на этой информации?
Сейчас попробуем проанализировать полученные значения и принять решение по оптимизации, основываясь на данной статистике.
В даннои млучае мы видим, что более трети индексных блоков считываются с диска, а не достаются из кеша => соответственно адекватной мерой по оптимизации будет увеличение данного параметра (key_buffer_size).
Но следует также принимать во внимание следующее: key_buffer_size — один из самых главных конфигурационных параметров, особенно если вы используете MyISAM таблицы. Тогда величина данного параметра должна составлять приблизительно 25-30% от объема свободной оперативноя памяти. Но следует также обращать внимание, чтобы этот параметр не был неоправданно большим (например, если сумарный объем файлов индексов .MYI составляет 1GB, а key_buffer_size=4GB, в таком случае вы только зря тратите память). Также следует обратить внимание на то, что максимальное значение данного параметра 4GB (The maximum allowable setting for key_buffer_size is 4GB on 32-bit platforms. As of MySQL 5.0.52, values larger than 4GB are allowed for 64-bit platforms (except 64-bit Windows, for which large values are truncated to 4GB with a warning). The effective maximum size might be less, depending on your available physical RAM and per-process RAM limits imposed by your operating system or hardware platform. The value of this variable indicates the amount of memory requested. Internally, the server allocates as much memory as possible up to this amount, but the actual allocation might be less. dev.mysql.com/doc/refman/5.0/en/server-system-variables.html). Но даже если вы не пользуетесь MyISAM таблицами, значение key_buffer_size, стоит все равно поставить в 32-64MB, т.к. он будет использоваться индексами для временных таблиц (MyISAM)
Итак, какую еще полезную статистику можно получить по более сложным запросам, например, по такому:
| Variable_name | Value |
+------------------------+-------+
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 2 |
+------------------------+-------+
Последнее поле показывает, что MySQL делал FULL TABLE SCAN, на самом деле эту инфоррмацию подтверждает EXPLAIN, поле Extra, которого содержит Using temporary; Using filesort.
Если у нас в запросе фигурирует несколько таблиц, которые склеиваются во время запроса, то и другие значения в этом списке могут быть не нулевыми.
mysql> SHOW SESSION STATUS LIKE 'Sort%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 598 |
| Sort_scan | 1 |
+-------------------+-------+
4 rows in set (0.00 sec)
Опять же привиду описание данных полей из мануала.
Также полезно знать создавались ли временные(temporary) таблицы, которые использовались во время выполнения запроса на диске или в памяти.
mysql> SHOW SESSION STATUS LIKE 'Created%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 0 |
| Created_tmp_tables | 3 |
+-------------------------+-------+
3 rows in set (0.00 sec)
Здесь MySQL показывает, что использовались инмемори tables, только почему-то показывает, что было их создано 3 :-) В действительности для данного запроса создается всего одна.
Здесь сделаю небольшое лирическое отступление.
После публикации первых двух статей я получил пару писем с вопросами и предложениями. Так вот, как я понял, достаточно распространенной ошибкой являются огрехи в плане архитектурных решений, которые тянут за собой очень низкую производительность в запросах на выборку.
Например, если у вас имеется таблица, содержащая какие-то статьи/новости и т.д. и имеет приблизительно следующую структуру.
Также предполагается, что львиная часть запросов на выборку будет обращена именно к этой таблице. Причем, как правило, такая таблица может содержать достаточно большое количество столбцов, причем некоторые из них, как в приведенном выше случае, имеют тип данных TEXT или BLOB (`bodytext` text NOT NULL), а также поля переменной длинны (VARCHAR). Все это усложняет работу MySQL особенно в операциях сортировки/группировки записей. Т.к. представьте себе, что каждая запись имеет в среднем длинну 1Kb и когда MySQL будет сортировать/группировать такие записи, сервер просто будет «задыхаться» ворочая каждый раз такие большие объемы данных.
И это еще не все недостатки данного подхода. На это следует обратить особое внимание — MySQL для temporary table использует HEAP engine, который работает только с полями фиксированной длинны, т.е. varchar он делает char-ом(максимальной длинны, указанной при объявлении varchar). Делается, это как вы понимаете, для быстрого поиска записей. А вот с полями TEXT и BLOB он работать не умеет, поэтому конвертит тип таблицы к MyISAM и данные оказываются на диске, а это очень медленно.
Поэтому такой архтектуры следует избегать на больших базах с большим количеством обращений и требующим быстроко времени отклика.
Что предлагается сделать? В некоторых случаях может пригодится использование covering indexes и self-join, данный пример был описан в моей первой статье (http://habrahabr.ru/blogs/mysql/38907/), поэтому я повторяться не буду… повторяться не буду… :-)
Более же правильным, с точки зрения архитектурного подхода, в данном случае является выделение полей переменной длинны, которые как правило, не учавствуют в условиях WHERE (TEXT, BLOB, в зависимости от условия задачи это может быть и VARCHAR) в отдельную таблицу. Соответственно основные условия по выборке, как правило, по индексным полям с фиксированной длинной, мы выполняем на одной таблице, также выполняем по ней LIMIT и только после этого склеиваем, довавляем дополнительные поля, в моем примере это будет «bodytext» из другой таблицы. При этом в данном случае такая склейка будет выполняться по PRIMARY KEY.
С версии 5.0.37 MySQL включает возможность профайлинга запросов. Данная утилита записывает статистику выполнения запросов в служебную БД information_schema
Для того, чтобы MySQL начал записывать статистику по запросу следует установить значение переменное profiling в 1
После этого выполняем интересующие нас SQL запрос(ы).
Далее выполняем следующий запрос
Как я говорил ранее, данная статистика записывается в БД — information_schema, таблицу — profiling, поэтому мы можем получить статистику по выполнению запроса сделав запрос к этой таблице.
Далее можно посмотреть статистику всех стадий выполнения запроса, делается это с помощью команды
mysql> show profile for query 1;
+--------------------+------------+
| Status | Duration |
+--------------------+------------+
| (initialization) | 0.00007300 |
| Opening tables | 0.00005100 |
| System lock | 0.00000600 |
| Table lock | 0.00002000 |
| init | 0.00002200 |
| optimizing | 0.00003400 |
| statistics | 0.00010600 |
| preparing | 0.00014800 |
| executing | 0.50000700 |
| Sending data | 0.30226800 |
| end | 0.00000700 |
| query end | 0.00000500 |
| freeing items | 0.00001300 |
| closing tables | 0.00000700 |
| logging slow query | 0.00000400 |
+--------------------+------------+
15 rows in set (0.00 sec)
Также с помощью профайлинга можно смотреть статистику не только по SELECT запросам. Вы можете просматривать статистику даже по запросам, которые изменяют структуру таблиц ALTER TABLE и изменяют/удаляют данные. Соответственно и стадии выполнения у этих запросов будут отличаться.
Также данный вид профайлинга позволяет отслеживать загрузку процессора во время каждой стадии выполнения запроса, SWAP и т.д.
Очень полезным средством для поиска узким мест и медленных запросов к БД является логирование медленных запросов, а именно директива --log-slow-queries при старте MySQL. До версии MySQL 5.1.21 минимальное значение параметра long_query_time 1, значение по умолчанию 10. Данное значение должно быть типа интеджер. Т.е. если время выполнения запроса превышает величину данного параметра в секундах, то он заносится в лог. Также следует отметить, что если запрос попадает в slow query log, то это не говорит, что это плохой запрос, может он долго ждал пока освоболится lock. Также полезным параметром есть логирование запросов, которые не используют индексы --log-queries-not-using-indexes.
Это очень полезная информация, но если у нас большой проект и нагруженная база, то нам необходимы средства мониторинга и визуализации подобной статистики. Я лично использую MONyog
Терерь отвечу на пару вопросов, на которые не успел ответить:
Во время обсуждения второй статьи я в комментариях (http://habrahabr.ru/blogs/mysql/39260/#comment_941339) спорил с хабрачеловеком juks об уместности применения force index в запросах.
Рассмотрим, для примера, такую структуру таблицы
После этого заполним её данным, для этого нам понадобится, к примеру, такой скрипт на PHP
Код в данном скрипте интуитивно понятный. Обращу внимание только на «ALTER TABLE `some_table` DISABLE KEYS» перед вставкой большого кол-ва записей, и «ALTER TABLE `some_table` ENABLE KEYS»
после вставки. Эти директивы существенно ускорят работу скрипта и вообще будут полезны в аналогичных ситуациях. «ALTER TABLE… DISABLE KEYS» — перестает обновлять не уникальные индексы (в нашем случае «fields1_2», «fields2_3») во время вставки новых записей. MySQL использует специальный алгоритм, который намного быстрей, чем обновление индексов после вставки каждой записи, поэтому отключение индексов перед большой вставкой данных должно дать значительное ускорение.
И такой запрос к данной таблице:
Как видим MySQL в данном случае предпочитает использовать индекс «fields1_2», который способствует быстрому поиску (lookup), но сортировка выполняется без помощи индекса, поле Extra — Using where; Using filesort говорит нам об этом.
В данном случае мы можем заставить MySQL выбрать индекс, при котором запрос будет выполнятся быстрей.
*Позволю заметить, что MySQL может сделать выбор в пользу индекса `fields2_3`и без FORCE INDEX это зависит от версии MySQL и актуальности статистики индексов. Поэтому так важно поддерживать актуальность индексов (ANALYZE TABLE `some_table`). Не стоит заставлять MySQL выбирать между быстрым поиском, или сортировкой по индексу в данном случае, т.к. оба варианта будут выполнятся медленней, чем вариант в котором мы имеем составной индекс по 3-м полям.
Для начала удалим старый индекс:
И добавим новый
Также после первой статьи где предлагалось для быстрой группировки (GROUP BY) результатов использовать конструкцию — GROUP BY BINARY crc32(tag_text), я получил достаточно много вопрос, и сомнений в этом варианте, т.к. на выполнение ф-ии crc32 тратится дополнительное время, и многие считают, то это будет малоэффективно, некоторые предлагали вместо CRC32 использовать MD5, чтобы уменьшить кол-во возможных совпадений результатов ф-ии у разных значений.
Отвечу на все подобные сомнения таким небольшим бэнчмарком.
1 row in set (0.22 sec)
1 row in set (6.02 sec)
Как видите ф-ия CRC32 выполняется очень быстро
*BENCHMARK — выполняет указанную ф-ию CRC32 или MD5 в данном случае указанное количество раз 1 000 000
На сим буду оканчивать третью статью, и так она оказалась длинней, чем планировал. На все вопросы буду стараться отвечать.
Если сообщество еще не устало от моих статей, то в следующей статье по данной тематике хотел рассказать о проблемы scale-инга больших таблиц и прочих вкустностях MySQL :-)
Также, после опубликования первых двух статей я получил пару отзывов и вопросов, связанных с проектированием БД / расстановкой индексов / составлением запросов. На многие вопросы старался отвечать. С некоторыми из них поделюсь и в этой статье.
Профайлинг запросов
Итак, начну со стандартных средств профайлинга запросов средствами MySQL
- EXPLAIN
- SHOW STATUS
- PROFILING
EXPLAIN
С помощью команды EXPLAIN MySQL показывает ориентировочный execution plan запроса (Описание всех возвращаемых полей командой EXPLAIN можно посмотреть по адресу — dev.mysql.com/doc/refman/5.0/en/using-explain.html ). На самом деле в этом определении ключевое слово — ориентировочный, т.к. EXPLAIN может ошибаться :-) Причиной данных ошибок есть то, что генерация execution plan является частью выполнения запроса (query execution), иногда построение execution plan происходит динамически в зависимости от данных. С помощью команды EXPLAIN MySQL пытается симулировать выполнение запроса, но не «трогает» при этом данные, соответственно не имеет доступа к этому динамическому компоненту. Такую оценку MySQL делает в основном полагаясь на статистику индексов. Которую в свою очередь следует держать всегда в актуальном состоянии, и в зависимости от интенсивности запросов к БД на добавление/изменение/удаление данных проводить с определенной переодичность по cron-у (скажем, каждую ночь) выполнять запросы — ANALYZE TABLE (перестраивает дерево индексов, не позволяя выраждаться ему в список, такое может происходить если мы, скажем, вставляем упорядоченные данные, тогда эффективность операции поиска записи составит не O(logn), а O(n). Замечу также, что не всегда следует выполнять команду ANALYZE TABLE на production сервере, т.к. на время её выполнения MySQL лочит таблицы MyISAM (read lock), InnoDB (write lock) ), OPTIMIZE TABLE (дефрагментация файла данных. Полезно при большом удалении записей из таблицы).
Также EXPLAIN дает далеко не всю информацию для оценки скорости выполнения запроса. Например, если в ходе запроса будут использоватся temporary table, то MySQL не покажет будут ли это in-memory или in-disk temporary tables. Также MySQL в эксплейне не покажет цену операций обращения к записи или выполнения функций используемых в запросе.
Также в этом абзаце хотел отметить главное — что производительность запроса зависит от количества записей, которые были проверены (исследованы) MySQL, а не от количества возвращенных запросом записей.
SHOW STATUS
MySQL издавна предоставляет мониторинг системных переменных по средствам команды — SHOW STATUS. Ранее (до версии 4.1.) данная команда показывала статус глобальных переменных, но с версии 4.1. появилось возможность показывать состояние сессионных переменных — SHOW SESSION STATUS. (Мануал по данной команде — dev.mysql.com/doc/refman/5.0/en/show-status.html)
Скажу лишь, что данная команда в отличие от EXPLAIN, который базируется на эвристических оценках, показывает непосредственно ЧТО произошло после выполнения запроса, т.е. количество записей, к которым MySQL физически обратился (притом посредством данной команды можно узнать сколько из них было получено из памяти, а сколько посредством обращения к диску) хочу также отметить, что это число представляет из себя не оценочный результат, а реальное число (фактически MySQL все время инкрементит переменную, когда обращается к каждой следующей строке). В общем скажу, что SHOW STATUS возвращает огромное кол-во статистики, всю её естественно описывать не буду. Покажу основные моменты, которые следует замечать при её(статистике) анализе.
Для того чтобы получить статистику относительно интересующего нас запроса вначале нужно выполнить команду:
FLUSH STATUS — данная каманда сбросит(обнулит) все сесионные переменные, глобальные переменные не будут затронуты.
После этого выполняем интересующий нас запрос
SELECT bla-bla-bla FROM test_table as tt WHERE tt.bla = 'test' LIMIT 10
mysql> SHOW STATUS LIKE 'Key_read%';
+--------------------+----------+
| Variable_name | Value |
+--------------------+----------+
| Key_read_requests | 96882 |
| Key_reads | 36200 |
+--------------------+----------+
Что может дать подобная информация? И какие решения принимать, основываясь на этой информации?
Сейчас попробуем проанализировать полученные значения и принять решение по оптимизации, основываясь на данной статистике.
- Key_read_requests — Количество запросов на чтение индексных блоков из кеша.
- Key_reads — Количество физического чтения индексных блоков с диска. Если число Key_reads большое, тогда, вероятно, ваше key_buffer_size достаточно мало и требует увеличения. Отношение непопаданий в кэш можно посчитать как Key_reads/Key_read_requests.
В даннои млучае мы видим, что более трети индексных блоков считываются с диска, а не достаются из кеша => соответственно адекватной мерой по оптимизации будет увеличение данного параметра (key_buffer_size).
Но следует также принимать во внимание следующее: key_buffer_size — один из самых главных конфигурационных параметров, особенно если вы используете MyISAM таблицы. Тогда величина данного параметра должна составлять приблизительно 25-30% от объема свободной оперативноя памяти. Но следует также обращать внимание, чтобы этот параметр не был неоправданно большим (например, если сумарный объем файлов индексов .MYI составляет 1GB, а key_buffer_size=4GB, в таком случае вы только зря тратите память). Также следует обратить внимание на то, что максимальное значение данного параметра 4GB (The maximum allowable setting for key_buffer_size is 4GB on 32-bit platforms. As of MySQL 5.0.52, values larger than 4GB are allowed for 64-bit platforms (except 64-bit Windows, for which large values are truncated to 4GB with a warning). The effective maximum size might be less, depending on your available physical RAM and per-process RAM limits imposed by your operating system or hardware platform. The value of this variable indicates the amount of memory requested. Internally, the server allocates as much memory as possible up to this amount, but the actual allocation might be less. dev.mysql.com/doc/refman/5.0/en/server-system-variables.html). Но даже если вы не пользуетесь MyISAM таблицами, значение key_buffer_size, стоит все равно поставить в 32-64MB, т.к. он будет использоваться индексами для временных таблиц (MyISAM)
Итак, какую еще полезную статистику можно получить по более сложным запросам, например, по такому:
FLUSH STATUS;
SELECT
SQL_NO_CACHE fe.username, COUNT(*) as `count`
FROM
`tx_images` as ti
INNER JOIN
`fe_users` as fe
ON ti.cruser_id = fe.uid
GROUP BY
fe.uid
ORDER BY
`count` desc;
* This source code was highlighted with Source Code Highlighter.
| Variable_name | Value |
+------------------------+-------+
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 2 |
+------------------------+-------+
- Select_scan — The number of joins that did a full scan of the first table.
Последнее поле показывает, что MySQL делал FULL TABLE SCAN, на самом деле эту инфоррмацию подтверждает EXPLAIN, поле Extra, которого содержит Using temporary; Using filesort.
Если у нас в запросе фигурирует несколько таблиц, которые склеиваются во время запроса, то и другие значения в этом списке могут быть не нулевыми.
mysql> SHOW SESSION STATUS LIKE 'Sort%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 598 |
| Sort_scan | 1 |
+-------------------+-------+
4 rows in set (0.00 sec)
Опять же привиду описание данных полей из мануала.
- Sort_rows — The number of sorted rows.
- Sort_scan — The number of sorts that were done by scanning the table.
Также полезно знать создавались ли временные(temporary) таблицы, которые использовались во время выполнения запроса на диске или в памяти.
mysql> SHOW SESSION STATUS LIKE 'Created%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 0 |
| Created_tmp_tables | 3 |
+-------------------------+-------+
3 rows in set (0.00 sec)
Здесь MySQL показывает, что использовались инмемори tables, только почему-то показывает, что было их создано 3 :-) В действительности для данного запроса создается всего одна.
Здесь сделаю небольшое лирическое отступление.
После публикации первых двух статей я получил пару писем с вопросами и предложениями. Так вот, как я понял, достаточно распространенной ошибкой являются огрехи в плане архитектурных решений, которые тянут за собой очень низкую производительность в запросах на выборку.
Например, если у вас имеется таблица, содержащая какие-то статьи/новости и т.д. и имеет приблизительно следующую структуру.
CREATE TABLE `tx_articles` (
`uid` int(11) NOT NULL auto_increment,
`pid` int(11) NOT NULL default '0',
`tstamp` int(11) NOT NULL default '0',
`crdate` int(11) NOT NULL default '0',
`cruser_id` int(11) NOT NULL default '0',
`deleted` tinyint(4) NOT NULL default '0',
`hidden` tinyint(4) NOT NULL default '0',
`headline` varchar(70) NOT NULL default '',
`bodytext` text NOT NULL,
`type` int(11) NOT NULL default '0',
`citycat_id` int(11) NOT NULL default '0',
`editable` int(11) NOT NULL default '0',
`confirm` int(11) NOT NULL default '0',
`confirm_code` varchar(64) NOT NULL default '',
`editorspick` int(11) NOT NULL default '0',
`newspaper_id` int(11) NOT NULL default '0',
PRIMARY KEY (`uid`),
KEY `parent` (`pid`),
KEY `citycat_id` (`citycat_id`, `tstamp`)
KEY `newspaper_id` (`newspaper_id`)
KEY `tstamp` (`tstamp`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
* This source code was highlighted with Source Code Highlighter.
Также предполагается, что львиная часть запросов на выборку будет обращена именно к этой таблице. Причем, как правило, такая таблица может содержать достаточно большое количество столбцов, причем некоторые из них, как в приведенном выше случае, имеют тип данных TEXT или BLOB (`bodytext` text NOT NULL), а также поля переменной длинны (VARCHAR). Все это усложняет работу MySQL особенно в операциях сортировки/группировки записей. Т.к. представьте себе, что каждая запись имеет в среднем длинну 1Kb и когда MySQL будет сортировать/группировать такие записи, сервер просто будет «задыхаться» ворочая каждый раз такие большие объемы данных.
И это еще не все недостатки данного подхода. На это следует обратить особое внимание — MySQL для temporary table использует HEAP engine, который работает только с полями фиксированной длинны, т.е. varchar он делает char-ом(максимальной длинны, указанной при объявлении varchar). Делается, это как вы понимаете, для быстрого поиска записей. А вот с полями TEXT и BLOB он работать не умеет, поэтому конвертит тип таблицы к MyISAM и данные оказываются на диске, а это очень медленно.
Поэтому такой архтектуры следует избегать на больших базах с большим количеством обращений и требующим быстроко времени отклика.
Что предлагается сделать? В некоторых случаях может пригодится использование covering indexes и self-join, данный пример был описан в моей первой статье (http://habrahabr.ru/blogs/mysql/38907/), поэтому я повторяться не буду… повторяться не буду… :-)
Более же правильным, с точки зрения архитектурного подхода, в данном случае является выделение полей переменной длинны, которые как правило, не учавствуют в условиях WHERE (TEXT, BLOB, в зависимости от условия задачи это может быть и VARCHAR) в отдельную таблицу. Соответственно основные условия по выборке, как правило, по индексным полям с фиксированной длинной, мы выполняем на одной таблице, также выполняем по ней LIMIT и только после этого склеиваем, довавляем дополнительные поля, в моем примере это будет «bodytext» из другой таблицы. При этом в данном случае такая склейка будет выполняться по PRIMARY KEY.
PROFILING
С версии 5.0.37 MySQL включает возможность профайлинга запросов. Данная утилита записывает статистику выполнения запросов в служебную БД information_schema
Для того, чтобы MySQL начал записывать статистику по запросу следует установить значение переменное profiling в 1
mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)
* This source code was highlighted with Source Code Highlighter.
После этого выполняем интересующие нас SQL запрос(ы).
Далее выполняем следующий запрос
mysql> show profiles;
+----------+------------+-----------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------------------------+
| 0 | 0.00005100 | set profiling=1 |
| 1 | 0.80246730 | SELECT SQL_NO_CACHE fe.username, COUNT(*) as `count` FROM `tx_images` as ti INNER JOIN `fe_users` as fe ON ti.cruser_id = fe.uid GROUP BY fe.uid ORDER BY `count` desc |
+----------+------------+-----------------------------------------------+
2 rows in set (0.00 sec)
* This source code was highlighted with Source Code Highlighter.
Как я говорил ранее, данная статистика записывается в БД — information_schema, таблицу — profiling, поэтому мы можем получить статистику по выполнению запроса сделав запрос к этой таблице.
mysql> select sum(duration) from information_schema.profiling where query_id=1;
+---------------+
| sum(duration) |
+---------------+
| 0.80246730 |
+---------------+
1 row in set (0.00 sec)
* This source code was highlighted with Source Code Highlighter.
Далее можно посмотреть статистику всех стадий выполнения запроса, делается это с помощью команды
mysql> show profile for query 1;
+--------------------+------------+
| Status | Duration |
+--------------------+------------+
| (initialization) | 0.00007300 |
| Opening tables | 0.00005100 |
| System lock | 0.00000600 |
| Table lock | 0.00002000 |
| init | 0.00002200 |
| optimizing | 0.00003400 |
| statistics | 0.00010600 |
| preparing | 0.00014800 |
| executing | 0.50000700 |
| Sending data | 0.30226800 |
| end | 0.00000700 |
| query end | 0.00000500 |
| freeing items | 0.00001300 |
| closing tables | 0.00000700 |
| logging slow query | 0.00000400 |
+--------------------+------------+
15 rows in set (0.00 sec)
Также с помощью профайлинга можно смотреть статистику не только по SELECT запросам. Вы можете просматривать статистику даже по запросам, которые изменяют структуру таблиц ALTER TABLE и изменяют/удаляют данные. Соответственно и стадии выполнения у этих запросов будут отличаться.
Также данный вид профайлинга позволяет отслеживать загрузку процессора во время каждой стадии выполнения запроса, SWAP и т.д.
Slow query log
Очень полезным средством для поиска узким мест и медленных запросов к БД является логирование медленных запросов, а именно директива --log-slow-queries при старте MySQL. До версии MySQL 5.1.21 минимальное значение параметра long_query_time 1, значение по умолчанию 10. Данное значение должно быть типа интеджер. Т.е. если время выполнения запроса превышает величину данного параметра в секундах, то он заносится в лог. Также следует отметить, что если запрос попадает в slow query log, то это не говорит, что это плохой запрос, может он долго ждал пока освоболится lock. Также полезным параметром есть логирование запросов, которые не используют индексы --log-queries-not-using-indexes.
Это очень полезная информация, но если у нас большой проект и нагруженная база, то нам необходимы средства мониторинга и визуализации подобной статистики. Я лично использую MONyog
ответы редакции :-)
Терерь отвечу на пару вопросов, на которые не успел ответить:
Во время обсуждения второй статьи я в комментариях (http://habrahabr.ru/blogs/mysql/39260/#comment_941339) спорил с хабрачеловеком juks об уместности применения force index в запросах.
Рассмотрим, для примера, такую структуру таблицы
CREATE TABLE `some_table`
(
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`field1` INT(10) NOT NULL,
`field2` VARCHAR(50) NOT NULL default '',
`field3` TIMESTAMP,
`field4` VARCHAR(255) default '',
PRIMARY KEY (`id`),
KEY fields1_2 (field1, field2(8)),
KEY fields2_3 (field1, field3)
) ENGINE=InnoDB DEFAULT CHARSET=«UTF8»;
* This source code was highlighted with Source Code Highlighter.
После этого заполним её данным, для этого нам понадобится, к примеру, такой скрипт на PHP
<?php
set_time_limit (0);
mysql_connect(«127.0.0.1», «root»,«root_password»);
mysql_select_db(«database_name»);
function random_string() {
$string = «ABCDEFGHIKJLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789»;
$len = strlen($string);
$return = '';
$length = rand(10, 50);
for($i=0; $i<$length; $i++) {
$return .= $string{rand(0, $len)};
}
return $return;
}
$names = array(«maghamed», «maghamed1», «maghamed2», «maghamed3», «maghamed4»);
mysql_query(«ALTER TABLE `some_table` DISABLE KEYS»); //stop updating non-unique indexes
for($i=0; $i<10000000; $i++) {
$a = $i%1000;
$b = rand(0, 4);
$b = $names[$b];
$c = random_string();
$sql = «INSERT INTO `some_table` SET field1 = ${a}, field2 = '${b}', field4 = '${c}'»;
mysql_query($sql) or die(«Invalid query: „. mysql_error());
}
mysql_query(“ALTER TABLE `some_table` ENABLE KEYS»);
?>* This source code was highlighted with Source Code Highlighter.
Код в данном скрипте интуитивно понятный. Обращу внимание только на «ALTER TABLE `some_table` DISABLE KEYS» перед вставкой большого кол-ва записей, и «ALTER TABLE `some_table` ENABLE KEYS»
после вставки. Эти директивы существенно ускорят работу скрипта и вообще будут полезны в аналогичных ситуациях. «ALTER TABLE… DISABLE KEYS» — перестает обновлять не уникальные индексы (в нашем случае «fields1_2», «fields2_3») во время вставки новых записей. MySQL использует специальный алгоритм, который намного быстрей, чем обновление индексов после вставки каждой записи, поэтому отключение индексов перед большой вставкой данных должно дать значительное ускорение.
И такой запрос к данной таблице:
mysql> EXPLAIN
-> SELECT
-> *
-> FROM
-> `some_table`
-> WHERE
-> field1 = 50 AND
-> field2 = 'maghamed'
-> ORDER BY
-> field3 DESC
-> LIMIT 100
-> \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: some_table
type: ref
possible_keys: fields1_2,fields2_3
key: fields1_2
key_len: 30
ref: const,const
rows: 3042
Extra: Using where; Using filesort
1 row in set (0.05 sec)
* This source code was highlighted with Source Code Highlighter.
Как видим MySQL в данном случае предпочитает использовать индекс «fields1_2», который способствует быстрому поиску (lookup), но сортировка выполняется без помощи индекса, поле Extra — Using where; Using filesort говорит нам об этом.
В данном случае мы можем заставить MySQL выбрать индекс, при котором запрос будет выполнятся быстрей.
mysql> explain
-> SELECT
-> *
-> FROM
-> `some_table`
-> FORCE INDEX (`fields2_3`)
-> WHERE
-> field1 = 50 AND
-> field2 = 'maghamed'
-> ORDER BY
-> field3 DESC
-> LIMIT 100;
+----+-------------+------------+------+---------------+-----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+-----------+---------+-------+------+-------------+
| 1 | SIMPLE | some_table | ref | fields2_3 | fields2_3 | 4 | const | 1928 | Using where |
+----+-------------+------------+------+---------------+-----------+---------+-------+------+-------------+
1 row in set (0.03 sec)
* This source code was highlighted with Source Code Highlighter.
*Позволю заметить, что MySQL может сделать выбор в пользу индекса `fields2_3`и без FORCE INDEX это зависит от версии MySQL и актуальности статистики индексов. Поэтому так важно поддерживать актуальность индексов (ANALYZE TABLE `some_table`). Не стоит заставлять MySQL выбирать между быстрым поиском, или сортировкой по индексу в данном случае, т.к. оба варианта будут выполнятся медленней, чем вариант в котором мы имеем составной индекс по 3-м полям.
Для начала удалим старый индекс:
DROP INDEX `fields1_2` ON `some_table`
* This source code was highlighted with Source Code Highlighter.
И добавим новый
CREATE INDEX `fields1_2_3` ON `some_table` (field1, field2(8), `field3`);
* This source code was highlighted with Source Code Highlighter.
Также после первой статьи где предлагалось для быстрой группировки (GROUP BY) результатов использовать конструкцию — GROUP BY BINARY crc32(tag_text), я получил достаточно много вопрос, и сомнений в этом варианте, т.к. на выполнение ф-ии crc32 тратится дополнительное время, и многие считают, то это будет малоэффективно, некоторые предлагали вместо CRC32 использовать MD5, чтобы уменьшить кол-во возможных совпадений результатов ф-ии у разных значений.
Отвечу на все подобные сомнения таким небольшим бэнчмарком.
SET @input := 'hello world';
SELECT BENCHMARK(1000000, CRC32(@input));* This source code was highlighted with Source Code Highlighter.
1 row in set (0.22 sec)
SET @input := 'hello world';
SELECT BENCHMARK(1000000, MD5(@input));* This source code was highlighted with Source Code Highlighter.
1 row in set (6.02 sec)
Как видите ф-ия CRC32 выполняется очень быстро
*BENCHMARK — выполняет указанную ф-ию CRC32 или MD5 в данном случае указанное количество раз 1 000 000
На сим буду оканчивать третью статью, и так она оказалась длинней, чем планировал. На все вопросы буду стараться отвечать.
Если сообщество еще не устало от моих статей, то в следующей статье по данной тематике хотел рассказать о проблемы scale-инга больших таблиц и прочих вкустностях MySQL :-)
