Как переписать структуру таблиц или запросы, чтобы работал индекс для моих выборок?

Question

[sokolnikov]

Краткое описание того, что имеем.

Собираем некоторую статистику посещений веб-сайтов. Таблица посещений, выглядит в упрощенном виде так:

Table: links
id | url                                         | domain
1  | https://www.youtube.com/watch?v=6Nu3ZVA8Gic | com.youtube.www
2  | https://www.youtube.com/watch?v=5ww70Xb5pm8 | com.youtube.www
3  | http://www.bbc.com/ukrainian/politics       | com.bbc.www
4  | http://bbc.com/ukrainian/business           | com.bbc

Почему домен записываем в обратном порядке? Потому что у нас есть еще таблица с информацией по крупным сайтам, например:

Table: sites
id | name     | domain      | description
1  | YouTube  | com.youtube | ...
2  | VKontake | com.vk      | ... 
3  | BBC      | com.bbc     | ...

И нам легко получать статистику посещений отдельных крупных сайтов так, чтобы работали индексы MySQL. Например, получаем ссылки по сайту BBC (включая возможные субдомены):

SELECT id, url FROM links 
WHERE domain = 'com.bbc' OR domain LIKE 'com.bbc.%'

Суть вопроса.

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

Например, нам надо выбрать какое-то кол-во ссылок, вместе с соответствующей им информацией по сайту. Делаем следующее:

SELECT links.id, links.url, sites.id AS site_id, sites.description 
FROM links
LEFT JOIN sites ON links.domain = sites.domain 
             OR links.domain LIKE CONCAT(sites.domain, '.%')

И естественно из-за использования LIKE CONCAT в JOIN перестает использоваться индекс для links.domain.
Какое-то время, когда было не слишком много записей в обеих таблицах, мы неспешно просчитывали статистику фоновыми задачами. Но сейчас даже просчитывать в фоне - не вариант, уж очень долго, и уж слишком ресурсоемко.

Так что ищу совета, может как-то перестроить структуру? Или что-то сделать с запросами так, чтобы заставить использовать индексы (USE INDEX и FORCE INDEX не хотят работать в моем случае).
А так же важен совет, какой лучше движок использовать в моем случае MyISAM или InnoDB?

Answer 1

[Oleg]

Для начала объясню почему индексы в вашем случае не работают и не могут работать.

> OR links.domain LIKE CONCAT(sites.domain, '.%')

CONCAT - это функция, и вы работаете с результатом функции.
Т.е. получается что в вашем запросу нужно:
1. выбрать все строки из links
2. подсодеденить к каждой строке по sites.domain или sites.domain результат фунции.
=> нужно посчитать каждую строку каждый раз.
Это очень много.

что я бы сделал:
1. создал таблицу доменов
в ней:

id | main_id |domain
1 | 1 | com.youtube
2. | 1 |com.youtube.www
3. | 1 |com.youtube.subdomain

Во всех таблицах - перешёл бы на этот ключ.
2. тогда ваша выборка сокращается до:

SELECT links.id, links.url, sites.id AS site_id, sites.description 
FROM links
LEFT JOIN domains ON links.domainId = domains.id
LEFT JOIN sites ON sites.id = domains.main_id

(смутно понимаю что вы хотели в этом запросе, поэтому уж не обессудьте :) )

Т.е. основной посыл:
переходите на int- ключи

PS: Так же это называется нормализация базы данных. Хранить много одинаковых строк - это плохо.

Comments

[Станислав Макаров]

Если уж иерархию доменов вводить - тогда можно просто subdomain вместо com.youtube.subdomain) А если еще и com.youtube разбить надвое, то можно отказаться от разворачивания наоборот

[Oleg]

Станислав Макаров: Тут ещё просто момент, что если оставить com.youtube.subdomain, то можно будет легко искать так:
select id from domains where domain like "com.youtube%"

и соответственно:

select * from links
left join domains on domains.id =links.domainId
where domain like "com.youtube%"

[sokolnikov]

Ваш вариант мне понятен, спасибо за участие. Но он влечет много дополнительных расходов, например:
1. при вставке новой ссылки, мы должны проверить не попадался ли нам ранее ее домен (субдомен), и если не попадался, мы должны определить в какой домен войдет текущий субдобен (например site1.co.uk и site2.co.uk - совершенно разные сайты, у них могут быть наборы своих субдоменов).
2. если в какой-то момент я захочу сайт economics.news.ru вынести как отдельный от news.ru сайт, то прийдется переписывать и связанные таблицы ссылок и доменов.
3. не понятно, что делать с сылками, для которых у нас еще нет соответствующих сайтов. Например при добавлении ссылки www.bbc.com/ukrainian/business у нас уже есть в базе сайт bbc.com и мы ассоциируем ссылку с ним. А если мы добавляем ссылку vasya2.site.org.ru/some/path, но у нас нет в базе ни сайта vasya2.site.org.ru ни site.org.ru. С каким доменом тогда ассоциировать это ссылку? С vasya2.site.org.ru или site.org.ru, а может org.ru? :)

Т.е. мы сейчас используем неявную связку таблиц (т.е. данные при добавлении не знают, с какими данными другой таблицы они будут связаны в будущем), вы же предлагаете использовать явную связку (т.е. формировать связи на этапе добавления данных).

Я, конечно, понимаю, что тут два варианта, или мы тратим ресурсы при добавлении, но тогда нам проще с выборкой данных, или же мы меньше заморачиваемся с подготовкой данных при в несении, но нам придется тратить ресурсы при выборке. Но хочется найти золотую середину - решение, которые бы оказалось для меня самым удобным и практичным на больших нагрузках.

[Oleg]

sokolnikov:
> Я, конечно, понимаю, что тут два варианта, или мы тратим ресурсы при добавлении, но тогда нам проще с выборкой данных, или же мы меньше заморачиваемся с подготовкой данных при в несении, но нам придется тратить ресурсы при выборке. Но хочется найти золотую середину - решение, которые бы оказалось для меня самым удобным и практичным на больших нагрузках.

Это ключевое тут :)
чем больше нагрузка - тем меньше имеет смысл хранить данныве в данном как они приходят .

Как вариант - вы можете писать сначала во временную таблицу, и потом ночью обрабатывать её и раскладывать по полочкам.

Можете сделать так же так, что не использовать столбик main_id - можно и без него.
Т.е. ваш запрос будет выглядеть так:
select * from links
left join domains on domains.id =links.domainId
where domain like "com.youtube%"

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

Это всё решаемо.

Основной посыл:
переходите на цифровые индексы и не храните одни и теже данные в копиях.

[sokolnikov]

Хорошо, спасибо за участие. Если через какое-то время не появится новое гениальное решение (на которое я очень надеюсь :) , иначе слишком много переписывать кода и слишком много данных переформатировать придется), то отмечу ваш вариант "решением".

Answer 2

[sim3x]

3НФ будет выглядеть так

domain_zone:
  parent = ForeignKey(domain_zone)
  name = Text

site_page
  domain_zone = ForeignKey(domain_zone)  
  url = URL

Суть такова: есть корневая доменная зона "." точка.
Есть доменная зона com, у нее parent точка
У сайта bbc.com parent будет указывать на com

Вобщем делаем аналогично DNS

Comments

[sim3x]

lol