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

Размещено на http://www.allbest.ru/

Федеральное агентство связи

Федеральное государственное бюджетное образовательное учреждение

высшего образования

«Поволжский государственный университет телекоммуникаций и информатики»

Факультет Информационных систем и технологий

Направление (специальность) Информационные системы и технологии

Кафедра Информационных систем и технологий

ВЫПУСКНАЯ КВАЛИФИКАЦИОННАЯ РАБОТА

(БАКАЛАВРСКАЯ РАБОТА)

Оптимизация запросов в СУБД MySQL

Утверждаю зав.кафедрой, д.т.н., доцент Н.И. Лиманова

Руководитель доцент, к.т.н., О.С. Козлова

Н. контролер ассистент А.С. Лошкарев

Разработал ИСТ-32 М.В. Терин

Самара 2017



Содержание

Задание

Реферат

Введение

1. Логическая архитектура MySQL

1.1 Оптимизация и выполнение

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

1.3 Выбор оптимальных типов данных

1.4 Основы индексирования

1.5 Основные принципы выполнения запросов

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

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

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

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

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

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

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

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

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

Заключение

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

Приложение



Задание

по подготовке выпускной квалификационной работы

Студента Терина Максима Викторовича

1 Тема ВКР

Оптимизация запросов в СУБД MySQL

Утверждена приказом по университету от 03.04.2017 № 74-2

2 Срок сдачи студентом законченной ВКР 10.06.2017

3 Исходные данные и постановка задачи

- Методы оптимизации запросов SQL

- Опытная база данных

- Изучить наиболее распространенные методы оптимизации

- Разработать примеры запросов для проведения их оптимизации

- Оценить эффективность проведенной оптимизации

4 Перечень подлежащих разработке в ВКР вопросов или краткое содержание ВКР. Сроки исполнения 09.04.2017

1. Логическая архитектура MySQL

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

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

5 Перечень графического материала. Сроки исполнения 12.06.2017

Презентационные материалы

6 Дата выдачи задания « 04 » апреля 2017 г.

Кафедра Информационных систем и технологий

Утверждаю зав.кафедрой, д.т.н., доцент Н.И. Лиманова

Руководитель доцент,к.т.н. О.С. Козлова

Задание принял к исполнению ИСТ-32 М.В. Терин



Реферат

Название Оптимизация запросов в СУБД MySQL

Автор Терин Максим Викторович

Научный руководитель Козлова Ольга Семеновна

Ключевые слова СУБД, MySQL, база данных, оптимизация, запрос, индекс, время выполнения, производительность

Дата публикации 2017 год

Библиографическое описание

Терин М.В. Оптимизация запросов в СУБД MySQL[Текст]: бакалаврская работа / М.В.Терин. Поволжский государственный университет телекоммуникаций и информатики (ПГУТИ). Факультет информационных систем и технологий (ФИСТ). Кафедра информационных систем и технологий (ИСТ): науч.рук. Козлова О.С. - Самара. 2017. - 67 с.

Аннотация

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



Введение

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

Концептуально новой вехой в развитии информационных технологий стало внедрение понятия файла, как именованной области данных, и, файловых систем, как структур, определяющих способ организации и хранения файлов, а также доступа к данным, содержащимся в них. Файловые системы связывают физическое расположение данных на носителе информации с прикладными программами, посредством интерфейса программирования приложений (Application Programming Interface - API), предоставляемым драйвером файловой системы.

Однако эти средства все же не позволяли полностью избавиться от описанных выше проблем. В связи с тем, что структуры данных, которыми оперировали новые программные комплексы, постоянно усложнялись, разработчики программного обеспечения были вынуждены, как и раньше, создавать индивидуальные средства структуризации данных для каждого своего программного продукта. Для того чтобы повысить эффективность разработки программных продуктов, работающих со сложно структурированными данными, было необходимо средство, позволяющее обобщить элементы информационных систем (ИС), отвечающих за работу с этими данными. Это логичное и вполне рациональное стремление положило начало введения нового термина -- базы данных.

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

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

Сегодня SQL является стандартным инструментом управления данными, который включает в себя операторы определения данных (Data Definition Language, DDL), операторы манипуляции данными (Data Manipulation Language, DML), операторы определения доступа к данным (Data Control Language, DCL) и операторы управления транзакциями (Transaction Control Language, TCL).

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

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

