Индексация SQL на varchar
У меня есть таблица, столбцы которой varchar(50) и a float. Мне нужно к (очень быстро), взгляд получает плавание, связанное с данной строкой. Даже с индексацией, это довольно медленно.
Я знаю, однако, что каждая строка связана с целым числом, которое я знаю во время поиска, так, чтобы каждая строка отобразилась на уникальное целое число, но каждое целое число не отображается на уникальную строку. Можно было бы думать о нем как о древовидной структуре.
Есть ли что-либо, чтобы быть полученным путем добавления этого целого числа к таблице, индексации на нем и использования запроса как:
SELECT floatval FROM mytable WHERE phrase=givenstring AND assoc=givenint
Это - Пост-ГРЭС, и если Вы не могли бы сказать, у меня есть очень мало опыта с базами данных.
5 ответов
Ключи в столбцах VARCHAR могут быть очень длинными, что приводит к меньшему количеству записей на страницу и большей глубине (больше уровней в B-дереве ). Более длинные индексы также увеличивают коэффициент промахов кэша.
Сколько строк в среднем соответствует каждому целому числу?
Если их относительно мало, вы можете создать индекс только для целочисленного столбца, а PostgreSQL выполнит тонкую фильтрацию записей:
CREATE INDEX ix_mytable_assoc ON mytable (assoc);
SELECT floatval
FROM mytable
WHERE assoc = givenint
AND phrase = givenstring
Вы также можете рассмотреть возможность создания индекса для строковых хэшей:
CREATE INDEX ix_mytable_md5 ON mytable (DECODE(MD5(phrase), 'HEX'));
SELECT floatval
FROM mytable
WHERE DECODE(MD5(phrase), 'HEX') = DECODE(MD5('givenstring'), 'HEX')
AND phrase = givenstring -- who knows when do we get a collision?
Каждый хэш имеет длину только 16 байт, поэтому ключи индекса будут намного короче, при этом почти полностью сохраняя избирательность.
Объявив index on (фраза, assoc, floatval) вы получите «покрывающий индекс», который позволяет выполнить запрос, размещенный в вопросе, даже без доступа к таблице. Предполагая, что одна только фраза или assoc очень избирательна (не многие строки имеют одно и то же значение для поля), создание индекса только для этого поля должно дать почти такую же производительность.
Как правило, вам нужно ограничить количество индексов до наименьшего набора, который позволяет вашим частым запросам достичь желаемой производительности. Для каждого индекса, добавляемого в таблицу, вы платите некоторое дисковое пространство, но, что более важно, вы платите цену за то, что СУБД выполняет больше работы над каждым INSERT в таблицу.
Краткий ответ: да, многое можно будет выиграть. По крайней мере, до тех пор, пока у вас не так много обновлений, но вполне вероятно, что накладные расходы даже там не будут заметны.
Не помешает попробовать добавить int и сделать индекс на int, varchar и включить float - это будет покрывать и довольно эффективно - не уверен, что Postgres имеет включаемые столбцы - если нет, просто добавьте их в сам индекс.
Есть несколько других методов, которые вы можете рассмотреть (я не знаком со всеми возможностями Postgres, поэтому буду называть их по имени SQL Server):
Индексированные представления - вы можете эффективно материализовать представление, которое соединяет несколько таблиц - таким образом, вы можете соединить varchar с int и иметь индекс на int, varchar и float
Включенные столбцы - вы можете включить столбцы в индекс, чтобы убедиться, что индекс охватывает - т.е., например, иметь индекс на varchar и int. Например, индекс на varchar включает (float) - если индекс не покрывает, оптимизатору запроса все равно придется использовать индекс, а затем выполнять поиск по закладкам, чтобы получить оставшиеся данные.
Я бы рекомендовал просто хэш-индекс:
create index mytable_phrase_idx on mytable using hash(phrase);
Таким образом, запросы вроде
select floatval from mytable where phrase='foo bar';
будет очень быстро. Проверьте это:
create temporary table test ( k varchar(50), v float);
insert into test (k, v) select 'foo bar number '||generate_series(1,1000000), 1;
create index test_k_idx on test using hash (k);
analyze test;
explain analyze select v from test where k='foo bar number 634652';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Index Scan using test_k_idx on test (cost=0.00..8.45 rows=1 width=8) (actual time=0.201..0.206 rows=1 loops=1)
Index Cond: ((k)::text = 'foo bar number 634652'::text)
Total runtime: 0.265 ms
(3 rows)