Оптимизация запросов к бд реферат

Обновлено: 05.07.2024

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

Введение………………………………….……………………. ……………….…. 3
1. Краткая история языка SQL……………………….……. …. 5
2. Основные определения и состав языка SQL…….……. …….10
2.1. Основные определения языка SQL…………………………. ………….……..10
2.2.Состав языка SQL……………………………………….…. …………….…..11
3. Основные команды языка SQL. 13
3.1. Запросы в языке SQL…………………………………. ……………. 17
3.1.1. Создание запроса. Что такое зарос. 17
3.1.2. Где применяются запросы….…………………………..…………………. 17
Заключение………………………. ……………. ……………….…………..…. 18
Глоссарий………………………………………………………………………………. 20
Список использованных источников…….…………. ………….…. ………. 22
Приложение А Архитектура СУБД: однозвенная, двухзвенная, трехзвенная ……. 23

Файлы: 1 файл

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

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

Претерпела некоторые изменения общая организация стандарта. Стандарт SQL:2003 состоит из следующих частей:

9075-1, SQL/Framework;
9075-2, SQL/Foundation;
9075-3, SQL/CLI;
9075-4, SQL/PSM;
9075-9, SQL/MED;
9075-10, SQL/OLB;
9075-11, SQL/Schemata;
9075-13, SQL/JRT;
9075-14, SQL/XML.

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

2 Основные определения и состав языка SQL

2.1. Основные определения языка SQL

SQL (обычно произносимый как " СИКВЭЛ" или "ЭСКЮЭЛЬ") символизирует собой Структурированный Язык Запросов. Это - язык, который дает возможность создавать и работать в реляционных базах данных, являющихся наборами связанной информации, сохраняемой в таблицах. Информационное пространство становится более унифицированным. Это привело к необходимости создания стандартного языка, который мог бы использоваться в большом количестве различных видов компьютерных сред.

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

Стандарт SQL определяется ANSI (Американским Национальным Институтом Стандартов) и в данное время также принимается ISO (Международной Организацией по Стандартизации).

Однако, большинство коммерческих программ баз данных расширяют SQL без уведомления ANSI, добавляя различные особенности в этот язык, которые, как они считают, будут весьма полезны. Иногда они несколько нарушают стандарт языка, хотя хорошие идеи имеют тенденцию развиваться и вскоре становиться стандартами "рынка" сами по себе в силу полезности своих качеств. 4

2.2. Состав языка SQL

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

язык манипулирования данными (Data Manipulation Language, DML)
язык определения данных (Data Definition Language, DDL)
язык управления данными (Data Control Language, DCL).

Это не отдельные языки, а различные команды одного языка.

Язык манипулирования данными используется, как это следует из его названия, для манипулирования данными в таблицах баз данных. Он состоит из 4 основных команд:

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

CREATE DATABASE (создать базу данных)

CREATE TABLE (создать таблицу)

CREATE VIEW (создать виртуальную таблицу)

CREATE INDEX (создать индекс)

CREATE TRIGGER (создать триггер)

CREATE PROCEDURE (создать сохраненную процедуру)

ALTER DATABASE (модифицировать базу данных)

ALTER TABLE (модифицировать таблицу)

ALTER VIEW (модифицировать виртуальную таблицу)

ALTER INDEX (модифицировать индекс)

ALTER TRIGGER (модифицировать триггер)

ALTER PROCEDURE (модифицировать сохраненную процедуру)

DROP DATABASE (удалить базу данных)

DROP TABLE (удалить таблицу)

DROP VIEW (удалить виртуальную таблицу)

DROP INDEX (удалить индекс)

DROP TRIGGER (удалить триггер)

DROP PROCEDURE (удалить сохраненную процедуру).

Язык управления данными используется для управления правами доступа к данным и выполнением процедур в многопользовательской среде. Более точно его можно назвать "язык управления доступом". Он состоит из двух основных команд:

GRANT (дать права)

REVOKE (забрать права)

С точки зрения прикладного интерфейса существуют две разновидности команд SQL:

Интерактивный SQL используется в специальных утилитах (типа WISQL или DBD), позволяющих в интерактивном режиме вводить запросы с использованием команд SQL, посылать их для выполнения на сервер и получать результаты в предназначенном для этого окне.

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

3 Основные команды языка SQL

В самой простой форме команда SELECT просто инструктирует БД, чтобы извлечь информацию из таблицы. Пример команды:

SELECT список столбцов таблицы

FROM имя таблицы

Другими словами, эта команда просто выводит все данные из таблицы. Вот объяснение каждой части этой команды:

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

Дальше идет список столбцов из таблицы, которые выбираются запросом. Любые столбцы, не перечисленные здесь, не будут включены в вывод команды. Это, конечно, не значит, что они будут удалены или их информация будет стёрта из таблиц, ведь запрос не воздействует на информацию в таблицах; он только показывает данные.

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

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

С тех пор как SQL использует точку с запятой, чтобы указывать конец команды, большинство программ SQL обрабатывают возврат каретки (через нажатие Возврат или клавиши ENTER ) как пробел.

