Я работаю над MySQL 5.5.29-0ubuntu0.12.04.1.
Мне нужно создать запрос, который может сортировать результаты по дате и по счету.
Я прочитал документацию и сообщения здесь в stackoverflow (в частности, this) о том, как оптимизировать запрос, но я все еще изо всех сил пытаюсь сделать это хорошо. Основные выводы заключаются в том, что во избежание использования временной таблицы ORDER BY или GROUP BY должны содержать только столбцы из первой таблицы в очереди соединения, поэтому используется предложение STRAIGHT_JOIN и два немного разных запроса.
Чтобы избежать путаницы, я собираюсь присвоить номер различным конфигурациям запросов:
- порядок по дате с предложением STRAIGHT_JOIN
- упорядочить по счету с предложением STRAIGHT_JOIN
- порядок по дате без предложения STRAIGHT_JOIN
- упорядочить по баллам без предложения STRAIGHT_JOIN
Ниже приведен запрос 1, выполнение которого занимает около 2,5 секунд:
SELECT STRAIGHT_JOIN item.id AS id
FROM item
INNER JOIN score ON item.id = score.item_id
LEFT JOIN url ON item.url_id = url.id
LEFT JOIN doc ON url.doc_id = doc.id
INNER JOIN feed ON feed.id = item.feed_id
INNER JOIN user_feed ON feed.id = user_feed.feed_id AND score.user_id = user_feed.user_id
LEFT JOIN star ON item.id = star.item_id AND score.user_id = star.user_id
JOIN unseen ON item.id = unseen.item_id AND score.user_id = unseen.user_id
WHERE score.user_id = 1 AND user_feed.id = 7
ORDER BY zen_time DESC
LIMIT 0, 10
Ниже приведен запрос 2 (первые таблицы соединений инвертированы, а столбец упорядочения отличается), для его выполнения требуется всего около 0,01 секунды:
SELECT STRAIGHT_JOIN item.id AS id
FROM score
INNER JOIN item ON item.id = score.item_id
LEFT JOIN url ON item.url_id = url.id
LEFT JOIN doc ON url.doc_id = doc.id
INNER JOIN feed ON feed.id = item.feed_id
INNER JOIN user_feed ON feed.id = user_feed.feed_id AND score.user_id = user_feed.user_id
LEFT JOIN star ON item.id = star.item_id AND score.user_id = star.user_id
JOIN unseen ON item.id = unseen.item_id AND score.user_id = unseen.user_id
WHERE score.user_id = 1 AND user_feed.id = 7
ORDER BY score DESC
LIMIT 0, 10
Ниже приведены результаты EXPLAIN для запросов.
Объясните запрос 1:
Объясните запрос 2:
Объясните запрос 3:
Объясните запрос 4:
Результат профилировщика для запроса 1:
Результат профилировщика для запроса 2:
Результат профилировщика для запроса 3:
Результат профилировщика для запроса 4:
Ниже приведены определения таблиц:
CREATE TABLE `doc` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`md5` char(32) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `Md5_index` (`md5`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `feed` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`url` text NOT NULL,
`title` text,
PRIMARY KEY (`id`),
FULLTEXT KEY `Title_url_index` (`title`,`url`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `item` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`feed_id` bigint(20) unsigned NOT NULL,
`url_id` bigint(20) unsigned DEFAULT NULL,
`md5` char(32) NOT NULL,
PRIMARY KEY (`id`),
KEY `Md5_index` (`md5`),
KEY `Zen_time_index` (`zen_time`),
KEY `Feed_index` (`feed_id`),
KEY `Url_index` (`url_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `score` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) unsigned NOT NULL,
`item_id` bigint(20) unsigned NOT NULL,
`score` float DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `User_item_index` (`user_id`,`item_id`),
KEY Score_index (`score`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `star` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) unsigned NOT NULL,
`item_id` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `User_item_index` (`user_id`,`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `unseen` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) unsigned NOT NULL,
`item_id` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `User_item_index` (`user_id`,`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `url` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`doc_id` bigint(20) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
KEY Doc_index (`doc_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`email` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `IDX_Email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `user_feed` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) unsigned NOT NULL,
`feed_id` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `User_feed_index` (`user_id`,`feed_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Вот количество строк для таблиц, задействованных в запросе:
Score: 68657
Item: 197602
Url: 198354
Doc: 186113
Feed: 754
User_feed: 721
Star: 0
Unseen: 150762
Какой подход мне следует выбрать, поскольку моя программа должна иметь возможность упорядочивать результаты как по zen_time, так и по максимально быстрому оцениванию?
ORDER BY zen_time DESC
, ваша производительность ухудшится? - person StevieG   schedule 04.10.2013STRAIGHT_JOIN
, по сути, вы говорите, что знаете лучше, чем оптимизатор запросов, и в случае первого запроса вы этого не сделаете, если для запуска требуется 2,5 секунды сSTRAIGHT_JOIN
И 0,1 секунды для запуска без . Без этого MySQL может свободно определять, какая таблица, по его мнению, будет наиболее эффективной в качестве 1-й таблицы, при этом она обязана использовать то, что вы определяете как первую таблицу, и поддерживать порядок соединения. - person GarethD   schedule 04.10.2013