sql Идексы: зачем они нужны и какими бывают. Внутренняя структура индекса

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

Все индексы можно разделить во основномм на такие группы:

• Primary Key
• Index B-tree
• Unique
• Hash
• Full Text

hash - если мы делаем выборку по текстовому полю, например по комментарию к товару, мы можем написать Hash Index Md5 и какая длина бы комментария бы не была, мы делаем сопостваление именно с 64 сивволами этой строки

Как создается индекс?

CREATE INDEX name_index ON PRODUCTS (row_index)

name_index - название индекса, row_index - столбец таблицы для которого делаем индекс

Пример:

CREATE TABLE CUSTOMERS(
  ID   INT              NOT NULL,
  NAME VARCHAR (20)     NOT NULL,
  AGE  INT              NOT NULL,
  ADDRESS  CHAR (25) ,
  SALARY   DECIMAL (18, 2)
);
SHOW INDEX FROM CUSTOMERS;

Запустите этот код и никаких индексов показано не будет.
Теперь допишем строчку PRIMARY KEY (ID):

 CREATE TABLE CUSTOMERS(
  ID   INT              NOT NULL,
  NAME VARCHAR (20)     NOT NULL,
  AGE  INT              NOT NULL,
  ADDRESS  CHAR (25) ,
  SALARY   DECIMAL (18, 2),       
  PRIMARY KEY (ID)
);
SHOW INDEX FROM CUSTOMERS;

Вы увидите множество свойств, среди которых есть кардианальность (cardinality) обозначающее уникальность столбцов. Если все значения разные то функция кардинальности максимальна, если минимальна, то много значений повторяются). Например, B-tree это способ выборки данных (подробнее: https://ru.stackoverflow.com/questions/2...) который уменьшает количество итерации выборки. Например сервер прозодит по попрядку не каждую строчку таблицы, а каждые три строчки таблицы находятся в определенном условном блоке, а данные между этими числами в другом блоке и получается вложенная древовидность.
Пример, есть таблица физических лиц, в которой имеются следующие столбцы:

id (физлица), ФИО, Телефонный номер, Регион прописки

В данной таблице считаем, что id сделан первичным ключом. Первичный ключ - это индекс, по которому сервер MySQL сразу сортирует по порядку строки с данными физлиц. Первичный ключ - это пример логического индексирования. А что если нам необходимо отсортировать не по id физлица, а по региону прописки? Тогда надо ввести логически дополнительный столбец индексов по регионам проживания и отсортировать его по алфавиту. То есть мы особым образом, с помощью дополнительного столбца структурируем данные.
C точки зрения производительности выборка, сортировка, поиск элементов по индексу увеличивает скорость выполнения этих действий в разы в отличие от стандартного способа с применением оператора WHERE. Например, представим, что количество юзеров в таблице users несколько десятков миллионов, если мы напишем [1]:

SELECT FROM users WHERE name ="Abdul" ; 

То MySQL сервер может не только выполнять выборку очень долго или недопустимо долго,
а если параллельных запросов (конкурентных запросов) к серверу базы данных несколько, то эти запросы вообще могут сломать сервер, так как серверу придется сравнивать на прдемет равенства каждую строчку в таблице со строкой "Abdul". Индексы используются для логической сортировки данных. Индексирование необходимо для быстрой ориентации по базе данных.Для одной небольшой таблицы (

Заполним таблицу:

CREATE TABLE users(
  id   INT              NOT NULL,
  name VARCHAR (250)     NOT NULL,
  email VARCHAR (250)     NOT NULL
  phone VARCHAR (250)     NOT NULL      
  PRIMARY KEY (id)
);
insert into CUSTOMERS(ID, NAME, AGE, ADDRESS,SALARY) values(
1, "Mike","mike[dog]mail.ru",546546);
insert into CUSTOMERS(ID, NAME, AGE, ADDRESS,SALARY) values(
2, "Jhon","mike[dog]mail.ru",546546);
insert into CUSTOMERS(ID, NAME, AGE, ADDRESS,SALARY) values(
3, "Fill","mike[dog]mail.ru",546546);
insert into CUSTOMERS(ID, NAME, AGE, ADDRESS,SALARY) values(
4, "Sergei","mike[dog]mail.ru",546546);
insert into CUSTOMERS(ID, NAME, AGE, ADDRESS,SALARY) values(
5, "White","mike[dog]mail.ru",546546);

Вставим с помощью конструкции ниже код (запускать вручную несколько раз):

INSERT INTO users (name,email,phone)
INSERT INTO users (name,email, phone)
SELECT
CONCAT (name, lpad(conv(floor(rand()*pow(36,6)),10,36),6,0) as name,
email,
CONCAT(LEFT(phone,8),ROUND(RAND()*100)) as phone
FROM
users

Сервер обходит гораздо быстрей эти блоки.

CREATE TABLE CUSTOMERS(
  ID   INT              NOT NULL,
  NAME VARCHAR (20)     NOT NULL,
  AGE  INT              NOT NULL,
  ADDRESS  CHAR (25) ,
  SALARY   DECIMAL (18, 2),       
  PRIMARY KEY (ID)
);

insert into CUSTOMERS(ID, NAME, AGE, ADDRESS,SALARY) values(
1, "Mike",25,"Green street",20000);

insert into CUSTOMERS(ID, NAME, AGE, ADDRESS,SALARY) values(
2, "Jhon",28,"Yellow street",25000);

insert into CUSTOMERS(ID, NAME, AGE, ADDRESS,SALARY) values(
3, "Jaison",21,"Red street",16000);

insert into CUSTOMERS(ID, NAME, AGE, ADDRESS,SALARY) values(
4, "White",23,"Blue street",18000);

CREATE INDEX ADDRESS_INDEX ON  CUSTOMERS (ADDRESS) USING BTREE;

SHOW INDEX FROM CUSTOMERS;

Для демонстрации времени запроса используем встроенный профайлер Mysql :

set profiling=1;
select count(*) from comment;
select count(*) from message;
show profiles;

Запустить код: https://paiza.io/en/languages/mysql

1 Бен Форта, "SQL, 10 минут на урок", страница 226.
2 Ссылка
3 https://www.youtube.com/watch?v=JgcI7BOlwjs