У меня есть таблица 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() 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 секундИтак, вот мои вопросы:
Я думал, что запрос будет автоматически использовать первичный ключ в качестве своего индекса на основе this. И все же между USE INDEX (cid)
и NO INDEX
есть большая разница. Какая разница? Кроме того, как я могу явно указать первичный ключ в качестве индекса?
Если NO INDEX
на самом деле не использует первичный ключ в качестве индекса, что делает USE INDEX(PRIMARY)
, чтобы он имел такое же время работы, как НЕТ ИНДЕКС
?
Есть ли разница (не только с точки зрения производительности) между USE INDEX(sid)
и USE INDEX(cid)
в запросе, который фильтрует только по sid
] ?
Прошу прощения за длинный пост, но я хотел, чтобы он был открыт для обсуждения.
Хорошо, вот что я выяснил на данный момент:
Прежде всего, мне сказали, что установка ключа должна быть одной из следующих: PRIMARY KEY(qid,sid), KEY(sid)
или ПЕРВИЧНЫЙ КЛЮЧ(sid,qid), KEY(qid)
. Я не очень понимаю разницу. Если кто-то делает, пожалуйста, дайте мне знать.
Во-вторых, КЛЮЧ sid
( sid
) ссылается на гораздо меньше индексных страниц, чем ключ большего размера, поэтому он работает быстрее. Что касается разницы между использованием ПЕРВИЧНОГО КЛЮЧА в качестве индекса и правильного КЛЮЧА (даже если они используют одни и те же поля), мне сказали, что это примерно так:
Первичные ключи индексируют все данные таблицы с полями первичный ключ. Это означает, что ПЕРВИЧНЫЙ КЛЮЧ и данные хранятся вместе.Таким образом, запрос с использованием PRIMARY KEY должен будет пройти через все данные таблицы, которые даже в индексе увязнут в больших некэшируемых таблицах.
С дискретными ключами количество строк может быть таким же, но сканируется гораздо меньший индекс (состоящий из указанных полей), который затрагивает меньшее количество дисковых блоков и, следовательно, выполняется намного быстрее. Я предполагаю, что это также является причиной разницы в использовании USE INDEX(cid)
и использовании первичного ключа в качестве индекса, оба из которых имеют одинаковые поля.