Язык SQL, как основной инструмент работы с базами данных, играет первостепенную роль в вопросе разработки СУБД, а также администрирования баз данных. Так как базы данных во многих сферах могут достигать очень больших объемов (как по количеству таблиц, записей и полей, так и по общему объему хранимой информации), то естественно встает вопрос об эффективной, с точки зрения быстродействия, работы с этими данными.

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

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

Предметом исследования бакалаврской работы являются запросы к СУБД MySQL

Объектом исследования бакалаврской работы являются способы оптимизации запросов к СУБД MySQL.

Целью данной бакалаврской работы является изучение различных методов и путей оптимизации запросов к СУБД MySQL.

Задачи исследования:

Изучить логическую архитектуру СУБД MySQL.

Изучить способы оптимизации запросов СУБД MySQL.

Разработать примеры запросов для оптимизации и оценить их эффективность.

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

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

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

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

В заключении представлены основные выводы и результаты проделанной работы.



1. Логическая архитектура MySQL

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

Рис.1.1 - Логическая архитектура сервера MySQL.

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

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

Третий уровень содержит подсистемы хранения данных. Они отвечают за сохранение и извлечение всех данных, хранимых в MySQL. Подобно различным файловым системам GNU/Linux, каждая подсистема хранения данных имеет свои сильные и слабые стороны. Сервер взаимодействует с ними с помощью API (интерфейса прикладного программирования) подсистемы хранения данных. Этот интерфейс скрывает различия между подсистемами хранения данных и делает их почти прозрачными на уровне запросов. Кроме того, данный интерфейс содержит пару десятков низкоуровневых функций, выполняющих операции типа «начать транзакцию» или «извлечь строку с таким первичным ключом». Подсистемы хранения не производят синтаксический анализ кода SQL и не взаимодействуют друг с другом, они просто отвечают на исходящие от сервера запросы. MySQL поддерживает различные подсистемы хранения данных: MyISAM, MyISAM Merge, InnoDB, Memory, Archive и некоторые другие. Рассмотрим указанные подсистемы чуть подробнее.

MyISAM. Будучи подсистемой хранения по умолчанию в MySQL, MyISAM являет собой удачный компромисс между производительностью и функциональностью. Так, она предоставляет полнотекстовое индексирование, сжатие и пространственные функции (для геоинформационных систем - ГИС). MyISAM не поддерживает транзакции и блокировки на уровне строк. Хранение MyISAM обычно хранит каждую таблицу в двух файлах: файле данных и индексном файле. Эти файлы имеют расширения .MYD и .MYI. соответственно. Формат MyISAM не зависит от платформы, то есть можно копировать файлы данных и индексов с сервера на базе процессора Intel на сервер PowerPC или Sun SPARC безо всяких проблем. Таблицы типа MyISAM могут содержать как динамические, так и статические строки (строки фиксированной длины). MySQL самостоятельно решает, какой формат использовать, основываясь на определении таблицы. Количество строк в таблице типа MyISAM ограничено в первую очередь доступным дисковым пространством на сервере базы данных и максимальным размером файла, который позволяет создавать операционная система. Таблицы MyISAM со строками переменной длины, создаваемые в версии MySQL 5.0, настроены по умолчанию на поддержку 256 Тбайт данных с использованием шестибайтных указателей на записи с данными. В более ранних версиях MySQL указатели по умолчанию были четырехбайтными с максимальным объемом данных 4 Гбайт. Все версии MySQL могут поддерживать размер указателя до восьми байт.

MyISAM Merge. Подсистема хранения Merge является вариацией на тему MyISAM. Таблица типа Merge представляет собой объединение нескольких структурно одинаковых таблиц MyISAM в одну виртуальную таблицу. Это особенно полезно, когда MySQL используется для протоколирования и организации хранилищ данных.

