Компонентные индексы: в первую очередь, для большинства избирательных столбцов?

Я читал о composite indexes, и я немного запутался в заказе. Эта документация (немного меньше, чем на полпути вниз) говорит

  

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

Однако вскоре после этого

  

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

Oracle также говорит, что здесь в других слова

  

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

Однако я нашел ответ SO , который говорит по-разному. В нем говорится:

  

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

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

Этот документация также говорит о skip scanning и говорит

  

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

Еще статья говорит

  

Столбец префикса должен быть наиболее дискриминационным и наиболее широко использоваться в запросах

, который, по моему мнению, наиболее дискриминирующий будет означать самый отличительный.

Все эти исследования все еще приводят меня к одному и тому же вопросу; Должен ли самый избирательный столбец быть первым или последним? Должен ли первый столбец наиболее использоваться и только самый избирательный на тай-брейке?

Эти статьи, кажется, противоречат друг другу, но они предлагают несколько примеров. Из того, что я собрал, для least selective column кажется более эффективным первым в заказе если вы ожидаете Index Skip Scans. Но я не уверен, что это правильно.

11 голосов | спросил Eric S 11 Jam1000000amWed, 11 Jan 2017 03:33:43 +030017 2017, 03:33:43

6 ответов


6

Из AskTom

  

(в 9i появляется новое «проскальзывание индекса» - поиск того, что там читается об этом. Он делает индекс (a, b) OR (b, a) полезным в обоих случаях выше !)

     

Итак, порядок столбцов в вашем индексе зависит от того, как написаны ваши вопросы. Вы хотите иметь возможность использовать индекс для как можно большего количества запросов (чтобы сократить все количество индексов, которые у вас есть), которые будут управлять порядком столбцов. Ничего другого (селективность a или b вообще не учитывается).

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

SQL> create table t as select * from all_objects;

Table created.

SQL> create index t_idx_1 on t(owner,object_type,object_name);

Index created.

SQL> create index t_idx_2 on t(object_name,object_type,owner);

Index created.

SQL> select count(distinct owner), count(distinct object_type), count(distinct object_name ), count(*)  from t;

COUNT(DISTINCTOWNER) COUNT(DISTINCTOBJECT_TYPE) COUNT(DISTINCTOBJECT_NAME)      COUNT(*)
-------------------- -------------------------- --------------------------      ----------
                 30                         45                       52205      89807

SQL> analyze index t_idx_1 validate structure; 

Index analyzed.

SQL> select btree_space, pct_used, opt_cmpr_count, opt_cmpr_pctsave from index_stats;

BTREE_SPACE   PCT_USED OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
----------- ---------- -------------- ----------------
    5085584     90          2           28

SQL> analyze index t_idx_2 validate structure; 

Index analyzed.

SQL> select btree_space, pct_used, opt_cmpr_count, opt_cmpr_pctsave  from index_stats; 

BTREE_SPACE   PCT_USED OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
----------- ---------- -------------- ----------------
    5085584     90          1           14

Согласно статистике индексов, первый индекс более сжимаем.

Другим является то, как индекс используется в ваших запросах. Если ваши запросы в основном используют col1,

