alan_beaulieu-learning_sql-ru (865932), страница 43

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

Текст из файла (страница 43)

Ограничения будут рассмотрены в этой главе позже.Если после создания индекса выясняется, что он не оправдывает себя,его можно удалить следующим образом: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;Уникальные индексыПри проектировании БД важно определить, какие столбцы могут содержать дублирующие данные, а какие нет.

Например, в таблице individual может быть два клиента по имени Джон Смит (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_idxON 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Этот индекс будет полезен для запросов, использующих имя и фамилию или только фамилию, но не подходит для запросов, в которых задано только имя сотрудника.

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

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

Следующие разделы иллюстрируют разные типы индексации, доступные в различных серверах.Индексы на основе ВдереваВсе приведенные до сих пор индексы – это индексы на основе сбалансированного дерева (balancedtree indexes), чаще называемые индексами на основе Вдерева (Btree indexes). MySQL, Oracle Database и SQLServer используют такие индексы по умолчанию, поэтому если явно незапросить другой тип индекса, вы всегда получите этот индекс.

Каки следовало ожидать, индексы на основе Вдерева организованы какдеревья с одним или более уровнями узлов (branch nodes), приводящими к единственному уровню листьев (leaf nodes). Узлы используютсядля навигации по дереву, тогда как на листьях располагаются фактические значения и информация о местоположении. Например, индексна основе Вдерева, созданный для столбца employee.lname, мог бы выглядеть примерно так, как показано на рис.

13.1.Если бы был сделан запрос для выбора всех сотрудников, фамилии которых начинаются на 'G', сервер нашел бы верхний узел – корневойузел (root node) – и проследовал бы по связи к узлу, отвечающему за фамилии, начинающиеся с букв от 'A' до 'M'. Этот узел, в свою очередь,направил бы сервер к листу, содержащему фамилии, начинающиесяс букв от 'G' до 'I'.

Затем сервер считывал бы значения листа до техпор, пока не встретил бы значение, начинающееся не на 'G' (которымв данном случае является 'Hawthorne').A–MN–ZA–CD–FG– IJ–MBarkerBlakeFlemingFowlerGoodingGrossmanHawthorneN–PQ–ST–VW–ZJamesonMarkhamMasonParkerPortmanРис. 13.1. Пример сбалансированного дереваRobertsSmithTuckerTulmanTylerZiegler246Глава 13. Индексы и ограниченияПри вставке, обновлении и удалении данных таблицы employee сервербудет стараться сохранять сбалансированность дерева, чтобы количество узлов/листьев с одной стороны корневого узла не сильно превышало количество узлов с другой стороны.

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

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

Чтобы обработать эту ситуацию с большей эффективностью, Oracle Database включает битовые индексы (bitmap indexes), которые формируютбитовый образ каждого значения, хранящегося в столбце. На рис. 13.2показано, как может выглядеть битовый индекс для данных столбцаaccount.product_cd.Этот индекс содержит шесть битовых карт, по одной для каждого значения столбца product_cd (два из восьми доступных типов счетов не используются). Каждая битовая карта включает значение 0/1 для кажValue/row 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24BUS0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 0 0CD0 0 1 0 0 0 0 0 0 0 0 0 1 1 0 0 0 0 1 0 0 0 0 0CHK1 0 0 1 0 1 0 1 0 0 1 1 0 0 1 0 1 0 0 1 0 0 1 0MM0 0 0 0 0 0 1 0 0 1 0 0 0 0 0 0 0 1 0 0 0 0 0 0SAV0 1 0 0 1 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0SBL0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1Рис.

13.2. Пример битового индекса247Индексыдой из 24 строк таблицы account. Таким образом, если серверу делаетсязапрос на извлечение всех депозитных счетов денежного рынка (product_cd = 'MM'), он просто находит все значения 1 в битовой карте MMи возвращает строки 7, 10 и 18. Если требуется найти несколько значений, сервер также может комбинировать битовые карты. Например,если пользователь хочет получить все депозитные счета денежногорынка и сберегательные счета (product_cd = 'MM' или product_cd = 'SAV'),сервер может осуществить для битовых карт MM и SAV операцию OR(ИЛИ) и возвратит строки 2, 5, 7, 9, 10, 16 и 18.Битовые индексы – милое компактное решение по индексации данныхс малым кардинальным числом. Однако эта стратегия не годится, есличисло хранящихся в столбце значений слишком велико по сравнениюс числом строк (в таком случае говорят о данных с большим кардинальным числом (highcardinality)), потому что серверу пришлось бы обслуживать слишком много битовых карт.

Например, не следует создавать битовый индекс для столбца первичного ключа, поскольку он является примером максимально возможного количества элементов (новое значение для каждой строки).Пользователи Oracle могут формировать битовые индексы, просто добавляя ключевое слово bitmap в выражение create index:CREATE BITMAP INDEX acc_prod_idx ON account (product_cd);Битовые индексы широко используются в информационных хранилищах, где обычно индексируются большие объемы данных для столбцов,содержащих относительно небольшое количество значений (например,квартальные отчеты, географические регионы, продукты, продавцы).Текстовые индексыЕсли БД используется для хранения документов, может потребоваться обеспечить пользователям возможность выполнять поиск слов илифраз в документах. Конечно, не хочется, чтобы сервер открывал каждый документ и просматривал его в поисках нужного текста при каждом запросе.

Характеристики

Список файлов ответов (шпаргалок)