Оптимизация запросов в системе управления базами данных MySQL

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

Теперь рассмотрим оптимизацию сортировки. Сортировка результатов может оказаться дорогостоящей операцией, поэтому зачастую производительность можно повысить, избежав сортировки вовсе или уменьшив количество сортируемых строк. Ранее было сказано, что индексы могут быть использованы для сортировки, однако если MySQL не может найти подходящего индекса, то ей приходится сортировать строки самостоятельно. Это можно сделать в памяти или на диске, но сама процедура всегда называется файловой сорти ровкой (filesort), пусть даже в действительности файл не используется.

Если обрабатываемые данные умещаются в буфер, то MySQL может выполнить сортировку целиком в памяти, применяя алгоритм быстрой сортировки (quicksort). В противном случае сортировка выполняется на диске поблочно. Каждый блок обрабатывается методом быстрой сортировки, а затем уже отсортированные блоки сливаются. Существует два алгоритма файловой сортировки.

Двухпроходный (старый) - читает указатели на строки и столбцы, упомянутые во фразе ORDER BY, сортирует их, затем проходит по отсортированному списку и снова читает исходные строки, чтобы вывести результат. Двухпроходный алгоритм обходится довольно дорого, поскольку читает строки из таблицы дважды, и второе чтение вызывает много непоследовательных операций ввода/вывода. Особенно накладно это в случае таблиц типа MyISAM, когда для выборки каждой строки необходим вызов операционной системы (в вопросах кэширования данных MyISAM полагается ОС). С другой стороны, для сортировки этим способом используется минимальный объем памяти, поэтому, если все сортируемые строки уже находятся в ОЗУ, то может оказаться дешевле хранить меньше данных и перечитывать строки для генерации окончательного результата.

Однопроходный (новый) - читает все необходимые запросу столбцы, сортирует строки по столбцам, упомянутым во фразе ORDER BY, проходит по отсортированному списку и выводит заданные столбцы. Этот алгоритм реализован, начиная с версии MySQL 4.1. Он может показывать гораздо более высокую скорость, особенно на больших наборах данных. Однопроходный алгоритм не читает строки из таблицы дважды, а случайный ввод/вывод в нем заменяется последовательным чтением. Но при этом необходимо больше памяти, так как для каждой строки приходится хранить все запрошенные столбцы, а не только те, по которым производится сортировка. Следовательно, в буфер сортировки поместится меньше строк и надо будет выполнить больше циклов слияния.

При выполнении соединения MySQL может производить файловую сортировку на двух стадиях выполнения запроса. Если во фразе ORDER BY упомянуты только столбцы из первой (в порядке соединения) таблицы, то MySQL может отсортировать ее, а затем приступить к соединению. Если задана фраза LIMIT, то она применяется после сортировки, поэтому временная таблица может быть очень велика.

2. Оптимизация запросов

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

2.1 Способы реструктуризации запросов

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

При конструировании запросов часто приходится отвечать на важный вопрос: не лучше ли будет разбить сложный запрос на несколько более простых? Традиционно при проектировании базы данных стараются сделать как можно больше работы с помощью наименьшего числа запросов. Исторически такой подход был оправдан из-за высокой стоимости сетевых коммуникаций и накладных расходов на разбор и оптимизацию. Но к MySQL данная рекомендация относится в меньшей степени, поскольку эта СУБД изначально проектировалась так, чтобы установление и разрыв соединения происходили максимально эффективно, а обработка небольших простых запросов выполнялась очень быстро. Современные сети гораздо быстрее, чем раньше, поэтому и сетевые задержки заметно сократились. MySQL способна выполнять свыше 50 000 простых запросов в секунду на типичном серверном оборудовании и свыше 2000 запросов в секунду от одиночного клиента в гигабитной сети, поэтому выполнение нескольких запросов может оказаться вполне приемлемой альтернативой. Передача информации с использованием соединения все же происходит значительно медленнее по сравнению с тем, какой объем находящихся в памяти данных сам MySQL может перебрать в секунду, - это число измеряется миллионами строк. Так что с учетом всех факторов по-прежнему лучше бы ограничиться минимальным количеством запросов, но иногда можно повысить скорость выполнения сложного запроса, разложив его на несколько более простых. Несмотря на все вышесказанное, чрезмерно большое количество запросов - одна из наиболее часто встречающихся ошибок при проектировании приложений. Например, в некоторых приложениях выполняется 10 запросов, возвращающих по одной строке, вместо одного запроса, отбирающего 10 строк. Иногда встречаются приложения, в которых каждый столбец выбирается по отдельности, для чего одна и та же строка запрашивается многократно.