InnoDB. Подсистема хранения InnoDB была разработана для транзакционной обработки, в частности для обработки большого количества краткосрочных транзакций, которые значительно чаще благополучно завершаются, чем откатываются. Она остается наиболее популярной транзакционной подсистемой хранения. Высокая производительность и автоматическое восстановление после сбоя делают ее популярной и для нетранзакционных применений. InnoDB сохраняет данные в наборе из одного или нескольких файлов данных. Этот набор файлов именуется табличным пространством (tablespace). Табличное пространство в сущности является черным ящиком, которым полностью управляет сама InnoDB. В MySQL 4.1 и более поздних версиях СУБД InnoDB может хранить данные и индексы каждой таблицы в отдельных файлах. Кроме того, данная подсистема может располагать табличные пространства на «сырых» (неформатированных) разделах диска.

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

Memory. Таблицы типа Memory (раньше называвшиеся таблицами типа HEAP) полезны, когда необходимо осуществить быстрый доступ к данным, которые либо никогда не изменяются, либо нет надобности в их сохранении после перезапуска. Обычно таблицы типа Memory обрабатываются примерно на порядок быстрее, чем таблицы MyISAM. Все их данные хранятся в памяти, поэтому запросам не нужно ждать выполнения операций дискового ввода/вывода. Структура таблицы Memory сохраняется после перезапуска сервера, но данные теряются. Таблицы Memory поддерживают индексы типа HASH, обеспечивающие очень большую скорость выполнения поисковых запросов. Таблицы Memory работают очень быстро, но не всегда годятся в качестве замены дисковых таблиц. Они используют блокировку на уровне таблицы, что уменьшает конкуренцию при записи, и не поддерживают столбцы типа TEXT и BLOB. Также они допускают использование только строк фиксированного размера, поэтому значения типа VARCHAR сохраняются как значения типа CHAR, что повышает расход памяти. MySQL внутри себя использует подсистему Memory для хранения промежуточных результатов при обработке запросов, которым требуется временная таблица.

Archive. Подсистема хранения Archive позволяет выполнять только команды INSERT и SELECT и до версии MySQL 5.1 не поддерживала индексирование. Она требует значительно меньше операций дискового ввода/вывода, чем MyISAM, поскольку буферизует записываемые данные и сжимает все вставляемые строки с помощью библиотеки zlib. Кроме того, каждый запрос SELECT требует полного сканирования таблицы. По этим причинам таблицы Archive идеальны для протоколирования и сбора данных, когда анализ чаще всего сводится к сканированию всей таблицы, а также в тех случаях, когда требуется обеспечить быстроту выполнения запросов INSERT на главном сервере репликации. Подчиненные серверы репликации могут использовать для той же таблицы другие подсистемы хранения, следовательно, существует возможность проиндексировать таблицу на подчиненном сервере для большей производительности при анализе. Подсистема Archive поддерживает блокировку на уровне строк и специальный системный буфер для вставок с высокой степенью конкурентности. Она обеспечивает согласованные чтения, останавливая выполнение команды SELECT после того, как извлечено столько строк, сколько было в таблице на момент начала выполнения запроса. Она также обеспечивает невидимость результатов пакетной вставки, пока процедура не будет завершена.

1.1 Оптимизация и выполнение

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

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

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

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



1.3 Выбор оптимальных типов данных

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

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

Для выполнения операций с более простыми типами данных обычно требуется меньше процессорного времени. Например, сравнение целых чисел дешевле сравнения символов, поскольку из-за различных кодировок и схем упорядочения (правил сортировки) сравнение символов усложняется. Несколько практических рекомендаций: следует хранить значения даты и времени во встроенных типах данных MySQL, а не в строках. Для IP-адресов имеет смысл использовать целочисленные типы данных.

Всюду, где это возможно, следует определять столбцы как NOT NULL. Очень часто в таблицах встречаются поля, допускающие хранение NULL (отсутствие значения), хотя приложению это совершенно не нужно, - просто потому, что такой режим выбирается по умолчанию. Однако не стоит объявлять столбец как NOT NULL, если у хранящихся в нем данных могут отсутствовать значения. Для MySQL оптимизация запросов, содержащих допускающие NULL столбцы, вызывает дополнительные сложности, поскольку из-за них усложняются индексы, статистика индексов и сравнение значений. Столбец, допускающий NULL, занимает больше места на диске и требует специальной обработки внутри MySQL. Когда такой столбец проиндексирован, ему требуется дополнительный байт для каждой записи, а в MyISAM даже может возникнуть ситуация, когда придется преобразовать индекс фиксированного размера (например, индекс по одному целочисленному столбцу) в индекс переменного размера. Даже когда требуется представить в таблице факт отсутствия значения, можно обойтись без использования NULL. Вместо этого иногда можно использовать нуль, специальное значение или пустую строку. Повышение производительности в результате замены столбцов NULL на NOT NULL обычно невелико, так что нет необходимости делать их поиск и изменение в существующих схемах приоритетом, если нет уверенности, что именно они вызывают проблемы. Однако если в дальнейшем планируется индексировать столбцы, нужно по возможности определять их как NOT NULL.

