Оптимизация производительности и индексация Mysql InnoDB

У меня есть 2 базы данных, и мне нужно связать информацию между двумя большими таблицами (более 3 миллионов записей в каждой, постоянно растущих). В первой базе данных есть таблица «страницы», в которой хранится различная информация о веб-страницах, а также URL-адрес каждой из них. Столбец «URL» представляет собой varchar (512) и не имеет индекса.

Вторая база данных имеет таблицу 'urlHops', определенную как:

CREATE TABLE urlHops ( dest varchar(512) NOT NULL, src varchar(512) DEFAULT NULL, timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY dest_key (dest), KEY src_key (src)) ENGINE=InnoDB DEFAULT CHARSET=latin1

Теперь мне нужно в основном выдавать (эффективно) такие запросы: select p.id,p.URL from db1.pages p, db2.urlHops u, где u.src=p.URL и u.dest=?

Сначала я думал добавить индекс по страницам(URL). Но это очень длинный столбец, и я уже делаю много INSERT и UPDATE для одной и той же таблицы (намного больше, чем количество SELECT, которое я бы сделал, используя этот индекс).

Другие возможные решения, которые я подумал: -добавление столбца на страницы, сохранение хэша md5 URL-адреса и его индексация; таким образом я мог выполнять запросы, используя md5 URL-адреса, с преимуществом индекса в меньшем столбце. - добавление другой таблицы, которая содержит только идентификатор страницы и URL-адрес страницы, индексирование обоих столбцов. Но это, возможно, пустая трата места, имеющая только то преимущество, что не замедляет вставки и обновления, которые я выполняю на «страницах».

Я не хочу замедлять вставки и обновления, но в то же время я смогу эффективно выполнять запросы по URL-адресу. Любой совет? Моя главная забота — производительность; если нужно, трата места на диске не проблема.

Спасибо! С уважением

Давиде


mysql optimization performance indexing innodb
person Davide C    schedule 09.06.2010    source источник
comment
@Gary: Я пытался сделать это раньше, но urlHops — это таблица, в которую я вставляю данные с очень высокой скоростью, поэтому я не могу разделить ее на две части (мне в основном нужно добавить к ней пару URL-адресов src и dest). Если я его так разделяю, то вставки на нем слишком тормозят для моих нужд.   -  person Davide C    schedule 10.06.2010

Ответы (3)


arrow_upward
0
arrow_downward

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

Это сделало бы индексацию и объединение более эффективными.

person mluebke    schedule 09.06.2010
comment
собираюсь попробовать это решение, даже если у меня нет сопоставления 1 к 1... Я ожидаю, что оно будет работать довольно хорошо - person Davide C; 10.06.2010

arrow_upward
3
arrow_downward

Предложение хэша MD5, которое у вас было, очень хорошее - оно задокументировано в High Performance MySQL 2nd Ed. Есть пара хитростей, чтобы заставить его работать:

CREATE TABLE urls ( id NOT NULL первичный ключ auto_increment, url varchar (255) не null, url_crc32 INT UNSIGNED не null, INDEX (url_crc32) );

Выбранные запросы должны выглядеть следующим образом:

ВЫБЕРИТЕ * ИЗ URL-адресов, ГДЕ url='http://stackoverflow.com' И url_crc32=crc32('http://stackoverflow.com');

url_crc32 предназначен для работы с индексом, в том числе URL-адрес в предложении WHERE предназначен для предотвращения коллизий хэшей.

Я бы, вероятно, порекомендовал crc32 вместо md5. Будет еще несколько коллизий, но у вас больше шансов уместить весь индекс в памяти.

person Morgan Tocker    schedule 12.06.2010

arrow_upward
0
arrow_downward

Я бы создал таблицу page_url с первичным ключом auto-inc integer и вашим значением URL. Затем обновите Pages и urlHops, чтобы использовать page_url.id.

Ваш urlHops станет (dest int,src int,...)
Ваша таблица Pages заменит url на pageid.

Проиндексируйте поле page_url.url, и все будет готово.

person Gary    schedule 09.06.2010