2.2 Разбиение запроса на части

Другой способ уменьшить сложность запроса состоит в применении тактики «разделяй и властвуй», когда выполняется по существу один и тот же запрос, но каждый раз из него возвращается меньшее число строк. Отличный пример - удаление старых данных. В процессе периодической чистки иногда приходится удалять значительные объемы информации. Если делать это одним большим запросом, то возможны всяческие неприятные последствия: блокировки большого числа строк на длительное время, переполнение журналов транзакций, истощение ресурсов, блокировка небольших запросов, которые не допускают прерывания. Разбив команду DELETE на части, каждая из которых удаляет умеренное число строк, можно заметно увеличить производительность и уменьшить отставание реплики в случае репликации запроса. Кроме того, имеет смысл вставить небольшую паузу между последовательными командами DELETE, чтобы распределить нагрузку по времени и не удерживать блокировки слишком долго.

2.3 Декомпозиция соединения

На многих высокопроизводительных сайтах применяется техника декомпозиции соединений (join decomposition). Смысл ее заключается в том, чтобы выполнить несколько однотабличных запросов вместо одного запроса к нескольким объединенным таблицам, а соединение выполнить уже вне СУБД. Например, следующий абстрактный запрос:

mysql> SELECT * FROM tag

-> JOIN tag_post ON tag_post.tag_id=tag.id

-> JOIN post ON tag_post.post_id=post.id -> WHERE tag.tag='mysql';

можно было бы заменить такими:

mysql> SELECT * FROM tag WHERE tag='mysql';

mysql> SELECT * FROM tag_post WHERE tag_id=1234;

mysql> SELECT * FROM post WHERE post.id in (123,456,567,9098);

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

Можно более эффективно реализовать кэширование. Во многих приложениях кэшируются «объекты», которые напрямую соответствуют таблицам. В данном примере, если объект, для которого поле tag равно mysql, уже кэширован, то приложение может пропустить первый запрос. Если выясняется, что в кэше уже есть записи из таблицы post с идентификаторами post_id, равными 123, 567 или 9098, то соответствующие значения можно исключить из списка IN(). Кэш запросов от такой стратегии также выигрывает. Если часто изменяется только одна таблица, то декомпозиция соединения может уменьшить количество перезагрузок записей в кэш (cache invalidations).

Для подсистемы MyISAM запросы, обращающиеся только к одной таблице, позволяют более эффективно использовать блокировки, поскольку таблицы блокируются по отдельности и на краткий промежуток времени, а не коллективно и надолго.

Соединение результатов на уровне приложения упрощает масштабирование базы данных путем размещения разных таблиц на различных серверах.

Сами запросы также могут стать более эффективными. В приведенном примере использование списка IN() вместо соединения позволяет MySQL более эффективно сортировать идентификаторы и более оптимально извлекать строки, чем это было бы возможно в процессе соединения.

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

Соединение в приложении может оказаться эффективнее в следующих случаях:

Организован кэш и ранее запрошенные данные используются повторно.

Часто используются таблицы типа MyISAM.

Данные распределены по нескольким серверам.

Вместо соединения с большой таблицей используется список IN().

В соединении несколько раз встречается одна и та же таблица.



2.4 Подсказки оптимизатору запросов

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

HIGH_PRIORITY и LOW_PRIORITY.