Например, если у вас есть запросы типа-

  • select * from t where col1 = :a and col2 = :b;
  • select * from t where col1 = :a;

    -then index(col1,col2) будет работать лучше.

    Если ваши запросы в основном используют col2,

  • select * from t where col1 = :a and col2 = :b;
  • select * from t where col2 = :b;

    -then index(col2,col1) будет работать лучше. Если все ваши запросы всегда указывают оба столбца, то не имеет значения, какой столбец будет первым в составном индексе.

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

    Литература:

  • Порядок столбцов в индексе
  • Менее эффективен для того, чтобы иметь слабые ведущие столбцы в индексе (справа)?
  • Index Skip Scan - содержит ли индексную колонку порядок вещей больше? (Предупреждающий знак)     
  • ответил JSapkota 24 Jam1000000amTue, 24 Jan 2017 09:00:59 +030017 2017, 09:00:59
    3

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

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

    Если есть таблица ADDRESS, с

    СТРАН СТРАНЫ СИТИ, еще что-то ...

    индексация STREET, CITY, COUNTRY даст самые быстрые запросы с именем улицы. Но, запрашивая все улицы города, индекс будет бесполезен, и запрос, скорее всего, сделает полное сканирование таблицы.

    Индексирование COUNTRY, CITY, STREET может быть немного медленнее для отдельных улиц, но индекс можно использовать для других запросов, только выбрав страну и /или город.

    ответил Erik Hart 14 Jpm1000000pmSat, 14 Jan 2017 20:19:23 +030017 2017, 20:19:23
    1

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

    , например:

    1. какой тип оператора запроса используется: если запросы имеют такие операторы, как: "& gt ;,> =, & lt ;, <="
    2. Сколько фактических строк ожидается в результате запроса: Результат запроса будет состоять из большинства строк из таблицы.
    3. Используются ли какие-либо функции в столбце таблицы во время предложения Where: Если в запросе есть функция UPPER, LOWER, TRIM, SUBSTRING, используемая на столбец, используемый в состоянии WHERE.

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

    1. "90% типов запросов в данной таблице имеет предложение WHERE с оператором ="
    2. "в большинстве запросов возвращается 10% итоговых строк в таблице в результате"
    3. "никакие функции не используются в столбце таблицы в предложении WHERE"
    4. "большинство столбцов времени в используемом WHERE разделе в основном имеют номер типа,
      строка "

    По моему опыту, оба DBA должны помнить.

      

    Предположим, что применяется только одно правило:

    1) Если я создаю индекс с наиболее избирательным столбцом, первым, но этот столбец фактически не используется большинством запросов в этой таблице, чем он не подходит для движка db.

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

    Я расскажу о столбцах, которые в основном используются в 90% запросов таблицы. Затем поместите их только в порядке большей мощности на наименьшую мощность.

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

    ответил Anup Shah 31 Jam1000000amTue, 31 Jan 2017 02:06:58 +030017 2017, 02:06:58
    1

    В теории наиболее избирательный столбец дает самый быстрый поиск. Но на работе я просто наткнулся на ситуацию, когда у нас есть составной индекс из трех частей с самой селективной частью. (дата, автор, издательская компания, скажем так, в этом порядке, таблицы контролируют большие пальцы на сообщениях), и у меня есть запрос, который использует все 3 части. Mysql по умолчанию использует индекс onlny автора, пропускающий составной индекс, содержащий компанию и дату, несмотря на то, что они присутствуют в моем запросе. Я использовал индекс силы, чтобы использовать композит, и запрос действительно работал медленнее. Почему это произошло? Я скажу вам:

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

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

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

    ответил Joe Yahchouchi 20 J0000006Europe/Moscow 2018, 13:23:27
    -2

    Различные случаи для разных ситуаций. Знай свою цель; затем создайте свои индексы и запустите планы объяснений для каждого, и у вас будет лучший ответ для вашей ситуации.

    ответил RMPJ 14 Jam1000000amSat, 14 Jan 2017 07:47:36 +030017 2017, 07:47:36
    -2

    Из Порядок столбцов в индексе на странице Ask Tom:

      

    Итак, порядок столбцов в вашем индексе зависит от того, КАК ВАШИ ЗАПРОСЫ   написаны. Вы хотите иметь возможность использовать индекс для такого количества запросов   как вы можете (чтобы сократить все количество индексов, вы   ), которые будут управлять порядком столбцов. Ничего больше   (селективность a или b вообще не учитывается).

    Согласитесь, что мы должны заказывать столбцы на основе предложения where, но утверждение «(селективность a или b вообще не учитывается)» неверно.) «Наиболее избирательные столбцы должны быть ведущими, если это удовлетворило первую роль («where clause»)

    ответил Andjelko Miovcic 29 Jpm1000000pmMon, 29 Jan 2018 22:24:51 +030018 2018, 22:24:51

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

    Популярные теги

    security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132