Таки здравствуйте!)

Есть такой запрос:



И так далее. Таблиц bonus более 20. То есть таких строк будет 20 и более. В каждой таблице bonus - 10 000 записей. Этот запрос выполняется где-то за 0,11 сек. Вроде бы и ничего, но хотел бы спросить: при посещаемости более 10 000 в день серв не загнется?

Сенкс!)

Это сообщение отредактировал(а) Acuna - 27.12.2013, 01:35

Интересно, а почему?


Для ответа катастрофически мало данных.

PS. Запрос-то вообще странненький... сколько он тащит строк без лимита?

Потому-что шардинг) Когда раньше была одна таблица, при ее увеличении выборка происходила все медленнее и медленнее. Решили разделить ее одну на несколько более маленьких по 10 000 записей. Теперь серв думает, что записей не так много. Помогло, но теперь нужно осуществлять поиск во всех этих таблицах одновременно и что-то там искать. Запрос отрабатывает подозрительно быстро. Настолько быстро, что я жду подвоха, тем более запрос и правда странный)

Ну не более 15-20 записей на страницу выводит. А пока всего записей соответственно 10 000 x 20 = 200 000. Каждый год увеличивается примерно в два раза :/

Ну в плане данных - если вообще машина, то камень - Xeon MP 2000 Мгц, 2 Гб оперативы. Но это VPS. Посещалка растет на 10 000 и более в год.

Так-то)))

Это сообщение отредактировал(а) Acuna - 27.12.2013, 14:49

Ага... и вместо того, чтобы использовать партиционирование, вы решили наплодить вот такой дури... ну теперь разгрёбывайтесь.

Akina,
Хм... Благодарю! Да, это интересное дело, но вроде как он не осуществляется автоматически, а ведь вставка осуществляется ежедневно. Что-нибудь автоматическое бы...

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

НУ забахай разделов на год вперёд... а за месяц до "часа икс" - добавь ещё на год...

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

Хотя я как-то не подумал. Можно же написать несложный скрипт для партицирования и запускать его кроном хоть раз в месяц. Вот что значит хороший брейн-шторм)))

Тогда возникает вопрос: а можно ли создавать раздел по количеству записей? Например, каждые 10 000 строк. Это было бы идеальным решением!


А может я Павел Дуров, который разогнал всех архитекторов БД и теперь шарится по форумам, прикидываясь обычным юзером 

Просто если уж при 200 000 записей такое дело, то боюсь представить, что будет через год :/

Это сообщение отредактировал(а) Acuna - 30.12.2013, 03:51

Это - безобразное решение. Оно потребует глобально нумеровать записи и породит неслабый оверхед. 
Дели по дате создания записи, и дело с концом. Ну будет в одном разделе 50 тысяч, а в другом 200 - и что? надо - при закрытии раздела посчитай количество записей в нём, и закинь навечно в таблицу служебных данных.


  какое дело? нет индекса? ну создай...

Просто если при 200000 записей Вы радуетесь, что выборка по уникальному ключу занимает 100ms (дажэ пусть на холодную) -- то Вы чего-то непонимаете в современных БД. Нормально время, кстати, на достаточно десктопном винте (не SSD) -- 40-50ms на холодную и 10-20ms -- с разогретым кэшэм (если вся БД всё-таки вытеснена из памяти).

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

Akina,

О, это конечно, не дело. Я просто думал, что вдруг мускул имеет что-то типа автоинкремента, который всегда будет увеличиваться на единицу. Что-то сродни переменной, значение которой - количество строк в этой партиции. Интересная идея, кстати) Жаль только, что не реализована...

tzirechnoy,
О, благодарю за ценные данные, возьму на заметочку!
Да, я вообще-то просто программер. С БД на таком уровне мне дело иметь не приходится... А этот ресурс мне достался почти от бабушки) Хочется его довести до ума, а заодно и закрепить приобретенные навыки на деле...

