alan_beaulieu-learning_sql-ru (865932), страница 42
Текст из файла (страница 42)
А сервер MySQL спроектирован так, чтодля обеспечения низкоуровневой функциональности БД, вклю чая блокировку ресурсов и управление транзакциями, могут ис пользоваться несколько механизмов хранения. MySQL версии4.1 поддерживает следующие механизмы хранения:MyISAMНетранзакционный механизм, использующий блокировкитаблицы.MEMORYНетранзакционный механизм, применяемый для таблиц в опе ративной памяти.BDBТранзакционный механизм, использующий блокировку науровне страницы.InnoDBТранзакционный механизм, использующий блокировку науровне строки.MergeСпециальный механизм, предназначенный для создания не скольких идентичных таблиц MyISAM, создающих при этомвпечатление одной таблицы (также называется сегментиро ванием таблиц).NDBСпециальный механизм, предназначенный для распределе ния одной БД по нескольким компьютерам (также называет ся кластеризацией).ArchiveСпециальный механизм, предназначенный для хранениябольших объемов неиндексированных данных, преимущест венно для архивных целей.238Глава 12.
ТранзакцииНе надо думать, что MySQL вынуждает выбирать для БД единст венный механизм хранения. Этот сервер достаточно гибоки обеспечивает возможность применять для каждой таблицысобственный механизм хранения. Однако для всех таблиц, кото рые могут принимать участие в транзакциях, следовало бы ис пользовать механизм хранения InnoDB, применяющий блоки ровку на уровне строки и контроль версий, обеспечивая тем са мым наиболее высокий уровень согласованности из всех меха низмов хранения.Механизм хранения можно задавать явно при создании таблицыили изменять его для уже существующей таблицы.
Если меха низм хранения таблицы не известен, можно воспользоваться ко мандой show table, например:mysql> SHOW TABLE STATUS LIKE 'transaction' \G********************* 1. row *********************Name: transactionEngine: InnoDB...Create_options:Comment: InnoDB free: 3072 kB; ...Взглянув на второй элемент, можно понять, что таблица transaction уже использует механизм InnoDB. Если бы это было не так,можно было бы назначить механизм InnoDB для этой таблицыпосредством следующей команды:ALTER TABLE transaction ENGINE = INNODB;Вот пример использования точек сохранения:START TRANSACTION;UPDATE productSET date_retired = CURRENT_TIMESTAMP( )WHERE product_cd = 'XYZ';SAVEPOINT before_close_accounts;UPDATE accountSET status = 'CLOSED', close_date = CURRENT_TIMESTAMP( ),last_activity_date = CURRENT_TIMESTAMP( )WHERE product_cd = 'XYZ';ROLLBACK TO SAVEPOINT before_close_accounts;COMMIT;Результирующий эффект этой транзакции состоит в том, что фиктив ный тип счета XYZ выходит из обращения, но ни один из счетов не за крывается.Что такое транзакция?239При использовании точек сохранения необходимо помнить следующее:• Несмотря на название, при создании точки сохранения ничего несохраняется.
Если требуется сделать транзакцию постоянной, необ ходимо выполнить команду commit.• Если использовать команду rollback без указания точки сохране ния, все точки сохранения транзакции будут проигнорированыи отменена будет вся транзакция.Работая с SQL Server, используйте его собственные команды: save transaction (сохранить транзакцию) – для создания точки сохранения и rollback 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 или delete, использующих это имя.
Вот как можно добавить такой индексв базу данных 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, считаютиндексы независимыми объектами схемы.
Поэтому для SQLServer и Oracle индекс формировался бы с помощью командыcreate index (создать индекс):CREATE INDEX dept_name_idxON department (name);Все серверы БД позволяют просматривать доступные индексы. Уви деть все индексы определенной таблицы пользователи MySQL могутс помощью команды show (показать):mysql> SHOW INDEX FROM department \G*************************** 1.
row ***************************Table: departmentNon_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: dept_idCollation: ACardinality: 3Sub_part: NULLPacked: NULLNull:Index_type: BTREEComment:*************************** 2. row ***************************Table: departmentNon_unique: 0Key_name: dept_name_uidxSeq_in_index: 1Column_name: nameCollation: ACardinality: 3Sub_part: NULLPacked: NULLNull:Index_type: BTREEComment:2 rows in set (0.02 sec)Из результата видно, что для таблицы department есть два индекса: PRIMARY – для столбца 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.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