Говорят, какой приоритет назначить данной команде относительно других команд, пытающихся обратиться к тем же таблицам. Подсказка HIGH_PRIORITY означает, что MySQL должен поместить команду SELECT в очередь раньше всех прочих, ожидающих получения блокировок для модификации данных. Иными словами, команда SELECT должна быть выполнена как можно быстрее, а не ожидать своей очереди. Эта подсказка применима и к команде INSERT; в этом случае она просто отменяет действие глобального параметра LOW_ PRIORITY, установленного на уровне сервера. Подсказка LOW_PRIORITY оказывает обратное действие: в этом случае команда будет ждать завершения всех остальных команд, желающих обратиться к тем же таблицам, даже если они были отправлены позже. Данная подсказка применима к командам SELECT, INSERT, UPDATE, REPLACE. Обе подсказки работают с подсистемами хранения, в которых реализована блокировка на уровне таблиц, но в InnoDB и других подсистемах с более детальным контролем блокировки и конкурентного доступа необходимости в них возникать не должно. Будьте осторожны, применяя их к таблицам типа MyISAM, поскольку таким образом можно запретить одновременные вставки и существенно понизить производительность. Подсказки HIGH_PRIORITY и LOW_PRIORITY часто понимают неправильно. Их смысл не в том, чтобы выделить запросу побольше ресурсов, чтобы «сервер уделил ему больше внимания», или поменьше, чтобы «сервер не перетруждался». Они просто влияют на дисциплину обслуживания очереди команд, ожидающих доступа к таблице. DELAYED

Эта подсказка применяется к командам INSERT и REPLACE. Команда с данной подсказкой возвращает управление немедленно, а подлежащие вставке строки помещаются в буфер и будут реально вставлены все сразу, когда таблица освободится. Чаще всего это бывает полезно для протоколирования и аналогичных приложений, в которых нужно записывать много строк, не заставляя клиента ждать и не выполняя операцию ввода/вывода для каждой команды в отдельности. Однако у данного режима есть много ограничений; так, отложенная вставка реализована не во всех подсистемах хранения, а функция LAST_INSERT_ID( ) в этом случае неприменима.

STRAIGHT_JOIN.

Эта подсказка может встречаться сразу после ключевого слова SELECT в команде SELECT или в любой другой команде между двумя соединяемыми таблицами. В первом случае она говорит серверу, что указанные в запросе таблицы нужно соединять в порядке перечисления. Во втором случае она задает порядок соединения таблиц, между которыми находится. Подсказка STRAIGHT_JOIN полезна, если выбранный MySQL порядок соединения не оптимален или оптимизатор тратит чересчур много времени на выбор порядка. В последнем случае поток слишком много времени проводит в состоянии «Statistics», а, включив эту подсказку, можно будет сократить пространство поиска оптимизатора. С помощью команды EXPLAIN вы можете посмотреть, какой порядок выбрал оптимизатор, а потом переписать запрос, расположив таблицы именно в этом порядке и добавив подсказку STRAIGHT_JOIN. Указанная идея неплоха, если вы уверены, что фиксированный порядок не приведет к снижению производительности для некоторых условий WHERE. Однако не забывайте пересматривать такие запросы после перехода на новую версию MySQL, поскольку могут появиться другие оптимизации, подавляемые наличием STRAIGHT_JOIN.

SQL_SMALL_RESULT и SQL_BIG_RESULT.

Эти подсказки применимы только к команде SELECT. Они говорят оптимизатору, когда и как использовать временные таблицы или сортировку при выполнении запросов с GROUP BY или DISTINCT. SQL_SMALL_ RESULT означает, что результирующий набор будет невелик, так что его можно поместить в индексированную временную таблицу, чтобы не сортировать для группировки. Напротив, SQL_BIG_RESULT означает, что результат велик, и лучше использовать временные таблицы на диске с последующей сортировкой.

SQL_BUFFER_RESULT.

Эта подсказка говорит оптимизатору, что результаты нужно поместить во временную таблицу и как можно скорее освободить табличные блокировки. Буферизация на стороне сервера может быть полезна, когда вы не используете буферизацию на стороне клиента, поскольку позволяет уменьшить потребление памяти клиентом и вместе с тем быстро освободить блокировки. Компромисс состоит в том, что теперь вместо памяти клиента потребляется память сервера. SQL_CACHE и SQL_NO_CACHE Эти подсказки говорят серверу о том, что данный запрос является или не является кандидатом на помещение в кэш запросов. О том, как ими пользоваться, рассказано в следующей главе.

SQL_CALC_FOUND_ROWS.

