Использование MD5 (URL) вместо URL в БД для WHERE

У меня есть большая таблица MySQL InnoDB (около 1 миллиона записей, увеличивающаяся на 300 тысяч в неделю), скажем, с сообщениями в блогах. Эта таблица имеет поле URL с индексом.

Добавляя в него новые записи, я проверяю наличие существующих записей с тем же URL-адресом. Вот как выглядит запрос:

SELECT COUNT(*) FROM `tablename` WHERE url='http://www.google.com/';

В настоящее время система производит около 10-20 запросов в секунду, и это количество будет увеличено. Я думаю о повышении производительности, добавив дополнительное поле, которое представляет собой хеш MD5 URL-адреса.

SELECT COUNT(*) FROM `tablename` WHERE md5url=MD5('http://www.google.com/');

Таким образом, он будет короче и с постоянной длиной, что лучше для индекса по сравнению с полем URL. Что вы, ребята, думаете об этом. Имеет ли это смысл?

Другое предложение моего друга - использовать CRC32 вместо MD5, но я не уверен, насколько уникальным будет результат CRC32. Дайте мне знать, что вы думаете о CRC32 для этой роли.

ОБНОВЛЕНИЕ: столбец URL уникален для каждой строки.


mysql sql performance innodb
person Vladimir Prudnikov    schedule 08.09.2009    source источник

Ответы (7)


arrow_upward
4
arrow_downward

Создайте некластеризованный индекс по URL. Это позволит вашему движку SQL выполнить всю внутреннюю оптимизацию и даст наилучшие результаты!

Если вы создадите индекс для столбца VARCHAR, SQL все равно создаст внутренний хэш, и использование индекса может повысить производительность на порядок или даже больше!

Кроме того, если вы только проверяете, существует ли URL-адрес, следует помнить, что некоторые продукты SQL будут давать более быстрые результаты с таким запросом:

IF NOT EXISTS(SELECT * FROM `tablename` WHERE url='')
    -- return TRUE or do your logic here
person Mike Dinescu    schedule 08.09.2009
comment
Я думал, что некластеризованный - это терминология SQL Server - разве это не должно читаться как просто индекс? - person OMG Ponies; 08.09.2009
comment
некластеризованные индексы — это виртуальные индексы данных, тогда как кластеризованные индексы — это физические индексы данных. У вас может быть только один кластеризованный индекс на таблицу, в то время как у вас может быть несколько некластеризованных индексов в одной таблице. - person Mike Dinescu; 08.09.2009
comment
Согласитесь, индекс NC будет иметь такую ​​​​же или аналогичную производительность, что и добавление MD5 или другого хэша. Если у вас высокое соотношение записей имен таблиц на URL-адрес, я бы рассмотрел структуру из двух таблиц, в которой уникальные URL-адреса сохраняются, скажем, в tblUrls, а имя таблицы будет хранить только соответствующий ключ. Это может немного повысить производительность вставки, но также снизить требования к хранилищу и дать несколько других преимуществ, в зависимости от базового приложения. - person mjv; 08.09.2009
comment
Вот статья, в которой рассказывается о производительности кластеризованных и некластеризованных индексов для таблиц MySQL InnoDB: dbscience.blogspot.com/2008/02/ - person Mike Dinescu; 08.09.2009

arrow_upward
0
arrow_downward

Я думаю, что CRC32 на самом деле лучше подходит для этой роли, так как он короче и экономит больше места в SQL. Если вы получаете так много запросов, цель в любом случае состоит в том, чтобы сэкономить место? Если это делает работу, я бы сказал, пойти на это.

Хотя, поскольку он всего 32-битный и короче по длине, он, конечно, не так уникален, как MD5. Вам придется решить, хотите ли вы уникальности или хотите сэкономить место.

Я все еще думаю, что выбрал бы CRC32.

Моя система генерирует примерно 4 тыс. запросов в секунду, и я использую CRC32 для ссылок.

person homework    schedule 08.09.2009
comment
Вы всегда можете сохранить полный URL-адрес в отдельном столбце и попросить MySQL сравнить оба: один и тот же CRC32 и один и тот же полный URL-адрес. - person too much php; 09.09.2009

arrow_upward
0
arrow_downward

Использование встроенной индексации всегда лучше, или вы все равно должны добровольно добавить в их кодовую базу;)

При использовании хеша создайте индекс из двух столбцов для хэша и URL-адреса. Если вы выберете только первые пару букв в индексе, он все равно будет иметь полное совпадение, но он не будет индексировать больше, чем первые несколько букв.

Что-то вроде этого:

INDEX(CRC32_col, URL_col(5))

Любой хэш будет работать в этом случае. Это компромисс между пространством и скоростью.

Кроме того, этот запрос будет намного быстрее:

SELECT * FROM table WHERE hash_col = 'hashvalue' AND url_col = 'urlvalue' LIMIT 1;

Это найдет первое значение и остановится. Гораздо быстрее, чем найти много совпадений для вычисления COUNT(*).

В конечном счете, лучший выбор — создать тестовые примеры для каждого варианта и эталона.

person Killroy    schedule 08.09.2009

arrow_upward
0
arrow_downward

Разве большинство движков SQL не используют внутренние хэш-функции для поиска по текстовым столбцам?

person David R Tribble    schedule 09.09.2009

arrow_upward
0
arrow_downward

Если вы собираетесь использовать хешированные ключи и беспокоитесь о коллизиях, используйте две разные хеш-функции и объедините два хешированных значения.

Но даже если вы это сделаете, вы всегда должны хранить в строке исходное значение ключа.

person David R Tribble    schedule 09.09.2009

arrow_upward
-1
arrow_downward

Если тенденция заключается в том, что результат этого оператора select будет довольно высоким, альтернативным решением будет наличие отдельной таблицы, которая отслеживает подсчеты. Очевидно, что за использование этого метода налагаются большие штрафы, но если этот конкретный запрос является распространенным и слишком медленным, это может быть решением.

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

person Brian    schedule 08.09.2009

arrow_upward
-1
arrow_downward

Если вы выбираете хеш, вам нужно учитывать коллизии. Даже с таким большим хешем, как MD5, вы должны учитывать встречу-в-среде. средняя вероятность, более известная как атака дня рождения. Для меньшего хэша, такого как CRC-32, вероятность коллизии будет довольно большой, и ваш WHERE должен указать хэш и полный URL.

Но я должен спросить, это лучший способ потратить свои усилия? Больше нечего оптимизировать? Если у вас нет четких метрик и измерений, указывающих на то, что эта проблема является узким местом системы, у вас может получиться правильно выполнить преждевременную оптимизацию. В конце концов, именно для такого поиска оптимизированы базы данных (все они), и, выполняя что-то вроде хеширования, вы можете фактически снизить производительность (например, ваш индекс может стать фрагментированным, потому что хэши имеют другое распределение, чем URL-адреса).

person Remus Rusanu    schedule 08.09.2009