Первым шагом в принятии решения, какой тип данных использовать для столбца, является определение общего класса типов: числовые, строковые, временные и т. п. Обычно никаких сложностей при этом не возникает, однако бывают ситуации, когда выбор неочевиден. Следующим шагом является выбор конкретного типа. Многие типы данных MySQL позволяют хранить данные одного и тот же вида, но с разным диапазоном значений, точностью или требуемым физическим пространством (на диске или в памяти). Некоторые типы обладают специальным поведением или свойствами. Например, в столбцах DATETIME и TIMESTAMP можно хранить один и тот же тип данных: дату и время, с точностью до секунды. Однако тип TIMESTAMP требует вдвое меньше места, позволяет работать с часовыми поясами и обладает специальными средствами автоматического обновления. С другой стороны, диапазон допустимых значений для него намного уже. В целях совместимости MySQL поддерживает различные псевдонимы, например, INTEGER, BOOL и NUMERIC. Все это - именно псевдонимы одного и того же типа данных. Данный факт может сбить с толку, но не оказывает влияния на производительность.

1.4 Основы индексирования

Индексы представляют собой структуры, которые помогают MySQL эффективно извлекать данные. Они критичны для достижения хорошей производительности, поэтому индексирование является главной причиной проблем с производительностью в реальных условиях. Важность индексов (именуемых в MySQL также ключами) увеличивается по мере роста объема данных. Небольшие, слабо загруженные базы зачастую могут удовлетворительно работать даже без правильно построенных индексов, но по мере роста объемов хранимой в базе информации производительность может падать очень быстро. Самый простой способ понять, как работает индекс в MySQL, - представить себе алфавитный указатель в книге. Чтобы выяснить, в какой части издания обсуждается конкретный вопрос, вы смотрите в алфавитный указатель и находите номер страницы, где упоминается термин. MySQL использует индексы сходным образом. Она ищет значение в структурах данных индекса. Обнаружив соответствие, она может перейти к самой строке. Допустим, выполняется следующий запрос:

mysql> SELECT first_name FROM sakila.actor WHERE actor_id = 5;

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

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

B-Tree-индексы.

Когда говорят об индексе без упоминания типа, обычно имеют в виду B-Tree индексы, в которых для хранения данных используется структура, называемая B-tree. Большинство подсистем хранения в MySQL поддерживает этот тип индекса. Термин «B-tree» для этих индексов используется потому, что именно так MySQL называет их в CREATE TABLE и других командах. Подсистемы хранения представляют B-Tree-индексы на диске по-разному, и это может влиять на производительность. Например, в MyISAM используется техника сжатия префикса, позволяющая уменьшить размер индекса, а InnoDB не сжимает индексы, поскольку это лишило бы ее возможности выполнять некоторые оптимизации. Кроме того, индексы MyISAM ссылаются на индексированные строки по их физическому адресу на диске, а InnoDB - по значениям первичного ключа. Каждый вариант имеет свои достоинства и недостатки. Общая идея B-дерева заключается в том, что значения хранятся по порядку, и все листовые страницы находятся на одинаковом расстоянии от корня. На рис.1.2 показано абстрактное представление B-Tree-индекса, которое приблизительно соответствует тому, как работают индексы InnoDB (InnoDB использует структуру данных B+Tree). В MyISAM другая структура, но те же принципы.

Рис.1.2 - Индекс, построенный на основе структуры B-tree.

B-Tree-индекс ускоряет доступ к данным, поскольку подсистеме хранения не нужно сканировать всю таблицу для поиска нужной информации. Вместо этого она начинает с корневого узла (не показанного на этом рисунке). В корневом узле имеется массив указателей на дочерние узлы, и подсистема хранения переходит по этим указателям. Чтобы найти подходящий указатель, она просматривает значения в узловых страницах, которые определяют верхнюю и нижнюю границы значений в дочерних узлах. В конечном итоге подсистема хранения либо определяет, что искомое значение не существует, либо благополучно достигает листовой страницы. Листовые страницы представляют собой особый случай, так как в них находятся указатели на индексированные данные, а не на другие страницы.

