Эффективная архитектура баз данных. Как профилировать и оптимизировать запросы и структуру БД


Содержание

Эффективная архитектура баз данных. Как профилировать и оптимизировать запросы и структуру БД?

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

Сегодня идет много разговоров о Dig Data и других новых технологиях. NoSQL и облачные решения это супер, но много популярного софта (такого как WordPress, phpBB, Drupal) до сих пор работает на MySQL. Миграция на новейшие решения может вылиться не только в изменении конфигурации на серверах. К тому же, эффективность MySQL до сих пор на уровне, особенно версия Percona.

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

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

Для кого эта статья?

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

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

Делать бэкапы MySQL просто, используя утилиту mysqldump:

Вы можете узнать больше о mysqldump .

Что делает запрос медленным?

Вот общий список факторов, влияющих на скорость выполнения запросов и нагрузки сервера:

  • индексы таблиц;
  • условие WHERE(и использования внутренних функций MySQL, например, таких как IF или DATE);
  • сортировка по ORDER BY;
  • частое повторение одинаковых запросов;
  • тип механизма хранения данных (InnoDB, MyISAM, Memory, Blackhole);
  • не использование версии Percona;
  • конфигурации сервера ( my.cnf / my.ini );
  • большие выдачи данных (более 1000 строк);
  • нестойкое соединение;
  • распределенная или кластерная конфигурация;
  • слабое проектирование таблиц.

Далее мы обратимся ко всем этим проблемам. Также, установите Percona , если вы еще не используете эту встроенную замену стандартному MySQL — это придаст сильное увеличение мощности базы данных.

Что такое индексы?

Индексы используются в MySQL для поиска строк с указанными значениями колонок, например, с командой WHERE. Без индексов, MySQL должна, начиная с первой строки, прочитать всю таблицу в поисках релевантных значений. Чем больше таблица, тем больше затрат.

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

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

Уменьшаем частое повторение одинаковых запросов

Наиболее быстрый и эффективный способ, который я нашел для этого — это создание хранилища запросов и результатов их выполнения с помощью Memcached или Redis. С Memcache вы можете легко положить в кэш результат выполнения вашего запроса, например, следующим образом:

Теперь тяжелый запрос, использующий LEFT JOIN, будет выполняться только раз за каждые 86 400 секунд (то есть раз в сутки), что значительно уменьшит нагрузку MySQL сервера, оставив ресурсы для других соединений.

Примечание: Допишите p: в начале аргумента хоста MySQLi для создания постоянного соединения.

Распределенная или кластерная конфигурация

Когда данных становится все больше, и скорость вашего сервиса идет под уклон, паника может овладеть вами. Быстрым решением может стать распределения ресурсов (sharding). Однако я не рекомендую делать это, если вы не обладаете хорошим опытом, поскольку распределение по своей сути делает структуры данных сложнейшими.

Слабое проектирование таблиц

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

Обеспечение правильного проектирования для нужного использования является первостепенным в создании вашего приложения. Храните различные данные в различных таблицах (например, категории и статьи) и убедитесь, что отношения к другу (many to one) и один ко многим (one to many) могут быть легко связаны с идентификаторами (ID). Использование FOREIGN KEY в MySQL идеально подходит для хранения каскадных данных в таблицах.

При создании таблицы помните следующее:

  • Создавайте эффективные таблицы для решения ваших задач, а не заполняйте таблицы лишними данными и связями.
  • Не ожидайте от MySQL выполнения вашей бизнес логики или програмности — данные должны быть готовы к вставке строки вашей скриптовым языком. Например, если вам нужно отсортировать список в случайном порядке, сделайте это в массиве PHP, не используя ORDER BY из арсенала MySQL.
  • Используйте индексные типы UNIQUE для уникальных наборов данных и применяйте ON DUPLICATE KEY UPDATE, чтобы хранить дату обновленной, например, для того, чтобы знать, когда строка была в последний раз изменена.
  • Используйте тип данных INT для сохранения целых чисел. Если вы не укажете размер типа данных, MySQL сделает это за вас.

Основы оптимизации

Для эффективной оптимизации мы должны применять три подхода к вашему приложению:

  1. Анализ (логирование медленных запросов, изучение системы, анализ запросов и проектирование базы данных)
  2. Требования к исполнению (сколько пользователей)
  3. Ограничения технологий (скорость железа, неправильное использование MySQL)

Анализ может быть сделан несколькими путями. Сначала мы рассмотрим наиболее очевидные способы, чтобы заглянуть под капот вашей MySQL, в котором выполняются запросы. Самый первый инструмент оптимизации в вашем арсенале это EXPLAIN. Если добавить этот оператор перед вашим запросом по SELECT, результат запроса будет таким:

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

Колонка possible_keys покажет индексы, в которые MySQL имел доступ, чтобы выполнить запрос. Иногда нужно назначить индексы, чтобы запрос выполнялся быстрее. Колонка Extra покажет, были ли использованы дополнительные WHEREили ORDER BY. Наиболее важно обратить внимание, есть ли Using Filesort в выводе.

Что делает Using Filesort, указано в справке MySQL:

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

Лишний проход замедлит ваше приложение, этого нужно избегать, чего бы это ни стоило. Другой критический результат Extra, который мы должны избегать — это Using temporary. Он говорит о том, что MySQL пришлось создать временную таблицу для выполнения запроса. Очевидно, это ужасное использования MySQL. В таком случае результат запроса должен быть сохранен в Redis или Memcache и не выполняться пользователями лишний раз.

Чтобы избежать проблемы с Using Filesort мы должны увериться, что MySQL использует INDEX. Сейчас указано несколько ключей в possible_keys, из которых можно выбирать, но MySQL может выбрать только один индекс для финального запроса. Также индексы могут быть составлены из нескольких колонок, также вы можете ввести подсказки (хинты) для оптимизатора MySQL, указывая на индексы, что вы создали.

Оптимизатор MySQL будет использовать статистику, основанную на запросах таблиц, чтобы выбрать лучший индекс для выполнения запроса. Он действует достаточно просто, основываясь на встроенной статистической логике, поэтому имея несколько вариантов, не всегда делает правильный выбор без помощи хинтинга. Чтобы убедиться, что был использован правильный (или неправильный) ключ, воспользуйтесь ключевым словам FORCE INDEX, USE INDEX и IGNORE INDEX в вашем запросе. Вы можете прочитать больше о хинтинге индексов в справке MySQL .

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

В дополнение к EXPLAIN существует ключевое слово DESCRIBE. Вместе с DESCRIBE можно просматривать информацию из таблицы следующим образом:

Для добавления индексов в MySQL надо использовать синтаксис CREATE INDEX. Есть несколько видов индексов. FULLTEXT Применяется для полнотекстового поиска, а UNIQUE — для хранения уникальных данных.

Чтобы добавить индекс в вашу таблицу, используйте следующий синтаксис:

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

Индексы имеют большое влияние на скорость выполнения запросов. Только назначения главного уникального ключа недостаточно — композитные ключи являются реальной областью применения в настройке MySQL, что иногда требует некоторых A/B проверок с использованием EXPLAIN.

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

Как только мы создали ключ на основе колонки username, в котором хранится имя пользователя и колонки active типа ENUM, определяющий, активен ли его аккаунт. Теперь все оптимизировано для запроса, который будет использовать WHERE для поиска валидного имени пользователя с активным аккаунтом (active = 1).

Насколько быстра ваша MySQL?

Включим профилирование, чтобы подробнее рассмотреть MySQL запросы. Это можно сделать, выполнив команду set profiling=1, после чего для просмотра результата надо выполнить show profiles.

Если вы используете PDO, выполните следующий код:

То же самое можно сделать с помощью mysqli:

Это вернет вам профилированные данные, содержащие время выполнения запроса во втором элементе ассоциативного массива.

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

Полный аудит работы базы вашего сайта

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

Чтобы включить логирование в MySQL 5.1.6 используйте глобальную переменную log_slow_queries, также вы можете отметить файл для логирования с помощью переменной slow_query_log_file. Это можно сделать, выполнив следующий запрос:

Также это можно указать в файлах конфигурации /etc/my.cnf или my.ini вашего сервера.

После внесения изменений не забудьте перезагрузить MySQL сервер необходимой командой, например service mysql restart, если вы используете Linux.

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

Переменная long_query_time определяет количество секунд, после которых выполнение запроса считается медленным. Значение это 10, а минимум это 0. Также можно указать миллисекунды, используя дробь; сейчас я указал одну секунду. И теперь каждый запрос, который будет выполняться дольше 1 секунды, записывается в логи в таблице.

Логирование будет вестись в таблицах mysql.slow_log и mysql.general_log вашей MySQL базы данных. Чтобы выключить логирование, измените log_output на NONE.

Логирование на рабочем сервере

На рабочем сервере, который обслуживает клиентов, лучше применять логирование только на короткий период и для мониторинга нагрузки, чтобы не создавать лишней нагрузки. Если ваш сервис перегружен и необходимо безотлагательное вмешательство, попробуйте выделить проблему, выполнив SHOW PROCESSLIST, или обратитесь к таблице information_schema.PROCESSLIST, выполнив SELECT * FROM information_schema.PROCESSLIST;.

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

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

Логирование множества запросов

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

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

Горячий и холодный кэш

Количество запросов и нагрузка сервера имеет сильное влияние на исполнение, также может повлиять на время выполнения запросов. При разработке вы должны взять за правило, что выполнение каждого запроса должно быть не более доли миллисекунды (0.0xx или быстрее) на свободном сервере.

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

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

Исправление медленных запросов

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

Если вы пока не нашли медленного запроса, проверьте настройки long_query_time, если вы пользуетесь этим методом логирования. Иначе, проверив все ваши запросы профилирования (set profiling=1), составьте список запросов, отнимают больше времени, чем доля миллисекунд (0.000x секунд) и начнем из них.

Вот шесть самых распространенных проблем, которые я находил, оптимизируя MySQL запросы:

ORDER BY и filesort

Предотвращение filesort иногда невозможно из-за выражения ORDER BY. Для оптимизации сохраните результат в Memcache, или выполните сортировку в логике вашего приложения.

Использование ORDER BY вместе с WHERE и LEFT JOIN

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

Применение ORDER BY по временным колонками

Просто не делайте этого. Если вам нужно объединить результаты, сделайте это в логике вашего приложения; не используйте фильтрацию или сортировку во временной таблице запроса MySQL. Это требует много ресурсов.

Игнорирование индекса FULLTEXT

Использование LIKE это самый лучший способ сделать полнотекстовый поиск медленным.

Беспричинный выбор большого количества строк

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

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

Когда в одном запросе вы пользуетесь больше чем тремя-четырьмя операторами LEFT JOIN, спросите себя: все ли здесь верно? Продолжайте, если у вас есть на то веская причина, например — запрос используется не часто для вывода в панели администратора, или результат вывода может быть сохранен в кэше. Если же вам нужно выполнять запрос с большим количеством операций объединения таблиц, тогда лучше задуматься о создании композитных таблиц из необходимых столбиков или использовать представления.

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

Также мы посмотрели на несколько примеров и классических ловушек, в которые вы можете попасть, используя MySQL. Используя хинтинг индексов, мы можем увериться в том, что MySQL выберет необходимые индексы, особенно при нескольких выборках в одной таблице. Чтобы продолжить изучение темы, я советую вам посмотреть в сторону Percona project.

Архитектура базы данных: понятие, определение, уровни

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

Что это?

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

Данное определение отражает одну из важнейших функций хранилищ информации — обеспечение возможности абстракции сведений БД. Она и формирует сложившийся в наши дни подход к архитектуре данных.

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

