Зависит ли производительность базы данных от количества записей?

Question

[dan0sss]

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

Comments

[mayton2019]

Отвечая на этот вопрос можно целую книгу написать. Разница будет. Мелкая база будет читать 1 блок или page из кеша страниц. Индекс при этом не нужен. Толстая база будет ходить в индекс и примерно за 3-4 чтения блока из кеша будет находить тебе эту одну и ту-же запись. Обычно индекс для миллионов записей состоит из 3-4 уровней. Я не знаю точно как устроен индекс в MySQL но если он похож на оракловый - то примерно так.

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

[Vitsliputsli]

mayton2019, индекс всегда используется, даже если у вас 1 строчка в базе, даже если вы не создавали индекс primary key, он все равно будет создан.

[mayton2019]

Vitsliputsli, пожалуй да. Но здесь автор ничего не указывает про таблицу и индексы. Индекс вобщем-то вещь опциональная. Например Oracle не требует создавать индексов. И классическая таблица-лог тоже их не требует в силу отсутсвия НФ выше чем 1. Таблицы staging. Таблицы дата-аналитики. Все это не индексируется обычно.

[Vitsliputsli]

mayton2019, да, про индексы не указано. Но, он хочет "получить запись с определенным айди", всетаки обычно id - это индекс primary key.

[mayton2019]

Да. Это справедливо для RBO-оптимизатора. Тоесть если движок следует правилам то он всегда пойдет по индексу если таковой есть. Если работает CBO - то возможен кейс когда проще сделать FTS по маленькой таблице в 1 блок чем сделать 2-3 чтения (1й уровень и второй индекса + чтение табличного блока по ROWID).

[Vitsliputsli]

mayton2019, хм, а вы уверены, что это актуально для выбора по primary key? Ведь CBO пользуется статистикой, а эта штука не гарантирует абсолютную актуальность, собственно, что можно увидеть, когда CBO выбирает full scan, а таблица уже перестала быть маленькой. Будет ли оптимизатор рисковать полагаясь на статистику, когда выборка по primary key это гарантированно быстрый запрос в любом случае, хоть и надо будет прочитать страницы индекса?

[mayton2019]

Vitsliputsli, я раньше хорошо помнил как работает оптимизатор Oracle. Сейчас после Databricks/catalyst у меня каша в голове. Тем более что этих CBO.... каждая овоще-база делает свой. Вобщем точно не помню. Но в идеале оптимизатор должен работать не на RBO это точно.

Будет ли оптимизатор рисковать полагаясь на статистику

это вопрос сложный. Старые рекомендации от оракла образца 2010 года писали что надо
просто пересобрать статистику. Там процедура была gather_table_stats. Она и так по скедулеру
работала ежесуточно. И предполагалось что количество строк в крупных БД - стационарно
или меняется не сильно. Ну если их - миллионы - то загрузка там сто тыщ строк в день
особо не делает погоды. Были краевае кейсы когда CBO ошибался. Но философия такова
что для крупных систем если оптимизатор угадывал эффективность плана хотябы в 9 запросах
из 10 то это уже был успех. Потому что тюнить запросы на ходу DBA не успевали. Особенно
там где кодо-генерация. Всякие ORM/Hibernate и динамический SQL и билдеры отчетов. Там даже доступа к исходнику не всегда можно получить.

[Vitsliputsli]

mayton2019, попробовал на MySQL, просто несколько запросов, особо не заморачивался. При выборке по pk, он всегда идет по pk. При выборке по некластеризованному неуникальному индексу, он также идет по нему, но если в таблице уже не 1 строчка (то 4, то 8, причем не вижу связи с длиной) он делает full scan, если % нужных строк падает примерно до 30 он возвращается к индексу.
Вероятнее всего, когда мы делаем full scan нам все равно нужно найти 1 страницу, а ее логично получать по pk, поэтому для единственного значения дешевле всегда идти по pk. Для неуникального индекса сложнее, хз как он расчитывает стоимость, но ради единственной строки он предпочтет индекс. Но это все в MySQL.

[mayton2019]

Vitsliputsli, есть старый фокус селективности. Он больше относится к мангитным дискам. Считается что если вы делаете выборку по индексу и выбираете более 3-5% строк - то использование indexscan уже не эффективно. Проще сделать Fulltablescan.

Как сейчас - не знаю. В эпоху SSD такой параметр как seek time перестал играть роль для индексного поиска.

И чтоб определить эту границу переключения - нужен был CBO. Для SSD я думаю что были подкручены коэффициенты. Всегда проще сделать так чем менять rules. Потому что CBO это как-бы персептрон и менять его поведение лучше коэффициентами чем логикой алгоритма.

[Vitsliputsli]

mayton2019, да, в CBO делают прямо настраиваемые параметры стоимости чтения страниц с диска. Но сейчас много, если не большинство, систем где упор делается на работу в оперативной памяти, и соответственно расчеты СВО должны быть другие, но я без понятия, как он учитывает загружена страница в память или нет.

Answer 1

[rPman]

Зависимость требований ресурсов от количества записей (участвующих в индексах) - примерно логарифм log(N) или если индексы не используются то N*log(N)

Про скорость чтения:

Пока файлы индексов или не иднексируемые данные кешируются в RAM, с увеличением объема данных скорость работы БД будет падать незначительно (время на получение самих данных будет выше чем их поиск), но как только оперативная память закончится (индексы в кеши не влезают) то скорость работы скачкоорбазно упадет.

Про скорость записи:
К сожалению на запись данных в базу данных активно используется диск, соответственно зависимость log(N) сохраняется, но будет с большим коэффициентом от скорости диска на запись.

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

Вот к примеру задача хранения и быстрого доступа к хешам может быть решена быстрее любой БД за счет накладных расходов на дисковое хранилище со скоростями почти равными iops накопителей помноженное на их количество.

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

Answer 2

[Vitsliputsli]

Изменится, но не значительно. И дело не в сетевом лаге, это вообще другой вопрос, а в других расходах на запрос.
Не понял, почему здесь пишут про использование/не использование индекса, в вопросе вы пишите про обращение по id, это надеюсь primary key, а значит в любом случае у нас поиск по этому индексу. Скорее всего как primary key вы используете число (int, bigint и т.п.), а значит у нас индекс BTree (с hash там вообще будет печально с производительностью). Сложность поиска по BTree - это log(n), где n - это порядок дерева. Только вот сложность и производительность не сильно коррелируют. Ведь когда вы будете менять высоту дерева это не просто обратиться к другому адресу в памяти, это нужно как минимум узнать загружена ли эта страница и получить ее адрес. Все это очень усложняет выбор дерева, теоретически не ответишь, нужно смотреть реализацию в MySQL.