Составной индекс имеет направление в MySQL?

Question

Когда будет необходимо следующее:

create index i_t_a_b on t(a,b);

create index i_t_b_a on t(b,a);

Answer 1

Если вы хотите получить максимальную скорость извлечения и иметь оба столбца в соединении или в определенных условиях, НО иногда столбец a имеет более высокую избирательность, а иногда столбец b имеет более высокую избирательность, и вы хотите извлечь выгоду из этого факта из одного индекса.

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

Тем не менее, опыт учит, что все зависит от многих факторов; с конкретными СУБД и средами приложений вы лучше запускаете свои собственные тесты.

РЕДАКТИРОВАТЬ: Дальнейшее объяснение составных индексов. из википедии:
"Порядок, в котором столбцы перечислены в определении индекса, важен. Можно получить набор идентификаторов строк, используя только первый индексированный столбец. Однако невозможно (или в большинстве баз данных) получить набор идентификаторы строк, использующие только второй или больший индексированный столбец.
Например, представьте телефонную книгу, которая организована сначала по городу, затем по фамилии, а затем по имени. Если вам указан город, вы можете легко извлечь список всех телефонных номеров этого города. Тем не менее, в этой телефонной книге было бы очень утомительно найти все номера телефонов для данной фамилии. Вы должны искать в разделе каждого города записи с этой фамилией."

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

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

Ах, просто подумал о лучшей аналогии с примером, который вы ищете. Представьте себе хороший учебник, в котором будет оглавление с главами и подразделами, а также количество страниц, на которых они находятся (это некластерный индекс, содержащий указатели). на записи данных - страницы). Теперь представьте, что учебник по стандарту SQL-92, тогда большинство терминов в оглавлении будут терминами SQL (придерживайтесь этого предположения). У вас также будет другой указатель в конце книги, в котором будут перечислены все интересные термины в алфавитном порядке (предположим, с основными названиями глав) и номера страниц.

Для такого вопроса, как "Скажите мне все главы, в которых появляется DISTINCT", вы должны использовать второй индекс. (потому что селективность более позднего поля высока)

Для таких вопросов, как "Скажите мне количество терминов, которые появляются в первой главе", вы бы использовали TOC

Так что для таких вопросов, как "Описан ли SELECT в главе DML?" Вы можете использовать любой из индексов. (потому что селективность обоих полей высока) Однако, если TOC самого DML имеет длину 3 страницы, а запись SELECT в индексе содержит только пятнадцать строк, вы, вероятно, перейдете ко второй, и это пример того, когда вы получаете выгоду от обоих индексов.

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

Как я уже говорил, все планирование в порядке, но в конце все же запустите свои собственные тесты.

Answer 2

Я не думаю, что есть какой-то реальный случай, когда вам это нужно.

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

Select Max(b) From t Where a=1  --# Would use i_t_a_b

а также

Select Max(a) From t Where b=1  --# Would use i_t_b_a

---

Допустим, ваш стол выглядит так:

a  b  c  d  e
-  -  -  -  -
0  8  x  x  x
0  9  x  x  x
1  8  x  x  x
1  9  x  x  x

i_t_a_b выглядит примерно так:

0
  8
  9
1
  8
  9

i_t_b_a выглядит примерно так:

8
  0
  1
9
  0
  1

---

Select Max(b) From t Where a=1

придется копаться в 8 а также 9 из i_t_b_a найти все строки с a=1, Это все еще намного быстрее, чем сканирование полной таблицы (нужно прочитать все x тоже), но это не так быстро, как при использовании i_t_a_b,