Виды БД

Архитектура систем управления базами данных будет различной в зависимости от разновидности последних. На сегодня выделяется два вида БД:

  • централизованный;
  • распределенный.

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

Централизованные базы данных

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

Распределенные базы данных

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

Как осуществляется работа с подобной БД? С помощью системы управления распределенными базами данных (СУРБД). Ее системный справочник будет описывать информацию, содержащуюся в хранилище данных, основы ее размещения в сети. В свою очередь, сам справочник может быть декомпозирован, размещен в различных узлах общей сети.

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

Типы БД по способу доступа к ним

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

  • Доступ локальный.
  • Доступ удаленный (сетевой).

Последний тип доступа предполагает разделение архитектуры подобных систем еще на две вариации:

Снова предлагаем читателю разобраться с представленными разновидностями подробнее.

БД «файл-сервер»

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

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

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

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

БД «клиент-сервер»

Здесь также предполагается наличие машины в сети, которая будет являться главной. Однако архитектура базы данных «клиент-сервер» имеет и собственную особенность. Главный компьютер не только хранит централизованную БД, но и обеспечивает основную часть обработки требуемых пользователю данных.

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

Что предполагает архитектура клиент-серверных баз данных? Клиентское приложение здесь оформляет и отправляет запрос удаленному компьютеру-серверу, где расположено централизованное хранилище информации. Он (запрос) составлен на специальном языке SQL — стандарте доступа к серверу при использовании реляционных БД.

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

Таким образом, вся обработка запросов здесь будет проходить на удаленном сервере. Чтобы реализовать подобную архитектуру, необходимо задействовать многоуровневые СУБД. Второе их название — промышленные. Такие СУБД способны организовать масштабную инфосистему, состоящую из большого числа пользователей.

Три уровня архитектуры БД

Архитектура баз данных подразделяется на три основных уровня — три степени описания элементов БД:

  • Внешний. На данном уровне информация воспринимается пользователями.
  • Внутренний. На этом уровне информация воспринимается операционными системами, СУБД (системами управления базами данных).
  • Концептуальный. Здесь осуществляется отображение внешнего уровня архитектуры системы баз данных на внутренний, обеспечение необходимой их независимости друг от друга.

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

Внешний уровень

Внешний уровень архитектуры систем баз данных — это предоставление информации с позиции людей-пользователей.

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

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

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

Если обратиться к терминологии ANSI/SPARC (Американского национального института стандартов), то представление каждого отдельного пользователя здесь будет называться внешним. В него будет входить содержимое БД — такое, каким его видит конкретный «юзер». Каждое такое внешнее представление определяется посредством внешней системы. Она же состоит из определения записи каждого типа, присутствующего во внешнем представлении.

Концептуальный уровень

Продолжаем разбирать архитектуру сервера, баз данных. Следующий ее уровень — концептуальный. Он включает в себя обобщающее представление о хранилище информации. Будет описывать, какие именно сведения хранятся в базе данных, а также каковы связи, их объединяющие.

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

Элементы концептуального уровня

Перечислим компоненты, представленных на концептуальном уровне архитектуры:

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

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

Внутренний уровень

И последняя ступень трехуровневой архитектуры базы данных. Тут находится физическое представление в компьютере БД. Что это значит? Уровень предназначен для описания физической реализации базы данных. Кроме того, с его помощью достигается оптимальная производительность, обеспечивается экономное использование дискового пространства компьютерной системы.

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

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

Элементы внутреннего уровня

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

  • О распределении дискового пространства для сохранения индексов и сведений.
  • Подробное описание сохранения записи (где указываются реальные объемы сохраняемых данных).
  • Информация о размещении записей.
  • Сведения о сжатии данных, избранных методик их шифрования.

Вы познакомились с распространенными типами, видами архитектур систем баз данных. Также мы представили уровни архитектуры СУБД — внешний, внутренний и концептуальный, их характеристики и элементы.

Эффективная архитектура баз данных. Как профилировать и оптимизировать запросы и структуру БД?

Матиас Ярке, Юрген Кох

Оригинал: Matthias Jarke, Jurgen Koch. Query Optimization in Database Systems. Computing Surveys, Vol. 16, No. 2, June 1984

Оригинал можно посмотреть здесь

Предисловие переводчика

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

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

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

Содержание

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

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

ВВЕДЕНИЕ

Системы управления базами данных (СУБД) стали стандартным инструментом экранирования пользователя компьютера от деталей управления вторичной памятью. СУБД разрабатываются для повышения производительности труда прикладных программистов и облегчения доступа к данным неискушенных в комьютерах конечных пользователей.

Имеются две основные области исследований систем баз данных. Одна из них — это анализ моделей данных, на которые может отображаться реальный мир и на основе которых могут строиться интерейсы для различных типов пользователей. Такие концептуальные модели включают иерерхическую, сетевую, реляционную модели, а также ряд моделей, ориентированных на семантику, которые обозревались в большом числе книг и обзоров [Brodie et al. 1984].

Вторая область интересов затрагивает безопасную и эффективную реализацию СУБД. Комьютеризоаванные данные становятся центральным ресурсом большинства организаций. Это должно учитываться в каждой реализации, предназначенной для производственного использования, путем гаранирования безопасности данных в случаях параллельного доступа [Bernstein and Goodman 1981c], восстановления [Verhofstad 1978] и реорганизации [Sockut and Goldberg 1979]. Одно из основных критических замечаний ко многим ранним СУБД относилось к отсутствию эффективности при обработке предлагаемых ими мощных операций, в особенности, доступа к данным на основе их содержимого через запросы. Оптимизация запросов предназначена для решения этой проблемы путем интеграции большого числа методов и стратегий, простирающихся от логических преобразований запросов до оптимизации путей доступа и хранения данных на уровне файловых систем.

Традиционно в каждом из этих подходов использовался отдельный язык. Вероятно, это является одной из причин, по которым до сих пор не представлен исчерпывающий обзор методов оптимизации запросов. Целью этой статьи является представление методов оптимизации запросов в общем каркасе реляционного исчисления. Показано, что реляционной исчисление технически эквивалентно представлению реляционной алгебры [Codd 1972; Klug 1982a] и поддается расширениям для реализации сетевых СУБД [Dayal and Goodman 1982]. Более того, многие популярные языки запросов, такие как SQL [Astrahan and Chamberlin 1975] и QUEL [Stonebraker et al. 1976], легко отображаются в реляционное исчисление.

Для экономии места в этой статье мы прежде всего сосредотачиваемся на проблеме оптимизации запросов в централизованной СУБД. Оптимизация централизованных запросов важна не только во многих базах данных на мейнфреймах — а в последнее время и в микропроцессорных СУБД — но также является подпроблемой оптимизации запросов в распределенных системах. Сама оптимизация распределенных запросов [Bayer et al. 1984; Sacco and Yao 1982; Ullman 1982] затрагивается только кратко, а следующих двух близких областей мы вообще не касаемся:

Пользовательская оптимизация. Общая стоимость информационной системы составляется из стоимости СУБД и стоимости усилий пользователей для работы с системой. Граница между этими двумя областями состоит из функциональных возможностей и удобства использования языка запросов [Vassiliou and Jarke 1984], и наиболее важной характеристикой является время отклика системы. Если предположить, язык запросов обладает заданными функциональными возможностями, а минимизация времени отклика является целью системы выполнения запросов, то оптимизация запросов может считаться отдельно трактуемой подпроблемой пользовательской оптимизации.

Структуры файлов. Алгоритм оптимизации запросов должен производить выбор между множеством путей доступа для выполнения запроса. Внутренние детали реализации таких путей доступа и вывод соответствующих оценочных форму (см., например, Teorey and Fry [1982]) находятся за пределами этой статьи.

Статья состоит из шести разделов, расположенных в соответствии с подходом «сверху-вниз». В разд. 1 мы представляем глобальный каркас для оптимизации запросов. В разд. 2 мы сравниваем четыре метода представления запросов с точки зрения их пригодности для оптимизации. В разд. 3 мы используем один из этих методов, реляционное исчисление, для представления преобразований, основанных на логике, включая развивающиеся методы семантической оптимизации.

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

Цукерберг рекомендует:  Меню, которое привязано к курсору мыши

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

1. ПРОБЛЕМА ОПТИМИЗАЦИИ ЗАПРОСОВ

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

1.1 Запросы

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

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

Второе применение запросов происходит в транзакциях, которые изменяют хранимые данные на основе их текущих значений (например, «повысить зарплату всем доцентам на 10%»). Наконец, выражения, подобные запросам, могут использоваться внутри СУБД, например, для проверки прав доступа [Griffiths and Wade 1976], поддержки ограничений целостности [Stonebraker 1975] и корректной синхронизации параллельного доступа [Reimer 1983].

1.2 Цели оптимизации

Чтобы допустить справедливое сравнение эффективности, функциональные возможности сравниваемых систем выполнения запросов должны быть сходными. Требование «реляционной полноты», придуманное Коддом [Codd 1972], (сравните с разд. 2.1) стало квазистандартом. Методы, обозреваемые в данной статье, представляются как вклады в реализацию запросов на реляционно полном языке с минимальной стоимостью выполнения или временем отклика. Запросы более высокого уровня сложности [Chandra and Harel 1982a] рассматриваются в разд. 6.1. Общая стоимость, подлежащая минимизации, складывается из следующих компонентов:

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

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

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

Стоимость вычислений: Стоимость (или время) использования ЦП.

На структуру алгоритмов оптимизации запросов влияют соотношения между этими компонентами стоимости. В территориально распределенной СУБД с относительно медленными коммуникационными каналами преобладает стоимость коммуникацонных задержек, а другие факторы существенны только для локальной подоптимизации. В централизованных системах доминирует время доступа к вторичной памяти, хотя для сложных запросов достаточно высокой может быть и стоимость ЦП [Gotlieb 1975]. В локально распределенных СУБД все факторы имеют близкие веса, что приводит к очень сложным оценочным функциям и процедурам оптимизации.

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

Остаются стоимость доступа ко вторичной памяти (обычно измеряемая числом обращения к страницам) и стоимость использования ЦП (часто измеряемая числом сравнений, которые требуется произвести). В основе большинства методов, разработанных для сокращения этой стоимости, лежит ряд общих идей: (1) избегать дублирования усилий; (2) использовать стандартизованные компоненты; (3) заглядывать вперед, чтобы избегать лишних операций; (4) выбирать наиболее дешевые способы выполнения элементарных операций; (5) выстраивать их последовательность оптимальным образом. Следующий простой пример показывает, что можно ожидать от оптимизации запросов.

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

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

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

Имеется 100 отделов, 5 из которых размещаются в Нью-Йорке. В физическом блоке может поместиться 5 записей об отделах или 50 значений dname.

Имеется 500 курсов, 20 из которых посвящены управлению базами данных. В физическом блоке помещается 10 записей.

Имеется 2000 лекций, три сотни из них — про управление базами данных, 100 проходят в отделах в Нью-Йорке и 20 (из трех отделов) удовлетворяют обоим условиям. В физический блок помещаются 10 записей.

Предположим также, что время сортировки составляет N * log(2)N, где N — размер файла в блоках, и что имеется буфер из одного блока для каждого отношения. Наконец, все отношения физически упорядочены по возрастанию значений ключа.

Первая представленная здесь стратегия следует прямому подходу трансляции выражения реляционного исчисления в операции алгебры [Codd 1972]. Вместе с каждым шагом стратегии приводится число обращений ко вторичной памяти, требующихся для чтения (r) и записи (w).

    Сформировать декартово произведение отношений «courses», «lectures» и «departments»

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

    Выполнить слияние отношений «courses» и «lectures»

