MCP

четверг, 7 апреля 2016 г.

Производительность SQL при поиске по ключу в зависимости от типа

Последнее время в проектах для SQL-таблиц в качестве идентификатора мы используем гуиды. Ибо уникально, не нужны идентити, и можно идшники генерить на клиенте (причём сразу для всего дерева объектов), а потом вставлять в базу (при этом, хоть асинхронно).

Но вот тут у меня закралась мысль, а насколько эффективно SQL в дальнейшем работает с данными типами? И не будут ли голые инты быстрее во всяких селектах и джойнах, да так, что перекроют с лихвой все недостатки при вставке (ведь в основном все селектят, это обычно важнее, чем вставка).

Тестировать можно долго и по-разному, но для простоты, я ограничился следующими условиями:

  • Таблицы с Id и Value (чтобы совсем грустные не были
  • Id типов int, bigint и guid (т.е. 4, 8 и 16 байт, меньшие типы несерьёзно тестировать)
  • Id - кластерный индекс и первичный ключ
  • Заполнение по Identity и по рандому (чтобы проверить, влияет ли более разреженный индекс на результаты). Для гуидов идентити эмулировалось вручную, генерацией "правильных" данных 
  • Заранее вставил 1000000 записей в каждую таблицу
  • Селекчу много раз определённую запись (глупо, но SQL ведь особых чудес ловкости отваги проявить тут не сможет, впрочем, попробовал разные записи, результат похож)
В качестве подходящей незагруженной железяки (чтобы не влияли на результаты) оказался сервер с SSD, достаточным количеством памяти, но с SQL 2008. Не очень свежо, но не думаю, что что-то менялось на подобных запросах. Впрочем, позднее перетестирую, и если результаты изменятся — обновлю пост.

В общем, замерил результаты и.... они оказались практически идентичными. Конечно, разница есть в пределах 3%, но это несущественно. Впрочем, смотрите картинку сами:
Вам может показаться, что разница существенная и в пользу инта, давайте немного поправим масштаб картинки:


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

Но дальше я решил разнообразить тесты и сделать точно такие же таблицы, но без ключей (не повторять самостоятельно, опасно для жизни!). Результаты оказались слегка обескураживающими
Инт тут не просто в пролёте, он в почти в 3 раза медленнее гуида! А бигинт самый быстрый (что, в принципе не так уж и странно, но не в случае такого провала с интом).
Вначале я думал, что табличке с интами не повезло и она нарвалась на длинный фулскан, пробовал селектить разные записи — результат идентичный. В чём дело, я не понимаю. Возможно проблемы SQL сервера, а возможно так и задумано, тут я не специалист. В общем, надо доисследовать, но ситуация очень неординарная. Бигинты круче интов в несколько раз...

Ну и напоследок размер таблиц. Понятно, что это всё сильно depends и от данных и от наполненности индекса, но общее представление можно поиметь:


Тут всё просто и логично, снос кластерного индекса экономит 4-5 мегабайт на миллионе записей, бигинт больше инта где-то на 4 мегабайта, гуид больше бигинта на 8-9 мегабайт в зависимости от наличия/отсутствия индекса.

Update: Обновил SQL Server с 2008 до 2014. Результаты: Поиск по индексу стал где-то на 40% быстрее (смысл обновляться оказывается имеется!), Поиск по таблицам без первичного ключа: с интами ничего не изменилось, гуиды стали быстрее на 30%, т.. практически сравнялись с бигнитами. Т.е. проблемы те же самые на месте, но гуиды стали ещё более привлекательнее. Картинка с данными:

PS: OLTP-таблицы дают офигенный прирост скорости для таблиц без индекса, но OLTP для таблиц с индексом даёт те же результаты в моём случае. В общем, OLTP, это предмет отдельного поста, просто, раз обновился, решил глянуть.

7 комментариев:

  1. Cуществует еще 32 битный сиквел сервер? Предположу, что из-за того что инт в сиквел сервере даже в 64 битном - это 32 бита, то, скорее всего, из-за оптимизации по памяти они получают проигрышь. В общем, было бы интересно посмотреть как этот тест будет вести себя на 32 битном сервере.

    Что-то как то мало ты для теста данных используешь. Миллион записей - это всего 12 мегабайт для индекса в случае гуида. Вот если будет в 1000 раз больше записей тогда и увидишь разницу, что у тебя будет 12 гигабайт вместо 6 гигабайт (сравнение 64 битного с гуидом), либо 3 гигабайта в случае 32 битного инта. Вот тогда и получишь значительную разницу, если не будет возможности держать это все в памяти.

    ОтветитьУдалить
    Ответы
    1. Я был удивлён, но оказывается 32хбитный SQL ещё живёт. Проверять, наверное не буду, ибо не настолько хочется развлекаться с этой проблемой, а установка скуля - муторный квест :)
      Мы тоже думаем, что проблема с тормозами из-за кривого выравнивания данных, но опять же - только идея.

      По поводу данных — угу, тут мало. Просто при большом объёме данных уже будет сложно получить достоверные и повторяющиеся результаты (слишком много кешей будут играть роль). Да и в случае, когда у тебя сам индекс на 12 гигабайт, база должна терабайтами меряться, это уже весьма специфический кейс, в котором надо действтельно думать про экономию, причём каждое поле смотреть отдельно, и id тут будут не главными.

      Ещё посоветовали погонять человеческие стандартизированные тесты для произвоидетельности баз, играясь в них с типом ключей. Может займусь, чтобы понять результаты.

      Удалить
    2. Не думаю, что ты увидишь заметную разницу в производительности. Единственное о чем бы я заботился - это о размере индекса, но так как ты не ожидаешь видеть такие большие таблицы - я бы не стал обращать на это внимание.
      А баг насчет int я бы завел в SQL Server.

      Удалить