Статьи

Уникальный индекс и его обоснование

Октябрь 22, 2018

Эта статья вдохновлена ​​недавним постом Крейга « Как использовать уникальные индексы в MySQL и других базах данных ».

Есть несколько более тесно связанных и не менее важных аспектов, касающихся Первичного ключа (PK) и Уникального индекса (UI), которые не были рассмотрены в этой статье.

Поэтому в этой статье мы подробнее рассмотрим эти области.

Авто приращение как ПК

Обычной практикой для разработчиков баз данных является использование автоинкремента в качестве PK для конкретной таблицы.

Как определение говорит нам:

Ограничение PRIMARY KEY однозначно идентифицирует каждую запись в таблице базы данных.

Чтобы быть точным, PK существует, чтобы сообщить базе данных, что запись уникальна.

Использование автоинкремента в качестве PK — простой, но ленивый способ сделать это. Запись с id=1 отличается от записи с id=2 . Вот и все.

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

Возьмите типичную запись для хранения товара, например. Может включать в себя следующие поля:

  • id (автоинкремент)
  • номер счета
  • имя клиента (или идентификатор)
  • Дата вступления
  • название предмета (или идентификатор)
  • количество
  • Цена за единицу
  • промежуточный итог (вычисляемое поле)

«Естественная» структура приведет нас к использованию id в качестве поля автоинкремента и, следовательно, в качестве PK. Я совсем не уверен, что этого будет достаточно.

Рассмотрим следующие две записи: 

 1 12345 GoodGuy 13-12-12 item1 2 199 398 2 12346 GoodGuy 13-12-12 item1 2 199 398

Они уникальны или просто дублированы? Я предпочел бы рассматривать их как дублированные записи, когда человек, который вводит записи, случайно набрал номер счета-фактуры, по крайней мере, для одной из записей.

Кроме того, в этом примере мы должны утверждать, что даже invoice number не был бы хорошим кандидатом на роль PK или unique index .

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

Составной уникальный индекс

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

Общая логика может быть:

  1. Если купленные предметы отличаются, то, безусловно, это разные предметы продаж;
  2. Если купленные товары одинаковы, но с разным клиентом, датой, количеством, они разные.
  3. …

В зависимости от ситуации возможны дополнительные подходы.

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

 ALTER TABLE `test`.`salesitem` ADD UNIQUE INDEX `uniqueentry` (`itemid` ASC, `clientid` ASC, `date` ASC, `quantity` ASC);

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

ПРИМЕЧАНИЕ. Имейте в виду, что дублирование, обнаруженное индексом uniqueentry весьма вероятно . В реальных случаях они не могут дублироваться в конце концов (скажем, GoodGuy только что разместил повторный заказ в тот же день с точно такими же предметами). В этом случае разработчик базы данных должен работать с продавцом и дополнительно обсудить, какие другие поля следует ввести в структуру таблицы и уникальный состав индекса, чтобы различать две записи.

ПРИМЕЧАНИЕ. Чтобы избежать дублирования записи в номерах счетов, настоятельно рекомендуется создать еще один уникальный индекс на основе поля invoice number .

В сочетании с двумя вышеупомянутыми уникальными индексными ограничениями мы избежали таких человеческих ошибок, как:

  1. Опечатка в номере счета, которая приводит к дублированию номера счета;
  2. Опечатка в номере счета-фактуры, в которой дублируется запись в таблице с тем же товаром, клиентом, датой и количеством;

Мы не можем устранить все человеческие ошибки с помощью нашего дизайна. Он находится в сфере разработки бизнес-процессов и полагается на вспомогательное оборудование (например, сканер для сканирования номера счета-фактуры, чтобы избежать ручного ввода) для повышения точности.

Эмпирические правила:

  1. В любом реальном приложении никогда не полагайтесь на PK с автоматическим приращением для определения уникальности записи;
  2. Не полагайтесь на одно искусственное поле (в данном случае номер счета) для проверки уникальности.
  3. Поля в составном уникальном индексе должны быть теми, которые физически существуют и генерируются бизнес-операциями. В нашем примере выше дата, элемент (который косвенно упоминается как ограничение внешнего ключа для нашей таблицы product ), количество и клиент (который косвенно упоминается как ограничение внешнего ключа для нашей таблицы client ) имеют эти характеристики.

Далее обратите внимание на 3 выше, поэтому я не люблю использовать UUID в качестве PK или UI. Помимо сложности вычисления UUID, сам UUID не имеет соответствующего реального атрибута для записи.

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

Обоснование существования автоинкремента ПК

