Оптимизация запросов MySQL — производительные индексы

Индексы (или ключи) — это специальные структуры данных, которые использует подсистемы хранения для ускорения нахождения строк.

• Индексы бесполезны, и, даже, вредны, если данных в таблицы мало (тысячи или меньше строк)

• Если данных много (десятки тысяч строк и больше) и есть запрос 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 для расшифровки