Производительность БД с одной и многими таблицами. Мини-тест⁠⁠

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

Для ЛЛ: В большинстве случаев выдавать каждому пользователю по таблице действительно нет смысла. Но для SQLite запросы обрабатываются ощутимо быстрее, если у каждого пользователя своя табличка.

Итак, тестовая задача, более- менее приближенная к моему сценарию:

периодически пользователи закидываеют в БД записи в которой есть ID пользоватея, время записи, текстовая метка (комментарий) и какой-то параметр (число)

INSERT INTO mega_table (id, dt, txt, dat_stat) VALUES ( %s, %s, %s, %s )

после наполнения базы пользователями иногда запрашивается статистика по тому самому числовому параметру за какой-то период времени и с определенным комментарием

SELECT SUM( dat_stat ) FROM mega_table WHERE id=%s AND dt < %s AND dt > %s AND txt = %s

Проверял 3 БД: MySQL сдвижками InnoDB и MyISAM, SQLite и Postgres. Написал скрипт, который эмулирует заполнение БД и запросы к ней, и измеряет сколько времени занимает добавление записи и выполнение запроса. Скачать скриптец можно тут (он сугубо тестовый, т.е. стрёмный и без никакой обработки ошибок, уж сорян). Менял количество пользователей и количество записей у каждого пользователя и смотрел что будет если всё писать в одну таблицу, либо каждому пользователю создавать отдельную. Заодно после выполнения скрипта посмотрел сколько полученные базы данных занимают места на диске.
И вот получились такие таблички.

Какие выводы я для себя сделал.

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

Во вторых если мне важнее скорость добавления/чтения (т.е. экономить вычислительные ресурсы) то из протестированных лучше пользовать Postgres, если важнее экономить место на диске – то MySQL с движком MyISAM. MySQL с движком InnoDB где-то посередине.

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

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

В любом случае это было интересно сделать, надеюсь кому-то было интересно и почитать.