Какой индекс используется для поиска приблизительных значений?

Partial Match и LIKE Поиск в SQL

Для поиска приблизительных (нечетких) значений в SQL традиционно используется оператор LIKE с wildcard-символами, но индексы по умолчанию часто не работают оптимально с такими запросами. Правильный подход зависит от конкретного СУБД и паттерна поиска.

Основные подходы к индексированию LIKE запросов

1. B-Tree индексы с определенными паттернами

B-Tree индексы могут использоваться для LIKE, но с важными ограничениями:

-- Индекс МОЖЕТ использоваться (префиксный поиск)
SELECT * FROM users WHERE name LIKE 'Joh%';

-- Индекс НЕ будет использоваться (постфиксный/инфиксный поиск)
SELECT * FROM users WHERE name LIKE '%ohn%';
SELECT * FROM users WHERE name LIKE '%son';

Для префиксного поиска обычный B-Tree индекс работает эффективно, так как данные в индексе отсортированы.

2. Reverse Index для постфиксного поиска

Для поиска по окончанию строк можно создать индекс на reversed строку:

-- Создаем колонку и индекс для reversed строки
ALTER TABLE products ADD COLUMN name_reverse VARCHAR(255);
UPDATE products SET name_reverse = REVERSE(name);
CREATE INDEX idx_reverse_name ON products(name_reverse);

-- Поиск по окончанию через reversed индекс
SELECT * FROM products 
WHERE REVERSE(name) LIKE REVERSE('%apple');
-- Это преобразуется в: WHERE name_reverse LIKE 'elppa%'

3. Full-Text Search индексы

Для сложного нечеткого поиска лучше использовать полнотекстовые индексы:

-- Создание полнотекстового индекса (MySQL)
CREATE FULLTEXT INDEX ft_idx_name ON products(description);

-- Поиск с различными операторами
SELECT * FROM products 
WHERE MATCH(description) AGAINST('apple*' IN BOOLEAN MODE);

Специализированные индексы в разных СУБД

PostgreSQL: GIN/GIST триграммные индексы

-- Включение расширения
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Создание триграммного индекса
CREATE INDEX trgm_idx_name ON users USING GIN(name gin_trgm_ops);

-- Поиск с различными паттернами
SELECT * FROM users WHERE name LIKE '%ohn%';
SELECT * FROM users WHERE name ILIKE '%SMITH%'; -- case-insensitive

Триграммные индексы работают для любых позиций wildcard'ов и поддерживают similarity поиск через операторы % и <->.

MySQL: полнотекстовые индексы

-- Только для InnoDB/MyISAM, минимальная длина слова
CREATE FULLTEXT INDEX ft_index ON documents(content);

-- Natural language search
SELECT * FROM documents 
WHERE MATCH(content) AGAINST('database optimization');

-- Boolean mode с префиксным поиском
SELECT * FROM documents 
WHERE MATCH(content) AGAINST('+data* +engin*' IN BOOLEAN MODE);

SQL Server: полнотекстовые и filtered индексы

-- Full-text catalog
CREATE FULLTEXT INDEX ON products(name)
KEY INDEX pk_products
ON FTCatalog;

-- Filtered index для конкретного паттерна
CREATE INDEX idx_specific_pattern ON customers(email)
WHERE email LIKE 'info@%.com';

Дополнительные техники оптимизации

Функциональные индексы

-- PostgreSQL
CREATE INDEX idx_lower_name ON users(LOWER(name));
SELECT * FROM users WHERE LOWER(name) LIKE '%smith%';

-- MySQL 8.0+
CREATE INDEX idx_functional ON users((LOWER(name)));

Covering индексы для уменьшения IO

CREATE INDEX idx_covering ON employees(last_name, first_name, department_id)
WHERE last_name LIKE 'A%';

-- Запрос использует только индекс
SELECT last_name, first_name 
FROM employees 
WHERE last_name LIKE 'A%';

Практические рекомендации

1. Анализируйте паттерны запросов:

   • Префиксные поиски (LIKE 'abc%') → обычный B-Tree
   • Инфиксные/постфиксные поиски (LIKE '%abc%') → триграммные/обратные индексы
   • Слово-ориентированный поиск → полнотекстовые индексы

2. Учитывайте селективность:

   -- Низкая селективность - возможно, лучше table scan
   SELECT * FROM logs WHERE message LIKE '%error%';
   
   -- Высокая селективность - индекс эффективен
   SELECT * FROM users WHERE email LIKE 'john.smith%@company.com';
   

3. Комбинируйте подходы:

   -- Multi-column индекс для частых сценариев
   CREATE INDEX idx_name_department ON employees(
       last_name varchar_pattern_ops, 
       department_id
   ) WHERE active = true;
   

4. Используйте материализованные представления для сложных поисков:

   CREATE MATERIALIZED VIEW search_view AS
   SELECT id, name, setweight(to_tsvector(name), 'A') AS ts_vector
   FROM products;
   
   CREATE INDEX idx_search_gin ON search_view USING GIN(ts_vector);
   

Выбор правильного индекса для approximate search требует анализа:

• Объема данных и паттернов доступа
• Распределения данных в колонке
• Требований к производительности (read vs write)
• Возможностей конкретной СУБД

Для большинства современных приложений с нечетким поиском рекомендую использовать триграммные индексы в PostgreSQL или full-text search в MySQL/SQL Server, так как они обеспечивают лучшую гибкость и производительность для различных паттернов LIKE запросов.