Если необходимо вывести все столбцы таблицы, не обязательно писать все имена столбцов, звёздочка (*) может применяться для вывода полного списка столбцов следующим образом: SELECT * FROM Sales;

INSERT INTO имя таблицы

[( имя столбца. )] выражение запроса

| конструктор значений таблицы

Данный оператор вносит одну или более строк в таблицу, имя которой указано в операторе. Вставляемые строки являются результатом исполнения запроса (выражение для которого определено в операторе) или представляют собой конструкторы значений строк из списка конструкторов значений таблицы. Эти строки должны относиться к тому же типу данных, что и столбцы, в которые они вносятся. Если в один из столбцов нельзя записать значение по умолчанию (например, когда установлено ограничение NOT NULL, но не определено никаких других значений), то выполнение оператора INSERT будет прервано. Если же не будет указан список имен столбцов, то во все столбцы таблицы должны быть занесены значения, предусмотренные в операторе INSERT. Количество и порядок имен столбцов в списке должны соответствовать количеству и порядку имен столбцов, полученных в результате запроса. 6

В качестве таблицы может выступать представление. В этом случае представление должно быть обновляемым, а новые строки вставляются в базовую таблицу, содержащую данные, на основе которых построено представление (так называемую основную "таблицу-лист'). Для представления может быть определено предложение WITH CHECK OPTION, которое будет ограничивать заносимые значения. В действительности может быть несколько уровней представлений "на пути" к базовой таблице, и если они помечены как WITH CASCADED CHECK OPTION, то оператор INSERT может быть отвергнут при нарушении условий какого-то из них

Если текущий тип транзакции (TRANSACTION MODE) определен как READ ONLY (только чтение), таблица должна быть временной, иначе оператор INSERT будет отвергнут.

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

Данный оператор заносит строку в таблицу people, используя конструктор значений таблицы.

INSERT INTO people (ID_num. Iname, fname, city)

VALUES (1023, 'Blanco', Mercedes', 'Barcelona')

UPDATE [DatabaseName1!]TableName1
SET Column_Name1 = eExpression1
[, Column_Name2 = eExpression2 . ]
WHERE FilterCondition1 [AND | OR FilterCondition2 . ]]

Где UPDATE задает таблицу, в которой необходимо обновить записи.

Аргумент DatabaseName1! задает имя содержащей таблицу базы данных, отличной от текущей. Это имя необходимо указывать, когда база данных, которой принадлежит таблица, не является текущей. Восклицательный знак (!) служит разделителем между именем базы и именем таблицы базы данных.

SET Column_Name1 = eExpression1
[, Column_Name2 = eExpression2

Задает обновляемые столбцы и их новые значения. Если предложение WHERE опущено, каждая строка одного столбца обновляется одним и тем же значением. WHERE FilterCondition1 [AND | OR FilterCondition2 . ]]

Определяет, какие записи следует обновлять. Условие FilterCondition задает критерий, которому должны удовлетворять обновляемые записи. Можно включить сколько угодно условий фильтрования, объединяя их операторами AND и OR. Можно также использовать оператор NOT, инвертирующий значение логического выражения, и функцию EMPTY(), проверяющую, является ли поле пустым. 7

Как вырасти в 10 раз под количеству запросов к БД не переезжая на более производительный сервер и сохранить работоспособность системы? Я расскажу, как мы боролись с падением производительности нашей базы данных, как оптимизировали SQL запросы, чтобы обслуживать как можно больше пользователей и не повышать расходы на вычислительные ресурсы.

Я делаю сервис для управления бизнес процессами в строительных компаниях. С нами работает около 3 тысяч компаний. Более 10 тысяч человек каждый день работают с нашей системой по 4-10 часов. Она решает разные задачи планирования, оповещения, предупреждения, валидации… Мы используем PostgreSQL 9.6. В базе данных у нас около 300 таблиц и каждые сутки в нее поступает до 200 млн запросов (10 тысяч различных). В среднем у нас 3-4 тысяч запросов в секунду, в самые активные моменты более 10 тысяч запросов в секунду. Большая часть запросов — OLAP. Добавлений, модификаций и удалений намного меньше, то есть OLTP нагрузка относительно небольшая. Все эти цифры я привел, чтобы вы могли оценить масштаб нашего проекта и понять насколько наш опыт может быть полезен для вас.

Картина первая. Лирическая

Когда мы начинали разработку, то особо не задумывались о том, какая нагрузка ляжет на БД и что мы будем делать если сервер перестанет вытягивать. При проектировании БД мы следовали общим рекомендациям и старались не стрелять себе в ногу, но дальше общих советов вроде “не используйте паттерн Entity Attribute Values мы не заходили. Проектировали исходя из принципов нормализации избегая избыточности данных и не заботились об ускорения тех или иных запросов. Как только пришли первые пользователи мы столкнулись с проблемой производительности. Как обычно мы оказались абсолютно не готовы к этому. Первые проблемы оказались простыми. Как правило все решалось добавлением нового индекса. Но наступил момент когда простые заплатки перестали работать. Осознав, что опыта не хватает и нам все сложнее понять в чем причина проблем, мы наняли специалистов, которые помогли нам правильно настроить сервер, подключить мониторинг, показали куда смотреть, чтобы получить статистику.

Картина вторая. Статистическая

Итак у нас есть около 10 тысяч различных запросов, которые выполняются на нашей БД за сутки. Из этих 10 тысяч есть монстры, которые выполняются по 2-3 млн раз со средним временем выполнения 0.1-0.3 мс и есть запросы со средним временем выполнения 30 секунд, которые вызываются 100 раз в сутки.

Оптимизировать все 10 тысяч запросов не представлялось возможным, поэтому мы решили разобраться с тем, куда направлять усилия, чтобы повышать производительность БД правильно. После нескольких итераций мы стали делить запросы на типы.

TOP запросы

Это самые тяжелые запросы, которые занимают больше всего времени (total time). Это запросы, которые либо очень часто вызываются либо запросы, которые очень долго выполняются (долгие и частые запросы были оптимизированы еще на первых итерациях борьбы за скорость). В итоге суммарно на их исполнение сервер тратит больше всего времени. Причем важно отделять топ запросы по общему времени исполнения и отдельно по IO time. Способы оптимизации таких запросов немного разные.

Обычная практика всех компаний- работать с TOP запросами. Их немного, оптимизация даже одного запроса может освободить 5-10% ресурсов. Однако, по мере “взросления” проекта оптимизация TOP запросов становится все более нетривиальной задачей. Все простые способы уже отработаны, да и самый “тяжелый” запрос отнимает “всего” 3-5% ресурсов. Если TOP запросы в сумме занимают менее 30-40% времени, то скорее всего вы уже приложили усилия, чтобы они работали быстро и пришла пора переходить к оптимизации запросов из следующей группы.
Остается ответить на вопрос сколько верхних запросов включить в эту группу. Я обычно беру не меньше 10, но не больше 20. Стараюсь, чтобы время первого и последнего в TOP группе отличалось не более чем в 10 раз. То есть если время исполнения запросов резко падает с 1 места до 10, то беру TOP-10, если падение более плавное, то увеличиваю размер группы до 15 или 20.

Середняки (medium)

Это все запросы, которые идут сразу за TOP, за исключением последних 5-10%. Обычно в оптимизации именно этих запросов кроется возможность сильно поднять производительность сервера. Эти запросы могут “весить” до 80%. Но даже если их доля перевалила за 50%, значит пора на них взглянуть более внимательно.

Хвост (tail)

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

Как оценить каждую группу?

Я использую SQL запрос, который помогает сделать такую оценку для PostgreSQL (уверен что для многих других СУБД можно написать похожий запрос)

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

Вот так примерно соотносятся доли запросов на момент начала работ по оптимизации и сейчас.

Из диаграммы видно, что доля TOP запросов резко снизилась, зато выросли “середняки”.
Поначалу в TOP запросы попадали откровенные ляпы. Со временем детские болезни исчезли, доля TOP запросов сокращалась, приходилось прилагать все больше усилий, чтобы ускорить тяжелые запросы.

Вот список самых часто используемых приемов, которые помогали нам ускорять TOP запросы:

Redesign системы, например переработка логики уведомлений на message broker вместо периодических запросов к БД
Добавление или изменение индексов
Переписывание ORM запросов на чистый SQL
Переписывание логики lazy подгрузки данных
Кеширование через денормализацию данных. Например у нас есть связь таблиц Доставка -> Счет -> Запрос -> Заявка. То есть каждая доставка связана с заявкой через другие таблицы. Чтобы не связывать в каждом запросе все таблицы, мы продублировали ссылку на заявку в таблице Доставка.
Кэширование статических таблиц со справочниками и редко меняющихся таблиц в памяти программы.

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

Вместо проверки наличия записей с помощью COUNT и полного сканирования таблицы стали использовать EXISTS
Избавились от DISTINCT (нет общего рецепта, но иногда можно легко от него избавиться ускоряя запрос в 10-100 раз).

Например, вместо запроса для выборки всех водителей по большой таблице доставок (DELIVERY)

сделали запрос по сравнительно небольшой таблице PERSON

В итоге мы уже три года работаем на одном и том же железе. Среднесуточная нагрузка около 30%, в пиках доходит до 70%. Количество запросов как и количество пользователей выросло примерно в 10 раз. И все это благодаря постоянному мониторингу этих самых групп запросов TOP-MEDIUM. Как только какой-то новый запрос появляется в группе TOP, мы его тут же анализируем и пытаемся ускорить. Группу MEDIUM мы раз в неделю просматриваем с помощью скриптов анализа запросов. Если попадаются новые запросы, которые мы уже знаем как оптимизировать, то мы их быстро меняем. Иногда находим новые способы оптимизации, которые можно применить сразу к нескольким запросам.

Читайте также: