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

Технологии хранения данных в то время доставляли множество неудобств разработчикам программного обеспечения, т.к. разработка каждой прикладной программы, требующей сохранения данных на внешний носитель, требовала собственной реализации логической структуры хранения блоков данных. Концептуально новой вехой в развитии информационных технологий стало внедрение понятия файла, как именованной области данных, и, файловых систем, как структур, определяющих способ организации и хранения файлов, а также доступа к данным, содержащимся в них. Файловые системы связывают физическое расположение данных на носителе информации с прикладными программами, посредством интерфейса программирования приложений (Application Programming Interface - API), предоставляемым драйвером файловой системы. Для того чтобы повысить эффективность разработки программных продуктов, работающих со сложно структурированными данными, было необходимо средство, позволяющее обобщить элементы информационных систем (ИС), отвечающих за работу с этими данными. К сожалению, многие разработчики не так часто задумываются о оптимизации SQL-запросов, напрасно полагая, что современное аппаратное обеспечение поможет компенсировать лишние затраты, возникающие при выполнении неэффективных SQL-запросов.На этом уровне реализуется любая независимая от подсистемы хранения данных функциональность, например, хранимые процедуры, триггеры и представления. Сервер взаимодействует с ними с помощью API (интерфейса прикладного программирования) подсистемы хранения данных. Подсистемы хранения не производят синтаксический анализ кода SQL и не взаимодействуют друг с другом, они просто отвечают на исходящие от сервера запросы. Хранение MYISAM обычно хранит каждую таблицу в двух файлах: файле данных и индексном файле. Формат MYISAM не зависит от платформы, то есть можно копировать файлы данных и индексов с сервера на базе процессора Intel на сервер POWERPC или Sun SPARC безо всяких проблем.MYSQL осуществляет синтаксический разбор запросов для создания внутренней структуры (дерева разбора), а затем выполняет ряд оптимизаций. На работу оптимизатора можно повлиять, включив в запрос специальные ключевые слова-подсказки (hints). Оптимизатор не интересуется тем, в какой подсистеме хранения данных находится каждая таблица, но подсистема хранения данных влияет на то, как сервер оптимизирует запрос.Оптимизация неудачно спроектированной или плохо проиндексированной схемы может увеличить производительность на порядки. Если от системы требуется высокое быстродействие, необходимо разработать схему и индексы под те конкретные запросы, которые в этой системе будут выполняться. Кроме того, следует оценить требования к производительности для различных типов запросов, поскольку изменения в одном из них или в одной части схемы могут иметь последствия в других местах.Меньшие по размеру типы данных обычно быстрее, поскольку занимают меньше места на диске, в памяти и в кэше процессора. Крайне важно избегать недооценки диапазона возможных значений данных, поскольку увеличение размерности типа данных во многих местах схемы может оказаться болезненным и длительным процессом. Однако не стоит объявлять столбец как NOT NULL, если у хранящихся в нем данных могут отсутствовать значения. Для MYSQL оптимизация запросов, содержащих допускающие NULL столбцы, вызывает дополнительные сложности, поскольку изза них усложняются индексы, статистика индексов и сравнение значений. Столбец, допускающий NULL, занимает больше места на диске и требует специальной обработки внутри MYSQL.Она ищет значение в структурах данных индекса. По столбцу actor_id построен индекс, поэтому СУБД MYSQL будет использовать его для поиска строк, в которых значением поля actor_id является 5. Таким образом, они не стандартизованы: в каждой подсистеме индексы работают немного по-разному, и далеко не все подсистемы допускают использование существующего разнообразия индексов. Когда говорят об индексе без упоминания типа, обычно имеют в виду B-Tree индексы, в которых для хранения данных используется структура, называемая B-tree. На рис.1.2 показано абстрактное представление B-Tree-индекса, которое приблизительно соответствует тому, как работают индексы INNODB (INNODB использует структуру данных B Tree).Сервер осуществляет разбор, предварительную обработку (preprocesing) и оптимизацию SQL-команды, преобразуя ее в план выполнения запроса. С точки зрения рассматриваемой темы бакалаврской работы, нас в большей степени интересуют следующие этапы выполнения запроса: проверка кэша запросов и оптимизация запроса. Кроме того, кэш не работает для запросов, в которых есть ссылки на определенные пользователем функции, хранимые процедуры, пользовательские переменные, временные таблицы, таблицы в базе данных mysql и таблицы, для которых заданы привилегии на уровне столбцов (полный перечень всех причин, по которым запрос не кэшируется, см. в руководстве по MYSQL). Во-первых, если кэш включен, добавляются накладные расходы как при чтении, так и при записи: Перед начал

Содержание

Задание

Реферат

Введение

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 с.

Аннотация