如何使用count(distinct)和join优化Mysql

Question

我有两个表，它们的结构如下：

CREATE TABLE  `metaservice`.`user` (
  `id` bigint(18) NOT NULL AUTO_INCREMENT,
  `userId` bigint(18) NOT NULL,
  `name` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `userId` (`userId`) USING BTREE,
  KEY `nameIndex` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

CREATE TABLE  `metaservice`.`tweet` (
  `id` bigint(18) NOT NULL AUTO_INCREMENT,
  `tweetId` bigint(18) NOT NULL,
  `reqId` int(8) NOT NULL DEFAULT '0',
  `postedTime` datetime NOT NULL,
  `body` text NOT NULL,
  `userId` bigint(18) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK69A46713BA64537` (`userId`),
  KEY `reqId` (`reqId`),
  CONSTRAINT `FK69A46713BA64537` FOREIGN KEY (`userId`) REFERENCES `user` (`userId`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

我的sql查询如下：

select
        count(distinct user.name) as c 
    from
        tweet as tweet 
    inner join
        user as user 
            on tweet.userId=user.userId  
            and tweet.reqId in (
                327774,
            215173,
            104302,
            239188,
            317122,
            972632,
            424187,
            644254,
            946792,
            543258)

当tweet表有6W记录，而user表有6w+记录时，速度太慢，查询返回结果:60594 in 10.45sec

Answer 1

我建议您使用EXPLAIN如下：

EXPLAIN select
        count(distinct user.name) as c 
    from
        tweet as tweet 
    inner join
        user as user 
            on tweet.userId=user.userId  
            and tweet.reqId in (
                327774,
            215173,
            104302,
            239188,
            317122,
            972632,
            424187,
            644254,
            946792,
            543258)

然后分析EXPLAIN给你的响应。有关MySQL解释的更多信息，可以通过以下来源找到：

MySQL Explain Syntax

Optimize Queries with Explain

Using MySQL Explain

MySQL Explain Reference

查看结果后，您需要确定应该对哪些列进行索引。

Answer 2

试试这个：

select count(*) as c from (
select
       user.name 
    from
        tweet as tweet 
    inner join
        user as user 
            on tweet.userId=user.userId  
            and tweet.reqId in (
                327774,
            215173,
            104302,
            239188,
            317122,
            972632,
            424187,
            644254,
            946792,
            543258)
group by user.name) a

Answer 3

我建议你给你的键建立索引。索引不仅仅用于主键或唯一键。如果表中有任何列可供搜索，则几乎总是应该对它们进行索引。

另外，请阅读Link