Вопрос по ruby-on-rails, activerecord, sql, indexing – Индекс для нескольких столбцов в ActiveRecord

45

В ActiveRecord есть два способа объявить индексы для нескольких столбцов:

add_index :classifications, [:species, :family, :trivial_names]
add_index :classifications, :species
add_index :classifications, :family
add_index :classifications, :trivial_names

Есть ли разница между первым подходом и вторым? Если да, когда я должен использовать первый, а когда второй?

Спасибо, Collimarco, за выбор моего ответа в качестве & quot; ответа. & Quot; Mark Brittingham

Ваш Ответ

3   ответа
10

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

Индексирование трех столбцов [A, B, C] хорошо работает, когда вам необходим доступ к значениям A, A + B и A + B + C. Это не будет хорошо, если ваш запрос (или найти условия или что-то еще) не ссылается на A.

Когда A, B и C индексируются по отдельности, некоторые оптимизаторы запросов к СУБД рассматривают возможность объединения двух или более индексов (в зависимости от оценки эффективности оптимизатора) для получения аналогичного результата для одного индекса из нескольких столбцов.

Предположим, у вас есть система электронной коммерции. Вы хотите запросить заказы по purchase_date, customer_id и иногда обоим. Я бы начал с создания двух индексов: по одному для каждого атрибута.

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

1

Из документов:

When creating an index on multiple columns, the first column is used as a name for the index. For example, when you specify an index on two columns [:first, :last], the DBMS creates an index for both columns as well as an index for the first column :first. Using just the first name for this index makes sense, because you will never have to create a singular index with this name.

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

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

Да, это должно быть хорошо.
Обновил мой ответ
Спасибо! Но ... когда имеет смысл использовать составной индекс вместо индекса для одного атрибута? Можете ли вы дать мне несколько примеров? collimarco
Вы сказали, что не используйте их в качестве замены, но что, если я использую их только в этом запросе: ВЫБРАТЬ * ИЗ КЛАССИФИКАЦИЙ, ГДЕ БЫВАЕТ ПРОДУКТ? ИЛИ семья как "% sth%" ИЛИ trivial_names LIKE '% sth%' В этом случае правильно ли использовать только составной индекс? collimarco
89

осто разные.

Подумайте об этом так: составной индекс дает вам быстрый поиск первого поля во вложенном наборе полей с последующим быстрым поиском второго поляwithin ONLY the records already selected by the first fieldс последующим быстрым поиском третьего поля - опять же, только в записях, выбранных двумя предыдущими индексами.

Давайте возьмем пример. Ваш механизм базы данных сделает не более 20 шагов, чтобы найти уникальное значение в пределах 1 000 000 записей (если память служит)if Вы используете индекс. Это верно независимо от того, используете ли вы составной или независимый индекс, но ТОЛЬКО для первого поля («виды» в вашем примере, хотя я думаю, что вам нужны семья, виды, а затем общее имя).

Теперь предположим, что существует 100 000 подходящих записей для этого первого значения поля. Если у вас есть только отдельные индексы, то любой поиск в этих записях займет 100 000 шагов: по одному для каждой записи, полученной по первому индексу. Это связано с тем, что второй индекс не будет использоваться (в большинстве баз данных - это немного упрощение) и должно использоваться грубое совпадение.

Если у тебя естьcomposite index тогда ваш поиск будет намного быстрее, потому что ваш поиск по второму полю будет иметь индексwithin первый набор значений. В этом случае вам потребуется не более 17 шагов, чтобы получить ваше первое совпадающее значение в поле 2 из 100 000 совпадений в поле 1 (основание журнала 2 из 100 000).

Итак: шаги, необходимые для поиска уникальной записи из базы данных, состоящей из 1 000 000 записей, с использованием составного индекса по 3 вложенным полям, где первое возвращает 100 000, а второе - 10 000 = 20 + 17 + 14 = 51 шагов.

Шаги, необходимые при тех же условиях, только с независимыми индексами = 20 + 100 000 + 10000 = 110 020 шагов.

Большая разница, а?

Сейчас,don't сойти с ума, поставив композитные индексы повсюду. Во-первых, они дорогие по вставкам и обновлениям. Во-вторых, они используются только в том случае, если вы действительно выполняете поиск по вложенным данным (для другого примера я использую их при извлечении данных для имен входа для клиента за указанный диапазон дат). Кроме того, они не стоят того, если вы работаете с относительно небольшими наборами данных.

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

Кстати, планы выполнения SQL доступны в более сложных базах данных, таких как SQL Server и Oracle, и могут быть весьма полезны как в качестве учебного пособия (чтобы помочь вам увидеть, что происходит под покровом, как попытка БД оптимизировать свой план поиска), так и как механизм для тестирования различных стратегий индексации.
collimarco - в примере, который вы предоставляете г-ну Мэтту, независимые индексы обеспечат лучшую производительность, поскольку каждый из них будет использоваться независимо как часть плана выполнения SQL. Думайте об этом следующим образом: И является композиционным, ИЛИ независимым. В качестве другого примера, если ваше предложение where было «WHERE (Семейство = X AND Species = Y) ИЛИ (CommonName = Z)» quot; затем вам нужен составной индекс по семейству | видов и независимый индекс по общему имени.
Спасибо за объяснение! Посмотрите, что я спросил, мистер Мэтт: предложение WHERE содержит OR. В этом случае полезно ли составной индекс? Я бы сказал нет, потому что БД всегда должен искать все элементы, а не только строки, которые получаются из первого условия (это было бы иначе, если бы существовал оператор AND, потому что он «фильтрует» строки и уменьшает область видимости). ). Я ошибся? collimarco

Похожие вопросы