Индексирование SQL на varchar

У меня есть таблица с столбцами varchar(50) и float. Мне нужно (очень быстро) посмотреть получить float, связанный с данной строкой. Даже при индексировании это довольно медленно.

Я знаю, однако, что каждая строка связана с целым числом, которое я знаю во время поиска, так что каждая строка сопоставляется с уникальным целым числом, но каждое целое не сопоставляется с уникальной строкой. Можно подумать об этом как о древовидной структуре.

Есть ли что-нибудь, что можно получить, добавив это целое число в таблицу, проиндексировав его и используя такой запрос, как:

SELECT floatval FROM mytable WHERE phrase=givenstring AND assoc=givenint

Это Postgres, и если вы не можете сказать, у меня очень мало опыта работы с базами данных.

Ответы

Ответ 1

Ключи в столбцах VARCHAR могут быть очень длинными, что приводит к меньшему количеству записей на странице и большей глубине (больше уровней в B-Tree). Более длинные индексы также увеличивают коэффициент пропуска кеша.

Сколько строк в средней карте для каждого целого?

Если относительно немного, вы можете создать индекс только для целочисленного столбца, а 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 байтов, поэтому индексные клавиши будут намного короче, сохраняя при этом почти полную селективность.

Ответ 2

Я бы рекомендовал просто хэш-индекс:

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)

Ответ 3

Короткий ответ: да, будет много, чтобы выиграть. По крайней мере, пока у вас мало обновлений, но вполне вероятно, что накладные расходы даже не будут заметны.

Ответ 4

Объявив индекс на (phrase, assoc, floatval), вы получите "индекс покрытия", который позволяет выполнить запрос, поставленный в вопросе, даже не обращаясь к таблице. Предполагая, что только один из phrase или assoc очень избирателен (не так много строк имеют одно и то же значение для поля), создание индекса только в этом поле должно давать почти такую же производительность.

Как правило, вам нужно ограничить количество индексов наименьшим набором, которое будет получать ваши частые запросы до желаемой производительности. Для каждого индекса, который вы добавляете в таблицу, вы платите некоторое дисковое пространство, но, что более важно, вы платите цену за то, что СУБД больше работают с каждым INSERT в таблице.

Ответ 5

Не помешает попробовать добавить int и сделать ваш индекс на int, varchar и include float - это будет охватывать и довольно эффективно - не уверен, что Postgres включил столбцы - если он не просто добавляет его в самого индекса.

Есть несколько других методов, которые вы могли бы изучить (я не знаком со всеми функциями Postgres, поэтому я дам их по имени SQL Server):

Индексированные представления - вы можете эффективно материализовать представление, которое объединяет несколько таблиц, чтобы вы могли присоединиться к вашему varchar к вашему int и иметь свой индекс в int и varchar и float

Включенные столбцы - вы можете включать столбцы в индекс, чтобы гарантировать, что индекс покрывает - то есть иметь индекс на varchar include (float) - если ваш индекс не покрывает, оптимизатору запросов все равно придется использовать индекс, а затем выполнить поиск по закладкам, чтобы получить оставшиеся данные.