Сказав все это и настроив два уникальных индекса, мы все равно можем использовать поле автоинкремента в качестве PK. Почему?

Ответ прост: упростить расположение данных, сделать их проще и быстрее.

В нашем примере, чтобы найти одну запись, мы можем либо использовать select * from sales_entry where id=1 либо использовать select * from sales_entry where invoice='...' либо использовать select * from sales_entry where itemid=... and clientid=... and date='...' and quantity=... Очевидно, что поиск через целое число будет самым быстрым для выполнения и самым простым для записи.

Кроме того, автоматическое увеличение PK поможет другим таблицам в базе данных иметь более простую настройку внешнего ключа для обеспечения целостности данных. По-прежнему рекомендуется устанавливать поле автоинкремента как PK, но настоятельно НЕ рекомендуется полагаться на PK для определения уникальности записи.

Порядок полей в составном (уникальном) индексе

Дизайн базы данных и ее таблиц — это искусство. Особенно, когда мы разрабатываем индексы каждой таблицы.

После вышеуказанной настройки в нашей таблице уже есть 3 индекса:

  1. Один ПК с использованием поля id автоинкремента.
  2. Один пользовательский интерфейс с использованием поля invoice .
  3. Один пользовательский интерфейс, использующий комбинацию нескольких полей.

Давайте снова посмотрим на SQL, который создает третий индекс: 

 ADD UNIQUE INDEX `uniqueentry` (`itemid` ASC, `clientid` ASC, `date` ASC, `quantity` ASC);

и рассмотрим следующий оператор SQL и соответствующий ему EXPLAIN выходной отрывок:

select * from salesitem where clientid=1

Таблица еще не имеет индекса для clientid, поэтому приведенный выше SQL не сможет использовать какие-либо индексы ( possible_keys = null ) для ускорения запроса и должен будет выполнить полное сканирование таблицы для извлечения записи ( Extra = Using Where ) ,

select * from salesitem where itemid=1 and clientid=1 and date='13-12-14'

Этот оператор SQL содержит несколько фильтров, и они находятся в строгом порядке, в котором мы создали наш индекс uniqueentry . Он использует все преимущества нашего пользовательского интерфейса, и мы можем ожидать от него максимальной скорости.

select * from salesitem where itemid=1 and clientid=1 and quantity=1

Этот оператор SQL содержит несколько фильтров, и они НЕ находятся в строгом порядке, в котором мы создали наш индекс uniqueentry . MySQL все еще старается использовать пользовательский интерфейс, и мы можем ожидать умеренной скорости.

select * from salesitem where itemid=1 and date='13-12-14' and quantity=1 and clientid=1

Это плохо. 4 поля пользовательского интерфейса находятся в SQL, но в полностью искаженном порядке. Он также вызовет полное сканирование таблицы для извлечения записи, и самое неудачное, после того, как мы просмотрим всю таблицу, записи не найдены!

EXPLAIN — очень полезная команда в MySQL и MariaDB. Чтобы его использовать, просто выполните EXPLAIN *your sql statment to run* в терминале MySQL или в приложениях с MySQL GUI, таких как PhpMyAdmin.

При разработке составного индекса будьте осторожны с порядком полей в индексе.

Наш uniqueentry индекс создается из 4 полей в строгом порядке: позиция, клиент, дата, количество .

В нашем операторе SQL, если наше предложение where соответствует полному или частичному (например, 2-му и 3-му SQL) порядку составных полей индекса, MySQL будет достаточно умен, чтобы соответствовать самой длинной цепочке полей в составном индексе, чтобы ускорить поиск , В противном случае MySQL не сможет найти соответствующий индекс и должен будет выполнить полное сканирование таблицы, что всегда не рекомендуется при разработке баз данных.

uniqueentry полей нашего uniqueentry пользовательского интерфейса может быть бесполезен, если наша программа будет чаще всего запрашивать историю покупок клиента (как в нашем 1-м операторе SQL). Если это так, нам нужно либо изменить порядок полей в uniqueentry пользовательском интерфейсе, либо создать новый (составной) индекс, в котором clientid единственным (или первым) полем в этом индексе.

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

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

Вывод

В этой статье мы более подробно остановились на некоторых основных и ключевых концепциях при разработке таблицы и ее индексов. В частности, мы много внимания уделяли тому, как определить уникальность записи, составив содержательный составной уникальный индекс. Мы также немного объяснили, как правильно разработанный составной индекс может использоваться MySQL для ускорения запроса.

В следующих статьях мы рассмотрим еще один важный аспект базы данных: хранимые процедуры и их использование в PHP.

Будьте на связи!

43
Share: