Индексы и первичные ключи нескольких столбцов

Прошел поиск и не нашел ответа на этот конкретный вопрос о нобе. Приносим извинения, если я пропустил это.

В базе данных MySQL у меня есть таблица со следующим основным ключом

Идентификатор PRIMARY KEY (счет-фактура, элемент)

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

MySQL не жалуется, когда я определяю следующее:

ИНДЕКС (счет-фактура), ИНДЕКС (позиция), ИД ПЕРВИЧНОГО КЛЮЧА (счет-фактура, элемент)

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

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

Ответ 1

Я не очень хорошо знаком с внутренними индексами на mySql, но в двух продуктах поставщиков баз данных, которые мне знакомы (индексы MsSQL, Oracle), есть сбалансированные структуры дерева, узлы которых упорядочены как упорядоченный кортеж в столбцах указатель определяется на (в определении последовательности)

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

Другими словами, это означает, что если у вас есть индекс (a, b, c, d), то запрос, который фильтрует (a), (a, b) или (a, b, c), также может используйте индекс, но запрос, который необходимо фильтровать на (b), или (c) или (b, c), не сможет использовать индекс...

Итак, в вашем случае, если вам часто приходится фильтровать или сортировать только столбец item, вам нужно добавить еще один индекс в этот столбец сам по себе...

Ответ 2

Я лично использую phpMyAdmin для просмотра и редактирования структуры баз данных MySQL. Это веб-приложение, но оно достаточно хорошо работает на локальном веб-сервере (я запускаю экземпляр apache на моей машине для этого и phpPgAdmin).

Что касается составного ключа (invoice, item), он действует как индекс для (invoice, item) и для invoice. Если вы хотите индексировать только item, вам нужно добавить этот индекс самостоятельно. Ваша PK будет сортироваться по invoice, а затем по item, где invoice будет одинаковой во многих записях. Хотя порядок в составной PK не имеет значения для принудительного выполнения уникальности, это имеет значение для доступа.

На вашей таблице я бы использовал:

PRIMARY KEY id (invoice, item), INDEX (item)

Ответ 3

Чтобы вернуть информацию индекса таблицы, вы можете использовать:

SHOW INDEX FROM <table>;

Смотрите: http://dev.mysql.com/doc/refman/5.0/en/show-index.html

Для просмотра информации таблицы:

SHOW CREATE TABLE <table>;

Смотрите: http://dev.mysql.com/doc/refman/5.0/en/show-create-table.html

Первичные ключи - это индексы, поэтому нет необходимости создавать дополнительные индексы. Вы можете узнать больше о них в синтаксисе CREATE TABLE (там слишком много для вставки здесь):

http://dev.mysql.com/doc/refman/5.0/en/create-table.html

Ответ 4

Я не знаком с MySQL, но, как правило, индекс с несколькими столбцами одинаково полезен для первого столбца индекса как индекс только для этого столбца. Индекс с несколькими столбцами становится менее полезным для запросов к одному столбцу, а затем столбец появляется в индексе.

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

Например, если у вас есть таблица следующим образом:

Col1 |Col2 |Col3
----------------
   A |   1 |   Z
   A |   2 |   Y
   A |   2 |   X
   B |   1 |   Z
   B |   2 |   X

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

A
+-1
  +-Z
+-2
  +-X
  +-Y
B
+-1
  +-Z
+-2
  +-X

Поиск Col1 = 'A' прост: вам нужно только посмотреть на 2 упорядоченных значения. Однако, чтобы разрешить col3 = 'X', вы должны посмотреть все значения в 4 больших ведрах, каждый из которых упорядочен по отдельности.

Ответ 5

Существует разница между составным индексом и составным первичным ключом. Если вы определили составной индекс, как показано ниже

INDEX idx(invoice,item)

индекс не работает, если вы запрашиваете на основе item, и вам нужно добавить отдельный индекс

INDEX itemidx(item)

Но если вы определили составной первичный ключ, как показано ниже

PRIMARY KEY(invoice, item)

индекс будет работать, если вы запрашиваете на основе item, и отдельный индекс не требуется.

Рабочий пример:

mysql>create table test ( col1 int(20), col2 int(20) ) primary key(col1,col2);
mysql>explain select * from test where col2 = 1;
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | test  | index | NULL          | PRIMARY | 8       | NULL |   10 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+