Индексы и использование первичных ключей в качестве индексов в MySQL

У меня есть таблица Assets на движке InnoDB, определенная как:

CREATE TABLE Assets (
qid SMALLINT(5) NOT NULL,
sid BIGINT(20) NOT NULL AUTO_INCREMENT,
...
PRIMARY KEY (sid,qid),
KEY sid (sid)
);

Я запускаю следующий запрос:

SELECT COUNT(*) FROM Assets WHERE sid>10000;

На моей машине этот запрос занимает около 30 секунд с 2 миллионами записей в таблице. Теперь, если я изменяю запрос на использование индексов, результаты широко варьируются:

SELECT COUNT(*) FROM Assets USE INDEX(<index>) WHERE sid>10000;
  • NO INDEX: нет явного USE INDEX, т.е. первого SELECT запроса: 30 секунд
  • KEY sid (sid): 1,5 секунды
  • KEY cid (sid,qid): 1,5 секунды
  • PRIMARY: Я использовал USE INDEX(PRIMARY) в запросе.: 30 секунд

Итак, это мои вопросы:

  • Я думал, что запрос будет автоматически использовать первичный ключ в качестве индекса, основанный на этом. И все же между ними существует большая разница между USE INDEX (cid) и NO INDEX. Какая разница? Кроме того, как я явно указываю первичный ключ как индекс?

  • Если NO INDEX фактически не использует первичный ключ в качестве индекса, то что делает USE INDEX(PRIMARY), что заставляет его иметь такое же время работы, что и NO INDEX?

  • Есть ли разница между (USE INDEX(sid) "и USE INDEX(cid)) в запросе, который фильтрует только sid?

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

Хорошо, вот что я выяснил до сих пор:

Прежде всего, мне сказали, что настройка ключа должна быть либо: PRIMARY KEY(qid,sid), KEY(sid), либо PRIMARY KEY(sid,qid), KEY(qid). Я не совсем понимаю разницу. Если кто-то это сделает, сообщите мне.

Во-вторых, KEY sid (sid) ссылается на гораздо меньше страниц индекса, чем на более крупный ключ, поэтому он имеет тенденцию быть быстрее. Что касается разницы между использованием PRIMARY KEY в качестве индекса и правильного KEY (даже если они используют одни и те же поля), мне сказали, что это примерно так:

Первичные ключи индексируют все данные таблицы с полями первичного ключа. Это означает, что ПЕРВИЧНЫЙ КЛЮЧ и данные хранятся вместе. Таким образом, запрос, использующий PRIMARY KEY, должен будет пройти через все данные таблицы, которые даже индексируются, будут падать на большие неупакованные таблицы.

С дискретными ключами число строк может быть одинаковым, но сканируется гораздо меньший индекс (состоящий из указанных полей), который попадает на меньшее количество блоков диска и, следовательно, работает намного быстрее. Я предполагаю, что это также является причиной разницы в использовании USE INDEX(cid) и использовании первичного ключа в качестве индекса, оба из которых имеют одинаковые поля.

Ответы

Ответ 1

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

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

Если ваш первичный ключ (sid, qid), то я не вижу возможности использовать еще один ключ (sid), который, вероятно, может получить двигатель в качестве префикса PK. Я бы предпочел добавить индекс на qid, если я собираюсь использовать его, то есть, если у меня есть некоторые запросы, которые фильтруют или сортируют в этом поле, или если у меня есть несколько JOINs в этом поле.

По порядку полей первичного ключа я обычно пытаюсь определить, как они будут использоваться в моих запросах: если все мои запросы используют sid, а некоторые используют как sid, так и qid, затем выберите (sid, QID); если все они используют qid, и только некоторые из них также используют sid, затем выберите (qid, sid); если они используют либо sid, либо qid, тогда имеют PK (sid, qid) и другой ключ (qid), так что запросы с использованием обоих полей будут использовать ваш PK, то же самое произойдет для запросов, используя только sid, и, наконец, те, кто использует только qid, будут использовать ключ (qid).

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

Здесь вы можете найти подсказки по подсказкам индекса: http://dev.mysql.com/doc/refman/5.1/en/index-hints.html

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

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

НТН