изучаем SQL. Она позволяет решать многошаговые задачи одним выражением
 Скачать 1.6 Mb.
|
|
Точки сохранения транзакций В некоторых случаях может возникнуть проблема, когда требуется от кат транзакции, но не хочется отменять все, что было сделано в рам ках этой транзакции. Для таких ситуаций в транзакции можно уста новить одну или более точек сохранения (savepoints) и использовать их для отката к определенному месту транзакции, а не откатывать полностью к началу. Всем точкам сохранения должны быть присвоены имена, что позволит иметь несколько таких точек в одной транзакции. Создать точку со хранения my_savepoint можно так: SAVEPOINT my_savepoint; Что такое транзакция? 237 Чтобы сделать откат к определенной точке сохранения, просто выпол няется команда rollback, за которой следуют ключевые слова to save point (к точке сохранения) и имя точки сохранения: ROLLBACK TO SAVEPOINT my_savepoint; Выбор механизма хранения Для низкоуровневых операций с БД, таких как извлечение из таблицы конкретной строки по значению первичного ключа, Oracle Database и Microsoft SQL Server используют всего один механизм хранения. А сервер MySQL спроектирован так, что для обеспечения низкоуровневой функциональности БД, вклю чая блокировку ресурсов и управление транзакциями, могут ис пользоваться несколько механизмов хранения. MySQL версии 4.1 поддерживает следующие механизмы хранения: MyISAM Нетранзакционный механизм, использующий блокировки таблицы. MEMORY Нетранзакционный механизм, применяемый для таблиц в опе ративной памяти. BDB Транзакционный механизм, использующий блокировку на уровне страницы. InnoDB Транзакционный механизм, использующий блокировку на уровне строки. Merge Специальный механизм, предназначенный для создания не скольких идентичных таблиц MyISAM, создающих при этом впечатление одной таблицы (также называется сегментиро ванием таблиц). NDB Специальный механизм, предназначенный для распределе ния одной БД по нескольким компьютерам (также называет ся кластеризацией). Archive Специальный механизм, предназначенный для хранения больших объемов неиндексированных данных, преимущест венно для архивных целей. 238 Глава 12. Транзакции Вот пример использования точек сохранения: START TRANSACTION; UPDATE product SET date_retired = CURRENT_TIMESTAMP( ) WHERE product_cd = 'XYZ'; SAVEPOINT before_close_accounts; UPDATE account SET status = 'CLOSED', close_date = CURRENT_TIMESTAMP( ), last_activity_date = CURRENT_TIMESTAMP( ) WHERE product_cd = 'XYZ'; ROLLBACK TO SAVEPOINT before_close_accounts; COMMIT; Результирующий эффект этой транзакции состоит в том, что фиктив ный тип счета XYZ выходит из обращения, но ни один из счетов не за крывается. Не надо думать, что MySQL вынуждает выбирать для БД единст венный механизм хранения. Этот сервер достаточно гибок и обеспечивает возможность применять для каждой таблицы собственный механизм хранения. Однако для всех таблиц, кото рые могут принимать участие в транзакциях, следовало бы ис пользовать механизм хранения InnoDB, применяющий блоки ровку на уровне строки и контроль версий, обеспечивая тем са мым наиболее высокий уровень согласованности из всех меха низмов хранения. Механизм хранения можно задавать явно при создании таблицы или изменять его для уже существующей таблицы. Если меха низм хранения таблицы не известен, можно воспользоваться ко мандой show table, например: mysql> SHOW TABLE STATUS LIKE 'transaction' \G ********************* 1. row ********************* Name: transaction Engine: InnoDB Create_options: Comment: InnoDB free: 3072 kB; ... Взглянув на второй элемент, можно понять, что таблица transac tion уже использует механизм InnoDB. Если бы это было не так, можно было бы назначить механизм InnoDB для этой таблицы посредством следующей команды: ALTER TABLE transaction ENGINE = INNODB; Что такое транзакция? 239 При использовании точек сохранения необходимо помнить следующее: • Несмотря на название, при создании точки сохранения ничего не сохраняется. Если требуется сделать транзакцию постоянной, необ ходимо выполнить команду commit. • Если использовать команду rollback без указания точки сохране ния, все точки сохранения транзакции будут проигнорированы и отменена будет вся транзакция. Работая с SQL Server, используйте его собственные команды: save trans action (сохранить транзакцию) – для создания точки сохранения и roll back transaction (откатить транзакцию) – для отката к точке сохранения. За каждой такой командой должно следовать имя точки сохранения. Индексы и ограничения Поскольку данная книга посвящена методикам программирования, предыдущие двенадцать глав рассматривали элементы языка SQL, по зволяющие создавать мощные выражения select, insert, update и delete. Однако базы данных обладают и другими средствами, косвенно влияю щими на создаваемый код. В этой главе рассмотрены два таких средст ва: индексы и ограничения. Индексы При вставке строки в таблицу сервер БД не пытается поместить дан ные в какое то определенное место таблицы. Например, добавляя строку в таблицу department, сервер размещает ее не по порядку номе ров столбца dept_id и не в алфавитном порядке по столбцу name. Вместо этого сервер просто помещает данные в следующую доступную ячейку памяти в файле (сервер хранит список свободной памяти для каждой таблицы). Поэтому, чтобы ответить на запрос к таблице department, серверу приходится проверять каждую ее строку. Например, выпол няется следующий запрос: mysql> SELECT dept_id, name > FROM department > WHERE name LIKE 'A%'; + + + | dept_id | name | + + + | 3 | Administration | + + + 1 row in set (0.03 sec) Чтобы найти все отделы, названия которых начинаются на 'A', сервер должен просмотреть каждую строку таблицы department и проверить Индексы 241 содержимое столбца name. Если имя отдела начинается с 'A', строка до бавляется в результирующий набор. Для таблицы, состоящей только из трех строк, этот метод хорош. А представьте, сколько времени потребуется, чтобы ответить на этот запрос, если в таблице 3 000 000 строк? При некотором числе строк (больше трех и меньше 3 000 000) сервер достигает того предела, когда уже не успевает ответить на запрос в течение приемлемого промежут ка времени без дополнительной помощи. Эта помощь приходит в фор ме одного или нескольких индексов таблицы department. Даже если вы ни разу не слышали об индексе БД, вы, безусловно, знае те, что такое индекс (в этой книге он тоже есть). Индекс – это просто механизм поиска определенного элемента ресурса. Например, в конце каждого технического издания есть предметный указатель, позволяю щий найти определенное слово или фразу. В указателе эти слова и фра зы перечислены в алфавитном порядке, что позволяет читателю быст ро перейти к определенной букве, выбрать нужную запись и затем най ти страницу или страницы, где можно отыскать это слово или фразу. Как человек использует предметный указатель, чтобы найти слова в печатном издании, так и сервер БД с помощью индексов выявляет местоположение строк в таблице. Индексы – это специальные табли цы, содержимое которых, в отличие от обычных таблиц данных, хра нится в определенном порядке. Однако индекс включает не все данные сущности, а только столбец (или столбцы), используемый для опреде ления местоположения строк в таблице данных, а также информацию, описывающую физическое размещение строк. Поэтому предназначе ние индексов – помочь в извлечении подмножества строк и столбцов таблицы без необходимости проверять все строки. Создание индекса Вернемся к таблице department. Допустим, принято решение добавить индекс для столбца name, чтобы ускорить выполнение запросов по пол ному или частичному имени отдела, а также операций update или de lete , использующих это имя. Вот как можно добавить такой индекс в базу данных MySQL: mysql> ALTER TABLE department > ADD INDEX dept_name_idx (name); Query OK, 3 rows affected (0.08 sec) Records: 3 Duplicates: 0 Warnings: 0 Это выражение создает индекс (точнее, индекс на основе В дерева, но более подробно об этом позже) для столбца department.name. Более того, индексу присвоено имя dept_name_idx. При наличии индекса оптимиза тор запросов (обсуждаемый в главе 3) воспользуется им, если сочтет это выгодным (например, если в таблице department только три строки, оптимизатор прекрасно справится и без использования индекса и про 242 Глава 13. Индексы и ограничения смотрит всю таблицу). Если для таблицы есть несколько индексов, оп тимизатору придется выбрать индекс, лучше всего подходящий для конкретного SQL выражения. MySQL рассматривает индексы как необязательные компонен ты таблицы, вот почему для добавления или удаления индекса используется команда alter table (видоизменить таблицу). Дру гие серверы БД, включая SQL Server и Oracle Database, считают индексы независимыми объектами схемы. Поэтому для SQL Server и Oracle индекс формировался бы с помощью команды create index (создать индекс): CREATE INDEX dept_name_idx ON department (name); Все серверы БД позволяют просматривать доступные индексы. Уви деть все индексы определенной таблицы пользователи MySQL могут с помощью команды show (показать): mysql> SHOW INDEX FROM department \G *************************** 1. row *************************** Table: department Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: dept_id Collation: A Cardinality: 3 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 2. row *************************** Table: department Non_unique: 0 Key_name: dept_name_uidx Seq_in_index: 1 Column_name: name Collation: A Cardinality: 3 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: 2 rows in set (0.02 sec) Из результата видно, что для таблицы department есть два индекса: PRI MARY – для столбца dept_id и dept_name_idx – для столбца name. Посколь ку мы создавали только один индекс (dept_name_idx), может возник Индексы 243 нуть вопрос, откуда взялся второй. При создании таблицы department выражение create table включало ограничение, назначающее столбец dept_id первичным ключом таблицы. Вот выражение для создания таблицы: CREATE TABLE department (dept_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(20) NOT NULL, CONSTRAINT pk_department PRIMARY KEY (dept_id) ); Когда таблица была создана, сервер MySQL автоматически сформиро вал индекс для столбца первичного ключа, которым в данном случае является dept_id, и назвал индекс PRIMARY. Ограничения будут рассмот рены в этой главе позже. Если после создания индекса выясняется, что он не оправдывает себя, его можно удалить следующим образом: mysql> ALTER TABLE department > DROP INDEX dept_name_idx; Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 Пользователи SQL Server и Oracle Database для уничтожения индекса должны использовать команду drop index: DROP INDEX dept_name_idx; Уникальные индексы При проектировании БД важно определить, какие столбцы могут со держать дублирующие данные, а какие нет. Например, в таблице indi vidual может быть два клиента по имени Джон Смит (John Smith), по скольку у каждой строки будут свои идентификатор (cust_id), дата ро ждения и идентификационный номер (customer.fed_id), помогающие различать их. Однако вряд ли допустимо, чтобы в таблице department было два отдела под одним и тем же названием. Можно установить правило, запрещающее дублирование имен отделов, создав для столб ца department.name уникальный индекс (unique index). Уникальный индекс выполняет несколько функций, поскольку поми мо обеспечения всех преимуществ обычного индекса он также служит механизмом запрета дублирования значений в индексируемом столб це. При любой вставке строки или изменении индексированного столб ца сервер БД проверяет уникальный индекс, чтобы увидеть, нет ли та кого значения в другой строке таблицы. Вот как создавался бы уни кальный индекс для столбца department.name: mysql> ALTER TABLE department > ADD UNIQUE dept_name_idx (name); 244 Глава 13. Индексы и ограничения Query OK, 3 rows affected (0.04 sec) Records: 3 Duplicates: 0 Warnings: 0 В SQL Server и Oracle Database при создании индекса нужно только добавить ключевое слово unique: CREATE UNIQUE INDEX dept_name_idx ON department (name); При наличии такого индекса в случае попытки добавить еще один от дел под названием 'Operations' будет получена ошибка: mysql> INSERT INTO department (dept_id, name) > VALUES (999, 'Operations'); ERROR 1062 (23000): Duplicate entry 'Operations' for key 2 Нет необходимости создавать уникальные индексы для столбца( ов) первичного ключа, поскольку сервер уже проверяет уникальность значений первичных ключей. Однако при необходимости для одной таблицы можно создать несколько уникальных индексов. Составные индексы Кроме уже представленных индексов по одному столбцу, можно созда вать индексы, охватывающие несколько столбцов. Если, например, требуется проводить поиск сотрудников по имени и фамилии, можно сделать индекс сразу для двух столбцов: mysql> ALTER TABLE employee > ADD INDEX emp_names_idx (lname, fname); Query OK, 18 rows affected (0.10 sec) Records: 18 Duplicates: 0 Warnings: 0 Этот индекс будет полезен для запросов, использующих имя и фами лию или только фамилию, но не подходит для запросов, в которых за дано только имя сотрудника. Чтобы понять почему, рассмотрим, как проводился бы поиск телефонного номера. Чтобы быстро найти чей то номер телефона, если известны имя и фамилия, можно воспользовать ся телефонной книгой, поскольку она организована по фамилии, а по том по имени. Если известно только имя человека, придется просмат ривать все записи телефонной книги и выбирать каждую запись с ука занным именем. Поэтому при создании составных индексов (multiple column indexes) необходимо тщательно продумать, какой столбец указывать первым, а какой вторым и т. д., чтобы индекс был максимально полезным. Од нако следует помнить, что если требуется обеспечить адекватное вре мя ответа, ничто не мешает создать несколько индексов, используя тот же набор столбцов, но в другом порядке. Индексы 245 Типы индексов Индексация – мощный инструмент, но из за большого разнообразия типов данных единственная стратегия индексации не всегда является оптимальной. Следующие разделы иллюстрируют разные типы индек сации, доступные в различных серверах. Индексы на основе В дерева Все приведенные до сих пор индексы – это индексы на основе сбалан сированного дерева (balanced tree indexes), чаще называемые индекса ми на основе В дерева (B tree indexes). MySQL, Oracle Database и SQL Server используют такие индексы по умолчанию, поэтому если явно не запросить другой тип индекса, вы всегда получите этот индекс. Как и следовало ожидать, индексы на основе В дерева организованы как деревья с одним или более уровнями узлов (branch nodes), приводящи ми к единственному уровню листьев (leaf nodes). Узлы используются для навигации по дереву, тогда как на листьях располагаются факти ческие значения и информация о местоположении. Например, индекс на основе В дерева, созданный для столбца employee.lname, мог бы вы глядеть примерно так, как показано на рис. 13.1. Если бы был сделан запрос для выбора всех сотрудников, фамилии ко торых начинаются на 'G', сервер нашел бы верхний узел – корневой узел (root node) – и проследовал бы по связи к узлу, отвечающему за фа милии, начинающиеся с букв от 'A' до 'M'. Этот узел, в свою очередь, направил бы сервер к листу, содержащему фамилии, начинающиеся с букв от 'G' до 'I'. Затем сервер считывал бы значения листа до тех пор, пока не встретил бы значение, начинающееся не на 'G' (которым в данном случае является 'Hawthorne'). A – M N – Z A – C D – F G – I J – M N – P W – Z Q – S T – V Barker Blake Fleming Fowler Ziegler Parker Portman Roberts Smith Tucker Tulman Tyler Gooding Grossman Hawthorne Jameson Markham Mason Рис. 13.1. Пример сбалансированного дерева 246 Глава 13. Индексы и ограничения При вставке, обновлении и удалении данных таблицы employee сервер будет стараться сохранять сбалансированность дерева, чтобы количе ство узлов/листьев с одной стороны корневого узла не сильно превы шало количество узлов с другой стороны. Сервер может добавлять или удалять узлы, чтобы более равномерно перераспределять значения. Он даже может добавить или удалить целый уровень узлов. Поддерживая дерево сбалансированным, сервер может быстро перемещаться к листь ям и находить нужные значения без навигации по множеству уровней узлов. Битовые индексы Индексы на основе В дерева замечательно подходят для обработки столбцов, содержащих много разных значений, таких как имена/фа милии клиентов, но они могут стать громоздкими для столбца с неболь шим количеством значений. Например, принято решение сформиро вать индекс для столбца account.product_cd, чтобы обеспечить быстрый выбор всех счетов определенного типа (например, текущих, сберега тельных). Однако есть всего восемь разных типов счетов, и некоторые из них встречаются гораздо чаще остальных. Поэтому по мере роста ко личества счетов могут возникнуть сложности с обеспечением сбаланси рованности индекса на основе В дерева. Для столбцов, содержащих небольшое количество значений при боль шом числе строк (это известно как данные с малым кардинальным чис лом (low cardinality)), необходима другая стратегия индексации. Что бы обработать эту ситуацию с большей эффективностью, Oracle Data base включает битовые индексы (bitmap indexes), которые формируют битовый образ каждого значения, хранящегося в столбце. На рис. 13.2 показано, как может выглядеть битовый индекс для данных столбца account.product_cd Этот индекс содержит шесть битовых карт, по одной для каждого зна чения столбца product_cd (два из восьми доступных типов счетов не ис пользуются). Каждая битовая карта включает значение 0/1 для каж Value/row BUS CD CHK MM SAV SBL 1 0 0 1 0 0 0 2 3 4 5 6 7 8 9 10 12 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 0 0 11 13 14 15 16 17 18 19 20 21 22 23 24 0 1 0 0 0 0 0 0 0 0 0 1 1 0 0 0 0 1 0 0 0 0 0 0 0 1 0 1 0 1 0 0 1 1 0 0 1 0 1 0 0 1 0 0 1 0 0 0 0 0 0 1 0 0 1 0 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 1 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 |