Как MySQL определяет, уникален ли INSERT?

Я хотел бы знать, существует ли неявный SELECT, выполняющийся до выполнения INSERT в таблице, которая имеет любой столбец, определяемый как UNIQUE. Я не могу найти ничего об этом в документации для INSERT.

Я задал еще несколько вопросов, на которые никто, похоже, не может ответить - возможно, потому, что я не объясняю себя, - которые связаны с вышеупомянутым вопросом.

Если я правильно понимаю, тогда я предполагаю, что следующее будет верно:

CASE 1: У вас есть таблица с 1 миллиардом строк. Каждая строка имеет уникальный столбец UUID. Если вы выполняете вставку, сервер должен сделать неявный SELECT COUNT(*) FROM table WHERE UUID = [new uuid] и определить, является ли счет 0 или 1. Правильно?

CASE 2: У вас есть таблица с 1 миллиардом строк. Каждая строка имеет составной уникальный ключ, состоящий из DATE и UUID. Если вы выполняете вставку, сервер должен выполнить неявный SELECT COUNT(*) FROM table WHERE DATE = [date] AND UUID = [new uuid] и проверить, равен ли счету 0 или 1. Да?

Я использую слово неявное, потому что в какой-то момент, где-то в процессе, сервер ДОЛЖЕН проверять значение. Если бы это не потребовало, чтобы законы физики диктовали, что две идентичные строки не могут существовать, и, насколько мне известно, физика не играет большой роли, когда речь идет о уникальности чисел, записанных где-то, в двоичном, на магнитный диск в компьютере.

Предположим, что ваши 1 миллиард строк одинаково и последовательно распределены по 2000 различным датам. Разве это не означает, что случай 2 будет выполнять вставку быстрее, потому что он может искать UUID, сегментированные в дате? Если нет, то было бы лучше использовать случай 1 для скорости вставки - и в этом случае, почему?

Этот вопрос является теоретическим, поэтому не беспокойтесь, рассматривая регулярную производительность SELECT в этом случае. Первичный ключ не будет индексом UUID + DATE.

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

Ответ 1

В предыдущих ответах есть несколько недостатков и неправильных представлений; вместо того, чтобы указывать на них, я начну с нуля.

Только для InnoDB...

ИНДЕКС (включая УНИКАЛЬНЫЙ И ПЕРВИЧНЫЙ КЛЮЧ) - это BTree. BTrees очень эффективны для размещения одной строки на основе ключа, на котором сортируется BTree. (Он также эффективен при сканировании в ключевом порядке.) "Фан-аут" типичного BTree в MySQL составляет порядка 100. Таким образом, для миллиона строк бит составляет примерно 3 уровня (log100 (млн) ); для триллиона рядов, это только вдвое больше (приблизительно). Таким образом, даже если ничего не кэшировано, для определения одной конкретной строки в индексе с миллионной строкой требуется всего 3 диска.

Я теряюсь здесь с "индексом" по сравнению с "таблицей", потому что они по существу одинаковы (по крайней мере, в InnoDB). Оба являются BTrees. Что отличает то, что находится в листовых узлах: листовые узлы таблицы BTree имеют все столбцы. (Я игнорирую неблокированное хранилище для TEXT/BLOB в InnoDB.) У ИНДЕКСА (кроме PRIMARY KEY) есть копия ПЕРВИЧНОГО КЛЮЧА в листе node. Вот как дополнительный ключ может получить от INDEX BTree до остальных столбцов строки и как InnoDB не должен хранить несколько копий всех столбцов.

ПЕРВИЧНЫЙ КЛЮЧ "сгруппирован" с данными. Это один бит содержит как все столбцы всех строк, так и упорядочен в соответствии с спецификацией PRIMARY KEY.

Поиск записи по PRIMARY KEY - это один поиск в режиме BTree. Поиск записи по SECONDARY KEY - это два запроса BTree, один во вторичном INDEX BTree, который дает вам PRIMARY KEY; затем второй, чтобы развернуть данные /PK BTree.

ПЕРВИЧНЫЙ КЛЮЧ (UUID)... Поскольку UUID является очень случайным, "следующая" строка, которую вы INSERT будет находиться в "случайном" месте. Если таблица намного больше, чем кэшируется в buffer_pool, блок, в который должна идти новая строка, скорее всего, не будет кэшироваться. Это приводит к удару диска, чтобы вытащить блок в кеш (пул буферов), и, в конечном итоге, другой диск попал, чтобы записать его обратно на диск.

Так как PRIMARY KEY является УНИКАЛЬНЫМ КЛЮЧОМ, что-то еще происходит в одно и то же время (нет SELECT COUNT (*) и т.д.). UNIQUEness проверяется после выбора блока и перед тем, как решить, следует ли указывать ошибку "дубликат ключа" или сохранить строку. Кроме того, если блок "заполнен", блок должен быть "разделен", чтобы освободить место для новой строки.

ИНДЕКС (UUID) или UNIQUE (UUID)... Для этого индекса есть БТР. В INSERT некоторые случайно размещенные блоки должны быть извлечены, изменены, возможно разделены и записаны обратно на диск, что очень похоже на обсуждение PK выше. Если у вас был UNIQUE (UUID), также была бы проверка на UNIQUEness и, возможно, сообщение об ошибке. В любом случае, есть и сейчас, и/или позже диск ввода/вывода.

AUTO_INCREMENT PK... Если PRIMARY KEY является auto_increment, то новые записи добавляются в "последний" блок в данных BTree. Когда он заполняется (каждые 100 или около того), существует (логически) разделение блоков и поток старого блока на диск. (На самом деле, ввод/вывод, вероятно, задерживается и выполняется в фоновом режиме.)