Эта подсказка заставляет MySQL вычислить весь результирующий набор, даже если имеется фраза LIMIT, ограничивающая количество возвращаемых строк. Получить общее количество строк позволяет функция FOUND_ROWS( )

FOR UPDATE и LOCK IN SHARE MODE.

Эти подсказки управляют блокировками для команд SELECT, но только в тех подсистемах хранения, где реализованы блокировки на уровне строк. Они позволяют поставить блокировки на найденные строки, что бывает полезно, когда заранее известно, что эти строки нужно будет обновить, или чтобы избежать эскалации и сразу получить монопольные блокировки. Данные подсказки не нужны в запросах вида INSERT ... SELECT, поскольку в этом случае MySQL 5.0 по умолчанию ставит блокировки чтения на строки исходной таблицы (это поведение можно отменить, однако мы не рекомендуем так поступать, в главах 8 и 11 объяснено, почему). В версии MySQL 5.1 упомянутое ограничение при определенных условиях может быть снято. Применяя эти подсказки в InnoDB, имейте в виду, что они подавляют некоторые оптимизации, например покрывающие индексы. InnoDB не может монопольно заблокировать строки, не обращаясь к индексу по первичному ключу, в котором хранится информация о версиях строк.

USE INDEX, IGNORE INDEX и FORCE INDEX.

Эти подсказки говорят оптимизатору о том, какие индексы использовать или игнорировать при поиске строк в таблице (например, для выработки решения о порядке соединения). В версии MySQL 5.0 и более ранних они не влияют на выбор сервером индексов для сортировки и группировки. В MySQL 5.1 можно дополнить подсказку ключевыми словами FOR ORDER BY или FOR GROUP BY. FORCE INDEX - то же самое, что USE INDEX, но эта подсказка сообщает оптимизатору о том, что сканирование таблицы обойдется гораздо дороже поиска по индексу, даже если индекс не очень полезен. Вы можете включить данные подсказки, если полагаете, что оптимизатор выбрал неподходящий индекс, или если хотите по какой-то причине воспользоваться конкретным индексом, например для неявного упорядочения без использования ORDER BY. В версии MySQL 5.0 и более поздних существуют также несколько системных переменных, влияющих на поведение оптимизатора.

optimizer_search_depth

Эта переменная говорит оптимизатору, насколько исчерпывающе исследовать частичные планы. Если запрос слишком долго пребывает в состоянии «Statistics», то попробуйте уменьшить это значение.

optimizer_prune_level

Эта переменная, которая по умолчанию включена, позволяет оптимизатору пропускать некоторые планы в зависимости от количества исследованных строк. Обе переменные контролируют режим сокращенного перебора планов выполнения. Такое «срезание углов» бывает полезно для повышения производительности при обработке сложных запросов, но чревато тем, что ради достижения эффективности сервер может пропустить оптимальный план. Поэтому иногда имеет смысл менять их значения.

2.5 Переменные, определяемые пользователем

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

mysql> SET @one := 1;

mysql> SET @min_actor := (SELECT MIN(actor_id) FROM sakila.actor);

mysql> SET @last_week := CURRENT_DATE-INTERVAL 1 WEEK;

Затем эти переменные можно использовать в различных местах выражения:

mysql> SELECT ... WHERE col <= @last_week;

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

Они подавляют кэширование запроса.

Их нельзя использовать в тех местах, где требуется литерал или идентификатор, например, вместо имени таблицы или столбца либо во фразе LIMIT.

Они связаны с конкретным соединением, поэтому не годятся для передачи информации между соединениями.

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

В версиях, предшествующих MySQL 5.0, они были чувствительны к регистру, поэтому обращайте внимание на совместимость.