Итого: Приблизительно 6000 обращений.

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

    Выполнить слияние отношений «courses» и «lectures»

Итого: 277 обращений

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

1.3 Нисходящий подход к оптимизации запросов

Потребность в работающих системах инициировала разработку полномасштабных процедур вычисления запросов, что повлияло на общность решений и заставило заниматься оптимизацией запросов в единообразной и эвристической манере [Astrahan and Chamberlin 1975; Makinouchi et al. 1981; Niebuhr et al. 1976; Palermo 1972; Schenk and Pinkert 1977; Wong and Youssefi 1976]. Поскольку часто это не позволяло достичь конкурентноспособной эффектвности систем, современной тенденцией представляется нисходящий подход, который обеспечивает возможность включения в общие процедуры большего знания о возможностях оптимизации в частных случаях. В то же время, сами общие алгоритмы усиливаются комбинаторными процедурами минимизации стоимости для выбора между стратегиями.

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

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

Шаг 2. Применить логические преобразования к представлению запроса, которые (1) стандартизируют запрос, (2) упрощают запрос, чтобы избежать дублирования усилий, (3) улучшают запрос для упрощения его выполнения и создания возможности применения процедур частных случаев.

Шаг 3. Отобразить преобразованный запросв в возможную последовательность элементарных операций, для которых известна хорошая реализация и соответсвующие оценки стоимости. В результате этого шага появляется набор возможных «планов доступа».

Шаг 4. Вычислить общую стоимость каждого плана доступа, выбрать наиболее дешевый план и выполнить его.

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

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

2. ПРЕДСТАВЛЕНИЕ ЗАПРОСОВ

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

2.1 Реляционное исчисление

Выражение выборки специфицирует содержимое отношения, происходящего в результате выполнения запроса, средствами предикатов первого порядка (т.е. обобщенных булевских выражений, возможно, содержащих кванторы существования и всеобщности). В целевом списке определяются свободные переменные, встречающиеся в предикате, и специфицируется структура результирующего отношения. В Примере 2.1 демонстрируется представление реляционного исчисления с использованием синтаксиса языка программирования баз данных Pascal/R [Schmidt 1977].

Пример 2.1. Имена профессоров, опубликовавших какую-либо статью в 1981 г.

В целевом списке, т.е. подвыражении, предшествующем двоеточию, область определения (свободной) переменной e ограничивается элементами отношения «employees». Поэтому отношение «employees» называется отношением области определения (range relation) переменной e. Спецификация атрибута цели » » показывает, что в результате запроса останутся только имена служащих.

