Sql и базы данных
 Скачать 1.44 Mb.
|
|
20. Что такое индексы? Какие они бывают? Индекс (index) — объект базы данных, создаваемый с целью повышения производительности выборки данных. Наборы данных могут иметь большое количество записей, которые хранятся в произвольном порядке, и их поиск по заданному критерию путем последовательного просмотра набора данных запись за записью может занимать много времени. Индекс формируется из значений одного или нескольких полей и указателей на соответствующие записи набора данных, - таким образом, достигается значительный прирост скорости выборки из этих данных. Преимущества ● ускорение поиска и сортировки по определенному полю или набору полей. ● обеспечение уникальности данных. Недостатки ● требование дополнительного места на диске и в оперативной памяти и чем больше/длиннее ключ, тем больше размер индекса. ● замедление операций вставки, обновления и удаления записей, поскольку при этом приходится обновлять сами индексы. Индексы предпочтительней для: ● Поля-счетчика, чтобы в том числе избежать и повторения значений в этом поле; ● Поля, по которому проводится сортировка данных; ● Полей, по которым часто проводится соединение наборов данных. Поскольку в этом случае данные располагаются в порядке возрастания индекса и соединение происходит значительно быстрее; ● Поля, которое объявлено первичным ключом (primary key); ● Поля, в котором данные выбираются из некоторого диапазона. В этом случае как только будет найдена первая запись с нужным значением, все последующие значения будут расположены рядом. Использование индексов нецелесообразно для: ● Полей, которые редко используются в запросах; ● Полей, которые содержат всего два или три значения, например: мужской, женский пол или значения «да», «нет». Типы индексов: По порядку сортировки: упорядоченные — индексы, в которых элементы упорядочены; По источнику данных: индексы по представлению (view); индексы по выражениям. По воздействию на источник данных вы можете помещать ● кластерный индекс - при определении в наборе данных физическое расположение данных перестраивается в соответствии со структурой индекса. Логическая структура набора данных в этом случае представляет собой скорее словарь, чем индекс. Данные в словаре физически упорядочены, например по алфавиту. Кластерные индексы могут дать существенное увеличение производительности поиска данных даже по сравнению с обычными индексами. Увеличение производительности особенно заметно при работе с последовательными данными. ● некластерный индекс — наиболее типичные представители семейства индексов. В отличие от кластерных, они не перестраивают физическую структуру набора данных, а лишь организуют ссылки на соответствующие записи. Для идентификации нужной записи в наборе данных некластерный индекс организует специальные указатели, включающие в себя: информацию об идентификационном номере файла, в котором хранится запись; идентификационный номер страницы соответствующих данных; номер искомой записи на соответствующей странице; содержимое столбца. По структуре: B*-деревья; B+-деревья; B-деревья; Хэши. По количественному составу ● простой индекс (индекс с одним ключом) — строится по одному полю; ● составной (многоключевой, композитный) индекс — строится по нескольким полям при этом важен порядок их следования; ● индекс с включенными столбцами — некластеризованный индекс, дополнительно содержащий кроме ключевых столбцов еще и неключевые; ● главный индекс (индекс по первичному ключу) — это тот индексный ключ, под управлением которого в данный момент находится набор данных. Набор данных не может быть отсортирован по нескольким индексным ключам одновременно. Хотя, если один и тот же набор данных открыт одновременно в нескольких рабочих областях, то у каждой копии набора данных может быть назначен свой главный индекс. По характеристике содержимого ● уникальный индекс состоит из множества уникальных значений поля; ● плотный индекс (NoSQL) — индекс, при котором, каждом документе в индексируемой коллекции соответствует запись в индексе, даже если в документе нет индексируемого поля. ● разреженный индекс (NoSQL) — тот, в котором представлены только те документы, для которых индексируемый ключ имеет какое-то определённое значение (существует). ● пространственный индекс — оптимизирован для описания географического местоположения. Представляет из себя многоключевой индекс состоящий из широты и долготы. ● составной пространственный индекс — индекс, включающий в себя кроме широты и долготы ещё какие-либо мета-данные (например теги). Но географические координаты должны стоять на первом месте. ● полнотекстовый (инвертированный) индекс — словарь, в котором перечислены все слова и указано, в каких местах они встречаются. При наличии такого индекса достаточно осуществить поиск нужных слов в нём и тогда сразу же будет получен список документов, в которых они встречаются. ● хэш-индекс предполагает хранение не самих значений, а их хэшей, благодаря чему уменьшается размер (а, соответственно, и увеличивается скорость их обработки) индексов из больших полей. Таким образом, при запросах с использованием хэш-индексов, сравниваться будут не искомое со значения поля, а хэш от искомого значения с хэшами полей. Из-за нелинейности хэш-функций данный индекс нельзя сортировать по значению, что приводит к невозможности использования в сравнениях больше/меньше и «is null». Кроме того, так как хэши не уникальны, то для совпадающих хэшей применяются методы разрешения коллизий. ● битовый индекс (bitmap index) — метод битовых индексов заключается в создании отдельных битовых карт (последовательностей 0 и 1) для каждого возможного значения столбца, где каждому биту соответствует запись с индексируемым значением, а его значение равное 1 означает, что запись, соответствующая позиции бита содержит индексируемое значение для данного столбца или свойства. ● обратный индекс (reverse index) — B-tree индекс, но с реверсированным ключом, используемый в основном для монотонно возрастающих значений (например, автоинкрементный идентификатор) в OLTP системах с целью снятия конкуренции за последний листовой блок индекса, т.к. благодаря переворачиванию значения две соседние записи индекса попадают в разные блоки индекса. Он не может использоваться для диапазонного поиска. ● функциональный индекс, индекс по вычисляемому полю (function-based index) — индекс, ключи которого хранят результат пользовательских функций. Функциональные индексы часто строятся для полей, значения которых проходят предварительную обработку перед сравнением в команде SQL. Например, при сравнении строковых данных без учета регистра символов часто используется функция UPPER. Кроме того, функциональный индекс может помочь реализовать любой другой отсутствующий тип индексов данной СУБД. ● первичный индекс — уникальный индекс по полю первичного ключа. ● вторичный индекс — индекс по другим полям (кроме поля первичного ключа). ● XML-индекс — вырезанное материализованное представление больших двоичных XML- объектов (BLOB) в столбце с типом данных xml. По механизму обновления ● полностью перестраиваемый — при добавлении элемента заново перестраивается весь индекс. ● пополняемый (балансируемый) — при добавлении элементов индекс перестраивается частично (например, одна из ветви) и периодически балансируется. По покрытию индексируемого содержимого ● полностью покрывающий (полный) индекс — покрывает всё содержимое индексируемого объекта. ● частичный индекс (partial index) — это индекс, построенный на части набора данных, удовлетворяющей определенному условию самого индекса. Данный индекс создан для уменьшения размера индекса. ● инкрементный (delta) индекс — индексируется малая часть данных(дельта), как правило, по истечении определенного времени. Используется при интенсивной записи. Например, полный индекс перестраивается раз в сутки, а дельта-индекс строится каждый час. По сути это частичный индекс по временной метке. ● индекс реального времени (real-time index) — особый вид инкрементного индекса, характеризующийся высокой скоростью построения. Предназначен для часто меняющихся данных. Индексы в кластерных системах ● глобальный индекс — индекс по всему содержимому всех сегментов БД (shard). ● сегментный индекс — глобальный индекс по полю-сегментируемому ключу (shard key). Используется для быстрого определения сегмента, на котором хранятся данные в процессе маршрутизации запроса в кластере БД. ● локальный индекс — индекс по содержимому только одного сегмента БД. В чем отличие между кластерными и некластерными индексами? Некластерные индексы - данные физически расположены в произвольном порядке, но логически упорядочены согласно индексу. Такой тип индексов подходит для часто изменяемого набора данных. При кластерном индексировании данные физически упорядочены, что серьезно повышает скорость выборок данных (но только в случае последовательного доступа к данным). Для одного набора данных может быть создан только один кластерный индекс. Имеет ли смысл индексировать данные, имеющие небольшое количество возможных значений? Примерное правило, которым можно руководствоваться при создании индекса - если объем информации (в байтах) НЕ удовлетворяющей условию выборки меньше, чем размер индекса (в байтах) по данному условию выборки, то в общем случае оптимизация приведет к замедлению выборки. Когда полное сканирование набора данных выгоднее доступа по индексу? Полное сканирование производится многоблочным чтением. Сканирование по индексу - одноблочным. Также, при доступе по индексу сначала идет сканирование самого индекса, а затем чтение блоков из набора данных. Число блоков, которые надо при этом прочитать из набора зависит от фактора кластеризации. Если суммарная стоимость всех необходимых одноблочных чтений больше стоимости полного сканирования многоблочным чтением, то полное сканирование выгоднее и оно выбирается оптимизатором. Таким образом, полное сканирование выбирается при слабой селективности предикатов запроса и/или слабой кластеризации данных, либо в случае очень маленьких наборов данных. Как создать индекс? Индекс можно создать либо с помощью выражения CREATE INDEX: CREATE INDEX index_name ON table_name (column_name) либо указав ограничение целостности в виде уникального UNIQUE или первичного PRIMARY ключа в операторе создания таблицы CREATE TABLE. 21. Чем TRUNCATE отличается от DELETE? Delete в целом не предназначена для полной очистки таблицы - генерит редо, поддерживает индексы, то есть работает медленно. Truncate наоборот предназначен именно для быстрой очистки ВСЕЙ таблицы или партиции - освобождает занятые экстенты. • Операция TRUNCATE не записывает в журнал событий удаление отдельных строк. Вследствие чего не может активировать триггеры • После операции TRUNCATE для некоторых СУБД (например, Oracle ) следует неявная операция COMMIT Поэтому удаленные в таблице записи нельзя восстановить операцией ROLLBACK . Но существуют и СУБД, в которых операция TRUNCATE может участвовать в транзакциях, например, PostgreSQL и Microsoft SQL Server • Операция DELETE блокирует каждую строку, а TRUNCATE — всю таблицу. • Операция TRUNCATE не возвращает какого-то осмысленного значения (обычно возвращает 0) в отличие от DELETE , которая возвращает число удаленных строк. • Операция TRUNCATE в некоторых СУБД (например, MySQL или Microsoft SQL Server ), сбрасывает значение счетчиков (для полей с AUTOINCREMENT / IDENTITY). В PostgreSQL для сброса счётчиков необходимо указывать модификатор RESTART IDENTITY. • Операция TRUNCATE в некоторых СУБД (например, MySQL , PostgreSQL или Microsoft SQL Server ) запрещена для таблиц, содержащих внешние ключи других таблиц. В PostgreSQL существует, однако, модификатор CASCADE, который разрешает TRUNCATE в этой ситуации – данные из зависимых таблиц удаляются в той же транзакции. • В SQLite операция как таковая отсутствует, но есть оптимизация операции DELETE, которая «значительно ускоряет её работу, если отсутствует аргумент WHERE». 22. Что такое хранимые процедуры? Для чего они нужны? Это муветон, нарушается принцип ,.., надо лезть в базу чтобы исправить её, а без неё вся логика в коде Храни́мая процеду́ра — объект базы данных, представляющий собой набор SQL- инструкций, который компилируется один раз и хранится на сервере. Хранимые процедуры очень похожи на обыкновенные процедуры языков высокого уровня, у них могут быть входные и выходные параметры и локальные переменные, в них могут производиться числовые вычисления и операции над символьными данными, результаты которых могут присваиваться переменным и параметрам. В хранимых процедурах могут выполняться стандартные операции с базами данных (как DDL, так и DML). Кроме того, в хранимых процедурах возможны циклы и ветвления, то есть в них могут использоваться инструкции управления процессом исполнения. 23. Что такое представления (VIEW)? Для чего они нужны? Представление, View - виртуальная таблица, представляющая данные одной или более таблиц альтернативным образом. В действительности представление – всего лишь результат выполнения оператора SELECT, который хранится в структуре памяти, напоминающей SQL таблицу. Они работают в запросах и операторах DML точно также как и основные таблицы, но не содержат никаких собственных данных. Представления значительно расширяют возможности управления данными. Это способ дать публичный доступ к некоторой (но не всей) информации в таблице.\ CREATE VIEW Londonstaff AS SELECT * FROM Salespeople WHERE city = 'London'; 24. Что такое временные таблицы? Для чего они нужны? Временная таблица - это объект базы данных, который хранится и управляется системой базы данных на временной основе. Они могут быть локальными (только я могу работать) или глобальными (все). Используется для сохранения результатов вызова хранимой процедуры, уменьшение числа строк при соединениях, агрегирование данных из различных источников или как замена курсоров и параметризованных представлений. Срок жизни временной таблицы – сеанс с БД 25. Что такое транзакции? Расскажите про принципы ACID. Транзакция - это воздействие на базу данных, переводящее её из одного целостного состояния в другое и выражаемое в изменении данных, хранящихся в базе данных. это N (N≥1) запросов к БД, которые выполнятся успешно все вместе или не выполнятся вовсе. Назовите основные свойства транзакции. ● Атомарность (atomicity) гарантирует, что никакая транзакция не будет зафиксирована в системе частично. Будут либо выполнены все её подоперации, либо не выполнено ни одной. ● Согласованность (consistency). Транзакция, достигающая своего нормального завершения (EOT — end of transaction, завершение транзакции) и, тем самым, фиксирующая свои результаты, сохраняет согласованность базы данных. Другими словами, каждая успешная транзакция по определению фиксирует только допустимые результаты. ● Изолированность (isolation). Во время выполнения транзакции параллельные транзакции не должны оказывать влияние на ее результат. ● Долговечность (durability). Независимо от проблем на нижних уровнях (к примеру, обесточивание системы или сбои в оборудовании) изменения, сделанные успешно завершённой транзакцией, должны остаться сохраненными после возвращения системы в работу. Если пользователь получил подтверждение от системы, что транзакция выполнена, он может быть уверен, что сделанные им изменения не будут отменены из-за какого-либо сбоя. 26. Расскажите про уровни изолированности транзакций. Выбирая используемый уровень изолированности транзакций, разработчик информационной системы в определённой мере обеспечивает выбор между скоростью работы и обеспечением гарантированной согласованности получаемых из системы данных При параллельном выполнении транзакций возможны следующие проблемы: 1. потерянное обновление (англ. lost update) — при одновременном изменении одного блока данных разными транзакциями теряются все изменения, кроме последнего; 2. «грязное» чтение (англ. dirty read) — чтение данных, добавленных или изменённых транзакцией, которая впоследствии не подтвердится (откатится); 3. неповторяющееся чтение (англ. non-repeatable read) — при повторном чтении в рамках одной транзакции ранее прочитанные данные оказываются изменёнными; 4. фантомное чтение (англ. phantom reads) — одна транзакция в ходе своего выполнения несколько раз выбирает множество строк по одним и тем же критериям. Другая транзакция в интервалах между этими выборками добавляет строки или изменяет столбцы некоторых строк, используемых в критериях выборки первой транзакции, и успешно заканчивается. В результате получится, что одни и те же выборки в первой транзакции дают разные множества строк. Уровни изолированности транзакций: В порядке увеличения изолированности транзакций и, соответственно, надежности работы с данными: ● Чтение неподтверждённых данных (грязное чтение) (read uncommitted, dirty read) — чтение незафиксированных изменений как своей транзакции, так и параллельных транзакций. Нет гарантии, что данные, измененные другими транзакциями, не будут в любой момент изменены в результате их отката, поэтому такое чтение является потенциальным источником ошибок. Невозможны потерянные изменения, возможны неповторяемое чтение и фантомы. ● Чтение подтвержденных данных (read committed) — чтение всех изменений своей транзакции и зафиксированных изменений параллельных транзакций. Потерянные изменения и грязное чтение не допускается, возможны неповторяемое чтение (когда мы видим обновленные и удаленные строки (UPDATE, DELETE)) и фантомы (когда мы видим добавленные записи (INSERT)). ● Повторяемость чтения (repeatable read, snapshot) — чтение всех изменений своей транзакции, любые изменения, внесенные параллельными транзакциями после начала своей, недоступны. Потерянные изменения, грязное и неповторяемое чтение невозможны, возможны фантомы. ● Упорядочиваемость (serializable) — результат параллельного выполнения сериализуемой транзакции с другими транзакциями должен быть логически эквивалентен результату их какого-либо последовательного выполнения. Проблемы синхронизации не возникают. Уровни Не должно быть Будут проблемы read uncommitted, dirty read видят не закомиченные результаты ТР + 2 проблемы lost update dirty read + non-repeatable read+ phantom reads read committed видят закомиченные результаты + 2 проблемы lost update + dirty read non-repeatable read+ phantom reads repeatable read, snapshot видят результаты Update и Delete + 1 проблема lost update + dirty read + non-repeatable read phantom reads Serializable lost update + dirty read + non-repeatable read+ phantom reads Нет проблем с синхронизацией Какие проблемы могут возникать при параллельном доступе с использованием транзакций? При параллельном выполнении транзакций возможны следующие проблемы: ● Потерянное обновление (lost update) — при одновременном изменении одного блока данных разными транзакциями одно из изменений теряется; ● «Грязное» чтение (dirty read) — чтение данных, добавленных или измененных транзакцией, которая впоследствии не подтвердится (откатится); ● Неповторяющееся чтение (non-repeatable read) — при повторном чтении в рамках одной транзакции ранее прочитанные данные оказываются измененными; ● Фантомное чтение (phantom reads) — одна транзакция в ходе своего выполнения несколько раз выбирает множество записей по одним и тем же критериям. Другая транзакция в интервалах между этими выборками добавляет или удаляет записи или изменяет столбцы некоторых записей, используемых в критериях выборки первой транзакции, и успешно заканчивается. В результате получится, что одни и те же выборки в первой транзакции дают разные множества записей. |