Поскольку в B-Tree индексах индексированные столбцы хранятся в упорядоченном виде, то они полезны для поиска по диапазону данных. Например, при спуске вниз по дереву индекса, построенного по текстовому полю, значения перебираются в алфавитном порядке, поэтому поиск «всех лиц, чьи фамилии начинаются с буквы от В до Д» оказывается эффективным.

B-Tree-индексы хорошо работают при поиске по полному значению ключа, по диапазону ключей или по префиксу ключа. Они полезны только в том случае, когда в процессе поиска используется самый левый префикс ключа (особенность некоторых версий MySQL).

Поскольку узлы дерева отсортированы, их можно использовать как для поиска значений, так и в запросах с фразой ORDER BY (возврат отсортированных строк). В общем случае, если B-Tree индекс позволяет найти строку по определенному критерию, то его можно использовать и для сортировки строк по тому же критерию.

У B-Tree-индексов есть некоторые ограничения:

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

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

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

Хеш-индексы.

Хеш-индекс строится на основе хеш-таблицы и полезен только для точного поиска с указанием всех столбцов индекса. Для каждой строки подсистема хранения вычисляет хеш-код индексированных столбцов - сравнительно короткое значение, которое, скорее всего, будет различно для строк с разными значениями ключей. В индексе хранятся хеш-коды и указатели на соответствующие строки. В MySQL только подсистема хранения Memory поддерживает явные хеш-индексы. Этот тип индекса принимается по умолчанию для таблиц типа Memory, хотя над ними можно строить также и B-Tree-индексы. Подсистема Memory поддерживает неуникальные хеш-индексы, что в мире баз данных является необычным. Если для нескольких строк хеш-код одинаков, то в индексе будет храниться связанный список указателей на эти строки.

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

* Поскольку индекс содержит только хеш-коды и указатели на строки, а не сами значения, MySQL не может использовать данные в индексе, чтобы избежать чтения строк. К счастью, доступ к строкам в памяти очень быстр, так что обычно это не снижает производительность.

* MySQL не может использовать хеш-индексы для сортировки, поскольку строки в нем не хранятся в отсортированном порядке.

* Хеш-индексы не поддерживают поиск по частичному ключу, так как хеш-коды вычисляются для всего индексируемого значения.

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

Собственные хеш-индексы.

Если подсистема хранения не поддерживает хеш-индексы, то возможно эмулировать их самостоятельно подобно тому, как это делает InnoDB. Данный подход позволяет использовать некоторые достоинства хеш-индексов, например, небольшие размеры при очень длинных ключах. Идея проста: создание псевдохеш-индекса поверх стандартного B-Tree-индекса. Он будет не совсем идентичен настоящему хеш-индексу, поскольку для поиска по-прежнему будет использоваться B-Tree-индекс. Однако искаться будут хеш-коды ключей вместо самих ключей. От пользователя требуется лишь вручную указать хеш-функцию во фразе WHERE запроса.

Одним из недостатков данного решения является необходимость обновлять хеш-значения. Можно делать это вручную или - в MySQL 5.0 и более поздних версиях - использовать триггеры.

При таком подходе не следует использовать хеш-функции SHA1() или MD5(). Они возвращают очень длинные строки, которые требуют много пространства и приводят к замедлению сравнения. Это мощные криптографические функции, предназначенные для почти гарантированного устранения коллизий. Простые хеш-функции могут дать приемлемый уровень коллизий с лучшей производительностью.

Пространственные индексы.

MyISAM поддерживает пространственные индексы (Spatial, R-Tree), которые можно строить по столбцам пространственного типа, например, GEOMETRY. Однако для того чтобы R-Tree индексы работали, необходимо использовать геоинформационные функции MySQL, например, MBRCONTAINS().

Полнотекстовые индексы.