Выражение выборки — предикат, следующий за двоеточием — определяет ограничения на свободную переменную. Первое ограничение является ограничивающим, или одноместным термом (monadic term), ограничивающим свободную переменную теми записями «employees», значением столбца status которых является значение «professor». Это ограничение связывается через AND с соединительным, или двуместным термом (dyadic term), связывающим «employees» с «papers», и еще одним одноместным термом, еще более ограничивающим результат теми служащими, которые опубликовали какую-либо статью в 1981 г. Обычно допускаемыми в термах операциями сравнения являются =, `, , d и e.

В отличие от односортового исчисления предикатов, в реляционном исчислении разрешаются переменные, привязанные к разным сортам (отношениям области определения); например, переменная e связана с «employees», а переменная p — с «papers». Последствия многосортовости реляционного исчисления в отношение преобразования запрсов обсуждаются в разд. 3.1.

Кроме логической операции AND, в предикатах могут использоваться и операции OR и NOT. Предикаты реляционного исчисления полностью определяются следующими рекурсивными правилами:

  1. Атомарные предикаты:
    1. (Одноместный или двуместный) терм является атомарным предикатом.
    2. TRUE является атомарным предикатом.
    3. FALSE является атомарным предикатом.
  • Атомарный предикат является предикатом. Пусть A — предикат, r — переменная элемента, и rel — отношение. Тогда
    1. SOME r IN rel(A),
    2. ALL r IN rel(A)
      также являются предикатами.
  • Пусть A и B — предикаты. Тогда
    1. NOT (A) (отрицание),
    2. A AND B (конъюнкция),
    3. A OR B (дизъюнкция)
      являются предикатами.
  • Никакие другие формулы предикатами не являются.
  • Реляционное исчисление было введено в [Codd 1972] как мерило реляционной мощности. Форма представления называется реляционно полной, если в ней допускается определение результата любого запроса, определяемого выражением реляционного исчисления. Ясно, что реляционная полнота должна рассматриваться как минимальное требование в отношении выразительной мощности. Часто приводимым примером концептуально простого запроса, выходящим за пределы реляционной полноты, является запрос «найти имена служащих, отчитывающихся перед менеджером Смитом на любом уровне», предусматривающий, что в одном отношении моделируется иерархия служащий (через атрибуты name и manager) [Pirotte 1979]. Кроме того, запросы в сегодняшних приложениях часто содержат агрегации, которые неаозможно выразить в чистом реляционном исчислении. Однако реляционное исчисление довольно легко расширяется агрегатными функциями [Klug 1982b; Maier and Warren 1981].

    2.2 Реляционная алгебра

    Операция ограничения, примененная к отношению «rel», конструирует горизонтальное подмножество в соответствии с безкванторным предикатом, содержащим только одноместные термы или «внутриотношенческие» двуместные термы (сравнения между атрибутами одного элемента отошения):

    Операция проекции служит для конструирования вертикального подмножества отношения «rel» путем выбора набора указанных атрибутов A и удаления кортежей-дубликатов из этих атрибутов:

    Операция соединения позволяет соединять два отношения «rel1» и «rel2» в одно отношение, атрибуты которого являются объединением атрибутов «rel1» и «rel2«:

    Допускаемые в соединениях операции сравнения «op» являются такими же, как в двуместных термах реляционного исчисления. Если «op» является операцией сравнения на равенство, в результате «естественного» соединения опускается A или B.

    Операция деления является алгебраическим двойником квантора всеобщности. Она определяется следующим образом:

    где compl(A) — это дополнение A во множестве атрибутов «rel1«. Как показывает определение, деление является довольно сложной операцией, что может затруднить понимание запросов.

    В Примере 2.2 представляется запрос Примера 2.1 в терминах реляционной алгебры.

    Пример 2.2. Имена профессоров, опубликовавших какую-либо статью в 1981 г.

    В противоположность выражению реляционного исчисления, которое описывает отношение, проиходящее из запроса, в терминах его свойств, выражение релционной алебры определяет алгоритм конструирования результирующего отношения. Выражение исчисления кажется лучшей стартовой точкой для оптимизации запросов, поскольку оно обеспечивает оптимизатор только базовыми свойствами запроса; возможности оптимизации могут быть скрыты в конкретной последовательности операций алгебры. Однако в отношении реляционной полноты реляционная алгебра, по меньшей мере, настолько же полна, как и реляционное исчисление. В [Codd 1972] показано, что любое выражение реляционного исчисления можно отрранслировать в эквивалентное выражение алгебры. Аналогичный результат для выражений алгебры и исчисления, расширенных агрегатыми функциями доказан Клугом [Klug 1982a].

    2.3 Графы запросов

    В объектных графах узлы представляют объекы, такие как переменные (отношений) и константы. Дуги описывают предикаты, которым эти объекты должны удовлетворять [Bernstein and Chiu 1981; Palermo 1972; Youssefi and Wong 1979]. Объектные графы содержат свойства результатов запросов и поэтому тесно связаны с реляционным исчислением. Графы операций описывают управляемые операциями потоки данных путем представления операций как узлов, связанных дугами, указывающими направление движения данных. В [Smith and Chang [1975]; Yao [1979] графы операций использовались для представления выражений алгебры. На рис. 1 и рис. 2 приведены примеры объектного графа и графа операций соответственно.

    Рис. 1. Объектный граф, представляющий запрос из примера

    Рис. 2. Граф операций, представляющий запрос из примера

    У графов запросов имеется много привлекательных свойств. Визуальное представление запроса способствует более простому пониманию его структурных характеристик. Кроме того, в теории графов имеется много результатов, полезных для автоматического анализа графов, например, обнаружение циклов и свойства древовидности. Наконец, важным достоинством графов запросов является то, что их легко расширять дополнительной информацией. Например, расширение графов деталями физической организации данных предложено в [Rosenthal and Reiner 1982].

    2.4 Табло

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

    На рис. 3 иллюстрируется конструирование табло, представляющего запрос из Примера 2.1. Процесс начинается с табло для одиночных отношений и продолжается путем комбинирования этих табло в новые табло для все больших выражений. Выделенные переменные отмечаются символами a; невыделенные — символом b.

    Рис. 3. Пошаговое конструирование табло T, представляющего запрос из Примера 2.1

    Выражения, содержащие дизъюнкцию (объединение множеств) и отрицание (вычитание множеств) могут представляться наборами табло [Sagiv and Yannakakis 1980]. В [Klug 1983] и Johnson and Klug 1983] набора табло используются для представления общих конъюгктивных запросов. Конкретная значимость табло в отношении оптимизации запросов обсуждается в разд. 3.2.

    3. ПРЕОБРАЗОВАНИЕ ЗАПРОСОВ

    3.1 Стандартизация

    Говорят, что представление запроса с использованием реляционного исчисления находится в предваренной нормальной форме (prenex normal form), если его выражение выборки имеет вид

    где M — предикат, не содержащий кванторов. M называется матрицей (matrix) и тоже может быть стандартизован. Говорят, что матрица, состоящая из дизъюнкции конъюнкций (термов Aij), такая как

    находится в дизъюнктивной нормальной форме, а матрица, состоящая из конъюнкции дизъюнктов, такая как

    находится в конъюнктивной нормальной форме

    Предваренная нормальная форма с нормальными формами матрицы приводит к двум нормальным формам выражений реляционного исчисления: дизъюнктивной предваренной нормальной форме (disjunctive prenex normal form, DPNF) и конъюнктивной предваренной нормальной форме (conjunctive prenex normal form, СPNF). Использование DPNF мотивируется целью раздельной оптимизации и выполнения независимых компонентов запросов [Bernstein et al. 1981]. CPNF оказалась полезной для декомпозиции запросов [Wong and Youssefi 1976] и для зависящего от данных улучшения запроса (например, проверка в первую очередь наиболее ограничительного дизъюнта).

    Запросы в CPNF могут подвергаться дальнейшему преобразованию к не содержащей кванторов форме, популярной в приложениях доказательства теорем искусственного интеллекта, к так называемой клаузальной форме (clausal form) [Nilsson 1982]. Языки баз данных, основанные на логике, такие как Prolog [Kowalski 1981], основываются на клаузальной форме. Поскольку клаузальная форма редко используется при оптимизации запросов (в качестве исключений см. Grant and Minker [1981], Jarke et al. [1984] и Warren [1981]) здесь мы опускаем подробности.

    Преобразование произвольного выражения реляционного исчисления к предваренной нормальной форме состоит в перемещении кванторов по термам (справа налево). Перемещение кванторов управляется правилами преобразований, представленными в таб. 1. Нормализация матрицы может достигаться с применением правил ДеМоргана, правил дистрибутивности и правила двойного отрицания (см. таб. 2).

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

    Таб. 2. Правила преобразований для общих выражений

    Различия для случаев пустых и непустых отношений областей определения в правилах Q2 и Q3 таб. 1 возникают из-за изменчивости отношений во времени и многосортности реляционного исчисления [Jarke and Schmidt 1982]. Выражение реляционного исчисления может быть преобразовано к односортному исчислению путем введения области определения, такой как (r IN rel), как еще одного типа атомарного предиката:

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

    3.2 Упрощение

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

    посредством правил M4d и M4g, сначала нужно распознать эквивалентность подвыражений

    Алгоритмы приводятся в [Downey et al. 1980 и Hall 1974, 1976]. Распознавание общих подвыражений и применение правил идемпотентности должно выполняться скорее совместно, чем последовательно, поскольку при упрощении выражения на основе правил идемпотентности могут появиться новые общие подвыражения, которые, в свою очередь, являются предметом упрощения. Выражения, связанные с пустыми отношениями, также можно упрощать. Правила преобразований для их упрощения приводятся в таб. 3. (Заметим, что эти правила можно применять только во время выполнения.)

    Таб. 3. Правила преобразований для выражений с пустыми отношениями

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

    позволяющее сократить в запросе число двуместных термов. В алгоритмах минимизации числа строк в табло (см. разд. 2.4) систематически используются такие правила упрощения для конъюнктивных запросов [Aho et al. 1979a; Sagiv 1981, 1983]. Поскольку число строк в табло больше числа соединений (двуместных соединительных термов) в выражении, минимизация числа строк соответствует исключению избыточных соединений.

    Сагив и Яннакакис [Sagiv and Yannakakis 1980] расширяют методы табло для обеспечения возможности упрощения выражений, содержащих дизъюнкты. Однако обобщение методов для всех выражений реляционно полного языка все еще остается открытой проблемой.

    Для «семантического» упрощения запросов может использоваться информация о семантических ограничениях целостности [Aho et al. 1979a, 1979b, 1979c; Jarke et al. 1984; Johnson and Klug 1982; Ott and Horlaender 1982; Rosenthal and Reiner 1984]. В качестве простого примера рассмотрим случай ограничений ключа. Если r и r’ — это (свободные или связанные квантором существования) переменные с одной и той же область определения, отношением «rel», то эквисоединение вида «r.key = r’.key» является избыточным в том смысле, что этот терм и одна из переменных, например, r’ могут быть удалены с подстановкой r вместо r’ в любом терме, в который входит r’. Этот тип упрощения особенно пригоден в контексте обработки представлений, когда преобразование пользовательских запросов над представлениями к системным запросам над хранимыми отношениями может привнести существенную избыточность. Область применения семантического упрощения может быть расширена за счет привлечения дополнительных ограничений, порождаемых структурой запроса [Klug 1980; Koch et al. 1981].

    Заключительная возможность упрощения возникает в тех случаях, когда удается показать, что один или несколько конъюнктов матрицы стандартизованного запроса никогда не удовлетворяются [Eswaran et al. 1976; Klug 1983; Munz et al. 1979; Ozsoyoglu and Yu 1980]. Для примера рассмотрим выражение

    которое порождает противоречие r.a > r.a и поэтому может быть заменено булевским значением false. Проблема определения невыполнимости условия терма эффективно решается во время компиляции для конъюнкции термов с операциями сравнения (=, , &ge) [Rosenkrantz and Hunt 1980], но вычислительно сложна, если допускается операция сравнения на неравенство.

    3.3 Улучшение

    Простейшими преобазованиями, рассматриваемыми в этом разделе, являются объединение последовательности проекций в одну проекцию и объединение последовательности ограничений в одно ограничение [Hall 1976; Smith and Chang 1975]. Соответствующими правилами преобразования являются следующие:

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

    Целью ряда улучшающих преобразований является минимизация размера конструируемых, сохраняемых и считываемых промежуточных результатов. Важная эвристика перемещает селективные операции, такие как ограничение и проекция, выше контруктивных операций, таких как соединение и декартово произведение, чтобы выполнять селективные операции как можно раньше [Smith and Chang 1975]. В контексте реляционного исчисления разбор некоторой последовательности вычислений может быть представлен вложенным выражением. Вычисление вложенного выражения начинается с вычисления наиболее внутреннего вложенного выражения, за которым следует непосредственно окружающее его выражение и т.д., пока не будет достигнуто наиболее внешнее выражение. Вложенное выражение, подразумевающее раннее вычисление одноместных термов (ограничений) приведено в Примере 3.1.

    Пример 3.1. Вложенное выражение, эквивалентное выражению из Примера 2.1.

    Ранее вычисление селективных операций представляет частный случай отсоединения запроса (query detachment), введенного Вонгом и Юссефи [Wong and Youssefi 1976]. Подвыражение, которое перекрывается с оставшейся частью запроса по одной переменной, отсоединяется и образует внутреннюю вложенность. Отсоединение выполняется рекурсивно на каждом уровне вложенности до тех пор, пока выражение не перестанет сокращаться. Эксперименты, описанные Юссефи и Вонгом [Wong and Youssefi 1979], показали, что это очень сильная эвристика. В Примере 3.2 демонстрируется отсоединение подвыражения сложного выражения.

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

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

    Объектный граф, представляющий запрос, показан на рис. 4.

    Заметим, что результирующее вложенное выражение является неразделимым [Goodman and Shmueli 1980]; т.е. его нельзя разделить на два подвыражения, перекрывающихся по одной переменной. Другими словами, вложенное выражение содержит цикл (см. рис. 4).

    Рис 4. Объектный граф для Примера 3.2

    Важность различения циклических и ациклических (древовидных) выражения для обработки запросов подробнее обсуждается в разд. 4.3. Пока мы лишь заметим, что бывают циклы, которые можно преобразовать к эквивалентным ациклическим графам. В число таких циклов входят те, которые (1) вводятся по транзитивности [Bernstein and Chiu 1981; Yu and Ozsoyoglu 1979], (2) содержат некоторые комбинации дуг, соотвествующих соединительным термам с условием сравнения на неравенство [Bernstein and Goodman 1981b; Ozsoyoglu and Yu 1980], (3) являются «замкнутыми» посредством переменных, связанных квантором всеобщности [Jarke and Koch 1983] и (4) содержат переменные, которые можно подвергнуть декомпозиции с использованием функциональных зависимостей [Kambayashi and Yoshikawa 1983].

    Понятия расширенных выражений областей определения (extended range expressions) [Jarke and Schmidt 1982] и вложенных областей определения (range nesting) [Jarke and Koch 1983] обеспечивают обобщение отсоединения запросов, поскольку в них учитываются выражения с кванторами всеобщности. Отношения базы данных, задающие область определения кортежной переменной замещаются выражениями исчисления в соответствии со следующими правилами преобразований:

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

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

    Ограничения целостности — это предикаты, которые должны быть истинными для каждого элемента некоторого отношения или каждой комбинации элементов некоторой группы отношений. Поэтому их можно добавлять к выражению выборки любого запроса без изменения его истинностного значения. Имеется несколько подходов, в которых это наблюдение используется скорее для улучшения, а не для упрощения (последняя возможность упоминалась в предыдущем разделе). Они называются основанной на знаниях (knowledge-based) [Hammer and Zdonik 1980] или семантической обработкой запросов [King 1979, 1981].

    Предположим, например, что ограничение целостости говорит: «Мы принимаем на работу только профессоров, которые публикуют, по крайней мере, одну статью в год». В этом случае вычисление запроса из Примера 2.1 (в котором спрашиваются имена профессоров со статьями в 1981 г.) становится тривиальным, а вычисление запроса из Примера 3.2 существенно упрощается.

    Добавление ограничения целостности к выражению выборки может также изменить структуру запроса, делая его более приспособленным для обработки. Нассмотрим ограничение: «Мы принимаем на работу только местных профессоров». В этом случае терм «d.city = e.city» в примере 3.2 можно опустить. В объектном графе оставшегося запроса больше не содержится цикл.

    Успех семантической обработки запросов сильно зависит от разработки эффективных эвристик для выбора между многими преобразованиями, делающими возможным добавление к запросу любой комбинации ограничений целостности. В [King 1981] и [Xu 1983] для принятия этого решения для специального класса реляционных баз данных используются правила в духе искусственного интеллекта.

    Яо [Yao 1979] указывает, что существуют случаи, в которых оптимальное преобразование является зависимым от данных. Представленные выше эвристики могут быть не всегда оптимальными, особенно в тех случаях, когда некоторые пути доступа поддерживаются структурами физического хранения. Одним из последствий такой зависимости от данных является то, что средства преобразования запросов должны поддерживаться не только во время компиляции, но и во время выполнения. Кроме того, если эвристики не приносят удовлетворительных результатов, требуется одновременная оптимизация на физическом и логическом уровнях. Однако, прежде чем обратиться к таким интегрированным подходам, необходимо описать физическое выполнение компонентов запросов.

    Оптимизация баз данных под высокую доступность

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

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

    Зачем оптимизировать базу данных

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

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

    Существует множество типов баз данных, каждая из которых уникальна. Их объединяет то, что они увеличивают сложность платформы. Давайте разберемся, как это происходит.

    Операционные знания

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

    Для большинства платформ инженеры — не проблема. Достаточно нескольких экспертов по базам данных, так как их можно распределить на разные платформы. Однако в масштабе это более сложная задача.

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

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

    Производительность

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

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

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

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

    Модернизация может требовать усилий

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

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

    Репликация данных и отказоустойчивость

    Устойчивость данных — самый сложный аспект работы с высокодоступной платформой. Это верно, говорим ли мы о базах данных или о любой другой системе хранения данных.

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

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

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

    Сокращение сложности за счет сокращения использования базы данных

    Важно помнить, что чем сложнее платформа, тем сложнее поддерживать доступность.

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

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

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

    Типичная реализация с высокой доступностью

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

    Типичная реализация REST API, такой как эта, будет простым приложением с бэкендом базы данных. Не принимая во внимание потребность в высокой доступности для этого приложения, одного экземпляра приложения и одного экземпляра базы данных будет более чем достаточно (рисунок 1).

    Рисунок 1

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

    Рисунок 2

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

    Что произойдет, если эта репликация не удалась и необходим переход на вторичный сайт? Будет ли эта служба затем возвращать неверные результаты?

    Перемещение БД вниз по стеку

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

    Рисунок 3

    На рисунке 3 показан другой подход к многосайтовому развертыванию. В этой реализации есть два основных компонента: приложение и API данных.

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

    Цукерберг рекомендует:  Автоматическая генерация содержания для статьи

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

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

    Устранить репликацию

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

    Рисунок 4

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

    Простая стратегия для приложений — отделение каждого экземпляра приложения друг от друга.

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

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

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

    Нет базы данных

    Этот метод похож на архитектуру базы данных без разделения ресурсов, за исключением того, что она на шаг впереди. Если данные пересчета валют собираются независимо каждым экземпляром приложения, почему бы просто не хранить эти данные в памяти, а не в базе данных? Это позволило бы полностью исключить необходимость в базе данных (рисунок 5).

    Рисунок 5

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

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

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

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

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

    Вывод

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

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

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

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

    Не используйте базы данных, если они не требуются.

    Оптимизация SQL-запросов (Часть 1)

    В данной статье рассматриваются особенности запросов на выборку данных, виды индексов, использование планов запросов, различные подходы к оптимизации запросов. Во второй части статьи «Оптимизация SQL-запросов (Часть 2)» рассматриваются рекомендации по разработке оптимальной структуры БД.

    Введение

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

    1. Демонстрационная база данных

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

    База наполнена тестовыми данными:

    • Status — 3 записи
    • Customer — 3 записи
    • Product — 10 записей
    • Order — 3 млн. записей.
    • OrderStatus — 9 млн. записей.
    • OrderProduct — 2,5 млн. записей

    2. Запросы на выборку данных

    Запросы на выборку данных реализуются с помощью конструкции SELECT языка SQL.
    Запрос в MS SQL имеет следующий синтаксис:

    2.1. Типы соединений таблиц

    Если запрос адресован к нескольким таблицам БД, то выполняется соединение этих таблиц по определенным полям.
    Существует несколько типов соединений:
    Внутреннее (INNER JOIN)

    Внешнее левое/правое (LEFT/RIGHT JOIN)

    Перекрестное (CROSS JOIN)
    (Используется редко)

    2.2. Вложенные запросы

    Вложенный запрос представляет собой запрос типа select from select
    Пример:

    2.3. Короткие и соотнесенные запросы

    Пример короткого селекта:

    Пример соотнесенного запроса:

    В MS SQL короткие и соотнесенные запросы могут быть реализованы с помощью конструкции OUTER/CROSS APPLY.

    Существует два типа оператора APPLY: CROSS APPLY и OUTER APPLY. Оператор CROSS APPLY возвращает только строки из внешней таблицы, которые создают результирующий набор из возвращающей табличное значение функции. Оператор OUTER APPLY возвращает и строки, которые формируют результирующий набор, и строки, которые этого не делают, со значениями NULL в столбцах, созданных возвращающей табличное значение функцией.

    3. Индексы

    3.1. Виды индексов MS-SQL

    В MS SQL Индексы могут быть двух типов: кластерные и не кластерные.
    Если упрощенно, то кластерный индекс отличается от не кластерного тем, что в листьях этого индекса содержатся не ссылки на записи в таблице, а сами записи. Таким образом, для таблицы допустим только один кластерный индекс.
    Скорость доступа к строкам при указании условия where, совпадающего с условием индексирования для кластерного индекса, будет выше, чем при поиске записей по простому индексу.
    Если в кластерный индекс включены часто обновляемые поля, то это отрицательно скажется на производительности, т. к. обновления будут приводить к логическому переупорядочиванию данных.
    Если из таблицы, имеющей кластерный индекс, часто удаляются записи, то это приводит к сильной фрагментации таблицы.
    MS SQL по умолчанию создает кластерные индексы по первичному ключу.

    3.2. Ограничения MS SQL при создании индексов

    При создании индекса максимальная суммарная длина полей допускается равной 900 байт. Причем индекс может быть создан по полям, типы которых допускают большую длину полей, но при этом хранящиеся данные обязательно должны быть меньше.
    Т.е., например, в таблице имеется 2 поля varchar(500) , MSSQL допускает создание индекса при условии, что реальная длина записей по этим полям не превышает 900. При наличии такого индекса в таблице, при попытке вставки (или обновления) данных, если сумма реальных полей будет превышать 500 байт, то будет выдано сообщение об ошибке.
    Для хранения каждого символа в типах nvarchar и nchar MS SQL использует 2 байта.

    4. Планы запросов

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

    4.1. Анализ плана запроса

    Ядро SQL Server Database Engine может показывать, каким образом оно переходит к таблицам и использует индексы для доступа к данным. Это называется выводом плана выполнения. Для проведения анализа медленно выполняемого запроса полезно изучить план выполнения запроса, чтобы определить причину проблемы.
    Планы выполнения запросов в MS SQL отображаются cредой SQL Server Management Studio. При этом отображается наилучший план выполнения, используемый ядром Database Engine для отдельных инструкций языка DML и Transact-SQL. В этом плане содержатся сведения о процессе компиляции хранимых процедур и о вызовах хранимых процедур произвольной глубины вложенности. Например, при выполнении инструкции SELECT можно увидеть, что Database Engine выполняет просмотр таблицы для получения данных. Выполнение инструкции SELECT может также показать, что просмотр индекса будет использоваться, если Database Engine определит, что просмотр индекса является наиболее быстрым способом получения данных из таблицы.
    Таким образом, выбор оптимального плана во многом зависит от наличия в базе индексов и их оптимальности.

    При выполнении запроса в MSSQL есть возможность поиска “отсутствующих” индексов. Т. е. MS SQL подсказывает, какие индексы можно создать, чтобы данный конкретный запрос выполнялся быстрее.
    Пример:

    Запрос выдает рекомендации на создание индекса:

    4.2. Способы применения индекса в планах запроса

    Основные типы получения записей индекса это Seek и Scan.
    Index Scan получает все записи индекса, указанного в столбце Argument. Если необязательный предикат WHERE:() появится в столбце Argument, то возвращаются только те строки, которые удовлетворяют условию, указанному в этом предикате.
    Оператор Index Seek использует возможности поиска по индексам для получения строк из индекса. С самого начала поиск обрабатывает только те строки, которые квалифицированы, и страницы, которые содержат эти квалифицированные строки, поэтому стоимость пропорциональна числу квалифицированных строк и содержащих их страниц, а не к общему числу строк в таблице. Таким образом, поиск является более эффективной стратегией, если мы имеем дело с очень селективным поисковым предикатом; то есть если мы имеем поисковый предикат, который отсекает большую часть таблицы.

    4.3. Способы изменения плана

    В MS SQL при выполнении запросов можно указать специальные «подсказки» (hint), с помощью которых можно повлиять на план запроса.
    Оптимизатор запросов SQL Server обычно автоматически выбирает наилучший план выполнения запроса. Поэтому подсказки, в том числе , рекомендуется использовать только опытным пользователям и администраторам базы данных в случае крайней необходимости.
    Примечание. В некоторых СУБД, например Firebird, можно косвенно влиять на план запроса, например добавлением «+0» к полю таблицы. В этом случае оптимизатор перестает использовать индекс.

    4.4. Кэширование и повторное использование плана

    В SQL Server есть пул памяти, предназначенный для хранения планов выполнения и буферов данных. Процентное соотношение размера пула, выделенного для планов выполнения и буферов данных, динамически изменяется в зависимости от состояния системы. Часть пула памяти, используемого для хранения планов выполнения, называется кэшем процедур.
    В SQL Server планы выполнения состоят из следующих основных компонентов.
    План запроса. Тело плана выполнения является структурой данных только для чтения, которая предназначена для использования любым числом пользователей. Оно называется планом запроса. План запроса не содержит контекста пользователя.
    Контекст выполнения. Для каждого пользователя, который в настоящий момент выполняет запрос, имеется структура данных, которая содержит данные, относящиеся к данному выполнению, например значения параметров. Эта структура данных называется контекстом выполнения. Структуры данных контекста выполнения являются повторно используемыми. Если пользователь выполняет запрос и одна из структур не используется, она повторно инициализируется контекстом нового пользователя.

    4.5. Проблема параметров при кэшировании запросов

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

    Для выполнения этих запросов используются разные планы, т. к. при построении плана, MS SQL анализирует количество возвращаемых данных.
    Если данный запрос сделать параметризованным:

    то план запроса будет определятся самым первым запросом.
    Это демонстрирует следующий скрипт:

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

    5. Подходы к оптимизации запросов

    5.1. Поиск неоптимальных запросов

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

    5.2. Статистика индексов

    Для индекса существует понятие плотности распределения. Если каждое значение в таблице уникальное, то плотность будет 1/ . Например, если в таблице 100 записей, то плотность будет равна 1%. Теория говорит, что чем меньше плотность, тем лучше – это увеличивает избирательность, а, следовательно, и ценность построенного индекса.
    Например, если колонка содержит только 3 значения, плотность распределения будет равна 33.3%, что показывает бесполезность построения индекса по данному полю. Индексы занимают место на диске и в оперативной памяти и отнимают быстродействие. В идеале, самый лучший индекс имеет плотность распределения равную единице, деленной на количество записей в таблице — все записи уникальны.
    При построении индексов, обращайте внимание на плотность распределения – если она превышает 10%, то индекс можно считать бесполезным. Сканирование по таблице в таком случае будет более эффективным.

    Для просмотра статистики индекса используется команда:

    5.3. Фильтрация в индексах

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

    Создадим индекс по полю Quantity:

    Создадим фильтрованный индекс:

    Кроме того, фильтрованный индекс создается гораздо быстрее.

    5.4. Индексы с включенными полями

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

    Создаем индекс на поле CustomerId:

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

    Создаем индекс с включенными полями:

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

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

    Команда CREATE INDEX имеет дополнительные параметры. Рассмотрим некоторые из них, которые в дальнейшем могут повлиять на производительность запросов.
    ASC|DESC
    ASC значит что индекс будет построен по возрастанию ключей. DESC соответственно – по убыванию. Эта опция не дает никакой разницы на поиск данных, но оказывает существенное влияние на скорость выполнения ORDER BY опции в запросах.
    Пример.

    SORT_IN_TEMPDB
    Полезна при создании индексов. Эта опция может увеличить производительность системы во время создания индексов. Индексы создаются в два этапа. На первом создается временный набор данных с отсортированными ключами для некластерного индекса. На втором – окончательный результат переносится на место его хранения в базе данных. Без указания данной опции временный результат создается в той же file group, где и будет создан индекс. При указанной опции временный результат будет находиться в базе данных Tempdb. Преимущества: индекс будет менее фрагментирован, временную базу можно поместить на другой диск и тем самым увеличить скорость чтения/записи. Недостаток: требуется больше памяти на дисках и надо уделять дополнительное внимание базе данных Tempdb.

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

    В создании композитного (сложного) индекса участвуют несколько полей таблицы. При создании индекса следует обращать внимание на порядок следования полей в индексе.
    Например, если создается индекс по полям Field1, Field2, то он может быть применен только в запросе, где в критериях используются оба этих поля. Также этот индекс будет полезен для условий, построенных для одного Field1. Для одного Field2 этот индекс не может быть применен.
    Если в дополнение к индексу по полям Field1, Field2 добавить индекс по полям Field2, Field1, то SQL Server при построении плана запроса будет анализировать, какой из них более селективен в применении к ограничениям на условия запроса. Последний момент в построении композитного индекса по полям Field1, Field2 – он не равен сумме индексов по указанным полям. В случае, когда в запросе могут быть использованы оба поля как критерий поиска при раздельных индексах по полям, будет построено пересечение по индексам, что медленнее чем выборка из композитного индекса.
    Примечание. В некоторых СУБД, например Firebird, применение нескольких индексов в запросе для одной таблицы ведет к существенному снижению производительности.
    Пример. Отберем заказы определенного клиента, начиная с указанной даты.

    План показывает сканирование по первичному ключу. MS SQL рекомендует создание композитного индекса по коду поставщика и дате.

    Создадим индекс:

    Тот же самый запрос стал выполняться быстрее.

    Уберем из условия отбор по дате:

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

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

    5.7. Общие рекомендации по проектированию индексов

    Большое количество индексов в таблице снижает производительность инструкций INSERT, UPDATE, DELETE и MERGE, потому что при изменении данных в таблице все индексы должны быть изменены соответствующим образом.
    Большое число индексов может повысить производительность запросов, которые не изменяют данных, таких как инструкции SELECT, поскольку у оптимизатора запросов будет больший выбор индексов при определении самого быстрого способа доступа.
    Индексирование маленьких таблиц может оказаться не лучшим выбором, так как поиск данных в индексе может потребовать у оптимизатора запросов больше времени, чем простой просмотр таблицы. Следовательно, для маленьких таблиц индексы могут вообще не использоваться, но тем не менее их необходимо поддерживать при изменении данных в таблице.

    5.8. Выбор типа соединения таблиц

    Выбор типа соединения таблиц в запросе (INNER/LEFT/RIGHT) зависит от задачи. Практика показывает, что если в запросе, который подразумевает однозначное соединение одной таблицы с другой, нет большой разницы, использовать INNER или LEFT JOIN. Оптимизатор генерирует для таких запросов одинаковый план.
    Пример:

    5.9. Рекомендации по оптимизации запросов от Microsoft

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

    • Запросы с предикатами, которые содержат операторы сравнения столбцов одной и той же таблицы.
    • Запросы с предикатами, использующими операторы, и выполнение одного из следующих условий:
      • отсутствует статистика для столбца, указанного с любой стороны от оператора;
      • распределение значений в статистике неоднородно, а запрос выполняет поиск весьма ограниченного набора значений. Эта ситуация особенно вероятна, если оператор не является оператором равенства (=);
      • предикат использует оператор неравенства (!=) или логический оператор NOT.
    • Запросы с любыми встроенными функциями SQL Server или пользовательскими скалярными функциями, которым в качестве аргументов передаются выражения, отличные от констант.
    • Запросы, в которые включены столбцы, соединяемые по арифметическим операторам или объединением строк.
    • Запросы, которые сравнивают переменные, значения которых в момент компиляции и оптимизации запроса неизвестны.

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

    Можно повысить производительность, добавив вычисляемый столбец Col3 к таблице MyTable, который подсчитывает разницу между Col1 и Col2 (Col1 минус Col2). Затем переписать запрос:

    Возможно, производительность еще больше повысится, если построить индекс для столбца MyTable.Col3.

    Во второй части статьи «Оптимизация SQL-запросов (Часть 2)» рассматриваются рекомендации по разработке оптимальной структуры БД.

    Оптимизация производительности в СУБД Microsoft SQL server

    технические науки

    • Умрихин Виктор Павлович , кандидат наук, доцент, декан
    • Королькова Любовь Алексеевна , доцент
    • Бушманова Валентина Никифоровна , старший преподаватель
    • Сибирский государственный университет водного транспорта
    • Похожие материалы

      Введение

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

      Как правило, молодые специалисты достаточно хорошо знают стандарты языка SQL и теорию реляционных баз данных. Они умеют проектировать хорошо нормализованные базы данных и строить достаточно сложные многотабличные запросы, возвращающие правильные результаты. К сожалению, на практике эти запросы работают непозволительно долго для системы on-line на больших таблицах (десятки и сотни тысяч строк) и/или в условиях работы большого числа (десятков и сотен) пользователей, выполняющих однотипные операции.

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

      «Самый важный момент, о котором необходимо помнить при настройке производительности, состоит в том, что вам никогда не удастся познать всю подноготную данного вопроса. Если вы являетесь среднестатистическим разработчиком SQL Server, тогда хорошо, если вам известно хотя бы 20% всей информации. К счастью, к этой теме, несомненно, применим принцип “20/80” (знание 20% теории позволяет решить 80% практических вопросов)» [2].

      Данная работа может быть интересна преподавателям курсов изучения многопользовательских SQL-ориентированных СУБД, а также администраторам и разработчикам баз данных (особенно на этапе сопровождения продукта).

      Задача оптимизации производительности в MS SQL Server

      Теоретически разработчик принимается за настройку производительности SQL-кода:

      1. При разработке функционала БД, который априорно определен как «жизненно важный» и при этом достаточно сложный (потенциально «тяжелым» для СУБД). К сожалению, после внедрения не так уж редко выясняется, что:
        • «жизненно важный» код на самом деле не такой уж «жизненно важный», выполняется не так часто, как предполагалось, и пользователи вполне готовы подождать результата лишнюю секунду/минуту. Тем временем другие пользователи при выполнении совершенно других операций постоянно жалуются, что «все работает медленно или не работает вообще».
        • запросы, выполняющиеся за миллисекунды на таблицах с десятками и сотнями записей, работают недопустимо долго (десятки минут), когда число записей достигают миллиона-другого.
      2. Нагрузочное тестирование показало недостаточную производительность с точки зрения некоторых заранее выбранных количественных показателей. Однако, принимая решения на этом этапе, следует учитывать, что:
        • возможно, выбранные показатели на самом деле не являются адекватными показателями производительности системы;
        • скорость выполнения многотабличного запроса зависит не столько от абсолютного размера используемых в нем таблиц, сколько от отношения их размеров, а также от конкретных данных, прогнозировать и генерировать которые на этапе тестирования порой слишком сложно, слишком долго, слишком дорого или вообще невозможно.
      3. Система уже внедрена и работает, с точки зрения конечного пользователя, недостаточно быстро.

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

      Собственно настройку SQL можно условно разделить на следующие этапы:

      1. Идентификация и анализ запросов, которые являются причинами недостаточно высокой производительности;
      2. Составление оптимального плана выполнения для запросов, определенных на предыдущем этапе;
      3. Внесение изменений в запрос или структуру базы данных так, чтобы получить оптимальный план выполнения.

      Мониторинг производительности в MS SQL Server

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

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

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

      Мы рассмотрим некоторые из них.

      Трассировка выполнения процессов

      Инструмент SQL Server Profiler по праву называют «спасательным средством» администратора и специалиста по сопровождению. Невозможно оценить исключительную важность этого инструментального средства при решении проблем быстродействия и не только. Именно SQL Server Profiler позволяет составить представление, что же «на самом деле» происходит на сервере. Однако настраивать трассировку следует так, чтобы она возвращала только необходимый минимум сведений. Нужно помнить о том, что SQL Server Profiler также необходимо пространство для аудита системы, и поэтому у системы могут появиться дополнительные накладные расходы, независимо от того, откуда был запущен SQL Server Profiler. Чем больше будет объем трассировочных сведений, тем больше увеличится нагрузка на систему.

      В результате работы с SQL Server Profiler может быть составлен «черный список» запросов, выполняющихся недопустимо долго. Поскольку следует учитывать, что один и тот же параметризованный запрос может запускаться с разными параметрами, гораздо проще анализировать трассировки, представляющие собой вызовы хранимых процедур (и это нужно иметь в виду при разработке архитектуры БД!). Трассировку удобно сохранить в виде таблицы, которую затем можно обработать с помощью агрегатных запросов, с целью поставить в соответствие каждой процедуре количественное значение некоторого критерия, выбранного для сравнения процедур с точки зрения даваемой ими нагрузки на сервер. Такими критериями могут быть:

      1. Максимальное зафиксированное время выполнения процедуры (самый «неудачный» с точки зрения производительности запуск).
      2. Суммарное время выполнения всех вызовов процедуры.
      3. Среднее время выполнения процедуры (суммарное время выполнения всех вызовов процедуры, отнесенное к числу вызовов).

      Оперативный мониторинг процессов, занимающих ресурсы сервера

      Нагрузка на сервер, как правило, является непостоянной в течение дня. Если с помощью SQL Server Profiler удалось обнаружить, что процедуры выполняются существенно дольше «среднестатистического» времени, или (что вероятнее) пользователи жалуются, что «два часа назад все работало нормально, а сейчас очень медленно», возникает необходимость оперативно выяснить, какие процессы занимают ресурсы сервера именно сейчас, а также какие именно ресурсы являются конфликтными.

      • Процессорное время (в этом случае нагрузка сервера максимальна, остальные процедуры выполняются, но медленнее обычного, так как на их «долю» ресурсов остается недостаточно).
      • Таблица/страница/строка, заблокированная в рамках транзакции (в этом случае процессы, обратившиеся конфликтным ресурсам, стоят в очереди и ожидают освобождения; загрузка сервера при этом может быть обычной или даже меньше обычного).

      SQL Server имеет встроенное средство для мониторинга процессов. На рис. 1 показан монитор активности от SQL Server 2005 [5].

      Рисунок 1. Мониторинг процессов.

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

      В этом случае, как правило, используются самостоятельно разработанные запросы к системным таблицам master.dbo.sysprocess и master.dbo.syslocks, названия которых говорят сами за себя. Ниже (рис.2) приведен пример запроса, используемого одним из авторов при сопровождении БД именно в таких целях.

      Наконец, можно узнать последнюю SQL-команду, которую отдал серверу интересующий нас процесс по его уникальному номеру spid с помощью специальной процедуры DBCC INPUTBUFFER (Spid).

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

      Оптимизация SQL-кода

      Так или иначе, разработчик получает список хранимых процедур, нуждающихся в оптимизации, и примеры их вызовов, выполняющиеся недостаточно быстро. Также он знает (или хотя бы предполагает, что знает) причину «медленной» работы:

      • Ожидание ресурса, заблокированного в рамках транзакции, запущенной другим процессом;
      • Неоптимальный план выполнения хранимой процедуры.

      Работа по устранению каждой из этих причин имеет свою специфику.

      Минимизация блокировок в системе

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

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

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

      1. Может ли объект блокировки (как правило, это таблица) быть конфликтным ресурсом? В каждой базе данных есть таблицы, по которым блокировки не должны возникать никогда.
      2. Какая часть таблицы (таблица/страница/строка) является конфликтным ресурсом? За очень редким исключением справедливо утверждение: «право на существование» имеет только блокировка по значению первичного ключа.
      3. Какие две параллельно выполняющиеся процедуры привели к возникновению блокировки? Имеют ли они общий конфликтный ресурс исходя из их функционального назначения?
      4. Если блокировка подлежит устранению – кто «виноват» в ее возникновении: процесс, неправильно заблокировавший ресурс, или процесс, неправильно к ресурсу обратившийся?

      При написании запросов на изменение и удаление строк в таблице (операторы UPDATE и DELETE) в рамках транзакции следует помнить: эти операции должны выполняться только по первичному ключу! В противном случае сервер заблокирует таблицу целиком.

      При написании запросов на выборку следует учитывать назначение полей таблицы, к которым выполняется обращение, а также назначение самого запроса. Возможно, что логика работы приложения допускает построение этого запроса с опциями, разрешающими чтение неподтвержденных данных (NOLOCK) или только незаблокированных строк (READPAST). Например, директор торговой сети периодически формирует и просматривает отчет о выполнении менеджерами планов по валовой прибыли за текущий месяц. При этом менеджеры активно совершают сделки, на сервере постоянно открыто несколько транзакций, проводящих или удаляющих соответствующие документы. Директору вовсе не нужно дожидаться, пока ему отдадут все ресурсы в монопольный доступ. Незавершенные транзакции можно просто не учитывать – они не могут серьезно изменить картину в целом.

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

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

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

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

      Выбор индекса

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

      Разработчики склонны к крайностям при работе с индексами: либо не использовать их вовсе, либо создавать «на все случаи жизни».

      Индексы должны создаваться для столбцов, которые будут часто использоваться в параметре WHERE либо, в меньшей степени, в параметре ORDER BY.

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

      Таким образом, если база данных организована так, что большая часть времени посвящена выполнению операторов SELECT, а изменения вносятся сравнительно редко, то чем больше индексов, тем лучше. В противном случае нужно ограничиться индексами только для наиболее часто используемых столбцов. Большую помощь в их определении может оказать мастер настройки индексов – специальный инструмент MS SQL Server, предназначенный для исследования рабочей нагрузки и поиска оптимального варианта внесения изменений в индексы.

      Стратегическая денормализация

      Иногда следование стандартным и, казалось бы, универсальным правилам, может навредить. На вопрос, в чем мы выигрываем, нормализуя базу данных, 90% вчерашних студентов бойко отвечают: «Обеспечивается целостность, устраняется избыточность и повышается быстродействие». При этом следующий вопрос о том, в чем же мы при этом проигрываем, приводит большинство в тупик. Далеко не все отдают себе отчет, что в хорошо нормализованной базе данных производительность повышается только при выполнении простых операций изменения данных, тогда как производительность запросов на выборку (а также в некоторых сложных случаях использования операторов DELETE, UPDATE и INSERT. SELECT) в лучшем случае не хуже, чем в слабо нормализованной базе данных. Часто добавление в таблицу всего лишь одного столбца может устранить необходимость в сложных объединениях, либо даже позволяет отказаться от объединений, включающих несколько таблиц, за счет чего время выполнения запроса уменьшается с нескольких минут до нескольких секунд.

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

      Правильная организация хранимых процедур

      Реализация множественных решений

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

      В такой ситуации можно поступить следующим образом: добавить в код несколько выражений IF . . . ELSE для проверки условий. Это уже может дать заметный эффект. Однако есть еще одна тонкость. Запуская такую процедуру с некоторым фиксированным набором параметров и исключая из недостижимых при данном вызове блоков IF . . . ELSE запросы на выборку, можно обнаружить, что время выполнения увеличивается иногда на порядок! Казалось бы, в чем причина? Ведь исключенные запросы все равно не запускались! К сожалению, SQL Server при составлении плана выполнения хранимой процедуры «не знает», что наши условия взаимоисключающие. Он исходит из худшего случая – что все операторы SELECT (а также INSERT, UPDATE и DELETE) будут выполняться последовательно. Алгоритмические конструкции (IF … ELSE, WHILE, GOTO) игнорируются. Поэтому с точки зрения быстродействия желательно, чтобы внутри блоков IF . . . ELSE помещались не запросы, а вызовы хранимых процедур, в каждой из которых содержится запрос для работы с каждым определенным набором параметров (вызов вложенной хранимой процедуры также игнорируется при составлении плана).

      Данная проблема возникает во множестве написанных сценариев. Разработчики – тоже люди, и, как правило, они привыкли иметь дело с объектно-ориентированными языками и развитыми технологиями повторного использования кода. Можно представить, как они отнесутся (и относятся!) к идее написать кучу практически идентичных запросов, чтобы в зависимости от заданных параметров учесть все возможные нюансы применения. Однако этот случай, когда результат, достигаемый таким «возмутительным» образом, говорит сам за себя, и его невозможно достичь никаким другим способом. «По этому поводу я хочу сказать, что в любой работе случаются рутинные моменты, а иначе все давно уже стали бы разработчиками программного обеспечения. Иногда вам стоит, скрипнув зубами, все же проделать эту работу ради получения конечного результата» [2].

      Минимальное использование курсоров и циклов

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

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

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

      На практике нужно помнить – операторы INSERT, UPDATE и DELETE – циклы сами по себе, и именно для выполнения таких и только таких циклов разрабатывался SQL Server. Один запрос на UPDATE (даже многотабличный, с вложенными JOIN), модифицирующий сразу 1000 строк выполняется в разы быстрее 1000 запросов, модифицирующих по одной строке.

      Для выполнения рекурсивных запросов совсем отказаться от циклов невозможно. В этом случае рекомендуется использовать CTE (common table expression – общие табличные приложения), доступные, начиная с SQL Server 2005.

      Заключение

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

      Список литературы

      1. Артемов Д.В. Microsoft SQL Server 2000. Новейшие технологии. – М.: Издательско-торговый дом «Русская редакция», 2001. – 576 с., ил.
      2. Вьейра Р. SQL Server 2000. Программирование. В 2 ч./ Р. Вьейра: Часть II; Пер. с англ.; Под ред. С.М. Молявко. – М.: БИНОМ. Лаборатория знаний, 2004. 807 c., ил.
      3. Мамаев Е., Шкарина Л. Microsoft SQL Server 2000 для профессионалов. – СПб: Питер, 2001. – 1088 с.
      4. Тоу Д. Настройка SQL. Для профессионалов. – СПб.: Питер, 2004. – 333 с., ил.
      5. Электронный ресурс: https://habrahabr.ru/post/136481

      Электронное периодическое издание зарегистрировано в Федеральной службе по надзору в сфере связи, информационных технологий и массовых коммуникаций (Роскомнадзор), свидетельство о регистрации СМИ — ЭЛ № ФС77-41429 от 23.07.2010 г.

      Соучредители СМИ: Долганов А.А., Майоров Е.В.

      IT-блог о веб-технологиях, серверах, протоколах, базах данных, СУБД, SQL, компьютерных сетях, языках программирования и создание сайтов.

      Архитектура СУБД. Архитектура баз данных. Логическая структура СУБД. Описание данных в базе данных. Базы данных схема данных

      Здравствуйте, уважаемые посетители моего скромного блога для начинающих вебразработчиков и web мастеров ZametkiNaPolyah.ru. Продолжаем рубрику Заметки о MySQL, в которой уже были публикации: Нормальные формы и транзитивная зависимость, избыточность данных в базе данных, типы и виды баз данных, настройка MySQL сервера и файл my.ini, MySQL сервер, установка и настройка. Сегодня мы поговорим о логической структуре СУБД и архитектуре баз данных. Как всегда, я постараюсь описать архитектуру СУБД на простом и понятном языке, без всяких сложных и умных слов.

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

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

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

      Трехуровневая архитектура баз данных. Три уровня абстракции описания данных.

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

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

      Для наглядности можете посмотреть на рисунок, на нем продемонстрирована структура трехуровневой СУБД:

      Структура базы данных

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

      Базы данных. Схема данных. Независимость уровней от данных.

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

      Каждая схема данных имеет свое собственное название и зависит от уровня. На самом высоком или внешнем уровне имеется несколько внешних схем данных или подсхем. На концептуальном уровне описание базы данных происходит при помощи концептуальных схем. Внутренний уровень СУБД описывается при помощи внутренней схемы данных.

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

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

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

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

      Внешний уровень – это пользовательский уровень, по-другому – пользовательский интерфейс. Внешняя схема данных описывает то, как видят различные группы пользователей архитектуру базы данных. Под пользователем стоит понимать: конечного пользователя базы данных, проектировщика базы данных, администратора базы данных и т.д. Внешнее представление базы данных – это то, как видит пользователь структуру базы данных.

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

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

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

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

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

      1. Таблицы и их атрибуты
      2. Связи между таблицами
      3. Ограничения, накладываемые на данные
      4. Семантику данных
      5. В концептуальной схеме данных должны быть учтены аспекты безопасности и целостности данных

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

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

      Внутренняя схема данных – это полное описание физической реализации базы данных. При помощи внутренней схемы данных осуществляется настройка СУБД (настройка MySQL сервера). С ее помощью можно достичь оптимальной производительности СУБД и обеспечить экономное использование места на носители информации.

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

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

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

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

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

      Оптимизация структуры БД, индексы, и скорость работы

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

      Первая проблема: фильтры

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

      Вот пример запроса:

      Этот запрос генерирует плагин SEO-фильтры (есть настройка для кеширования), и скорее всего shopProductsCollection. Такой-же запрос летит на COUNT(DISTINCT product.id) и сама выборка товаров.

      Первое, что было сделано — все таблицы сконвертировали на InnoDB и сделали апдейт ПО. Результат улучшился, стало чуть меньше секунды.

      Проблема в том, что в таблице нет индекса по feature_id, feature_value_id. Лечится путем создания индекса:

      Как результат: было 12 секунд, стало 0.2 секунды.

      Вторая проблема: не кеширует данные

      Вот список того, что кеширует фреймворк:

      1. валюты
      2. категории (даже не отрендеренное дерево, а просто массив)
      3. блоки
      4. списки товаров

      Из всего этого, хорошая идея кешировать категории, но лучше это делать сразу в отрендеренном виде, и вставлять в шаблон как строку. Туда же нужно писать все тяжелые запросы, а это выборки min/max, всякие COUNT(*) и другое. Возможно, стоит подумать над тем, чтобы кеш использовался более активно?

      Третья проблема: shop_category_products

      Товары в категориях хранятся не очень эффективно. Намного лучше было бы пойти «в тупую», и сделать category_ >

      Я не поленился, написал рекурсивный CTE, создал таблицу, где category_ >

      Вот что я получил:

        Теперь запросы строятся через WHERE category_ >На моем примере, в исходной таблице, как сейчас хранит Shop-Script — 28000 записей. В модифицированной таблице — 46635. Даже если в ней будет в 10 раз больше записей, она все равно будет работать быстрее.

      Предыдущий запрос на выборку, включая добавленный индекс, занимает 0.027 sec (0.531 sec без индекса), при этом новая структура позволяет делать выборки за 0.003 sec. Такая структура ускорит не один запрос, а минимум три — на выбор мин-макс цены, на выбор товаров, на подсчет количество товаров в категории. На скорости загрузки страницы, и на нагрузке, это скажется очень позитивно.

      Проблема четвертая: MyISAM

      Я понимаю, что это тяжело, но я думаю уже пора переезжать на InnoDB. Там и FK CONSTRAINT, и кластеризированные индексы, и транзакции, и многое-многое другое. Да и вообще, сам движок развивается намного активней, чем MyISAM, который оставлен с целью совместимости. Не знаю, чтобы кто-то в 2020 году использовал MyISAM.

      Результат

      Вот примерный результат до создания индекса, используя старую структуру таблиц, и новый результат: после создания индекса, и с новой структурой. Учтите, что на MyISAM было не 0.5 секунд, а 12, т.е. это. в 3000 раз быстрее! Надеюсь, хотя бы индексы после этого поста добавят в таблице.

      Oleg Zorin

      Инструменты пользователя

      Инструменты сайта

      Боковая панель

      Олег Зорин

      Содержание

      Построение структуры баз данных

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

      Простая структура

      С простой структурой базы данных встречался каждый. Суть очень проста — есть некая логика которая подключается к базе и осуществляет чтение и запись данных. В подавляющем большинстве логика и сама БД крутятся на одном сервере.

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

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

      Давайте еще раз повторим. Преимущества:

      Репликация базы данных

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

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

      Алгоритм работы простой и разбивается на две группы — запись и чтение.

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

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

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

      И снова кратко плюсы-минусы репликации БД. Достоинства:

      Шардирование баз дынных

      Шардирование — это некий принцип распределения баз данных (shard — осколок).

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

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

      Трудно написано, но легко понимаемо на примере. Пусть у нас есть некая огромная база со следующей структурой:

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

      Теперь сгруппируем данные по какому-либо признаку. К примеру по странам. Так в группе «Россия» будут находиться все записи для которых верно `country` = ‘Россия’. При этом в этой группе не будет записей относящихся, к примеру, к Китаю. Это и есть наш шард. А процесс группирования называется — шардирование.

      В идеале каждый шард можно положить на свой сервер БД. Остается только научить логику определять к какому шарду должен будет пойти запрос и отправить его на соответствующий сервер.

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

      В заключении, достоинства:

      Более подробно о шардировании на практике можно прочесть в статье Шардирование баз данных.

      Курсовая работа: Проектирование базы данных 4

      Федеральное Агентство Железнодорожного Транспорта

      Проектирование базы данных.

      студент гр. Ит-314

        Инфологическое проектирование 5

      1.1. Описание предметной области 5

      1.2. Описание информационных потребностей пользователей 5

      1.3. Построение инфологической модели 6

        Даталогическое проектирование 7

      2.1. Выбор и характеристика СУБД 7

      2.2. Построение даталогической модели 9

      2.3. Создание базы данных 11

      2.4. Заполнение БД 12

      2.5. Запросы к БД 14

      Список использованной литературы 18

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

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

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

      При проектировании базы данных решаются две основных проблемы:

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

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

      Название: Проектирование базы данных 4
      Раздел: Рефераты по информатике
      Тип: курсовая работа Добавлен 19:16:42 11 июля 2011 Похожие работы
      Просмотров: 2532 Комментариев: 13 Оценило: 3 человек Средний балл: 4 Оценка: неизвестно Скачать
      Проектирование внешних и концептуальной инфологических моделей
      Предварительное планирование

      Этапы проектирования базы данных.

      Рис.1 Этапы проектирования БД

      1.1 Описание предметной области

      Предметная область определяется с помощью четырех основных составляющих:

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

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

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

      1.2. Описание информационных потребностей пользователей

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

      Основными понятиями ER-модели являются сущность, связь и атрибут:

      Сущность – это реальный или представляемый объект, информация о котором должна сохраняться и быть доступна. В диаграммах ER-модели сущность представляется в виде прямоугольника, содержащего имя сущности. При этом имя сущности — это имя типа, а не некоторого конкретного экземпляра этого типа.

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

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

      Связь представляется в виде линии. При этом над местом «стыковки» связи с сущностью ставится знак «∞» или буква «M», если для этой сущности в связи могут использоваться много (many) экземпляров сущности, и цифра «1», если в связи может участвовать только один экземпляр сущности.

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

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

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

      Связь – ассоциирование двух или более сущностей. Ниже приведена диаграмма ER-типов, на которой определены связи между сущностями.

      Построение инфологической модели

      Инфологическая модель для базы данных «Результаты игр футбольной команды» проектировалась, как модель «Сущность-связь».

      Сущность – это класс однотипных объектов. Процесс деятельности фирмы идентифицирует такие сущности: Команда, Тренер, Члены команды, Матчи, Чемпионат.

      Каждая из сущностей имеет свой набор атрибутов.

      Рисунок 1. Диаграмма ER – типов.

      Команда, Тренер, Члены команды, Матчи, Чемпионат.

      2. Даталогическое проектирование.

      2.1. Выбор и характеристика СУБД

      Система управления базой данных (СУБД) представляет собой набор программных средств, посредством которого осуществляется управление базой данных и доступ к данным.

      К числу основных функций СУБД принято относить следующие:

      1. Непосредственное управление данными во внешней памяти.

      Эта функция заключается в обеспечении необходимых структур внешней памяти, как для хранения непосредственных данных, входящих в БД, так и для служебных целей. СУБД поддерживает собственную систему именования объектов БД.

      2. Управление буферами оперативной памяти.

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

      3. Управление транзакциями.

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

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

      5. Поддержка языков БД.

      Для работы с БД используются специальные языки баз данных. Чаще всего выделяются 2 языка – язык определения данных (DDL) и язык манипулирования данными (DML). DDL служит, главным образом, для определения логической структуры БД, а DML, содержит набор операторов манипулирования данными. Во многих СУБД обычно поддерживается единый интегрированный язык, содержащий все необходимые средства для работы с БД. Стандартным языком реляционных СУБД является язык SQL. Язык SQL сочетает средства DDL и DML, т.е. позволяет определять схему реляционной БД и манипулировать данными.

      В SQL используются следующие основные типы данных, форматы которых могут несколько различаться для разных СУБД:

      INTEGER — целое число (обычно до 10 значащих цифр и знак);

      SMALLINT — «короткое целое» (обычно до 5 значащих цифр и знак);

      DECIMAL ( p , q ) — десятичное число, имеющее p цифр (0

      FLOAT — вещественное число с 15 значащими цифрами и целочисленным порядком, определяемым типом СУБД;

      CHAR ( n ) — символьная строка фиксированной длины из n символов (0 0 и разное в разных СУБД, но не меньше 4096);

      DATE — дата в формате, определяемом специальной командой (по умолчанию mm/dd/yy); поля даты могут содержать только реальные даты, начинающиеся за несколько тысячелетий до н.э. и ограниченные пятым-десятым тысячелетием н.э.;

      DOUBLE PRECISION — для научных вычислений 15 цифр точности.

      NUMERIC ( p . s ) — численные значения содержат цифры от 0 до 9 и необязательные знак и десятичную точку.

      Поэтому при проектировании БД выбор остановился на СУБД InterBase 6.0, как СУБД поддерживающей все основные выше перечисленные функции. Помимо этого InterBase 6.0 имеет следующие характеристики:

      1. Повышенная производительность за счет развитой архитектуры

      Сервер InterBase реализует архитектуру множественных поколений записей (MGA — Multi-Generational Architecture). MGA обеспечивает уникальные возможности использования версий, что ведет к высокой степени доступности данных как для пользователей, работающих с транзакциями, так и для пользователей, использующих приложения поддержки принятия решений. Механизм MGA в InterBase хорошо работает при оперативной обработке коротких транзакций (OLTP — On-Line Transaction Processing) и является уникальным для крупномасштабных реальных приложений, превосходя другие базы данных в области параллельного исполнения длительных транзакций для поддержки принятия решений. Механизм версий устраняет необходимость блокировки записей, к которым осуществляется доступ по чтению во время транзакции, делая их свободными от конфликтов доступа – доступ по чтению никогда не блокирует доступ по записи. В отличие от других баз данных, InterBase обеспечивает своевременные, устойчиво воспроизводимые результаты для каждого запроса без специального программирования. В результате достигается максимальная пропускная способность для всех пользовательских транзакций.

      2. Многопотоковая архитектура

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

      3. Мощная поддержка различных типов данных

      Многим приложениям (мультимедиа, научные, интернет – приложения), требуется возможность обработки неструктурированных данных. InterBase является первой реляционной базой данных, удовлетворившей это требование с помощью BLOB. Использование BLOB позволяет сохранять в базе данных аудио-, видео-, графическую и бинарную информацию. В современных приложениях фильтры BLOB используются для сжатия и трансформации данных. Разработка приложений и улучшенная производительность для научных приложений поддерживаются многомерными типами данных InterBase, обеспечивающими хранение до 16 измерений в одном поле базы данных.

      4. Сигнализаторы событий

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

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

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

      6. Строгое соблюдение индустриальных стандартов

      InterBase придерживается строгого соответствия индустриальным стандартам для клиент-серверных вычислительных сред, таким как ANSI/SQL, Java, UNICODE и XDR (External Data Representation – внешнее представление данных). Наша приверженность критически важным технологическим стандартам означает, что вы можете сократить время, необходимое для разработки, внедрения и сопровождения ваших приложений на множестве платформ с гарантией немедленного достижения наивысшей производительности.

      2.2. Построение даталогической модели

      На этом этапе необходимо установить соответствие между сущностями и характеристиками предметной области и отношениями и атрибутами в InterBase 6.0. Для этого нужно каждой сущности и характеристикам поставить в соответствие набор отношений (таблиц) и их атрибутов (полей).

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

      Таблица соответствий названий сущностей.

      Сущность Соответствие
      Команда Team
      Члены команды Ludi
      Матчи Matchi
      Тренер Trener
      Чемпионат Chemp
      Работает Work1
      Позиция Pozitziya

      Таблица соответствий названий полей.

      Атрибуты Соответствие
      Фамилия Famil
      Имя Imya
      Отчество Otchestvo
      Телефон Tel
      Команда 1 Komanda_1
      Команда 2 Komanda_2
      Очки 1 ochki_1
      Очки 2 ochki_2
      Время Vremya
      Вид чемпионата Vid_chemp
      Год оснавания God_osn
      Город Gorod
      Страна Strana
      Тренеровочные базы Basi
      Адрес Adres
      Название Nazvanie
      Дата начала Data_nachala
      Дата_конца Data_konza

      Рисунок 2. Даталогическая модель.

      2.3. Создание базы данных .

      CREATE TABLE «CHEMP» ( «KOD_CHEMP» INTEGER NOT NULL, «VID_CHEMP» VARCHAR(20), «VREMYA» DATE, PRIMARY KEY («KOD_CHEMP»));

      CREATE TABLE «LUDI» («KOD_CHEL» INTEGER NOT NULL, «FAMIL» VARCHAR(20), «IMYA» VARCHAR(20), «OTCHESTVO» VARCHAR(20), «TEL» VARCHAR(20), «KOD_KOMANDI» INTEGER NOT NULL, «NOMER» INTEGER NOT NULL);

      ALTER TABLE «LUDI» ADD FOREIGN KEY («KOD_KOMANDI») REFERENCES TEAM («KOD_KOMANDI»);

      ALTER TABLE «LUDI» ADD FOREIGN KEY («KOD_KOMANDI») REFERENCES TEAM («KOD_KOMANDI»);

      CREATE TABLE «MATCHI» («KOD_K1» INTEGER NOT NULL, «KOD_K2» INTEGER, «OCHKI_1» INTEGER, «OCHKI_2» INTEGER, «KOMANDA_1» VARCHAR(20), «KOMANDA_2» VARCHAR(20), «KOD_KOMANDI» INTEGER NOT NULL, «VREMYA» DATE, «KOD_CHEMP» INTEGER NOT NULL, PRIMARY KEY («KOD_KOMANDI», «KOD_CHEMP»));

      ALTER TABLE «MATCHI» ADD FOREIGN KEY («KOD_CHEMP») REFERENCES CHEMP («KOD_CHEMP»);

      ALTER TABLE «MATCHI» ADD FOREIGN KEY («KOD_K1») REFERENCES TEAM («KOD_KOMANDI»);

      ALTER TABLE «MATCHI» ADD FOREIGN KEY («KOD_K2») REFERENCES TEAM («KOD_KOMANDI»);

      CREATE TABLE «WORK1» («KOD_KOMANDI» INTEGER NOT NULL, «KOD_TRENERA» INTEGER NOT NULL, PRIMARY KEY («KOD_KOMANDI», «KOD_TRENERA»));

      CREATE TABLE «TEAM» («KOD_KOMANDI» INTEGER NOT NULL, «STRANA» VARCHAR(20), «GOROD» VARCHAR(20), «GOD_OSN» DATE, «NAZVANIE» VARCHAR(20), PRIMARY KEY («KOD_KOMANDI»));

      CREATE TABLE «TRENER» («KOD_TRENERA» INTEGER NOT NULL, «FAMIL» VARCHAR(20), «IMYA» VARCHAR(20), «OTCHESTVO» VARCHAR(20), «TEL» VARCHAR(20), «ADRES» VARCHAR(20), PRIMARY KEY («KOD_TRENERA»));

      CREATE TABLE «POZITZIYA» ( «KOD_POZITZII» INTEGER NOT NULL,

      «POZITZIYA» VARCHAR(20), PRIMARY KEY («KOD_POZITZII»));

      2.4. Заполнение БД

      Таблица «Члены команд».

      2.5. Запросы к БД

      I. Однотабличные запросы:

      1. Выводит всех футболистов у кого первая буква фамилии находится в промежутке от «А» до «Г»:

      select famil from ludi where famil >=’А’ and famil =’А’ and famil

      3. Выводит футболистов, кто играет в каком клубе:

      select famil, nazvanie from ludi, team where team.kod_komandi=ludi.kod_komandi;

      III. С использованием функций и вычисляемых значений:

      1. Вычисляет количество играков команды Локомотв:

      select count(*) kod_chel from ludi where kod_komandi=1;

      2. Выводит команду основанную раньше всех:

      select min(god_osn) from team;

      3. Выводит какое количесво матчей сыграла команда Локомотив:

      select count(*) from matchi where kod_k1=1 or kod_k2=1;

      IV. С групповыми операциями

      1. Выводит количество играков каждой команды:

      selectnazvanie, count(famil) fromludi, teamwhereteam.kod_komandi=ludi.kod_komandigroupbynazvanie;

      2. Выводит сколько игр сыграно в каждом чемпионате:

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

      Список использованной литературы

      1. МАРТИН ГРУБЕР «Понимание SQL»

      2. Э.К. Лецкий «Информационные технологии на железнодорожном транспорте», М.:УМК МПС России, 2000.

      Цукерберг рекомендует:  #веб-разработка - JS для чайников
    Понравилась статья? Поделиться с друзьями:
    Все языки программирования для начинающих