Программирование и безопасность баз данных мобильных систем

SQLite
Индексы

Индекс
Что такое индекс?
Каким образом применяется?
В каких случаях создается автоматически?

Индекс
Что получаем при добавлении индекса?
Чем за это платим?
Для каких объектов можно создать индекс?
Для каких столбцов создаем индекс?

Индекс
Что такое простой индекс?
Что такое составной индекс?
Что такое уникальный / не уникальный индекс?
Какой порядок значений (ASC, DESC)?
Что такое индекс покрытия?
Что такое фильтрующий индекс?

Индекс
Интеграция индекса с таблицей:
кластеризованные
некластеризованные

Эффективность индекса
Эффективность индекса оценивает оптимизатор запросов, основываясь на характеристиках запроса:
Селективность
Плотность
Распределение значений

Селективность
Селективность – отношение числа выбираемых записей к общему числу записей
Чем выше селективность – чем больше записей выбирается – тем хуже

Плотность
Плотность – отношение числа дубликатов значений к общему числу значений
Лучшая плотность – уникальные значения

Распределение значений
Распределение значений – показатель, как значения ключей индекса распределены по всему интервалу возможных значений
Больше слов на букву А, чем на букву Й

Индекс
Структура индекса:
деревья (tree-index)
частичные
функциональные
двоичные таблицы (bitmap, Oracle)
пространственные индексы (spatial)
полнотекстовые индексы (full text)
XML-индексы
колоночные индексы (для OLAP-приложений)

B-tree индекс

B-tree кластеризованный индекс

B-tree некластеризованный индекс

Битовые индексы в Oracle
Битовый индекс создает битовые карты для каждого возможного значения столбца, где каждому биту соответствует строка, а значение бита 1 (0) означает, что соответствующая строка содержит (не содержит) индексируемое значение
Предназначен для индексирования столбцов с набором значений
Не подходит для таблиц с частым обновлением
Хорошо подходят для хранилищ данных

Пространственные индексы MS SQL Server

Полнотекстовые индексы MS SQL Server

Полнотекстовые индексы MS SQL Server
Средства разбиения по словам и парадигматические модули
Списки стоп-слов
Файлы тезауруса
Фильтры

XML индексы в MS SQL Server
Первичный XML-индекс:
Индексируются все теги, значения и пути
Используется для возвращения скалярных значений или поддеревьев
Вторичные XML-индексы:
FOR PATH —по структуре документа
FOR VALUE —по значениям элементов и атрибутов столбца XML
FOR PROPERTY —для поиска по свойствам
Не могут быть составными
Не могут быть кластеризованными

Индексы XML в MS SQL Server

Колоночные индексы в MS SQL Server
Введены с версии 2012
Данные хранятся по столбцам
В индекс включаются столбцы, по которым будет производиться поиск
Позволяют получить значительный выигрыш в производительности для больших массивов данных

Индекс
Что такое фрагментация индекса?
В чем заключается обслуживание индекса?

Индекс
Как принимается решение о применении индекса?
Каковы критерии принятия решения?
Что такое hints?

Поиск и сортировка в запросах в SQLite
Полный скан таблицы
Поиск по RowId
Поиск по индексу (поиск RowId, переход по RowId)
Поиск по нескольким условиям (И)
Поиск по нескольким условиям (ИЛИ)
Покрывающий индекс (включены все поля поиска)

Поиск и сортировка в запросах в SQLite
Полный скан таблицы

Поиск и сортировка в запросах в SQLite
Поиск по RowId

Поиск и сортировка в запросах в SQLite
Поиск по индексу (поиск RowId, переход по RowId)

Поиск и сортировка в запросах в SQLite
Поиск по индексу (поиск RowId, переход по RowId)

Поиск и сортировка в запросах в SQLite
Поиск по нескольким условиям (И)

Поиск и сортировка в запросах в SQLite
Поиск по нескольким условиям (И)

Поиск и сортировка в запросах в SQLite
Покрывающий индекс (включены все поля поиска)

Поиск и сортировка в запросах в SQLite
Поиск по нескольким условиям (ИЛИ)

Создание индекса

Пересоздание и удаление индекса

Именование индекса
Стандартный префикс idx_
Имя таблицы
Имя столбца (столбцов)

Создание индекса
Индекс строится на столбцах одной таблицы
На представлении индекс построить нельзя
На виртуальной таблице индекс построить нельзя
Нет ограничений на количество индексов для одной таблицы
Количество столбцов в индексе ограничено
Если используется ключевое слово UNIQUE дублирование записей индекса не допускается

Автоматическое создание индекса
При указании UNIQUE и PRIMARY KEY
Не могут быть удалены DROP INDEX
Показаны в sqlite_master
Индекс по RowID не показывается в sqlite_master

Автоматическое создание индекса

Индекс на представление

Индексы WHERE
Частичный индекс - это индекс над подмножеством строк таблицы
Например, частичный индекс может опускать записи, для которых индексируемый столбец NULL
Назначение - уменьшение файла базы данных

Индексы WHERE
Если в индексе используется OR, то в запросе, использующем индекс, может быть указано одно из условий
Если в индексе используется точное равенство (=a), то запрос будет использовать индекс при таком же условии (between a and a – не подойдет)
Если в индексе указано IS NOT NULL, то в запросе, использующем <> индекс не применяется

Составные индексы
Используются для ускорения выполнения запросов по нескольким полям
Порядок расположения столбцов важен!

Индексы

Индексы с указанием порядка сортировки
Указание порядка хранения значений ключей оправдано для запроса с предложением ORDER BY

Индексы
COLLATE определяет последовательность сортировки для текстовых записей

Индексы в выражениях
В индексе можно использовать выражение от столбца/набора столбцов

Индексы в выражениях
Выражения в индексе не могут ссылаться на другие таблицы и использовать подзапросы и функции, результат которых может измениться

Оптимизация запросов
Используются только для оперативного анализа
ANALYZE
EXPLAIN
EXPLAIN QUERY PLAN

ANALYZE
Сбор статистики о таблицах и индексах
Хранится в sqlite_stat1
Оптимизатор запросов может получить доступ к информации и использовать ее, чтобы помочь улучшить выбор планирования запросов

ANALYZE

EXPLAIN
Пошаговое исполнение оператора
Оператор при этом не выполняется

EXPLAIN QUERY PLAN
Показывает, каким образом будет проводится поиск в таблице

PRAGMA
INDEX_INFO (indexname) – возвращает одну строку для каждого столбца ключа в названном индексе
INDEX_LIST (tablename) – возвращает одну строку для каждого индекса, связанного с данной таблицей
INDEX_XINFO (indexname) – возвращает информацию о каждом столбце в индексе

PRAGMA

Итого
Что такое индекс?
Каким образом применяется?
В каких случаях создается автоматически?
Что такое плотность и селективность запроса?
Что получаем при добавлении индекса?
Плюсы и минусы индекса?
Для каких столбцов создаем индекс?
Как обслуживать индексы?

Индекс
Что такое простой индекс?
Что такое составной индекс?
Что такое уникальный / не уникальный индекс?
Какой порядок значений (ASC, DESC)?
Что такое индекс покрытия?
Что такое фильтрующий индекс?
Что такое кластеризованный / некластеризованный индекс?
Какие виды индексов есть в SQLite?