Полнотекстовый (FULLTEXT) индекс представляет собой специальный тип индекса для таблиц типа MyISAM. Он позволяет искать в тексте ключевые слова, а не сравнивать искомое значение со значениями в столбце. Полнотекстовый поиск не имеет ничего общего с другими типами поиска. С ним связано много тонкостей, например, стоп-слова, стемминг, учет множественного числа, а также булевский поиск. Он гораздо больше напоминает поисковые системы, нежели обычное сравнение с критерием во фразе WHERE. Наличие полнотекстового индекса по столбцу не делает B-Tree-индекс по этому же столбцу менее ценным. Полнотекстовые индексы предназначены для операций MATCH AGAINST, а не обычных операций с фразой WHERE.

1.5 Основные принципы выполнения запросов

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

1. Клиент отправляет SQL-команду серверу.

2. Сервер смотрит, есть ли эта команда в кэше запросов. Если да, то возвращается сохраненный результат из кэша; в противном случае выполняется следующий шаг.

3. Сервер осуществляет разбор, предварительную обработку (preprocesing) и оптимизацию SQL-команды, преобразуя ее в план выполнения запроса.

4. Подсистема выполнения запросов выполняет этот план, обращаясь к подсистеме хранения.

5. Сервер отправляет результат клиенту.



Рис.1.3 - Порядок выполнения запроса.

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

Транзакции.

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

Компания MySQL AB предоставляет пользователям три транзакционных подсистемы хранения данных: InnoDB, NDB Cluster и Falcon. MySQL по умолчанию работает в режиме AUTOCOMMIT. Это означает, что если транзакция не начата явным образом, каждый запрос автоматически выполняется в отдельной транзакции.

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

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

Транзакций недостаточно, если система не проходит тест ACID. Аббревиатура ACID расшифровывается как Atomicity, Consistency, Isolation и Durability (атомарность, непротиворечивость, изолированность и долговечность). Это тесно связанные критерии, которым должна соответствовать правильно функционирующая система транзакционной обработки. Также стоит отметить, что сервер базы данных с транзакциями ACID обычно требует большей мощности процессора, объема памяти и дискового пространства, чем без них.

Атомарность. Транзакция должна функционировать как единая неделимая единица работы таким образом, чтобы вся транзакция была либо выполнена, либо отменена. Когда транзакции являются атомарными, не существует такого понятия, как частично выполненная транзакция: все или ничего.

Непротиворечивость. База данных должна всегда переходить из одного непротиворечивого состояния в последующее.

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

Долговечность. Будучи зафиксированы, внесенные в ходе транзакции изменения становятся постоянными. Это означает, что изменения должны быть записаны так, чтобы данные не могли быть потеряны в случае сбоя системы. Долговечность, однако, является несколько расплывчатой концепцией, поскольку на самом деле существует много уровней. Некоторые стратегии обеспечения долговечности предоставляют более сильные гарантии безопасности, чем другие, и ни одна из них не является надежной на 100%.

Кэш запросов.

Многие СУБД умеют кэшировать планы выполнения, что позволяет серверу пропустить стадии разбора и оптимизации запросов, уже встречавшихся ранее. MySQL при некоторых условиях тоже может это делать, но, кроме того, у нее имеется кэш особого вида (так называемый кэш запросов), в котором хранятся полные результирующие наборы, сгенерированные командами SELECT. Этот раздел посвящен именно такому кэшу. В кэше запросов MySQL хранится в точности тот результат, который запрос вернул клиенту. При попадании в кэш запросов сервер сразу же возвращает сохраненные итоги, пропуская стадии разбора, оптимизации и выполнения. Кэш запросов отслеживает, какие таблицы были использованы в запросе, и, если хотя бы одна из них изменилась, данные в кэше становятся недействительными. Такая грубая политика определения недействительности (инвалидации) может показаться неэффективной, поскольку внесенные изменения, возможно, и не отражаются на сохраненных результатах. Однако накладные расходы, сопряженные с таким упрощенным подходом, невелики, что очень важно для сильно нагруженной системы. Кэш запросов спроектирован так, что остается абсолютно прозрачным для приложений. Программе не нужно знать, откуда она получила данные: из кэша или в результате реального выполнения запроса. В любом случае результат будут одинаковым. Иными словами, кэш запросов не изменяет семантику - с точки зрения приложения, поведение сервера не зависит от того, активирован кэш или нет.

