Автоинкрементные первичные ключи (суррогатные ключи) = зло? (original) (raw)
В этой статье я приведу взгляд (отрицательный по большей части) Джоша Беркуса, CEO компании PostgreSQL Experts Inc. на использование суррогатных ключей для таблиц базы данных, тех самых INT NOT NULL AUTO_INCREMENT PRIMARY KEY, к которым мы привыкли. Фактически, это будет вольный, сильно сокращенный перевод его статьи на ittoolbox.
За статьей последует разбор моих собственных ошибок по этой теме, допущенных в одном старом проекте. Я был молод и глуп, но это меня не извиняет.
Честно говоря, прочитав эту статью и не заметив, кто автор, я подумал, что он все же преувеличивает и вообще, я без него как-нибудь разберусь, где и какие ключи мне использовать. Потом я еще немного подумал и полез за дампом структуры базы моего старого проекта. Было интересно.
Если вы опытный DBA, наверное, вам стоит пройти мимо, чтобы не расстраиваться.
Но обо всем по порядку. Сначала ОЧЕНЬ сокращенный перевод:
«Суррогатные числовые ключи попали в стандарт SQL89 для сохранения со старыми приложениями, которым требовались номера строк. Впоследствии в разговоре с Джо Селко, Кодд сказал, что жалеет, что допустил это.
Неопытные разработчики, не понимая, что использование суррогатные ключей является прагматичным компромиссом с соображениями производительности, используют их везде. Даже авторы книг по базам данных советуют обязательно создавать их во всех таблицах в любом случае.
В теории реляционных баз данных нет понятия первичных ключей. Все ключи базы данных имеют одинаковую значимость. Понятие первичного ключа базируется на представлении, что один и только один ключ определяет порядок кортежей на диске, а реляционная теория говорит нам о том, что как раз это мы должны игнорировать в логической модели наших данных. Так что, первичные ключи вообще – это нарушение реляционной теории.
Я не говорю о том, что суррогатные ключи нельзя использовать вообще, я говорю о том, что нельзя злоупотреблять их использованием.
Какие причины могут побудить нас использовать суррогатные ключи?
Компромисс с многоколоночными ключами. Обычно, довольно убедительна. Синтаксис SQL запросов с использованием многоколоночных ключей и механизм соединений в настоящее время оставляет желать много лучшего, как и производительность запросов такого рода. Как только эти проблемы будут решены, данная причина отпадет.
У данных нет реального ключа. Очень плохая причина. Ее появление иллюстрирует как плохой дизайн базы данных в целом, таки и то, что разработчик на самом деле не понимает данных, с которыми работает.
Внешние требования. Обычно убедительна. Как правило, среды разработки и инструменты для работы с базами данных поддерживают только суррогатные ключи. И если вы считаете, что данный инструмент незаменим в проблеме, которую вы решаете, что ж…
Согласованность данных. Обычно убедительна. Но только в том случае, если вы действительно скрупулезно следуете плану и весь ваш дизайн тщательно спланирован.
Следование SQL стандарту и принципам хорошего дизайна. Очень плохая причина. Она полностью основана на невежестве. Обычно, ей следуют потому, что где-то услышали, как кто-то прочитал в блоге кого-то, кто учится в УНИВЕРСИТЕТЕ, что использование суррогатных ключей – это стандарт в индустрии. Имейте ввиду, что ни современные стандарты SQL, ни сама реляционная теория не содержит даже упоминания о суррогатных ключах.
Возможность легкого изменения. Неясно. Действительно, некоторые СУБД не умеют выполнять ON UPDATE CASCADE или делают это слишком неэффективно (кстати, подумайте об этом как о причине смены СУБД). И в этом случае, данная причина может оказаться весомой. Однако иногда разработчики говорят о том, что ключи [первичные] для записи меняться не должны и должны оставаться одинаковыми на всей протяженности жизненного цикла записи. Имейте ввиду, что это утверждение яйца выеденного не стоит и уж, разумеется, полностью отсутствует в реляционной теории.
Производительность. Обычно плохая причина. Да, действительно, могут возникать ситуации, в которых использование естественных ключей сильно замедляет работу системы по сравнению с суррогатными. Но в 80% случаев за этим утверждением не стоят реальные тесты и подобное утверждение остается безосновательным. Предварительная оптимизация – корень многих бед в дизайне баз данных.
Для баз данных мега-объема результирующий размер таблицы также может иметь значение. Но для этого база должна быть уж очень большой.
Производительность соединений или сортировки также имеет значение на большом количестве данных, в зависимости от типа первичного ключа и числа его компонентов. Однако мой опыт показывает, что когда называют эту причину, за ней очень редко стоят реальные расчеты или замеры производительности. Например, www.bricolage.cc использует 14-байтовые числовые первичные ключи для своих таблиц много лет. Однако и в этом случае, после появления пользователя с трехмиллионной записью в истории, когда встал вопрос об изменении первичных ключей ради производительности, эта проблема была решена переписыванием запросов. Было достигнуто примерно 10-кратное увеличение производительности.
Обратите внимание, что проблемы причиняет не использование суррогатных ключей, а злоупотребление ими».
Конец моего ОЧЕНЬ СОКРАЩЕННОГО перевода. Оригинал тут (Primary Keyvil называется): it.toolbox.com/home/search.aspx?r=%22Primary+kevill%22&community=1&contentType=5
Если я упустил что-то важное в переводе, пожалуйста, скажите мне об этом. Добавлю.
Теперь немного о том, что я сам думаю.
Все же статья показалось мне немного драматизирующей проблему. Мне кажется, что суррогатные ключи выбираются все чаще всего именно из-за того, чтобы избежать проблем с производительностью впоследствии и в последнее время все так к ним привыкли, что они насаждаются на уровне самих СУБД. Например, InnoDB, если вы не создадите первичный ключ, просто создаст его сама. Кстати, в случае с InnoDB выбор первичного ключа имеет серьезные последствия с точки зрения производительности, поскольку по нему производится кластеризация (соответственно, выбор естественного ключа может как улучшить, так и ухудшить ситуацию).
Несмотря на то, что статья звучит так, будто суррогатные ключи суть воплощенное зло, автор несколько раз подчеркивает, что проблему несет не их использование, а злоупотребление ими.
Эта статья открыла мне глаза в том плане, что я всегда считал естественным не искать себе особых кандидатов в первичные ключи, а просто создать INT NOT NULL AUTO_INCREMENT PRIMARY KEY поле и сидеть спокойно. Разумеется, я знал о том, что в качестве первичного ключа можно выбрать любой уникальный ключ, но я никогда на этом не акцентировался. Я никогда особо не задумывался о том, что вообще по-настоящему делает данную строку базы данных уникальной и почему это важно. Как выяснилось, зря.
В качестве примера я хочу вам привести свой небольшой старый проект. Там всего несколько таблиц. Вначале я хотел выбрать что-то побольше, но думаю, что это лишнее. Только напрасно отниму у вас время. Пусть каждый сам откроет какой-нибудь свой старый проект и посмотрит на него с точки зрения описанной позиции. Я там на самом деле добавил одну ошибку сейчас справедливости ради. Я бы ее все равно сделал. Меня спасла только случайность.
Проект представляет собой некоторый закрытый торрент-трекер. Я прошу вас не обращать сейчас внимание на проблемы с нормализацией и всякие другие. Если бы я писал его сейчас, может быть, кое-что я бы сделал по-другому. Давайте сосредоточимся на суррогатных ключах.
Структура базы данных
Первая таблица, о которой я бы хотел поговорить, это таблица логов. Вообще, именно этот случай меня немного ошарашил что ли, поскольку я неожиданно увидел ошибку. Совсем небольшую, не стоящую особого внимания, но, тем не менее, это ошибка, которой я не замечал много-много лет. Совсем не замечал. Отвлекитесь сейчас от текста и вернитесь к структуре этой таблицы. Видите? Я не видел.
В этой таблице хранится простая информация. IP, ID пользователя, дата возникновения события и его текст. Да, конечно, текст можно было заменить кодом и сделать много еще чего, но речь сейчас не об этом. После прочтения статьи, я посмотрел на эту таблицу и подумал, что, вот я создал суррогатный ключ. Но каков реальный ключ данных? Что делает конкретную строку таблицы уникальной?
Ответ очень простой. Комбинация из ID пользователя и времени возникновения события. И вот тут я неожиданно увидел ситуацию с другой стороны. Практически во всех моих старых проектах поле DATETIME используется для хранения времени в логах. Просто потому, что это удобно. Да, я знал, что оно хранится с точностью до секунды и меня это полностью устраивало. Сейчас, когда я начал искать естественные ключи, мне неожиданно пришло в голову, какие последствия это несет. Торрент-трекер, о котором идет речь, нагружен очень сильно и в течение секунды многое может произойти. Фактически, если в логе с этим чертовым суррогатным ключом окажется несколько событий с одним и тем же временем и они произошли друг за другом очень быстро, я смогу сказать, какое из них произошло первым, а какое последним только ориентируясь по автоинкременту суррогатного ключа. Само поле информации о дате, которое создано именно затем, чтобы сообщать такие вещи, мне ничем не поможет. А точно выяснить интервал между событиями я не смогу вообще.
В целом, это, конечно, неважно. Вероятность того, что мне потребуется выяснить интервал между двумя событиями, который в любом случае составляет менее секунды, весьма невелика. Но все свои проекты, и старые и новые, я всегда рассматриваю как учебные. Проект мог быть немного другим, и это могло стать важным.
Я хочу сказать, что рассмотрение проблемы с точки зрения поиска естественного ключа – это взгляд несколько с другой стороны. Попробуйте взглянуть на дизайн вашего проекта таким образом и посмотрите, что обнаружится.
Похоже, что мое объяснение получилось сумбурным. Надеюсь, все же, мне удалось донести до вас мою мысль.
Теперь таблица peer. У нее уже есть уникальный ключ, который просто просится на роль первичного. В таблицу peer производятся многие сотни вставок/удалений в секунду и держать там лишний индекс в виде первичного ключа просто накладно. Вот я его и ликвидировал.
Таблица session. По некоторой причине я не полагался на сессии PHP полностью, а частично реализовал свои. В качестве первичного ключа этой таблицы выступает случайное значение. Мало того, что это просто глупо использовать 40-символьные случайные последовательности, так оно тут вообще не очень нужно. Что выступает в качестве естественного ключа для записей в этой таблице? В этом проекте пользователю не позволялось быть залогиненым с нескольких компов одновременно. Гм. user_id? Все остальное по отношению к данному значению вторично. Я не буду сейчас анализировать, что вытекает из этого простого утверждения. Много чего вплоть до удаления таблицы сессий и реализации другого механизма. Вариантов тут много.
Перейдем к таблице torrent.
Небольшое отступление для того, чтобы вы представляли себе предмет обсуждения. Торрент-трекер, который я разрабатывал, одновременно являлся и первым сидом для файлов, которые раздавались. В таблице torrent хранилась информация о файлах, которые сидировались. Эти файлы лежали в файловой системе сервера, для них создавались соответствующие .torrent файлы по схеме один файл = один торрент, которые и скачивались пользователями. Каждый торрент имеет так называемый info_hash, который его уникальным образом идентифицирует.
Это поле в таблице peer называется peer_info_hash. А в таблице torrent это поле torrent_info_hash. torrent_id там лишний. Совсем. Обратите внимание, что в таблице peer torrent_id тоже есть. Непонятно зачем.
Ну и таблица user. Казалось бы, тут я просто не мог сделать ошибки. Ошибался.
В системе авторизации, торрент-трекеров используется GET параметр с уникальным для пользователя значением. В таблице это значение user_torrent_uid. Вот спросите меня, кто мешал использовать это значение в качестве естественного ключа в том или ином варианте? Да, оно может измениться. В очень редком случае. Ну и что? Если 8 байт – слишком длинно, можно было взять обычный случайный INT и конвертировать его в текст, как на Flickr делают умные люди. Можно было… Да много чего можно было.
Вот так. Все очевидно, не так ли? :)