Индексы и сравнение скорости

Ускорение операций с помощью индексов

Здравствуйте и вновь добро пожаловать на занятия по теме «SQL для маркетологов».

Возьмём в качестве примера случай, когда я даю вам некоторый список чисел и прошу найти какое-то одно из них, например

[5,2,3,9,7]

Как вам его найти? В общем случае вам не остаётся ничего другого, как просматривать каждое из них по отдельности, пока не найдёте то, которое я просил. Как вы понимаете, это совершенно нереально, если у нас действительно большое количество данных.

Но что, если бы я дал вам упорядоченный список чисел и попросил найти одно из них? Пусть, например, есть список

[1,2,3,4,5,6,7,8,9,10]

и я прошу найти число 9. Посмотрев в середину списка, вы увидите 6. Поскольку 6 меньше 9, вы понимаете, что искать нужно лишь во второй половине списка:

[6,7,8,9,10]

Посмотрев в середину списка ещё раз, мы видим 8. 8 меньше 9, поэтому нам вновь остаётся просмотреть только вторую часть списка:

[9,10]

Остаются лишь 9 и 10, и теперь нам легко найти девятку.

Как видите, размещение объектов в определённом порядке позволяет проводить поиск намного эффективнее.

Индексы и являются специальной структурой данных, позволяющей более эффективно проводить поиск в таблице. Существует несколько типов индексов, используемых в реляционной базе данных. Первый тип – одноколоночный индекс. Он просто облегчает поиск по одному из столбцов, например, по столбцу «Действия». Второй тип – уникальный индекс, облегчающий и ускоряющий поиск по причине своей уникальности, но, очевидно, его нельзя использовать в столбце «Действия», поскольку разные клиенты могут совершать одинаковые действия. И третий тип – составной индекс, который, к примеру, может быть одновременно относиться и к столбцу «Действие», и к столбцу «Стоимость».

Синтаксис создания индексов выглядит следующим образом:

CREATE INDEX название_индекса ON таблица (название_столбца);

CREATE UNIQUE INDEX название_индекса ON таблица (название_столбца);

CREATE INDEX название_индекса ON таблица (столбец1, столбец2);

Существует также специальный тип индексов, называемый первичным ключом. Допустим, у нас есть таблица «Заказы» со столбцами «id», «user_id», «total_price» и таблица «Клиенты» со столбцами «id», «name», «email». «Id» в таблицах «Заказы» и «Клиенты» является тем, что называется первичным ключом. В качестве первичного ключа можно указать и что-либо другое, но так делать не принято. Первичные ключи являются уникальными индексами, но имеющими специальную возможность – способность автоматически увеличивать своё значение. Так, например, если в таблице уже есть клиенты с id 1 и 2, ввод новой строки с данными только в столбцах «name» и «email» автоматически создаст id со значением 3.

Как же создать таблицу с первичным ключом? Синтаксис таков:

CREATE TABLE users(

  id INTEGER PRIMARY KEY AUTOINCREMENT,

  name TEXT,

  email TEXT

);

В данном случае я дал название первичному ключу «id», но вы можете использовать любое, лишь бы оно имело тип INTEGER, хотя, как правило, его называют именно «id». Также мною были использованы одновременно служебные слова PRIMARY KEY и AUTOINCREMENT, что необязательно, но в то же время является общепринятым стандартом.

 

Пример индексов в командной строке

Прежде всего нам понадобится набор данных побольше, чтобы увидеть ощутимую разницу в скорости работы. Я не включал этот файл в репозитарий, поскольку он чересчур велик. А потому давайте выйдем из нашей базы данных с помощью команды

.exit

И запустим файл языка Python с названием generate_actions.py, установив размер таблицы, равный 1 000 000 – вы, если желаете, можете установить ещё большее значение, и назвав файл big_actions.csv:

python generate_actions.py 1000000 big_actions.csv

Теперь запустим SQLite:

sqlite3 the_database.db

Для начала вновь создадим таблицу «users_action», поскольку нам не нужен созданный ранее столбец «created_at». Для этого запустим команды

drop table user_actions;

create table user_actions(name text, product text, action text, price real);

.mode csv

.import big_actions.csv user_actions

Кроме того, запустим команду

.timer on

чтобы мы могли измерить время обработки запроса.

Итак, запустим запрос поиска по имени Джина и отобразим заголовки:

select count(*) from user_actions where name = ‘Gina’;

.header on

Теперь добавим индекс в поле «Имя»:

create index name_index on user_actions (name);

И запустим запрос снова.

Как вы видите, запрос обработался примерно в 10 раз быстрее.

Понравилась статья? Поделить с друзьями:
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: