Sql Flashcards
Что такое индексы?
Индексы - это структура данных, позволяющая быстро определить положение интересующих данных в таблице.
Создается для столбца или совокупности столбцов.
Обеспечивает возможность повысить производительность запросов.
create index iN_NAME on TABLE_NAME(COLUMN_NAME)
Индексы используются
Фильтрация
Объединение
Сортировка данных, но тут при создании индекса нужно указать сортировку, и она должна совпадать, например COLUMN_NAME DESC
Индекс по нескольким столбцам будет использоваться, если поиск идет по этим столбцам, или поиск идет по первому столбцу из имени.
Индексы минусы.
Обновление
Вставка
ACID
ACID описывает требования к транзакционной системе (например, к СУБД), обеспечивающие наиболее надёжную и предсказуемую её работу.
Транза́кция (англ. transaction) — группа последовательных операций с базой данных, которая представляет собой логическую единицу работы с данными.
Atomicity — Атомарность
Атомарность гарантирует, что никакая транзакция не будет зафиксирована в системе частично. Будут либо выполнены все её подоперации, либо не выполнено ни одной.
Многие считают, что лучше соответствует слово Abortability
Consistency — Согласованность
Транзакция, достигающая своего нормального завершения (EOT — end of transaction, завершение транзакции) и тем самым фиксирующая свои результаты, сохраняет согласованность базы данных.
Если данные находились в Good State до начала транзакции то в этом состоянии они должны остаться и после. UK, FK минимальные механизмы базы данных
Согласованность является более широким понятием. Например, в банковской системе может существовать требование равенства суммы, списываемой с одного счёта, сумме, зачисляемой на другой. Это бизнес-правило и оно не может быть гарантировано только проверками целостности, его должны соблюсти программисты при написании кода транзакций. Если какая-либо транзакция произведёт списание, но не произведёт зачисления, то система останется в некорректном состоянии и свойство согласованности будет нарушено.
Isolation — Изолированность
Во время выполнения транзакции параллельные транзакции не должны оказывать влияния на её результат.
Durability — Прочность
Независимо от проблем на нижних уровнях (к примеру, обесточивание системы или сбои в оборудовании) изменения, сделанные успешно завершённой транзакцией, должны остаться сохранёнными после возвращения системы в работу. Другими словами, если пользователь получил подтверждение от системы, что транзакция выполнена, он может быть уверен, что сделанные им изменения не будут отменены из-за какого-либо сбоя.
Уровень изолированности транзакций
Уровень изолированности транзакций — условное значение, определяющее, в какой мере в результате выполнения логически параллельных транзакций в СУБД допускается получение несогласованных данных. Чем выше уровень изолированности, тем ниже скорость. Нужен компромисс.
Проблемы параллельного доступа с использованием транзакций
потерянное обновление (англ. lost update) — при одновременном изменении одного блока данных разными транзакциями теряются все изменения, кроме последнего;
«грязное» чтение (англ. dirty read) — чтение данных, добавленных или изменённых транзакцией, которая впоследствии не подтвердится (откатится);
неповторяющееся чтение (англ. non-repeatable read) — при повторном чтении в рамках одной транзакции ранее прочитанные данные оказываются изменёнными;
фантомное чтение (англ. phantom reads) — одна транзакция в ходе своего выполнения несколько раз выбирает множество строк по одним и тем же критериям. Другая транзакция в интервалах между этими выборками добавляет строки или изменяет столбцы некоторых строк, используемых в критериях выборки первой транзакции, и успешно заканчивается. В результате получится, что одни и те же выборки в первой транзакции дают разные множества строк.
Потерянное обновление
Транзакции на +20 + 25, должны увеличить поле на +45, но увеличивают только на +25(или +20) одно обновление теряется.
«Грязное» чтение
1 транзакция записывает в поле 50(там где раньше было 20)
2 транзакция читает это поле и получает 50.
Происходит откат транзакции 1, в результате 2 транзакция работает с числом 50, а в базе уже 20.
Неповторяющееся чтение
Транзакция 1 читает поле получает 20.
Транзакция 2 меняет 20 -> 30
Транзакция 1 читает опять и получает 30 Т.е в рамках транзакции 1 получили 2 разных значения.
Чтение «фантомов»
Чем - то похоже на неповторяющееся чтение с тем условием, что тут был не изменение данных, а вставка дополнительной строчки.(тяжелее контролировать чем update)
Уровни изоляции
Read uncommitted
Низший (первый) уровень изоляции[1]. Если несколько параллельных транзакций пытаются изменять одну и ту же строку таблицы, то в окончательном варианте строка будет иметь значение, определенное всем набором успешно выполненных транзакций. При этом возможно считывание не только логически несогласованных данных, но и данных, изменения которых ещё не зафиксированы.
Обычно данные блокируются на момент обновления. Избегаем потери потерянного обновления.
Read committed
Большинство СУБД работает в этом режиме.
2 уровня реализации
Блокирование читаемых и изменяемых данных.
Заключается в том, что пишущая транзакция блокирует изменяемые данные для читающих транзакций, работающих на уровне read committed или более высоком, до своего завершения, препятствуя, таким образом, «грязному» чтению, а данные, блокируемые читающей транзакцией, освобождаются сразу после завершения операции SELECT (таким образом, ситуация «неповторяющегося чтения» может возникать на данном уровне изоляции).
Сохранение нескольких версий параллельно изменяемых строк.
При каждом изменении строки СУБД создаёт новую версию этой строки, с которой продолжает работать изменившая данные транзакция, в то время как любой другой «читающей» транзакции возвращается последняя зафиксированная версия. Преимущество такого подхода в том, что он обеспечивает бо́льшую скорость, так как предотвращает блокировки. Однако он требует, по сравнению с первым, существенно бо́льшего расхода оперативной памяти, которая тратится на хранение версий строк. Кроме того, при параллельном изменении данных несколькими транзакциями может создаться ситуация, когда несколько параллельных транзакций произведут несогласованные изменения одних и тех же данных (поскольку блокировки отсутствуют, ничто не помешает это сделать). Тогда та транзакция, которая зафиксируется первой, сохранит свои изменения в основной БД, а остальные параллельные транзакции окажется невозможно зафиксировать (так как это приведёт к потере обновления первой транзакции). Единственное, что может в такой ситуации СУБД — это откатить остальные транзакции и выдать сообщение об ошибке «Запись уже изменена».
Repeatable read (повторяющееся чтение)
*На примере MySQL не увидела вставку строк, не блокировала другую транзакцию. Напоминает Снапшот.
Получается, что у нас хранится много версий одного и того же объекта. Ресурсы!
MVCC - MULTI VERSION CONCURENCY CONTROL
Уровень, при котором читающая транзакция «не видит» изменения данных, которые были ею ранее прочитаны. При этом никакая другая транзакция не может изменять данные, читаемые текущей транзакцией, пока та не окончена.
Блокировки в разделяющем режиме применяются ко всем данным, считываемым любой инструкцией транзакции, и сохраняются до её завершения. Это запрещает другим транзакциям изменять строки, которые были считаны незавершённой транзакцией. Однако другие транзакции могут вставлять новые строки, соответствующие условиям поиска инструкций, содержащихся в текущей транзакции. При повторном запуске инструкции текущей транзакцией будут извлечены новые строки, что приведёт к фантомному чтению. Учитывая то, что разделяющие блокировки сохраняются до завершения транзакции, а не снимаются в конце каждой инструкции, степень параллелизма ниже, чем при уровне изоляции READ COMMITTED. Поэтому пользоваться данным и более высокими уровнями транзакций без необходимости обычно не рекомендуется.
Serializable (упорядочиваемость)
Самый высокий уровень изолированности; транзакции полностью изолируются друг от друга, каждая выполняется так, как будто параллельных транзакций не существует. Только на этом уровне параллельные транзакции не подвержены эффекту «фантомного чтения».
Тут скорее очередь из транзакций, выполняем последовательно.
В Oracle RR = S, a S не существует.
Так, Oracle в принципе не поддерживает нулевой уровень, так как его реализация транзакций исключает «грязные чтения», и формально не позволяет устанавливать уровень Repeatable read, то есть поддерживает только Read committed (по умолчанию) и Serializable. При этом на уровне отдельных команд он, фактически, гарантирует повторяемость чтения (если команда SELECT в первой транзакции выбирает из базы набор строк, и в это время параллельная вторая транзакция изменяет какие-то из этих строк, то результирующий набор, полученный первой транзакцией, будет содержать неизменённые строки, как будто второй транзакции не было). Также Oracle поддерживает так называемые READ-ONLY транзакции, которые соответствуют Serializable, но при этом не могут сами изменять данные.
Что такое реляционная база данных?
Реляционная база данных – это набор данных с предопределенными связями между ними.
Как осуществляется поиск по индексу B-TREE?
Например ищем по имени. Ищем сначала по первой букве, потом по второй, ит.д Но при проходе по индексу происходит не так.
- Сортируем Массив
- Бинарный поиск. Берем центральный элемент отсортированной последовательности. Понимаем, где должна быть 3. слева или справа.
Дальше тоже самое. log2N
Селективность индекса
В составных индексах, лучше начинать с того поля значений, которого меньше.
Чем меньшему количеству строк соответствует значение атрибута - тем выше селективность. Такие атрибуты желательно использовать вначале.
Если нам потребуется сортировка, то ее тоже можно добавить в индекс, но в самый конец.
Типы индексов
B-Tree
Семейство B-Tree индексов — это наиболее часто используемый тип индексов, организованных как сбалансированное дерево, упорядоченных ключей. Они поддерживаются практически всеми СУБД как реляционными, так нереляционными, и практически для всех типов данных.
LOG2(N) разбиваются на ветки идем до элемента сравнением
Пространственные индексы
Spatial grid Spatial grid(пространственная сетка) index – это древовидная структура, подобная B-дереву, но используется для организации доступа к пространственным(Spatial) данным
Quadtree
Quadtree – это подвид Grid-based Spatial index, в котором в родительской ячейке всегда 4 потомка и разрешение сетки варьируется в зависимости от характера или сложности данных.
R-Tree
R-Tree (Regions Tree) – это тоже древовидная структура данных подобная Spatial Grid, предложенная в 1984 году Антонином Гуттманом. Эта структура данных тоже разбивает пространство на множество иерархически вложенных ячеек, но которые, в отличие от Spatial Grid, не обязаны полностью покрывать родительскую ячейку и могут пересекаться.
HASH
Hash-индексы были предложены Артуром Фуллером, и предполагают хранение не самих значений, а их хэшей, благодаря чему уменьшается размер(а, соответственно, и увеличивается скорость их обработки) индексов из больших полей. Таким образом, при запросах с использованием HASH-индексов, сравниваться будут не искомое со значения поля, а хэш от искомого значения с хэшами полей.
Из-за нелинейнойсти хэш-функций данный индекс нельзя сортировать по значению, что приводит к невозможности использования в сравнениях больше/меньше и «is null». Кроме того, так как хэши не уникальны, то для совпадающих хэшей применяются методы разрешения коллизий.
Bitmap
Bitmap index – метод битовых индексов заключается в создании отдельных битовых карт (последовательность 0 и 1) для каждого возможного значения столбца, где каждому биту соответствует строка с индексируемым значением, а его значение равное 1 означает, что запись, соответствующая позиции бита содержит индексируемое значение для данного столбца или свойства.
Reverse index
Reverse index – это тоже B-tree индекс но с реверсированным ключом, используемый в основном для монотонно возрастающих значений(например, автоинкрементный идентификатор) в OLTP системах с целью снятия конкуренции за последний листовой блок индекса, т.к. благодаря переворачиванию значения две соседние записи индекса попадают в разные блоки индекса. Он не может использоваться для диапазонного поиска.
Inverted index
Инвертированный индекс – это полнотекстовый индекс, хранящий для каждого лексемы ключей отсортированный список адресов записей таблицы, которые содержат данный ключ.
Partial index
Partial index — это индекс, построенный на части таблицы, удовлетворяющей определенному условию самого индекса. Данный индекс создан для уменьшения размера индекса.
Function-based index
Самим же гибким типом индексов являются функциональные индексы, то есть индексы, ключи которых хранят результат пользовательских функций.
DENSE_RANK/RANK ФУНКЦИЯ
Oracle/PLSQL функция DENSE_RANK возвращает ранг строки в упорядоченной группе строк. Она очень похожа на функцию RANK. Однако функция RANK может вызвать непоследовательное ранжирование если тестируемые значения одинаковы. Поэтому, функция DENSE_RANK всегда будет приводить к последовательному ранжированию строк.
DENSE_RANK( expression1, … expression_n ) WITHIN GROUP ( ORDER BY expression1, … expression_n )
Важный момент. RANK и DENSE_RANK присуждают одинаковые значения, если встретили одинаковые элементы, но RANK пропускает в этом случае элемент последовательности и если было 2.2 следующим пишет 3.
MAX () / MIN () KEEP (DENSE_RANK LAST / FIRST) функция
MIN(sal) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) AS lowest,
MAX(sal) KEEP (DENSE_RANK LAST ORDER BY sal) OVER (PARTITION BY deptno) AS highest
