Индексы (или ключи) — это специальные структуры данных, которые использует подсистемы хранения для ускорения нахождения строк.
- Индексы бесполезны, и, даже, вредны, если данных в таблицы мало (тысячи или меньше строк)
- Если данных много (десятки тысяч строк и больше) и есть запрос WHERE по одному столбцу (или же сортировка ORDER BY по этому столбцу), который выполняется часто, лучше «повесить» на него простой индекс
- Индекс хорошо работают по полному значению, диапазону значений
- Составной индекс включает набор индексов (например, по трем столбцам A + B + C)
- В это случае, индекс хорошо работает по полному набору проиндексированных столбцов («A + B + C»), по префиксам (левой части индекса: «A» или «A + B») и НЕ работает по пост-фиксам (правой части индекса: «C» или «B + C»). В случае необходимости поиска по «B» или «B + C», можно создать отдельный индекс — «B + C». Опять таки, индекс «B + C» будет неэффективен при поиске по C.
- Альтернативой при существующем индексе «A + B + C» и необходимости поиска по пост-фиксу индекса, может быть включение в поиск столбца A искусственным образом:
SELECT * FROM TABLE WHERE B=2 AND C=3; -- преобразуем в: SELECT * FROM TABLE WHERE A="2022-10-14" AND B=2 AND C=3;
- Так же, необходимо рассмотреть возможность смены порядка следования столбцов в запросе WHERE. Если, имеем индекс «A + B + C», при этом наш запрос выглядит так:
-- было: SELECT * FROM TABLE WHERE A = 1 AND C = 2 AND B = 3; -- стало: SELECT * FROM TABLE WHERE A = 1 AND B = 3 AND C = 2;
- Нужно понимать, что, если в запросе есть поиск по одному столбцу и сортировка по другому, хорошо подойдет составно индекс по этим столбцам и плохо будет работать отдельный индекс по одному из столбцов
- В случае индексов на столбец строкового типа, индекс хорошо работает по полному совпадению и при поиске по префиксу (WHERE column LIKE «abc%»). При это у индекса есть ограничения, и по длинной строке поиск будет работать плохо
- Чем меньше памяти потребляет тип столбца, тем быстрее работает и индекс. Поэтому поиск по индексированному целочисленному столбцу гораздо быстрее, чем по строковому
- В случае длинных строк, и необходимости поиска по ним, можно ввести индексируемый столбец с хешем по длинной строке.
Хороший пример данных, это столбец с адресом веб-страниц url и поддержка столбца c хешем этой строки — url_hash. При сохранение данных, записываем в столбец url оригинальный адрес веб-страницы, а в url_hash — хеш строки этого адреса (например кодировкой CRC32 или другой кодировкой в цифровое значение). А при поиске ведем поиск только по индексированному url_hash.
-- долгий запрос:
SELECT * FROM TABLE WHERE url = "https://long-adress-url.com";
-- заменяем на:
SELECT * FROM TABLE WHERE url_hash = CRC32("https://long-adress-url.com");
- Другой частый случай плохо-индексируемых данных — это IP-адреса. Для них, так же есть специальный функции на бекенде, которые превращают строку IP-адреса в числовой тип для отдельного индексируемого столбца.
Конкретные функции зависят от языка программирования. Например, для PHP подойдет функция ip2long для шифровки и long2ip для расшифровки