Для проверки попадания в кэш MySQL применяет простую и очень быструю методику: по сути кэш - это справочная таблица (lookup table). Ключом этой таблицы является хеш, рассчитанный по тексту запроса, текущей базе данных, номеру версии клиентского протокола и еще нескольким параметрам, от которых могут зависеть результаты обработки запроса. При проверке попадания в кэш MySQL не разбирает, не «нормализует» и не параметризует команду; текст команды и прочие данные используются точно в том виде, в каком они получены от клиента. Любое различие, будь то регистр символов, лишние пробелы или комментарии, приведет к тому, что новый запрос не совпадет с кэшированной версией. Об этом следует помнить при написании запросов. Следовать единому стилю или способу форматирования - вообще хорошая привычка, но в данном случае она может еще и ускорить работу системы. Следует также иметь в виду, что результат не попадет в кэш запросов, если сгенерирован недетерминированным запросом. Иначе говоря, любой запрос, содержащий недетерминированную функцию, например NOW() или CURRENT_DATE(), не кэшируется. Аналогично, такие функции, как CURRENT_USER() и CONNECTION_ID(), дают разные результаты в зависимости от того, каким пользователем вызваны, поэтому также препятствуют записи запроса в кэш. Кроме того, кэш не работает для запросов, в которых есть ссылки на определенные пользователем функции, хранимые процедуры, пользовательские переменные, временные таблицы, таблицы в базе данных mysql и таблицы, для которых заданы привилегии на уровне столбцов (полный перечень всех причин, по которым запрос не кэшируется, см. в руководстве по MySQL).

Поскольку кэш запросов работает с полным текстом команды SELECT, идентичные запросы, сделанные внутри подзапроса или представления, не могут воспользоваться кэшем, равно как и запросы внутри хранимых процедур. В версиях до MySQL 5.1 подготовленные запросы также не могли быть закэшированы. Кэш запросов MySQL может повысить производительность, но при работе с ним нужно помнить о некоторых тонкостях. Во-первых, если кэш включен, добавляются накладные расходы как при чтении, так и при записи:

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

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

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

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

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

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

Теоретически определить, полезен кэш или нет, можно, сравнив объем работы, который сервер должен проделать с включенным и отключенным кэшем. Если кэш отключен, то сервер выполняет все запросы на чтение и на запись, причем в первом случае - еще генерирует и возвращает результаты. Если кэш включен, то при обработке любого запроса на чтение необходимо сначала проверить кэш, а затем либо вернуть сохраненный результат, либо (если его нет в кэше) выполнить запрос, сгенерировать результирующий набор и отослать его клиенту. При обработке любого запроса на запись его следует сначала выполнить, а потом проверить, нужно ли объявить недействительными какие-то записи в кэше. Хотя выглядит все это достаточно просто, точно предсказать выигрыш от использования кэша затруднительно. Необходимо принимать во внимание еще и внешние факторы. Например, кэш запросов может сократить время, необходимое для формирования результата, но не время, требующееся для отправки его клиенту, а именно оно может оказаться доминирующим фактором. Выигрывают от наличия кэша те запросы, которые долго выполняются, но занимают в кэше немного места, так что их хранение, возврат клиенту и инвалидация обходятся дешево. В эту категорию попадают, в частности, агрегирующие запросы, например, с функцией COUNT() для больших таблиц. Но есть и много других типов запросов, которые имеет смысл кэшировать. Один из самых простых способов понять, дает ли кэш выигрыш, - посмотреть на коэффициент попаданий в кэш. Он показывает, сколько запросов было обслужено из кэша, а не выполнено сервером с нуля. Когда сервер получает команду SELECT, он увеличивает одну из двух переменных состояния: Qcache_hits или Com_select, в зависимости от того, был запрос кэширован или нет. Поэтому коэффициент попаданий в кэш вычисляется по формуле Qcache_hits / (Qcache_hits+Com_select).

Любой запрос SELECT, не обслуженный из кэша, называется непопаданием в кэш. Не попасть в кэш можно по следующим причинам:

Запрос не допускает кэширования, поскольку либо содержит недетерминированную конструкцию (например, функцию CURRENT_DATE), либо потому что результирующий набор слишком велик для сохранения в кэше. В обоих случаях переменная Qcache_not_cached увеличивается на единицу.

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

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