Нельзя явно объявить тип переменной, а точки, в которых MySQL принимает решение о типе неопределенной переменной, в разных версиях различаются. Самое правильное - присвоить начальное значение 0 переменным, предназначенным для хранения целых чисел, 0.0 - переменным, предназначенным для хранения чисел с плавающей точкой, и `' (пустая строка) - переменным, предназначенным для хранения строк. Тип переменной изменяется в момент присваивания ей значения; в MySQL типизация пользовательских переменных динамическая.

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

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

Приоритет оператора присваивание := ниже, чем у всех остальных операторов, поэтому следует явно расставлять скобки.

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

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

Большая часть проблем при работе с пользовательскими переменными проистекает из-за того, что присваивание и чтение значений происходят на разных стадиях обработки запроса. Например, невозможно предсказать, что произойдет, если присвоить значение во фразе SELECT, а прочитать во фразе WHERE.

Далее приведены несколько задач, в которых можно использовать пользовательские переменные:

Вычисление промежуточных итогов и средних.

Эмуляция функций FIRST() и LAST() с помощью запросов с группировкой.

Математические операции над очень большими числами * Вычисление MD5-свертки для всей таблицы.

Восстановление выборочного значения, которое «оборачивается», если превышает некоторую границу.

Эмуляция курсоров чтения/записи.



3. Оптимизация запросов конкретных типов

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

3.1 Частные случаи оптимизации запросов

В этом подразделе рассматриваются следующие частные случаи запросов: запросы с COUNT(), запросы с JOIN, запросы с GROUP BY и DISTINCT, запросы с LIMIT со смещением, запросы с UNION.

Оптимизация запросов с COUNT().

Агрегатная функция COUNT() и способы оптимизации содержащих ее запросов - пожалуй, один из десяти хуже всего понимаемых аспектов MySQL. Поиск в Сети даст столько неправильной информации, что мы даже думать об этом не хотим. Но прежде чем переходить к вопросу об оптимизации, неплохо бы понять, что в действительности делает функция COUNT().

COUNT() - это особая функция, которая решает две очень разные задачи: подсчитывает значения и строки. Значение - это выражение, отличное от NULL (NULL означает отсутствие какого бы то ни было значения). Если указать имя столбца или какое-нибудь другое выражение в скобках, то COUNT( ) посчитает, сколько раз это выражение имеет значение (т. е. сколько раз оно не равно NULL). Вторая форма COUNT() просто подсчитывает количество строк в результирующем наборе. Так MySQL поступает, когда точно знает, что выражение внутри скобок не может быть равно NULL. Наиболее очевидный пример - выражение COUNT(*), специальная форма COUNT(), которая вовсе не сводится к подстановке вместо метасимвола * полного списка столбцов таблицы, как вы, возможно, подумали. На самом деле столбцы вообще игнорируются, а подсчитываются сами строки. Одна из наиболее часто встречающихся ошибок - задание имени столбца в скобках, когда требуется подсчитать строки. Если необходимо знать, сколько строк в результирующем наборе, всегда следует употреблять COUNT(*). Тем самым вы недвусмысленно сообщите серверу о своем намерении и избежите возможного падения производительности.

Распространенным заблуждением является мнение, что для таблиц типа MyISAM запросы, содержащие функцию COUNT(), выполняются очень быстро. Это верно только в одном случае: COUNT(*) без фразы WHERE, то есть при подсчете общего количества строк в таблице. MySQL может оптимизировать такой запрос, поскольку подсистеме хранения в любой момент известно, сколько в таблице строк. Если MySQL знает, что столбец col не может содержать NULL, то СУБД оптимизирует и выражение COUNT(col), самостоятельно преобразовав его в COUNT(*). MyISAM не обладает никакими уникальными возможностями для подсчета строк, когда в запросе есть фраза WHERE, равно как и для подсчета значений, а не строк. Возможно, конкретный запрос она выполнит быстрее, чем другая подсистема хранения, а, возможно, и нет. Это зависит от множества факторов.

Иногда оптимизацию COUNT(*) в MyISAM можно эффективно применить, если требуется подсчитать все строки, кроме очень небольшого числа, при условии наличия высокоселективного индекса. В следующем примере была использована стандартная база данных World, чтобы показать, как можно эффективно подсчитать количество городов с идентификаторами больше 5. Можно было бы записать запрос так:

mysql> SELECT COUNT(*) FROM world.City WHERE ID > 5;

Выполнив профилирование этого запроса с помощью SHOW STATUS, вы обнаружите, что он просматривает 4079 строк. Если изменить условие на противоположное и вычесть количество городов с идентификаторами, меньшими либо равными 5, из общего количества, то число просмотренных строк сократится до пяти:

mysql> SELECT (SELECT COUNT(*) FROM world.City) - COUNT(*)

-> FROM world.City WHERE ID <= 5;

В этом варианте читается меньше строк, поскольку на стадии оптимизации подзапрос преобразуется в константу.

В общем случае запросы, содержащие COUNT(), с трудом поддаются оптимизации, поскольку обычно они должны подсчитать много строк (то есть просмотреть большой объем данных). Единственная возможность внутри самого сервера MySQL - воспользоваться покрывающим индексом. Если этого недостаточно, рекомендуется внести изменения в архитектуру приложения. Можно рассмотреть вариант заведения сводных таблиц или применить внешнюю систему кэширования, скажем, memcached. Не исключено, что придется столкнуться с известной дилеммой: «быстро, точно, просто - выбирайте любые два варианта».

Оптимизация запросов с JOIN.

При работе с оператором JOIN следует придерживаться следующих правил:

Строить индексы по столбцам, используемым во фразах ON или

USING. При добавлении индексов учитывать порядок соединения. Если таблицы A и B соединяются по столбцу С и оптимизатор решит, что их надо соединять в порядке B, A, то индексировать таблицу B необязательно. Неиспользуемые индексы только влекут за собой лишние накладные расходы. В общем случае следует индексировать только вторую таблицу в порядке соединения, если, конечно, индекс не нужен для каких-то других целей.

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

Аккуратно переходить на новую версию MySQL, поскольку в разные моменты изменялись синтаксис соединения, приоритеты операторов и другие аспекты поведения. Конструкция, которая раньше была обычным соединением, иногда вдруг становится декартовым произведением (а это совершенно другой тип соединения, который возвращает иные результаты), а то и вовсе оказывается синтаксически некорректной.

Оптимизация GROUP BY и DISTINCT.

Во многих случаях MySQL оптимизирует эти два вида запросов схожим образом; по существу, зачастую он внутренне переключается между ними на стадии оптимизации. Как обычно, выполнение того и другого запроса можно ускорить при наличии подходящих индексов. Если подходящего индекса не существует, то MySQL может применить одну из двух стратегий реализации GROUP BY: воспользоваться временной таблицей или прибегнуть к файловой сортировке. Для конкретного запроса более эффективным может оказаться тот или другой подход. Чтобы оптимизатор выбрал нужный метод, в запрос включаются подсказки SQL_BIG_RESULT или SQL_SMALL_RESULT. Если нужна группировка по значению столбца, который извлекается при соединении из справочной таблицы, то обычно более продуктивно группировать по идентификатору из этой таблицы, а не по его значению. Например, следующий запрос написан не очень удачно:

mysql> SELECT actor.first_name, actor.last_name, COUNT(*)

-> FROM sakila.film_actor

-> INNER JOIN sakila.actor USING(actor_id)

-> GROUP BY actor.first_name, actor.last_name;

Эффективнее было бы переписать его в таком виде:

mysql> SELECT actor.first_name, actor.last_name, COUNT(*)

-> FROM sakila.film_actor

-> INNER JOIN sakila.actor USING(actor_id)

-> GROUP BY film_actor.actor_id;

Группировка по столбцу actor.actor_id может оказаться производительнее, чем по film_actor.actor_id. Чтобы выбрать правильное решение, необходимо профилировать запрос и тестировать его на реальных данных. В этом примере используется тот факт, что имя и фамилия актера зависят от значения поля actor_id, поэтому результат получится одинаковым. Однако не всегда можно так небрежно включить в список SELECT столбцы, по которым не производится группировка, в надежде получить тот же самый эффект. Более того, в конфигурационном файле сервера может быть задан параметр SQL_MODE, который вообще запрещает такой нестандартный режим.

MySQL автоматически упорядочивает результат запроса с группировкой по столбцам, перечисленным во фразе GROUP BY, если фраза ORDER BY явно не указана. Если для вас порядок не имеет значения, но вы видите, что в плане выполнения присутствует файловая сортировка (filesort), то можно включить фразу ORDER BY NULL, которая подавляет автоматическую сортировку. Можно также поместить сразу после GROUP BY необязательное ключевое слово DESC или ASC, задающее направление сортировки (по убыванию или по возрастанию).

Оптимизация LIMIT со смещением.

Запросы, содержащие ключевые слова LIMIT и OFFSET, часто встречаются в системах, производящих разбиение на страницы, и неизменно в сочетании с ORDER BY. Полезно иметь индекс, поддерживающий нужное упорядочение, иначе серверу придется слишком часто прибегать к файловой сортировке. Типичная проблема - слишком большое смещение. Если в запросе встречается фраза LIMIT 10000, 20, то сервер сгенерирует 10 020 строк и отбросит первые 10 000, а это очень дорого. В предположении, что доступ ко всем страницам производится с одинаковой частотой, такой запрос в среднем просматривает половину таблицы. Для оптимизации можно либо наложить ограничения на то, сколько страниц разрешено просматривать, или попытаться реализовать обработку больших смещений более эффективно. Один из простых приемов повышения производительности - выполнять смещение, пользуясь покрывающим индексом, а не исходной таблицей. Затем полученные результаты можно соединить с полными строками, чтобы дополнительно выбрать интересующие вас столбцы. Такой подход может оказаться намного эффективнее. Иногда можно также преобразовать запрос с LIMIT в позиционный запрос, который сервер сможет выполнить путем просмотра диапазона индекса.

Оптимизация UNION.

MySQL всегда выполняет запросы с UNION путем создания и заполнения временной таблицы. К подобным запросам MySQL может применить не так уж много оптимизаций. Однако есть возможность упростить работу оптимизатору, «опустив вниз» фразы WHERE, LIMIT, ORDER BY и другие условия (то есть переместить их из внешнего запроса в каждый SELECT, входящий в объединение). Очень важно всегда употреблять UNION ALL, если только не нужно, чтобы сервер устранял строки-дубликаты. Когда ключевое слово ALL отсутствует, MySQL будет создавать временную таблицу в режиме distinct, а это значит, что для соблюдения уникальности производится сравнение строк целиком. Такая операция обойдется очень недешево. Однако следует иметь в виду, что наличие слова ALL не отменяет необходимости во временной таблице. MySQL в обязательном порядке помещает в нее результаты, а затем читает их оттуда, даже если без этого и можно было бы обойтись (например, когда результаты можно было вернуть напрямую клиенту).

3.2 Реализация некоторых методов оптимизации

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

Прежде всего, необходимо убедиться в эффективности работы индексов.

Их работу мы будем проверять в следующих запросах:

mysql> select * from companies where row_id = 100;

Рис.2.1 - Результат поиска по индексированному полю.

mysql> select * from companies where company_name = 'AMERICAN STOCK TRANSFER & TRUST CO';

Рис.2.2 - Результат поиска по неиндексированному полю.

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

Рассмотрим еще один пример, связанный с индексами. Производится подсчет компаний, расположенных в Филадельфии. Приведенный далее запрос производится по неиндексированному полю company_locations:

mysql> select count(*) from company_locations where city = 'Philadelphia'

Рис.2.3 - Поиск по неиндексированному полю.

Чтобы убедиться в эффективности индексов, создадим индекс для поля company_locations:

mysql> alter table company_locations add index i_city(city);

Query OK, 1581307 rows affected (52,94 sec)

Records: 1581307 Duplicates: 0 Warnings: 0

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

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

mysql> select count(*) from company_locations where city = 'Philadelphia';

Рис.2.4 - Поиск по индексированному полю.

Используя встроенный в MySQL механизм - profiling, выведем таблицу, показывающую время выполнения каждого из этих запросов:

Рис.2.5 - Вывод profiling.

Основываясь на данных, предоставляемых этим механизмом, можно с уверенностью отметить, что при использовании индексов производительность увеличилась на порядок: время обработки запроса уменьшилось с 0.6 секунды до 0.1 секунды. Также на данном примере можно отметить высокую ресурсоемкость создания индексов, такая же ситуация и с удалением ненужных индексов.

Следующий рассматриваемый пример - запрос с использованием функции подсчета строк COUNT(). Нас интересует тот факт, что при подсчете строк в таблице следует использовать COUNT(*). Время выполнения данных запросов проверим также с помощью profiling.

mysql> select count(filing_id) from filings;

mysql> select count(filing_id) from filings where year IS NOT NULL;

Рис.2.6 - Вывод profiling.

Так как filing_id (Primary Index) не может быть NULL, оптимизатор приводит этот запрос к виду COUNT(*), следовательно, больше не требуется пересчитывать количество строк, поскольку подсистема хранения данных в любой момент времени знает, сколько в таблице строк. Второй запрос получает на выходе то же значение, но из-за присутствия проверки WHERE сервер вынужден пересчитывать все строки, что очень значительно сказывается на быстродействии.



Заключение

В результате исследовании в рамках данной работы было сделано следующее:

1. Проанализирована логическая структура СУБД MySQL.

Оптимизация в широком смысле не ограничивается одними лишь запросами. Необходимо понимать устройство и принципы работы программного продукта, а также иметь четкое представление о его области применения, ведь от нее зависит очень многое: размеры баз данных и отдельных таблиц, их количество и формат данных в них используемый. Мною были рассмотрены вопросы, которые, на мой взгляд, имеют наибольшее значение в деле повышения производительности.

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

Корпоративные системы, ERP/CRM-приложения;

Live-support systems - системы технической поддержки в реальном времени;

CMS - системы управления контентом;

Groupware - системы групповой работы, планировщики;

Форумы и чаты;

И т.п.

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

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

2. Рассмотрены основные методы оптимизации запросов.

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

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

3. Рассмотрены некоторые методы оптимизации на примере реальной базы данных.

На примере базы данных, имеющей более 8 млн. записей, была продемонстрирована эффективность оптимизации различными методами.

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



Список использованных источников

1. MySQL / [Электронный ресурс]: многопредмет.науч.журн. - Режим доступа: https://ru.wikipedia.org/wiki/MySQL, - Загл. с экрана.

2. Оптимизация запросов СУБД/ [Электронный ресурс]: многопредмет.науч.журн. - Режим доступа к журн.: https://ru.wikipedia.org/wiki/Оптимизация_запросов_СУБД, - Загл. с экрана.

3. Справочное руководство по MySQL / [Электронный ресурс]: многопредмет.науч.журн. - Режим доступа: http://www.mysql.ru/docs/man/Tutorial.html, - Загл. с экрана.

4. Работа с индексами в MySQL [Электронный ресурс]: многопредмет.науч.журн. - Режим доступа к журн.: https://ruhighload.com/post/Работа+с+индексами+в+MySQL, - Загл. с экрана.

5. Как узнать время выполнения запроса/ [Электронный ресурс]: многопредмет.науч.журн. -- Режим доступа: http://yournet.kz/blog/mysql/kak-uznat-vremya-vypolneniya-mysql-zaprosa, - Загл. с экрана.

6. Оптимизация сложных запросов MySQL/ [Электронный ресурс]: многопредмет.науч.журн. -- Режим доступа к журн.: https://habrahabr.ru/post/154167, - Загл. с экрана.

7. Оптимизация запросов. Просто о сложном / [Электронный ресурс]: Режим доступа: http://yournet.kz/blog/mysql/optimizaciya-mysql-prosto-o-slozhnom, - Загл.с экрана.

8. Кэширование запросов в MySQL / [Электронный ресурс]: многопредмет.науч.журн. - Режим доступа: http://webew.ru/articles/1041.webew, - Загл.с экрана.

9. Производительность MySQL/ [Электронный ресурс]: многопредмет.науч.журн. -- Режим доступа к журн.: http://hosting101.ru/articles/mysql-performance-1.html, - Загл.с экрана.

10. Дэвид М. Кренке. Теория и практика построения баз данных [Текст] / Дэвид М. Кренке. - СПб : Питер, 2005. - 864 с.

11. MySQL. Оптимизация производительности, 2-е издание. [Текст] / Шварц Б.[и др.] - СПб: Символ-Плюс, 2010. - 832 с.

12. М. Грабер. SQL для простых смертных [Текст] / М.Грабер-М: Лори, Москва, 2014. - 378 с.

Размещено на Allbest.ru

...