Кириллов В.В., Громов Г.Ю. - Введение в реляционные базы данных. Литература для вузов isbn 9785941577705 в книге рассматриваются основные понятия баз данных и систем управления ими
 Скачать 11.62 Mb.
|
|
Глава 15 Оптимизация SQL- запросов 15.1. Введение Как сделать запросы SQL более эффективными, прежде всего с точки зрения времени их выполнения? Оптимизация SQL — это огромная и очень важная тема, специфичная для каждой СУБД. Мы рассмотрим общие вопросы оптимизации и укажем, на что надо обратить внимание. Каждая СУБД имеет в своем составе оптимизатор запросов — наиболее хит- роумный, сложный и интересный компонент. Сложность оптимизации SQL- запросов состоит в том, что язык SQL декларативен. В формулировках SQL- запросов указывается, какими свойствами должны обладать данные, которые желательно получить, но ничего не говорится о том, как система должна ре- ально выполнить запрос. Проблема в том, чтобы по декларативной формули- ровке запроса найти (или построить) программу, которую принято называть планом выполнения запроса. Более точно, основная трудность состоит в том, что нужно: уметь построить все возможные программы, результаты которых соответ- ствуют указанным свойствам; выбрать из множества этих программ такую программу, выполнение ко- торой было бы наиболее эффективным. Обе части проблемы нетривиальны. Прежде всего, необходимо обнаружить все корректные планы выполнения запроса или, по крайней мере, не упустить какой-либо план, который является наиболее эффективным. Далее, для облег- чения решения второй части проблемы требуется предельно сократить про- странство корректных планов, оставив только те планы, которые претендуют на максимальную эффективность. Обе эти задачи являются не полностью формализуемыми, поскольку отсутствуют точные математические критерии Часть V. Язык SQL. Создание базы данных 270 выбора. Обычно решение задач опирается на эвристические алгоритмы; об- судим некоторые из них. Предположим теперь, что первая часть проблемы каким-то образом решена. Теперь требуется решить вторую — и более ответственную — часть пробле- мы: найти в пространстве планов выполнения запроса единственный план, в соответствии с которым запрос будет реально выполнен (нередко эту часть проблемы называют проблемой физической оптимизации). Здесь уже требу- ются формальные критерии отбора. Таким критерием может быть оценочная стоимость выполнения запроса по данному плану. Основным компонентом оцениваемой стоимости может быть число обменов с устройствами внешней памяти, которые потребуются при выполнении плана запроса. Именно этот подход продолжает использоваться в подавляющем большинстве SQL- ориентированных СУБД. Технически не очень трудно обеспечить полный набор планов выполнения для любой заданной формулировки SQL-запроса. Но ситуация существенно усложняется тем, что для любого нетривиального SQL-запроса существует несколько (и даже много) семантически эквивалентных формулировок. Если не учитывать альтернативные формулировки заданного запроса, можно упус- тить эффективные планы выполнения. Если учитывать все возможные фор- мулировки, пространство корректных планов может оказаться слишком большим, чтобы можно было эффективно решить вторую часть проблемы оптимизации. Эти соображения привели к возникновению направления, ко- торое принято называть логической оптимизацией запросов. Мы не будем углубляться в детали этой проблемы. Отметим только, что СУБД позволяет познакомиться с полученным ею планом доступа и вносить в него изменения, если вы знаете лучший способ его выполнения. Однако ни один оптимизатор не сможет найти хорошего плана, если вы не будете знать и по возможности использовать важнейшие способы увеличения производи- тельности системы. (Для того чтобы выиграть в лотерею, необходимо иметь хотя бы один лотерейный билет!) Использование индексов. Индексы — это объекты баз данных, основная цель которых состоит в повышении производительности запросов (см. разд. 15.2). Задание выражений SQL с учетом производительности. Многие детали отличаются в разных реализациях. В разд. 15.4 вводятся общие принципы и предлагаются некоторые вариации, повышающие производительность. Настройка физических параметров СУБД. Под физическими параметрами понимаются такие, как способ разделения пространства хранения данных, размер кеша, выделенного под операторы (при условии, что ваша система Глава 15. Оптимизация SQL- запросов 271 поддерживает кеширование операторов). Все эти вопросы зависят исклю- чительно от конкретной реализации, поэтому они не освещаются в этой главе. Однако мы упоминаем о них, так как, возможно, будет полезно справиться о них в документации продукта. Минимизация уровня локализации блокировки или использование опти- мистической блокировки (см. разд. 9.3.2). 15.2. Использование индексов 15.2.1. Что такое индексы Основным способом повышения производительности операций SQL, не за- висящим от платформы, является использование индексов. Индекс SQL по- хож на полный алфавитный указатель в книге, т. е. на список всех терминов (обычно за исключением тривиальных), упоминающихся в книге со ссылка- ми на все места, где они встречаются. Индекс SQL — это список всех значе- ний в группе из одного или нескольких столбцов, упорядоченный в некото- ром приемлемом для данного типа данных смысле (например, в порядке возрастания для чисел или в алфавитном порядке для символьных строк). Каждое значение имеет указатель на строку в таблице, где это значение встречается. Стандарты, как правило, не касаются вопросов производительности. Поэтому синтаксис индексов и их функциональность различны для конкретных про- дуктов. Однако имеются некоторые общие черты. Индексы работают неявно. Они в основном используются в запросах. При выполнении запроса СУБД решает, какие индексы надо применить и надо ли вообще. Если вы создаете индексы, то следовательно, определяете возмож- ность их использования. Можно также использовать подсказки, называемые директивами (directives), которые приказывают оптимизатору изменить спо- соб выполнения, включая использование или неиспользование конкретных индексов при условии, что это приведет к хорошим результатам. Индексы можно классифицировать несколькими способами, но самая фун- даментальная классификация — это уникальные (unique) и неуникальные (non-unique) индексы. В уникальном индексе может быть только одна ссылка для каждого значения, а в неуникальном индексе — произвольное число. Уникальный индекс похож наограничение целостности UNIQUE , которое за- прещает повторение значения в столбце или в группе столбцов. Часть V. Язык SQL. Создание базы данных 272 15.2.2. Создание индексов Индекс в качестве объекта схемы создается так же, как и другие объекты — с помощью предложения CREATE : CREATE [UNIQUE] INDEX имя_иидекса ON имя_таблицы {имя_столбца [,...]); Ключевые слова: CREAТЕ [UNIQUE] INDEX имя_индекса Создается новый индекс с именем имя_индекса в контексте текущей базы данных и схемы. Поскольку индексы связаны с конкретными таблицами, имя_индекса должно быть уникальным только в пределах таблицы, от кото- рой индекс зависит. Ключевое слово UNIQUE определяет индекс как ограни- чение типа UNIQUE в данной таблице. Оно запрещает повторяющиеся значе- ния в проиндексированном столбце или столбцах таблицы. ON имя_та6лицы Объявляет, с какой таблицей связывается индекс. Индекс зависит от табли- цы. Если таблица удаляется, то же происходит и с индексом. (имя_столбца [,...]) Определяется один или несколько столбцов таблицы, которые будут индек- сироваться. Наличие проиндексированного столбца или столбцов позволяют оптимизатору запросов базы данных значительно увеличить скорость мани- пуляций с данными, например инструкций SELECT или DELETE . Все крупные производители СУБД поддерживают составные индексы (composite index), иначе называемые сцепленными индексами (concatenated index). Эти индексы используются в тех случаях, когда лучше всего проводить поиск по двум столбцам как по единому целому, например по фамилии и имени. Общие правила. Индексы создаются по таблицам для ускорения операций манипулирования данными, которые проводятся с таблицами, например предложений WHERE и JOIN . Индексы способны ускорить и другие операции, например: определение значений MIN() или MAX() по индексированному столбцу; сортировку и группировку столбцов таблицы; поиск типа IS NULL или IS NOT NULL ; извлечение данных, когда нужны только проиндексированные данные. После создания таблицы вы можете создавать индексы по столбцам таблицы. Хорошей практикой является создание индексов по столбцам, которые часто указываются во фразах WHERE и JOIN Глава 15. Оптимизация SQL- запросов 273 15.2.3. Необходимость использования индексов Использование индексов имеет некоторые недостатки. На них расходуются ресурсы. При всяком изменении или удалении содержимого индексированного столбца, а также при добавлении новой строки индекс необходимо обнов- лять. Это замедляет операции обновления. Однако обычно индексы не за- медляют обновление на столько же, на сколько они ускоряют запросы. Индекс сам по себе занимает место — вдобавок к тому, что занимает таб- лица. Индекс не обязательно должен храниться вместе с таблицей, если только он не в той же схеме. При извлечении из таблицы очень большого числа строк использование индекса — только потеря времени. Это несколько замедляет исполнение. Решите, стоит ли индекс того места, которое он занимает, и превысит ли вы- игрыш во времени исполнения запросов замедление обновлений. Чтобы лучше понять ситуацию, рассмотрите следующие вопросы: 1. Как часто будет выполняться запрос к таблице и как часто она будет об- новляться? 2. Насколько вероятно то, что обычные запросы и обновления будут выпол- няться слишком медленно? 3. Как часто столбцы, которые вы собираетесь индексировать, будут исполь- зоваться в предикатах, особенно в предикатах запросов? 4. Как часто столбцы, которые вы собираетесь индексировать, будут исполь- зоваться в предикатах соединения (join)? Соединения в основном выпол- няются медленнее, чем другие виды запросов, и выигрыш во времени по- лучается больше. 5. Что для вас более важно: время исполнения или место на диске? Индексы жертвуют одним ради другого. Обычно время более ценно, так как индексы радикально уменьшают время исполнения, а места занимают не так уж много. 6. Имеются ли предпосылки для создания специальных видов индексов, та- ких как индексы массивов битов или кластеры? Эти виды индексов и об- ласти их применения подробно обсуждаются в [1 и 3]. Наилучший способ решить, нужны ли индексы — это проанализировать код приложения и понять, чего оно требует. При использовании интерактивного или динамического SQL нужно выбрать наилучшие из возможных предпо- ложений. Однако полезной оказывается возможность создания и удаления индексов по ходу дела, так что можно постепенно настраивать производи- тельность приложения желаемым способом. ЧАСТЬ V I СОЗДАНИЕ ПРИЛОЖЕНИЙ НА SQL Глава 16. Программирование на SQL Глава 17. Процедурные расширения SQL Глава 18. Хранимые процедуры Глава 16 Программирование на SQL 16.1. Введение Язык SQL можно использовать для доступа к базам данных в двух режимах. Приинтерактивной работе и в прикладных программах. По большей части сам язык одинаков в обоих вариантах. Эта двойственность SQL имеет не- сколько преимуществ: прикладные программисты могут относительно легко научиться писать программы, которые в ходе своей работы обращаются к базам данных; все возможности, доступные в интерактивном языке запросов, автомати- чески доступны и в прикладных программах; предложения SQL, предназначенные для использования в программах, вначале могут быть проверены в интерактивном режиме, а затем вставле- ны в исходный текст программы; программы могут работать с базами данных на уровне таблиц и результа- тов запросов. Так как непроцедурный язык SQL первоначально создавался исключительно для осуществления доступа к базам данных, то в нем отсутствовали даже са- мые элементарные возможности процедурных языков программирования [2]. В SQL нельзя объявлять переменные, в нем отсутствуют инструкция перехо- да GOTO , инструкция IF для проверки условий, инструкции FOR , DO и WHILE для организации циклов и т. д. Чтобы создать полноценную программу, предна- значенную для работы с базой данных, необходимо либо написать ее: на обычном языке программировании, таком как COBOL, PL/1, PASCAL, C, JAVA или PHP, и по мере надобности "встраивать" в нее инструкции SQL; на каком-либо из расширений языка SQL, разработанных различными производителями СУБД, и, к сожалению, пока не стандартизированных. Часть VI. Создание приложений на SQL 278 Рассмотрим существующие возможности создания прикладных программ, использующих язык SQL: встраивание в код некоторого языка программирования SQL-предложений (статический SQL); формирование в процессе выполнения программы на некотором языке программирования кода SQL-предложений и дальнейшего их выполнения (динамический SQL); использование интерфейса программирования приложений (Application Programming Interface, API), позволяющего реализовывать работу с базой данных через предоставляемый набор функций. API может быть целевым, предоставленным производителем коммерческой СУБД для работы имен- но с этой базой данных, или межплатформенным, реализующим унифици- рованные средства доступа к СУБД различных производителей (напри- мер, ODBC — Open DataBase Connectivity); использование процедурных расширений SQL, вводимых производителя- ми СУБД. Практически в каждой СУБД применяется свой процедурный язык: SQLpl в DB2, PL/SQL в Oracle, PL/pgSQL в PostgreSQL, Trunsact- SQL в SQL Server и т. п. Эти диалекты включают кроме SQL средства ус- ловной обработки (например, IF…THEN ), управляющие операторы (например, циклы WHILE ), переменные, средства обработки ошибок, позволяющие создавать хранимые процедуры, функции и триггеры, использующиеся большим количеством приложений, повышая эффективность их функцио- нирования, обеспечивая высокую степень защиты последних и унифици- руя способы обращения к данным из приложений. 16.2. Статический SQL При таком подходе к созданию прикладных программ, предложения SQL встраиваются непосредственно в исходный текст программы, создаваемой на "полноценном" языке программирования, который поддерживает SQL. Для пересылки информации из базы данных в программу используются специ- альные предложения встроенного SQL (рис. 16.1). Исходный текст програм- мы, включающий в себя предложения встроенного SQL, перед компиляцией подается на вход специального препроцессора SQL, который с помощью ря- да других программных модулей преобразует этот исходный текст в испол- няемую программу. Везде, где во встроенных предложениях SQL могут стоять константы, вместо них можно использовать переменные из базовой прикладной программы. Глава 16. Программирование на SQL 279 Эти переменные называются базовыми переменными. С помощью этих, входных, переменных в базу данных можно передавать значения, вводимые пользователем. Исходный текст программы Прекомпилятор (препроцессор) Создание модуля доступа Замена SQL-предложения на вызовы подпрограмм Компиляция Связывание Компановка (линкование) Исполняемый код План доступа Рис. 16.1. Процесс выполнения программы, содержащей предложения встроенного SQL Базовые переменные применяются также и для получения результатов запро- сов. Значения этих, выходных, переменных могут затем обрабатываться при- кладной программой. 16.3. Динамический SQL Статический SQL пригоден для написании обычных программ обработки данных, в которых заранее определена и жестко зафиксирована схема досту- па к базе данных. В каждом встроенном предложении SQL программист за- ранее указывает, на какие таблицы и столбцы он будет ссылаться. Входные базовые переменные придают статическому SQL некоторую гибкость, но не могут коренным образом изменить его статическую природу. Часть VI. Создание приложений на SQL 280 Однако существует достаточно большой класс приложений, в которых не- возможно заранее определить схему доступа к базе данных. Например, про- грамма создания запросов или программа, генерирующая отчеты, должна иметь возможность во время выполнения решать, какие предложения SQL она будет использовать для доступа к базе данных. Программа для работы с электронными таблицами, установленная на персональном компьютере и имеющая доступ к серверной базе данных, также должна иметь возможность сформировать запрос к этой базе данных "на ходу". Перечисленные про- граммы, а также другие клиентские приложения общего назначения невоз- можно написать, используя инструкции статического SQL. Для создания этих программ необходима усовершенствованная разновидность встроенного SQL, которая называется динамический SQL. Общая концепция, лежащая в основе динамического SQL, проста — встро- енная инструкция SQL не записывается в исходный текст программы. Вместо этого программа формирует текст инструкции во время выполнения в одной из своих областей данных, а затем передает сформированную инструкцию в СУБД для динамического выполнения. Хотя детали реализации являются довольно сложными, весь динамический SQL построен на этом простом принципе, о котором не следует забывать. Динамический SQL менее эффективен (в смысле производительности), чем статический SQL. По этой причине всегда, когда это возможно, используется статический SQL. Важность динамического SQL возросла с появлением трехуровневых Internet- архитектур, в которых управляющее программное обеспечение расположено на одной системе (прикладной, или промежуточный, уровень), а СУБД — на другой (информационный, или серверный, уровень). В большинстве таких систем программная логика довольно непостоянна, динамична по своей при- роде. Она должна адаптироваться к меняющимся условиям бизнеса, к появ- лению новых деловых правил. Регулярно изменяющаяся программная среда плохо сочетается со статическим SQL, в котором между программой и со- держимым базы данных существует жесткая связь. Статический и динамический SQL представляют собой классический пример компромисса между эффективностью и гибкостью, что выражается в сле- дующем. Простота. Статический SQL относительно прост; даже самый сложный его элемент — наборы записей — можно легко освоить, вспомнив кон- цепцию файлового ввода/вывода. Динамический SQL довольно сложен, в нем осуществляется динамическое формирование предложений, исполь- зуются структуры данных переменной длины и выполняется распределе- ние памяти. Глава 16. Программирование на SQL 281 Производительность. Во время компиляции программы, использующей статический SQL, создастся план выполнения всех встроенных инструкций; инструкции динамического SQL компилируются непосредственно на эта- пе выполнения. В результате производительность статического SQL, как правило, намного выше, чем динамического. Гибкость. Динамический SQL дает программе возможность решать на этапе выполнения, какие конкретно инструкции SQL она будет выпол- нять. Статический SQL требует, чтобы все инструкции SQL были написа- ны заранее, на этапе создания программы; тем самым он ограничивает гибкость программы. Пример использования динамического SQL будет рассмотрен в главе 18. 16.4. Интерфейс программирования приложений Наконец, еще об одном способе создания приложений к базам данных — это интерфейс прикладного программирования (API) баз данных, который ис- пользуется для передачи предложений SQL в СУБД и для получения резуль- татов их обработки из СУБД. Упомянем здесь только три распространенных API, которые предоставляют единый интерфейс для работы с разными плат- формами баз данных. ODBC (Open DataBase Connectivity) — это программный интерфейс дос- тупа к базам данных, разработанный фирмой X/Open. Позволяет единооб- разно оперировать с разными источниками данных, отвлекаясь от особен- ностей взаимодействия в каждом конкретном случае. Поставщики различных СУБД создают драйверы, реализующие конкретное наполне- ние стандартных функций из ODBC API с учетом особенностей их про- дукта. Приложения используют эти функции, реализованные в драйверах, для унифицированного доступа к различным источникам данных, исполь- зуя соответствующие источникам данных драйверы. ADO.NET(ActiveX Data Objects) — это высокоуровневый интерфейс при- кладного программирования баз данных от компании Microsoft, работаю- щий на платформе .NET. Он представляет собой коллекцию интерфейсов .NET, доступ к которым осуществляется с помощью любого языка с под- держкой .NET. Главное преимущество ADO.NET — это простота исполь- Часть VI. Создание приложений на SQL 282 зования, переносимость в пределах платформы .NET, интеграция с XML и доступ к источникам данных, отличным от реляционных баз данных. JDBC (Java Database Connectivity) разработан компанией Sun Microsys- tems в первую очередь как API баз данных для языка Java, JDBC является наиболее популярным интерфейсом прикладного программирования баз данных на языке Java [3]. Он предоставляет возможность перенесения ко- да с одной операционной системы на другую, предлагает приемлемую производительность для большинства областей применения, и он доста- точно хорошо документирован. Кроме того, драйверы для большинства баз данных распространяются, как правило, бесплатно. |