Индекс MariaDB для запросов подмножества column1 и диапазона column2

Question

У меня есть этот запрос:

SELECT column1 FROM table WHERE column2 IN (*small set of values*) AND column3 > number

В моей таблице 3 столбца, первичный ключ - (column1, column2).

Итак, я изучал составные индексы, но мне не очень ясно, в каком порядке должны быть столбцы в индексе (column2, column3) или (column3, column2), так как нет большой информации о том, как именно будет BTree для этого составного индекса быть построенным (по крайней мере, я не мог понять).

Итак, как построено дерево, и поможет ли оно мне больше, чем создание и индексирование, например, только для column2?

Бонусный вопрос: я видел кое-что о "покрывающем" индексе, который, кажется, работает для меня здесь, но, поскольку нет "бесплатного питания", каковы последствия этого? Меньше индекса умещается в памяти? Сохраняет ли MariaDB индекс в памяти?

Answer 1

(Я предполагаю, что вы используете InnoDB.)

• INDEX(col2, ...) будет лучше, если IN является более избирательным, чем >,
• INDEX(col3, ...) будет лучше, если > более избирательно
• InnoDB всегда ставит PRIMARY KEY столбцы в конце каждого вторичного индекса. Следовательно, INDEX(col2, col3) очень похоже INDEX(col2, col3, col1), который является "прикрытием". То же самое для (col3, col2),
• Ожидая, что PK будет добавлен, я явно добавлю его - это подсказка для других пользователей (и меня), что я стремился к "покрытию" или что-то еще.
• Оптимизатор (см. "MRR") может перепрыгнуть через IN ценности, так что...

Я рекомендую конкретно:

INDEX(col2,        -- hoping to leapfrog
      col3,        -- assuming the leapfrogging works
      col1)        -- covering

Может быть, лучше перейти на PRIMARY KEY(col2, col1) и не имеют дополнительного индекса. Это предполагает, что у вас не было col1 сначала в ПК, чтобы воспользоваться некоторыми другими запросами / запросами.

Как составной индекс создается в BTree? Подумайте о соединении столбцов (col1, col2) вместе, чтобы создать один ключ. (Детали могут быть сложнее, но думать об этом "работает".)

Примечание: Данные - это BT данные, упорядоченные в соответствии с PK. Вторичный индекс - это BTree столбцов во вторичном индексе, плюс PK, и ничего лишнего в конечных узлах.

MySQL и MariaDB хранят все индексы на диске (см. Выше), затем кэшируют блоки по 16 КБ в "buffer_pool", который находится в оперативной памяти. После некоторого времени работы системы блоки индекса, как правило, находятся в этом кеше; блоки данных могут или не могут.

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

В этом вся прелесть "кеширования" - вы приближаетесь к "бесплатной еде", чем неуклюжими приемами. Например... "Я загружу все свои индексы в оперативную память". Но что, если я использую только "новые" его части; это вытеснит другие виды использования оперативной памяти. "Я заблокирую эту таблицу в оперативной памяти". Опять же, это связано с другими видами использования ОЗУ, которые могут быть более эффективными.