MySQL-GROUP BY降低页面速度 - MySQL - GROUP BY slow down the page

GROUP BY子句在下面的查询中减慢了页面的速度，请帮助解决这个问题

 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给出的结果为：

- 1 SIMPLE a index PRIMARY,user_id,area_id,industry_id,country PRIMARY 4 NULL 20145 Using temporary; Using filesort

我们的要求如下

管理员登录中所有联系人的列表
经理/职员登录中联系人的中心列表

联系人表中的记录总数>20K。

user_centres表中将有多个用户条目，即：一个用户被分配到多个中心。

而排除GROUP BY在服务器中执行查询时，数据量接近300k，这就造成了问题。

Db结构

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

由于ORDER BY和GROUP BY子句不同，所以没有使用索引。

http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

您花了很多时间检查user_centres，但不需要任何内容。请将其从查询中删除。

users可以被制成相关子查询：

SELECT  `a`.*,
        CONCAT(a.`firstname`, " ", a.`lastname`) AS `cont_name`,
        CONCAT(a.`position`, " / ", a.`company`) AS `comp_pos`,
        ( SELECT  CONCAT(f.`firstname`, " ", f.`lastname`)
            FROM  `users` AS `f` ON f.id = a.user_id 
        ) AS `created_by`
    FROM  `contacts` AS `a`
    GROUP BY  `a`.`id`
    ORDER BY  `a`.`created` desc

你真的需要全部2万行吗？？结果的绝大部分是迟缓的一部分。

谢谢大家，根据大家的反馈，我现在已经尝试了下面的查询，并将速度从30秒提高到15秒

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秒。

请参阅界面（注意筛选和排序字段）-http://prntscr.com/6q6q70