База SQLite и основы SQL - 8. Что такое индексы
15-11-2021Время чтения ~ 5 мин.SQL 3666
Сегодня поговорим про индексы в SQLite. Тема достаточно сложная даже для опытных разработчиков, поскольку встречается много тонкостей и нюансов.
Что такое индексы? Это особый вид данных, которые создаются в дополнение к таблицам для обеспечения более высокой скорости выборки. Индексы имеет смысл использовать в первую очередь для больших данных. В более скромных масштабах, использование индексов даёт уже не такое заметное преимущество.
Чтобы понять как для чего нужны индексы, нужно знать как SQLite выполняет запросы с условиями WHERE.
Например у нас есть таблица:
CREATE TABLE f1( product TEXT, made TEXT, price REAL );
Пусть в ней будут такие данные:
rowid product made price ---------------------------- 1 mouse DE 10 2 monitor KR 200 3 keyboard CH 50 4 hdd CH 150
Поле ROWID есть у каждой таблицы (если она создана, где специально указано, что ROWID не нужен, тогда используется PRIMARY KEY). ROWID нужен для того, чтобы однозначно идентифицировать каждую запись.
Теперь мы выполним запрос:
SELECT price FROM f1 WHERE product = 'keyboard';
Для того, чтобы найти нужные записи, SQLite начнёт перебор всей таблицы и проверит условие для каждой записи. Происходит полное сканирование таблицы. Очевидно, что это достаточно долгий процесс, который напрямую зависит от количества записей в таблице.
Если же мы выполним запрос:
SELECT price FROM f1 WHERE rowid = 3;
то SQLite применит двоичный поиск, что приведёт к ускорению на несколько порядков. Но нам неудобно искать по ROWID, мы хотим искать по человеко понятному product.
Вот здесь как раз индексы и помогают. Для таблицы можно создать индекс для поля product, который будет представлять собой некую таблицу вида:
product rowid --------------- hdd 4 keyboard 3 monitor 2 mouse 1
То есть вначале SQLite обратится к индексу и быстро найдет, что keyboard соответствует rowid = 3. А дальше выполнит поиск по этому rowid в самой таблице.
Вот в этом и есть основное предназначение индексов — обеспечить быстрое нахождение нужного поля.
Разница между ключом и индексом
Ключ — это особый вид индекса, который ориентируется на создание уникальности. По большому счёту, когда упоминают слово «ключ», то почти всегда имеют ввиду первичный ключ (PRIMARY KEY), который в большинстве случаев автоинкремент. Он нужен для того, чтобы иметь возможность однозначно идентифицировать строки.
В SQLite есть автоматически создаваемое поле ROWID, поэтому PRIMARY KEY нужен только для таблиц, к которым будет обращение через ключ.
Создание индексов
Чтобы создать индекс нужно решить будет ли он использоваться в sql-запросах в первую очередь в условии WHERE. Если, например нам не нужно будет использовать условия с полем price, то для него не имеет смысла делать индекс. И это довольно важно, поскольку каждый индекс занимает дополнительное место. Если например сделать индексы для всех полей, то размер базы увеличится в два раза. Для больших баз это будет критичным. Поэтому индексы нужно создавать осознано.
Нюанс ещё в том, что алгоритм использования индексов в выборках несколько сложнее, чем кажется. Во многих запросах используется не равно «=», а условия больше, меньше, что изменяет поведение SQLite. То есть может возникнуть ситуация, когда индекс не работает при определенных условиях во WHERE. Лучше всего проверять это на реальной базе, замеряя время выполнения запроса с индексами и без.
Создать индекс довольно просто. Для этого используется команда CREATE INDEX.
CREATE INDEX idx_product ON f1(product);
В данном примере idx_product — это название индекса. Оно может быть произвольным, но уникальным. Дальше указывается таблица и поле, которое будет проиндексировано.
Удалить индекс можно с помощью DROP INDEX:
DROP INDEX idx_product;
Поскольку создание и удаление индексов достаточно простая операция, то можно экспериментировать с индексами даже на рабочей базе данных.
Индексы могут быть составными, состоящими из нескольких полей. Например если во WHERE часто встречается условия для нескольких полей, то можно создать для них отдельный индекс:
CREATE INDEX idx_product_made ON f1(product, made); product made rowid ---------------------- hdd CH 4 keyboard CH 3 monitor KR 2 mouse DE 1
Тогда запросы вида:
SELECT price FROM f1 WHERE product = 'monitor' AND made = 'KR';
будут выполняться намного быстрей.
Если же сделать индекс для трех полей, то поиск будет ещё быстрей, поскольку SQLite даже не будет обращаться к исходной таблице, ведь все данные уже есть в индексе.
Другой случай, если во WHERE используется не AND, а OR или другие условия. Здесь составной индекс будет бесполезен, поэтому SQLite будет использовать объединение результатов по каждой части условия. Вначале найдет ROWID в индексе первого поля (которое в условии WHERE), потом найдет во втором индексе и только после этого найдет эти записи в исходной таблице и выполнить необходимое соединение.
С этой точки зрения более предпочтительней делать индексы по одному полю, поскольку это покрывает запросы AND, OR (и другие) и не так сильно раздувает базу данных.
Индексы также используются и для сортировки ORDER BY. Если для указанного поля есть индекс, то SQLite посмотрит можно ли его использовать для более быстрого выполнения.
Вообще в SQLite используется планировщик запросов (Query Planning), который автоматически определяет какой алгоритм выполнения запросов будет быстрее. Именно поэтому нам довольно сложно оценить реальный выигрыш от использования индексов. Лучший способ — это профилирование запросов по времени на реальной базе. Тогда можно оперировать реальными цифрами и понять какие индексы необходимы, а какие впустую занимают полезное место.
Уникальность через индекс
Мы уже знаем, что можно задать уникальность значений поля с помощью ключевого слова UNIQUE при создании таблицы. Минус такого подхода в том, что, если эту «уникальность» нужно убрать/добавить, то сделать это будет не так просто. Вместо этого, можно создать индекс, который сделает поле уникальным.
CREATE UNIQUE INDEX index_id ON t1 (id);
Команда CREATE UNIQUE INDEX создаёт индекс и делает указанное поле (поля) уникальными. Поскольку создавать/удалять индексы несложно, то можно добавлять уникальность к любому полю без правки самой таблицы.
Итого
Индексы достаточно сложная штука. На начальном этапе проектирования базы, лучше отработать все поля и их связи, а уже потом подумать о создании индексов. Я намерено опускаю другие возможности SQLite для индексов, поскольку они достаточно специфичные и используются уже на серьёзном уровне.
Использование индексов имеет два недостатка. Первый — увеличение размера базы. Второй — более низкая скорость обновления данных через INSERT или UPDATE. После того, как данные в базе были изменены, SQLite запускает переиндексацию. Поэтому если размер индексов большой, то скорость обновления данных будет низкой. Если в приложении данные часто обновляются, то от индексов пользы будет мало.
Другой случай, когда индексы лучше не использовать, если в базе много значений NULL. Ну и конечно, если база данных небольшая, то ощутить реальную разницу от использования индексов будет проблематично. Если счёт на десятые и сотые доли секунды, то индексы просто не имеют смысла. Алгоритмы SQLite достаточно сообразительны, чтобы обеспечить быструю работу базы даже без индексов.