alan_beaulieu-learning_sql-ru (865932), страница 44
Текст из файла (страница 44)
Традиционные стратегии индексации в данном случае негодятся. Чтобы справиться с этой ситуацией, MySQL и Oracle Databaseвключают для документов специальные механизмы индексации и по иска. В SQL Server и MySQL есть так называемые индексы по всемутексту (fulltext) (для MySQL индексы по всему тексту доступны толь ко с механизмом хранения MyISAM). Oracle Database включает мощ ный инструментарий Oracle Text. Поиск по документам достаточноспециализирован, поэтому не будем приводить здесь пример, но я хо тел хотя бы сообщить о такой возможности.Использование индексовОбычно сервер использует индексы для быстрого обнаружения место положения интересующих строк в конкретной таблице.
После этого248Глава 13. Индексы и ограничениясервер просматривает ассоциированную таблицу и извлекает дополни тельную информацию, запрашиваемую пользователем. Рассмотримследующий запрос:mysql> SELECT emp_id, fname, lname> FROM employee> WHERE emp_id IN (1, 3, 9, 15, 22);++++| emp_id | fname | lname|++++|1 | Michael | Smith||3 | Robert | Tyler||9 | Jane| Grossman ||15 | Frank | Portman |++++4 rows in set (0.00 sec)Для этого запроса сервер может использовать в качестве индекса пер вичный ключ для столбца emp_id, чтобы найти местоположение сотруд ников с ID 1, 3, 9, 15 и 22 в таблице employee, и затем просмотреть каж дую из пяти строк, чтобы извлечь значения столбцов имени и фамилии.Однако если индекс содержит все, что необходимо для удовлетворениязапроса, серверу не надо просматривать ассоциированную таблицу.Для иллюстрации давайте посмотрим на то, как обращается оптимиза тор запросов с одним и тем же запросом при разных типах индексов.Запрос, агрегирующий остатки на счетах для определенных клиентов,выглядит так:mysql> SELECT cust_id, SUM(avail_balance) tot_bal> FROM account> WHERE cust_id IN (1, 5, 9, 11)> GROUP BY cust_id;+++| cust_id | tot_bal |+++|1 | 4557.75 ||5 | 2237.97 ||9 | 10971.22 ||11 | 9345.55 |+++4 rows in set (0.00 sec)С помощью выражения explain (объяснить) рассмотрим, как оптими затор запросов MySQL принимает решение об обработке запроса.
Сер вер не будет выполнять запрос, а покажет план его выполнения:mysql> EXPLAIN SELECT cust_id, SUM(avail_balance) tot_bal> FROM account> WHERE cust_id IN (1, 5, 9, 11)> GROUP BY cust_id \G*************************** 1. row ***************************249Индексыid: 1select_type: SIMPLEtable: accounttype: indexpossible_keys: fk_a_cust_idkey: fk_a_cust_idkey_len: 4ref: NULLrows: 24Extra: Using where1 row in set (0.00 sec)Каждый сервер БД включает инструменты, позволяющие уви деть, как оптимизатор запросов обрабатывает SQL выражение.В SQL Server увидеть план выполнения перед выполнениемSQL выражения позволяет выражение set showplan_text on.В Oracle Database есть выражение explain plan, записывающееплан выполнения в специальную таблицу plan_table.Если не вдаваться в детали, то план выполнения сообщает следующее:• Индекс fk_a_cust_id используется для поиска строк таблицы account,которые удовлетворяют условию блока where.• После прочтения индекса ожидается, что сервер просмотрит все24 строки таблицы account для сбора данных о доступных остатках,поскольку он не знает, что могут существовать другие клиенты,кроме клиентов с ID 1, 5, 9 и 11.Индекс fk_a_cust_id – еще один индекс, автоматически сгенерирован ный сервером, но на этот раз из за ограничения внешнего ключа, а неограничения первичного ключа (более подробно об этом позже в этойглаве).
Индекс fk_a_cust_id создан для столбца account.cust_id, такимобразом, сервер использует его для определения местоположения кли ентов с ID 1, 5, 9 и 11 в таблице account, а затем посещает эти строкидля извлечения и агрегирования данных по доступным остаткам.Далее добавим новый индекс с названием acc_bal_idx для обоих столб цов cust_id и avail_balance:mysql> ALTER TABLE account> ADD INDEX acc_bal_idx (cust_id, avail_balance);Query OK, 24 rows affected (0.03 sec)Records: 24 Duplicates: 0 Warnings: 0Теперь, имея этот индекс, посмотрим, как оптимизатор будет обраба тывать тот же запрос:mysql> EXPLAIN SELECT cust_id, SUM(avail_balance) tot_bal> FROM account> WHERE cust_id IN (1, 5, 9, 11)> GROUP BY cust_id \G*************************** 1.
row ***************************250Глава 13. Индексы и ограниченияid: 1select_type: SIMPLEtable: accounttype: rangepossible_keys: acc_bal_idxkey: acc_bal_idxkey_len: 4ref: NULLrows: 8Extra: Using where; Using index1 row in set (0.01 sec)Сравнение двух планов выполнения дает следующие различия:• Вместо индекса fk_a_cust_id оптимизатор использует новый индексacc_bal_idx.• Оптимизатор ускоряет свою работу, поскольку теперь ему надо об работать только восемь строк вместо 24.• Для обеспечения результатов запроса таблица account не нужна(обозначена через Using index в столбце Extra).Следовательно, сервер может определить с помощью индексов место положение строк в ассоциированной таблице или использовать индекскак таблицу, если он содержит все столбцы, необходимые для удовле творения запроса.Только что рассмотренный процесс – это пример оптимизациизапроса.
Оптимизация включает изучение SQL выражения и ус тановление доступных серверу ресурсов, необходимых для вы полнения этого выражения. Чтобы обеспечить более эффектив ное выполнение выражения, можно изменить SQL выражениеили настроить ресурсы БД, или сделать и то, и другое. Оптими зация – тонкий вопрос, поэтому настоятельно рекомендую про читать руководство по оптимизации для используемого сервераили найти хорошую книгу по этой теме, чтобы увидеть все воз можные подходы к оптимизации для конкретного сервера.Обратная сторона индексацииЕсли индексы – это так замечательно, почему бы не индексировать всеподряд? Чтобы понять, почему большее количество индексов не всегдахорошо, надо помнить, что каждый индекс – это таблица (особого ти па, но все равно таблица). Поэтому при каждом добавлении или удале нии строки из таблицы все ее индексы должны быть модифицирова ны. При обновлении строки все задействованные индексы столбца илистолбцов тоже должны изменяться.
Следовательно, чем больше индек сов, тем больше работы приходится выполнять серверу для обновле ния всех объектов схемы. А это очень замедляет процесс.Индексам требуется некоторое количество памяти, а также некотороевнимание администраторов, поэтому наилучшая стратегия – добавлять251Ограниченияиндекс только при возникновении вполне определенной необходимо сти. Если индекс нужен только для конкретной цели, например длявыполнения программы по ежемесячному обслуживанию, всегда мож но добавить индекс, выполнить программу и удалить индекс до тогомомента, пока он не понадобится вновь.
Для хранилищ данных, гдеиндексы очень нужны в рабочие часы (поскольку пользователи состав ляют отчеты и выполняют случайные запросы), но становятся пробле мой при загрузке данных в хранилище в ночное время, общепринятойпрактикой является уничтожение индексов перед загрузкой данныхи их повторное создание перед открытием хранилищ для работы.ОграниченияОграничение – это просто некоторое ограничивающее условие, нала гаемое на один или более столбцов таблицы. Есть несколько разныхтипов ограничений, включая:Ограничения первичного ключа (Primarykey constraints)Идентифицируют столбец или столбцы, гарантирующие уникаль ность в рамках таблицы.Ограничения внешнего ключа (Foreignkey constraints)На один или более столбцов накладывается такое ограничение: онимогут содержать только значения, содержащиеся в столбцах первич ного ключа другой таблицы.
Также могут ограничиваться допусти мые значения других таблиц, если установлены правила update cascade (каскадное обновление) или delete cascade (каскадное удаление).Ограничения уникальности (Unique constraints)На один или более столбцов накладывается ограничение: они могутсодержать только уникальные в рамках таблицы значения.Проверочные ограничения целостности (Check constraints)Ограничивают допустимые значения столбца.Без ограничений под сомнением может оказаться непротиворечивостьбазы данных.
Например, если сервер допускает изменение ID клиентав таблице customer без изменения этого же ID клиента в таблице account, все может закончиться тем, что счета больше не будут указыватьна соответствующие записи клиентов (это явление известно как «оси ротевшие» строки (orphaned rows)). Но если заданы ограничения пер вичного и внешнего ключей, сервер или сформирует ошибку в случаепопытки изменения или удаления данных, на которые ссылаются дру гие таблицы, или распространит изменения на другие таблицы (болееподробно об этом чуть позже).Если при работе с сервером MySQL требуются ограничения внеш него ключа, в таблицах должен использоваться механизм хра нения InnoDB.252Глава 13.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
