Я не специалист по базам данных, я веб-разработчик. И еще один Rails. Большинство моих взаимодействий с базой данных осуществляется через ActiveRecord или Sequel, а необработанный SQL, который я обычно пишу, в основном состоит из SELECT, JOIN и WHERE.

НО я работал с приложением на базе Microsoft SQL Server, что является интересным изменением. Задача привела к задаче, которая привела к задаче, и мне пришлось создать новый столбец с уникальным индексом в существующей таблице, где столбец не может иметь нулевое значение и где не нужно заполнять данные для 100K + записи. В основном я могу делать это в Rails. Я знаю базовый SQL. Я знаю столбцы, значения по умолчанию и NOT NULL. Но в основном я знал об индексах в Rails:

  • Ускоряет поиск ??
  • Делает пишет медленнее ??
  • Есть ли что-то с перестановкой строк базы данных, чтобы увеличить масштаб?

В основном, я знал, что вы просто добавляете одну строчку к миграции, add_index: column_name, а затем Rails что-то делает, и БД увеличивается. Но не добавляйте его ко всему, потому что это не оптимизированная база данных ™.

Некоторый быстрый поиск в Google и некоторые эксперименты привели меня к тому, чтобы узнать, что на самом деле представляют собой кластерные и некластеризованные индексы и как это делать в SQL.

Кластерный индекс

  • Определяет, в каком порядке записи хранятся в таблице
  • Может быть только 1 на стол
  • Обычно по первичному ключу
  • Хочу на уникальных столбцах
  • Самый быстрый поиск, самая медленная запись

Поиск строк по их кластеризованному индексу - самый быстрый шаг, потому что ваша база данных знает точное местоположение той строки, о которой вы говорите. Запись строки, которая «не соответствует порядку» кластеризованного индекса, выполняется медленно, потому что база данных должна переупорядочивать строки в таблице при записи. Помещение кластеризованного индекса в столбец, который не является уникальным, заставляет базу данных добавлять дополнительные «фоновые байты», чтобы определить, в каком порядке должны быть строки с одинаковым значением.

Некластерный индекс

  • Может иметь много на таблицу
  • Более медленный поиск, чем кластерный индекс, но быстрее, чем без индекса
  • Все еще медленная запись, но быстрее, чем изменение порядка кластерного индекса
  • Может быть на неуникальных столбцах

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

И наконец

  • Можно сделать с фильтрами

Это была палочка-выручалочка. Используя некластеризованный индекс в SQL, вы можете создавать уникальные индексы, которые слушают предложения WHERE, что привело к решению…

CREATE UNIQUE NONCLUSTERED INDEX my_column
ON MyTable(my_column)
WHERE my_column <> '';

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

Думаю, это довольно круто.