А тогда вопрос к Вам еще по времени: самописный двиг выбирает порядка 20 новостей на страницу, получает данные о юзере и его права согласно его группе. Вроде все. Все эти запросы выполняются где-то около 1,06-1,11 сек. Это нормально?

P. S. Кстати, возможно в ВК партицирование (но там и шардинг, думаю, естесственно) реализовано как раз по количеству строк. Например, юзер не может добавить более 10 000 друзей. Не берусь ничего судить, но число уж больно кругленькое) Почему не по дате добавления - один Паша Дуров знает, но считаю, что эта инфа заслуживает внимания...

Это сообщение отредактировал(а) Acuna - 2.1.2014, 23:24

Это прискорбно. Если бы Вы были верстальщиком, SEOшником или экономистом -- то незнание основ RDBMS было бы Вам простительно. Но как-то среди 10-15 языков, которые должэн бы знать программист, SQL явно в тройке общеобязательных.

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

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

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



Нет. Притом к БД этот вопрос просто не относится. Это ненормально потому, что время, воспринимаемое человеком как "мгновенно" -- около 20мс, как "очень быстро" -- около 50мс. И нормально -- это когда элементарные действия укладываются хотя бы во второе время (ну, в бОльшэй части случаев укладываются). 
Неэлементарные, кстати, действия правильно бы иметь возможность ставить в очереди элементарными -- или найти другой выход из положэния, чтобы пользователю не приходилось по секунде втыкать в подвисшый комп после каждой кнопки.

И да, в обычной БД и обычной структуре данных -- надо во-первых очень постараться, чтобы хотя бы на холодную выборка из прав и новостей заняла секунду (а не порядка тех жэ 50-150 мс, выборка по индэксу из линейной структуры с большой вероятностью получить все нужные страницы данных подряд плюс выборка из прав, которая скорее всего -- тожэ несколько подряд идущих страниц), во-вторых -- ещё очень постараться, чтобы выборка из прав была холодной. В общем, у Вас в базе, скорее всего, творится трэш, угар и содомия.

tzirechnoy,
Нет-нет, БД я проектирую параллельно с разработкой. Просто на таком уровне (партицирование и шардинг) я сталкиваюсь впервые.

Индексы я вроде расставил) Насколько я их знаю, индекс ставится на те колонки, по которым выборка осуществляется чаще всего. То есть, например, нужный юзер выбирается по столбцу user_id, значит на него ставим FOREIGN KEY (он же чаще называется просто KEY). Но если мы будем джойнить один-ко-многим с этой таблицей (чтобы, например, получить данные юзера, оставившего комментарий), тогда на него ставим PRIMARY KEY (потому-что значения должны быть уникальными), а на user_id таблицы комментариев - FOREIGN KEY.

Раньше как-то на это внимания не обращал, но теперь перечитал маны еще раз, и перепроверил свои. Как и ожидалось, все PRIMARY KEY стояли на столбцах id во всех таблицах. Переставил по-нормальному. Ну время выполнения уменьшилось на 20мс (с 1,2 с до 1 с). Как-то особой разницы не видно, да?)))

Это сообщение отредактировал(а) Acuna - 3.1.2014, 16:06

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



Нет, FOREIGN KEY на некотором отношэнии вообще не является KEY на этом отношэнии. Прочитайте и поймите определение и того и другого -- просто чтобы лучшэ ориентироваться в теме.

Впрочем, Вам сейчас вообще незачем лезть определять foreign keys -- производительность они изменяют лишь постольку, поскольку автоматически создают индэксы. И вообще это само по себе -- не средство изменения производительности, не надо так делать.

PS У меня в этом комментарии до  `PS' выражэны три простые мысли в трёх небольшых абзацах. Постарайтесь проверить себя, что Вы поняли каждую из трёх до того, как запостите комментарий, и ответить хотя бы в двух словах на каждую -- это важно для Вас.

Вы неверно знаете. По простой причине - вы не изучили, что такое индексы. Это становится опасным - снежный ком неверных "знаний" растёт очень быстро, а вычищается потом из мозгов долго и болезненно - в т.ч. болезненно и для результатов своей работы.

Спасибо, что сидите тут со мной)

Поразительно, выдавил все запросы в лог, выполнил всю эту конструкцию в консоли, не оскверненную всяческими сомнительными скриптами - процесс занял 40мс. Быстрее уже некуда. Другое дело) Бум крутить, почему двиг неверно считает время... Сенкс.

Да, а этот запрос из другой оперы был...

Прочитал про FOREIGN KEY - действительно, я не правильно его охарактеризовал. Оказывается, мускул их то ли не поддерживает вовсе, либо поддерживает половинчато. Видать, все в голове перемешалось просто.

Набросал от руки чисто механический запрос, удовлетворяющий моему условию. В определениях могу путаться, поэтому легче просто показать все на примере:


Он выводит 20 комментариев к новости номер 2352. У нас происходит выборка один-ко-многим: `users`.`id` у нас уникален, тогда как значений `comments`.`user_id` может быть большое количество (один юзер может добавить множество комментов к одной новости). Вот... Тогда на столбец `users`.`id` ставим PRIMARY KEY, на `comments`.`user_id`- просто KEY (ибо джойним с `users`.`id`по нему). И на `comments`.`post_id`тоже просто KEY (потому-что выборка по нему). Кроме как по `comments`.`post_id` выборка не осуществляется (нет необходимости).

Я правильно поступаю?

Это сообщение отредактировал(а) Acuna - 5.1.2014, 15:12

Полностью. Но только на одном движке.


Изучите, что такое EXPLAIN. И главное - как интерпретировать его вывод. Затем используйте. 
Без этих сведений ничего сказать нельзя.

Очень вероятно, что разница как раз между холодным (с диска) и тёплым (из кэша в памяти) чтением. Да, на 200k записей при seqscan будет где-то пол-секунды при холодном и 40мс при тёплом. Холодное -- при условии, что движок БД всё-таки в памяти, т.е. действия с другими таблицами недавно выполнялись
При этом при index scan с одной записью -- будет около 40-100 мс при холодном и 0.5 мс при тёплом.



ещё раз: главное, что это -- не важно. Это не средство улучшэния производительности, это средство поддержания цэлостности.



Не особенно.



users.id является практически в любом случае PRIMARY KEY -- независимо от того, какие конкретно запросы ты проектируешь. Поскольку именно id является основным идэнтификатором пользователя в твоей базе данных. Так что хочешь-не хочешь -- а users.id надо делать PRIMARY KEY.
Вообще, на начальном этапе освоения -- PRIMARY KEY надо делать в любой таблицэ. Потом могут быть какие-нибудь не очень реляцыонные примеры таблиц, в которых не нужэн PRIMARY KEY -- но в первые пол-года тебе таких не попадётся.
Ну и да, так получилось, что на PRIMARY KEY всегда есть индэкс.



1) comments.user_id не является ключом в таблицэ comments -- поскольку очевидно, что не идэнтифицырует однозначно кортэж.
2) Именно поэтому не стоит называть такие индэксы, которые делаются только для ускорения работы запросов, ключами. Это не ключи!!! Это именно индэксы, средство для ускорения работы движка БД.
3) К тому жэ, CREATE KEY вместо CREATE INDEX -- это какое-то местечковое оракловское расшырение. В большынстве БД есть ограничения PRIMARY KEY, UNIQUE, CHECK, FOREIGN KEY (всё именно в таком написании), а просто KEY -- нет. И, отдельно, есть индэксы -- на создание которых нет особо стандартов, но чаще всего их можно создать через CREATE INDEX.

При этом некоторые ограничения создают некоторые индэксы для своей работы, но это побочный эффект в общем-то.

То есть -- для ускорения работы ковыряния ужэ должны быть в индэксах, а не в ключах.



При сканировании таблицы comments (возможно, многократном за один запрос, это сейчас неважно) -- очевидно, что индэксированная выборка будет по одному столбцу из этих двух. Поскольку либо у нас сначала выбирается что-то из comments по post_id, add_date, тогда comments.user_id у нас есть, и по нему ищется ужэ кортэж в таблицэ users -- либо что-то выбирается из таблицы users, по нему ищутся все комментарии с данным comments.user_id в таблицэ comments, и post_id индэксированно искать ужэ не надо, посколько он ужэ прочитан и получен. Есть, кстати, варианты, когда вообще никаких индэксов использоваться не будет -- обе таблицы прочитаются, отсортируются в памяти и сольются в результат последовательным перебором отсортированных значений. Но это не тот случай, конечно.
Кстати, почитайте про организацыю btree index, это полезно.

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

Да, скажу ответ для данного запроса и типичного соотношэния размеров таблиц: относительно правильным будет создать индэкс на comments (post_id, add_date) -- чтобы движок мог чисто по индэксу определить первую запись из comments, которая нам нужна, а потом ужэ для всех 20 подряд нужных записей выбирать их идя по индэксу. Ну, и на каждую --  по одной записи по PRIMARY KEY из users.

Относительно правильным -- поскольку при большом limit движку придётся выбирать все соответствующие строки. Т.е. если нужна 100-я страница темы -- то никаких индэксов, чтобы отсчитать 2000-й коммент с данным post_id у него не будет, и придётся пересчитывать все 2000 записей. Более правильным будет либо указывать в html-ссылках на страницы даты, с которой эта страница начинается (ну там, before=2012-11-25+14:21:15.12312) или перенумеровать комменты темы отдельным полем сделать на него индэкс и выбирать ужэ по этому полю, а не по лимиту.

Ну и, в общем, если таблица users относительно невелика и висит в памяти -- то можэте дажэ уложыться в нормальные 50мс.

Умрет и винты взорвуться попутно разнеся датацентр на куски. Число жертв будет исчисляться миллионами...

Гуглите:

• нагрузочное тестирование
• профилирование запросов
• профилирование приложений

Вопросов будет много меньше

Это не шардниг, это копипаст! Тут явно какая-то кривость с ключом, туда и копать нужно, в первую очередь, и если уж есть уверенность что с ключами все хорошо, можно посмотреть на партиционирование! А вертикальный и горизонтальный шардинг строится совсем по другим принципам, хотя легкая внешняя схожость с шардингом есть, но только внешне!

users.id - вешаешь primary key
comments.user_id - вешаешь foreign key, который ссылается(references) на users.id
comments.post_id - рискну предположить, что тоже можно повесить primary key, но тут нужно смотреть структуру таблицы comments

Скажыте, а Вам до этого кто-нибудь говорил, что не надо лишний раз высказывать суждения о вещах, в которых Вы не разбираетесь?

Это сообщение отредактировал(а) tzirechnoy - 9.1.2014, 12:10

Страдаете звездной болезнью??

Наслаждаюсь.

Ничего, это тоже лечится! Лучшее средство лечения - работа!

Большой брат внимательно наблюдал за вами со стороны! ;)

Хотел бы поблагодарить всех за отличные мысли, надо их все обмозговать теперь...

Особенно мне понравился ответ Bulat'а, кратко и по существу, особенно, когда работы столько, что забываешь, как тебя зовут 

А сам шардинг - дело вообще не мое, данный крупный проект некоторое время меняет хозяев, это подобие шардинга создавал прошлый владелец, а моя работа как PHP-программиста касаемо БД заключается только в написании запросов, сами БД проектируют другие люди. А теперь я, получив этот проект, вдруг нечаянно осознал, что я авно, и ничего в этой жизни не добился  В следствии чего и появилась данная тема!)

tzirechnoy,
А можно вам задать вопрос не по существу: давно хотел спросить, а почему вы вместо "е" пишите "э", а вместо "и" - "ы"?)))