Как я избегаю полного сканирования таблицы с этим основным внутренним объединением?
У меня есть таблица, которая имеет внешний ключ к таблице, которая хранит некоторые данные блоба. Когда я делаю внутреннее объединение на таблицах с условием на основной таблице, тип соединения идет от 'индекса' до 'ВСЕХ'. Я хотел бы избежать этого, как моя таблица блоба находится на порядке десятков гигабайтов. Как я могу избежать его?
Вот основное внутреннее объединение:
EXPLAIN SELECT m.id, b.id, b.data
FROM metadata m, blobstore b
WHERE m.fkBlob = b.id;
1, 'SIMPLE', 'm', 'index', 'fk_blob', 'fk_blob', '4', '', 1, 'Using index'
1, 'SIMPLE', 'b', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'blob_index.m.fkBlob', 1, ''
Здесь я добавляю условие на основной таблице:
EXPLAIN SELECT m.id, b.id, b.data
FROM metadata m, blobstore b
WHERE m.fkBlob = b.id AND m.start < '2009-01-01';
1, 'SIMPLE', 'b', 'ALL', 'PRIMARY', '', '', '', 1, ''
1, 'SIMPLE', 'm', 'ref', 'fk_blob,index_start', 'fk_blob', '4', 'blob_index.b.id', 1, 'Using where'
Заметьте, что порядок, в котором перечислены таблицы, изменился. Это теперь делает полное сканирование таблицы на таблице блоба из-за условия, которое я добавил относительно основной таблицы.
Вот схема:
DROP TABLE IF EXISTS `blob_index`.`metadata`;
CREATE TABLE `blob_index`.`metadata` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`fkBlob` int(10) unsigned NOT NULL,
`start` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_blob` (`fkBlob`),
KEY `index_start` (`start`),
CONSTRAINT `fk_blob` FOREIGN KEY (`fkBlob`) REFERENCES `blobstore` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `blob_index`.`blobstore`;
CREATE TABLE `blob_index`.`blobstore` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`data` mediumblob NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
5 ответов
Думаю, вы пытаетесь это сделать на пустой таблице (потому что MySQL думает, что ему нужно пройти через одну строку, чтобы выполнить полное сканирование таблицы), что может повлиять на результаты работы планировщика. Когда вы будете делать это на реальной таблице, результаты EXPLAIN могут отличаться (и в моем тесте действительно отличались).
Оптимизатор считает, что при запросе будет полезно поменять порядок следования таблиц (что, скорее всего, означает, что статистика неактуальна).
Можно попробовать добавить индекс по метаданным (start, fkBlob):
CREATE INDEX ix_metadata_start_blob ON metadata (start, fkBlob)
и запустить ANALYZE TABLE на обеих таблицах. Таким образом, индекс на start будет использован для фильтрации по metadata, которые будут лидировать.
Вы также можете явно форсировать порядок соединения:
SELECT *
FROM metadata m
STRAIGHT_JOIN
blobstore b
ON b.id = m.fkBlob
WHERE m.start <= '2009-01-01'
, хотя обычно это не рекомендуется.
. Если я правильно прочитал то, что вы разместили, то он переходит из индекса в ссылку и eq_ref в все .
CREATE INDEX idx_metadata USING BTREE ON `metadata` (fkBlob,start);
Должен забрать его обратно.
.if the index doesnot take it right use HINTS
select /* INDEX <index_name> */
blah blah blah
from ........
В первом примере MySQL использовал метаданные fk_blob index, потому что это был закрывающий index-- всякий столбец, который вы использовали в запросе, присутствовал в индексе. (Вот что означает "использовать индекс".) Этот запрос все равно выполнял полное сканирование, но сканировал каждую строку через вторичный индекс, а не через первичный. Как только вы использовали start, вы потеряли индекс покрытия, и MySQL рассчитал, что быстрее использовать blobstore в качестве ведущего индекса. (Первичный индекс InnoDB интегрирован с хранилищем строк.)
Если вы хотите, чтобы MySQL продолжал использовать индекс метаданных в качестве ведущего индекса, убедитесь, что на нем есть один индекс, который будет полезен для запроса. Индекс на (start, fkBlob) лучше всего использовать для второго запроса, но это может быть не полезно для других запросов. Следующий лучший индекс - заменить (fkBlob) на (fkBlob, start). Вам придется балансировать, имея слишком много индексов (которые дорого обслуживаются), и имея эффективные планы запросов. Тест, тест, тест - и никогда вслепую не верьте объяснениям в вашей базе данных dev.
.