Если количество непопаданий в кэш велико, а количество некэшируемых запросов очень мало, то верно одно из следующих утверждений:

Сервер не успел заполнить кэш результатами запросов.

Сервер постоянно видит запросы, которые раньше не встречались.

Слишком часто записи в кэше объявляются недействительными.

Запись может стать недействительной из-за фрагментации, нехватки ресурсов или изменения данных. Если вы отвели под кэш достаточно памяти и правильно настроили параметр query_cache_min_res_unit, то инвалидация по большей части обусловлена изменением хранимых значений. Узнать, сколько запросов модифицировали данные, позволяют переменные состояния Com_* (Com_update, Com_delete и т. д.), а сколько запросов было объявлено недействительными из-за нехватки памяти, - переменная Qcache_lowmem_prunes. Имеет смысл рассматривать издержки на инвалидацию отдельно от коэффициента попаданий. Предположим, например, что из одной таблицы производится только чтение, при этом коэффициент попадания равен 100%, а в другой - только обновление. Если вычислить коэффициент попадания по переменным состояния, то он составит 100%. Но даже в этом случае использование кэша может оказаться неэффективным, поскольку замедляет выполнение запросов на обновление. При обработке любого запроса на обновление приходится проверять, не нужно ли объявить недействительными какие-то кэшированные данные, но поскольку ответ неизменно будет «нет», то вся эта работа проделана впустую. Такого рода проблему можно и не заметить, если не проанализировать совместно количество некэшируемых запросов и коэффициент попадания. Сервер, для которого смесь операций записи и кэшируемого чтения из одних и тех же таблиц сбалансирована, тоже не всегда выигрывает от наличия кэша запросов. Любая операция записи делает запросы в кэше недействительными, тогда как любая операция чтения вставляет в кэш новый запрос. А выигрыш можно получить только, если эти запросы потом обслуживаются из кэша.

Процесс оптимизации запроса.

Следующий шаг в жизненном цикле запроса - преобразование SQL-команды в план выполнения, необходимый подсистеме выполнения запросов. Он состоит из нескольких этапов: разбор, предварительная обработка (preprocessing) и оптимизация. Ошибки (например, синтаксические) возможны в любой точке этого процесса. Поскольку в бакалаврской работе не требуется строгое документирование внутреннего устройства MySQL, можно позволить описывать шаги по отдельности, хотя часто они ради эффективности целиком или частично совмещены. Цель рассмотрения этого вопроса одна - разобраться в том, как MySQL выполняет запросы, чтобы понимать, как составить запросы более оптимально.

Анализатор и препроцессор.

Прежде всего, анализатор MySQL разбивает запрос на лексемы и строит «дерево разбора». Для интерпретации и проверки запроса анализатор использует грамматику языка SQL. В частности, проверяется, что все лексемы допустимы, следуют в нужном порядке и нет других ошибок, например, непарных кавычек. Затем получившееся дерево разбора передается препроцессору, который контролирует дополнительную семантику, не входящую в компетенцию анализатора. К примеру, проверяется, что указанные таблицы и столбцы существуют, а ссылки на столбцы не допускают неоднозначного толкования. Далее препроцессор проверяет привилегии. Обычно этот шаг выполняется очень быстро, если только на сервере определено не слишком много привилегий.

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

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

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

Оптимизатор соединений пытается построить дерево плана выполнения запроса с минимально достижимой стоимостью. Если возможно, он исследует все потенциальные перестановки поддеревьев. К сожалению, для полного исследования соединения n таблиц нужно перебрать n-факториал возможных перестановок. Это называется пространством поиска всех возможных планов выполнения, и его размер растет очень быстро - соединить 10 таблиц можно 3 628 800 способами. Если пространство поиска оказывается слишком большим, то для оптимизации запроса потребуется чересчур много времени, поэтому сервер отказывается от полного анализа. Если количество соединяемых таблиц превышает значение параметра optimizer_search_depth, то сервер применяет сокращенные алгоритмы, например «жадный» поиск. Для ускорения стадии оптимизации в MySQL реализовано множество эвристических приемов, выработанных за годы экспериментов. Эти приемы, конечно, полезны, но означают также, что иногда (в редких случаях) MySQL может пропустить оптимальный план и выбрать менее удачный, поскольку СУБД не пыталась исследовать все возможные планы.

...