Индексация URL

Наверное те, кто читал пост Брета Тейлора “Как FriendFeed использует MySQL для хранения schema-less данных” (перевод на Хабре) могли обратить внимание на пример создания индексной таблицы для хранения ссылок.

CREATE TABLE index_link (
    link VARCHAR(735) NOT NULL,
    entity_id BINARY(16) NOT NULL UNIQUE,
    PRIMARY KEY (link, entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Как видно из примера, URL индексируются в виде строк. Но так ли эффективно хранить URL в чистом виде?


Не стану привязываться к конкретной реализации FriendFeed и рассмотрю тип данных URL в общем. В спецификации RFC2616 нет четкого определения длины URL, наверное поэтому в разных броузерах длина URL отличается и может варьироваться от 2000 до 100 000 символов. Обычно принято, что URL длиной более 2000 символов скорее всего ошибочны. На практике, использование строки длиной даже в 1000 символов в качестве индекса не всегда может быть целесообразно. Например, для случаев, когда вам не нужно осуществлять поиск фрагмента URL, не нужно извлекать из него какие-либо данные, части. Когда просто важно, что URL уникальны в базе данных. И все что необходимо, так это выполнить поиск по полному URL.

Для решения данного вопроса можно воспользоваться функциями вычисления md5 или sha1 хешей для URL. В случае использования md5 функции, индексная таблица будет иметь вид

CREATE TABLE index_link (
    link BINARY(16) NOT NULL,
    entity_id BINARY(16) NOT NULL UNIQUE,
    PRIMARY KEY (link, entity_id)
) ENGINE=InnoDB;

Для sha1 функции

CREATE TABLE index_link (
    link BINARY(20) NOT NULL,
    entity_id BINARY(16) NOT NULL UNIQUE,
    PRIMARY KEY (link, entity_id)
) ENGINE=InnoDB;

При индексировании данных поля link, вычисляется md5 или sha1 функция URL и данные записываются в индексную таблицу в виде последовательности байт с помощью конструкции UNHEX(MD5(link)) или UNHEX(SHA1(link)). Данный подход позволяет легко выполнить условие уникальности URL, а также осуществить поиск по полным URL. Для этого, для искомого значения URL вычисляется хеш функция и уже по ней осуществляется поиск.