Комментарии 46
Без настроек БД говорить о чем-то смысла нет, тем более на таких небольших данных. Почти наверняка у innoDB все попало в кэш, sqllite традиционно тупо пишет на диск, а myisam где-то посредине. Что собственно и видим в результатах, но это ничего не говорит о самих БД как таковых.
настройки были "по умолчанию" и это, насколько я знаю, достаточно распространенный случай. Я прочел десяток поверхностных статей по настройкам, и для InnoDB даже поувеличивал разные части кэша, но на результатах это особо не сказалось, поэтому оставил как есть. Видимо при моих размерах данных всё что нужно влезало в кэш и так.
В результатах видно, что разница между "одна таблица + индекс" и "по таблице каждому юзеру" определенно есть, но не в ту сторону, на которую я расчитывал (ну кроме SQlite). Связываю это с тем, что все протестированные БД заводят для каждой таблицы отдельные файлы, а SQlite всё пишет в один файл и видимо с некоторого количества таблиц переключение между файлами становится более накладным, чем индексация.
Как вам выше справедливо заметили без подробностей настроек БД смысла говорить о результатах мало. На таких малых объемах и с такими малыми длинами записей и тривиальными индексами во взрослых RDBMS вся ваша БД будет держаться в памяти, а не на диске. А подчас даже данные вероятно будут лежат в самом индексе, а не в таблицах. И вы своими тестами скорее будете измерять скорость доступа к RAM и производительность CPU, нежели эффективность механизмов самой БД. Чтобы проводить подобные осмысленные тесты, надо сначала почитать как организовано хранение данных в той или иной БД. Потом почитать как организован поиск этих данных. Потом возможно взглянуть какие оптимизации используются для чтения\записи. И уже с учетом этих знаний пытаться что-то измерять на данных приближенных к боевым с боевыми запросами, а не на синтетически сгенерированных на коленке с тривиальными запросами.
ну это "так и не так".
Изначально у меня вопрос был не с ускорением запросов вцелом, а более конкретно: можно-ли выкинуть ненужную процедуру индексации по пользователям за счет использования многих таблиц. Для моего проекта это было полезно, т.к. мои "боевые данные" выглядят в точности так как в тесте, и сценарий использования БД очень похож. Ответа в литературе я сходу не нашел, подробное изучение внутренней механики каждой из БД это не одна неделя, поэтому я просто взял и померил.
По поводу результатов - тестовая система для всех БД одинаковая, с одинаковым временем доступа к RAM и диску - а время выполнения разное, мне кажется эффективность БД таки поучаствовала в тестировании. Единственное, о чем я не подумал - о том что нужно было это всё развернуть на виртуалке и проверить с небольшим количеством RAM и одним-двумя ядрами, как на настоящем сервере небольшого проекта.
"В некоторых случаях имеет смысл давать каждому пользователю по табличке" - а каждому футболисту по мячу, как Хоттабыч
для SQlite это ускоряет запросы в несколько раз...
Итерация по табличкам тоже потребует усилий.
А в нормальных БД, то же самое делается индексом. По скорости не ниже, и физически почти то же самое.
Нужно делить на таблицы отдельные типы данных.
А если нужно совсем отдельный вид данных хранить для пользователя, легче отдельный файл в распределённом хранилище держать.
Так как скорее там окажется много таблиц и данных. Что бы вообще вынести и распределить нагрузку.
раздам каждому пользователю по табличке [...] Потом сходил на один из популярных ресурсов и там мне объяснили, что так делать нельзя. Но почему – никто объяснить не смог (или возможно не захотел).
Потому что никто не будет создавать новую таблицу при регистрации пользователя.
иногда партиционирование по пользователю может иметь смысл.
ну вот эта концепция ответа на вопрос "почему?" - "потому что." и привела меня к идее тестирования. Например "потому что появляются большие накладки на работу с файлами" - это понятно, а "потому что никто так не делает" - ну не особо понятно, может у людей просто сценарии использования не такие как у меня.
Ага, "я такой особенный, у меня уникальные сценарии" :)))
А может быть объяснение немного проще? "Я двоечник, который не удосужился узнать, что такое реляционная база данных и выдумал от невежества такой сценарий?"
Вам следовало написать это не на хабр, а на https://qna.habr.com и попросить объяснить полученные результаты. А здесь вы только получите минусов в карму.
Сильно этот сценарий уникальный или нет - я не знаю, в той пачке книжек по БД, которую я прочел в поисках ответа такого сценария небыло. Я обычно ищу инструмент для решения определенной задачи, а не подгоняю задачи под известные инструменты. Моя задача вот такая.
Минусы в карму это наверное печально, но я пост сделал не для кармы, а потому что в ходе решения своей задачи сделал что-то, чего в популярных книжках и статьях из интернета - небыло, поэтому поделился с сообществом. Просто потому что мне было интересно.
Вы подгоняете инструмент не под задачу, а под свою чудовищную неграмотность.
И это ещё пол-беды, потому что все тупят поначалу.
Куда хуже то, что даже после всех объяснений, вы все так же считаете свое решение гениальным
эм. Вижу прям недопонимание.
Я считаю что объяснения в стиле "ты ничего не зниешь" и "так никто не делает" - это недостаточный аргумент. Ну да, я многого не знаю и действительно так никто не делает. Для меня объяснение - это когда указывают причину, почему это не будет работать. Если причину в литературе (и в каментах) найти не удалось - значит нужно проверить - будет работать или нет, потому что возможно вот с этим конкретным случаем люди просто не сталкивались, именно поэтому так никто не делает - им было не нужно. А мне было нужно и я проверил идею.
Вот проверил и убедился что да, в большинстве случаев - так не работает, о чем написал. Потому что раньше про такой сценарий никто не писал; вдруг кому-то понадобится или будет интересно.
Про "гениальность идеи", ну хз, у меня про это нигде не написано. Выводы у меня достаточно чёткие - чаще всего не работает, я этим пользоваться не буду.
Вот проверил и убедился что да, в большинстве случаев - так не работает, о чем написал.
...даже после нескольких комментариев вы все еще не поняли, что дело не в производительности?
ну блин, в ней, родимой. Вот есть прекрасный ответ, почему оно так не работает: https://habr.com/ru/articles/764224/#comment_26013286
Я и говорю: вы, к сожалению, все еще не понимаете. Да, прозводительность такого решения может быть не восторг (хотя реальные цифры надо мерять, и они могут радикально отличаться в зависимости от множества параметров); но первое, что останавливает от его применения - это безопасность и поддерживаемость. Просто это те качества, которые даже не пришли вам в голову, пока вы не попробуете это провести через код-ревью.
давайте обсудим проблемы безопасности и поддерживаемости? =)
Очень интересно, какие проблемы вы видите как специалист.
Для этого наверное нужно рассказать не только о базе, но и о том, как она используется в проекте.
У меня есть условный "фронт-энд", который формирует запросы от пользователей и отсылает их на "бэк-энд" (программа на сервере). Бэк проверяет поля запроса на соответствие, формирует SQL запрос и закидывает в БД, ответ отсылает обратно во фронт. Пользователь и программист фронта с базой не контактируют вообще. Для программиста бэка ID пользователя идет либо как одно из полей данных, либо как название таблички, вся остальная разница спрятана внутри БД. Какие могут возникнуть проблемы с поддерживаемостью или безопасностью, если у нас ID пользователя переедет из данных в название таблицы?
ну вот эта концепция ответа на вопрос "почему?" - "потому что."
Ответ на больщую часть вопросов "почему" начинается с "потому что". В вашем случае ответ на вопрос "почему нельзя разделить юзеров по таблицам" начинается с "потому что никто не будет создавать по таблице на юзера". Это разные вещи. Вас интересует, почему нельзя создавать по таблице на юзера? Например, потому что это требует от приложения слишком много прав, которые в норме приложению стараются не давать.
Ну их же не руками создавать
А вот то, что запросы для работы с данными пользователей будет менее удобно писать уже похоже на правду. Запросы вроде "Выбрать всех пользователей старше 20", которые в одной таблице пишутся в одну строчку, теперь надо как-то костылить проходясь по всем таблицам с подходящей схемой
Ну их же не руками создавать
А чтобы не руками создавать, нужно много прав.
задача не подразумевала запросов типа "выбрать каких-то пользователей". Всегда каждый пользователь работает только со своими данными, и всё. Пользователи не взаимодействуют между собой вообще никак.
Соответственно никаких костылей и никаких прав для пользователя не нужно - пользователь обращается к бэкенду, бэк на основе логина обращается к нужной табличке и возвращает пользователю ответ.
Всегда каждый пользователь работает только со своими данными, и всё. Пользователи не взаимодействуют между собой вообще никак.
Я, кстати, хочу заметить, что описанная вами ситуация называется multitenancy, где то, что вы называете "пользователем" - это тенант (tenant), и для нее существуют описанные архитектурные паттерны, вплоть до "изолированное окружение на тенанта". Вопрос только в том, зачем конкретно это делается.
Экономия на индексации - это даже не копейки
Новая таблица ... ну, к примеру DDL автоматом делает commit (fixme), что крайне неудобно, если в рамках транзакции. Накладные расходы на создание таблицы и ведение словаря такого размера тоже как бы наверняка не бесплатны, но честно говоря, никогда не задумывался
Идея чудовищная.
Во-первых, это раздувание словаря, что не есть хорошо.
Во-вторых, на каждый запрос будут выполнятся все шаги Parse -> Rewrite -> Plan ->Execute.
И чем больше раздувается первое, тем медленнее работает оптимизатор (Plan) во втором.
Нормально - когда первые три фазы делаются один раз, а потом только выполняется Execute столько раз, сколько потребуется.
А вы уверены, что всё так однозначно?
Вот выдержка из документации к Postgres: "Сначала планировщик/оптимизатор вырабатывает планы для сканирования каждого отдельного отношения (таблицы), используемого в запросе." Выглядит так, как будто работа оптимизатора наоборот должна ускориться, потому что ему не нужно по индексу собирать данные пользователя, я ему сразу указываю таблицу, в которой лежат все нужные данные, причем лежат упорядоченно.
А вы уверены, что всё так однозначно?
Я не уверен, я знаю. :-) На самом деле всё еще хуже чем я писал.
я ему сразу указываю таблицу
Вот в этом и проблема.
Вам знакомы термины Hard Parse, Soft Parse, Library Cache ?
Вы в каждом запросе указываете СУБД разную таблицу, из-за чего ей приходится каждый раз делать hard parse и строить новый план.
На этапе hard-парсинга:
проверяется синтаксис запроса.
проверяется существование объектов запроса (select-ы к словарю)
проверяются права пользователя к этим объектам (select-ы к словарю)
проверяется наличие и тип колонок в таблицах (select-ы к словарю)
проверяются настройки безопасности любого уровня (select-ы к словарю)
На этапе построения плана запроса:
выбираются статистики по таблице (select-ы к словарю)
выбираются статистики по колонкам (select-ы к словарю)
выбираются наличие и состав индексов на таблицах (select-ы к словарю)
перебираются варианты доступа и соединения с оценкой стоимости
Это упрощенно, но надо ли вот это всё проделывать для каждого запроса? Или все-таки стоит не убивать СУБД, а проделать это один раз, а потом только переиспользовать проделанное и вызывать только фазу exec?
Достаточно один раз посмотреть трейс 10046 (это оракл), чтобы понять какую чудовищную работу приходится делать чтобы сделать hard parse.
Я ответил на ваш вопрос - "все говорят что не надо так делать, но не могут объяснить почему" ?
Добавлю, если это не очевидно.
Нормальная СУБД закэширует все этапы подготовки к исполнению в Library Cache и сама будет переиспользовать результаты парсинга и планирования.
Только не надо ей мешать и каждый раз подсовывать разные таблицы.
божечки, спасибо!!!
Вот без всякого сарказма - спасибо огромное!!
не могли-бы посоветовать хорошую книжку, где про это прочитать можно?
Вот по части литературы сложно что-то посоветовать. Хороших современных книг по базам данных так сходу на ум не приходит.
Можно почитать старого доброго Тома Кайта. Книжка, конечно, старая, написана про Оракл, но базовые принципы всех РСУБД одинаковы и там объяснены. Читается легко, я бы даже сказал увлекательно.
Льюис Адамс... ну тяжеловатое чтиво, тем более что оптимизаторы с тех пор сильно продвинулись вперед. Но если есть цель немного "влезть под кожу" оптимайзера - то можно.
Совсем лайтовый вариант - курс QPT от PostgresPro, он свободно выложен у них на сайте. Толком ничего не объясняет, но "по вершкам", в принципе, пробегает.
Дело не только в производительности. Представьте что у вас банк. И счета каждого клиента хранятся в отдельной таблице. Вам надо сосчитать общую сумму. Ваши действия?
раздам каждому пользователю по табличке
Собственно партиционирование и шардирование примерно по этому принципу "унутре" и работают. Только без "закатывания солнца вручную".
Вам нужно почитать книги про архитектуру баз данных, нормальные формы и всё такое. Ваши тесты непоказательны - 10000 записей любая их этих баз не заметит.
По поводу почему отговаривали так делать... Обычно люди используют СУБД для работы с более сложными отношениями данных. Если вам нужно дать возможность нескольким людям соединяться с базой напрямую и хранить что-то в независимых табличках с 4-мя полями - почему нет? Главное прав им больше не давайте.
Но это странный бизнес-кейс какой-то. Обычно либо пользователи работают с базой через приложение (в этом случае на индексах экономить смысла нет и одной таблицей будет проще управлять, тем более, что 3000 пользователей * 10000 строк = 30,000,000 - это немного), либо это shared hosting и пользователям дают доступ к базе, а не одной табличке.
Небольшой тест нескольких БД при работе с многими таблицами