ПЕРВИЧНЫЙ КЛЮЧ (id) + UNIQUE (UUID)... Два BTrees. В INSERT есть активность в обоих. Вероятно, это будет хуже, чем просто PRIMARY KEY (UUID). Добавьте вышеприведенные образы дисков, чтобы увидеть, что я имею в виду.

"Диск-хиты" - это убийца в огромных таблицах, и особенно с UUID. "Подсчитайте образы дисков", чтобы получить представление о производительности, особенно при сравнении двух возможных методов.

Теперь для вашего секретного соуса... ПЕРВИЧНЫЙ КЛЮЧ (дата, UUID)... Вы разрешаете тот же UUID появляться в два разных дня. Это может помочь! Вернемся к тому, как ПК работает и проверяет UNIQUEness... Индекс "составной" (дата, UUID) проверяется на UNIQUEness по мере того, как запись вставлена. Записи сортируются по дате + UUID, поэтому все сегодняшние записи группируются вместе. IF (и это может быть большой IF), один день данные вписываются в пул буферов (но вся таблица не работает), то это то, что происходит каждое утро... ВСТАВКИ внезапно добавляют новые записи к "концу" таблицу из-за новой "даты". Эти вставки происходят случайным образом в новую дату. Блоки в buffer_pool выталкиваются на диск, чтобы освободить место для новых блоков. Но, красиво, то, что вы видите, гладко, быстро, INSERT. Это не похоже на то, что вы видели с PRIMARY KEY (UUID), когда многим строкам приходилось ждать чтения диска, прежде чем можно было бы проверить UNIQUEness. Все блоки сегодня остаются в кэше, и вам не нужно ждать ввода-вывода.

Но, если вы когда-либо становитесь настолько большими, что не можете поместить данные дня в буферный пул, все начнет замедляться, сначала в конце дня, затем оно будет ползти раньше и раньше, когда частота INSERT увеличивается.

Кстати, PARTITION BY RANGE (дата) вместе с PRIMARY KEY (uuid, date) имеет несколько схожие характеристики. (Да, я намеренно перевернул столбцы PK.)

Ответ 2

При вставке больших объемов данных в таблицу помните, что данные в конечном итоге физически хранятся на диске. Чтобы действительно читать и записывать данные с диска, MySQL (и большинство других СУБД) использует что-то, называемое кластеризованный индекс, Если вы укажете первичный ключ или уникальный индекс в таблице, столбец или столбцы, участвующие в ключе/индексе, станут кластеризованным индексом. Это означает, что на диске данные физически хранятся в том же порядке, что и значения в столбце (столбцах) ключа.

Используя кластерный индекс, механизм базы данных может быстро определить, существует ли уже существующее значение, без необходимости сканировать всю таблицу. Теоретически, если таблица содержит N = 1.000.000 записей, двигатель в среднем нуждается в log2 (N) = 20 операций, чтобы проверить, существует ли значение, независимо от того, сколько столбцов участвует в индексе. Для вторичных индексов обычно используется B-дерево или хеш-таблица (поиск в Интернете для этих условий, подробное объяснение того, как они работают).

Заключение этой статьи неверно:

"... MySQL не может буферизировать достаточное количество данных, чтобы гарантировать, что значение является уникальным и, следовательно, вызвана огромным количеством чтение для каждой вставки, чтобы гарантировать уникальность"

Это неверно. Проверка уникальности на самом деле не требует дополнительной работы, так как движок должен был найти место для вставки новой записи. Что вызывает замедление производительности, это использование UUID. Помните, что UUID генерируются случайным образом, когда вставлена новая запись. Это означает, что новая запись должна быть вставлена в случайное физическое положение на диске, и это заставляет существующие данные перемещаться вокруг, чтобы разместить новую запись. Если, с другой стороны, индексный столбец является монотонным значением (например, INT с автоматическим инкрементом), новые записи всегда будут вставлены после последней записи, что означает, что никакие существующие данные никогда не будут перемещены.

В вашем случае не будет никакой разницы в производительности между случаем 1 и случаем 2. Но вы все равно столкнетесь с проблемой из-за случайности UUID. Было бы намного лучше, если бы вместо UUID использовалось значение автоматического увеличения. Кроме того, поскольку UUID всегда уникальны по своей природе, на самом деле нет смысла индексировать их с помощью ограничения UNIQUE. Кроме того, если вы действительно должны использовать UUID, убедитесь, что у вас есть первичный ключ в вашей таблице, основанный на автоматическом инкрементном INT, чтобы гарантировать, что новые записи никогда не будут случайно вставлены на диск.

Ответ 3

Это главная цель UNIQUE ограничение:

A UNIQUE index создает ограничение, так что все значения в индексе должны быть разными. Произошла ошибка, если вы попытаетесь добавить новую строку [или обновить существующую строку] с помощью значения , которое соответствует [другой] существующей строке.

Ранее на той же странице руководства было указано, что

Список столбцов формы (col1,col2,...) создает индекс с несколькими столбцами. Значения ключевых слов формируются путем объединения значений данных столбцов.

Как это ограничение реализовано, не документировано, но оно должно как-то отождествлять с предварительным SELECT со значениями, которые нужно вставить/обновить. Стоимость такой проверки часто незначительна, потому что по определению поля индексируются (эти служебные данные становятся актуальными при работе с объемными вставками).

Число столбцов, охватываемых индексом, не имеет смысла с точки зрения производительности (например, по сравнению с количеством строк в таблице). Это влияет на дисковое пространство, занимаемое индексом, но это не должно иметь большого значения в ваших проектных решениях.