Intereting Posts

Как оптимизировать запрос, содержащий объединения и подзапросы

Я унаследовал следующий запрос и структуру БД, и я хочу оптимизировать его, поскольку он медленный. Он содержит объединения и подзапросы, которые я прочитал, не является хорошим планом. Я пробовал различные способы улучшить его, но я застрял / потерял.

Если это хорошо, так как это хорошо, но если есть предложения по его улучшению, я был бы очень благодарен …

Запрос извлекает данные из разных таблиц, чтобы получить отчет о том, сколько кликов на веб-сайте поставщика, номер телефона «показывает», который был у поставщика, и электронные письма были отправлены поставщику.

Предложение WHERE использует 1 = 1, поскольку условия иногда добавляются для фильтрации отчета по региону, округу и типу бизнеса поставщика.

Код копируется из журнала mysql_slow для интерполяции всех переменных $. Структура таблиц выводится из дампа mysql.

Запрос:

SELECT Business.*, ( SELECT Count(Message.id) FROM messages as Message WHERE (U.id = Message.from_to OR U.id = Message.user_id) AND Message.created BETWEEN '2014-04-01 00:00:00' and '2014-04-30 23:59:59' ) as message_no, ( SELECT Count(DISTINCT(MessageUnique.user_id)) FROM messages as MessageUnique WHERE (U.id = MessageUnique.from_to OR U.id = MessageUnique.user_id) AND (MessageUnique.parent_message_id is null OR MessageUnique.parent_message_id = MessageUnique.id) AND MessageUnique.created BETWEEN '2014-04-01 00:00:00' and '2014-04-30 23:59:59' ) as message_unique_no, ( SELECT Count(*) FROM business_counties as bc2 WHERE Business.id = bc2.business_id ) as county_no, ( SELECT Count(click.id) FROM business_clickthroughs as click WHERE Business.id = click.business_id AND click.created BETWEEN '2014-04-01 00:00:00' and '2014-04-30 23:59:59' ) as clicks, ( SELECT Count(*) FROM business_regions as br2 WHERE Business.id = br2.business_id ) as region_no, ( SELECT count(BusinessReveal.id) as reveal_no FROM business_reveals as BusinessReveal WHERE 1=1 AND BusinessReveal.created BETWEEN '2014-04-01 00:00:00' and '2014-04-30 23:59:59' AND BusinessReveal.business_id = Business.id ) as reveals_no FROM businesses as Business LEFT JOIN users as U ON Business.id = U.business_id LEFT JOIN business_counties as bc ON Business.id = bc.business_id LEFT JOIN businesses_business_types as bt ON Business.id = bt.business_id LEFT JOIN business_regions as br ON Business.id = br.business_id WHERE 1=1 Group By Business.id; 

