изучаем SQL. Она позволяет решать многошаговые задачи одним выражением
 Скачать 1.6 Mb.
|
|
• Oracle University (http://education.oracle.com) • Learning Tree International (http://www.learningtree.com) • Microsoft Learning (learning) • MySQL Training (training) Проектирование БД Если вы новичок в SQL (а я предполагаю, что это так), то скорее всего будете работать с имеющимися БД, по крайней мере, поначалу. Одна ко если вы также отвечаете за разработку БД для своего проекта, реко мендую не просто ознакомиться с кратким обзором проектирования и нормализации БД, приведенным в главе 2, а рассмотреть этот вопрос более внимательно. На самом деле есть несколько разновидностей мо делей БД, каждая из которых имеет специальное назначение: Логические модели Обычно это представление высокого уровня детализации организа ции и среды, в которой осуществляется деятельность. Функциональные модели Обычно это представление среднего уровня детализации отдельного сегмента деятельности организации; как правило, используется в до полнение к спецификации проекта. Физические модели Обычно используются для формирования БД. Администратора БД, возможно, интересуют только физические моде ли, тогда как логические модели часто являются сферой интересов кор поративных архитекторов (если организации посчастливилось иметь команду разработки корпоративной архитекторы). 1 Аллигатор Декарт и Тим Банс «Программирование на Perl DBI», Символ Плюс, 2000. 2 С. Фейерштейн, Б. Прибыл «Oracle PL/SQL для профессионалов», Питер, 2003. 3 Кен Хендерсон «Профессиональное руководство по Transact SQL», Питер, 2005. Дополнительные источники 295 Как бы то ни было, прежде чем браться за выражения create table, не обходимо серьезно подумать об использовании инструмента моделиро вания для построения визуальных моделей. При создании моделей БД обычно используется одна из двух методик: Моделирование сущностей и связей (Entity relationship, ER) Используется практически исключительно для моделирования БД. Моделирование с использованием Унифицированного языка моделиро вания (Unified Modeling Language, UML) Универсальный инструмент моделирования для разработки объ ектно ориентированного программного обеспечения. Если БД проектируется как часть проекта по разработке объектно ори ентированного программного обеспечения, команда разработки может приобрести инструмент моделирования UML для объектного модели рования, чтобы использовать его и для проектирования БД. Если вы вольны в выборе инструментария, более полезным может оказаться один из следующих ER инструментов, способных формировать полно функциональные схемы БД (включая таблицы, ограничения, индек сы, представления и т. д.) по одному нажатию клавиши: • ERwin Data Modeler (ER моделирование) • Computer Associates (http://www.ca.com) • ER/Studio (ER моделирование) • Embarcadero Technologies (http://www.embarcadero.com) • Rational Rose (UML моделирование) • IBM (http://www.ibm.com) • Visio (и ER моделирование, и UML моделирование) • Microsoft (http://www.microsoft.com) Ниже приведены две хорошие книги по проектированию БД: Майкл Дж. Хернандес (Michael J. Hernandez) «Database Design for Mere Mortals: A Hands on Guide to Relational Database Design», Se cond Edition (Проектирование баз данных для простых смертных. Практическое руководство по проектированию реляционных баз данных), Addison Wesley, 2003. Эрик Дж. Нейбург (Eric J. Naiburg) и Роберт А. Максимчук (Robert A. Maksimchuk) «UML for Database Design», Addison Wesley, 2001. 1 1 Эрик Дж. Нейбург и Роберт А. Максимчук «Проектирование баз данных с помощью UML», Вильямс, 2002. 296 Приложение D Настройка баз данных Настройка БД – это, по сути, искусство и наука выявления и устране ния узких мест производительности в: • Приложениях, организующих доступ к БД (SQL, блокировки, тран закции) • Схемах БД (проектирование, индексация, сегментирование таблиц) • Серверах БД (конфигурация серверов, журналирование, управле ние соединением) • Дисковых массивах, на которых хранятся файлы БД (конфигура ции RAID, определение «горячих» точек) • Компьютерах, на которых располагаются серверы БД (конфигура ция операционной системы, файловые системы) • Сетях, распространяющих данные на/с серверов БД Выявление и устранение узких мест в оборудовании и программном обеспечении такой широкой номенклатуры может показаться пугающе сложной задачей, но большая часть работы обычно сосредоточена на схеме БД и языке SQL, который используется приложениями для дос тупа к БД. Это никак не преуменьшает значимости конфигурирования операционной системы, установки и конфигурирования сервера БД, а также компоновки ресурсов данных в дисковом массиве, но схемы БД и используемый для доступа к ним SQL – гораздо более динамичные компоненты системы, т. е. чреваты большим количеством проблем. Будь вы штатный специалист по вопросам производительности, про граммист БД или администратор БД, в круг вашей основной деятель ности по настройке войдут: • Просмотр плана выполнения SQL выражений для поиска неэффек тивных моментов • Разработка стратегий индексации для обеспечения эффективного доступа • Доработка или переписывание SQL выражений с целью повлиять на выбор плана выполнения Как упоминалось в главе 3, каждая БД включает компонент под на званием оптимизатор запросов, задачей которого является вычисле ние SQL выражений и выбор эффективного пути доступа к информа ционным ресурсам для достижения желаемых результатов. Результат работы оптимизатора – план выполнения, показывающий, какие ре сурсы в каком порядке используются. Каждая из трех БД, обсуждае мых в книге, включает инструменты получения и просмотра плана выполнения SQL выражения. Вам понадобится научиться генериро вать и расшифровывать планы выполнения для своей БД. Просто чтобы дать вам понять, о чем идет речь, привожу созданный MySQL план выполнения для запроса, организующего доступ к двум таблицам: Дополнительные источники 297 mysql> EXPLAIN SELECT c.fed_id, a.account_id, a.avail_balance > FROM account a INNER JOIN customer c > ON a.cust_id = c.cust_id > WHERE c.cust_type_cd = 'I' \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: c type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 13 Extra: Using where *************************** 2. row *************************** id: 1 select_type: SIMPLE table: a type: ref possible_keys: fk_a_cust_id key: fk_a_cust_id key_len: 4 ref: bank.c.cust_id rows: 1 Extra: 2 rows in set (0.00 sec) Чтобы увидеть план выполнения этого запроса, я просто поставил пе ред выражением select ключевое слово explain (объяснить), т. е. сервер получил команду показать план выполнения, а не результирующий набор запроса. План включает два этапа. Первый показывает, как бу дет осуществляться доступ к таблице customer (выполняется доступ ко всем строкам, поскольку столбец cust_type_cd не имеет индекса). Вто рой показывает, как будет организован доступ к таблице account (по средством внешнего ключа fk_a_cust_id). Формирование и расшифров ка планов выполнения не является предметом рассмотрения для ввод ной книги по SQL, поэтому в конце данного раздела приведены имею щиеся источники (книги и учебные курсы). Также в продаже есть несколько замечательных инструментов, которые помогут строить, вычислять и настраивать SQL выражения. Вот некоторые из них: • Quest Central for Oracle и Quest Central for SQL Server • Quest Software (http://www.quest.com) • Embarcadero SQL Profiler • Embarcadero Technologies (http://www.embarcadero.com) • Oracle Enterprise Manager Tuning Pack 298 Приложение D • Oracle Corporation (http://www.oracle.com) Можно порекомендовать несколько хороших книг по настройке про изводительности SQL: Томас Кайт (Thomas Kyte) «Effective Oracle by Design», McGraw Hill Osborne Media, 2003. 1 Кэри Миллсап (Cary Millsap) и Джефф Хольт (Jeff Holt) «Optimiz ing Oracle Performance», O’Reilly, 2003. 2 Джереми Заводный (Jeremy Zawodny) и Дерек Баллинг (Derek Ball ing) «High Performance MySQL» (Высокопроизводительный MySQL), O’Reilly, 2004. Кен Ингланд (Ken England) «Microsoft SQL Server 2000 Perform ance Optimization and Tuning Handbook» (Справочник по оптимиза ции производительности и настройке Microsoft SQL Server 2000), Digital Press, 2001. Также есть много курсов по настройке производительности, предос тавляемых следующими учебными центрами: • Oracle University (http://education.oracle.com) • Learning Tree International (http://www.learningtree.com) • Microsoft Learning (learning) • MySQL Training (training) Администрирование баз данных Администрирование БД, на самом деле, – многогранная задача, кото рая может включать что угодно или все из перечисленного ниже: • Установка и конфигурирование сервера БД • Проектирование БД • Программирование БД, особенно хранимых процедур, функций и триггеров • Система безопасности БД • Резервное копирование и восстановление • Настройка производительности Организации покрупнее могут нанять одного или нескольких специа листов в каждой из вышеупомянутых областей, а более мелкие часто ожидают, что со всем этим справится один администратор БД. Если вы будете заниматься проектированием, программированием и настрой кой, пожалуйста, ознакомьтесь со списками источников, приведенны 1 Томас Кайт «Эффективное проектирование приложений Oracle», Лори, 2006. 2 Кэри Миллсап «Oracle. Оптимизация производительности», Символ Плюс, 2006. Дополнительные источники 299 ми в предыдущих разделах. Однако все администраторы БД должны прочитать общие книги по администрированию или пройти курсы, что бы профессионально овладеть базовыми навыками администратора, та кими как установка и конфигурирование БД, создание пользователей и назначение привилегий, стратегии резервного копирования и восста новления, формирование схем. Еще один основной источник для адми нистраторов – справочное руководство по SQL для используемого сер вера БД, где приведен синтаксис SQL выражений управления схемой, таких как create index и alter table. Самые популярные справочники: Кевин Лоуни (Kevin Loney), Боб Брила (Bob Bryla) «Oracle Database 10g DBA Handbook» (Справочник администратора Oracle Database 10g), McGraw Hill Osborne Media, 2004. «MySQL Administrator’s Guide», MySQL Press, 2004. 1 Энтони Секвейра (Anthony Sequeira), Брайан Алдерман (Brian Al derman) «The SQL Server 2000 Book», Paraglyph, 2003. Также есть масса курсов по администрированию, предоставляемых следующими учебными центрами: • Oracle University (http://education.oracle.com) • Learning Tree International (http://www.learningtree.com) • Microsoft Learning (learning) • MySQL Training (training) Формирование отчетов Если вы отвечаете за разработку и формирование отчетов для своей ор ганизации, вот два самых важных навыка, которые стоит приобрести: • Знание возможностей механизма создания отчетов, используемых в вашей организации • Владение в совершенстве реализацией SQL, используемого вашим сервером БД Хотя большинство инструментов создания отчетов претендует на фор мирование SQL на базе визуального представления отчета, настоятель но рекомендую игнорировать эту возможность и самостоятельно соз давать SQL выражения для всех нетривиальных отчетов. В этом слу чае вы точно будете знать, что отправляется серверу БД, и впоследст вии сможете лучше поддерживать и настраивать отчеты. Некоторые механизмы создания отчетов достаточно гибки в отноше нии получения данных для отчета, но многие инструменты требуют, чтобы все данные одного отчета были сформированы посредством одно го запроса. Глубокое понимание SQL, особенно подзапросов (глава 9), 1 «MySQL. Руководство администратора», Вильямс, 2005. 300 Приложение D операций работы с множествами (глава 6) и условной логики (глава 11), позволит создавать гораздо более изощренные отчеты. Среди хороших книг по составлению отчетов можно назвать: Питер Блэкберн (Peter Blackburn), Уильям Вон (William Vaughn) «Hitchhiker’s Guide to SQL Server 2000 Reporting Services» (Руко водство по службам отчетов SQL Server 2000), Addison Wesley, 2004. Синди Хаусон (Cindi Howson) «Business Objects: The Complete Refe rence» (Business Objects. Полное руководство), McGraw Hill Osbor ne Media, 2003. Нейл Фитцджеральд (Neil FitzGerald) и др. «Special Edition Using Crystal Reports 10» (Использование Crystal Reports 10. Специаль ное издание), Pearson Education, 2004. Алфавитный указатель Символы () (скобки), оценка условий, 73 + (конкатенация), оператор, 127, 133 ’ (апострофы), 124 A, C, D, G ANSI (Национальный институт стандартизации США), 19 синтаксис соединения, 95 CLOB (Character Large Object – большой символьный объект), тип данных, 32 Configuration Wizard, запуск, 28 Daylight Savings Time, декретное время, 140 GMT (время по Гринвичу), 140 M MySQL установка, 27 выражение select блок into outfile, 263 индексы, вставка, 241 описание, 24 упорядоченные обновления и удаления, 267–268 часовые пояса, 142 N, O, S, U NULL, значения агрегатные функции, 160 выражения case, 228 описание, 40 фильтрация, 86–89 Oracle Text, инструмент, 247 SQL, описание, 19–24 UTC (универсальное глобальное время), 141 А автоприращение, 43 агрегатные функции, 154–161 count(), 155 агрегация, селективная, 222 администрирование БД, 298 аргументы больше нуля, 133 одноаргументные числовые функции, 135 функция truncate(), 138 арифметические операторы, 135 атомные часы, 141 Б базы данных ER диаграммы, 257 MySQL, 25, 27 администрирование, 298 доступ, 292 естественные соединения, 212–214 индексы, 240–251 многопользовательские, 230 настройка, 296 нереляционные системы, 14 ограничения, 251–256 определение, 13 программирование, 292 проектирование, 294 реляционные модели, 16 терминология, 18 транзакции, 232–239 хранение, 14 языки программирования, 292 банковская схема, 49 беззнаковые данные, 33 безопасность, инструмент командной строки mysql, 28 безопасные ошибки, 48 битовые индексы, 246 блоки from, 59–63 group by, 65 having, 65 into outfile, 263 limit, 259 302 Алфавитный указатель order by, 45 сочетание с limit, 260 select, агрегатные функции, 154 where, 63–65 условия групповой фильтрации, 165 условия, оценка, 72–75 запросы, 54–59 выполнение без блоков, 29 блокирование, 231 блокировка, 230 взаимоблокировки, 235 записи и чтения, 231 большой символьный объект (clob), 32 В взаимоблокировки, 235 високосные годы, 149 внешние ключи, 17, 19 ER диаграммы, 257 несуществующие, 47 ограничения, 41, 251 рекурсивные, 102 внешние объединения, 94 внешние соединения, 195–205 рекурсивные, 203 сравнение левосторонних и правосторонних, 199 трехсторонние, 201 условная логика, 216 внутренние объединения, 90 внутренние соединения, 93 возвращение даты, 147 одного столбца, подзапросы, 171 строки, 149 числа, 150 временные данные, 34–36 применение, 140–142 работа с, 147–151 создание, 142–147 время всемирное, 141 конфигурация часовых поясов, 140 по Гринвичу (GMT), 140 вставка данных в таблицы, 42 индексы, 241 интервалы, 147 ключевые слова, 58 псевдонимы столбцов, 56 встроенные функции, 135 выражения select, 56 строки, 127 числовые, 135 выбор механизмы хранения, 237 текстовые типы, 32 выполнение запросы, 51–53 без блоков, 29 транзакции, 232–239 условная логика, 216 выражение case, 218–229 выражения case, 218–229 create table, 39 insert, 43 update, сочетание с, 265 значения, 44 select, 55 блоки запроса, 54–59 расширения, 259–265 update, сочетание с insert, 265 агрегатные функции, 159 группировка, 163 для работы с данными, 9 классы, 20 обзор, 9 область видимости, 168 подзапросы, 168 как генераторы, 190 несвязанные, 170–179 применение, 183–193 связанные, 179–183 типы, 169 поиск, 83 устранение неполадок и, 46 регулярные, применение, 85 сортировка, 69 схемы, создание, 38 условия, создание, 75 Г группировка, 153–155 агрегатные функции, 156–161 выражения, 163 обобщения, 163 по нескольким столбцам, 162 по одному столбцу, 161 подзапросы, 188 сравнение неявных и явных групп, 156 фильтрация, 165 группы формирование, 161–165 Д данные с большим и малым кардинальным числом, 246–247 Алфавитный указатель 303 данные со знаком, 139 дата и время, 34 даты, 35 возвращение, 147 компоненты, 142–143 недействительные преобразования, 48 преобразования строки в дату, 144 создание, 145 форматирование, 35 декартовы произведения, 92 перекрестные соединения, 205–212 декретное время, 140 десятичные точки (числовые типы данных), 34 диаграммы ER (сущностей и связей), 257 диапазоны строки, 80 условия, 77 доступ базы данных, 292 транзакции, 232–239 дублирующие значения операторы объединения, 113 строки, уничтожение, 57 |