MySQL - GROUP BY 减慢页面速度 [英] MySQL - GROUP BY slow down the page
问题描述
下面查询中的GROUP BY子句拖慢了页面，请帮忙解决这个问题
GROUP BY clause in the query below slow down the page, please help to resolve this issue
SELECT
`a`.*,
CONCAT(a.`firstname`, " ", a.`lastname`) AS `cont_name`,
CONCAT(a.`position`, " / ", a.`company`) AS `comp_pos`,
CONCAT(f.`firstname`, " ", f.`lastname`) AS `created_by`
FROM
`contacts` AS `a`
LEFT JOIN `users` AS `f` ON f.id = a.user_id
LEFT JOIN `user_centres` AS `b` ON a.user_id = b.user_id
WHERE b.centre_id IN (23, 24, 25, 26, 20, 21, 22, 27, 28)
GROUP BY `a`.`id`
ORDER BY `a`.`created` desc
这里与 user_centres 表的连接用于数据的中心过滤.EXPLAIN 给出的结果为:
Here the join with user_centres table is for centre wise filtering of data. EXPLAIN gives the result as:
- 1 SIMPLE a index PRIMARY,user_id,area_id,industry_id,country PRIMARY 4 NULL 20145 Using temporary; Using filesort
我们的要求如下
列出管理员登录中的所有联系人
Listing of all contacts in admin login
经理/文员登录中的联系人集中列表
Centre wise listing of contacts in manager/clerk login
联系人表中的总记录数 > 20K.
Total records in contact table is > 20K.
user_centres 表中将有多个用户条目，即:一个用户被分配到多个中心.
There will be multiple entry for users in user_centres table, ie: a user is assigned to more than one centre.
通过排除GROUP BY在服务器中执行查询时有近300k的数据，这导致了问题.
While executing the query in server by excluding GROUP BY is nearly 300k data which makes the problem.
数据库结构
表contacts
CREATE TABLE IF NOT EXISTS `contacts` (
`id` int(11) NOT NULL,
`user_id` int(11) DEFAULT NULL,
`imported` tinyint(4) NOT NULL DEFAULT '0',
`situation` char(10) DEFAULT NULL,
`firstname` varchar(150) DEFAULT NULL,
`lastname` varchar(150) DEFAULT NULL,
`position` varchar(150) DEFAULT NULL,
`dob` datetime DEFAULT NULL,
`office_contact` varchar(100) DEFAULT NULL,
`mobile_contact` varchar(100) DEFAULT NULL,
`email` varchar(255) NOT NULL,
`company` varchar(150) DEFAULT NULL,
`industry_id` int(11) DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
`city` varchar(150) DEFAULT NULL,
`country` int(11) DEFAULT NULL,
`isclient` tinyint(4) NOT NULL DEFAULT '0',
`classification` varchar(100) DEFAULT NULL,
`created` datetime NOT NULL,
`updated` datetime NOT NULL,
`unsubscribe` enum('Y','N') NOT NULL DEFAULT 'N'
) ENGINE=InnoDB AUTO_INCREMENT=25203 DEFAULT CHARSET=latin1;
表contacts
ALTER TABLE `contacts`
ADD PRIMARY KEY (`id`), ADD KEY `user_id` (`user_id`),
ADD KEY `industry_id` (`industry_id`), ADD KEY `country` (`country`);
表contacts
ALTER TABLE `contacts`
ADD CONSTRAINT `contacts_ibfk_4` FOREIGN KEY (`user_id`)
REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION,
ADD CONSTRAINT `contacts_ibfk_6` FOREIGN KEY (`industry_id`)
REFERENCES `industries` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION,
ADD CONSTRAINT `contacts_ibfk_7` FOREIGN KEY (`country`)
REFERENCES `country` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION;
表users
CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) NOT NULL,
`role_id` int(11) NOT NULL,
`email` varchar(250) NOT NULL,
`password` varchar(45) NOT NULL,
`salt` varchar(45) DEFAULT NULL,
`status_id` int(11) DEFAULT NULL,
`status` tinyint(1) DEFAULT '1',
`firstname` varchar(255) NOT NULL,
`lastname` varchar(255) NOT NULL,
`created` datetime DEFAULT NULL,
`updated` datetime DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
表users
ALTER TABLE `users`
ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `email_UNIQUE` (`email`),
ADD KEY `type_id_idx` (`role_id`), ADD KEY `status_id_idx` (`status_id`);
表users
ALTER TABLE `users`
ADD CONSTRAINT `role_id` FOREIGN KEY (`role_id`)
REFERENCES `users_roles` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `status_id` FOREIGN KEY (`status_id`)
REFERENCES `users_status` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `users_ibfk_1` FOREIGN KEY (`area`)
REFERENCES `area` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION;
表user_centres
CREATE TABLE IF NOT EXISTS `user_centres` (
`id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`area_id` int(11) NOT NULL,
`centre_id` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=72 DEFAULT CHARSET=utf8;
表user_centres
ALTER TABLE `user_centres`
ADD PRIMARY KEY (`id`), ADD KEY `user_id` (`user_id`),
ADD KEY `centre_id` (`centre_id`), ADD KEY `area_id` (`area_id`);
表user_centres的约束
ALTER TABLE `user_centres`
ADD CONSTRAINT `user_centres_ibfk_1` FOREIGN KEY (`user_id`)
REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
ADD CONSTRAINT `user_centres_ibfk_2` FOREIGN KEY (`centre_id`)
REFERENCES `centre` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION;
另请参阅解释屏幕 - http://prntscr.com/6o5h8s
Also please refer EXPLAIN screens - http://prntscr.com/6o5h8s
推荐答案
谢谢大家，根据大家的反馈，我现在尝试了下面的查询，并将速度从 30 秒提高到 15 秒
Thanks all, based on the feedback got from all of you I have tried the query below now and give me the speed improvement from 30 secs to 15 secs
SELECT `a`.`id`, `a`.`user_id`, `a`.`imported`, `a`.`created`,
`a`.`unsubscribe`, CONCAT(a.firstname, " ", a.lastname) AS `cont_name`,
CONCAT(a.position, " / ", a.company) AS `comp_pos`,
( SELECT COUNT(uc.id)
FROM `user_centres` AS `uc`
WHERE (uc.user_id = a.user_id)
AND (uc.centre_id IN (29))
GROUP BY `uc`.`user_id`
) AS `centre_cnt`,
( SELECT GROUP_CONCAT(DISTINCT g.group_name
ORDER BY g.group_name ASC SEPARATOR ", ")
FROM `groups` AS `g`
INNER JOIN `group_contacts` AS `gc` ON g.id = gc.group_id
WHERE (gc.contact_id = a.id)
GROUP BY `gc`.`contact_id`
) AS `group_name`,
( SELECT CONCAT(u.`firstname`, " ", u.`lastname`)
FROM `users` AS `u`
WHERE (u.id = a.user_id)
) AS `created_by`, `e`.`name` AS `industry_name`
FROM `contacts` AS `a`
LEFT JOIN `industries` AS `e` ON e.id = a.industry_id
WHERE (1)
HAVING (centre_cnt is NOT NULL)
ORDER BY `a`.`id` desc
让我知道有没有办法提高速度并使页面加载时间低于 5 秒.
Let me know Is there a way to improve the speed and make the page loading below 5 secs.
请看界面(注意过滤和排序字段)-http://prntscr.com/6q6q70
Please see the interface (noted the filtering and sorting fields) - http://prntscr.com/6q6q70
这篇关于MySQL - GROUP BY 减慢页面速度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