Структуры таблиц:

 /* Navicat MySQL Data Transfer Source Server : _Localhost Source Server Type : MySQL Source Server Version : 50530 Target Server Type : MySQL Target Server Version : 50530 File Encoding : utf-8 */ -- ---------------------------- -- Table structure for `business_clickthroughs` -- ---------------------------- DROP TABLE IF EXISTS `business_clickthroughs`; CREATE TABLE `business_clickthroughs` ( `id` bigint(12) unsigned NOT NULL AUTO_INCREMENT, `business_id` int(8) unsigned NOT NULL, `registered_user` tinyint(1) unsigned DEFAULT '0', `created` datetime NOT NULL, PRIMARY KEY (`id`), KEY `bid` (`business_id`) ) ENGINE=InnoDB AUTO_INCREMENT=29357 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT; -- ---------------------------- -- Table structure for `business_counties` -- ---------------------------- DROP TABLE IF EXISTS `business_counties`; CREATE TABLE `business_counties` ( `id` int(11) NOT NULL AUTO_INCREMENT, `business_id` int(11) NOT NULL, `county_id` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `bcid` (`business_id`) ) ENGINE=MyISAM AUTO_INCREMENT=20124 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=FIXED; -- ---------------------------- -- Table structure for `business_regions` -- ---------------------------- DROP TABLE IF EXISTS `business_regions`; CREATE TABLE `business_regions` ( `id` int(11) NOT NULL AUTO_INCREMENT, `business_id` int(11) NOT NULL, `region_id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=2719 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=FIXED; -- ---------------------------- -- Table structure for `business_reveals` -- ---------------------------- DROP TABLE IF EXISTS `business_reveals`; CREATE TABLE `business_reveals` ( `id` int(11) NOT NULL AUTO_INCREMENT, `business_id` int(11) NOT NULL, `customer_id` int(11) DEFAULT NULL, `created` datetime NOT NULL, `modified` datetime NOT NULL, PRIMARY KEY (`id`), KEY `bid` (`business_id`) ) ENGINE=InnoDB AUTO_INCREMENT=3172 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT; -- ---------------------------- -- Table structure for `businesses_business_types` -- ---------------------------- DROP TABLE IF EXISTS `businesses_business_types`; CREATE TABLE `businesses_business_types` ( `id` int(11) NOT NULL AUTO_INCREMENT, `business_id` int(11) NOT NULL, `business_type_id` int(11) NOT NULL, `level` int(2) NOT NULL DEFAULT '2', PRIMARY KEY (`id`), KEY `bid` (`business_id`) COMMENT '(null)' ) ENGINE=MyISAM AUTO_INCREMENT=4484 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=FIXED; -- ---------------------------- -- Table structure for `messages` -- ---------------------------- DROP TABLE IF EXISTS `messages`; CREATE TABLE `messages` ( `id` int(11) NOT NULL AUTO_INCREMENT, `subject` varchar(500) DEFAULT NULL, `message` text, `user_id` int(11) DEFAULT NULL, `message_folder_id` int(11) DEFAULT NULL, `parent_message_id` int(11) DEFAULT NULL, `status` int(11) DEFAULT NULL, `direction` int(11) DEFAULT NULL, `from_to` varchar(500) DEFAULT NULL, `attachment` varchar(500) DEFAULT NULL, `created` datetime DEFAULT NULL, `modified` datetime DEFAULT NULL, `guest_sender` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), KEY `fromto` (`from_to`(255)), KEY `uid` (`user_id`), KEY `pmid` (`parent_message_id`) ) ENGINE=InnoDB AUTO_INCREMENT=4582 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT; -- ---------------------------- -- Table structure for `users` -- ---------------------------- DROP TABLE IF EXISTS `users`; CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `login` varchar(255) COLLATE latin1_general_ci NOT NULL, `password` varchar(255) COLLATE latin1_general_ci NOT NULL, `name` varchar(255) COLLATE latin1_general_ci NOT NULL, `email` varchar(255) COLLATE latin1_general_ci NOT NULL, `title` varchar(20) COLLATE latin1_general_ci NOT NULL, `firstname` varchar(255) COLLATE latin1_general_ci NOT NULL, `lastname` varchar(255) COLLATE latin1_general_ci NOT NULL, `active` tinyint(1) NOT NULL DEFAULT '0', `first_visit` tinyint(1) NOT NULL DEFAULT '1', `signature` text COLLATE latin1_general_ci, `type` varchar(45) COLLATE latin1_general_ci DEFAULT 'customer', `business_id` int(11) DEFAULT NULL, `admin_monitor` tinyint(1) NOT NULL DEFAULT '0', `partner_name` varchar(255) COLLATE latin1_general_ci DEFAULT NULL, `postcode` varchar(255) COLLATE latin1_general_ci DEFAULT NULL, `venue_postcode` varchar(255) COLLATE latin1_general_ci DEFAULT NULL, `wedding_date` datetime DEFAULT NULL, `phone` varchar(255) COLLATE latin1_general_ci NOT NULL, `register_date` datetime DEFAULT NULL, `event` text COLLATE latin1_general_ci, `mailing_list` tinyint(1) NOT NULL DEFAULT '0', `created` datetime NOT NULL, `modified` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=2854 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ROW_FORMAT=DYNAMIC; 

План объяснения.

 id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY Business ALL - - - - 444 Using temporary; Using filesort 1 PRIMARY U ALL - - - - 2658 - 1 PRIMARY bc ref bcid bcid 4 Business.id 7 Using index 1 PRIMARY bt ref bid bid 4 Business.id 9 Using index 1 PRIMARY br ALL - - - - 440 - 7 DEPENDENT SUBQUERY BusinessReveal ref bid bid 4 func 5 Using where 6 DEPENDENT SUBQUERY br2 ALL - - - - 440 Using where 5 DEPENDENT SUBQUERY click ref bid bid 4 func 22 Using where 4 DEPENDENT SUBQUERY bc2 ref bcid bcid 4 func 7 Using index 3 DEPENDENT SUBQUERY MessageUnique ALL fromto,uid,pmid - - - 4958 Using where 2 DEPENDENT SUBQUERY Message ALL fromto,uid - - - 4958 Using where 

Ваш запрос имеет 6 коррелированных подзапросов и в итоге возвращает 444 строки. Каждый из этих коррелированных подзапросов эффективно выполняется для каждой возвращаемой строки. Следовательно, ваш единственный запрос приводит к чуть более 3000 запросов.

Лично я предпочитаю избегать этого, используя большое объединение или объединение по подзапросам. Однако это зависит от количества возвращенных строк

Кроме того, вы также присоединяетесь непосредственно к таблицам, которые вы делаете в левом соединении, в любом случае, которые генерируют много дубликатов, которые исключает GROUP BY. Поскольку вы ничего не принимаете непосредственно из большинства этих таблиц, а GROUP BY – это то, что кажется уникальным ключом, кажется неуместным.

Если вы сохраняете коррелированные подзапросы:

 SELECT Count(Message.id) FROM messages as Message WHERE (U.id = Message.from_to OR U.id = Message.user_id) AND Message.created BETWEEN '2014-04-01 00:00:00' and '2014-04-30 23:59:59' 

Для этого вспомогательного запроса нет полезного индекса в этой таблице. Поскольку вы проверяете 2 разных столбца для U.id, там не так много, что можно было бы сделать там, но индекс на созданном поможет. Возможно, было бы лучше дублировать этот вспомогательный запрос, однажды проверив from_to и однажды проверив user_id и добавив результаты вместе. Как вы могли бы тогда иметь индекс в соответствующем поле id и дате.

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

 SELECT Count(DISTINCT(MessageUnique.user_id)) FROM messages as MessageUnique WHERE (U.id = MessageUnique.from_to OR U.id = MessageUnique.user_id) AND (MessageUnique.parent_message_id is null OR MessageUnique.parent_message_id = MessageUnique.id) AND MessageUnique.created BETWEEN '2014-04-01 00:00:00' and '2014-04-30 23:59:59' 

Такая же проблема, как предыдущий подзапрос.

 SELECT Count(*) FROM business_counties as bc2 WHERE Business.id = bc2.business_id 

У этого есть ключ на business_id, и он должен быть в порядке

 SELECT Count(click.id) FROM business_clickthroughs as click WHERE Business.id = click.business_id AND click.created BETWEEN '2014-04-01 00:00:00' and '2014-04-30 23:59:59' 

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

 SELECT Count(*) FROM business_regions as br2 WHERE Business.id = br2.business_id 

Для этого необходим индекс business_id в таблице бизнес-регионов

 SELECT count(BusinessReveal.id) as reveal_no FROM business_reveals as BusinessReveal WHERE 1=1 AND BusinessReveal.created BETWEEN '2014-04-01 00:00:00' and '2014-04-30 23:59:59' AND BusinessReveal.business_id = Business.id 

Здесь ключ не охватывает созданную дату, а только идентификатор бизнеса.

Если вы хотите попробовать выполнить объединения по подзапросам (что может быть более эффективным, несмотря на то, что MySQL не подходит для присоединения к подзапросам), то что-то вроде этого (не тестировалось): –

 SELECT Business.*, mess_1.mess_count + mess_2.mess_count as message_no, mess_3.mess_count + mess_4.mess_count as message_unique_no, business1.county_no, click1.clicks, business_regions.region_no, business_reveals1.reveals_no FROM businesses as Business LEFT JOIN users as U ON Business.id = U.business_id LEFT OUTER JOIN ( SELECT Message.from_to, Count(Message.id) AS mess_count FROM messages as Message WHERE Message.created BETWEEN '2014-04-01 00:00:00' and '2014-04-30 23:59:59' GROUP BY Message.from_to ) AS mess_1 ON U.id = mess_1.from_to LEFT OUTER JOIN ( SELECT Message.user_id, Count(Message.id) AS mess_count FROM messages as Message WHERE Message.created BETWEEN '2014-04-01 00:00:00' and '2014-04-30 23:59:59' GROUP BY Message.user_id ) AS mess_2 ON U.id = mess_2.user_id LEFT OUTER JOIN ( SELECT MessageUnique.from_to, Count(DISTINCT(MessageUnique.user_id)) AS mess_count FROM messages as MessageUnique WHERE (MessageUnique.parent_message_id is null OR MessageUnique.parent_message_id = MessageUnique.id) AND MessageUnique.created BETWEEN '2014-04-01 00:00:00' and '2014-04-30 23:59:59' GROUP BY MessageUnique.from_to ) AS mess_3 ON U.id = mess_3.from_to LEFT OUTER JOIN ( SELECT MessageUnique.user_id, Count(DISTINCT(MessageUnique.user_id)) AS mess_count FROM messages as MessageUnique WHERE (MessageUnique.parent_message_id is null OR MessageUnique.parent_message_id = MessageUnique.id) AND MessageUnique.created BETWEEN '2014-04-01 00:00:00' and '2014-04-30 23:59:59' GROUP BY MessageUnique.user_id ) AS mess_4 ON U.id = mess_4.from_to LEFT OUTER JOIN ( SELECT business_id, Count(*) AS county_no FROM business_counties as bc2 GROUP BY Business.id ) as business1 ON Business.id = business1.business_id LEFT OUTER JOIN ( SELECT click.business_id, Count(click.id) AS clicks FROM business_clickthroughs as click WHERE click.created BETWEEN '2014-04-01 00:00:00' and '2014-04-30 23:59:59' GROUP BY click.business_id ) as click1 ON Business.id = click1.business_id LEFT OUTER JOIN ( SELECT br2.business_id, Count(*) AS region_no FROM business_regions as br2 WHERE Business.id = br2.business_id GROUP BY br2.business_id ) as business_regions ON Business.id = business_regions.business_id LEFT OUTER JOIN ( SELECT BusinessReveal.business_id, count(BusinessReveal.id) as reveal_no FROM business_reveals as BusinessReveal WHERE BusinessReveal.created BETWEEN '2014-04-01 00:00:00' and '2014-04-30 23:59:59' GROUP BY BusinessReveal.business_id ) as business_reveals1 ON business_reveals1.business_id = Business.id