Я новичок в работе с базами данных и для их изучения (а заодно для изучения Питона) начал небольшой пет-проект. В процессе оказалось, что в моей многопользовательской системе запросы к БД всегда касаются только одного пользователя. «Отлично!» подумал я, «раздам каждому пользователю по табличке и сэкономлю ресурсы сервера на индексации!». Конечно сначала я почитал литературу и подобных решений в ней не нашел. Потом сходил на один из популярных ресурсов и там мне объяснили, что так делать нельзя. Но почему – никто объяснить не смог (или возможно не захотел). Поэтому я решил сделать небольшой тест на самых популярных БД – проверить что будет на самом деле.
Методика тестирования
Конечно этот тест не особо репрезентативный, поскольку результаты будут зависеть от конфигурации сервера и настроек БД, но надеюсь почитать будет интересно, а кому-то возможно окажется и полезно. В тестовой системе использовался Ryzen 5 5600 (6 ядер – 12 потоков), 32Гб оперативной памяти и NVMe SSD диск, под управлением Windows 10. Версии БД последние на весну 2023г. Тестовый скрипт работал на той-же машине, т.е. затрат на пересылку по сети небыло. В процессе отладки и налаживания методики скрипт запускался много раз, с небольшими изменениями, результат всегда приблизительно один и тот-же.
Тестовая задача, более- менее приближенная к моей реальности, выглядела так:
периодически пользователи добавляют в БД записи в которой есть 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. Написал скрипт, который эмулирует сначала случайное заполнение БД пользователями, потом рандомные запросы, и измеряет сколько времени в среднем ушло на добавление записи в БД и на выполнение запроса в зависимости от количества пользователей и количества записей (строк) у каждого пользователя. Часть скрипта для одной из БД лежит под спойлером, для остальных БД всё аналогично. Скрипт сугубо тестовый, т.е. некрасив и без обработки ошибок, увы.
Кусочек кода с процедурой тестирования
date_start = datetime(2023, 1, 1, 0, 0, 0, 0)
d_step = timedelta( minutes= 10)
days_max = int(user_rows_num / 144) - 1
users_list = range( users_num )
texsts_list = [""]*texts_num
for ii in range( texts_num ):
texsts_list[ii] = str( randrange(1, max_data) ) + "sometext" + str( ii )
#==================================================================================================================================
# MySQL InnoDB, все данные всех пользователей лежат в одной таблице, есть Primary key по столбцам id и dt
conn = get_conn_mysql('test_db1a')
cursor = conn.cursor()
create_megatable = "CREATE TABLE IF NOT EXISTS mega_table( id INTEGER, dt DATETIME, txt TEXT, dat_stat INTEGER, PRIMARY KEY(id, dt) );"
cursor.execute( create_megatable )
conn.commit()
current_date = date_start
start_time = datetime.now()
for ii in range( user_rows_num ): # эмуляция наполнения таблицы данными
cmd = "INSERT INTO mega_table (id, dt, txt, dat_stat) VALUES ( %s, %s, %s, %s )"
curr_dt = current_date.strftime('%Y-%m-%d %H:%M:%S')
current_date += d_step
users_list = sample(users_list, users_num)
for jj in range( users_num ):
cursor.execute(cmd , ( users_list[jj], curr_dt, texsts_list[randrange(texts_num)], randrange(1, max_data)))
conn.commit()
end_time = datetime.now() - start_time
start_time = datetime.now()
for ii in range(tests_num): # эмуляция запросов статистики по одному из текстов для рандомного пользователя
cmd = "SELECT SUM( dat_stat ) FROM mega_table WHERE id=%s AND dt < %s AND dt > %s AND txt = %s"
select_date_start = current_date - timedelta( days = randrange( days_max ) )
select_date_end = select_date_start- timedelta(days = 1 )
cursor.execute(cmd , (users_list[ randrange(users_num) ], select_date_start.strftime('%Y-%m-%d %H:%M:%S'), select_date_end.strftime('%Y-%m-%d %H:%M:%S'), texsts_list[ randrange(texts_num) ] ))
res = cursor.fetchall()
print("InnoDB one table. Create :", end_time *1000 / (users_num * user_rows_num)," | select :", (datetime.now() - start_time)*1000 / tests_num )
#cursor.execute("DROP TABLE mega_table") # зачищаем всё если хотим следующей итерации теста
#cursor.execute( "VACUUM" )
conn.commit()
conn.close()Скачать полный скрипт можно отсюда.
Кроме измерения времени после выполнения скрипта посмотрел сколько полученные базы данных занимают места на диске. И в результате получились такие таблички.
Результаты
Выводы
Какие выводы новичка я для себя сделал.
Все БД достаточно надежно обеспечивают одинаковое время создания записи и выполнения однотипных запросов, вне зависимости от внутреннего наполнения базы. Особенно хорошо это работает для стандартного сценария использования - одной большой таблицы с данными.
В некоторых случаях имеет смысл давать каждому пользователю по табличке, но для этого нужно либо хорошо разбираться в БД, либо хорошо проверять гипотезу. Для себя, как для новичка, я выбрал использование одной таблицы для всех пользователей как более предсказуемый вариант, а далее возможно использовать другие механизмы БД для улучшения производительности.
Если мне будет важнее скорость добавления записей (т.е. экономить вычислительные ресурсы) то нужно пользовать Postgres, если важнее экономить место на диске – то MySQL с движком MyISAM. MySQL с движком InnoDB где-то посередине, а SQLite я рассматривал из любопытства, для моего проекта он не очень подходит.
Нужно поискать более углубленных книг по программированию баз данных ( возможно кто-то что-то посоветует ).
