Подтвердить что ты не робот

Оболочка Mysql против составного и индексного столбца

В следующем запросе

SELECT  col1,col2
FROM    table1
WHERE   col3='value1'
  AND   col4='value2'

Если у меня есть 2 отдельных индекса один на col3, а другой на col4, какой из них будет использоваться в этом запросе?

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

Во-вторых, если я создал составной индекс, используя как col3, так и col4, но использовал только col3 в WHERE strong > , что будет хуже для производительности?  Пример:

SELECT  col1,col2
FROM    table1
WHERE   col3='value1'

Наконец, лучше ли использовать индексы Covering во всех случаях? и отличается ли он от систем хранения MYISAM и innodb?

4b9b3361

ОТВЕТЫ

Ответ 1

Индекс покрытия не совпадает с составным индексом.

Если у меня есть 2 отдельных индекса один на col3, а другой на col4, какой из них будет использоваться в этом запросе?

Индекс с наивысшей мощностью.
MySQL сохраняет статистику, какой индекс имеет какие свойства. Будет использоваться индекс, который имеет наиболее различающую силу (как видно из статистики MySQL).

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

Вы можете использовать подзапрос.
Или даже лучше использовать составной индекс, который включает как col3, так и col4.

Во-вторых, если я создал составной индекс, используя оба col3 и col4 вместе, но использовал только col3 в предложении WHERE, это будет хуже для производительности? Пример:

Составной индекс
Правильный термин compound index, а не композитный.
Будет использоваться только самая левая часть составного индекса.
Поэтому, если индекс определяется как

index myindex (col3, col4)  <<-- will work with your example.
index myindex (col4, col3)  <<-- will not work.

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

Обратите внимание: если вы выберете самое левое поле, вы можете уйти, не используя эту часть индекса в своем предложении where.
Представьте, что у нас есть составной индекс

Myindex(col1,col2)

SELECT col1 FROM table1 WHERE col2 = 200  <<-- will use index
SELECT * FROM table1 where col2 = 200     <<-- will NOT use index.

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

Что такое индекс покрытия
Индекс покрытия относится к случаю, когда все поля, выбранные в запросе, являются covered индексом, в этом случае InnoDB (не MyISAM) никогда не будет считывать данные в таблице, а только использует данные в индексе, значительно ускоряя выбор. Обратите внимание, что в InnoDB первичный ключ включен во все вторичные индексы, так что все вторичные индексы являются составными индексами.
Это означает, что если вы запустите следующий запрос в InnoDB:

SELECT indexed_field FROM table1 WHERE pk = something

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

Ответ 2

Я подтвердил ответ Johan для полноты, но Я думаю, что следующее утверждение, которое он делает относительно вторичных индексов, неверно и/или запутывает;

Note that in InnoDB the primary key is included in all secondary indexes, 
so in a way all secondary indexes are compound indexes.

This means that if you run the following query on InnoDB:

SELECT indexed_field FROM table1 WHERE pk = something

MySQL will always use a covering index and will not access the actual table.

Пока я согласен, что первичный ключ ВКЛЮЧЕН во вторичный индекс, Я не согласен с MySQL "всегда будет использовать индекс покрытия в запросе SELECT, указанном здесь.

Чтобы понять, почему, обратите внимание, что полный индекс "сканировать" всегда требуется в этом случае. Это не то же самое, что операция "искать", а вместо этого - 100% -ное сканирование содержимого вторичного индекса. Это связано с тем, что вторичный индекс не упорядочен первичным ключом; он упорядочивается "indexed_field" (, иначе он не будет использоваться в качестве индекса!).

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

Ответ 3

Это вопрос, который я слышу много, и есть много путаницы вокруг проблем из-за:

  • Различия в mySQL на протяжении многих лет. Индексы и поддержка нескольких индексов менялись с годами (к поддержке)

  • различия InnoDB/myISAM Существуют некоторые ключевые различия (ниже), но я не считаю, что несколько индексов являются одним из них.

MyISAM старше, но доказан. Данные в таблицах MyISAM разделяются между тремя различными файлами для: - формата таблицы, данных и индексов.
InnoDB относительно новее, чем MyISAM, и безопасен для транзакций. InnoDB также обеспечивает блокировку строк, а не стоп-блокировку, что увеличивает многопользовательский concurrency и производительность. InnoDB также имеет ограничения внешнего ключа.
Из-за своей функции блокировки строк InnoDB хорошо подходит для сред с высокой нагрузкой.

Чтобы убедиться в этом, обязательно используйте explain_plan для анализа выполнения запроса.

Ответ 4

Компонентный индекс не совпадает с составным индексом.

  • Composite index охватывает все столбцы в вашем фильтре, объединяет и выбирает критерии. Все эти столбцы хранятся на всех страницах индекса соответственно по всему индексу B-дерева.
  • Компонентный индекс охватывает все столбцы фильтра и объединения ключей в B-дереве, но сохраняет столбцы выбора только на листах листа, поскольку их не будут искать, а только извлекать! Это экономит пространство и, следовательно, создает меньше индексных страниц, следовательно, более быстрый ввод-вывод.